数据库规范化理论PPT学习教案

1、会计学1 数据库规范化理论数据库规范化理论 2007年4月19日星期四2 示例示例: : 考虑为管理职工的工资信息而设计一个关系模式考虑为管理职工的工资信息而设计一个关系模式 。 职工职工 级别级别 工资工资 赵明赵明 4 400 钱广钱广 5 500 孙志孙志 6 600 李开李开 5 500 周祥周祥 6 600 数据库原理与应用数据库原理与应用Principle and Application of DataBase 第1页/共35页 2007年4月19日星期四3 在表中包含着两类信息：在表中包含着两类信息： 职工个人的工资信息；职工个人的工资信息； 各个级别的工资数额。各个级别的工资数

2、额。 数据库原理与应用数据库原理与应用Principle and Application of DataBase 问题：问题： 如果我希望知道在这个单位如果我希望知道在这个单位8 8级工的工资是级工的工资是 多少，能否查询到？多少，能否查询到？ 职职工工 级级别别 工工资资 赵赵明明 4 400 钱钱广广 5 500 孙孙志志 6 600 李李开开 5 500 周周祥祥 6 600 第2页/共35页 2007年4月19日星期四4 l问题：问题： 插入异常：插入异常：如果没有职工具有如果没有职工具有8 8 级工资，则级工资，则8 8 级级 工资的工资数额就难以插入工资的工资数额就难以插入 数据库

3、原理与应用数据库原理与应用Principle and Application of DataBase l 删除异常：删除异常：如果仅有职工赵明具有如果仅有职工赵明具有4 4级工资，级工资， 如果将赵明删除，则有关如果将赵明删除，则有关4 4级工资的工资数额信息级工资的工资数额信息 也随之删除了。也随之删除了。 职职工工 级级别别 工工资资 赵赵明明 4 400 钱钱广广 5 500 孙孙志志 6 600 李李开开 5 500 周周祥祥 6 600 第3页/共35页 2007年4月19日星期四5 数据冗余：数据冗余：职工很多，工资级别有限，每一级别职工很多，工资级别有限，每一级别 的工资数额反复

4、存储多次。的工资数额反复存储多次。 数据库原理与应用数据库原理与应用Principle and Application of DataBase 更新异常：更新异常：如果将如果将5 5级工资的工资数额调为级工资的工资数额调为620620 ，则需要找到每个具有，则需要找到每个具有5 5级工资的职工，逐一修级工资的职工，逐一修 改。改。 职职工工 级级别别 工工资资 赵赵明明 4 400 钱钱广广 5 500 孙孙志志 6 600 李李开开 5 500 周周祥祥 6 600 第4页/共35页 2007年4月19日星期四6 l解决之道：解决之道：分解分解 ! ! 职工职工 级别级别 赵明赵明 4 钱广

5、钱广 5 孙志孙志 6 李开李开 5 周祥周祥 6 级别级别 工资工资 4 400 5 500 6 600 7 700 8 800 数据库原理与应用数据库原理与应用Principle and Application of DataBase 职职工工 级级别别 工工资资 赵赵明明 4 400 钱钱广广 5 500 孙孙志志 6 600 李李开开 5 500 周周祥祥 6 600 第5页/共35页 2007年4月19日星期四7 有关学生的关系模式 S(学号 , 姓名 , 系号 , 主任 , 课程编号 , 成绩 ) 学学号号 姓姓名名 系系号号 主主任任 课课号号 成成绩绩 S S0 01 1 杨杨

6、明明 D D0 01 1 李李齐齐 C C0 01 1 9 90 0 S S0 02 2 李李婉婉 D D0 01 1 李李齐齐 C C0 01 1 8 87 7 S S0 01 1 杨杨明明 D D0 01 1 李李齐齐 C C0 02 2 9 92 2 S S0 03 3 刘刘海海 D D0 02 2 张张圣圣 C C0 01 1 9 95 5 S S0 04 4 安安然然 D D0 02 2 张张圣圣 C C0 02 2 7 78 8 S S0 05 5 乐乐天天 D D0 03 3 王王丽丽 C C0 01 1 8 82 2 它有哪些数据冗余？它有哪些数据冗余？ 数据库原理与应用数据库

