数据库技术及应用课件：关系数据库设计理论

1、数据库设计关系数据库设计理论关系数据库设计E.F.Codd提出规范化理论;后经深入的研究，形成了系统的设计理论。关系数据库系统中，关系模型包括一组关系模式，各个关系模式相互有联系；一个合适的关系数据库系统的设计，关键是关系数据库模式的设计。关系数据库设计理论-前言关系数据库的设计理论-前言规范化理论指导设计一个好的关系数据库系统：每一关系模式应包括哪些属性？多少关系模式？如何将这些相互关联的关系模式组建成一个适合的关系模型？对上述三个问题的回答决定整个系统运行效率，系统设计成败的关键。关系数据库的设计理论-前言关系数据库规范化理论主要包括以下内容：函数依赖（FD）范式（NF）模式设计函数依赖起

2、着核心作用，是模式分解和模式设计的基础，范式是模式分解的标准。不合理关系模式存在的问题例1某教学管理数据库，关系模式SCD： SCD（SNO，SN，AGE，DEPT，MN，CNO，SCORE）SNO表示学生学号， SN表示学生姓名， AGE表示学生年龄，DEPT表示学生所在系别， MN表示系主任姓名，CNO表示课程号，SCORE表示成绩。 数据有以下语义规定（用户的解释）一个系有若干个学生，一个学生只属于一个系；一个系只有一名系主任，系主任可以兼任；一个学生可以选修多门功课，每门课程可被多个学生选修；每个学生学习的课程有一个成绩。 填入部分具体数据，则可得到SCD的某一实例，即一个教学管理数据

3、库实例： 不合理关系模式存在的问题不合理关系模式存在的问题 不合理关系模式存在的问题根据语义规定并分析关系中的数据可看出:（SNO，CNO）属性组合能唯一标识一个元组，故（SNO，CNO）是该关系模式的主键。不合理关系模式存在的问题（1）数据冗余系名和系主任的名字存储次数等于该系学生人数乘以每个学生选修课程数学生的姓名、年龄也都重复存储多次数据冗余度很大，浪费了存储空间。（称为存储异常）不合理的关系模式存在的问题（2）插入异常若某新系未招生，则系名和系主任信息无法插入到数据库中。（SNO，CNO）是主键，主键值不能为空，由于SNO和CNO均无值，因此不能进行插入数据的操作；若某学生未选课，则数

4、据库中就没有该生的信息实体完整性约束还规定，主键的值不能部分为空，CNO未知，同样也不能进行数据插入操作。不合理的关系模式存在的问题（3）删除异常。当某系学生毕业而未招生时，删除全部学生的记录，则其他所有信息随之删除。现实中系仍存在，但数据库中却无法找到该系的信息。如果某学生不再选修C1课程，删去C1，则有关该学生所有的其他信息也随之丢失。C1是主键的一部分，为保证实体完整性，必须将整个元组一起删掉。不合理的关系模式存在的问题（4）更新异常某学生改名，则该学生的所有其他记录都要逐一修改SN的值；某系更换系主任，则属于该系的学生记录都要修改MN的内容；不合理关系模式存在的问题关系模式的异常存储异

5、常插入异常更新异常删除异常由于存在以上4类问题，称SCD是一个不好的关系模式。得到好关系模式的方法模式分解把关系模式SCD分解为学生关系S（SNO，SN，AGE，DEPT） 选修课SC（SNO，CNO，SCORE）系关系D（DEPT，MN） 三个结构简单的关系模式，每一关系模式关注一个方面的内容。得到好关系模式的方法模式分解SSCD得到好关系模式的方法模式分解在以上三个关系模式中，实现了信息某种程度的分离：S保存学生基本信息，与所选课程及系主任无关D保存院系信息，与学生无关SC保存学生选课信息，而与学生及院系无关与SCD相比，分解为三个关系模式后，数据冗余程度明显降低。当新开办一个系时，只要在

6、关系D中添加一个记录就可以了；当尚未选课某个新生到来时，只要在关系 S中添加一条学生记录就可以了，而与选课关系无关；可避免插入异常。得到好关系模式的方法模式分解得到好关系模式的方法模式分解当一个系的学生全部毕业时，只需在 S中删除该系的全部学生记录，而关系D 中有关该系的信息仍然保留，从而不会引起删除异常；由于数据冗余度的降低，数据没有重复存储，也不会引起更新异常。好关系模式的四个条件分解后的关系模式是一个好的关系数据库模式。一个好的关系模式应该具备四个条件：可控的存储异常没有插入异常没有删除异常没有更新异常异常的共性是什么？好关系模式VS.最优关系模式一个好的关系模式并不是在任何情况下都是最

