第5周关系模型的完整性约束与关系的规范化

1、1回顾回顾p关系模型、关系模式、关系关系模型、关系模式、关系p关系代数运算并、差、交、笛卡尔积、投影、选择、连接、除p 基本运算并、差、笛卡尔积、投影、选择p 其它运算可以用5种基本运算来表达数据库原理与应用数据库原理与应用第第5讲讲 关系模型的完整性约束与关系模型的完整性约束与关系的规范化关系的规范化3提纲提纲n 重点：重点：p关系数据库的三种完整性约束关系数据库的三种完整性约束p关系模式分解的关系模式分解的原因、依据和规范原因、依据和规范n 难点难点p函数依赖的概念函数依赖的概念p2NF，3NF的特征及分解方法的特征及分解方法关系模型的完整性约束关系模型的完整性约束实体完整性实体完整性参照

2、完整性参照完整性 用户自定义完整性用户自定义完整性5实体完整性实体完整性n 定义定义p所谓实体完整性，也就是一个二维表中所谓实体完整性，也就是一个二维表中没有两个完全没有两个完全相同行相同行，也称为行完整性。，也称为行完整性。 n 办法办法p必须有候选关键字，必须有候选关键字，p关键字不为空、不重复。关键字不为空、不重复。n 例：例：p学生（学生（学号学号，），学号不能为空且不能重复。），学号不能为空且不能重复。6参照完整性参照完整性7n定义定义p 当表当表A(成绩表）成绩表）是表是表B（学生表）（学生表）的从表，其每一行中外的从表，其每一行中外部关键字的值必须是部关键字的值必须是B中的关键字

3、的一个已有值。中的关键字的一个已有值。n办法办法p 对从表进行对从表进行插入插入限制，对主表进行限制，对主表进行修改和删除修改和删除限制。限制。n例（思考，提问）例（思考，提问）p 学生表是学生表是从表从表，班级表是，班级表是主表主表，班级表的主键是班级号。，班级表的主键是班级号。p 如果班级表中没有如果班级表中没有A999班，学生表中能不能插入班，学生表中能不能插入A999班班的学生？的学生？p 若学生表中班级号为若学生表中班级号为A201的有数据，能不能将班级表中的有数据，能不能将班级表中A201班改成了班改成了A301？p 如果学生表中存在如果学生表中存在A101班的学生，班级表中能不能

4、删除班的学生，班级表中能不能删除A101？8用户自定义完整性用户自定义完整性n 定义：定义：p单个属性的取值单个属性的取值只能在许可的范围内，也称只能在许可的范围内，也称域完整性域完整性。p一个元组的一个元组的多个属性值多个属性值之间相容，也称之间相容，也称元组完整性元组完整性。n 办法：办法：p指定每个列的数据类型和取值范围。指定每个列的数据类型和取值范围。p制定数据检查规则。制定数据检查规则。n 例：例：p域完整性：将年龄属性的数据类型设置为整数，并且域完整性：将年龄属性的数据类型设置为整数，并且大于大于18小与小与25。p元组完整性：性别属性为元组完整性：性别属性为“男男”，宿舍楼属性不

5、能为，宿舍楼属性不能为“紫薇楼紫薇楼”。9n 讨论讨论p设定性别只能取男设定性别只能取男/女，学号长度为女，学号长度为6位，男生学号位，男生学号为奇数，女生为偶数。以下关系违反了哪些约束为奇数，女生为偶数。以下关系违反了哪些约束学号学号姓名姓名性别性别班级编号班级编号20118张三张三1A00120119张三张三1A001201110李四李四2A001201111王五王五1A001班级编号班级编号班级全称班级全称A0012011生物工程生物工程1班班A0012011生物工程生物工程2班班学生表学生表班级表班级表关系模式的分解关系模式的分解不好的关系模式引发的问题不好的关系模式引发的问题函数依赖

6、的基本概念函数依赖的基本概念关系的规范化关系的规范化关系的分解关系的分解11不好的关系模式引发的问题不好的关系模式引发的问题n 引例：下表关系模型存在如下引例：下表关系模型存在如下4方面的问题。方面的问题。p插入异常插入异常 p删除异常删除异常p数据冗余数据冗余p更新异常更新异常订货单号订货单号供应商代码供应商代码供应商名称供应商名称联系人联系人商品名称商品名称订货数量订货数量单价单价日期日期A201201S001 华科电子有限公司华科电子有限公司施宾彬施宾彬笔记本计算机笔记本计算机1098002011.1.2A201202S001 华科电子有限公司华科电子有限公司施宾彬施宾彬激光打印机激光打

7、印机528002011.1.2A201203S002 湘江计算机公司湘江计算机公司方胜力方胜力笔记本计算机笔记本计算机5102002011.8.12A201204S003 韦力电子实业公司韦力电子实业公司周昌周昌 喷墨打印机喷墨打印机54802011.8.12A201205S003 韦力电子实业公司韦力电子实业公司周昌周昌 交换机交换机285002011-8.12一个不好的关系模式一个不好的关系模式订货关系订货关系12n 上述订货关系模式的问题分析上述订货关系模式的问题分析p插入异常插入异常 新发展了一个供应商，但新发展了一个供应商，但没有订货没有订货，无法插入该供，无法插入该供应商信息。应商

8、信息。p删除异常删除异常如果删除某些订货信息，则连供应商的信息也会被如果删除某些订货信息，则连供应商的信息也会被删除。删除。p数据冗余和更新异常数据冗余和更新异常供应商的名称出现了重复。供应商的名称出现了重复。如果某供应商更换了联系人，则所有的订货信息都如果某供应商更换了联系人，则所有的订货信息都要更新，漏掉一处就会造成数据不一致。要更新，漏掉一处就会造成数据不一致。13n 上述订货关系模式的问题的解决方法上述订货关系模式的问题的解决方法p对关系模式进行分解，分成两个表，上述异常问题就对关系模式进行分解，分成两个表，上述异常问题就完全解决了。完全解决了。供应商代码供应商代码供应商名称供应商名称

9、联系人联系人S001 华科电子有华科电子有限公司限公司施宾彬施宾彬S002 湘江计算机湘江计算机公司公司方胜力方胜力S003 韦力电子实韦力电子实业公司业公司周昌周昌 订货单号订货单号供应商供应商代码代码商品名商品名称称订货订货数量数量日期日期单价单价A201201S001 笔记本笔记本计算机计算机102011.1.29800A201202S001 激光打激光打印机印机52011.1.22800A201203S002 笔记本笔记本计算机计算机52011.8.1210200A201204S003 喷墨打喷墨打印机印机52011.8.12480A201205S003 交换机交换机22011-8.1

10、28500供应商关系供应商关系订货关系订货关系14函数依赖的基本概念函数依赖的基本概念n 函数依赖函数依赖定义：定义： 对应于属性组合对应于属性组合X的不同取值，的不同取值， Y属性必定有不同的取值，属性必定有不同的取值，则称则称X决定决定Y，或称，或称Y函数依赖于函数依赖于X。记为：。记为：XY。例：学号例：学号姓名姓名n 完全函数依赖和部分函数依赖完全函数依赖和部分函数依赖定义：定义： 如果如果XY成立，但对成立，但对X的任意真子集的任意真子集X1，都有，都有X1Y不成不成立，称立，称Y完全函数依赖于完全函数依赖于X，否则，称，否则，称Y部分函数依赖于部分函数依赖于X。例：例：成绩表（成绩

11、表（学号，课程号学号，课程号，成绩，补考成绩），成绩，补考成绩）中的中的成绩成绩完全依赖完全依赖于表中（于表中（学号，课程编号学号，课程编号）例：例：订货表订货表（订货单号，订货单号，供应商代码供应商代码 ，供应商名称，联系人，商品供应商名称，联系人，商品名称，订货数量名称，订货数量 单价，日期单价，日期）中的）中的供应商名称供应商名称部分依赖于（部分依赖于（订订货单号，货单号，供应商代码供应商代码 ）中的）中的供应商代码供应商代码。n 传递函数依赖传递函数依赖定义：定义： 设设X，Y，Z是关系模式是关系模式R的不同属性集，若的不同属性集，若XY(并且并且YX不成立不成立)，YZ，称，称X传递

12、决定传递决定Z，或称，或称Z传递函数依赖于传递函数依赖于X。例：订货单号例：订货单号供应商代码供应商代码 供应商名称供应商名称，供应商名称供应商名称传递依赖于传递依赖于订货单号。订货单号。15关系的规范化关系的规范化n 主属性与非主属性主属性与非主属性p候选关键字（或候选关键属性）候选关键字（或候选关键属性）如果如果所有所有的属性都的属性都完全依赖于属性组合完全依赖于属性组合X，则称，则称X为候选关键字。为候选关键字。p关键属性关键属性若关系若关系R中只有一个候选关键属性，称唯中只有一个候选关键属性，称唯 一的候选关键属性为关键属性，否则，应从多个候选关键一的候选关键属性为关键属性，否则，应从

13、多个候选关键属性中指定一个作为关键属性。也叫主关键字。属性中指定一个作为关键属性。也叫主关键字。p主属性主属性候选关键字中的候选关键字中的一个属性一个属性，注意注意单个主属性单个主属性并不一定能作为候选关键属性并不一定能作为候选关键属性。p非主属性非主属性不属于候选关键字的属性不属于候选关键字的属性例例：学生表（：学生表（学号学号，姓名姓名，性别，年龄，家庭住址），性别，年龄，家庭住址）成绩表（成绩表（学号，课程号学号，课程号，成绩），成绩）p问问： R(员工号员工号, 部门编号，姓名部门编号，姓名,月份月份,工资工资, 个人月度奖个人月度奖)，候选关，候选关键字是什么？主属性有哪些？非主属性

14、有哪些？键字是什么？主属性有哪些？非主属性有哪些？若姓名若姓名不同名不同名16n 第第1范式范式p当关系模式当关系模式R的所有属性都不能分解为更基本的数据单的所有属性都不能分解为更基本的数据单位时，称位时，称R是满足第是满足第1范式的，简记为范式的，简记为1NF。p例例： R(员工号员工号,姓名姓名,工资含基本工资和岗位工资工资含基本工资和岗位工资)不满不满足足1NF。应该分解为。应该分解为R(员工号员工号,姓名姓名,基本工资基本工资,岗位工资岗位工资)n 第第2范式范式p如果关系模式如果关系模式R满足第满足第1范式，并且范式，并且R的所有非主属性的所有非主属性都都完全依赖完全依赖于于R的每一

15、个候选关键属性，称的每一个候选关键属性，称R满足第满足第2范式，简记为范式，简记为2NF。p例例：R(工号工号,部门编号部门编号,姓名姓名, 部门名部门名)不满足不满足2NF,因为部因为部门名部分依赖（而不是完全依赖）于候选关键字（工门名部分依赖（而不是完全依赖）于候选关键字（工号，部门编号）。号，部门编号）。要让其满足要让其满足2NF，转换，转换：R1（工号工号,部门编号部门编号,姓名）姓名）R2（部门编号部门编号, 部门名）部门名）17n 第第3范式范式p设设R是一个满足是一个满足2NF条件的关系模式，条件的关系模式，X是是R的任意属的任意属性集，如果性集，如果X非传递依赖于非传递依赖于R

16、的任意一个候选关键字，的任意一个候选关键字，称称R满足第满足第3范式，简记为范式，简记为3NF。p若关系模式若关系模式R符合符合3NF条件，则条件，则R一定符合一定符合2NF条件。条件。p例例：R（学号学号，系名，系主任），不满足，系名，系主任），不满足3NF,因为因为系主系主任任依赖于依赖于系名系名，系名系名依赖于依赖于学号学号，产生了传递依赖。，产生了传递依赖。要让其满足要让其满足3NF，转换，转换：R1（学号学号，系名），系名）R2（系名系名，系主任），系主任）n Boyce-Codd范式范式(只作了解只作了解)p设设R是一个关系模式，若是一个关系模式，若R的每一个函数依赖关系的左的每一

17、个函数依赖关系的左部都是部都是R的一个候选关键字，称的一个候选关键字，称R满足满足Boyce-Codd范范式，简记为式，简记为BCNF。p符合符合BCNF条件的关系模式一定符合条件的关系模式一定符合3NF条件。条件。18n 范式总结：范式总结：p1NF原子性，原子性，2NF完全依赖于关键字，完全依赖于关键字，3NF无传递依赖，无传递依赖，BCNF只依赖于关键字。只依赖于关键字。19关系分解举例：关系分解举例：工工号号姓名姓名部门部门月月份份基本工资和基本工资和岗位工资岗位工资个人月奖和个人月奖和部门月奖部门月奖1，以上关系符合第一范式么？违反原子性，分解如下：，以上关系符合第一范式么？违反原子

18、性，分解如下：工工号号姓姓名名部门部门月月份份基本工基本工资资岗位工岗位工资资个人月个人月奖奖部门月奖部门月奖2，以上关系符合，以上关系符合2NF么？关键字取（工号，部门，月份），没有么？关键字取（工号，部门，月份），没有做到完全依赖，比如姓名不依赖于部门。分解如下：做到完全依赖，比如姓名不依赖于部门。分解如下：由由ER图转换的结果：同时还要依据函数依赖判断是否符合图转换的结果：同时还要依据函数依赖判断是否符合3NF部门部门工号工号月份月份姓名姓名个人月个人月奖奖基本工基本工资资岗位工岗位工资资部部门门月月份份部门月部门月奖奖3，以上关系的主键各是什么？外键各是什么？，以上关系的主键各是什么？

19、外键各是什么？4，以上关系符合，以上关系符合3NF么？符合么？符合BCNF么？么？工工号号月月份份个人个人月奖月奖工工号号姓名姓名基本基本工资工资岗位岗位工资工资部部门门月月份份部门月部门月奖奖20关系分解举例关系分解举例(所有员工工号一重复所有员工工号一重复)：工工号号姓名姓名部门部门月月份份基本工资和基本工资和岗位工资岗位工资个人月奖和个人月奖和部门月奖部门月奖1，以上关系符合第一范式么？违反原子性，分解如下：，以上关系符合第一范式么？违反原子性，分解如下：工工号号姓姓名名部门部门月月份份基本工基本工资资岗位工岗位工资资个人月个人月奖奖部门月奖部门月奖2，以上关系符合，以上关系符合2NF么

20、？关键字取（工号，部门，月份），没有么？关键字取（工号，部门，月份），没有做到完全依赖，比如姓名不依赖于部门。分解如下：做到完全依赖，比如姓名不依赖于部门。分解如下：由由ER图转换的结果：同时还要依据函数依赖判断是否符合图转换的结果：同时还要依据函数依赖判断是否符合3NF部门部门工号工号月份月份姓名姓名个人月个人月奖奖基本工基本工资资岗位工岗位工资资部部门门月月份份部门月部门月奖奖3，以上关系的主键各是什么？外键各是什么？，以上关系的主键各是什么？外键各是什么？4，以上关系符合，以上关系符合3NF么？符合么？符合BCNF么？么？工工号号月月份份个人个人月奖月奖工工号号姓名姓名基本基本工资工资岗

21、位岗位工资工资部部门门月月份份部门月部门月奖奖21自联系指同一个实体类中实体间的联系。如下图。自联系指同一个实体类中实体间的联系。如下图。如果只一个领导，则只建一张表；如果有多个领导，需建两张表；如果只一个领导，则只建一张表；如果有多个领导，需建两张表；姓姓 名名员工号员工号民民 族族基本工基本工资资职职 务务住住 址址领领 导导员员 工工上图转化为关系模型：上图转化为关系模型：员工员工(员工号员工号,姓名姓名,住址住址,民族民族,基本工资基本工资,职务职务)领导领导(领导员工号领导员工号,被领导员工号被领导员工号)注：同一实体类联系也可如下转换注：同一实体类联系也可如下转换22小结小结p 数

22、据完整性分为实体完整性、参照完整性和自定义完数据完整性分为实体完整性、参照完整性和自定义完整性。整性。p 不好的关系模式存在数据冗余、插入异常、删除异常不好的关系模式存在数据冗余、插入异常、删除异常等许多问题，解决这些问题的基本方法是依照关系规等许多问题，解决这些问题的基本方法是依照关系规范对关系模式进行分解。范对关系模式进行分解。1. 预习第预习第4章数据库的管理。章数据库的管理。小课内容小课内容习题课习题课24模式分解模式分解40分钟分钟-同学们先消化下，老师点拨同学们先消化下，老师点拨p原因：模式分解是为了减少因原因：模式分解是为了减少因数据冗余带来的插入异数据冗余带来的插入异常、修改异

23、常和删除异常常、修改异常和删除异常。p根据：根据：模式分解的依据是函数依赖模式分解的依据是函数依赖p规范：目前模式分解的规范是规范：目前模式分解的规范是1NF,2NF,3NF，BCNF25原因原因冗余和异常冗余和异常n 看下面的关系模式：学生借书表看下面的关系模式：学生借书表学号学号班级班级图书编号图书编号班主任班主任姓名姓名以上模式将导致数据冗余，并可能导致插入异常、删除异常、修改异常。以上模式将导致数据冗余，并可能导致插入异常、删除异常、修改异常。比如：比如：插入异常：如果有一插入异常：如果有一本新书本新书，而没有学生借，则无法插入该书的信息，而没有学生借，则无法插入该书的信息修改异常：如

24、果有一个学生，他的修改异常：如果有一个学生，他的班级发生了变化班级发生了变化，则需要修改与该学生，则需要修改与该学生相关的所有的行，否则，将会引起数据混乱。相关的所有的行，否则，将会引起数据混乱。删除异常：如果要删除异常：如果要删除一个学生删除一个学生，可能连图书的信息也会失去。，可能连图书的信息也会失去。26关键字和关键属性关键字和关键属性如果知道属性如果知道属性A的值的值,就可以在表中唯一的找到一行。就可以在表中唯一的找到一行。称称A是是关键字关键字。关键字也可能由多个属性组成，比如：在考试成绩表关键字也可能由多个属性组成，比如：在考试成绩表中，关键字是（学号，课程编号）。学号和课程编号中

25、，关键字是（学号，课程编号）。学号和课程编号都是都是关键属性关键属性即即主属性主属性。学生（学号，姓名，班级，班级名，辅导员），学生（学号，姓名，班级，班级名，辅导员），在这在这样一个关系中，关键字是什么呢？有人说是样一个关系中，关键字是什么呢？有人说是（学号，（学号，班级），班级），这是不对的，因为这是不对的，因为学号学号班级班级，关键字关键字如果如果有多个属性组成，这些属性之间不能有依赖关系，所有多个属性组成，这些属性之间不能有依赖关系，所以关键字应该是学号，班级不是关键属性。以关键字应该是学号，班级不是关键属性。27根据根据函数依赖函数依赖在学生表中，知道学号就能查到姓名。称学号在学生表

26、中，知道学号就能查到姓名。称学号姓名。姓名。读作姓名读作姓名依赖依赖于学号。在成绩表中，知道学号和课程于学号。在成绩表中，知道学号和课程编号就能查到成绩。称（学号，课程编号）编号就能查到成绩。称（学号，课程编号）成绩。成绩。在在1中，我们说姓名中，我们说姓名完全依赖完全依赖于学号，成绩于学号，成绩完全依赖完全依赖于（学号，课程编号）。于（学号，课程编号）。假设在成绩表中存放了姓名，假设在成绩表中存放了姓名，“知道了（学号，课程知道了（学号，课程编号）就能查到姓名编号）就能查到姓名”这句话当然是正确的，但其实这句话当然是正确的，但其实姓名只需要根据学号就能查到，称姓名姓名只需要根据学号就能查到，

27、称姓名部分依赖部分依赖于于（学号，课程编号）。（学号，课程编号）。学生（学号，姓名，班级，班级名，辅导员）关系中，学生（学号，姓名，班级，班级名，辅导员）关系中，学号学号班级，班级班级，班级辅导员（，班级辅导员（，班级班级名），班级名），这这样就产生了样就产生了传递依赖传递依赖。当一个模式存在部分依赖或传递依赖时，就会产生数当一个模式存在部分依赖或传递依赖时，就会产生数据冗余。就会导致插入异常、删除异常、更改异常。据冗余。就会导致插入异常、删除异常、更改异常。28规范规范2NF，3NF2NFp 要求：没有部分依赖。要求：没有部分依赖。p 例如：例如： 成绩（学号，姓名，课程号，课程名，成绩）成绩（学号，姓名，课程号，课程名，成绩）关系中，应该剔出姓名和课程名。关系中，应该剔出姓名和课程名。3NFp 要求：没有传递依赖要求：没有传递依赖p 例如：例如： （学号，姓名，班级，班级名，辅导员）关系（学号，姓名，班级，班级名，辅导员）关系中应该剔出中应该剔出 班级名，辅导员班级名，辅导员。怎么办怎么办p 1，2中剔出了一些列，但这些数据不能丢失，所以要中剔出了一些列，但这些数据不能丢失，所以要用另外的表来存放被剔出的数据，这就是关系模式分用另外的表来存放被剔出的数据，这就是关系模式分解的原因。解的原因。29例例成绩（学号，姓名