第7章 关系数据库规范化理论 7.1 函数依赖 7.2 关系规范化 7.3 关系模式的分解准则.

Presentation on theme: "第7章 关系数据库规范化理论 7.1 函数依赖 7.2 关系规范化 7.3 关系模式的分解准则."— Presentation transcript:

1 第7章 关系数据库规范化理论 7.1 函数依赖 7.2 关系规范化 7.3 关系模式的分解准则

2 第7章 关系数据库规范化理论 数据库设计是数据库应用领域中的主要研究课题。 关系数据库规范化理论就是数据库设计的一个理论指南。
规范化理论研究的是关系模式中各属性之间的数据依赖关系及其对关系模式性能的影响 ；以及判断关系模式好坏的理论标准—范式。 如何构造一个合适的关系模式，应构造几个关系模式，每个关系模式由哪些属性组成等，都是数据库设计问题，确切地讲是关系数据库的逻辑设计问题。

3 第7章 关系数据库规范化理论 关系模式的形式化定义 一个完整的关系模式由五部分组成，即它是一个五元组： R(U, D, DOM, F)
D：属性组U中属性所来自的域 DOM：属性向域的映象集合 F：属性间数据的依赖关系集合

4 第7章 关系数据库规范化理论 什么是数据依赖 一个关系内部属性与属性之间的约束关系 数据依赖的类型
函数依赖（Functional Dependency，简记为FD） 多值依赖（Multivalued Dependency，简记为MVD） 其他

5 第7章 关系数据库规范化理论 关系模式的简化表示 关系模式五元组R（U, D, DOM, F） 可简化为一个三元组： R（U, F）
当且仅当属性组U上的一个关系r满足函数依赖关系F时，r称为关系模式 R（U, F）的一个关系。

6 7.1 函数依赖 省=f(城市) 如“武汉市”在“湖北省”，这里“城市” 是自变量X，“省”是因变量或函数值Y。
只要给出一个具体的城市值 就会有唯一一个省值和它对应 如“武汉市”在“湖北省”，这里“城市” 是自变量X，“省”是因变量或函数值Y。 把X函数决定Y，或Y函数依赖于X表示为： 　　　　X→Y

7 7.1 函数依赖（续） 设有关系模式R(A1，A2，…，An) X和Y均为{A1，A2，…，An}的子集 r是R的任一具体关系
t1、t2是r中的任意两个元组 如果由t1[X]=t2[X]可以推导出t1[Y]=t2[Y]，则称X函数决定Y，或Y函数依赖于X，记为X→Y。

8 7.1 函数依赖（续） 例：Student（Sno, SName, Sdept, Sage） Sno→SName Sno→Sdept
Sno→Sage 例：SC（Sno, Cno, Grade） （Sno, Cno）→Grade

9 7.1.2 一些术语和符号 平凡函数依赖与非平凡函数依赖 在关系模式R(U)中，对于U的子集X和Y，
如果X→Y，但Y  X，则称X→Y是非平凡的函数依赖 若X→Y，但Y  X, 则称X→Y是平凡的函数依赖 例：在关系SC(Sno, Cno, Grade)中， 非平凡函数依赖： (Sno, Cno) → Grade 平凡函数依赖： (Sno, Cno) → Sno (Sno, Cno) → Cno 如不作特别说明，总是讨论非平凡函数依赖。

10 7.1.2 一些术语和符号 若X→Y，则X称为这个函数依赖的决定属性组，也称为决定因素（Determinant）。
若X→Y，Y→X，则记作X←→Y。 若Y不函数依赖于X，则记作X→Y。

11 7.1.2 一些术语和符号 完全函数依赖与部分函数依赖
在R(U)中，如果X→Y，并且对于X的任何一个真子集X’，都有X’ Y, 则称Y对X完全函数依赖，记作 X F Y。 若X→Y，但Y不完全函数依赖于X，则称Y对X部分函数依赖，记作X P Y。 例:(Sno,Cno)→Grade是完全函数依赖， (Sno,Cno)→Sdept是部分函数依赖 因为Sno →Sdept成立，且Sno是（Sno，Cno）的真子集