7、原理与应用Principle and Application of DataBase 第6页/共35页 2007年4月19日星期四8 规范化理论规范化理论 问题的提出问题的提出 针对一个具体问题，如何构造一个合适的数针对一个具体问题，如何构造一个合适的数 据模式。即应该构造几个关系模式（表），每个据模式。即应该构造几个关系模式（表），每个 关系有那些属性组成？关系有那些属性组成？ 数据库原理与应用数据库原理与应用Principle and Application of DataBase 第7页/共35页 2007年4月19日星期四9 数据库原理与应用数据库原理与应用Principle and

8、Application of DataBase 定定 义：义：设设R(U)R(U)是属性集是属性集U U上的关系模式。上的关系模式。X X，Y Y是是U U的子集。若对于的子集。若对于R(U)R(U)的任意一个可能的关系的任意一个可能的关系r r，r r中不可能存在两个元组在中不可能存在两个元组在X X上的属性值相等，而在上的属性值相等，而在Y Y上的属性值不等，则称上的属性值不等，则称X X函数确定函数确定Y Y或或Y Y函数依赖于函数依赖于X X，记为，记为XYXY。 记号记号 xy xy 称称x x函数确定函数确定y y，或，或y y函数依赖于函数依赖于x x。称称X X为决定因素。为决

9、定因素。 如如 学号学号 姓名，姓名， （学号，课程）（学号，课程） 成绩成绩 第8页/共35页 2007年4月19日星期四10 注注 意：意：函数依赖是语义范畴的概念，我们只能根据语义来确定函数依赖。例如在没有同名的情况下，函数依赖是语义范畴的概念，我们只能根据语义来确定函数依赖。例如在没有同名的情况下，姓名姓名年龄年龄 是成立的，而在有同名的情况下，这个函数依赖就不成立了。是成立的，而在有同名的情况下，这个函数依赖就不成立了。 平凡函数依赖：平凡函数依赖：如果如果X X Y Y，但，但Y Y不是不是X X的子集，则称其为非平凡的函数依赖，否则称为平凡的函数依赖。的子集，则称其为非平凡的函数

10、依赖，否则称为平凡的函数依赖。 如如（学号，姓名）（学号，姓名） 姓名姓名 是平凡的函数依赖是平凡的函数依赖 数据库原理与应用数据库原理与应用Principle and Application of DataBase 第9页/共35页 2007年4月19日星期四11 函数依赖可分为三类：函数依赖可分为三类：完全函数依赖，部分函数依赖和传递函数依赖完全函数依赖，部分函数依赖和传递函数依赖。 定定 义：义：在在R(U)R(U)中有中有X X、Y Y U U，如果，如果XYXY，并且对于，并且对于X X的任何一个真子集的任何一个真子集XX，都有，都有Y Y不函数依赖于不函数依赖于XX，则称，则称Y

11、Y对对X X是完全函数依赖的。是完全函数依赖的。 定定 义：义：在在R(U)R(U)中，如果中，如果XYXY，并且对于，并且对于X X的某个真子集的某个真子集XX，有，有XYXY，则称，则称Y Y对对X X部分函数依赖。部分函数依赖。 定定 义：义：在在R(U)R(U)中，如果中，如果XYXY（Y Y不包含于不包含于X X，X X不依赖于不依赖于Y Y ），且），且 YZYZ，则称，则称Z Z对对X X传递函数依赖。传递函数依赖。 数据库原理与应用数据库原理与应用Principle and Application of DataBase 第10页/共35页 2007年4月19日星期四12 例例

