第7章关系数据库规范化理论

数据库原理与应用（第2版）高等院校计算机教材系列第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表示为：X→Y•如果有关系模式R(A1,A2,…,An)，X和Y为{A1,A2,…,An}的子集，则对于关系R中的任意一个X值，都只有一个Y值与之对应，则称X函数决定Y，或Y函数依赖于X。示例•例1：对学生关系模式Student（Sno,Sname,Sdept,Sage）•有以下依赖关系：Sno→Sname,Sno→Sdept,Sno→Sage例2：SC（Sno,Cno,Grade）（Sno,Cno）→Grade函数依赖定义•设有关系模式R(A1,A2,…,An)，X和Y均为{A1,A2,…,An}的子集，r是R的任一具体关系，t1、t2是r中的任意两个元组；•如果由t1[X]=t2[X]可以推导出t1[Y]=t2[Y]，则称X函数决定Y，或Y函数依赖于X，记为X→Y。7.1.2一些术语和符号（1）如果X→Y，但Y不包含于X，则称X→Y是非平凡的函数依赖。（2）如果X→Y，但Y包含于X，则称X→Y是平凡的函数依赖。若无特别声明，我们讨论的都是非平凡的函数依赖。（3）如果X→Y，则X称为决定因子。术语和符号（续）（4）如果X→Y，并且Y→X，则记作X←→Y。（5）如果X→Y，并且对于X的一个任意真子集X‘都有X‘—/→Y，则称Y完全函数依赖于X，记作：如果X‘→Y成立，则称Y部分函数依赖于X，记作：（6）如果X→Y（非平凡函数依赖，并且Y—/→X）、Y→Z，则称Z传递函数依赖于X。fXYpXY示例•例1：有关系模式SC（Sno,Sname,Cno,Credit,Grade）•主键为（Sno,Cno），则函数依赖关系有：示例•例2：假设有关系模式S（Sno,Sname,Dept,Dept_master）•假设一个系只有一个主任，主键为Sno，则函数依赖关系有：7.1.3为什么要讨论函数依赖？有关系模式：S-L-C(Sno,Sname,Ssex,Sdept,SLOC,Cno,Grade)其中各属性分别为：学号、姓名、性别、学生所在系、学生所住宿舍楼、课程号和考试成绩。假设每个系的学生都住在一栋楼里，（Sno,Cno）为主键2020年2月26日12时12分13数据示例SnoSnameSsexSdeptSlocCnoGrade9512101李勇男计算机系2公寓C01909512101李勇男计算机系2公寓C02869512101李勇男计算机系2公寓C06NULL9512102刘晨男计算机系2公寓C02789512102刘晨男计算机系2公寓C04669521102吴宾女信息系1公寓C01829521102吴宾女信息系1公寓C02759521102吴宾女信息系1公寓C04929521102吴宾女信息系1公寓C05509521103张海男信息系1公寓C02689521103张海男信息系1公寓C06NULL9531101钱小平女数学系1公寓C01809531101钱小平女数学系1公寓C05959531102王大力男数学系1公寓C0585存在问题•数据冗余问题•数据更新问题•数据插入问题•数据删除问题结论•S-L-C关系模式不是一个好的模式。•如何改造这个关系模式并克服以上种种问题是关系规范化理论要解决的问题，也是讨论函数依赖的原因。•解决方法:模式分解，即把一个关系模式分解成两个或多个关系模式，在分解的过程中消除那些“不良”的函数依赖，从而获得良好的关系模式7.2关系规范化•7.2.1关系模式中的码•7.2.2范式7.2.1关系模式中的码1．候选码：设K为R(U,F)中的属性或属性组，若Kf→U，则K为R候选码。（K为决定R全部属性值的最小属性组）。2.主码：关系R(U,F)中可能有多个候选码，则选其中一个作为主码。3.全码：候选码为整个属性组。主属性与非主属性：4.在R(U,F)中，包含在任一候选码中的属性称为主属性，不包含在任一候选码中的属性称为非主属性。示例1•有关系模式：学生（学号，姓名，性别，身份证号，年龄，所在系）•候选码：学号，身份证号。•主码：“学号”或“身份证号”。•主属性：学号，课程号。•非主属性：姓名，性别，年龄，所在系。示例2•有关系模式：选课（学号，课程号，考试次数，成绩）•设一个学生对一门课程可以有多次考试，每一次考试有一个考试成绩。•候选码：（学号，课程号，考试次数），也为主码。•主属性：学号，课程号，考试次数•非主属性：成绩。示例3•有关系模式：授课（教师号，课程号，学年）•语义：一个教师在一个学年可以讲授多门不同的课程，可以在不同学年对同一门课程讲授多次，但不能在同一个学年对同一门课程讲授多次。一门课程在一个学年可以由多个不同的教师讲授，同一个学年可以开设多门课程，同一门课程可以在不同学年开设多次。•候选码：（教师号，课程号，学年）•主码：同候选码。•主属性：教师号，课程号，学年•非主属性：无•称这种候选码为全部属性的表为全码表7.2.1关系模式中的码（续）外码：用于关系表之间建立关联的属性（组）。定义：若R（U，F）的属性（组）X（X属于U）是另一个关系S的主码，则称X为R的外码。（X必须先被定义为S的主码）。7.2.2范式•关系数据库中的关系要满足一定的要求，满足不同程度要求的为不不同的范式。第一范式第一范式：不包含重复组的关系。第二范式•2.第二范式:如果R(U,F)∈1NF，并且R中的每个非主属性都完全函数依赖于主码，则R(U,F)∈2NF•例:S-L-C（Sno,Sname,Ssex,Sdept,Sloc,Cno,Grade）•∵有：(Sno,Cno)→Sname•∴S-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；•插入异常：当新建一个系时，若还没有招收学生，则无法插入；SnoSnameSsexSdeptSloc9512101李勇男计算机系2公寓9512102刘晨男计算机系2公寓9521102吴宾女信息系1公寓9521103张海男信息系1公寓9531101钱小平女数学系1公寓9531103王大力女数学系1公寓第三范式•定义：如果R(U,F)∈2NF，并且所有非主属性都不传递依赖于主码，则R(U,F)∈3NF。•对S-L(Sno,Sname,Ssex,Sdept,SLOC)∵Sno传递→SLOC,∴不是3NF分解过程•对于不是候选码的每个决定因子，从表中删去依赖于它的所有属性；•新建一个表，新表中包含在原表中所有依赖于该决定因子的属性；•将决定因子作为新表的主码。•S-L分解后的关系模式为：•S-D（Sno,Sname,Ssex,Sdept），主码为Sno•S-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的。ffff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关系模式的外码BCNF•例：关系模式：CSZ（City，Street，Zip）•语义：城市和街道可以决定邮政编码，邮政编码可以决定城市。•候选码：(City，Street），(Street，Zip)•F:{(City，Street）→Zip，Zip→City}•是3NF•但存在的问题:插入异常,删除异常BCNF定义•若R∈1NF，且能决定其它属性取值的属性（组）必定包含候选码，则R∈BCNF。•如果一个关系的每个决定因素都是候选码，则其是BCNF。•如果一个关系的每个函数依赖的左部都是候选码，则其是BCNF。•如果R∈3NF，并且不存在主属性对非主属性的函数依赖，则其是BCNF。BCNF和3NF的关系•若R∈BCNF，则R∈3NF•若R∈3NF，则R不一定属于BCNF可能违反BCNF的情形•关系中包含两个（或更多）复合候选码。•候选码有重叠，通常至少有一个重叠的属性。规范化举例•设有关系模式：Student（学号，姓名，导师号，导师名，课程号，课程说明，成绩）•语义：一名学生只有一个导师，学生可选多门课。•将其规范化成3NF的。1．此表是1NF，其函数依赖为：学号p→姓名，学号p→导师号，学号p→导师名，课程号p→课程说明,(学号，课程号)→成绩主码为（学号，课程号）存在部分函数依赖关系，不是2NF，首先将其分解为2NF。学生（学号，姓名，导师号，导师名），课程（课程号，课程说明），成绩（学号，课程号，成绩）均为2NF2．判是否为3NF“学生”表不是3NF，其函数依赖为：学号→姓名，学号→导师号，导师号p→导师名，∴学号传递→导师名•消除依赖于决定者的属性，把它们放在一个单独的表中，得到：•学生（学号，姓名，导师号），导师（导师号，导师名）7.3关系模式的分解准则•模式分解要满足：模式分解具有无损连接性；模式分解能够保持函数依赖。无损连接是指分解后的关系通过自然连接可以恢复成原来的关系，即通过自然连接得到的关系与原来的关系相比，既不多出信息、又不丢失信息。保持函数依赖分解是指在模式的分解过程中，函数依赖不能丢失的特性，即模式分解不能破坏原来的语义。关系模式的分解准则（续）•例：S-D-L（Sno，Dept，Loc）有函数依赖：•Sno→Dept，Dept→Loc•不是第三范式的。至少可以有三种分解方案，分别为：方案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）•这三种分解方案得到的关系模式都是第三范式的，那么如何比较这三种方案的好坏呢？由此在将一个关系模式分解为多个关系模式时除了提高规范化程度之外，还需要考虑其他的一些因素。关系模式的分解准则（续）•将一个关系模式RU，F分解为若干个关系模式R1U1，F1，R2U2，F2，…，RnUn，Fn，意味着将存储在一张二维表r中的数据分散到了若干个二维表r1，r2，…，rn中。这样的分解应该