7、优的比如查询某个学生选修课程名及所在系的系主任时，要通过连接，而连接所需的系统资源（空间和时间）非常大注意如何得到好的关系模式？关系模式的好与不好是否有结构上的规律？属性之间的关系依赖关系不好的模式有没有某种路径可以成为好的模式？标准范式通往标准的路径模式设计数据依赖数据依赖关系中属性之间体现出来的数据间相互关系数据依赖是数据之内在性质，语义的体现。重要的数据依赖函数依赖（Functional Dependency, FD）多值依赖（Multi-value d Dependency, MVD）对语义，谁有最终解释权？定义：设R（U）是属性集U上的关系模式，X，Y是U的子集，r是R的任一具体关系

8、，如果对r的任意两个元组t1，t2，由t1x=t2x导致t1Y=t2Y，则称X函数决定Y或Y函数依赖于X，记为XY。这里t1X，t2X分别表示元组t1，t2在属性集X上的值，函数依赖 函数依赖FD是对关系R的一切可能的当前值 r 定义的，不是针对某个特定关系，也就是说，对于X的每一个具体的值，都有惟一的Y值与之对应，即 Y 值由 X值决定，因而这种数据依赖称为函数依赖。 函数依赖函数依赖是语义范畴的概念。根据语义来确定一个函数依赖。例如，“姓名出生年月” 成立吗？如果允许同一关系中有相同姓名存在，则出生年月就不再函数依赖于姓名。如果限定不允许相同姓名出现，则“姓名出生年月”函数依赖成立。函数依

9、赖【例】关系模式R（学号，姓名，出生年月，系编号，系负责人）。在R 的关系r中，存在着如下函数依赖： 学号姓名 （每个学号只能对应一个姓名）； 学号出生年月 （每个学生只能对应一个出生年月）;系编号系负责人（每个系只能有一名负责人）。 一些术语和记号： （1）若XY，则X称为决定因素。 （2）若XY，YX，则记作XY。 （3）若Y不函数依赖于X，则记作X Y。 函数依赖函数依赖分类完全函数依赖部分函数依赖传递函数依赖定义 ：R（U）中，如果XY，且对于X的任何一个真子集X，有X Y，则称Y对X完全函数依赖，记作X Y。 F函数依赖分类完全函数依赖函数依赖分类完全函数依赖【例】有一关系模式S（学

10、号，姓名，系名称，出生年月）。 根据语义在S中存在如下完全函数依赖： 学号 系名称，学号 出生年月 通常记为 学号系名称，学号出生年月 若无重名，还存在学号姓名FF定义 在R（U）中，如果XY，XY，则称Y对X部分函数依赖，记作 X Y。【例】有一关系模式SC（学号，课程号，成绩，教师编号）。在SC中，（学号，课程号） 成绩（学号，课程号）教师编号（XY） 课程号教师编号 （XY）因此 （学号，课程号） 教师编号 P函数依赖分类部分函数依赖FP定义：在R（U）中，如果XY，Y X， YX ，YZ，则称Z对X传递函数依赖，记作：X Z。 T函数依赖分类传递函数依赖函数依赖分类传递函数依赖【例】关

11、系模式R（学号，姓名，出生年月，系编号，系负责人）在此关系模式中有如下函数依赖：学号系编号 （相当于XY）系编号学号 （相当于YX）系编号系负责人 （相当于YZ）因此，在R中存在传递函数依赖学号 系负责人。TF用函数依赖的概念定义主键 键的直观定义是唯一标识实体的属性集，用函数依赖的概念如何定义？定义 设K为R（U）中的属性或属性组合，若K U，则K为R的候选键（Candidate Key）。若候选键多于一个，则选定其中的一个为主键（Primary Key），也称为键（Key）；当只有一个候选键时这个候选键即是主键。 用函数依赖的概念定义主键包含在任一候选键中的属性，叫主属性（Primary

