第5讲 关系完整性规则和规范化要求

1、第第5讲讲 关系完整性规则和规范化理论关系完整性规则和规范化理论 2 关系模式的规范化理论与方法（重点与难点） 1 关系规范化的作用（了解） 1 关系完整性规则（掌握） 要求学生掌握关系模式的3个完整性规则 要求学生了解关系模式的设计问题，掌握函数依赖 和多值依赖的定义和含义，能够应用关系模式的范 式及关系模式的分解特性解决实际的关系数据库设 计问题 本讲的理论性和应用性都比较强，学生应 从概念着手，弄清概念之间的联系和作用。 了解关系数据库规范化理论及其在数据库 设计中的作用。 本讲的重点是函数依赖和范式，学生必须 掌握这些概念并能在实际问题中加以运用， 为以后设计关系数据库奠定一个良好基础

2、。 关系数据库设计主要是关系模式设计。关系模式设 计的好坏将直接影响数据库的质量。 要求设计一个教学管理数据库，希望从该数据库中经常得 到下面的有关信息：学生学号，学生姓名，年龄，性别， 系别，系主任姓名，学生学习的课程号，该课程的成绩等。 该怎样设计该数据库的关系模式？ S S( (Sno,Sname,Sage,Sex,Dept,DN,Cno,Cname,ScoreSno,Sname,Sage,Sex,Dept,DN,Cno,Cname,Score) ) 5 引例：下表关系模型存在如下4方面的问题。 插入异常 删除异常 数据冗余 更新异常 订货单 号 供应商代 码 供应商名称联系人商品名称订

3、货数 量 单价 日期 A20120 1 S001 华科电子有限公 司 施宾彬笔记本计算 机 109800 2011.1.2 A20120 2 S001 华科电子有限公 司 施宾彬激光打印机52800 2011.1.2 A20120 3 S002 湘江计算机公司方胜力笔记本计算 机 51020 0 2011.8.1 2 A20120 4 S003 韦力电子实业公 司 周昌 喷墨打印机5480 2011.8.1 2 一个不好的关系模式一个不好的关系模式订货关系订货关系 1.实体完整性规则实体完整性规则（） 规则要求： 在任何关系的任何一个元组中，主键的值不能为空值、 也不能取重复的值。 目的：用于

4、保证数据库表中的每一个元组都是惟一的。 请思考：下面的关系是否违反实体完整性规则？ 学号姓名性别年龄系编号 03001马力刚男2101 03102王萍华女2002 03223王平男2103 张华男22 04 03001李萍女1905 2.域完整性规则域完整性规则(用户定义完整性规则用户定义完整性规则) 规则要求 由用户根据实际情况，定义表中属性的取值范围 例如：性别只能是男和女、年龄不能为负值、成 绩在0100之间等。 目的 用于保证给定字段中数据的有效性,即保证数据 的取值在有效的范围内。 3.参照完整性规则（引用完整性规则）参照完整性规则（引用完整性规则） 规则要求： “不引用不存在的实体

5、”。即：不允许在一个不允许在一个 关系中引用另一个关系中不存在的元组关系中引用另一个关系中不存在的元组。 目的 在关系数据库中通常用外码来实现参照完整性 的，用于确保相关联的表间的数据保持一致。 请思考：下面两个关系是否违反参照完整性规则？ 系表（主表） 学生表（从表） 说明 从学生表的“系编号（外键）”的取值只能为两种情况： 若取非空值，则它必须是主表中若取非空值，则它必须是主表中存在的值存在的值。 取空值取空值(null)。表明尚未给学生分配专业。表明尚未给学生分配专业。Null不等于不等于0或空字符串。或空字符串。 学号学号姓名姓名性别性别年龄年龄系编号系编号 03001马力刚马力刚男男

6、2101 03102王萍华王萍华女女20 03223王平王平男男2105 系编号系编号系名系名系主任系主任办公室办公室电话电话 01计算机计算机龚小勇龚小勇2056003 02通信通信谭中华谭中华2076025 03电子电子袁勇袁勇2106018 上面两个关系存在着属性的引用，即学生关系引用了系关系的主码上面两个关系存在着属性的引用，即学生关系引用了系关系的主码“系编号系编号” 以主表的主键（以主表的主键（ primary key）值）值 为外键为外键 (Foreign Key)的表，可以通的表，可以通 过外键与主表进行过外键与主表进行 关联查询关联查询 一个好的关系数据库模式，到底包括多少

7、个关系模式，而每个关系模式又应该包括 哪些属性（字段），这就是关系数据库设 计要解决的主要问题，或者说，这就是数 据库逻辑设计要解决的规范化问题 11 引例：下表关系模型存在如下4方面的问题。 插入异常 删除异常 数据冗余 更新异常 订货单号供应商代 码 供应商名称联系人商品名称订货数量单价日期 A201201S001 华科电子有限公司施宾彬笔记本计算机1098002011.1. 2 A201202S001 华科电子有限公司施宾彬激光打印机528002011.1. 2 A201203S002 湘江计算机公司方胜力笔记本计算机5102002011.8. 12 A201204S003 韦力电子实业

