第五章 关系的规范化(数据库原理与应用)

DataBase,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,魏英 ,tutor_,wei, 7952616,第五章 关系的规范化,教学内容,理解关系规范化的必要性,掌握函数依赖的有关概念,掌握1,NF，2NF，3NF，BCNF,和4,NF,的定义,掌握关系模式的分解方法,教学重点,关系模式的分解方法,教学难点,关系模式的范式,关系规范化,一个工资报表的实例,工程号,工程名称,职工号,姓名,职务,小时工资率,工时,实发,工资,A1,花园大厦,1001,齐,光明,工程师,65,13,845,1002,李思岐,技术员,60,16,960,1004,葛宇洪,律师,60,19,1140,小计,2945,A2,立交桥,1001,齐,光明,工程师,65,15,975,1003,鞠,明亮,工人,55,17,935,小计,1910,A3,临江,饭店,1002,李思岐,技术员,60,18,1080,1004,葛宇洪,律师,60,14,840,小计,1920,总计,6775,关系规范化,按照工资报表设计一个表,工程号,工程名称,职工号,姓名,职务,小时工资率,工时,实发,工资,A1,花园大厦,1001,齐,光明,工程师,65,13,845,1002,李思岐,技术员,60,16,960,1004,葛宇洪,律师,60,19,1140,A2,立交桥,1001,齐,光明,工程师,65,15,975,1003,鞠,明亮,工人,55,17,935,A3,临江,饭店,1002,李思岐,技术员,60,18,1080,1004,葛宇洪,律师,60,14,840,工程号,工程名称,职工号,姓名,职务,小时工资率,工时,A1,花园大厦,1001,齐,光明,工程师,65,13,A1,花园大厦,1002,李思岐,技术员,60,16,A1,花园大厦,1004,葛宇洪,律师,60,19,A2,立交桥,1001,齐,光明,工程师,65,15,A2,立交桥,1003,鞠,明亮,工人,55,17,A3,临江,饭店,1002,李思岐,技术员,60,18,A3,临江,饭店,1004,葛宇洪,律师,60,14,设计一个关系模式,R(,工程号，工程名称，职工号，姓名，职务，小时工资率，工时),关系规范化,典型问题,数据冗余,：如果一个职工参与多个工程项目，那么这个职工的职工号，姓名，职务和小时工资率就要重复几次存储。,操作异常,修改异常,：一个职工参与了多个工程项目，如果其职务发生变化，就必须修改多个元组，否则就会产生数据不一致,插入异常,：一个新来的职工尚未参与任何工程项目，那么要将其职工号，姓名，职务和小时工资率存储到关系中时，在工程号和工程名称属性上就没有值(空值),删除异常,：如果一个职工只参与一个工程项目，要取消其任务时，将同时删除其职工号，姓名，职务和小时工资信息,关系规范化,改进后的关系模式,工程号,工程名称,A1,花园大厦,A2,立交桥,A3,临江,饭店,职工号,姓名,职务,小时工资率,1001,齐,光明,工程师,65,1002,李思岐,技术员,60,1003,鞠,明亮,工人,55,1004,葛宇洪,律师,60,工程(工程号，工程名称),职工(职工号，姓名，职务，小时工资率),工程号,职工号,工时,A1,1001,13,A1,1002,16,A1,1004,19,A2,1001,15,A2,1003,17,A3,1002,18,A3,1004,14,报酬(工程号，职工号，工时),关系规范化,关系规范化是从,微观,角度研究关系模式中属性与属性之间的函数依赖性，判断关系模式中设计的,合理性,，解决数据库设计的,优化,问题,规范化是通过一组不同级别的,范式,判定关系规范化的程度，确认产生数据异常的原因，并通过关系模式的,分解,方法，消除数据异常,规范化设计理论主要包括三方面内容：,数据依赖,，,范式,和,模式设计方法,函数依赖,在数据依赖中，函数依赖(,F,unctional,D,ependency),是最基本、最重要的一种依赖,定义,：设有关系模式,R(U)，U,是,属性集合，,X,和,Y,是,U,的子集，对于,R(U),的,任意,一个可能的关系,r，,如果,r,中不存在两个元组,t,和,s，,使得,tX=sX,而,tYsY，,则称属性集,X,函数决定,属性集,Y，,或称,属性集,Y,函数依赖,属性集,X，,记为,XY,函数依赖,例,5.1：学生关系模式为学生(学号，姓名，性别，系名称，系地址),学号,姓名,性别,系,名称,系,地址,101,张三,男,计算机,C,102,李四,男,计算机,C,103,王五,女,计算机,C,104,赵六,女,经管,B,105,张三,女,理学,A,学生关系的,FD,集为,：,FD=,学号,姓名，,学号,性别，,学号,系名称，,学号,系地址,系名称,系地址,也可记为：,学号,姓名，性别，系名称，系地址,系名称,系地址,函数依赖,函数依赖是关系模式,R,上属性之间需要满足的约束条件，属于,语义,范畴的概念，只能根据语义来确定,若,XY,且,YX，,则记为,X,Y,例,：设有关系模式,R(A，B，C，D)，,在,R,的关系中，属性值之间存在以下联系：,A,值与,B,值有一对多联系，,C,值与,D,值有一对一联系，试根据这些规则写出,R,的,FD,集,A,值与,B,值有一对多联系：,BA,C,值与,D,值有一对一联系：,CD,和,DC,，,或,C,D,函数依赖与主码的联系,实际上，,FD,是,主码,概念的推广，是属性之间所发生的联系,设关系模式,R,的属性集是,U，X,是,U,的一个子集,如果,XU,在,R,上成立，则称,X,是,R,的一个,超码,如果,XU,在,R,上成立，但对于,X,的任一真子集,X,1,都有,X,1,U,不,成立，则称,X,是,R,的一个,候选码,函数依赖图,学号姓名性别系名称系地址,主码与,非,主属性,之间的函数依赖,其它属性之间的函数依赖,如果,A,是关系模式,R,中候选码的属性，则称,A,是,R,的,主属性,，否则，称,A,是,R,的,非主属性,之间的函数依赖,平凡函数依赖与非平凡函数依赖,在关系模式,R(U),中，对于,U,的子集,X,和,Y，,如果,XY，,且,Y,X，,则称,XY,是,平凡函数依赖,在关系模式,R(U),中，对于,U,的子集,X,和,Y，,如果,XY，,且,Y,X，,则称,XY,是,非平凡函数依赖,对于任意关系模式，平凡函数依赖都必然成立,完全函数依赖,在关系模式,R(U),中，对于,U,的子集,X,和,Y，,如果,XY，,并且对于,X,的任何真子集,X，,都不存在,XY，,则称,Y,完全函数依赖,于,X，,否则称,Y,部分函数依赖,于,X,例,5.2：已知关系模式成绩(学号，姓名，课程号，课程名称，学时，分数)，其,FD,集为：,学号,姓名,课程号,课程名称，学时,学号，课程号,分数,学号，课程号,姓名,学号，课程号,课程名称，学时,f,f,f,p,p,学号,姓名,课程号,课程名称 学时 分数,传递函数依赖,在关系模式,R(U),中，对于,U,的子集,X、Y,和,Z，,如果,XY，YZ，,且,Y,X，ZY，,不,存在,Y,X，,则称,Z,传递函数依赖,于,X,例5.3：已知关系模式,SDP(,学号，姓名，系名称，系地址)，其,FD,集为：,学号,姓名，系名称，系地址,系名称,系地址,学号,系地址,t,学号姓名系名称系地址,关系模式的范式,衡量关系模式好坏的标准就是模式的,范式,(,N,ormal,F,orms),范式的种类与数据依赖有着直接联系,基于,FD,的范式：1,NF，2NF，3NF，BCNF,基于多值依赖的范式：4,NF,一个低一级范式的关系模式，通过,模式分解,可以转换为若干个高一级范式的关系模式的集合，这种过程就叫做,规范化,第一范式,定义,：在关系模式,R,的每个关系,r,中，如果每个属性值都是不可再分的,原子值,，则称,R,为第一范式的模式，即,R1NF,1NF,是关系模式应具备的最基本条件，满足1,NF,的关系称为,规范化的关系,，否则称为,非规范化的关系,满足1,NF,的关系中所有非主属性都依赖于码,第一范式,将非规范化关系转换成1,NF：,P131,工资(,工程号,，工程名称，,职工号,，姓名，职务，小时工资率，工时),工程号,工程名称,职工号,姓名,职务,小时工资率,工时,A1,花园大厦,1001,齐,光明,工程师,65,13,A1,花园大厦,1002,李思岐,技术员,60,16,A1,花园大厦,1004,葛宇洪,律师,60,19,A2,立交桥,1001,齐,光明,工程师,65,15,A2,立交桥,1003,鞠,明亮,工人,55,17,A3,临江,饭店,1002,李思岐,技术员,60,18,A3,临江,饭店,1004,葛宇洪,律师,60,14,第一范式,FD,集,工程号,工程名称,职工号,姓名，职务，小时工资率,工程号，职工号,工程名称，姓名，职务，小时工资率，工时,工程号,工程名称,职工号,姓名 职务 小时工资率 工时,第一范式,分析,满足1,NF,的关系中可能存在大量数据冗余，将导致数据异常(修改、插入、删除)和数据不一致性,产生上述问题的原因为关系中存在部分函数依赖,第二范式,定义,：如果关系模式,R1NF，,且,每个非主属性完全,函数依赖于候选码，则称,R,为第二范式的模式，即,R2NF,算法,：将关系模式,R,分解成2,NF,模式集,设有关系模式,R(U)，,主码是,W，R,上还存在,FDXZ，,其中,Z,是非主属性，,X,W，,则,W,Z,就是一个局部函数依赖，此时应把,R,分解成两个模式,R1(X,Z)：,主码是,X,R2(Y)：,其中,Y=U-Z，,主码仍是,W，,外码是,X(,参考,R1),如果,R1,和,R2,还不是2,NF，,则重复上述过程，直到数据库模式中每一个关系模式都是2,NF,为止,第二范式,将1,NF,转换成2,NF：,P133,工资(,工程号,，工程名称，,职工号,，姓名，职务，小时工资率，工时)1,NF,工程(,工程号,，工程名称)2,NF,职工(,职工号,，姓名，职务，小时工资率)2,NF,报酬(,工程号,，,职工号,，工时)2,NF,工程号 工程名称,工程号 职工号 工时,职工号 姓名 职务 小时工资率,第二范式,第二范式的关系可能还会存在数据冗余现象，可能引起修改异常、插入异常和删除异常,工程号,工程名称,A1,花园大厦,A2,立交桥,A3,临江,饭店,职工号,姓名,职务,小时工资率,1001,齐,光明,工程师,65,1002,李思岐,技术员,60,1003,鞠,明亮,工人,55,1004,葛宇洪,律师,60,工程号,职工号,工时,A1,1001,13,A1,1002,16,A1,1004,19,A2,1001,15,A2,1003,17,A3,1002,18,A3,1004,14,工程师,65,造成上述数据异常的原因在于存在传递函数依赖,第三范式,定义,：如果关系模式,R1NF，,且,每个非主属性,都不,传递,函数依赖于候选码，则称,R,为第三范式的模式，即,R3NF,定理,：如果,R,是3,NF,模式，那么,R,也是2,NF,算法,：将关系模式,R,分解成3,NF,模式集,设有关系模式,R(U)，,主码是,W，R,上还存在,FDXZ，,其中,Z,是非主属性，,Z,X,且,X,不是候选码，,则,W,Z,就是一个传递函数依赖，此时应把,R,分解成两个模式,R1(X,Z)：,主码是,X,R2(Y)：,其中,Y=U-Z，,主码仍是,W，,外码是,X(,参考,R1),如果,R1,和,R2,还不是3,NF，,则重复上述过程，直到数据库模式中每一个关系模式都是3,NF,为止,第三范式,将1,NF,转换成3,NF,工资(,工程号,，工程名称，,职工号,，姓名，职务，小时工资率，工时)1,NF,先转换成2,NF,工程(,工程号,，工程名称)2,NF,职工(,职工号,，姓名，职务，小时工资率),2,NF,报酬(,工程号,，,职工号,，工时)2,NF,再将不属于3,NF,的关系模式转换成3,NF,级别(,职务,，小时工资率)3,NF,职工(,职工号,，姓名，职务)3,NF,3,NF,3,NF,第三范式,在,绝大多数情况下，一个数据库的所有关系都满足3,NF，,就基本达到数据库设计的目标,在实际应用中，有时为了系统的或其它需要，可能会,保留一定的数据冗余,，但一定要估计到数据冗余可能产生的后果，权衡利弊,级别(,职务代码,，职务，小时工资率),职工(,职工号,，姓名，,职务代码,),BC,范式,一个汽车公司的零件配送例子,每个配送中心有多名职工，且每名职工只能在一个中心工作,每个配送中心有多种零件，且每种零件在任意配送中心都能买到,每个配送中心的职工按照零件的分类实施管理，在一个配送中心内部，一个职工管理着多种零件，而每一种零件只能由一个职工管理,BC,范式,公司零件汇总表,配送,中心,零件号,职工号,存货数量,存货状态,A,BY12123,10347,220,积压,B,BY12123,20321,100,适当,A,LY43251,10546,12,不足,B,LY43251,20109,44,积压,A,BV12120,10347,20,积压,B,BV12120,20321,10,适当,配,送(配送中心，零件号，职工号，存货数量，存货状态),候选码,主,属性,：,配送中心，零件号，职工号,BC,范式,配送,关系的函数依赖图,配送中心零件号职工号存货数量存货状态,配送,关系3,NF,主,属性对候选码的传递依赖,定义,：如果关系模式,R1NF，,且,每个属性,都不,传递,函数依赖于候选码，则称,R,为,BC,范式的模式，即,RBCNF,BC,范式,算法,：将关系模式,R,分解成,BCNF,模式集,设,R(U),BCNF，,则,R,上一定存在非平凡函数依赖,XZ，,其中,X,不包含,码,。此时应把,R,分解成两个模式：,R1(X，Z)，,主码为,X,R2(Y)，Y=U-Z，,主码与,R,相同,，,外码是,X(,参考,R1),如果,R1,和,R2,还不是,BCNF，,则重复上述过程，直到数据库模式中每一个关系模式都是,BCNF,为止,将配送关系转换成,BCNF,职工(,职工号,，配送中心),零件(,职工号，零件号,，存货数量，存货状态),规范化与数据库设计,E-R,模型方法主要从宏观角度解决一个企业内部所有数据需求和数据联系的设计问题,当将,E-R,模型转换成关系数据模型后，为进一步提高数据库应用系统的性能，还应该根据应用需要,适当地,进行优化,规范化方法则是从微观角度研究某个关系模式的性质，但,并不是规范化程度越高的关系就越优，,一般规范到3,NF,即可,规范化与数据库设计,零件配送的,E-R,图,配送,中心,职工,零件,聘用,1,n,配送,m,n,管理,m,n,配送,m,n,冗余联系,关系模型低效的主要是连接运算引起的，当查询经常涉及到两个或多个关系模式的属性时，可以考虑将这几个关系合并成一个关系。,多值,依赖,定义,：在关系,R,中有属性,A、B,和,C，,若对于,A,的一个给定值，存在,B,的一组值与其对应，而,B,的这一组值又不以任何方式与,C,的任何值相关，则称,B,多值依赖,于,A(,或,A,决定,B,的多个值)，记为,AB,例：教师(教工号，课程名称，学术团体),教工号,课程名称,学术团体,10075,高等数学,数学研究,10075,高等数学,高教研究,10075,运筹学,数学研究,10075,运筹学,高教研究,10034,英语,外语研究,10034,英语,高教研究,教工号,课程名称,教工号,学术团体,3,NF,BCNF,问题,：插入异常、删除异常、更新异常,第四范式,定义,：如果关系,RBCNF，,且不存在多值依赖，则,R,属于第四范式，记为,R4NF,将教师关系分解成4,NF,模式集,教学(,教工号，课程名称,),学术(,教工号，学术团体,),规范化小结,各种范式之间的关系,1NF,2NF,3NF,BCNF,4,NF,各种范,式的,规范化过程,1,NF,2,NF,3,NF,BCNF,4,NF,消除决定因素非码的非平凡函数依赖,消除非主属性对码的部分函数依赖,消除非主属性对码的传递函数依赖,消除主属性对码的部分和传递函数依赖,消除非平凡且非函数依赖的多值依赖,