第4章关系数据模式的规范化理论

第第4章章 关系数据模式关系数据模式的规范化理论的规范化理论4.1 4.1 问题的提出问题的提出4.2 4.2 函数依赖函数依赖4.3 4.3 范式和规范化范式和规范化2什么是数据库设计?怎么设计？现实世界数据/关系机器世界4.1 问题的提出问题的提出 3是不是从信息世界的模型中简单地转化为机器世界的数据就可以了呢?实体关系表属性数据项4.1 问题的提出问题的提出 44.1 问题的提出问题的提出如果要设计一个教学管理数据库，希望从数据库中得到学生学号(sno)、学生姓名(name)、性别(sex)、学生学习的课程号(cno)、课程名(cname)和该门课程的成绩(grade)。如何设计该关系模式？主码是什么?(学号,姓名,性别,课程号,课程名,成绩)(SNO,NAME,SEX,CNO,CNAME,GRADE)54.1 问题的提出问题的提出问题1：数据冗余SNONAMESEXCNOCNAME GRADES0102王华男C108C语言84S0102王华男C206数据库92S0108李丽女C206数据库86S0108李丽女C207数学8664.1 问题的提出问题的提出问题2：不一致性SNONAMESEXCNOCNAME GRADES0102王华男C108C语言84S0102张三男C206数据库92S0108李丽女C206数据库86S0108李丽女C207数学8674.1 问题的提出问题的提出问题3：插入异常SNONAMESEX CNOCNAMEGRADES0102王华男C108C语言84S0102王华男C206数据库92S0108李丽女C206数据库86S0108李丽女C207数学86（S0010，李四，男，null，null，null）84.1 问题的提出问题的提出问题4：删除异常（当某学号只有一条记录，并做删除操作时）SNONAME SEXCNOCNAMEGRADES0102 王华男C108C语言84S0103 张三男C206数据库92S0108 李丽女C206数据库86S0108 李丽女C207数学8694.1 问题的提出问题的提出解决方案S1（SNO，NAME，SEX）S2（CNO，CNAME）S3（SNO，CNO，GRADE）104.2 函数依赖函数依赖定义1设R(U)是属性集U上的关系模式，X，Y是U的子集。若对于R(U)任意一个可能的关系r，r中不可能存在两个元组在X上的属性值相等，而在Y上的属性值不等，则称X函数确定Y或Y函数依赖于X，记作X-Y114.2 函数依赖函数依赖例子职工号-姓名S1:SNO-NAME;SNO-SEXS2:(SNO,CNO)-GRADES3:CNO-CNAME124.2 函数依赖函数依赖定义2 设X-Y是一个函数依赖，若 则称X-Y是一个平凡函数依赖。YXYX 设X-Y是一个函数依赖，若 则称X-Y是一个非平凡函数依赖。134.2 函数依赖函数依赖 例子 在S2中有 (SNO,CNO)-SNO (SNO,CNO)-CNO 所以这些都是平凡函数依赖关系 (SNO,CNO)-GRADE 这个是非平凡函数依赖关系144.2 函数依赖函数依赖定义3设X-Y是一个函数依赖，并且对于任何则称XY是一个完全函数依赖。即Y函数依赖于整个X，记,XX XY都不成立（记为XY）fXY154.2 函数依赖函数依赖举例：在关系S(SNO,NAME,SEX,CNO,CNAME,GRADE)中：(SNO,CNO)-GRADE但SNO-GRADE；(CNO)-GRADE都不成立所以(SNO,CNO)-GRADE是完全函数依赖关系。164.2 函数依赖函数依赖定义4设X-Y是一个函数依赖,但不是完全函数依赖,则称X-Y是一个部分函数依赖,或称Y函数依赖于X的某个真子集,记pXY174.2 函数依赖函数依赖在关系S(SNO,NAME,SEX,CNO,CNAME,GRADE)中：(SNO,CNO)-NAME,而对于每个学生都有唯一的SNO值,所以SNO-NAME,而CNO-NAME,因此，(SNO,CNO)-NAME是部分函数依赖(SNO,CNO)NAMEp184.2 函数依赖函数依赖设R(U)是一个关系模式,则称Z传递函数依赖于X,记,X Y ZUXYX Y如果（YX）,YZ成立tXZ194.2 函数依赖函数依赖例子：班级(班号,专业名,系名,人数,入学年份)班号-专业名,专业名-系名,班号-人数,班号-入学年份班号-专业名,专业名-系名t班级号系名204.2 函数依赖函数依赖函数依赖与属性关系 属性之间有3种关系,但并不是每一种关系中都存在函数依赖。21函数依赖与属性关系函数依赖与属性关系设R(U)是属性集U上的关系模式,X,Y是U的子集:若X和Y之间是1:1关系,则存在函数依赖 X-Y,Y-X;若X和Y之间是1:n关系,则存在函数依赖 X-Y;若X和Y之间是m:n关系,则X,Y间不存在函数依赖.22函数依赖与属性关系函数依赖与属性关系分析下列关系中各种函数的依赖关系:学生(学号,姓名,出生年月,系名,班号,宿舍区)23Armstrong公理公理背景为了从一组函数依赖中求得逻辑蕴涵的函数依赖,例如已知函数依赖集F,要问是否逻辑蕴涵X-Y,就需要一套推理规则.24Armstrong公理公理设A,B,C,D是给定关系模式R的属性集的任意子集,并把A和B的并集称为AB,则其推理规则可归结为3条:自反律:如果,BAAB则这是一个平凡函数依赖增广律:如果ABACBC，则传递律:如果ABAC，且BC 则25Armstrong公理公理推论推论自合规则:A-A分解规则:A-BC,则A-B且 A-C合并规则:A-B且 A-C,则A-BC复合规则:A-B且 C-D成立,则AC-BD264.2 函数依赖函数依赖4.2.6 4.2.6 闭包及其计算闭包及其计算定义定义6 6：设：设F F是关系模式是关系模式R R的一个函数依赖集，的一个函数依赖集，X X，Y Y是是R R的属的属性子集，如果从性子集，如果从F F中的函数依赖能够推出中的函数依赖能够推出XYXY，则称，则称F F逻辑逻辑蕴涵蕴涵XYXY。定义定义7 7：被：被F F逻辑蕴涵的函数依赖的全体构成的集合，称为逻辑蕴涵的函数依赖的全体构成的集合，称为F F的闭包，记为的闭包，记为F F+。练习练习1 1：设有关系模式：设有关系模式R(A,B,C,D,E)R(A,B,C,D,E)，F=AC,BC E,D C,E AF=AC,BC E,D C,E A，试求，试求F F的闭包的闭包F F+。274.2 函数依赖函数依赖定义定义8 8：设：设F F是属性集是属性集U U上的一组函数依赖，上的一组函数依赖，X UX U，则属性，则属性集集X X关于关于F F的闭包的闭包X X+定义为定义为X X+=A|AU=A|AU且且XAXA在在F F+中中，即，即X=A|XAFX=A|XAF+。算法算法4.1 result=X do if F中有某个函数依赖中有某个函数依赖YZ满足满足Y result then result=result Z while(result有所改变有所改变);28XF+算法算法:设设i=0 令令 X(0)=X 计算计算 A=B|一切一切W X(i)且且 W B F+令令 X(i+1)=X(i)A 判断判断 X(i+1)=X(i)是否成立，是否成立，成立，转，成立，转，不成立，不成立，i=i+1，转，转 算法结束，算法结束，XF+=X(i)4.2 函数依赖函数依赖29随堂练习随堂练习练习练习1 1：设有关系模式：设有关系模式R(A,B,C,D,E)R(A,B,C,D,E)，F=AB,C E,D ACF=AB,C E,D AC，试求，试求D D关于关于F F的闭包。的闭包。练习练习2 2：设有关系模式：设有关系模式R(A,B,C,D,E)R(A,B,C,D,E)，F=ABC,BD,CE,EC,ACF=ABC,BD,CE,EC,AC，试求，试求BCBC关于关于F F的闭包的闭包(BC)(BC)F F+。304.2 函数依赖函数依赖关键码的定义 如果XU在R上成立（即XU在F+中），那么称X是R的一个超键。如果XU在R上成立，但对X的任一真子集X都有XU不成立（即XU不在F+中，或者X U），那么称X是R上的一个候选键。快速求解候选键的一个充分条件 对于给定的关系模式R(A1，An)和函数依赖集F，可将其属性分为以下四类：fL类类R类类 N类类 LR类类 31定理4.4 对于给定的关系模式R及其函数依赖集F（1）若X（XR）是L类属性，则X必为R的任一候选键的成员。（2）若X（XR）是L类属性，且X+包含了R的全部属性，则X必为R的惟一候选键。（3）若X（XR）是R类属性，则X不在任何候选键中。（4）若X（XR）是N类属性，则X包含在R的任一候选键中。（5）若X（XR）是R的N类和L类属性组成的属性集，且X+包含了R的全部属性，则X是R的惟一候选键。32随堂练习随堂练习练习练习1 1：设有关系模式：设有关系模式R(A,B,C,D,E)R(A,B,C,D,E)，F=AB,CG,EAF=AB,CG,EA，CE DCE D，求，求R R的所有候选键。的所有候选键。练习练习2 2：设有关系模式：设有关系模式R(A,B,C,D,E,P)R(A,B,C,D,E,P)，F=AD,ED,DB,BCD,DCAF=AD,ED,DB,BCD,DCA，求，求R R的所有候选键。的所有候选键。练习练习3 3：设有关系模式：设有关系模式R(A,B,C,D,E)R(A,B,C,D,E)，F=ABC,CDE,BD,EAF=ABC,CDE,BD,EA，求，求R R的所有候选键。的所有候选键。33多属性函数依赖集候选键的求解算法（1）属性分类（L、R、N和LR）（2）若X+包含了R的全部属性，转（5）；否则，转（3）。（3）在Y中取一个属性A，求(XA)+，若它包含了R的全部属性，则转（4）；否则，调换一属性反复进行这一过程，直到试完所有Y中的属性。（4）如果已找出所有候选键，则转（5）；否则在Y中依次取两个、三个、，求它们的属性集的闭包，直到其闭包包含R的全部属性。（5）停止，输出结果。令令X代表代表L和和N类，类，Y代表代表LR类类34随堂练习随堂练习关系模式关系模式R(A,B,C,D,E,F)，其函数依赖集其函数依赖集F=ABE,ACF,ADB,BC,CD，试求试求R的所有候选键。的所有候选键。354.4 范式和规范化范式和规范化 要想设计一个好的关系,必须使关系满足一定的约束条件,此约束条件已经形成了规范,分成几个等级,一级比一级要求得严格。364.4 范式和规范化范式和规范化 满足最低要求的关系称为第一范式,在此基础上又满足某条件,达到第二范式,如此类推,直到第五范式.374.4 范式和规范化范式和规范化 一个较低范式关系,可以通过关系的无损分解转换为若干较高范式关系的集合,这一过程叫做关系规范化关系规范化.384.4 范式和规范化范式和规范化 一般情况下,第一范式和第二范式的关系存在许多缺点,实际的关系数据库一般使用第三范式以上的关系.394.4.1 范式范式范式是符合某一种级别的关系模式的集合。关系数据库中的关系必须满足一定的要求。满足不同程度要求的为不同范式。范式的种类：第一范式(1NF)第二范式(2NF)第三范式(3NF)BC范式(BCNF)第四范式(4NF)第五范式(5NF)40范式（续）范式（续）各种范式之间存在联系：某一关系模式R为第n范式，可简记为RnNF。NF5NF4BCNFNF3NF2NF1414.4 范式和规范化范式和规范化 n候选码定义 设K为关系模式R(U，F)中的属性或属性组合，若K U，则k称为R的一个候选码。若关系模式R中有多个候选码，则选定其中一个作为主码。组成候选码的属性称为主属性，不参加任何候选码的属性成为非主属性。f42第一范式第一范式4.4.2 4.4.2 范式的判定条件与规范化范式的判定条件与规范化1.1.第一范式（第一范式（1NF1NF）定义：设定义：设R R是一个关系模式，是一个关系模式，R R属于第一范式当且仅当属于第一范式当且仅当R R中中每一个属性每一个属性A A的值域只包含原子项，即不可分割的数据项的值域只包含原子项，即不可分割的数据项。43第一范式（续）第一范式（续）例:关系模式 SLC(Sno,Sdept,Sloc,Cno,Grade)Sloc为学生住处，假设每个系的学生住在同一个地方。函数依赖包括：(Sno,Cno)f Grade Sno Sdept (Sno,Cno)P Sdept Sno Sloc (Sno,Cno)P Sloc Sdept Sloc44第一范式（续）第一范式（续）SLC的码为(Sno,Cno)SnoCnoGradeSdeptSlocSLC45第一范式（续）第一范式（续）结论:1.SLC满足第一范式。2.非主属性Sdept和Sloc部分函数依赖于码(Sno,Cno)。snocnosdeptslocgrade46第一范式（续）第一范式（续）第一范式存在的问题第一范式存在的问题n插入异常：若插入一个学生，但未选课，这样的元祖不能插入。n删除异常：如果某个学生只选修了一门课，如果他不选该课程了，则在删除课程号的同时会把该学生也一起删除。n数据冗余大：如果一个学生选修了10门课程，则他的DEPT和SLOC值就要重复存储10次。n修改复杂：如果要修改DEPT和SLOC的值，则必须修改10次。47第一范式（续）第一范式（续）原因 Sdept、Sloc部分函数依赖于码。解决方法 采用投影分解法，把SLC分解为两个关系模式，以消除这些部分函数依赖。SC（Sno，Cno，Grade）SL（Sno，Sdept，Sloc）48第一范式（续）第一范式（续）SnoCnoGradeSdeptSlocSLC49第一范式（续）第一范式（续）函数依赖图：SnoCnoGradeSCSLSnoSdeptSloc50第二范式第二范式2.2.第二范式（第二范式（2NF2NF）定义：设定义：设R R是一个关系模式，是一个关系模式，R R属于第二范式当且仅当属于第二范式当且仅当R R是是1NF1NF，且每个非主属性都完全函数依赖于主码。，且每个非主属性都完全函数依赖于主码。51 第二范式（续）第二范式（续）例：2NF关系模式SL(Sno,Sdept,Sloc)中函数依赖：SnoSdept SdeptSloc SnoSlocSLSnoSdeptSlocSloc传递函数依赖于传递函数依赖于Sno，即，即SL中存在非中存在非主属性对码的传递函数依赖。主属性对码的传递函数依赖。52第二范式（续）第二范式（续）第二范式存在的问题第二范式存在的问题n删除异常：如果某个系的学生全部毕业了，在删除该系学生信息的同时，把这个系的信息也丢掉了。n数据冗余大：每一个系的学生都住在同一个地方，系的住处重复了多次。n修改复杂：当学校调整学生住处，如信息系的学生全部迁到另一地方，修改时必须同时修改该系的所有学生的SLOC属性值。n原因是还存在原因是还存在sloc对对sno的传递函数依赖的传递函数依赖53 第二范式（续）第二范式（续）原因Sloc传递函数依赖于Sno解决方法 采用投影分解法，把SL分解为两个关系模式，以消除传递函数依赖：SD（Sno，Sdept）DL（Sdept，Sloc）SD的码为Sno，DL的码为Sdept。SLSnoSdeptSloc54 第二范式（续）第二范式（续）SD的码为Sno，DL的码为Sdept。SnoSdeptSDSdeptSlocDL55第三范式第三范式3.3.第三范式（第三范式（3NF3NF）定义：设定义：设R R是一个关系模式，是一个关系模式，R R属于第三范式当且仅当属于第三范式当且仅当R R是是2NF2NF，且每个非主属性都非传递函数依赖于主码。，且每个非主属性都非传递函数依赖于主码。56第三范式（续）第三范式（续）例如：模型例如：模型SC(SNO,SNAME,CNO,GRADE)SC(SNO,SNAME,CNO,GRADE)模型的候选码是：模型的候选码是：(SNO,CNO)(SNO,CNO)、(SNAME,CNO)(SNAME,CNO)非主属性只有非主属性只有GRADEGRADE，对这两个候选码都是完全函数，对这两个候选码都是完全函数依赖，因此依赖，因此SC3NFSC3NF。存在的问题存在的问题n删除异常：当学生退选了课程，元组被删除也失去学生学号和姓名的对应关系。n数据冗余：学生选课多时，姓名会被重复存储。57BC范式范式4.BC4.BC范式（范式（BCNFBCNF）定义：对于关系模式定义：对于关系模式R R，对任何非平凡的函数依赖，对任何非平凡的函数依赖X X Y Y，X X均包含码，则均包含码，则R R属于属于BCNFBCNF。58BC范式（续）范式（续）例：假设仓库管理关系表为例：假设仓库管理关系表为StorehouseManage(StorehouseManage(仓库仓库ID,ID,存储存储物品物品ID,ID,管理员管理员ID,ID,数量数量)，且有一个管理员只在一个仓库工，且有一个管理员只在一个仓库工作；一个仓库可以存储多种物品。这个数据库表中存在如下决作；一个仓库可以存储多种物品。这个数据库表中存在如下决定关系：定关系：(仓库仓库ID,ID,存储物品存储物品ID)(ID)(管理员管理员ID,ID,数量数量)(管理员管理员ID,ID,存储物品存储物品ID)(ID)(仓库仓库ID,ID,数量数量)候选键：候选键：(仓库仓库ID,ID,存储物品存储物品ID)ID)、(管理员管理员ID,ID,存储物品存储物品ID)ID)59BC范式（续）范式（续）它会出现如下异常情况：它会出现如下异常情况：(1)(1)删除异常：删除异常：当仓库被清空后，所有当仓库被清空后，所有 存储物品存储物品IDID和和 数量数量 信息被删除的同信息被删除的同时，时，仓库仓库IDID和和 管理员管理员IDID信息也被删除了。信息也被删除了。(2)(2)插入异常：插入异常：当仓库没有存储任何物品时，无法给仓库分配管理员。当仓库没有存储任何物品时，无法给仓库分配管理员。(3)(3)更新异常：更新异常：如果仓库换了管理员，则表中所有行的管理员如果仓库换了管理员，则表中所有行的管理员IDID都要修改。都要修改。60BC范式（续）范式（续）由于存在如下决定关系：由于存在如下决定关系：(仓库仓库ID)(ID)(管理员管理员ID)ID)(管理员管理员ID)(ID)(仓库仓库ID)ID)即存在关键字段决定关键字段的情况，所以其不符合即存在关键字段决定关键字段的情况，所以其不符合BCNFBCNF范式。范式。u 把仓库管理关系表分解为二个关系表：把仓库管理关系表分解为二个关系表：仓库管理：仓库管理：StorehouseManage(StorehouseManage(仓库仓库ID,ID,管理员管理员ID)ID)；仓库：仓库：Storehouse(Storehouse(仓库仓库ID,ID,存储物品存储物品ID,ID,数量数量)。61规范化步骤规范化步骤 消除非主属性对码的部分函数依赖消除非主属性对码的部分函数依赖BCNFBCNF2NF2NF3NF3NF1NF1NF消除非主属性对码的传递函数依赖消除非主属性对码的传递函数依赖消除每一属性对码的部分和传递函数依赖消除每一属性对码的部分和传递函数依赖62随堂练习随堂练习（1 1）在关系模式）在关系模式R(U,F)R(U,F)中，如果中，如果X YX Y，存在，存在X X的真子集的真子集X X1 1，使使X X1 1 Y Y，称函数依赖，称函数依赖X YX Y为（为（）A.A.平凡函数依赖平凡函数依赖 B.B.部分函数依赖部分函数依赖C.C.完全函数依赖完全函数依赖D.D.传递函数依赖传递函数依赖（2 2）在关系模式）在关系模式R(U,F)R(U,F)中，对任何非平凡的函数依赖中，对任何非平凡的函数依赖X X YY，X X均包含键，则均包含键，则R R最高可以达到（最高可以达到（）A.2NFA.2NF B.3NFB.3NFC.BCNFC.BCNFD.4NFD.4NF63随堂练习随堂练习（3 3）已知：关系模式）已知：关系模式R(U,F),U=ABCDEG,R(U,F),U=ABCDEG,F=A B,C G,E A,CE D F=A B,C G,E A,CE D 求：（求：（1 1）R R的候选码的候选码 （2 2）R R最高属于哪级范式最高属于哪级范式（4 4）已知：关系模式）已知：关系模式R(U,F),U=ABCDE,R(U,F),U=ABCDE,F=A BC,CD E,E A,B D F=A BC,CD E,E A,B D 求：（求：（1 1）R R的候选码的候选码 （2 2）R R最高属于哪级范式最高属于哪级范式