12 7.1.2 一些术语和符号 Dept_master传递函数依赖于Sno 传递函数依赖
在R(U)中，如果X→Y，(Y X) ,Y→X Y→Z， 则称Z对X传递函数依赖。 记为：X → Z 注: 如果Y→X， 即X←→Y，则Z直接依赖于X。 例: 在关系S（Sno,Sname,Dept,Dept_master）中有： Sno → dept，Sdept → Dept_master Dept_master传递函数依赖于Sno 传递

13 7.1.3 为什么要讨论函数依赖 数据依赖对关系模式的影响 例:建立一个描述学校教务的数据库关系模式：
S-L-C(Sno，Sdept，SLOC，Cno，Grade) 学生的学号（Sno）、 所在系（Sdept） 学生所住宿舍楼（SLOC）、 课程号（Cno） 成绩（Grade） {假设每个系的学生都住在一栋楼里，(Sno,Cno)为主码}

14 数据示例 Sno Sname Ssex Sdept Sloc Cno Grade 9512101 李勇 男 计算机系 2公寓 C01 90
86 C06 NULL 刘晨 78 C04 66 吴宾 女 信息系 1公寓 82 75 92 C05 50 张海 68 钱小平 数学系 80 95 王大力 85

15 7.1.3 为什么要讨论函数依赖 关系模式 ： S-L-C <U、F>
U ＝｛ Sno, Sdept, SLOC, Cno, Grade ｝ 属性组U上的一组函数依赖F： F ＝｛ Sno → Sdept, Sdept → SLOC, (Sno, Cno) → Grade ｝ Sno Cno Sdept SLOC Grade

16 7.1.3 为什么要讨论函数依赖 关系模式Student<U, F>中存在的问题 1. 数据冗余太大
2. 更新异常（Update Anomalies） 3. 插入异常（Insertion Anomalies） 4. 删除异常（Deletion Anomalies）

17 7.1.3 为什么要讨论函数依赖 结论： S-L-C关系模式不是一个好的模式。
“好”的模式：不会发生插入异常、删除异常、更新异常，数据冗余应尽可能少 原因：由存在于模式中的某些函数依赖引起的。 解决方法：模式分解，即把一个关系模式分解成两个或多个关系模式，在分解的过程中消除那些“不良”的函数依赖，从而获得良好的关系模式。

18 7.1.3 为什么要讨论函数依赖 分解关系模式 把这个单一模式分成3个关系模式： S（Sno，Sdept）
SC（Sno，Cno，Grade ） （Sno，Cno） → Grade） DEPT（Sdept，SLOC ） Sdept→ SLOC

19 7.2 关系规范化 规范化理论正是用来改造关系模式，通过分解关系模式来消除其中不合适的数据依赖，以解决插入异常、删除异常、更新异常和数据冗余问题。

20 7.2.1 关系模式中的码 候选码与主码 设K为R<U,F>中的属性或属性组合。若K U， 则K称为R的侯选码。
若候选码多于一个，则选定其中的一个做为主码。 主属性与非主属性 包含在任何一个候选码中的属性 ，称为主属性 不包含在任何码中的属性称为非主属性或非码属性 全码 整个属性组是码，称为全码 F

21 7.2.1 关系模式中的码 例： 关系模式S(Sno,Sdept,Sage)，单个属性Sno是码
SC（Sno，Cno，Grade）中，（Sno，Cno）是码 关系模式R（P，W，A） P：演奏者 W：作品 A：听众 一个演奏者可以演奏多个作品 某一作品可被多个演奏者演奏 听众可以欣赏不同演奏者的不同作品 码为(P，W，A)，即All-Key

22 例：有关系模式 候选码：学号，身份证号。 主码：“学号”或“身份证号”。 主属性：学号，课程号。 非主属性：姓名，性别，年龄，所在系。
学生（学号，姓名，性别，身份证号，年龄，所在系） 候选码：学号，身份证号。 主码：“学号”或“身份证号”。 主属性：学号，课程号。 非主属性：姓名，性别，年龄，所在系。

23 设一个学生对一门课程可以有多次考试，每一次考试有一个考试成绩。
例．有关系模式： 选课（学号，课程号，考试次数，成绩） 设一个学生对一门课程可以有多次考试，每一次考试有一个考试成绩。 候选码：（学号，课程号，考试次数），也为主码。 主属性：学号，课程号，考试次数 非主属性：成绩。

