第五章 关系数据库设计理论

3-1第五章关系数据库设计理论3-2关系数据理论的意义意义：提供分析和判断数据库设计好坏的准则；指导设计好的数据库模式。对于数据库设计十分有用3-35.1问题的提出－什么是不好的数据库设计以下关系模式在实际应用中会出现什么样的问题？Student(Sno,Sname,Dept，Mname，Cno,Grade)学生（学号，姓名，院系，系主任，课程号，成绩）SnoSnameDeptMnameCnoGrade1000李平计算机张明001861000李平计算机张明003831001王莉计算机张明001801001王莉计算机张明00275数据存在冗余，插入异常，删除异常，更新异常3-45.1问题的提出－什么是不好的数据库设计结论：•Student关系模式不是一个好的模式。•“好”的模式：不会发生插入异常、删除异常、更新异常，数据冗余应尽可能少。原因：由存在于模式中的某些数据依赖引起的解决方法：通过分解关系模式来消除其中不合适的数据依赖将上述的Student关系模式进行分解：Student(Sno,Sname，Dept)SC(Sno,Cno,Grade)Dept(Dept，Mname)3-55.2相关内容复习•关系：在关系数据库，关系以二维表的形式存储。•关系的特征:–行包含一个实体的数据–列包含实体属性的数据–每一列都有唯一的名字–表的每个单元保存单个值–列是无序的–行是无序的–没有两行是相同的3-65.2相关内容复习•候选键：能唯一标识元组的属性（组）•组合键：包含两列或多列的键•主键：选择某个候选键作为主键•代理键：在表中加入一列IDENTITY作为代理键RENTAL_PROPERTY(Street,City,Province,PostalCode,Country,Rental_Rate)RENTAL_PROPERTY(PropertyID,Street,City,Province,PostalCode,Country,Rental_Rate)•外键：关系中某属性（组）不是本关系的主键，是其他关系的主键。3-75.3函数依赖•函数•y=f(x)•u=f(m,n)•函数依赖•当一个或多个属性决定其他属性的值时，就称为存在函数依赖StudentNoStudentNameStudentNo(DormName,DormRoom,Fee)(StudentNo,ClassNo)(Grade)•左边的属性称为决定因素，决定因素由多个属性组成，称为复合决定因素3-85.3函数依赖•函数依赖的规则若A(B,C),则AB，AC若(A,B)C,则不能单独有AC，BC例子：(StudentNo,ClassNo)(Grade)StudentNoGrade不成立ClassNoGrade不成立(StudentNo,Age)(Dept)StudentNoDept成立完全函数依赖部分函数依赖3-95.3函数依赖•识别函数依赖SKU(SKU_Description,Department,Buyer)SKU_Description(SKU,Department,Buyer)BuyerDepartment3-105.3函数依赖•识别函数依赖(OrderNumber,SKU)(Quantity,Price,ExtendedPrice)(Quantity,Price)(ExtendedPrice)3-115.4范式•范式的历史•关系模型之父Codd定义了第一、二、三范式，表示为1NF，2NF，3NF•满足关系条件的表定义为第一范式，进一步满足一些条件限制的为第二范式，再进一步满足一些条件的为第三范式•之后，Boyce和Codd共同定义了BCNF•2NF到BCNF主要考虑函数依赖引起的异常•后来，研究者发现其它的异常源，进而定义4NF和5NF•各种范式之间的关系：1NF2NF3NFBCNF4NF5NF3-125.4范式•第一范式1NF•满足关系条件的表都是第一范式关系的特征:行包含一个实体的数据列包含实体属性的数据每一列都有唯一的名字表的每个单元保存单个值列是无序的行是无序的没有两行是相同的3-135.4范式•第一范式1NF•满足关系条件的表都是第一范式SnoSnameDeptMnameCnoGrade1000李平计算机张明001861000李平计算机张明003831001王莉计算机张明001801001王莉计算机张明002753-145.4范式•第二范式2NF•若R是1NF，且R的每一个非主属性完全函数依赖于候选键，称R是满足2NF的关系模式(StudentNo,ClassNo)(Grade)StudentNoGrade不成立ClassNoGrade不成立(StudentNo,Age)(Dept)StudentNoDept成立完全函数依赖部分函数依赖3-155.4范式•第二范式2NF•若R是1NF，且R的每一个非主属性完全函数依赖于候选键，称R是满足2NF的关系模式•单属性主键的关系都是2NF例：设有关系模式SDC(Sno,Dept,Dorm,Cno,Grade)其中Dorm为宿舍楼，并规定一个系的同学住在同一栋楼。GradeCno)(Sno,DormDeptDorm,SnoDept,Sno函数依赖？是否满足2NF？3-165.4范式•第二范式2NF•处理方法：将存在部分函数依赖的列移出，作为一个单独的关系，留下决定因素作为原关系的外键•仍存在：数据冗余、插入异常、删除异常、更新异常例：设有关系模式SDC(Sno,Dept,Dorm,Cno,Grade)其中Dorm为宿舍楼，并规定一个系的同学住在同一栋楼。GradeCno)(Sno,DormDeptDorm,SnoDept,SnoSC(Sno,Cno,Grade)SD(Sno,Dept,Dorm)是否存在异常？3-175.4范式•第三范式3NF•当且仅当一个2NF不存在非主属性由其他非主属性决定的条件下，才满足3NF例：关系模式SD(Sno,Dept,Dorm)DormDeptDept,Sno传递函数依赖3-185.4范式•第三范式3NF•处理方法：将传递函数依赖的列移出，作为一个单独的关系，留下决定因素作为原关系的外键例：关系模式SD(Sno,Dept,Dorm)SD(Sno,Dept)DD(Dept,Dorm)DormDeptDept,Sno传递函数依赖是否存在异常？3-195.4范式•1NF，2NF，3NF小结•1NF–满足关系特征•2NF–所有非主属性完全函数依赖于整个键•3NF–非主属性除了键之外不依赖于其他非主属性allnonkeycolumnsaredependentonthekey[1NF],thewholekey[2NF]andnothingbutthekey[3NF]3-205.4范式•BCNF范式关系模式Student_Advisor(Student,Advisor,Major)Student表示学生，Advisor表示导师，Major表示主修课程。假设每一个导师只教一门课程。但是每门课程由若干导师教授，学生可以选修一门或多门课程，学生如果选定一门课程就由一个固定的导师教授。•当且仅当关系R是3NF并且每一个决定因素都是候选键(Student,Advisor)Major(Student,Major)AdvisorAdvisorMajor满足第几范式？是否存在异常？3-215.4范式•BCNF范式•处理方法：将不是候选键的决定因素的函数依赖关系移出，留下决定因素作为外键。Student_Advisor(Student,Advisor,Major)Student_Advisor(Student,Advisor)Advisor_Major(Advisor,Major)(Student,Advisor)Major(Student,Major)AdvisorAdvisorMajor3-225.4范式•消除因函数依赖引起的异常•步骤（1）识别每一个函数依赖（2）识别每一个候选关键字（3）如果存在某个决定因素不是候选关键字A.将这个函数依赖中所有的列移入一个新的关系中B.让决定因素成为新关系的主关键字C.将决定因素保留在原关系中使其成为外键D.在新关系和原关系之间创建参照完整性约束（4）重复第（3）步，直到每一个决定因素都是候选关键字3-235.4范式•消除因函数依赖引起的异常SKU_DATA(SKU,SKU_Description,Department,Buyer)SKU(SKU_Description,Department,Buyer)SKU_Description(SKU,Department,Buyer)BuyerDepartmentSKU_DATA(SKU,SKU_Description,Buyer)BUYER(Buyer,Department)3-245.4范式•消除因函数依赖引起的异常ORDER_ITEM(OrderNumber,SKU,Quantity,Price,ExtendedPrice)(OrderNumber,SKU)(Quantity,Price,ExtendedPrice)(Quantity,Price)(ExtendedPrice)ORDER_ITEM(OrderNumber,SKU,Quantity,Price)3-255.4范式•识别候选关键字•Armstrong公理系统设有关系模式R（U），X、Y、Z是U的子集：A1(自反性)：如果，则有在R上成立。A2(增广性)：如果在R上成立，那么有A3(传递性)：如果在R上成立，则有XYYXYXYZXZZY和YXZX3-265.4范式•识别候选关键字•Armstrong的推论：1、合并规则：2、分解规则：3、伪传递规则：YZZ可以得到XXY,由XZXY,YZ可以得到X由XZZ则得到XWYWY,由X3-275.4范式•识别候选关键字•属性集的闭包定义：设F是属性集U上的一组函数依赖，X是U的子集，那么属性集X的闭包X+，它是一个从函数依赖集F使用函数依赖推导规则推出的所有满足X－A的属性集A的集合：}AX|{属性集AX公理导出根据能由ArmstrongF3-285.4范式•识别候选关键字•算法：求属性集X关于函数依赖集F的闭包X+输入：属性集U，U上的函数依赖集F，X是U的子集输出：X关于F的闭包X＋方法：Result:=X;repeatforF中的每个函数依赖Y－ZdoifthenResult:=ResultUZ;until(result没有改变)；Result即为所求的X+ResultY3-295.4范式•识别候选关键字例：属性集U为ABCD，FD集为,求A+,AD+A+(1)Result=A(2)Result=AUB=AB(3)Result=ABUC=ABCAD+=ABCDB}DC,BB,{A3-305.4范式•识别候选关键字例题：AG}CEB,ACDBD,CGD,BCC,BEA,CEG,DC,{ABF求BD+BD+=ABCDEG3-315.4范式•识别候选关键字对于给定的关系模式R=(A1,A2,…,An)和函数依赖集F，可将其属性分为四类：1、仅仅出现在F的函数依赖左部的属性L类；2、仅仅出现在F的函数依赖右部的属性R类；3、在F的函数依赖的左右两边都没有出现的属性N类；4、在F的函数依赖的左右两边都出现的属性LR类；3-325.4范式•识别候选关键字对于给定关系模式R及其函数依赖集F1)X是L类属性，则X必为R的任何一个候选键的成员。2)X是R类属性，则X不包含在任何候选键中。3)X是R的N类属性，则X也必为R的任何一个候选键的成员。4)X是R的LR类属性，则X可能是也可能不是候选键的成员。3-335.4范式•识别候选关键字识别过程：（1）若存在L类，求出它的闭包，若其闭包包含所有属性，则说明该L类为候选键，且为唯一候选键。（2）L类的闭包