数据库基础与应用 access2010 第三章 关系规范化课件

1、第3章 关系规范化本章知识点关系规范化目的和意义 函数依赖 第一范式（1NF）第二范式 （2NF）第三范式（3NF）BC范式（BCNF）逆规范化 关系规范化的目的和意义E.F.Codd指出：一个关系数据库中的关系都应满足一定的规范，才能构造出好的关系模式E. F. Codd埃德加弗兰克科德（Edgar Frank Codd，19232003）是密执安大学哲学博士，IBM公司研究员，被誉为“关系数据库之父”，并因为在数据库管理系统的理论和实践方面的杰出贡献于1981年获图灵奖。20世纪80年代以来，计算机厂商推出的数据库管理系统几乎都支持关系模型，数据库领域当前的研究工作大都以关系模型为基础。关

2、系规范化的目的和意义一个好的数据库设计所遵循的标准： 每个表应提供一个惟一的标识符，用来区分不同的行 每个表应当存储单个实体类型的数据 每个表不应该出现值全部相同的行（或列） 尽量避免接受NULL值（空值）的列 尽量避免值重复关系规范化过程用形式更为简洁、结构更加规范的关系模式取代原有关系模式的过程关系规范化的目的和意义实例分析 将专业和系两个实体存放在一个表内majorNomajorNamemajorLeaderdepNamedepDeanNameM001日语王金宝外语系李强生M002英语陈龙外语系李强生M003阿拉伯语何炅新外语系李强生M004工商管理任德华工商系赵德胜M005金融张学斌工

3、商系赵德胜M006国际贸易李东生工商系赵德胜M007经济学钱海波工商系赵德胜M008软件工程朱晓明计算机系王海洋M009计算机网络刘少云计算机系王海洋M010建筑学白意愿建筑系刘玉明关系规范化的目的和意义实例分析数据冗余 ：数据在数据库中的重复存放称为数据冗余数据冗余导致的问题：浪费存储空间可能造成数据的不一致性，产生插入、删除和更新异常增加了插入、删除和更新数据的时间majorNomajorNamemajorLeaderdepNamedepDeanNameM001日语王金宝外语系李强生M002英语陈龙外语系李强生M003阿拉伯语何炅新外语系李强生数据重复关系规范化的目的和意义实例分析插入异常

4、 ：在不规范的数据表中插入数据时，由于实体完整性约束要求主键非空的限制，将会出现一些有用数据无法插入的情况majorNomajorNamemajorLeaderdepNamedepDeanNameM001日语王金宝外语系李强生M002英语陈龙外语系李强生历史系张大同主键不能为空，插入失败关系规范化的目的和意义设计数据库的方法：凭借经验和常识可以进行数据库初步设计使用一些系统方法（如：规范化理论）来降低冗余，从而使数据库设计更为合理关系规范化是数据库逻辑设计的指南，它是基于函数依赖和范式的3.1函数依赖 数据依赖是一个关系中各属性之间的相互依赖数据依赖包括函数依赖和多值依赖，我们这本书只讨论有关

5、函数依赖(Functional Dependencies)的内容。函数依赖涉及到平凡函数依赖、非平凡函数依赖、完全函数依赖、局部（部分）函数依赖、直接函数依赖和传递函数依赖等概念。学生关系（学号，姓名，性别，年龄，专业编号，已取得总学分） 学号姓名学号性别 学号年龄 学号专业编号 学号已取得总学分3.1函数依赖 函数依赖的定义已知：X和Y是关系R中两个属性（或属性组）若对于X的任何一个值，只有一个Y的值与之对应，则称 X函数决定Y ，或Y函数依赖于X，记为：XY，X被称为决定因素 任何属性都函数依赖于候选键，所有候选键都是决定因子例 子：传递与直接函数依赖的定义：一个关系为R（U），X，Y和Z

6、为属性集U上的子集，其中XY， YZ，但YX且Y不包含Z，则存在XZ，称此为传递函数依赖。 否则称XZ为直接函数依赖。 如：学号系名，系名系主任名 所以 学号系主任设一个关系为R（U），X，Y和Z为属性集U上的子集，若XY，则存在XZYZ和XZY。最小函数依赖集的定义：设一个关系为R（U），X和Y为U的子集，若XY，并且为完全非平凡函数依赖，同时Y是单属性，则称XY为R的最小函数依赖。由R中所有最小函数依赖构成R的最小函数依赖集，其中不含有冗余的传递函数依赖。候选码的定义：设一个关系为R（U），X为U的一个子集，若X能够函数决定U中每个属性，并且X的任何真子集都不能函数决定U中的每个属性，则称