8、公司周昌 喷墨打印机54802011.8. 12 A201205S003 韦力电子实业公司周昌 交换机285002011- 8.12 一个不好的关系模式一个不好的关系模式订货关系订货关系 12 上述订货关系模式的问题分析 插入异常 新发展了一个供应商，但没有订货，无法插 入该供应商信息。 删除异常 如果删除某些订货信息，则连供应商的信息 也会被删除。 数据冗余和更新异常 供应商的名称出现了重复。 如果某供应商更换了联系人，则所有的订货 信息都要更新，漏掉一处就会造成数据不一 致。 13 上述订货关系模式的问题的解决方法 对关系模式进行分解，分成两个表，上述异常 问题就完全解决了。 供应商代 码

9、 供应商名 称 联系人 S001 华科电子 有限公司 施宾彬 S002 湘江计算 机公司 方胜力 S003 韦力电子 实业公司 周昌 订货单 号 供应商 代码 商品名 称 订 货 数 量 日期单价 A20120 1 S001 笔记本 计算机 102011.1.29800 A20120 2 S001 激光打 印机 52011.1.22800 A20120 3 S002 笔记本 计算机 52011.8.1210200 A20120 4 S003 喷墨打 印机 52011.8.12480 供应商关系供应商关系订货关系订货关系 指数据之间存在的各种联系和约 束，函数依赖是最基本的一种依赖，在规范 化理

10、论中具有核心作用。 是衡量关系模式优劣的标准， 范式有许多种，与数据依赖有着直接的联系。 设计规范的数据库模式的方 法。 实体内部和实体之间各属性间的联系： 一对一关系（1:1） 一对多关系（1:n） 多对多关系（m:n） 属性间的数据依赖 函数依赖FD 多值依赖MD 连接依赖JD 解决单表异常 的理论基础 解决多表异常 的理论基础 Y=f(X) 函数 Y=sin(X) Y=X+1 Y=X2+2X+1 省=f(城市) 姓名=f(学号) n定义1：设R(U)是属性集U上的关系模式X，Y 是U的子集若对于R(U)的任意个可能的关系r ，r中不可能存在两个元组在X上的属性值相等， 而在Y上的属性值不

11、等，则称 X函数确定函数确定Y或或Y函函 数依赖于数依赖于X，记作，记作XY。 n定义定义2：在关系R中，X、Y为R的两个属性或属性 组，如果对于R的所有关系r 都存在：对于X的每 一个具体值，Y都只有一个具体值与之对应，则 称属性Y函数依赖于属性X。或者说，属性X函数 决定属性Y，记作XY。其中X称为决定因素，Y 称为被决定因素。 有函数依赖： 编号名称（名称函数依赖于编号） 编号负责人（负责人函数依赖于编号） 名称地点（地点函数依赖于名称） 名称编号（编号函数依赖于名称） 若Y函数不依赖不依赖于X，记作：XY。 如学号不函数依赖于性别 若X,Y间是1:1关系，则存在函数依赖 XY 若X,Y

12、间是1:n关系，则存在函数依赖 XY或 YX（多方为决定因素） 若X,Y间是m:n关系，则不存在函数依赖 11 经经 理理公公 司司 领领 导导 姓姓 名名 住住 址址 出生年出生年 月月 电电 话话 民民 族族 名名 称称 电电 话话 类类 型型 注册地注册地 若若X,Y间是间是1:1关系关系，则存在函数依赖，则存在函数依赖 XY 因此经理因此经理-公司，或者公司公司，或者公司-经理经理 1 n 学学 生生 班班 级级 属属 于于 姓姓 名名学学 号号 民民 族族 出生年出生年 月月 名名 称称 班班 号号 系系 年年 级级 专专 业业 若若X,Y间是间是1:n关系关系，则存在函数依赖，则存

13、在函数依赖 XY(多方为决定因素多方为决定因素) 因此学生因此学生-班级（一个学生的取值，班级仅有一个值与之对应）班级（一个学生的取值，班级仅有一个值与之对应） 如果如果XY，并且，并且YX，则可记作，则可记作XY， 这时这时X和和Y可以称做可以称做函数等价函数等价。 如在院系关系上：如在院系关系上： 编号编号名称名称,名称名称编号编号 所以在院系关系上编号和名称可以称作函所以在院系关系上编号和名称可以称作函 数等价。数等价。 如果XY，但Y不包含于X，则称XY是非平凡非平凡 的函数依赖的函数依赖。若不特别声明，我们总是讨论非平若不特别声明，我们总是讨论非平 凡的函数依赖凡的函数依赖 如：(学

14、号,课程号)成绩 如：(学号,学院)学院 非平凡依赖 平凡依赖 关系模式：学生（学号，姓名，年龄） (学号，姓名)姓名 函数依赖 学号姓名 函数依赖 平凡平凡 非平凡非平凡 如果XY，并且对于X的一个任意真子集X/ 都有X/ Y，则称Y完全函数依赖完全函数依赖于X，并记 作 ；如果X/ Y成立，则称Y部分函数部分函数 依赖依赖于X，并记作 YX f YX p 如：如：(学号学号, ,课程号课程号)成绩成绩 完全完全函数依赖函数依赖 而：而：(学号学号,院系院系)负责人负责人 部分部分函数依赖函数依赖 1、如果在关系模式R(A,B,C)中存在 (A,B)C和BC，则(A,B)C为 函数依赖。 2

