清华大学数据库原理课件第七章.pptVIP

数 据 库 原 理 王 建 民 清 华 大 学 软 件 学 院 2003年/秋 Main Contents Boyce-Codd Normal Form Third Normal Form Multivalued Dependencies Fourth Normal Form Overall Database Design Process Boyce-Codd Normal Form ?? ? ? is trivial (i.e., ? ? ?) ? is a superkey for R Example R = (A, B, C)F = {A ? B B ? C}Key = {A} R is not in BCNF Decomposition R1 = (A, B), R2 = (B, C) R1 and R2 in BCNF Lossless-join decomposition Dependency preserving Testing for BCNF To check if a non-trivial dependency ???? in F+ causes a violation of BCNF 1. compute ?+ (the attribute closure of ?) 2. verify that it includes all attributes of R, that is, it is a superkey of R Simplified Testing for BCNF To check if a relation schema R with a given set of functional dependencies F 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+ We can show that 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 No Test on a Decomposition of R Consider R (A, B, C, D), with F = { A ?B, B ?C} Decompose R into R1(A,B) and R2(A,C,D) Neither of the dependencies in F contain only attributes from (A,C,D) so we might be mislead into thinking R2 satisfies BCNF In fact, dependency A ? C in F+ shows R2 is not in BCNF However, using only F is incorrect when testing a relation in a decomposition of R BCNF Decomposition Algorithm result := {R}; done := false;compute F+;while (not done) do if (there is a schema Ri in result that is not in BCNF) then begin let ?? ? ? be a nontrivial functional dependency that holds on Ri such that ?? ? Ri is not in F+, and ? ? ? = ?; result := (result – Ri) ? (Ri – ?) ? (?, ? ); end else done := true; Example of BCNF Decomposition R = (branch-name, branch-city, assets, customer-name, loan-number, amount)F = {branch-name ? assets branch-city loan-number ? amount branch-name}Key = {loa