7、X为关系R的一个候选码。根据一个关系模式的语义，能够求出它的最小函数依赖集，从而能够得到各属性间的函数依赖关系，以及得到所有的候选码。自反性：若X包含Y，则存在XY。增广性：若XY，则存在XZYZ。传递性：若XY和YZ则存在XZ。合并性：若XY和XZ则存在XYZ。分解性：若XY且Y包含Z，则存在XZ。伪传递性：若XY和WYZ则存在WXZ。复合性：若XY和ZW则存在XZYW。自增性：若XY，则存在WXY。范式 范式的定义范式（Normal Formula：NF）是表结构在优化时必须满足的一些规则最重要的范式包括：第一范式（1NF）是一个关系的最低规范化级别，它确保关系中的每个属性都是单值属性，即

8、不是复合属性。第二范式（2NF）消除了关系中所有非主属性对候选码的部分依赖。若关系中的每个候选码都是单属性，则符合第一范式的关系自然也达到第二范式。第三范式（3NF）消除了关系中所有非主属性对候选码的部分和传递依赖。 Boyce-Code范式（BCNF）实例分析： 假设一个关系为R(A,B,C,D,E)，它的最小函数依赖集为FD= AB,CD, CE ，则该关系的候选码为什么？该关系属于第几范式，请简要地说明理由。若要规范化到高一级的范式，则将得到什么样的关系。解答：该关系的候选码为（A，C）。因为该关系中存在有非主属性对候选码的部分函数数依赖，即AB,CD， CE ，其中B，C和E只依赖于候

9、选码的部分A或C，所以该关系只属于第一范式。 若要规范化到高一级的范式，则需要将关系R根据属性对候选码的部分依赖拆分成两个关系，它们分别为： R1（A，B）和R2（C，D，E），这两个关系达到了BC范式的要求。第一范式 第一范式定义 若关系R中的每个属性（列）都是不可再分的，并且每个属性都是单值的，则称关系R满足第一范式，记为：R1NF属性不可再分：1NF中不能出现子属性属性值为单值：每个单元格中不允许出现一个以上的数据1NF是对关系的最低要求，不满足1NF的关系是非规范化的关系第一范式第二范式第三范式BC范式第一范式 实例分析 提出 / 分析问题 teacherNoteacherNamete

10、acherTitleteacherTelofficeTelhomeTelT001夏玉明讲 师8123498181231231T002李 娟教 授8123498688712136T003王筱琳讲 师8123498188245178T004刘明辉刘明浩副教授8123498186570081T005唐 勇副教授8123498283226315T006黄 洁讲 师8123498282225743T007曾 惠副教授8123498288710038T008赵文波教 授8123498688461136非规范化第一范式 实例分析 问题求解 teacherNoteacherNameteacherTitleof

11、ficeTelhomeTelT001夏玉明讲 师8123498181231231T002李 娟教 授8123498688712136T003王筱琳讲 师8123498188245178T004刘明辉副教授8123498186570081T005唐 勇副教授8123498283226315T006黄 洁讲 师8123498282225743T007曾 惠副教授8123498288710038T008赵文波教 授8123498688461136T009刘明浩副教授8123498186570081第二范式 第二范式定义 若关系R1NF，并且不存在非主属性对候选码的部分函数依赖，则称关系R满足第二范式

12、，记为：R2NF1NF转化为2NF：优先判断主键的规范性将依赖部分键的非主属性和所依赖部分键组成一个关系将依赖整个键的非主属性和整个键组成另一个关系第一范式第二范式第三范式BC范式第二范式 实例分析 提出问题：满足1NF，不满足2NF stuNostuNamestuSexstuAgeclassNoclassCourseNameclassStartTimescoreS001白玉芬女19C001微观经济学2008-3-178S001白玉芬女19C002大学英语2008-3-167S001白玉芬女19C005操作系统2009-4-6S002唐嘉芝女18C001微观经济学2008-3-192S003刘

13、健鹏男21C006数据库原理2009-4-897S004田 洋男19C001微观经济学2008-3-166S004田 洋男19C002大学英语2008-3-165S004田 洋男19C003建筑艺术2008-3-1663S004田 洋男19C004计算机网络2008-3-2571S005申一雅男18C004计算机网络2008-3-2559S005申一雅男18C005操作系统2009-4-6S006范 力男19C005操作系统2009-4-6S007高 红女20C005操作系统2009-4-6S008张埔涛男18C005操作系统2009-4-6S009蒲惠妃女19C005操作系统2009-4-6

14、第二范式 实例分析 分析问题：插入异常：在一个学生未选择任何课程时，该学生信息不能被插入 更新异常：对于给定的学生，学号、性别、姓名和年龄重复多次；对于给定的班级，课程名称、开课时间重复多次 删除异常：当需要删除某个学生的信息时，与该学生相关的班级信息将被删除；删除班级信息时，选该班级课程的学生信息也将被删除，这种删除将造成一些信息的丢失第二范式 实例分析 问题求解stuNostuNamestuSexstuAgeS001白玉芬女19S002唐嘉芝女18S003刘健鹏男21S004田 洋男19S005申一雅男18S006范 力男19S007高 红女20S008张埔涛男18S009蒲惠妃女19cl

