第八章关系模式规范化

第八章关系模式规范化刘根萍本章内容问题的提出函数依赖关系的规范化刘根萍数据库的基本概念学习目标认知目标：了解函数依赖、关系规范化的含义理解三种范式的定义能力目标:能判别关系的范式类别(重点、难点)能进行范式的分解（重点、难点）刘根萍在关系数据库系统中，关系模型包括一组关系模式，各个关系不是完全孤立的，数据库的设计较层次和网状模型更为重要。如何设计一个适合的关系数据库系统，关键是关系数据库模式的设计一个好的关系数据库模式应该包括多少关系模式每一个关系模式又应该包括哪些属性如何将这些相互关联的关系模式组建一个适合的关系模型这些工作决定了到整个系统运行的效率，也是系统成败的关键所在，所以必须在关系数据库的规范化理论的指导下逐步完成。

一、问题的提出刘根萍情境：要求设计教学管理数据库，其关系模式如下：

SCD(SNO,SN,AGE,DEPT,MN,CNO,SCORE)其中，SNO表示学生学号，SN表示学生姓名，AGE表示学生年龄，DEPT表示学生所在的系别，MN表示系主任姓名，CNO表示课程号，SCORE表示成绩。

根据实际情况，这些数据有如下语义规定：一个系有若干个学生，但一个学生只属于一个系；一个系只有一名系主任，但一个系主任可以同时兼几个系的系主任；一个学生可以选修多门功课，每门课程可有若干学生选修；每个学生学习课程有一个成绩。一、问题的提出刘根萍关系SCDSNOSNAGEDEPTMNCNOSCORES1赵亦17计算机刘伟C190S1赵亦17计算机刘伟C285S2钱尔18信息王平C557S2钱尔18信息王平C680S2钱尔18信息王平C7

70S2钱尔18信息王平C570S3孙珊20信息王平C10S3孙珊20信息王平C270S3孙珊20信息王平C485S4李思男自动化刘伟C193一、问题的提出填入一部分具体的数据，则可得到SCD关系模式的实例，即一个教学管理数据库，如下图所示：(SNO,CNO)属性的组合能唯一标识一个元组，所以(SNO,CNO)是该关系模式的主关系键。刘根萍每个系名和系主任的名字存储的次数等于该系的学生人数乘以每个学生选修的课程门数，同时学生的姓名、年龄也都要重复存储多次，数据的冗余度很大，浪费了存储空间。存在的问题1.数据冗余刘根萍如果某个新系没有招生，尚无学生时，则系名和系主任的信息无法插入到数据库中。因为(SNO,CNO)是主关系键，不能为空，而这时没有学生，SNO和CNO均无值，因此不能进行插入操作。当某个学生尚未选课，即CNO未知，实体完整性约束还规定，主关系键的值不能部分为空，同样不能进行插入操作。存在的问题2.插入异常刘根萍某系学生全部毕业而没有招生时，删除全部学生的记录则系名、系主任也随之删除，而这个系依然存在，在数据库中却无法找到该系的信息。另外，如果某个学生不再选修C1课程，本应该只删去C1，但C1是主关系键的一部分，为保证实体完整性，必须将整个元组一起删掉，这样，有关该学生的其它信息也随之丢失。存在的问题3.删除异常刘根萍如果学生改名，则该学生的所有记录都要逐一修改SN；又如某系更换系主任，则属于该系的学生记录都要修改MN的内容，稍有不慎，就有可能漏改某些记录，这就会造成数据的不一致性，破坏了数据的完整性。存在的问题4.更新异常刘根萍由于存在以上问题，我们说，SCD是一个不好的关系模式。产生上述问题的原因，直观地说，是因为关系中“包罗万象”，内容太杂了。那么，怎样才能得到一个好的关系模式呢？我们把关系模式SCD分解为下面三个结构简单的关系模式：学生关系S(SNO,SN,AGE,DEPT)选课关系SC(SNO,CNO,SCORE)系关系D(DEPT,MN)一、问题的提出刘根萍SSCSNOSNAGEDEPT