12、Attribute）。不包含在任一候选键中的属性称为非主属性（Nonprime Attribute）或非键属性（Non-key Attribute）。主键可为单个属性，也可为属性组。在特殊情况下，主键可以由整个元组组成，称为全键（All-key）。用函数依赖的概念定义主键关系模式 S（学号，姓名，系名称，出生年月）中，学号是主键；关系模式 SC （学号，课程号，成绩，教师编号）中，属性组合（学号，课程号）是主键；设有关系模式 A（作者，书籍，读者）假设一个作者可以编著多本书，某一本书可由多个作者编著，读者可以阅读不同作者的不同书籍，这个关系模式的主键为（作者，书籍，读者）。用函数依赖的概念定义

13、外键 定义：关系模式R中属性或属性组X并非R的主键，但X是另一个关系模式的主键，则称X是R的外来键（Foreign Key），也称外键。用函数依赖的概念定义主键关系模式SC（学号，课程号，成绩，教师编号），学号不是主键；关系模式S（学号，姓名，系名称，出生年月），学号是主键；则学号是关系模式SC的外键。外键提供了一个表示关系间联系的手段。如关系模式S与SC的联系就是通过学号来实现的。关系的规范化 在关系模式的设计中，函数依赖起着重要作用，关系模式设计的好坏依赖于它的函数依赖是否满足特定的要求。满足特定要求的模式称为范式（Normal Form）。满足不同程度要求的模式为不同范式。 关系的规范化

14、E.F.Codd在1971年至1972年间系统地提出了第一范式（ 1NF）、第二范式（ 2NF）和第三范式（ 3NF）的概念；1974年，E.F.Codd和 Boyce 又共同提出了一个新范式，即BCNF。1976年Fagin 提出第四范式（4NF）。现在已有人提出第五范式（5NF）。关系的规范化 所谓“第几范式”是表示关系模式的某一种规范级别，可以理解成符合某种级别的关系模式的集合。一般地，如果R属于第x范式，那么就可以写成RxNF。 各种范式之间的关系满足5NF 4NF 3NF 2NF 1NF关系的规范化5NF4NF3NF2NF1NF图示各种范式之间的关系 关系的规范化 一个低一级范式的关

15、系模式，通过模式分解可以转换为若干个高一级范式的关系模式的集合，这种过程叫做规范化。 第一范式定义 设R是一个关系模式，如果R中每一个属性A的值域中的每一个值都是不可分解的，则称R属于第一范式，记作R1NF。 第一范式【例】有一关系模式SC1（学号，课程），学生和选修课程的记录如下表所示学号课程007501（程序设计，操作系统，数据库）007101（电工学，继电保护）SC1第一范式在上述模式中，课程属性值是可分解的，因此SC1 1NF。 有啥问题？怎么解决？第一范式在关系模式SC1中存在以下问题：更新操作困难如果学号为007501的学生想把选修课程改为电工学，继电保护，则DBMS在处理上将面临

16、二义性问题：是修改第一个元组的课程属性值，还是修改第二个元组的学号属性值？ 增加属性存在问题若增加一个属性“ 成绩 ”，那么该属性值表达的含义不清。解决关系模式SC1问题的方案将课程属性的属性值拆开，形成如下关系模式SC2（学号，课程）（见下表所示）。显然，SC21NF 学号课程007501程序设计007501操作系统007501数据库007101电工学007101继电保护SC2（学号，课程）第一范式定义 如果R1NF，且每一个非主属性完全函数依赖于主键，则R2NF。 【例】有一关系模式SCD（学号，姓名，系名称，系负责人，课程号，成绩）在该模式中，有以下函数依赖存在： （学号，课程号） 成绩

17、 学号姓名 （学号，课程号） 姓名 学号系名称 （学号，课程号） 系名称 学号系负责人 （学号，课程号） 系负责人 系名称系负责人 FPPP第二范式还有没有FD？有多少？由定义可知SCD中的主键为（学号，课程号），因此，姓名，系名称，系负责人，成绩均是非主属性，而非主属性中只有成绩是完全函数依赖于主键，其他属性是部分函数依赖于主键，因此，关系模式SCD不符合2NF定义，即SCD 2NF。第二范式第二范式结论：从1NF关系中消除非主属性对主键的部分函数依赖，则可得到2NF关系。如果R的键为单属性，则R2NF若R的所有属性均为主属性，则R2NF。一个关系模式R不属于2NF，就会产生以下问题： （1

