北邮-数据库系统原理(英文)-7-15讲课.ppt

Database System Concepts - Chapter 7 Relational-Database Design - * E.g.1 R={SNO，SD，Head，CNO，G} F={SNO?SD，SNO?Head，SD?Head，(SNO,CNO)?G} step1. as far as SNO?SD is concerned, R is decomposed into BCNF R1={SNO, SD} , on which F1={SNO?SD} holds non-BCNF R2={SNO, Head, CNO, G}, on which F2={SNO?Head, (SNO, CNO)?G} holds step2. as for SNO?Head, R2 is decomposed into BCNF R3 = {SNO, Head}, on which F2={SNO?Head} holds BCNF R4 = {SNO, CNO, G}, on which F3 = {(SNO,CNO)?G} holds Appendix C Examples about BCNF Decomposition Database System Concepts - Chapter 7 Relational-Database Design - * E.g.2 R = (A, B, C), F = {A ? B, B ? C} key = {A}, R is not in BCNF with respect to B ? C , decomposition R1 = (A, B), R2 = (B, C) R1 and R2 in BCNF lossless decomposition dependency preserving Appendix C Examples about BCNF Decomposition (cont.) Database System Concepts - Chapter 7 Relational-Database Design - * E.g.3 R = (J, K, L), F = {JK ? L , L ? K} two candidate keys: JK and JL R is not in BCNF decomposition R1 = (K, L), F1 = {L ? K} R2 = (J, L) any decomposition of R will fail to preserve JK ? L Appendix C Examples about BCNF Decomposition (cont.) Database System Concepts - Chapter 7 Relational-Database Design - * 定理。如果属性A只出现在F中函数依赖的左部, 则A一定是主属性, 必然出现在候选键中 输入：关系模式R及其函数依赖集F 输出：R的所有候选关键字 方法： step1.将R的所有属性分为四类： L类：仅出现在F中函数依赖左部的属性 R类：仅出现在F中函数依赖右部的属性 N类：在F中函数依赖左右两边均未出现的属性 LR类：在F中函数依赖左右两边均出现的属性 Appendix D Computing of Candidate Keys Database System Concepts - Chapter 7 Relational-Database Design - * 并令X_set代表L、N类，Y_set代表LR类； step2. 求X_set +，若包含了R的所有属性，则X_set即为R的唯一候选关键字，转step5； 否则转step3 step3. 在Y_set中取一属性A，求（X _set ∪ A)+,若它包含了R的所有属性，则转step4; 否则，调换一属性反复进行这一过程，直到试完所有Y_set中的属性 step4. 如果已找出所有的候选关键字，则转step5; 否则，在Y_set中依此取两个、三个，…，求他们的属性闭包，直到其闭包包含R的所有的属性 step5. 停止，输出结果 Appendix D Computing of Candidate Keys (cont.) Database System Concepts - Chapter 7 Relational-Database Design - * E.g.1. R(X, Y, Z, W), and F = {X?Z, Z?X, Y