Database Systems
--Unt6. the Relational Theorem •苏向阳
6. the Relational Theorem
知识点5 Normalization Based
on FD
☞A relational schema R is in first normal form(1NF)if the domains of all attributes of R are atomic.
NO composite attributes, such as:
customer( customer-id, name(first-name, middle-initial, last-
name), date-of-birth )
Each attribute as an unit, even they have several part that have individual information.
A tuple has only one value at each attribute.
☞A schema R not in 1NF, then it’s NOT a relational schema. ☞A relation R is in 1NF is not ‘good’ enough.
For relation:
Employee( emp_id, emp_name, emp_phone, dept_name,
dept_phone, dept_mgrname, skill_id, skill_name, skill_date, skill_lvl)
➢Is in 1NF
➢Has Insert Anomaly, Delete Anomaly, Update Anomaly
and Data Redundancy .
☞A relational schema R is in second normal form (2NF) if there are NO non-prime attributes dependent on Candidate Key partially.
☞Example：R(A, B,C,D), F = {AB →C, AC →BD} Candidate Key : AB, AC
non-prime attributes: D
AB →D, AC→D are all fully dependency
R∈2NF
F c = { emp_id→(emp_name, epm_phone, dept_name),
dept_name→( dept_phone, dept_mgrname) ,
skill_id→skill_name,
(emp_id, skill_id, skill_date)→skill_lvl}
emp_info( emp_id, emp_name, epm_phone, dept_name, dept_phone, dept_mgrname, skill_id, skill_name, skill_date,
skill_lvl)
F c = { emp_id→(emp_name, epm_phone, dept_name),
dept_name→( dept_phone, dept_mgrname) ,
skill_id→skill_name,
(emp_id, skill_id, skill_date)→skill_lvl}
emp_info ∈2NF CK: (emp_id, skill_id, skill_date)
(emp_id, skill_id, skill_date) →emp_name is partially.
Decomposition(模式分解):
⚫emp(emp_id, emp_name, epm_phone, dept_name,
dept_phone, dept_mgrname)
F c = {emp_id→(emp_name, epm_phone, dept_name),
dept_name→( dept_phone, dept_mgrname) }∈2NF
⚫skill ( skill_id, skill_name)
F c = {skill_id→skill_name}∈2NF
Decomposition(模式分解):
⚫emp_skill( emp_id, skill_id, skill_date, skill_lvl)
F c = { (emp_id, skill_id, skill_date)→skill_lvl}∈2NF
☞A relation R is in 2NF is not ‘good’ enough.
⚫emp(emp_id, emp_name, epm_phone, dept_name,
dept_phone, dept_mgrname)
F c = {emp_id→(emp_name, epm_phone, dept_name),
dept_name→( dept_phone, dept_mgrname) }∈2NF ➢Has Insert Anomaly, Delete Anomaly, Update Anomaly
and Data Redundancy .
☞A relational schema R is in third normal form (3NF) if there are no nonprime attributes which transitively dependent on
a key for R.
☞Example：R(A, B, C, D), F = {AB →C, C →D}
Candidate Key : AB
non-prime attributes: C, D
D is transitively dependent on Key AB by attributes C.
R∈3NF
⚫
emp (emp_id, emp_name, epm_phone, dept_name,
dept_phone, dept_mgrname )F c = {emp_id →(emp_name, epm_phone, dept_name ), dept_name →( dept_phone, dept_mgrname ) }emp ∈3NF ∈2NF
Decomposition (模式分解):
⚫emp (emp_id, emp_name, epm_phone, dept_name )F c = {emp_id →(emp_name, epm_phone, dept_name ) }⚫dept (dept_name, dept_phone, dept_mgrname )F c = { dept_name →( dept_phone, dept_mgrname) }∈3NF ∈3NF
☞A relation R is in 3NF is not ‘good’ enough.
For relation:
STC( S, T, C) S—Student, T—Teacher, C--Course
F = { (S，C)→T, (S，T)→C, T→C }
➢There are no nonprime attribute. STC is IN 3NF.
➢Has Insert Anomaly, Delete Anomaly, Update Anomaly and Data Redundancy.
6.3.4 Normal Forms --BCNF
A relational schema R is in BCNF(Boyce-Codd Normal Form)if for all functional dependencies α→βin F,αis a superkey for R.
Example：For relation
STC( S, T, C) S—Student, T—Teacher, C--Course
F = { (S，C)→T, (S，T)→C, T→C }
➢T in T→C is NOT a superkey, STC is NOT IN BCNF.
➢STC is IN 3NF.
☞Theorem:
1NF ⊃2NF ⊃3NF ⊃BCNF
☞To determine a relation is in n NF, one should give the highest Normal Form.
☞Relation Database:
emp(emp_id, emp_name, epm_phone, dept_name)
F = { emp_id→emp_name, epm_phone, dept_name}
emp∈BCNF.
dept(dept_name, dept_phone, dept_mgrname)
F = {dept_name→dept_phone, dept_mgrname}
dept∈BCNF.
☞Relation Database:
……
skill ( skill_id, skill_name)
F = { skill_id→skill_name}
skill ∈BCNF.
emp_skill( emp_id, skill_id, skill_date, skill_lvl)
F = { emp_id, skill_id, skill_date→skill_lvl}
emp_skill∈BCNF.。
