数据库chapter6课件讲解.ppt

Step3. With respect to R2(CTHRS) the restriction of F to R2(CTHRS) is F2={C ?T, TH ?R, HR ?C, HS ?R} the candidate key is HS R2 (CTHRS) is not in BCNF, because there is C ?T in F2, and C is not the candidate key of R2 R2(CTHRS) is decomposed into R21(CT) and R22(CHRS) R21(CT) is in BCNF , F21= {C ?T} Step4. With respect to R22(CHRS) the restriction of F to R22(CHRS) is F22={HR ?C, HS ?R, CH ? R} /* CH ?TH, TH ?R the candidate key is HS R22(CHRS) is not in BCNF, because there is HR ?C in F22, and HR is not candidate key of R22 R22(CHRS) is decomposed into R221(HRC) and R222(HRS) R221(HRC) is in BCNF Step5. With respect to R222(HRS), the restriction of F to R222(HRS) is F222={HS ?R}, and the candidate key is HS R222(HRS) is in BCNF Finally, the BCNF decomposition of R(C, T, H, R, S, G) is R1(CSG) , R21(CT) , R221(HRC) , R222(HRS) Testing for BCNF To check if a non-trivial dependency ???? causes a violation of BCNF 1. compute ?+ (the attribute closure of ?), and 2. verify that it includes all attributes of R, that is, it is a superkey of R. Simplified test: To check if a relation schema R is in BCNF, it suffices to check only the dependencies in the given set F for violation of BCNF, rather than checking all dependencies in F+. If none of the dependencies in F causes a violation of BCNF, then none of the dependencies in F+ will cause a violation of BCNF either. DataBase System Concepts However, using only F is incorrect when testing a relation in a decomposition of R Consider R = (A, B, C, D, E), with F = { A ? B, BC ? D} Decompose R into R1 = (A,B) and R2 = (A,C,D, E) Neither of the dependencies in F contain only attributes from (A,C,D,E) so we might be mislead into thinking R2 satisfies BCNF. In fact, dependency AC ? D in F+ shows R2 is not in BCNF. DataBase System Concepts To check if a relation Ri in a decomposition of R is in BCNF, use the original set of dependencies F that hold on R, but with the following test: for ev