《kc第9讲-关系数据库设计扩展知识.pptVIP

第9讲: (第78章) 关系数据库设计扩展知识  重庆大学计算机学院 分解应注意保持无损连接！ 分解应注意保持函数依赖！ 理想情况是两者同时保持！ BCNF模式的判定方法 To check if a non-trivial dependency ???? causes a violation of BCNF 1) compute ?+ , and 2) verify that ?+ includes all attributes of R (? is a superkey of R). 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 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. But, using 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. （A ? B先用增补律加C，后用传递律） BCNF子模式的判定方法 To check if a relation Ri in a decomposition of R is in BCNF, Either test Ri for BCNF with respect to the restriction of F to Ri (that is, all FDs in F+ that contain only attributes from Ri) or use the original set of dependencies F that hold on R, but with the following test: for every set of attributes ? ? Ri, check that ?+ (F下) either includes no attribute of Ri- ?, or includes all attributes of Ri. （即?为Ri的超键） If the condition is violated by some ??? ? in F, the dependency ??? (?+ - ??) ? Rican be shown to hold on Ri, and Ri violates BCNF. We use above dependency to decompose Ri 到BCNF的无损分解算法 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