24 例.有关系模式：授课（教师号，课程号，学年）
语义：一个教师在一个学年可以讲授多门不同的课程，可以在不同学年对同一门课程讲授多次，但不能在同一个学年对同一门课程讲授多次。一门课程在一个学年可以由多个不同的教师讲授，同一个学年可以开设多门课程，同一门课程可以在不同学年开设多次。 候选码：（教师号，课程号，学年） 主码：同候选码。 主属性：教师号，课程号，学年 非主属性：无 称这种候选码为全部属性的表为全码表

25 7.2.1 关系模式中的码 外部码:用于关系表之间建立关联的属性（组）。
关系模式 R 中属性或属性组X 并非 R的码，但 X 是另一个关系模式的码，则称 X 是R 的外部码，也称外码。 如在SC（Sno，Cno，Grade）中，Sno不是码，但Sno是关系模式S（Sno，Sdept，Sage）的码，则Sno是关系模式SC的外部码 主码与外部码一起提供了表示关系间联系的手段。

26 7.2.2 范式 范式是符合某一种级别的关系模式的集合
关系数据库中的关系必须满足一定的要求。满足不同程度要求的为不同范式（Normal Form）。 范式的种类： 第一范式(1NF) 第二范式(2NF) 第三范式(3NF) BC范式(BCNF) 第四范式(4NF) 第五范式(5NF)

27 7.2.2 范式 各种范式之间存在联系： 某一关系模式R为第n范式，可简记为R∈nNF。
一个低一级范式的关系模式，通过模式分解可以转换为若干个高一级范式的关系模式的集合，这种过程就叫规范化 。

28 1NF 第一范式:如果一个关系模式R的所有属性都是不可分的基本数据项，则R∈1NF
第一范式是对关系模式的最起码的要求。不满足第一范式的数据库模式不能称为关系数据库 但是满足第一范式的关系模式并不一定是一个好的关系模式

29 1NF

30 2NF 第二范式:如果R(U,F)∈1NF，并且R中的每个非主属性都完全函数依赖于主码，则R(U,F)∈2NF
例: S-L-C（Sno,Sdept,SLOC,Cno,Grade） 函数依赖包括： (Sno, Cno) F Grade Sno → Sdept (Sno, Cno) P Sdept Sno → Sloc (Sno, Cno) P Sloc Sdept → Sloc 存在部分函数依赖，不是2NF。

31 2NF（续） S-L-C Sno Sdept Grade Cno Sloc S-L-C的码为(Sno, Cno) S-L-C满足第一范式。
非主属性Sdept和Sloc部分函数依赖于码(Sno, Cno) Grade Cno Sloc

32 2NF（续） S-L-C不是一个好的关系模式 原因 Sdept、 Sloc部分函数依赖于码。 解决方法

33 2NF（续） 分解办法 首先，对于组成主码的属性集合的每一个子集，用它作为主码构成一个表。 然后，将依赖于这些主码的属性放置到相应的表中。
最后，去掉只由主码的子集构成的表。 S-L-C分解为两个关系模式 SC（Sno， Cno， Grade） S-L（Sno， Sdept， Sloc）

34 2NF（续） 分解示例 S-L（Sno，…） C（Cno，…） S-C（Sno, Cno,…） S-L（Sno，Sdept, Sloc）
对于S-L-C表，首先分解为如下形式的三张表： S-L（Sno，…） C（Cno，…） S-C（Sno, Cno,…） 然后，将依赖于这些主码的属性放置到相应的表中 S-L（Sno，Sdept, Sloc） C（Cno） S-C（Sno, Cno, Grade） 最后，去掉只由主码的子集构成的表，最终分解为： S-L（Sno,Sdept, Sloc）

35 2NF（续） 函数依赖图： S-L SC Sdept Sno Sno Sloc Grade Cno 关系模式SC的码为（Sno，Cno）
关系模式S-L的码为Sno 这样非主属性对码都是完全函数依赖