12、 1 1：某单位有一资料室，它管理的数据有：某单位有一资料室，它管理的数据有读者读者 信息、图书信息、借阅信息信息、图书信息、借阅信息。 读者信息读者信息：借书证号，读者姓名，性别，部门，借书证号，读者姓名，性别，部门， 学历，部门电话，个人电话，电子信箱等；学历，部门电话，个人电话，电子信箱等； 图书信息图书信息：图书编号，分类号，书名，作者，出图书编号，分类号，书名，作者，出 版社，单价等；版社，单价等； 借阅信息借阅信息：借书证号，图书编号，书名，借出日：借书证号，图书编号，书名，借出日 期，应还日期等。期，应还日期等。 数据库原理与应用数据库原理与应用Principle and App

13、lication of DataBase 第11页/共35页 2007年4月19日星期四13 函数依赖关系函数依赖关系（读者信息）（读者信息）： 借书证号借书证号读者姓名读者姓名 借书证号借书证号性别性别 借书证号借书证号部门部门 借书证号借书证号学历学历 部门部门部门电话部门电话 借书证号借书证号个人电话个人电话 借书证号借书证号电子信箱电子信箱 数据库原理与应用数据库原理与应用Principle and Application of DataBase 第12页/共35页 2007年4月19日星期四14 函数依赖关系函数依赖关系（图书信息）（图书信息）： 图书编号图书编号分类号分类号 图书编

14、号图书编号书名书名 图书编号图书编号作者作者 图书编号图书编号出版社出版社 图书编号图书编号单价单价 函数依赖关系函数依赖关系（借阅信息）（借阅信息）： 图书编号图书编号书名书名 借书证号、图书编号，借出日期借书证号、图书编号，借出日期应还日期应还日期 数据库原理与应用数据库原理与应用Principle and Application of DataBase 第13页/共35页 2007年4月19日星期四15 多值依赖多值依赖 函数依赖关系是一种关系模式内属性间较为明显的依赖关系，但是随着人们对关系模式的了解越来越深刻，发现尚有另外的一些依赖关系存在，多值依赖就是其中的一种。例如，有一个课程关

15、系函数依赖关系是一种关系模式内属性间较为明显的依赖关系，但是随着人们对关系模式的了解越来越深刻，发现尚有另外的一些依赖关系存在，多值依赖就是其中的一种。例如，有一个课程关系COURSECOURSE（如下表所示）。（如下表所示）。 课程名课程名CNAMECNAME教师名教师名TEACHTEACH选用参考书选用参考书BOOKBOOK 数据库原理数据库原理李勇李勇 张明张明 王阳王阳 数据库管理系统数据库管理系统 数据库系统数据库系统 数据结构数据结构任学琴任学琴 李瑞李瑞 张文其张文其 数据结构习题集数据结构习题集 算法分析算法分析 数据结构上机指导数据结构上机指导 数据库原理与应用数据库原理与应

16、用Principle and Application of DataBase 第14页/共35页 2007年4月19日星期四16 CNAMETEACHBOOK 数据库原理数据库原理李勇李勇数据库管理系统数据库管理系统 数据库原理数据库原理李勇李勇数据库系统数据库系统 数据库原理数据库原理张明张明数据库管理系统数据库管理系统 数据库原理数据库原理张明张明数据库系统数据库系统 数据库原理数据库原理王阳王阳数据库管理系统数据库管理系统 数据库原理数据库原理王阳王阳数据库系统数据库系统 数据结构数据结构任学琴任学琴数据结构习题集数据结构习题集 数据结构数据结构任学琴任学琴算法分析算法分析 数据结构数据

17、结构任学琴任学琴数据结构上机指导数据结构上机指导 数据结构数据结构李瑞李瑞数据结构习题集数据结构习题集 数据结构数据结构李瑞李瑞算法分析算法分析 数据结构数据结构李瑞李瑞数据结构上机指导数据结构上机指导 数据结构数据结构张文其张文其数据结构习题集数据结构习题集 数据结构数据结构张文其张文其算法分析算法分析 数据结构数据结构张文其张文其数据结构上机指导数据结构上机指导 从这个关系中可以从这个关系中可以 看出两点看出两点: : (1) (1) 该关系的数据该关系的数据 冗余很大。冗余很大。 (2) (2) 该关系的属性该关系的属性 间有一种有别于函数间有一种有别于函数 依赖的依赖关系存在依赖的依赖