15、、如果在关系模式R(A,B,C)中存在 (A,B)C但不存在BC和AC，则 (A,B)C为 函数依赖。 部分部分 完全完全 如果如果XY（非平凡函数依赖，并（非平凡函数依赖，并 且且Y X）、）、YZ，则称，则称Z传递函数传递函数 依赖依赖于于X。 如学号专业，专业院系， 则院系传递函数依赖于学号。 如果在关系模式R(A,B,C)中存在 AC 且存在AB，BC，则 A C 为 函数依赖 传递传递 设有关系模式设有关系模式R R( (U U, ,F F) )，X X、Y Y、Z Z 均为均为U U 的子集，的子集， 有如下推理规则：有如下推理规则： 增广律：如果增广律：如果X XY Y，则，则X

16、ZXZYZYZ； 传递律：如果传递律：如果X XY Y、Y YZ Z，则，则X XZ Z 。 合并规则：如果合并规则：如果X XY Y、X XZ Z，则，则X XYZYZ。 分解规则：如果分解规则：如果X XYZYZ，则，则X XY Y、X XZ Z。 伪传递规则：如果伪传递规则：如果X XY Y、YWYWZ Z，则，则XWXWZ Z。 在关系模式中，函数依赖不能表示属性值之间 的一对多联系，这些属性之间有些虽然没有直 接关系，但存在间接的关系。把没有直接联系、 但有间接的联系称为多值依赖的数据依赖。 设R(U)是属性集U上的一个关系模式。X，Y，Z 是的U的子集,并且Z=U-X-Y。关系模式

17、R(U)中 多值依赖多值依赖XYXY成立，当且仅当对R(U)的任一 关系r,给定的一对(x，z)值有一组Y的值，这 组值仅仅决定于x值而与z值无关。 定义中若Z=0(空值)，则称 XYXY为平凡的多为平凡的多 值依赖，否则为非平凡的多值依赖值依赖，否则为非平凡的多值依赖 函数就是唯一确定的关系；多值依赖却不能唯一确定函数就是唯一确定的关系；多值依赖却不能唯一确定 举例如下，有这样一个关系 ，假设一个产品只 能放到一个仓库中，但是一个仓库可以有 若干管理员，那么对应于一个 仓库管理 员，库存产品有一组仓库号，而实际上， 这个仓库号只与库存产品号有关，与管理 员无关，就说这是多值依赖。 库存产品仓

18、库号 仓管员库存产品仓库号 张三X11 李四X22 李四X11 多值依赖的性质： 对称性：若XYXY，则，则XZ,XZ,其中其中Z=U-X-YZ=U-X-Y 传递性：若传递性：若XYXY， YZ,YZ,则则XZ-YXZ-Y 合并规则：合并规则：若若XYXY， XZ,XZ,则则XYZXYZ 若若XYXY， XZ,XZ,则则XYXYZ Z 若若XYXY， XZ,XZ,则则XY-ZXY-Z， XZ-YXZ-Y 目的是要设计“好的”关系数据库模式。 关系模式的范式分为： 第一范式INF 第二范式2NF 第三范式3NF BCNF范式 第四范式4NF 第五范式5NF 低低 高高 规范化程度规范化程度 每个

19、关系模式都应满足最低要求：所有分量都必须是不 可分的最小数据项，并把其称为第一范式（1NF）关系。 如果R(U,F) 1NF，并且R中的每个非主属性 都完全函数依赖于关键字，则R(U,F) 2NF。 判断关系模式是否满足2NF的方法？ 主关键字为单个属性时：一定为2NF 主关键字为多个属性时：如果每个非主属性组 都完全依赖于主关键字，则为2NF；否则不 是2NF。 不满足第二范式不满足第二范式 因为存在部分函数依赖因为存在部分函数依赖 把“选课”关系分解为如下“选课”和“课程”两个 关系： 选课(学号, 课程号, 考试成绩) 课程(课程号, 课程名, 责任教师, 职称) 存储冗余、插入异常、更新异常、存储冗余、插入异常、更新异常、 删除异常等现象是否还存在呢？删除异常等现象是否还存在呢？ 如果R(U,F) 2NF，并且所有非主属性都不传 递依赖于关键字，则R(U,F) 3NF。 例如