36 2NF（续） S-L-C(Sno, Sdept, Sloc, Cno, Grade) ∈1NF
SC（Sno， Cno， Grade） ∈ 2NF S-L（Sno， Sdept， Sloc） ∈ 2NF

37 2NF（续） 采用投影分解法将一个1NF的关系分解为多个2NF的关系，可以在一定程度上减轻原1NF关系中存在的插入异常、删除异常、数据冗余度大、修改复杂等问题。 将一个1NF关系分解为多个2NF的关系，并不能完全消除关系模式中的各种异常情况和数据冗余。

38 2NF（续） S-L（Sno,Sdept, Sloc）存在问题 数据冗余 ：有多少个学生就有多少个重复的Sdept和SLOC；
插入异常：当新建一个系时，若还没有招收学生，则无法插入； Sno Sname Ssex Sdept Sloc 李勇 男 计算机系 2公寓 刘晨 吴宾 女 信息系 1公寓 张海 钱小平 数学系 王大力

39 3NF 若R∈3NF，则每一个非主属性既不部分依赖于码也不传递依赖于码。 3NF的定义
关系模式R<U，F> 中若不存在这样的码X、属性组Y及非主属性Z（Z  Y）, 使得X→Y，Y→Z成立，Y → X，则称R<U，F> ∈ 3NF。 若R∈3NF，则每一个非主属性既不部分依赖于码也不传递依赖于码。

40 3NF（续） 例：2NF关系模式S-L(Sno, Sdept, Sloc)中 函数依赖： Sno→Sdept Sdept → Sno
可得： Sno→Sloc，即S-L中存在非主属性对码的传递函数依赖，S-L ∈ 3NF 传递

41 3NF（续） 函数依赖图： S-L Sno Sdept Sloc

42 3NF（续） 采用投影分解法，把S-L分解为两个关系模式，以消除传递函数依赖： D-L（Sdept，Sloc）
解决方法 采用投影分解法，把S-L分解为两个关系模式，以消除传递函数依赖： S-D（Sno， Sdept） D-L（Sdept，Sloc） S-D的码为Sno， D-L的码为Sdept。 分解后的关系模式S-D与D-L中不再存在传递依赖

43 3NF（续） 分解过程 对于不是候选码的每个决定因子，从表中删去依赖于它的所有属性； S-D（Sno,Sdept）（主码为Sno）
新建一个表，新表中包含在原表中所有依赖于该决定因子的属性； S-L（Sdept,Sloc） 将决定因子作为新表的主码。 S-L的 主码为Sdept

44 S-L(Sno, Sdept, Sloc) ∈ 2NF S-L(Sno, Sdept, Sloc) ∈ 3NF
S-D的码为Sno， D-L的码为Sdept Sno Sdept S-D Sloc D-L S-L(Sno, Sdept, Sloc) ∈ 2NF S-L(Sno, Sdept, Sloc) ∈ 3NF S-D(Sno，Sdept) ∈ 3NF D-L(Sdept，Sloc)∈ 3NF

45 3NF（续） 违反3NF的传递依赖的三种情况

46 3NF（续） 采用投影分解法将一个2NF的关系分解为多个3NF的关系，可以在一定程度上解决原2NF关系中存在的插入异常、删除异常、数据冗余度大、修改复杂等问题。 将一个2NF关系分解为多个3NF的关系后，仍然不能完全消除关系模式中的各种异常情况和数据冗余。

47 BC范式（BCNF） 关系模式R<U，F>∈1NF，若X→Y且Y  X时X必含有码，则R<U，F> ∈BCNF。
等价于：每一个决定属性因素都包含码

48 BCNF（续） 若R∈BCNF R ∈BCNF R ∈3NF 所有非主属性对每一个码都是完全函数依赖
所有的主属性对每一个不包含它的码，也是完全函数依赖 没有任何属性完全函数依赖于非码的任何一组属性 R ∈BCNF R ∈3NF 充分 不必要

49 BCNF（续） 例：关系模式C（Cno，Cname，Pcno） C∈3NF C∈BCNF
例：关系模式S（Sno，Sname，Sdept，Sage） 假定S有两个码Sno，Sname S∈3NF S ∈BCNF

