数据库-规范化理论.ppt

数据库设计数据库设计 规范化理论规范化理论数据库原理与应用数据库原理与应用Principle and Application of DataBase 2007年4月19日星期四1兰州理工大学计算机与通信学院兰州理工大学计算机与通信学院关系数据库设计中存在的问题关系数据库设计中存在的问题关系数据库设计中存在的问题关系数据库设计中存在的问题 示例示例:考虑为管理职工的工资信息而设计一个关系模式。考虑为管理职工的工资信息而设计一个关系模式。数据库原理与应用数据库原理与应用Principle and Application of DataBase 2007年4月19日星期四2兰州理工大学计算机与通信学院兰州理工大学计算机与通信学院在表在表中中包含着两类信息：包含着两类信息：职工个人的工资信息；职工个人的工资信息；各个级别的工资数额。各个级别的工资数额。数据库原理与应用数据库原理与应用Principle and Application of DataBase 问题：问题：如果我希望知道在这个单位如果我希望知道在这个单位8 8级工的工资是级工的工资是多少，能否查询到？多少，能否查询到？2007年4月19日星期四3兰州理工大学计算机与通信学院兰州理工大学计算机与通信学院l问题：问题：插入异常：插入异常：如果没有职工具有如果没有职工具有8 8 级工资，则级工资，则8 8 级级工资的工资数额就难以插入工资的工资数额就难以插入 数据库原理与应用数据库原理与应用Principle and Application of DataBase l 删除异常：删除异常：如果仅有职工赵明具有如果仅有职工赵明具有4 4级工资，如级工资，如果将赵明删除，则有关果将赵明删除，则有关4 4级工资的工资数额信息也随级工资的工资数额信息也随之删除了。之删除了。2007年4月19日星期四4兰州理工大学计算机与通信学院兰州理工大学计算机与通信学院 数据冗余：数据冗余：职工很多，工资级别有限，每一级别职工很多，工资级别有限，每一级别的工资数额反复存储多次。的工资数额反复存储多次。数据库原理与应用数据库原理与应用Principle and Application of DataBase 更新异常：更新异常：如果将如果将5 5级工资的工资数额调为级工资的工资数额调为620620，则需要找到每个具有则需要找到每个具有5 5级工资的职工，逐一修改。级工资的职工，逐一修改。2007年4月19日星期四5兰州理工大学计算机与通信学院兰州理工大学计算机与通信学院l解决之道：解决之道：分解分解 !数据库原理与应用数据库原理与应用Principle and Application of DataBase 2007年4月19日星期四6兰州理工大学计算机与通信学院兰州理工大学计算机与通信学院有关学生的关系模式S(学号,姓名,系号,主任,课程编号,成绩)它有哪些数据冗余？它有哪些数据冗余？数据库原理与应用数据库原理与应用Principle and Application of DataBase 2007年4月19日星期四7兰州理工大学计算机与通信学院兰州理工大学计算机与通信学院 规范化理论规范化理论问题的提出问题的提出 针对一个具体问题，如何构造一个合适的数针对一个具体问题，如何构造一个合适的数据模式。即应该构造几个关系模式（表），每个据模式。即应该构造几个关系模式（表），每个关系有那些属性组成？关系有那些属性组成？数据库原理与应用数据库原理与应用Principle and Application of DataBase 2007年4月19日星期四8兰州理工大学计算机与通信学院兰州理工大学计算机与通信学院数据库原理与应用数据库原理与应用Principle and 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。记号记号 xyxy 称称x x函数确定函数确定y y，或，或y y函数依赖于函数依赖于x x。称称X X为为决定因素。决定因素。如如 学号学号 姓名，姓名，（学号，课程）（学号，课程）成绩成绩 2007年4月19日星期四9兰州理工大学计算机与通信学院兰州理工大学计算机与通信学院注注 意：意：函数依赖是语义范畴的概念，我们只能根据语义函数依赖是语义范畴的概念，我们只能根据语义来确定函数依赖。例如在没有同名的情况下，来确定函数依赖。例如在没有同名的情况下，姓名姓名年龄年龄 是成立的，而在有同名的情况下，这个函数依赖就不成立是成立的，而在有同名的情况下，这个函数依赖就不成立了。了。平凡函数依赖：平凡函数依赖：如果如果X X Y Y，但，但Y Y不是不是X X的子集，则称其为的子集，则称其为非平凡的函数依赖，否则称为平凡的函数依赖。非平凡的函数依赖，否则称为平凡的函数依赖。如如（学号，姓名）（学号，姓名）姓名姓名 是平凡的函数依赖是平凡的函数依赖数据库原理与应用数据库原理与应用Principle and Application of DataBase 2007年4月19日星期四10兰州理工大学计算机与通信学院兰州理工大学计算机与通信学院 函数依赖可分为三类：函数依赖可分为三类：完全函数依赖，部分函数依赖和完全函数依赖，部分函数依赖和传递函数依赖传递函数依赖。定定 义：义：在在R(U)R(U)中有中有X X、Y Y U U，如果，如果XYXY，并且对于，并且对于X X的任的任何一个真子集何一个真子集XX，都有，都有Y Y不函数依赖于不函数依赖于XX，则称，则称Y 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 2007年4月19日星期四11兰州理工大学计算机与通信学院兰州理工大学计算机与通信学院例例 1 1：某单位有一资料室，它管理的数据有：某单位有一资料室，它管理的数据有读者读者信息、图书信息、借阅信息信息、图书信息、借阅信息。读者信息读者信息：借书证号，读者姓名，性别，部门，借书证号，读者姓名，性别，部门，学历，部门电话，个人电话，电子信箱等；学历，部门电话，个人电话，电子信箱等；图书信息图书信息：图书编号，分类号，书名，作者，出图书编号，分类号，书名，作者，出版社，单价等；版社，单价等；借阅信息借阅信息：借书证号，图书编号，书名，借出日：借书证号，图书编号，书名，借出日期，应还日期等。期，应还日期等。数据库原理与应用数据库原理与应用Principle and Application of DataBase 2007年4月19日星期四12兰州理工大学计算机与通信学院兰州理工大学计算机与通信学院函数依赖关系函数依赖关系（读者信息）（读者信息）：借书证号借书证号读者姓名读者姓名 借书证号借书证号性别性别 借书证号借书证号部门部门 借书证号借书证号学历学历 部门部门部门电话部门电话 借书证号借书证号个人电话个人电话 借书证号借书证号电子信箱电子信箱数据库原理与应用数据库原理与应用Principle and Application of DataBase 2007年4月19日星期四13兰州理工大学计算机与通信学院兰州理工大学计算机与通信学院函数依赖关系函数依赖关系（图书信息）（图书信息）：图书编号图书编号分类号分类号 图书编号图书编号书名书名 图书编号图书编号作者作者 图书编号图书编号出版社出版社 图书编号图书编号单价单价函数依赖关系函数依赖关系（借阅信息）（借阅信息）：图书编号图书编号书名书名 借书证号、图书编号，借出日期借书证号、图书编号，借出日期应还日期应还日期数据库原理与应用数据库原理与应用Principle and Application of DataBase 2007年4月19日星期四14兰州理工大学计算机与通信学院兰州理工大学计算机与通信学院多值依赖多值依赖 函数依赖关系是一种关系模式内属性间较为明显的依函数依赖关系是一种关系模式内属性间较为明显的依赖关系，但是随着人们对关系模式的了解越来越深刻，发赖关系，但是随着人们对关系模式的了解越来越深刻，发现尚有另外的一些依赖关系存在，多值依赖就是其中的一现尚有另外的一些依赖关系存在，多值依赖就是其中的一种。例如，有一个课程关系种。例如，有一个课程关系COURSECOURSE（如下表所示）。（如下表所示）。课课程名程名CNAMECNAME教教师师名名TEACHTEACH选选用参考用参考书书BOOKBOOK数据数据库库原理原理李勇李勇张张明明王阳王阳数据数据库库管理系管理系统统数据数据库库系系统统数据数据结结构构任学琴任学琴李瑞李瑞张张文其文其数据数据结结构构习题习题集集算法分析算法分析数据数据结结构上机指构上机指导导数据库原理与应用数据库原理与应用Principle and Application of DataBase 2007年4月19日星期四15兰州理工大学计算机与通信学院兰州理工大学计算机与通信学院CNAMETEACHBOOK数据数据库库原理原理李勇李勇数据数据库库管理系管理系统统数据数据库库原理原理李勇李勇数据数据库库系系统统数据数据库库原理原理张张明明数据数据库库管理系管理系统统数据数据库库原理原理张张明明数据数据库库系系统统数据数据库库原理原理王阳王阳数据数据库库管理系管理系统统数据数据库库原理原理王阳王阳数据数据库库系系统统数据数据结结构构任学琴任学琴数据数据结结构构习题习题集集数据数据结结构构任学琴任学琴算法分析算法分析数据数据结结构构任学琴任学琴数据数据结结构上机指构上机指导导数据数据结结构构李瑞李瑞数据数据结结构构习题习题集集数据数据结结构构李瑞李瑞算法分析算法分析数据数据结结构构李瑞李瑞数据数据结结构上机指构上机指导导数据数据结结构构张张文其文其数据数据结结构构习题习题集集数据数据结结构构张张文其文其算法分析算法分析数据数据结结构构张张文其文其数据数据结结构上机指构上机指导导从这个关系中可以从这个关系中可以看出两点看出两点:(1)(1)该关系的数据冗该关系的数据冗余很大。余很大。(2)(2)该关系的属性间该关系的属性间有一种有别于函数依有一种有别于函数依赖的依赖关系存在。赖的依赖关系存在。2007年4月19日星期四16兰州理工大学计算机与通信学院兰州理工大学计算机与通信学院范式理论范式理论1NF1NF：任一属性不能同时具有多个值（任一属性不能同时具有多个值（关系中每一关系中每一分量不可再分。即不能以集合、序列等作为属性值分量不可再分。即不能以集合、序列等作为属性值）。）。2NF2NF：属性必须完全依赖唯一标识符。属性必须完全依赖唯一标识符。3NF3NF：属性间不存在传递依赖。属性间不存在传递依赖。BCNFBCNF：每一个决定因素都包含码。每一个决定因素都包含码。数据库原理与应用数据库原理与应用Principle and Application of DataBase 2007年4月19日星期四17兰州理工大学计算机与通信学院兰州理工大学计算机与通信学院例例 2 2：R R(学号，姓名，课程编号，课程名称，学分，成绩学号，姓名，课程编号，课程名称，学分，成绩)唯一标识符唯一标识符(Key)(Key)：(学号，课程编号学号，课程编号)数据库原理与应用数据库原理与应用Principle and Application of DataBase 不符合不符合2NF2NF依赖关系：依赖关系：学号学号姓名，课程编号姓名，课程编号 课程名称，课程名称，课程编号课程编号学分学分 ，(学号，课程编号学号，课程编号)成绩成绩2007年4月19日星期四18兰州理工大学计算机与通信学院兰州理工大学计算机与通信学院例例 3 3：S S(学号，姓名，性别，学院，院长学号，姓名，性别，学院，院长)。唯一标识符唯一标识符(Key)(Key)：学号学号 数据库原理与应用数据库原理与应用Principle and Application of DataBase 不符合不符合3NF3NF依赖关系：依赖关系：学号学号姓名，学号姓名，学号性别，学号性别，学号学院，学院，学院学院院长院长2007年4月19日星期四19兰州理工大学计算机与通信学院兰州理工大学计算机与通信学院数据库原理与应用数据库原理与应用Principle and Application of DataBase 问题的解决办法：问题的解决办法：拆分关系（表）拆分关系（表）2007年4月19日星期四20兰州理工大学计算机与通信学院兰州理工大学计算机与通信学院关于例关于例 2 2R(R(学号学号，姓名，姓名，课程编号课程编号，课程名称，学分，成绩，课程名称，学分，成绩)R1(R1(学号学号，姓名姓名)R2(R2(课程编号课程编号，课程名称，学分课程名称，学分)R3(R3(学号学号，课程编号课程编号，成绩成绩)学号学号姓名，课程编号姓名，课程编号 课程名称，课程编号课程名称，课程编号学分学分,(学号，课程编号学号，课程编号)成绩成绩数据库原理与应用数据库原理与应用Principle and Application of DataBase 2007年4月19日星期四21兰州理工大学计算机与通信学院兰州理工大学计算机与通信学院关于例关于例 3 3S(S(学号学号，姓名，性别，学院，院长，姓名，性别，学院，院长)S1(S1(学号学号，姓名，性别，学院，姓名，性别，学院)S2(S2(学院学院，院长，院长)学号学号姓名，学号姓名，学号性别，学号性别，学号学院，学院学院，学院院长院长数据库原理与应用数据库原理与应用Principle and Application of DataBase 2007年4月19日星期四22兰州理工大学计算机与通信学院兰州理工大学计算机与通信学院例例 4 4：某部队拟建立干部档案，数据项有：某部队拟建立干部档案，数据项有：编号，姓名，现军衔，现任职务，入伍日期，最编号，姓名，现军衔，现任职务，入伍日期，最高学历，低级军衔及获得日期，曾担任职务及任高学历，低级军衔及获得日期，曾担任职务及任命日期，所取得各学历及取得日期。命日期，所取得各学历及取得日期。数据库原理与应用数据库原理与应用Principle and Application of DataBase 2007年4月19日星期四23兰州理工大学计算机与通信学院兰州理工大学计算机与通信学院函数依赖关系：函数依赖关系：编号编号姓名，编号姓名，编号现军衔，编号现军衔，编号现任职务，现任职务，编号编号入伍日期，编号入伍日期，编号最高学历，编号最高学历，编号之前之前军衔，军衔，(编号编号,低级军衔低级军衔)获得日期，编号获得日期，编号曾担曾担任职务，任职务，(编号编号,曾担任职务曾担任职务)任命日期，编号任命日期，编号各学历，各学历，(编号编号,各学历各学历)取得日期取得日期数据库原理与应用数据库原理与应用Principle and Application of DataBase 2007年4月19日星期四24兰州理工大学计算机与通信学院兰州理工大学计算机与通信学院表表1 1（编编号号，姓姓名名，现现军军衔衔，现现任任职职务务，入入伍伍日日期期，最高学历）最高学历）表表2 2（编号编号，低级军衔低级军衔，获得日期），获得日期）表表3 3（编号编号，曾担任职务曾担任职务，任命日期），任命日期）表表4 4（编号编号，学历学历，取得日期）。，取得日期）。数据库原理与应用数据库原理与应用Principle and Application of DataBase 2007年4月19日星期四25兰州理工大学计算机与通信学院兰州理工大学计算机与通信学院规范化步骤规范化步骤2NF 3NF2NF 3NF BCNF 4NFBCNF 4NF数据库原理与应用数据库原理与应用Principle and Application of DataBase 规范化的目的就是构造规范化的目的就是构造合适的关系模式。合适的关系模式。2007年4月19日星期四26兰州理工大学计算机与通信学院兰州理工大学计算机与通信学院数据库原理与应用数据库原理与应用Principle and Application 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成立；成立；这与这与R R 3NF3NF矛盾，矛盾，所以所以R R 2 2NFNF。2007年4月19日星期四27兰州理工大学计算机与通信学院兰州理工大学计算机与通信学院数据库原理与应用数据库原理与应用Principle and Application of DataBase 定定定定 理：理：理：理：关系模式关系模式R(U)R(U)若满足若满足BCNFBCNF，则必定，则必定满足满足3NF3NF。证明略，请大家看参考书。证明略，请大家看参考书。2007年4月19日星期四28兰州理工大学计算机与通信学院兰州理工大学计算机与通信学院模式分解中的问题模式分解中的问题l实例实例表（职工，级别，工资）可以有两种分解途径，表（职工，级别，工资）可以有两种分解途径，分解一：（职工，工资），（工资，级别）分解一：（职工，工资），（工资，级别）姓名姓名级别级别工资工资赵赵4500钱钱5600孙孙6700李李7600姓名工资赵500钱600孙700李600级别工资4500560067007600丢失函丢失函数依赖数依赖分解二：（职工，级别），（工资，级别）分解二：（职工，级别），（工资，级别）不同行业机构的不同工资级别会有相同工资数额。按分不同行业机构的不同工资级别会有相同工资数额。按分解一，有可能导致同一职工对应不同的工资级别，从而丢失了解一，有可能导致同一职工对应不同的工资级别，从而丢失了有关职工工资级别的信息（有关职工工资级别的信息（丢失了函数依赖：职工丢失了函数依赖：职工级别级别）。2007年4月19日星期四29兰州理工大学计算机与通信学院兰州理工大学计算机与通信学院R(A,B,C)ABC112221AB1122BC1221ABC112221AB(R)BC(R)AB(R)BC(R)R(A,B,C)ABC111212AB1121BC1112ABC111112211212AB(R)BC(R)AB(R)BC(R)有损分解有损分解无损分解无损分解2007年4月19日星期四30兰州理工大学计算机与通信学院兰州理工大学计算机与通信学院 将将R R分解为分解为R1R1和和R2R2的分解是无损连接分解的条的分解是无损连接分解的条件是，件是，R1R2 R1R1R2 R1，或，或R1R2 R2R1R2 R2。如果有如果有R R上的上的函数依赖函数依赖X YX Y成立，且成立，且X YX Y是空是空集，则分解集，则分解R R Y Y和和X YX Y是无损连接分解。是无损连接分解。数据库原理与应用数据库原理与应用Principle and Application of DataBase 2007年4月19日星期四31兰州理工大学计算机与通信学院兰州理工大学计算机与通信学院数据库原理与应用数据库原理与应用Principle and Application of DataBase 判定一个分解是否为依赖保持分解的算法判定一个分解是否为依赖保持分解的算法比较复杂。请看参考文献。比较复杂。请看参考文献。2007年4月19日星期四32兰州理工大学计算机与通信学院兰州理工大学计算机与通信学院 设计目标设计目标：无损连接、保持依赖、无损连接、保持依赖、x xNFNF 无损连接是分解的一个必要条件。无损连接是分解的一个必要条件。BCNFBCNF不一定不一定能保持依赖，但能保持依赖，但3NF3NF可以保证分解是保持依赖的。可以保证分解是保持依赖的。因此，在工程中一般分解到因此，在工程中一般分解到3NF3NF。此外，有时需要对表进行水平分解。此外，有时需要对表进行水平分解。数据库原理与应用数据库原理与应用Principle and Application of DataBase 2007年4月19日星期四33兰州理工大学计算机与通信学院兰州理工大学计算机与通信学院数据库原理与应用数据库原理与应用Principle and Application of DataBase 小结小结 规范化理论为数据库设计提供了理论和方法。规范化理论为数据库设计提供了理论和方法。但是，客观世界是复杂的，在构造模式时尚需考虑但是，客观世界是复杂的，在构造模式时尚需考虑到其他的多种因素。在实际应用中，必须结合应用到其他的多种因素。在实际应用中，必须结合应用环境和现实世界的具体情况，合理地选择数据库模环境和现实世界的具体情况，合理地选择数据库模式。式。2007年4月19日星期四34兰州理工大学计算机与通信学院兰州理工大学计算机与通信学院谢谢!数据库原理与应用数据库原理与应用Principle and Application of DataBase 2007年4月19日星期四35兰州理工大学计算机与通信学院兰州理工大学计算机与通信学院