SNOCNOSCORES1赵亦17计算机

S1C190S2钱尔18信息

S1C285S3孙珊20信息

S2C557S4李思21自动化

S2C680

S2C7

D

S2C570DEPTMN

S3C10计算机刘伟

S3C270信息王平

S3C485自动化刘伟

S4C193分解后的关系模式

一、问题的提出刘根萍在以上三个关系模式中，实现了信息的某种程度的分离，S中存储学生基本信息，与所选课程及系主任无关；D中存储系的有关信息，与学生无关；SC中存储学生选课的信息，而与所学生及系的有关信息无关。与SCD相比，分解后，数据冗余度明显降低。当新插入一个系时，只要在关系D中添加一条记录。当某个学生尚未选课，只要在关系S中添加一条学生记录，而与选课关系无关，这就避免了插入异常。当一个系的学生全部毕业时，只需在S中删除该系的全部学生记录，而关系D中有关该系的信息仍然保留，从而不会引起删除异常。同时，由于数据冗余度的降低，数据没有重复存储，也不会引起更新异常。一、问题的提出刘根萍经过上述分析，我们说分解后的关系模式是一个好的关系数据库模式。从而得出结论，一个好的关系模式应该具备以下四个条件：1.尽可能少的数据冗余。2.没有插入异常。3.没有删除异常。4.没有更新异常。

一、问题的提出刘根萍但要注意，一个好的关系模式并不是在任何情况下都是最优的，比如查询某个学生选修课程名及所在系的系主任时，要通过连接，而连接所需要的系统开销非常大，因此要以实际设计的目标出发进行设计如何按照一定的规范设计关系模式，将结构复杂的关系分解成结构简单的关系，从而把不好的关系数据库模式转变为好的关系数据库模式，这就是关系的规范化。我们要设计的关系模式中的各属性是相互依赖、相互制约的，这样才构成了一个结构严谨的整体。因此在设计关模式时，必须从语义上分析这些依赖关系。数据库模式的好坏和关系中各属性间的依赖关系有关。一、问题的提出刘根萍二、函数依赖函数依赖是指关系中各属性或属性组之间的相互依赖关系。它是关系规范化的理论基础。函数依赖的定义给定一个关系模式R，X和Y都是R的属性或属性组，对于X的每个确定值在任何时刻Y只有一个确定的值与之对应，则称“X函数决定Y”，或称为“Y函数依赖于X”，X称为决定因素，记为：X→Y。函数依赖的类型完全函数依赖1.概念、类型部分函数依赖传递函数依赖定义如果属性Y函数依赖于复合属性X，且不与X的任何子集函数相依赖，则称“Y完全函数依赖于X”。示例有如下的关系模式:生产(工厂编号,产品编号,计划数量)“工厂编号+产品编号”→“计划数量”，是完全函数依赖。二、函数依赖2.完全函数依赖定义对于一个函数依赖：X→Y，如果存在Z∈X（Z属于X），且有Z→Y成立，则称“Y部分函数依赖于X”。示例有如下的关系模式

SCD(学号，姓名，课程号，成绩，系名，系主任)主码为复合码：学号+课程号“姓名、系名、系主任”与主码是部分函数依赖二、函数依赖3.部分函数依赖（局部依赖）定义在关系模式中，如果X→Y(不能Y→X)，Y→Z，若存在X→Z，则称“Z传递依赖于X”。示例有如下的关系模式