18、）插入异常 （2）删除异常（3）修改复杂对主键部分函数依赖的非主属性，可以采用关系模式分解的方法，消除非主属性对主键的部分依赖，就可以解决关系模式SCD 2NF存在的3个问题。第二范式第二范式将SCD分解为两个关系模式：SC（学号，课程名，成绩）SD（学号，姓名，系名称，系负责人） SC的主键是（学号，课程号），SD的主键是学号，从前面的函数依赖分析中得知，关系模式SC和 SD的非主属性对主键都是完全函数依赖，因此，SC2NF，SD2NF。定义 如果R2NF，且每一个非主属性不传递函数依赖于主键，则R3NF。【例】 考察上例中的两个关系模式SC和SD。显然，SC中不存在传递函数依赖，因此，SC

19、3NFSD中的函数依赖学号系名称 （ 系名称学号）系名称系负责人 由传递函数依赖的定义可得学号 系负责人；因此SD 3NF T第三范式第三范式一个关系模式R不属于3NF，同样会产生类似于上节的问题。（1）插入异常 （2）删除异常（3）修改复杂解决上述问题的办法是将关系模式SD分解，消除关系模式SD中存在的传递函数依赖。第三范式下面将SD分解为两个关系模式：SDN（学号，姓名，系名称）DM（系名称，系负责人） SDN的主键是学号，DM的主键是系名称。显然，在这两个关系模式中既不存在部分函数依赖，也不存在传递函数依赖，因此SDN3NF，DM3NF。 BCNF（Boyce Codd Normal F

20、orm）是由Boyce与Codd提出的。它比3NF更进了一步，是修正的第三范式，有时也称为扩充的第三范式。定义：关系模式R3NF，若XY且Y X时X必含有主键，则RBCNF。BCNF BCNF 满足BCNF的关系模式有以下 特点：所有非主属性对每一个主键都是完全函数依赖；所有的主属性对每一个不包含它的主键也是完全函数依赖；没有任何属性完全函数依赖于任何非主属性。BCNF 可以证明，若RBCNF，则R3NF，但是若R3NF，则R不一定属于BCNF。由于RBCNF，按定义排除了任何属性对主键的部分函数依赖和传递函数依赖，所以R3NF。BCNF 考察关系模式SG（学号，课程号，成绩），它只有一个主键

21、（学号，课程号），并且没有任何属性对（学号，课程号）部分函数依赖或传递函数依赖，所以SG3NF。同时，SG中（学号，课程号）是唯一的决定因素，所以SGBCNF。 BCNF 在关系模式SDN（学号，姓名，系名称）中，假设姓名具有唯一性即没有同名人，那么SDN就有两个候选键，这两个候选键都由单个属性组成，其他属性不存在对主键的传递函数依赖与部分函数依赖，所以SDN3NF，同时SDN中除学号，姓名之外没有其他决定因素，所以SDN也属于BCNF。BCNF 【例】有一关系模式SCT（学生，课程，教师），在该关系模式中，存在如下对应关系：对于每门课，每个学生的授课教师只有一位；每位教师只讲授一门课；每门课

22、可由不同教师讲授。 BCNF由语义可得到如下函数依赖：（学生，课程）教师 教师课程（学生，教师）课程这些对应关系可用下图表示。BCNF关系模式SCT的函数依赖课程学生教师学生教师课程根据上面的分析，在该关系中有两个候选键（学生，课程）和（学生，教师），因为没有任何非主属性，因而也不存在非主属性对主键的部分函数依赖或传递函数依赖，所以SCT3NF。另外，因为教师课程，也就是教师是决定因素，但教师不是主键，由BCNF的定义可知，SCT BCNF。BCNF BCNF 【例】有一关系模式SCP（学生，课程，名次）。在SCP中有如下对应关系：每个学生选修每门课程的成绩有一定的名次；每门课程中每一名次只有一个学生（即没有并列名次）。 由语义可得到如下函数依赖： （学生，课程）名次 （课程，名次）学生 显然，这个关系模式有两个候选键（学生，课程）和（课程，名次），并且没有非主属性对主键的部分函数依赖或传递函数依赖，因此，SCP3NF。另外，除（学生，课程）和（课程，名次）之外没有其他决定因素，所以，SCPBCNF。 BCNF 不满足BCNF的关系模式同样存在着更新异常。假设SCT