50 BCNF（续） 函数依赖：（S，J）→P；(J，P）→S （S，J）与（J，P）都可以作为候选码,属性相交 SJP∈3NF SJP∈BCNF
例：关系模式SJP（S，J，P） 函数依赖：（S，J）→P；(J，P）→S （S，J）与（J，P）都可以作为候选码,属性相交 SJP∈3NF SJP∈BCNF

51 BCNF（续） 例：在关系模式STC（S，T，C）中，S表示学生，T表示教师，C表示课程。 函数依赖：
(S，C)→T，(S，T)→C，T→C (S，C)和(S，T)都是候选码

52 BCNF（续） S C T STC中的函数依赖 C

53 BCNF（续） STC∈3NF  没有任何非主属性对码传递依赖或部分依赖  STC∈BCNF T是决定因素，T不包含码

54 BCNF（续） 解决方法：将STC分解为二个关系模式： ST(S，T) ∈ BCNF， TC(T，C)∈ BCNF
没有任何属性对码的部分函数依赖和传递函数依赖 S T ST C TC

55 BCNF（续） 主属性对候选键的传递依赖

56 3NF与BCNF的关系 R ∈BCNF R ∈3NF 如果R∈3NF，且R只有一个候选码 充分 不必要 充分 必要

57 多值依赖与4NF（了解） 多值依赖定义 设R(U)是一个属性集U上的一个关系模式， X、 Y和Z是U的子集，并且Z＝U－X－Y。关系模式R(U)中多值依赖 X→→Y成立，当且仅当对R(U)的任一关系r，给定的一对（x，z）值，有一组Y的值，这组值仅仅决定于x值而与z值无关。 关系模式R<U，F>∈1NF，如果对于R的每个非平凡多值依赖X→→Y（Y  X），X都含有码，则R∈4NF。

58 规范化举例 设有关系模式：Student（学号，姓名，导师号，导师名，课程号，课程说明，成绩） 语义：一名学生只有一个导师，学生可选多门课。
将其规范化成3NF的。

59 1．此表是1NF，其函数依赖为： 学号 p 姓名，学号 p 导师号，学号 p 导师名， 课程号 p 课程说明, (学号，课程号)→成绩 主码为（学号，课程号） 存在部分函数依赖关系，不是2NF， 首先将其分解为2NF。 学生（学号，姓名，导师号，导师名）， 课程（课程号，课程说明）， 成绩（学号，课程号，成绩） 均为2NF

60 2．判是否为3NF “学生”表不是3NF，其函数依赖为： 学号→姓名，学号→导师号， 导师号 p 导师名，∴学号传递导师名 消除依赖于决定者的属性，把它们放在一个单独的表中，得到： 学生（学号，姓名，导师号），导师（导师号，导师名）

61 7.3 关系模式的分解准则 模式分解要满足： 模式分解具有无损连接性； 模式分解能够保持函数依赖。
无损连接是指分解后的关系通过自然连接可以恢复成原来的关系，即通过自然连接得到的关系与原来的关系相比，既不多出信息、又不丢失信息。 保持函数依赖分解是指在模式的分解过程中，函数依赖不能丢失的特性，即模式分解不能破坏原来的语义。

62 关系模式的分解准则（续） 例：S-D-L（Sno，Dept，Loc）有函数依赖： Sno → Dept， Dept → Loc
不是第三范式的。至少可以有三种分解方案，分别为： 方案1：S-L（Sno，Loc），D-L（Dept，Loc） 方案2：S-D（Sno，Dept），S-L（Sno，Loc） 方案3：S-D（Sno，Dept），D-L（Dept，Loc） 这三种分解方案得到的关系模式都是第三范式的，那么如何比较这三种方案的好坏呢？由此在将一个关系模式分解为多个关系模式时除了提高规范化程度之外，还需要考虑其他的一些因素。

63 关系模式的分解准则（续） 将一个关系模式R<U，F>分解为若干个关系模式R1<U1，F1>，R2<U2，F2>，…，Rn<Un，Fn>，意味着将存储在一张二维表r中的数据分散到了若干个二维表r1，r2，…，rn中。这样的分解应该不丢失信息，即能通过对关系r1，r2，…，rn的自然连接运算重新得到关系r中的所有信息。 事实上，将关系r投影为r1，r2，…，rn时不会丢失信息，关键是对r1，r2，…，rn做自然连接时可能产生一些r中原来没有的元组，从而无法区别哪些元组是r中原来有的，即数据库中应该存在的数据，哪些是不应该有的。在这个意义上就丢失了信息。

64 关系模式的分解准则（续） Sno Dept Loc S01 D1 L1 S02 D2 L2 S03 D3 L3 S04 D4 L4
这三种分解方案是否都满足分解要求呢？ 假设此关系模式的数据如表所示，此关系用r表示。 Sno Dept Loc S01 D1 L1 S02 D2 L2 S03 D3 L3 S04 D4 L4

65 关系模式的分解准则（续） 若按方案1将S-D-L投影到S-L和D-L的属性上，得到如左边两个表所示的关系。做自然连接得到结果如右表所示。
Sno Dept Loc S01 D1 L1 D3 S02 D2 L2 S03 S04 L41 Sno Loc S01 L1 S02 L2 S03 L3 S04 L4 Dept Loc D1 L1 D2 L2 D3 L3 D4 L4

66 关系模式的分解准则（续） 无损连接性 将关系模式R<U，F>分解为个关系模式R1<U1，F1>，R2<U2，F2>，…，Rn<Un，Fn>，若对于R中的任何一个可能的r，都有r ＝r1*r2*…*rn，即r在R1，R2，…，Rn上的投影的自然连接等于r，则称关系模式R的这个分解具有无损连接性。

67 关系模式的分解准则（续） 再分析方案2。将S-D-L投影到S-D，S-L的属性上，得到的关系如左边两个表所示。做自然连接得到的关系右表所示。
Sno Dept S01 D1 S02 D2 S03 D3 S04 D4 Sno Loc S01 L1 S02 L2 S03 L3 S04 L4 Sno Dept Loc S01 D1 L1 S02 D2 L2 S03 S04 D3

68 关系模式的分解准则（续） 方案2自然连接后恢复成了原来的关系，因此，分解方案2具有无损连接性。
但分解方案2没有保持原有的函数依赖关系，也不是好的分解方法。

69 关系模式的分解准则（续） 分解方案3既满足无损连接性，又保持了原有的函数依赖关系，因此它是有个好的分解方法。
分解具有无损连接性和分解保持函数依赖是两个独立的标准。具有无损连接性的分解不一定保持函数依赖；保持函数依赖的分解不一定具有无损连接性。 一般情况下，在进行模式分解时，应将有直接依赖关系的属性放置在一个关系模式中，这样得到的分解结果一般能具有无损连接性，并能保持函数依赖关系不变。

70 规范化小结 关系数据库的规范化理论是数据库逻辑设计的工具 目的：尽量消除插入、删除异常，修改复杂，数据冗余
基本思想：逐步消除数据依赖中不合适的部分 实质：概念的单一化

71 规范化小结（续） 关系模式规范化的基本步骤 1NF ↓ 消除非主属性对码的部分函数依赖 消除决定属性 2NF
↓ 消除非主属性对码的部分函数依赖 消除决定属性 2NF 集非码的非平 ↓ 消除非主属性对码的传递函数依赖 凡函数依赖 NF ↓ 消除主属性对码的部分和传递函数依赖 BCNF ↓ 消除非平凡且非函数依赖的多值依赖 4NF

72 规范化小结（续） 不能说规范化程度越高的关系模式就越好
在设计数据库模式结构时，必须对现实世界的实际情况和用户应用需求作进一步分析，确定一个合适的、能够反映现实世界的模式 上面的规范化步骤可以在其中任何一步终止

73 作业 设某商业集团数据库中有一个关系模式为： 该模式的关系记载每个顾客在每个商店的累计消费总额。如果规定：
R（商店编码，顾客编码，消费总额，顾客单位，地址，电话） 该模式的关系记载每个顾客在每个商店的累计消费总额。如果规定： ①每个顾客在每个商店只有一个消费总额； ②每个顾客只属于一个单位； ③每个顾客单位只有一个地址、一个电话。 试回答下列问题： （1）说明R不是2NF的理由，并把R分解成2NF模式集。 （2）进而分解成3NF模式集。