15、assNoclassCourseNameclassStartTimeC001微观经济学2008-3-1C002大学英语2008-3-1C003建筑艺术2008-3-16C004计算机网络2008-3-25C005操作系统2009-4-6C006数据库原理2009-4-8stuNoclassNoscoreS001C00178S001C00267S001C005S002C00192S003C00697S004C00166S004C00265S004C00363S004C00471S005C00459S005C005S006C005S007C005S008C005S009C005满足2NF第二范式

16、思 考在按照2NF要求分解之后的关系模式中，确定学生表、班级表和选课表的主键和外键。 第三范式实例分析 分析问题 插入异常：如果一个专业没有学生时不能插入该专业的信息更新异常：对于给定的专业，专业名称和专业带头人姓名重复多次出现，产生了数据冗余，如果一个专业的专业带头人姓名发生变更，则与该专业相关的所有记录都要更新，否则将造成同一专业其带头人不一致，引发更新异常。删除异常：当一个专业只有一个学生时，在删除该学生信息的时候会将该专业的信息全部删除，造成有效数据的丢失，引发删除异常。第三范式 实例分析 问题求解stuNostuNamestuSexstuAgemajorNoS001白玉芬女19M00

17、1S002唐嘉芝女18M001S003刘健鹏男21M001S004田 洋男19M001S005申一雅男18M001S006范 力男19M002S007高 红女20M002S008张埔涛男18M002S009蒲惠妃女19M003满足3NFmajorNomajorNamemajorLeaderM001日语王金宝M002英语陈龙M003阿拉伯语何炅新第三范式思 考在按照3NF要求分解之后的关系模式中，确定学生表和专业表的主键和外键。 实例分析： 假设一个关系为R(A,B,C,D,E)，它的最小函数依赖集为FD= AB,CD, CE ，则该关系的候选码为什么？该关系属于第几范式，请简要地说明理由。若要

18、规范化到高一级的范式，则将得到什么样的关系。解答：该关系的候选码为（A，C）。因为该关系中存在有非主属性对候选码的部分函数数依赖，即AB,CD，CE ，其中B，D和E只依赖于候选码的部分A或C，所以该关系只属于第一范式。 若要规范化到高一级的范式，则需要将关系R根据属性对候选码的部分依赖拆分成两个关系，它们分别为： R1（A，B）和R2（C，D，E），这两个关系达到了3NF的要求。BC范式 BC范式定义若关系R1NF，并且它的决定因子都是候选键，则称R满足BC范式，记为：RBCNF第一范式第二范式第三范式BC范式BC范式 实例分析 提出问题：满足3NF，不满足BCNFdepNodepNamep

19、roductNoaccountD001外语系P001100D001外语系P002120D001外语系P0032D001外语系P0046D002工商系P00180D002工商系P002100D003计算机系P00178D003计算机系P002160D004建筑系P00162BC范式实例分析 分析问题 该关系中存在如下函数依赖：(depNo, productNo) account(depName, productNo ) accountdepNodepNamedepNamedepNo候选键包括：（depNo, productNo）和（depName，productNo）主属性包括：depNo、de

20、pName和productNo非主属性包括：accountBC范式 实例分析 分析问题 BCNF转换方法将产生函数依赖关系的主属性组成一个新关系组合剩余的属性，合并相同的元组BC范式 实例分析 问题求解depNoproductNoaccountD001P001100D001P002120D001P0032D001P0046D002P00180D002P002100D003P00178D003P002160D004P00162满足BCNFdepNodepNameD001外语系D002工商系D003计算机系D004建筑系逆规范化 通过规范化可以分解得到一些结构简单的表，但是在查询时可能需要连接多个

21、表，这造成了大量的数据库逻辑运算，从而降低了查询效率为了提高查询效率，可以在特定的表中引入一些额外的属性数据库设计允许存在合理的冗余逆规范化逆规范化的定义 将两个或多个满足高范式的关系模式通过自然连接，重新合并成一个满足较低范式的关系模式的过程称为逆规范化规范化和逆规范化是相互矛盾的，何时进行规范化、何时进行逆规范化、进行到什么程度，在具体的应用环境中，需要数据库设计者仔细分析和平衡逆规范化需要设计者在关系中有意地引入一些冗余以改进系统的查询性能逆规范化实例分析 提出问题 stuNostuNamestuSexstuAgeS001白玉芬女19S002唐嘉芝女18stuNoclassNoscore

22、S001C00178S001C00267S001C00356S002C00192classNoclassCourseNameclassStartTimeclassCreditC001微观经济学2008-3-12C002大学英语2008-3-14C003建筑艺术2008-3-162 计算stuNo= S001学生的已获学分，需要对三个表进行连接逆规范化实例分析 分析问题 查询和计算过程在经过数据库编译后，将会变成一个比较繁琐的程序在后台运行，尤其在数据库记录非常多、用户访问量很大的情况下将会造成用户难以忍受的延迟。在表中适当增加一定冗余，可以减少数据库服务器的负荷，提高查询速度，如果需要经常查询和统计学生已获得学分，可以在原来的学生表中增加一个新的字段stu