SD（学号，姓名，系名，系主任）“学号→系名，系名→系主任”，则系主任传递依赖于学号。二、函数依赖4.传递函数依赖三、关系的规范化在关系数据库的设计过程中，对于同一个问题，选用不同的关系模式，其性能的优劣是大不相同的，为了区分关系模式的优劣，人们常常把关系模式分为各种不同等级的范式。类型第一范式(1NF)第二范式(2NF)第三范式(3NF)……关系规范化的概念将低级范式转换为若干个高级范式过程。低高1.概念和范式的类型三、关系的规范化定义如果关系模式R的所有属性的值域中第一个值都是不可再分解值，则称R是属于1NF模式。第一范式是对关系的最低要求。示例:判断哪一张表是1NF？2.第一范式（1NF）三、关系的规范化第一范式可能存在的问题：数据冗余：如各位老师的信息重复。插入异常：如果要插入刘老师的个人信息，但刘老师未开课，会造成缺关键字“学号”、“课程号”。删除异常：当要删除课程号=“C3”

的元组，会丢失李老师的信息。修改量大。837757857890成绩2-1434王C1041-0145周C2011-0145周C2033-1527李C3032-1434王C1022-1434王C101办公室教师年龄教师课程号学号2.第一范式（1NF）三、关系的规范化定义如果关系模式R为第一范式，且R中每个非主属性完全函数依赖于R的主码(复合码)，则称R为2NF模式。示例下列关系模式是1NF，是否是2NF？为什么？

SCD（学号，姓名，课程号，成绩，系名，系主任）主码为复合码：学号+课程号解决方法：消除部分函数依赖。不是。因为：“姓名、系名、系主任”与主码是部分函数依赖（即：只依赖于学号）3.第二范式（2NF）三、关系的规范化分解为2NF的方法：把关系模式中对主码完全函数依赖的非主属性与决定它们的主码放在一个关系模式中。把对主码部分函数依赖的非主属性和决定它们的主属性放在一个关系模式中。检查分解后的新模式，如果仍不是2NF，则继续按照前面的方法进行分解，直到达到要求。关系模式SCD的分解结果如下：SC（学号，课程号，成绩）SD（学号，姓名，系名，系主任）3.第二范式（2NF）三、关系的规范化定义如果关系模式R为第二范式，且R中每个非主属性都不传递函数依赖于R的某个候选码，则称R为3NF模式。示例下列关系模式是2NF，是否为3NF？为什么？

SD（学号，姓名，系名，系主任）解决方法：消除传递函数依赖。不是。因为：学号→系名，系名→系主任，存在传递依赖关系。4.第三范式（3NF）三、关系的规范化分解为3NF的方法：把直接对主码函数依赖的非主属性与决定它们的主码放在一个关系模式中。把造成传递函数依赖的决定因素连同被它们决定的属性放在一个关系模式中。检查分解后的新模式，如果不是3NF，则继续按照前面的方法进行分解，直到达到要求。关系模式SD的分解结果如下：SD（学号，姓名，系名）SD（系名，系主任）4.第三范式（3NF）三、关系的规范化5.各范式间的关系三、关系的规范化四、综合应用实例：假设某商业集团数据库中有1个关系模式R(商店编号,商品编号,数量,部门编号,负责人)。如果规定：

(1)每个商店的每种商品只在一个部门销售。

(2)每个商店的每个部门只有一个负责人。

(3)每个商店的每种商品只有一个库存数量。写出关系模式R的基本函数依赖集找出关系模式R的候选码。关系模式R最高已经达到第几范式？为什么？如果R不属于3NF，请将R分解成3NF。四、综合应用分析与解答：基本的函数依赖有：(商店编号+商品编号)→部门编号(商店编号+部门编号)→负责人(商店编号+商品编号)→数量由题意,上面的函数依赖可知“商店编号”是主属性，商品编号也是主属性，由这两个属性，可以确定关系元组的所有属性，所以R的候选码是：商店编号+商品编号。四、综合应用分析与解答：R(商店编号,商品编号,数量,部门编号,负责人)最高已达到2NF。因为负责人是码的传递依赖。要达到3NF，必须消除非主属性对码的传递依赖。对R分解后的两个模式如下：R1(商店编号，商品编号，数量，部门编号)R2(部门编号，负责人)本课小结关系