18、关系存在 。 第15页/共35页 2007年4月19日星期四17 范式理论范式理论 1NF1NF：任一属性不能同时具有多个值（：任一属性不能同时具有多个值（关系中每一分量不可再分。即不能以集合、序列等作为属性值关系中每一分量不可再分。即不能以集合、序列等作为属性值）。）。 2NF2NF：属性必须完全依赖唯一标识符。：属性必须完全依赖唯一标识符。 3NF3NF：属性间不存在传递依赖。：属性间不存在传递依赖。 BCNFBCNF：每一个决定因素都包含码。：每一个决定因素都包含码。 数据库原理与应用数据库原理与应用Principle and Application of DataBase 第16页/共

19、35页 2007年4月19日星期四18 例例 2 2 ： R R( (学号，姓名，课程编号，课程名称，学分，成绩学号，姓名，课程编号，课程名称，学分，成绩 ) ) 唯一标识符唯一标识符(Key)(Key)： ( (学号，课程编号学号，课程编号) ) 数据库原理与应用数据库原理与应用Principle and Application of DataBase 不符合不符合2NF2NF 依赖关系：依赖关系： 学号学号姓名，课程编号姓名，课程编号 课程名称，课程名称， 课程编号课程编号学分学分 ，( (学号，课程编号学号，课程编号) ) 成绩成绩 第17页/共35页 2007年4月19日星期四19 例

20、例 3 3 ： S S( (学号，姓名，性别，学院，院长学号，姓名，性别，学院，院长) )。 唯一标识符唯一标识符(Key)(Key)： 学号学号 数据库原理与应用数据库原理与应用Principle and Application of DataBase 不符合不符合3NF3NF 依赖关系：依赖关系： 学号学号姓名，学号姓名，学号性别，学号性别，学号学院，学院， 学院学院院长院长 第18页/共35页 2007年4月19日星期四20 数据库原理与应用数据库原理与应用Principle and Application of DataBase 问题的解决办法：问题的解决办法：拆分关系（表）拆分关系（

21、表） 第19页/共35页 2007年4月19日星期四21 关于例关于例 2 2 R(R(学号学号，姓名，姓名，课程编号课程编号，课程名称，学分，成绩，课程名称，学分，成绩) ) R1 (R1 (学号学号，姓名姓名) ) R2(R2(课程编号课程编号，课程名称，学分课程名称，学分) ) R3(R3(学号学号，课程编号课程编号，成绩成绩) ) 学号学号姓名，课程编号姓名，课程编号 课程名称，课程编号课程名称，课程编号学分学分, , ( (学号，课程编号学号，课程编号) ) 成绩成绩 数据库原理与应用数据库原理与应用Principle and Application of DataBase 第20页

22、/共35页 2007年4月19日星期四22 关于例关于例 3 3 S(S(学号学号，姓名，性别，学院，院长，姓名，性别，学院，院长) ) S1(S1(学号学号，姓名，性别，学院，姓名，性别，学院) ) S2(S2(学院学院，院长，院长) ) 学号学号姓名，学号姓名，学号性别，学号性别，学号学院，学院学院，学院院长院长 数据库原理与应用数据库原理与应用Principle and Application of DataBase 第21页/共35页 2007年4月19日星期四23 例例 4 4：某部队拟建立干部档案，数据项有：某部队拟建立干部档案，数据项有： 编号，姓名，现军衔，现任职务，入伍日期，

