MySQL数据库应用（微课版）课件 1.3 关系模式规范化

项目1：部署数据库开发环境数据库应用任务1数据模型的规划与设计——关系模式规范化

教学目标

素质目标

知识目标1

线上线下结合，引导学习方式，培养自主学习能力；2

培养学生积极探索、勇于创新的科学素养；3

养成小组沟通协作共同学习，解决问题能力和团队合作精神；4

思政内容：培养学生好的职业道德与职业素养1能理解关系模式规范化的作用；2了解第一范式满足的条件；3掌握如何将第一范式转化为第二范式；4掌握如何将第二范式转化为第三范式；

能力目标1能识别一个不好的关系模式具有哪些缺点；2能将具有缺陷的关系模式转化成为可用的关系模式；

教学重点将第一范式转化为第二范式；将第二范式转化为第三范式

教学难点在范式转化时，如何理解、识别并解决“属性完全依赖”，“传递依赖”的问题教学重难点知识储备在关系数据库中，对同一个问题，数据库的逻辑设计结果不是唯一的。为进一步提高数据库应用系统的性能，有必要对关系模式进一步修改，调整数据模型的结构，这需要以规范化理论为指导，对关系模式进行规范化。设计一个学生管理数据库，需要该数据库中包括的信息有：学号、姓名、性别、出生日期、系名、系主任、课程号和成绩。如果将这些信息包含在一个关系中，则学生关系模式S为：S（学号、姓名、性别、出生日期、系名、系主任、课程号、成绩）在学生关系模式S中，关系模式的主键为（学号、课程号）。各属性之间的关系为：一个系有若干个学生，但一个学生只属于一个系且只有一个系主任，但一个系主任可以兼任几个系的主任；一个学生可以选修多门课程，每门课程可以被多个学生选修；每个学生的每门课程只有一个成绩。关系模式规范化4知识储备学生关系模式S的实例

关系模式规范化5学号姓名性别出生日期系名系主任课程号成绩2020110101朱军男2002-10-15计算机系武春岭101772020110101朱军男2002-10-15计算机系武春岭102832020110101朱军男2002-10-15计算机系武春岭103822020110101朱军男2002-10-15计算机系武春岭105692020110102龙婷秀女2002-11-05计算机系武春岭101642020110102龙婷秀女2002-11-05计算机系武春岭102582020110102龙婷秀女2002-11-05计算机系武春岭104682020110103张庆国男2003-01-09计算机系武春岭101692020110103张庆国男2003-01-09计算机系武春岭103882020110103张庆国男2003-01-09计算机系武春岭105772020120101李成男2002-07-09机电系王春强201782020120101李成男2002-07-09机电系王春强20363知识储备从上表存放的数据可以看出，该关系具有以下缺陷：（1）数据冗余。系名和系主任的存储次数等于该系学生选修课程的人次；（2）插入异常。这个关系模式的主键是（学号、课程号），当一个系里的学生如果没有选修课程时，则课程号无值，导致该生的所有信息将无法插入数据库中；（3）删除异常。如果在某个系的学生全部毕业又没招新生的情况下，删除已毕业学生的信息时，将会使系名和系主任的信息也随之删除，但由于这个系仍然存在，却又找不到该系的信息，即会出现删除异常；（4）更新异常。当要更改某个学生的姓名时，则必须搜索出包含该姓名的每条记录，并对其姓名逐一修改，修改量大，如果某条记录漏改了，则会造成数据不一致，即出现更新异常。

关系模式规范化6知识储备针对上表所示的关系具有的上述缺陷应该如何解决呢？就要利用规范化理论对关系模式进行规范化。满足特定要求的关系模式称为范式，按其规范化程度从低到高可分为5级范式（NormalForm），分别为1NF、2NF、3NF（BCNF）、4NF和5NF。规范化程度较高的范式是较低范式的子集，一个低一级范式的关系模式，通过分解可以转换为若干个高一级范式的关系模式，这个过程称为关系的规范化。关系规范化的基本方法是逐步消除关系模式中不恰当的数据依赖，使关系模式达到某种程度的分离，用一个关系来表达一事或一物。

