第五章数据库设计2(规范化).ppt

* Normal Forms -- BCNF example1 关系模式SJP(S, J, P) 中，S是学生，J表示课程，P表示名次。每一学生选修每门课程的成绩都有一定名次，且名次不重复。 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 superkeySTC is NOT in BCNF. * Normal Forms Theorem: 1NF ? 2NF ? 3NF ? BCNF To determine a relation in nNF, one should give the highest Normal Form. * 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. * 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 * 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 * 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 Stall