23、最编号，姓名，现军衔，现任职务，入伍日期，最 高学历，低级军衔及获得日期，曾担任职务及任高学历，低级军衔及获得日期，曾担任职务及任 命日期，所取得各学历及取得日期。命日期，所取得各学历及取得日期。 数据库原理与应用数据库原理与应用Principle and Application of DataBase 第22页/共35页 2007年4月19日星期四24 函数依赖关系：函数依赖关系： 编号编号姓名，编号姓名，编号现军衔，编号现军衔，编号现任职务，现任职务， 编号编号入伍日期，编号入伍日期，编号最高学历，编号最高学历，编号之前之前 军衔，军衔，( (编号编号, ,低级军衔低级军衔)获得日期，编号

24、获得日期，编号曾担曾担 任职务，任职务，( (编号编号, ,曾担任职务曾担任职务)任命日期，编号任命日期，编号 各学历，各学历，( (编号编号, ,各学历各学历)取得日期取得日期 数据库原理与应用数据库原理与应用Principle and Application of DataBase 第23页/共35页 2007年4月19日星期四25 表表1 1（编号编号，姓名，现军衔，现任职务，入伍日，姓名，现军衔，现任职务，入伍日 期，最高学历）期，最高学历） 表表2 2（编号编号，低级军衔低级军衔，获得日期），获得日期） 表表3 3（编号编号，曾担任职务曾担任职务，任命日期），任命日期） 表表4 4（

25、编号编号，学历学历，取得日期）。，取得日期）。 数据库原理与应用数据库原理与应用Principle and Application of DataBase 第24页/共35页 2007年4月19日星期四26 规范化步骤规范化步骤 2NF 3NF2NF 3NF BCNF 4NFBCNF 4NF 数据库原理与应用数据库原理与应用Principle and Application of DataBase 规范化的目的就是构造合规范化的目的就是构造合 适的关系模式。适的关系模式。 第25页/共35页 2007年4月19日星期四27 数据库原理与应用数据库原理与应用Principle and Appli

26、cation of DataBase 3NF 3NF 2 2NFNF。 反证：若反证：若R R 3NF3NF， 但但R R 2 2NFNF，则按，则按2NF2NF定义，一定有非主属性部分依赖于码；定义，一定有非主属性部分依赖于码； 设设X X为为R R的码，则存在的码，则存在X X的真子集的真子集S S，以及非主属性，以及非主属性Z Z （ 其中其中S S 不包含于不包含于Z Z ），），使得使得S Z Z 成立；成立； 于是在于是在R R中存在码中存在码X X，属性组，属性组S S，以及非主属性，以及非主属性Z Z，使得，使得X XS S，S SZ Z 成立，进而有成立，进而有 S SX X

27、成立；成立； 这与这与R R 3NF3NF矛盾，矛盾， 所以所以R R 2 2NFNF。 第26页/共35页 2007年4月19日星期四28 数据库原理与应用数据库原理与应用Principle and Application of DataBase 关系模式关系模式R(U)R(U)若满足若满足BCNFBCNF，则必定，则必定 满足满足3NF3NF。 证明略，请大家看参考书。证明略，请大家看参考书。 第27页/共35页 2007年4月19日星期四29 l实例实例 表（职工，级别，工资）可以有两种分解途径，表（职工，级别，工资）可以有两种分解途径， 分解一：（职工，工资），（工资，级别）分解一：（

28、职工，工资），（工资，级别） 姓名姓名级别级别工资工资 赵赵4500 钱钱5600 孙孙6700 李李7600 姓名工资 赵500 钱600 孙700 李600 级别工资 4500 5600 6700 7600 丢失函丢失函 数依赖数依赖 分解二：（职工，级别），（工资，级别）分解二：（职工，级别），（工资，级别） 不同行业机构的不同工资级别会有相同工资数额。按分解一，有可能导致同一职工对应不同的工资级别，从而丢失了有关职工工资级别的信息（不同行业机构的不同工资级别会有相同工资数额。按分解一，有可能导致同一职工对应不同的工资级别，从而丢失了有关职工工资级别的信息（丢失了函数依赖：职工丢失了函数依赖：职工级别级别）。 第28页/共35页 2007年4月19日星期四30 R(A, B, C) ABC 112 221 AB 11 22 BC 12 21 ABC 112 221 AB(R)BC(R)AB(R)BC(R ) R(A, B, C) ABC 111 212 AB 11 21 BC 11 12 ABC 111 112 211 212 AB(R)BC(R)AB(R)BC(R ) 有损分解有损分解 无损分解无损分解 第29页/共35页 2007年4月19日星期四31 将将R R分解为分解为R1R1和和R2R2的分解是无损连接分解的条的分解是无