关系模式规范化7知识储备1.第一范式（1NF）1NF：如果关系模式R中不包含多值属性，则R满足第一范式，记为R∈1NF。第一范式要求不能在表中嵌套表，是关系模式要遵循的最基本要求，数据库中所在的关系模式必须满足第一范式。例如，前面的复合表所对应的关系模式不满足1NF，因为其成绩属性中包含了多门课程的成绩，属于表中嵌套表的情况，只有将复合表中的成绩属性拆开，形成二维表的形式，这样就不存在表中嵌套表的情况了，其对应的关系模式就满足1NF了。

关系模式规范化8知识储备1.第一范式（1NF）互动1：什么样的表才满足第一范式？

关系模式规范化9知识储备2.第二范式（2NF）

关系模式仅仅满足1NF是不够的，尽管学生关系模式满足1NF，根据前面的分析，这个关模式存在数据冗余、插入异常、删除异常和更新异常的缺陷，所以需要对该关系模式进一步规范化，使之达到更高级别的范式。2NF：如果关系模式R满足第一范式，且每个非键属性完全函数依赖于R的键属性，则R满足第二范式，记为R∈2NF。在2NF中解决了插入异常问题。

关系模式规范化10知识储备2.第二范式（2NF）

例如，学生关系模式S（学号，姓名，性别，出生日期，系名，系主任，课程号，成绩）不是2NF。因为该关系模式的主键为（学号，课程号），对于非键属性姓名和系名来说，它们只依依赖于学号，而与课程号无关，因此关系模式S存在部分函数依赖。解决的办法是将关系模式进行分解，使每个非键属性完全函数依赖于键属性。分解的方法是采用投影分解法。（1）把关系模式中对键完全函数依赖的非键属性与决定它们的键放在一个关系模式中。（2）把对键部分函数依赖的非键属性和决定它们的键放在一个关系模式中。（3）检查分解结果，如果仍有不满足2NF的，则按前两步骤继续分解。

关系模式规范化11知识储备3.第三范式（3NF）

3NF：如果关系模式R满足第二范式，且没有一个非键属性传递依赖于键，则称R满足第三范式，记为R∈3NF。在3NF中解决了删除异常问题。例如，学生和系关系模式S-D（学号，姓名，性别，出生日期，系名，系主任）满足第二范式，由于系名由学号决定，系主任是由系名来决定，即存在系主任传递依赖于学号，因此S-D不满足3NF，它存在删除异常问题。解决的方法同样是对S-D进行投影分解。（1）把直接对键函数依赖的非主键属性与决定它们的键放在一个关系模式中。（2）把造成传递依赖的属性和被该属性决定的其他属性放在一个关系模式中。（3）检查分解结果，如果仍有不满足3NF的，则按前两步骤继续分解。3NF是一个可用关系模式应满足的最低范式，如果一个关系模式不满足3NF，事实上它是不可用的。

关系模式规范化12任务实施【任务1】将学生关系模式S（学号，姓名，性别，出生日期，系名，系主任，课程号，成绩）规范化为第二范式。分析：对学生关系模式S来说，姓名、系名、系主任只依赖于学号，与学生所选课程号无关，因此可将它们放到一个关系模式中；成绩属性完全依赖于学号和课程号，可将它们放到一个关系模式中。因此，将学生关系模式S进行分解，形成两个新的关系模式，结果如下：选修关系模式：S-C（学号，课程号、成绩）学生和系关系模式：S-D（学号，姓名，性别，出生日期，系名，系主任）经过上述模式分解，两个关系模式中的非键属性对键都是完全函数依赖，所以它们都满足2NF。

互动2：一个可用关系模式至少应满足第几范式？关系模式规范化13任务实施【任务2】将学生和系关系模式S-D（学号，姓名，性别，出生日期，系名，系主任）规范化为第三范式。分析：对学生和系关系模式S-D来说，姓名、性别、出生日期和系名直接依赖于学号，可将它们放在一个关系模式中，而把系名和系主任放到另一个关系模式中。因此，学生和系关系模式分解的结果如下：学生关系模式：S（学号，姓名，性别，出生日期，系名）系关系模式：D（系名，系主任）分解后的关系