关系数据库规范化理论.ppt

第7章关系数据库规范化理论,7.1函数依赖7.2关系规范化7.3关系模式分解准则,概述,数据库设计是数据库应用领域中的主要研究课题，其主要任务是创建满足用户需求且性能良好的数据库模式。关系数据库规范化理论是指导数据库设计的一个理论指南。,概述,数据的语义不仅表现为完整性约束，对关系模式的设计也提出了一定的要求。如何构造一个合适的关系模式，应构造几个关系模式，每个关系模式由哪些属性组成等，都是数据库设计问题，确切地讲是关系数据库的逻辑设计问题。,7.1函数依赖,7.1.1函数依赖基本概念7.1.2一些术语和符号7.1.3为什么要讨论函数依赖,7.1.1基本概念,省=f(城市)：只要给出一个具体的城市值，就会有唯一一个省值和它对应，如“武汉市”在“湖北省”，这里“城市”是自变量X，“省”是因变量或函数值Y。把X函数决定Y，或Y函数依赖于X表示为：XY如果有关系模式R(A1,A2,An)，X和Y为A1,A2,An的子集，则对于关系R中的任意一个X值，都只有一个Y值与之对应，则称X函数决定Y，或Y函数依赖于X。,示例,例1：对学生关系模式Student（Sno,Sname,Sdept,Sage）有以下依赖关系：,SnoSname,SnoSdept,SnoSage,例2：SC（Sno,Cno,Grade）,（Sno,Cno）Grade,函数依赖定义,设有关系模式R(A1,A2,An)，X和Y均为A1,A2,An的子集，r是R的任一具体关系，t1、t2是r中的任意两个元组；如果由t1X=t2X可以推导出t1Y=t2Y，则称X函数决定Y，或Y函数依赖于X，记为XY。函数依赖讨论的是关系中属性之间在语义范畴的概念,7.1.2一些术语和符号,（1）如果XY，但Y不包含于X，则称XY是非平凡的函数依赖。（2）如果XY，但Y包含于X，则称XY是平凡的函数依赖。若无特别声明，我们讨论的都是非平凡的函数依赖。（3）如果XY，则X称为决定因子。,术语和符号（续）,（4）如果XY，并且YX，则记作XY。（5）如果XY，并且对于X的一个任意真子集X都有X/Y，则称Y完全函数依赖于X，记作：如果XY成立，则称Y部分函数依赖于X，记作：（6）如果XY（非平凡函数依赖，并且Y/X）、YZ，则称Z传递函数依赖于X。,示例,例1：有关系模式SC（Sno,Sname,Cno,Credit,Grade）主键为（Sno,Cno），则函数依赖关系有：,示例,例2：假设有关系模式S（Sno,Sname,Dept,Dept_master）假设一个系只有一个主任，主键为Sno，则函数依赖关系有：,7.1.3为什么要讨论函数依赖？,2019年12月1日11时36分,12,有关系模式：S-L-C(Sno,Sname,Ssex,Sdept,SLOC,Cno,Grade)其中各属性分别为：学号、姓名、性别、学生所在系、学生所住宿舍楼、课程号和考试成绩。假设每个系的学生都住在一栋楼里，（Sno,Cno）为主键,数据示例,存在问题,数据冗余问题数据更新问题数据插入问题数据删除问题,结论,S-L-C关系模式不是一个好的模式。如何改造这个关系模式并克服以上种种问题是关系规范化理论要解决的问题，也是讨论函数依赖的原因。解决方法:模式分解，即把一个关系模式分解成两个或多个关系模式，在分解的过程中消除那些“不良”的函数依赖，从而获得良好的关系模式,7.2关系规范化,7.2.1关系模式中的码7.2.2范式,7.2.1关系模式中的码,1候选码：设K为R(U,F)中的属性或属性组，若KU，则K为R候选码。（K为决定R全部属性值的最小属性组）。2.主码：关系R(U,F)中可能有多个候选码，则选其中一个作为主码。3.全码：候选码为整个属性组。主属性与非主属性：4.在R(U,F)中，包含在任一候选码中的属性称为主属性，不包含在任一候选码中的属性称为非主属性。,f,示例1,有关系模式：学生（学号，姓名，性别，身份证号，年龄，所在系）学号，身份证号：“学号”或“身份证号”:主属性：非主属性：,候选码,主码,学号，身份证号,姓名，性别，年龄，所在系,示例2,有关系模式：选课（学号，课程号，考试次数，成绩）设一个学生对一门课程可以有多次考试，每一次考试有一个考试成绩。候选码：（学号，课程号，考试次数），也为主码。主属性：学号，课程号，考试次数非主属性：成绩。,示例3,有关系模式：授课（教师号，课程号，学年）语义：一个教师在一个学年可以讲授多门不同的课程，可以在不同学年对同一门课程讲授多次，但不能在同一个学年对同一门课程讲授多次。一门课程在一个学年可以由多个不同的教师讲授，同一个学年可以开设多门课程，同一门课程可以在不同学年开设多次。候选码：（教师号，课程号，学年）主码：同候选码。主属性：教师号，课程号，学年非主属性：无称这种候选码为全部属性的表为全码表,7.2.1关系模式中的码（续）,外码：用于关系表之间建立关联的属性（组）。定义：若R（U，F）的属性（组）X（X属于U）是另一个关系S的主码，则称X为R的外码。（X必须先被定义为S的主码）。,7.2.2范式,关系数据库中的关系要满足一定的要求，满足不同程度要求的为不同的范式。,第一范式,第一范式：不包含重复组的关系（即不包含非原子项的属性）,课堂思考与练习,下面“部门”关系中,因哪个属性而使它不满足第1范式?部门(部门号,部门名,部门成员,部门总经理),第二范式,第二范式:如果R(U,F)1NF，并且R中的每个非主属性都完全函数依赖于主码，则R(U,F)2NF例:有关系模式S-L-C（Sno,Sname,Ssex,Sdept,Sloc,Cno,Grade）其中(Sno,Cno)是主码并且(Sno,Cno)SnameS-L-C不是2NF。,P,分解办法,用组成主码的属性集合的每一个子集作为主码构成一个关系模式。将依赖于这些主码的属性放置到相应的关系模式中。最后去掉只由主码的子集构成的关系模式。,分解示例,对于S-L-C表，首先分解为如下形式的三张表：S-L（Sno，）C（Cno，）S-C（Sno,Cno,）将依赖于这些主码的属性放置到相应的表中:S-L（Sno，Sname,Ssex,Sdept,Sloc）C（Cno）S-C（Sno,Cno,Grade）去掉只由主码的子集构成的表，最终分解为：S-L（Sno,Sname,Ssex,Sdept,Sloc）S-C（Sno,Cno,Grade）,S-L(Sno,Sname,Ssex,Sdept,Sloc)存在问题,数据冗余：有多少个学生就有多少个重复的Sdept和SLOC；插入异常：当新建一个系时，若还没有招收学生，则无法插入；,传递函数依赖,定义在R(U)中，如果XY，(YX),YX,YZ，则称Z对X传递函数依赖。记为：XZ例:在关系Std(Sno,Sname,Ssex,Sdept,Sloc)中，有：SnoSdept，SdeptSlocSloc传递函数依赖于Sno,传递,第三范式,定义：如果R(U,F)2NF，并且所有非主属性都不传递依赖于主码，则R(U,F)3NF。对S-L(Sno,Sname,Ssex,Sdept,SLOC)SnoSLOC,不是3NF,传递,分解过程,对于不是候选码的每个决定因子，从表中删去依赖于它的所有属性；新建一个表，新表中包含在原表中所有依赖于该决定因子的属性；将决定因子作为新表的主码。S-L分解后的关系模式为：S-D（Sno,Sname,Ssex,Sdept），主码为SnoS-L（Sdept,Sloc），主码为Sdept,分析S-D和S-L关系模式,S-D（Sno,Sname,Ssex,Sdept）S-L（Sdept,Sloc）对S-D，有：SnoSname，SnoSsex，SnoSdept，因此S-D是3NF的。对S-L，有：SdeptSloc，因此S-L也是3NF的。,S-L-C最终分解结果,S-D（Sno,Sname,Ssex,Sdept）Sno为主码Sdept为引用S-L关系模式的外码S-L（Sdept,Sloc）Sdept为主码没有外码S-C（Sno,Cno,Grade）（Sno，Cno）为主码Sno为引用S-D关系模式的外码,课堂练习1,设有关系W(工号,姓名,工种,工种定额工资),将其规范化到3NF,正确的答案是().W1(工号,姓名),W2(工种,定额)W1(工号,工种,定额),W2(工号,姓名)W1(工号,姓名,工种),W2(工种,定额)以上都不对,课堂练习2,在关系模式R(A,B,C,D)中，有函数依赖集F=BC，CD，DA，则R能达到。A1NF；B2NF；C3NF；D以上三者都不行。候选码是？,答案：B原因：关系模式R的候选码是B，因为BC，CD，DA,所以存在非主属性D对候选码的传递函数依赖，R不是3NF。又因为不存在非主属性对候选码的部分函数依赖，所以R是2NF。,课堂练习3,若关系R的候选码都是由单属性构成的，则R的最高范式必定是。AINF；B2NF；C3NF；D无法确定。,答案：B原因：候选码由单属性构成，一定不会存在非主属性对候选码部分函数依赖,总结：1、规范化的过程实际是通过把范式程度低的关系模式分解成若干个范式程度高的关系模式来实现的，分解的最终目的是使每个规范化的关系模式只描述一个主题。2、模式分解不能破坏原来的语义，同时还要保证不丢失原来的函数依赖关系。3、通常的数据库设计中一般要达到3NF即可。,BC范式（BCNF）,定义:关系模式R1NF，若XY且YX时X必包含有候选码时，则RBCNF。等价于：每一个函数依赖的决定因子都是候选码。,BCNF定义,若R1NF，且能决定其它属性取值的属性（组）必定包含候选码，则RBCNF。如果一个关系的每个决定因素都是候选码，则其是BCNF。如果一个关系的每个函数依赖的左部都是候选码，则其是BCNF。如果R3NF，并且不存在主属性对非主属性的函数依赖，则其是BCNF。,BCNF（续）,例5关系模式C（Cno，Cname，Credit）C3NFCBCNF例6关系模式S（Sno，Sname，Sdept，Sage）假定Sname也具有唯一性S有两个候选码Sno，SnameS3NF。SBCNF,BCNF（续）,例7关系模式SJP（S，J，P）语义：S是学生，J是课程，P是名次。每一个学生选修每门课程的成绩有一定的名次，每门课程中每一个名次只有一个学生（没有并列）函数依赖：（S，J）P；(J，P）S（S，J）与（J，P）都可以作为候选码,属性相交SJP3NF，SJPBCNF,规范化举例,设有关系模式：Student（学号，姓名，导师号，导师名，课程号，课程说明，成绩）语义：一名学生只有一个导师，学生可选多门课。将其规范化成3NF的。,1此表是1NF，其函数依赖为：学号姓名，学号导师号，学号导师名，课程号课程说明,(学号，课程号)成绩主码为（学号，课程号）存在部分函数依赖关系，不是2NF，首先将其分解为2NF。,学生（学号，姓名，导师号，导师名），课程（课程号，课程说明），成绩（学号，课程号，成绩）均为2NF,2判是否为3NF“学生”表不是3NF，其函数依赖为：学号姓名，学号导师号，导师号导师名，学号导师名消除依赖于决定者的属性，把它们放在一个单独的表中，得到：学生（学号，姓名，导师号），导师（导师号，导师名）,传递,关系模式：Student（学号，姓名，导师号，导师名，课程号，课程说明，成绩）规范化关系模式：课程（课程号，课程说明），成绩（学号，课程号，成绩）学生（学号，姓名，导师号）导师（导师号，导师名）,7.3关系模式的分解准则,模式分解要满足：模式分解具有无损连接性；模式分解能够保持函数依赖。无损连接是指分解后的关系通过自然连接可以恢复成原来的关系，即通过自然连接得到的关系与原来的关系相比，既不多出信息、又不丢失信息。保持函数依赖是指在模式的分解过程中，函数依赖不能丢失的特性，即模式分解不能破坏原来的语义。,关系模式的分解准则（续）,例：S-D-L（Sno，Dept，Loc）有函数依赖：SnoDept，DeptLoc不是第三范式的。至少可以有三种分解方案，分别为：方案1：S-L（Sno，Loc），D-L（Dept，Loc）方案2：S-D（Sno，Dept），S-L（Sno，Loc）方案3：S-D（Sno，Dept），D-L（Dept，Loc）这三种分解方案得到的关系模式都是第三范式的，那么如何比较这三种方案的好坏呢？由此在将一个关系模式分解为多个关系模式时除了提高规范化程度之外，还需要考虑其他的一些因素。,关系模式的分解准则（续）,将一个关系模式R分解为若干个关系模式R1，R2，Rn，意味着将存储在一张二维表r中的数据分散到了若干个二维表r1，r2，rn中。这样的分解应该不丢失信息，即能通过对关系r1，r2，rn的自然连接运算重新得到关系r中的所有信息。事实上，将关系r投影为r1，r2，rn时不会丢失信息，关键是对r1，r2，rn做自然连接时可能产生一些r中原来没有的元组，从而无法区别哪些元组是r中原来有的，即数据库中应该存在的数据，哪些是不应该有的。在这个意义上就丢失了信息。,关系模式的分解准则（续）,这三种分解方案是否都满足分解要求呢？假设此关系模式的数据如表所示，此关系用r表示。,关系模式的分解准则（续）,若按方案1将S-D-L投影到