【正文】 anch_city so nonprime attribute branch_city is transitively dependent on candidate key loan_number It is NOT in 3NF DataBase System Haichang Gao , Software School , Xidian University 57 Normal Forms 3NF ?The two definations are equivalent: ? A relation schema R is in third normal form (3NF) if for all: ? ? ? in F + at least one of the following holds: ?? ? ? is trivial (., ? ? ?) (not exist in canonical cover ) ?? is a superkey for R ? Each attribute A in ? – ? is contained in a candidate key for R.(or for canonical cover, A in ? is Prime attribute) ? Another define: A relation R is in 3NF if there are no nonprime attributes which transitively dependent on a key for R. DataBase System Haichang Gao , Software School , Xidian University 58 Normal Forms 3NF ? For example ?emp (emp_id, emp_name, epm_phone, dept_name, dept_phone, dept_mgrname ) ∈ 2NF F = { emp_id ? emp_name, epm_phone, dept_name, dept_name ? dept_phone, dept_mgrname } ?dept_name is NOT a superkey。 emp_name NOT in any candidate key。 emp is NOT in 3NF ?Nonprime attribute dept_phone is transitively dependent on candidate key emp_id. So emp is NOT in 3NF. DataBase System Haichang Gao , Software School , Xidian University 59 Normal Forms 3NF ? For example ?emp (emp_id, emp_name, epm_phone, dept_name, dept_phone, dept_mgrname ) F = { emp_id ? emp_name, epm_phone, dept_name, dept_name ? dept_phone, dept_mgrname } ?emp is NOT in 3NF ?Deposition ?emp (emp_id, emp_name, epm_phone, dept_name ) F = { emp_id ? emp_name, epm_phone, dept_name } emp∈ 3NF. ?dept (dept_name, dept_phone, dept_mgrname ) F = { dept_name ? dept_phone, dept_mgrname } dept ∈ 3NF. DataBase System Haichang Gao , Software School , Xidian University 60 Normal Forms 3NF ?A relation R is in 3NF is not ‘good’ enough. ? For relation: STC( S, T, C) S— Student, T— Teacher, CCourse F = { (S， C)→T, (S ， T)→C, T→C } ? There is no nonprime attribute. STC is IN 3NF. ? The first two FD, LHS is SuperKey C in T→C is prime attribute STC is IN 3NF. ? Has Insert Anomaly, Delete Anomaly, Update Anomaly and Data Redundancy . DataBase System Haichang Gao , Software School , Xidian University 61 Normal Forms BCNF ? A relation schema R is in BCNF(BoyceCodd Normal Form) with respect to a set F of functional dependencies if for all functional dependencies in F+ of the form ??? ? where ? ? R and ? ? R, at least one of the following holds: ??? ? ? is trivial (., ? ? ?) ?? is a superkey for R ? For example ? bor_loan ( customer_id, loan_number, amount ) F = { loan_number ? amount } ? bor_loan is not in BCNF, for loan_number is not a superkey ? bor_loan is not in 2NF, it just in 1NF. DataBase System Haichang Gao , Software School , Xidian University 62 Normal Forms BCNF ? example1 關(guān)系模式 SJP(S, J, P) 中， S是學(xué)生， J表示課程， P表示名次。每一學(xué)生選修每門課程的成績都有一定名次，且名次不重復(fù)。 FD: (S, J)→P ， (J, P)→S ? CK: (S, J), (J, P) LHS of each FD is superkey, SPJ is in BCNF. ? example2 ?STC( S, T, C) F = { (S,C)→T, (S,T)→C, T→C } ? There is no nonprime attribute. STC is IN 3NF. ?For T→C, T is not a superkey STC is NOT in BCNF. DataBase System Haichang Gao , Software School , Xidian University 63 Normal Forms ?Theorem: ?1NF ? 2NF ? 3NF ? BCNF ? To determine a relation in nNF, one should give the highest Normal Form. DataBase System Haichang Gao , Software School , Xidian University 64 Normal Forms ?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. ? 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. DataBase System Haichang Gao , Software School , Xidian University 65 Normal Forms (4NF) ? Multivalued dependency ?Let R be a relation schema and let ? ? R and ? ? R. The multivalued dependency(MVD, 多值依賴 ) ? ?? ? holds on R if in any legal relation r(R), for all pairs for tuples t1 and t2 in r such that t1[?] = t2 [?], there exist tuples t3 and t4 in r such that: t1[?] = t2 [?] = t3 [?] = t4 [?] t3[?] = t1 [?] t3[R – ?] = t2[R – ?] t4 ?] = t2[?] t4[R – ?] = t1[R – ?] X Y Z t1 x y1 z1 t2 x y2 z2 t3 x y1 z2 t4 x y2 z1 DataBase System Haichang Gao , Software School , Xidian University 66 Normal Forms ?For example ? WSC(W,S,C) W— warehouse S— safeguard C— cargo ? MVD: W??S W??C W S C w1 s1 c1 w1 s1 c2 w1 s1 c3 w1 s2 c1 w1 s2 c2 w1 s2 c3 w2 s3 c4 w2 s3 c5 w2 s4 c4 w2 s4 c5 DataBase System Haichang Gao , Software School , Xidian University 67 Normal Forms ? Consider a database classes (course, teacher, book ) ? MVD: course ?? teacher, course ?? book course teacher book database database database database database database operating systems operating systems operating systems operating systems Avi Avi Hank Hank Sudarshan Sudarshan Avi Avi Pete Pete DB Concepts Ullman DB Concepts Ullman DB Concepts Ullman OS Concepts Stallings OS Concepts Stallings classes DataBase System Haichang Gao , Software School , Xidian University 68 Normal Forms ? Consider a database classes (course, teacher, book ) ? Therefore, it is better to depose classes into: course teacher database database database operating systems operating systems Avi Hank Sudarshan Avi Jim teaches course book database database oper