1、第7章 关系数据库设计,关系数据库设计的过程,逻辑模式 (关系模型),概念模式 (E-R模型),3.逻辑设计,用户需求,4.物理设计,2.概念设计,1.需求分析,开始,“好”的关系数据库设计,关系数据库是由一组关系构成的“好”的关系数据库设计要求我们找到一些“好”关系然而有时包含“坏”关系的“坏”数据库设计具有某些问题,关系数据库中的问题,考虑以下数据库设计 关系：worker (name, branch, manager) 假设一个部门仅有一位经理。但反过来，一个经理可以是多个部门的经理。,worker,关系数据库中的问题,该数据库设计有如下问题 冗余 思考：有几个元组存储了David是部门
2、B经理的信息？ 同部门的每一个员工都不必要地重复关于谁是部门经理的信息,worker,更新异常 思考：如果部门B的经理变成Debbie，需要更新几个元组？ 如果一个部门的经理变动，我们必须更新部门里的每个员工以反映谁是新经理，否则就会出现这样的错误：该部门有两个经理,worker,worker,关系数据库中的问题,插入异常 思考：假设成立一个新部门D。经理是kevin，没有员工。我们能增加关于部门D的信息么？ 如果一个部门还没有员工，我们就无法添加这个部门的信息,worker,worker,关系数据库中的问题,删除异常 思考：在删除了部门B的所有员工以后，我们还能找出谁是部门B的经理么？ 如果
3、一个部门里的所有员工都被删除了，谁是部门经理的信息也会被删除,worker,worker,谁是部门B的经理?,什么导致了这些问题？,不良的数据依赖 函数依赖(简记为FD)是一种数据依赖，它具有以下形式 (读作：蕴涵)意义：当任意两个元组在属性集上相等时，则它们在属性集上也相等。即同一个（的值），必然对应同一个（的值）,什么导致了这些问题？,我们可以在worker关系中发现某些函数依赖，如下所示name branch branch manager name manager,worker,什么导致了这些问题？,worker关系的这些函数依赖中, branch manager是不良的 由于左边的br
4、anch不是关系的码（这里指超码），同一branch可能出现多次，造成同一组合: (branch，manager)的重复,worker,如何解决这些问题？,分解 把一个“坏”关系分解为几个更小但是“好”的关系 例如worker (name, branch, manager) worker1 (name, branch), branch (branch, manager),如何解决这些问题？,在分解后消除了不良函数依赖，所以避免了同一组合的重复，解决了以上问题,worker,worker1,branch,如何解决这些问题？,考虑另一种分解方案，如下所示 worker (name, branch,
5、 manager) worker2 (name, manager), branch (branch, manager),worker,worker2,Branch,如何解决这些问题？,思考 在分解后，你能找出Jones在哪个部门么？ 不正确的分解可能带来新的问题：丢失某些信息 例如，这里会丢失有关员工属于哪个部门的信息,worker,worker2,Branch,数据库设计理论,如何获得“好”的数据库设计？ 判定其中的一个关系R是否“好”的，即有没有“坏”数据依赖 如果R不是好的，则要正确地分解为几个较小的好关系 重复以上两步，直到全部关系都变成“好”的为止,数据库设计理论,两个要点 如何知道
6、一个关系是否“好”的，是否有“坏”数据依赖 判断属于哪一级别的范式 越高级的范式，不良数据依赖就越少 如何正确地分解某个关系(没有信息的丢失) 进行规范化,思考与练习,观察以下关系，然后回答问题 问题1：在这个关系上能找到哪些函数依赖？它们是“好”的还是“坏”的？ 问题2: 它是一个“好”关系么？如果不是，存在哪些问题？,选修,函数依赖,定义R: 一个关系 U: 关系R中的所有属性 , : R中一或多个属性的集合,函数依赖,我们说关系R上存在以下函数依赖 (读作：蕴涵 ，或依赖)的条件是当且仅当：如果两个元组如果在属性集上相等，它们在属性集上必然相等（ 同一个对应同一个）t1 = t2 t1
7、= t2 ,函数依赖,例如 考虑以下关系以上关系满足什么函数依赖？,选修,特殊类型的函数依赖,函数依赖 ，当 时是平凡的；否则是非平凡的例如，以下是平凡的函数依赖 name, branch name name name例如，以下是非平凡的函数依赖 name, branch manager,思考与练习,平凡的函数依赖总是成立的，为什么？ 换句话说，平凡的函数依赖是没有意义的，我们一般所讨论的函数依赖都应该排除这种情况。,特殊类型的函数依赖,函数依赖 是部分的，当 存在 , 使 成立 例如，以下函数依赖是部分的 name, branch manager函数依赖 是完全的，当 对任何 , 例如，以下
8、函数依赖是完全的 name manager SNO, CNO result,f,f,p,特殊类型的函数依赖,函数依赖 是传递的，当 存在 ，使 , 同时成立 例如. name manager是传递的, 因为 name branch, 且branch manager函数依赖 是非传递的，当 不存在 ，使 , 同时成立 例如. name branch 是非传递的,t,思考与练习,如果 是部分的，那么它也是传递的。为什么？ 是部分的 存在 , ， 是传递的,码和函数依赖,在关系R中 K是一个超码，当且仅当 K U (U 是R中全部属性的集合)K是一个候选码，当且仅当 K U, 且 任何K的真子集K都
9、不满足: K U 思考 如何证明,函数依赖集的闭包,R: 一个关系 F: R满足的函数依赖的集合,函数依赖集的闭包,可从F推出某些函数依赖 例如，如果有F=A B, B C，可以推导出A C也成立能从F推导出的全部函数依赖（包括F自身）的集合，就是F的闭包 (用F+表示) in F+ 能从 F 中推导出例如，对 F=A B, B C, F+ = ？ A B, B C, A C, A A, B B, ,函数依赖集的闭包,我们可以应用Armstrong公理来找到F+中的所有(函数依赖)如果 , 那么 (自反律)如果 , 那么 (增广律)如果 , 且 , 那么 (传递律)如果 , 且 , 那么 (结
10、合律)如果 , 那么 且 (分解律)如果 且 , 那么 (伪传递律),函数依赖集的闭包,R ( A, B, C, G, H, I ) F = A B, A C, CG H, CG I, B H F+中的某些成员 A H 用传递律从A B 和 B H推出 AG I 用增广律从A C 推出AG CG 用传递律从AGCG, CGI 推出AG I CG HI 用结合律从CGH和CGI 推出,函数依赖集的闭包,实际上，找到F+中的所有函数依赖是非常复杂的工作，也意义不大 重要的是给出一些函数依赖（F），判断另外一个函数依赖 是否成立(在F+中) 。这要用到另外一个概念：属性集的闭包,属性集的闭包,R:
11、一个关系 F: R所满足的函数依赖集合 a, ： R中一或多个属性的集合我们定义属性集a的闭包（用a +表示）为a蕴涵的所有属性的集合（包括a自身） a is in a+ 例如，给出 F=A B, B C, (A)+ = (ABC), (B)+ = ?,属性集的闭包,计算闭包的算法（输入a，输出a +） 开始: a+ := a; while (a+ is changed ) do for F中每个函数依赖 begin if a+ then a+ := a+ end,属性集的闭包,R (A, B, C, G, H, I) F = CG H, CG I, A B, A C, B H (AG)+ 开
12、始. (AG)+ = AG1. (AG)+ = ABCG (A B, A C)2. (AG)+ = ABCGHI (CG H, CG I)3. (AG)+ = ABCGHI (无变化) 思考 我们现在能推导出AG BCI么? 是，因为(BCI) (AG) +结论 判断a 是否成立，等价的方法判断是否在a +中,属性集的闭包,R (A, B, C, G, H, I) F = CG H, CG I, A B, A C, B H (AG)+ = ABCGHI (AG)是候选码么？ (AG)是超码么？ (AG) U ? U (AG)+ ? (AG)的某个真子集是超码么？ A U ? U (A)+ ?
13、G U ? U (G)+?,练习,R (C, T, H, R, S) F = CT, HRC, HTR, HSR 问题1：求(HR) +？ 问题2：HS是候选码么？ 问题3：CH S成立么？,为什么使用范式？,“好”的关系数据库设计 如何知道一个关系是否“好”，是否有“坏”的数据依赖 判断属于哪一级别的范式 高级范式与低级范式相比，是“更好”的关系，因为“坏”数据依赖更少,范式,范式 满足一定要求的所有关系的全集 范式级别(从低级到高级) 第一范式 (1NF) 第二范式 (2NF) 第三范式 (3NF) BC范式 (BCNF) 第四范式* (4NF) 范式越高级，代表的关系就越“好”，要满足的
14、要求也就越高,范式,不同范式之间的联系 4NF BCNF 3NF 2NF 1NF 高级范式是低级范式的子集 范式越高，满足的要求也就越高；满足高要求的关系肯定能够满足低要求，所以高级范式中的关系肯定也在低级范式中,第一范式,要求 关系的每个属性都是原子的 原子属性 属性值不可再分 例如. age, sex 非原子属性 属性值可以再分，包括 复合属性，例如parents 多值属性，例如phone-numbers,第一范式,例子 下面关系中，哪些在1NF中，哪些不在？,Student,Student,Student,一些术语和解释,例子关系 R(ABCDE)。候选码只有一个ABC，所以也是主码。
15、码属性 一个属性，出现在某个候选码中 例如：A, B, C 非码属性 一个属性，不出现在任何一个候选码中 例如：D, E （候选码）码的一部分 就是候选码的真子集。 例如，AB, BC, AC, A, B, C等等,一些术语和解释,超码 具有唯一性的属性集。 超码里面如果有多余的属性，那么去除后剩下的就是候选码。所以直观上看，超码含有或等于候选码。 例如：ABC, ABCD, ABCE, ABCDE都是超码 非码 非超码，也就是超码以外，不具有唯一性的属性集。 直观上看，非码不含有候选码 例如：AC, AD, CD, BD等都是非码,第二范式,要求 关系在1NF中 每个非码属性都完全函数依赖于
16、候选码 判断是否属于2NF：检查每个非码属性Y所依赖的X（即XY）， X是否候选码的一部分？是，不属于。都不是，属于。,第二范式,例子 以下关系在1NF中？在2NF中？为什么？ 它们有我们以前讨论过的四个问题吗？,SC,SC,第三范式,要求 关系在1NF中 每个非码属性都非传递依赖于候选码 判断是否属于3NF：检查每个非码属性Y所依赖的X（即XY），X是非码么？是，不属于3NF。都不是，属于。 因为任何属性集都函数依赖于候选码(KU，注意U是全部属性)，所以非码属性Y依赖于非码X的同时，也就传递依赖了候选码K (KXY),第三范式,例子 以下关系在3NF中？在2NF中？ 它们有我们以前讨论过的
17、四个问题吗？,Student,Student,第三范式,思考 如何证明3NF是2NF的子集?（或者，如何证明满足3NF的条件也就一定满足2NF的条件?）要点：函数依赖 是非传递的 是完全的反证：如果 是部分的 是传递的，与前提矛盾,BC范式,要求 关系在1NF中 对每个非平凡的函数依赖 ，一定是超码(具有唯一性) 判断是否属于BCNF：检查每一个(非平凡的)函数依赖，左边都是超码？有一个不是，不属于；全部都是，属于。可以证明：BCNF是3NF的真子集,BC范式,例子 考虑以下关系 R(S, T, C)S: 学生; T: 教师; C: 课程,R,BC范式,假设一个教师只教一门课程，但是一门课程有
18、多个教师。也就是TC 假设给定一个学生和一门课程，只有一个老师给他上这门课程。也就是SCT所以函数依赖集为: F= TC, SCT ,R,BC范式,R(S, T, C), F= TC, SCT 问题1: R的候选码是什么？ 候选码: ST, SC 证明: (ST)+=(STC), (SC)+=(STC),所以ST, TC是超码. 而(S)+=(S), (T)+=(TC), (C)+=(C), 所以ST, SC的真子集都不是超码,R,BC范式,R(S, T, C), F= TC, SCT , 候选码: ST, SC问题2: R在3NF中么？在BCNF中么？ R在3NF中，因为R中没有非码属性 R
19、不在BCNF中，因为TC是非平凡的，且左边T不是超码,R,BC范式,R(S, T, C), F= TC, SCT , 候选码: ST, SC问题3: 它们有我们以前讨论过的四个问题吗？ 是的，因为组合(T, C)的值重复,R,BC范式,总结 3NF消除了所有非码属性对候选码的传递依赖，即任何非码属性Y所函数依赖的X （XY），都是候选码或者比候选码更大的超码。 BCNF在3NF的基础上，消除了所有码属性对候选码的传递依赖，即任何码属性Z所函数依赖的X （XZ），都是候选码或者比候选码更大的超码。 总之，到BCNF为止，任何有意义（非平凡）的函数依赖的左边都必须是具有唯一性的超码，不管右边是什么
20、。,总结,在学习这么多的范式以后,结论 高级范式的问题比低级范式要少 但即使是非常高级的范式，仍然可能还是有问题所以我们不能期望没有任何问题的“完美”关系。只能尽量让关系达到尽可能高的范式，使问题尽可能的少,如何让关系达到更高的范式?,分解 把一个属于低级范式的“坏”关系，分解为几个属于高级范式的 “好”关系 但是某些情况下分解会带来新的问题，比如信息丢失，这样的分解是不正确的。两个要点 1. 什么样的分解方案才是正确的（不丢失信息的）? 无损连接分解 2. 怎么找到正确的分解方案? 规范化,无损连接分解,无损连接分解 不会丢失信息的分解判定“一分二”是否无损连接分解的充分必要条件 将关系R分
21、解为R1和R2 。则当以下两个函数依赖之一能够成立时，这种是无损的R1 R2 R1R2R1 R2 R2R1,无损连接分解,R(C, T, H, R, S) F = CT, HRC, HTR, HSR 问题1: R R1(C, H, S), R2(C, T, H, R). 这一分解是无损的么?是的，因为 CH+ (CTHR) CH TR R1 R2 R2R1 R 分解为 R1, R2 是无损的 思考: R R1(C, H, S), R2(C, T), R3(C, H, R). 这一分解是无损的么,规范化,规范化 将一个属于低级范式的“坏”关系，分解为多个属于高级范式的“好”关系，且无信息丢失的过
22、程根据目标范式的级别，又有 规范化到1NF 规范化到3NF 规范化到BCNF,规范化到1NF,非1NF的关系变为1NF的关系 方法：将关系中每个非原子的属性转化成原子的 复合属性的处理：转化成相应的多个原子属性,Student,Student,规范化到1NF,多值属性的处理：移出去生成一个新关系，同时还包含原来的主码（新关系的主码为这两者的和）,Student,Student,SC,规范化到3NF,1NF关系分解为3NF关系的算法 输入 : R（属于1NF）, F（R满足的函数依赖集合） 输出 : R1, R2, R3Rn（都属于3NF）,规范化到3NF,1- n=0; (n是输出关系个数)f
23、or F 中每一个 do if 已经在某个输出关系Ri (1 i n)中 then什么也不做else if 是某一个输出关系Ri (1 i n) 的主码 then Ri := Ri + ; elsen := n+1; Rn := ; (增加一个新关系， 作为主码)end if 2- if R每个候选码都不出现在输出关系Ri (1i n) 中 then n := n+1;Rn := R的任何一个候选码 end if,步骤,步骤,规范化到3NF,例1 输入: R(A, B, C, D, E), F = (AB, CD, D E) 候选码: AC 1. R1(AB)2. R1(AB), R2(CD)
24、3. R1(AB), R2(CD), R3(DE)4. R1(AB), R2(CD), R3(DE), R4(AC)Output: R1(AB), R2(CD), R3(DE), R4(AC),规范化到3NF,例2 输入: R(A, B, C, D, E, F), F = (ABD, CE, AB C, CF) 候选码: AB 1. R1(ABD)2. R1(ABD), R2(CE)3. R1(ABCD), R2(CE)4. R1(ABCD), R2(CEF)Output: R1(ABCD), R2(CEF),规范化到BCNF,1NF关系分解为BCNF关系的算法 输入 : R1（属于1NF）,
25、 F（R1满足的函数依赖集合） 输出 : R1, R2, R3Rn（都属于BCNF）,规范化到BCNF,n=0; for F 中每个 dofor 每个包含 的关系Ri do if 不是Ri的超码 thenRi = Ri if 是另一个输出关系Rj (1 i n) 的主码 then Rj := Rj + ; elsen := n+1; Rn := ; (增加一个新关系， 作为主码)end ifend if,规范化到BCNF,例1 输入: R1(A, B, C, D, E, F), F = (ABD, CE, AB C, CF) 候选码: AB 1. R1(ABCDF), R2(CE) 处理CE2
26、. R1(ABCD), R2(CEF) 处理CFOutput: R1(ABCD), R2(CEF),规范化到BCNF,例2 输入: R1(A, B, C, D), F = (ABC, CA, C D) 候选码: AB, BC1. R1(BCD), R2(CA) 处理CA2. R1(BC), R2(CAD) 处理CDOutput: R1(BC), R2(CAD)思考 把上面的关系规范化到3NF，结果如何？试与BCNF的结果作比较 此时原来的函数依赖ABC, CA, C D在规范化的结果关系上是否还成立？,规范化到BCNF与规范化到3NF的对比规范化到BCNF得到的关系问题更少，但是可能丢失某些函
27、数依赖（在原来的关系上成立，但在分解后的关系上不成立）规范化到3NF得到的关系可能不是很好，但往往已经足够好了。而且不会丢失任何函数依赖,练习,R (C, T, H, R, S) F = CT, HRC, HTR, HSR 问题： 1. HT是否R的候选码? HS呢? 2. R最高属于第几范式? 试证明之 3. 把R规范到BCNF级别 4. 证明你在3中使用的分解是无损分解,答案,R (C, T, H, R, S) F = CT, HRC, HTR, HSR 问题： 1. HT是否R的候选码? HS呢? 答:HT不是R的候选码，推理如下： (HT)+=(HTRC) U (HT)+ HT U H
28、T不是超码 HT不是候选码HS是R的候选码，推理如下： (HS)+=(HSRTC) U (HS)+ HS U HS是超码 (H)+=(H) U (H)+ H U H不是超码 (S)+=(S) U (S)+ S U S不是超码 综合 HS是最小的超码即候选码,答案,R (C, T, H, R, S) F = CT, HRC, HTR, HSR 问题： 2. R最高属于第几范式? 试证明之 答: R最高属于第二范式，推理如下： R属于第二范式（思路：证明全部非码属性: C、T、R，对候选码HS的依赖都是完全的，即不会函数依赖于HS的一部分：H或S，也就是不在H+或者S+中） (H)+=(H), (
29、S)+=(S) 所有非码属性: T, C, R都不函数依赖于主码HS的一部分 R属于第二范式 R不属于第三范式，推理如下： 由CT可知，非码属性C函数依赖T，而T又函数依赖于候选码HS HSC, CT 非码属性T对候选码HS的函数依赖是传递的 R不属于第三范式综上可知，R最高属于第二范式,答案,R (C, T, H, R, S) F = CT, HRC, HTR, HSR 问题： 3. 把R规范到BCNF级别 规范化的分解过程如下 1. R(HSRC), R1(CT)2. R(HSR), R1(CT), R2(HRC)所以规范化的输出为： R(HSR), R1(CT), R2(HRC),答案,R (C, T, H, R, S) F = CT, HRC, HTR, HSR 问题： 4. 证明你在3中使用的分解是无损分解首先证明R分解为R(HSRC), R1(CT)是无损分解: C T R R1R1R R分解为R, R1是无损的再证明R分解为R(HSR), R2(HRC)是无损的: HR CRR2R2RR分解为R,R2无损所以R分解为R(HSR), R1(CT), R2(HRC)是无损的,
