数据库课件lec10norm幻灯片.ppt

* * * * * * * * * * * * * * * * * * * * Dependency Preserving Decompositions (Contd.) Decomposition of R into X and Y is dependency preserving if (FX ? FY ) + = F +. Important to consider F + in this definition: ABC, A ? B, B ? C, C ? A, decomposed into AB and BC. Is this dependency preserving? Is C ? A preserved? note: F + contains F ? {A ? C, B ? A, C ? B}, so… FAB contains A ?B and B ? A; FBC contains B ? C and C ? B .So, (FAB ? FBC)+ contains C ? A Decomposition into BCNF Consider relation R with FDs F. If X ? Y violates BCNF, decompose R into R - Y and XY (guaranteed to be loss-less). Repeated application of this idea will give us a collection of relations that are in BCNF; lossless join decomposition, and guaranteed to terminate. e.g., CSJDPQV, key C, JP ? C, SD ? P, J ? S {contractid, supplierid, projectid, deptid, partid, qty, value} To deal with SD ? P, decompose into SDP, CSJDQV. To deal with J ? S, decompose CSJDQV into JS and CJDQV So we end up with: SDP, JS, and CJDQV Decomposition of CSJDPQV intoSDP, JS, and CJDQV What if we do the decomposition by using the dependency J?S first? BCNF and Dependency Preservation In general, there may not be a dependency preserving decomposition into BCNF. decomposition of CSJDPQV into SDP, JS and CJDQV is not dependency preserving (w.r.t. the FDs JP ? C, SD ? P and J ? S). i.e., the dependency JP ? C can not be enforced without a join. However, it is a lossless join decomposition. In this case, adding JPC to the collection of relations gives us a dependency preserving decomposition. but JPC tuples are stored only for checking the FD. (Redundancy!) Third Normal Form (3NF) Relation R with FDs F is in 3NF if, for all X ? A in F+ A ? X (called a trivial FD), or X is a superkey of R, or A is part of some candidate key (not superkey!) for R. Minimality of a key is crucial in third condition above! If R is in BCNF, obviously in 3NF. If R is in 3NF, some redundancy is pos