《数据库技术与设计》课件第4章 关系数据库的模式设计

第4章关系数据库的模式设计Chapter4PatternDesignofRelationDatabase本章导言关系数据库的模式设计主要是设计关系模式，而深入理解函数依赖和键码的概念则是设计和分解关系模式的基础。

本章要点关系模式的存储异常和数据依赖函数依赖的概念函数依赖的规则关系的规范化模式分解的优劣典型案例分析4.1关系模式的存储异常和数据依赖

关系数据库模式是若干关系模式的集合。

所谓关系数据库的模式设计实际上就是从多种可能的组合中选取一个合适的或者说性能好的关系模式集合作为关系数据库模式的问题。例1已知描述学生和系的一些情况，面临的对象有：学号(SNO)、姓名(SNAME)、系名(DEPT)、系负责人(MN)、课程号（CNO)、课程名（CNAME)和成绩(GRADE)。

现实世界的事实是：一个系有若干学生，但一个学生只属于一个系；一个系只有一名负责人；一个学生可以选修多门课程，每门课程有若干学生选修；每个学生学习每一门课程有一个成绩。

方案一：采用一个总的关系模式：

SA(SNO，SNAME，DEPT，MN，CNO，

CNAME，GRADE)方案二：采用四个关系模式：

S(SNO，SNAME，DEPT)、D(DEPT，MN)、

SC(SNO，CNO，GRADE)、C(CNO，CNAME)

比较起来，第一个方案可能带来下列问题：

1．数据冗余;

2.修改异常或潜在的不一致性;3.插入异常;4.删除异常。问题:产生这种存储异常的根源何在？

第二方案在性能上优于第一方案。

4.2函数依赖的概念4.2.1函数依赖的定义定义4.1

设R(U)是属性集U上的关系模式，X,Y是U的子集。若对于R(U)的任意一个可能的关系r，r中不可能存在两个元组在X上的属性值相等，而在Y上的属性值不等，则称“X函数确定Y”或“Y函数依赖于X”，记作X→Y。7.设有关系模式R（A，B，C），其关系r如右表所示：

例如：R（A，B，C），其关系r如下表所示。判断下面陈述正确与否？函数依赖A→B在r中成立

函数依赖BC→A在r中成立函数依赖B→A在r中成立函数依赖A→BC在r中成立根据例1的语义可得出SA的函数依赖：F={SNO→SNAME，SNO→DEPT，

DEPT→MN,CNO→CNAME，(SNO，

CNO)→GRADE}下面介绍一些记号和术语：

1．X→Y，但Y

X，则称X→Y是非平凡的函数依赖。若不特别声明，我们总是讨论非平凡的函数依赖。

2、若X→Y，Y→X，则记作:X

Y3．若Y不函数依赖于X，则记作:XY4.2.2完全函数依赖和部分函数依赖定义4.2

在R(U)中，如果X→Y，并且对于X的任何一个真子集X’，都有X’Y，则称Y对X完全函数依赖，记作:

上述完全函数依赖定义可用下图表示:

在R(U)中，如果X→Y，并且存在X的一个真子集X0，使得X0→Y，则称Y对X部分函数依赖，记作:

上述部分函数依赖定义可用下图表示:

4.2.3传递函数依赖定义4.3

在R(U)中，如果X→Y，(Y

X)，YX，Y→Z，则称Z对X传递函数依赖。记作上述传递函数依赖定义可以用下图表示:

思考题：

在SA(SNO，SNAME，DEPT，MN，CNO，CNAME，GRADE)中，有函数依赖集：F={SNO→SNAME，SNO→DEPT，DEPT→MN,CNO→CNAME，(SNO，CNO)→GRADE}问题：下面的函数依赖表示正确吗？4.2.4关系模式的键码定义4.4

已知R＜U，F＞是属性集U上的关系模式，F是属性集U上的一组数据依赖。设K为R＜U，F＞中的属性或属性组合，若K

U-K且K的任何真子集都不能决定U,则K为R的键码。

问题：判断关系的键码归纳起来有哪二点？

包含键码的属性集称为超键码，它是“键码的超集”的简称。若键码多于—个，则选定其中之一作为主键码。包含在任何一个键码中的属性，叫做主属性，不包含在任何键码中的属性称为非主属性。最简单的情况，单个属性是键码；最极端的情况，整个属性组是键码，称为全码。

例2考虑关系模式：人(身份证号，姓名，性别，住址，出生年月)，在此模式中存在以下函数依赖关系：身份证号→姓名，性别，住址，出生年月

(姓名，住址)→身份证号，性别，出生年月例3关系模式R(演奏者，作品，听众)。假设一个演奏者可以演奏多个作品，某一作品可被多个演奏者演奏，听众也可以欣赏不同演奏者的不同作品，这个关系模式的键码为：(演奏者，作品，听众)，即全码。

思考题：1、已知R(A,B,C,D),F={A

B,B

C,A

D}，求：（1）R的键码；（2）A

B,(A,B)

C,AC分别是何种函数依赖？2、已知S(A,B,C),F={(A,B)C),(A,C)B)}，求：（1）S的键码；（2）S的主属性和非主属性。定义4.5

关系模式R中属性或属性组X并非R的键码，但X是另一个关系模式的键码，则称X是R的外键码。例如在SC(SNO，CNO，GRADE)中，SNO不是键码，但SNO是关系模式S(SNO，SNAME，DEPT)的键码，则SNO对关系模式SC来说是外键码。思考题：主键码与外键码提供了一个什么样的手段？发挥了什么样的作用？4.3函数依赖的规则4.3.1三个推理规则1．分解／合并规则(1)把一个函数依赖A1A2…An

BlB2…Bm用一组函数依赖A1A2…An

Bi(i＝l，2，…，m)来代替，这种转换称为“分解规则”(splittingrule)。(2)把一组函数依赖A1A2…An

Bi(i＝l，2，…，m)用一个依赖A1A2…An

BlB2…Bm来代替，这种转换称为“合并规则“(combiningrule)。2．平凡依赖规则在介绍平凡依赖规则之前，先介绍平凡函数依赖和非平凡函数依赖这两个概念。

设A={A1A2…An}，B={BlB2…Bm}，对于函数依赖A1A2…An

BlB2…Bm，(1)如果B是A的子集，则称该依赖为平凡的函数依赖。(2)如果B中至少有一个属性不在A中，则称该依赖为非平凡的函数依赖。(3)如果B中没有一个属性在A中，则称该依赖为完全非平凡的函数依赖。举例：对于关系Student(SNO,SNAME,SDEPT,MNAME,CNAME,GRADE)，其属性含义依次为学号、姓名、所在系、系主任姓名、课程名、成绩。我们举例如下：

Sno,Cname,Grade→Cname,Grade，此函数依赖属于平凡的函数依赖。

Sno,Cname→Cname,Grade，此函数依赖属于非平凡的函数依赖。

Sno,Cname→Sname,Grade，此函数依赖属于完全非平凡的函数依赖。

如果函数依赖右边的属性中有一些也出现在左边，那么我们就可以将右边的这些属件删除。即：函数依赖A1A2…An

BlB2…Bm等价于A1A2…An

ClC2…CK，其中C={ClC2…CK}是B的子集，但不在A中出现(这里A={A1A2…An}，B={BlB2…Bm})。我们称这个规则为“平凡依赖规则"(trivialdependencyrule)。3．传递规则传递规则能将两个函数依赖级联成一个新的函数依赖。如果A1A2…An

BlB2…Bm和BlB2…Bm

ClC2…CK在关系R中成立，则A1A2…An

ClC2…CK在R中也成立。这个规则就称为“传递规则”(transitiverule)。例如，在关系Student中，有如下两个依赖成立：

SNO

SDEPT，SDEPT

MNAME

根据传递规则，可以把上面两个函数依赖组合起来，得到一个新的函数依赖：

SNO

MNAME

4.3.2闭包的计算

假设A={A1,A2,

,An}是属性集，F是函数依赖集。属性集A在依赖集F下的闭包A+是这样的属性集X，它表示A1A2…An

X是蕴含于F中的函数依赖。

即：若{A1,A2,

,An}+=X，则有

A1A2…An

X且此函数依赖是蕴含于F中的函数依赖。求解属性集{A1,A2,

,An}在某函数依赖集下的闭包的计算过程如下：

1．属性集X最终将成为闭包。首先，将X初始化为{A1,A2,

,An}，即：X={A1,A2,

,An}。

2．然后，反复地检查某个函数依赖

BlB2…Bm

C使得所有的BlB2…Bm都在属性集X中，但C不在其中，于是将C加到属性集X中。

3．根据需要多次重复步骤2，直到没有属性能加到X中。由于X是只增的，而任何关系的属性数目必然是有限的，因此最终再也没有属性可以加到X中。

4．最后得到的不能再增加的属性集X就是{A1,A2,

,An}+的正确值。

例4考虑一个关系R(A,B,C,D,E,F),其函数依赖集为F={AB

C，BC

AD，D

E,CF

B},试计算{A,B}的闭包{A,B}+。

从X＝{A,B}出发。首先，函数依赖AB

C左边的所属性都在X中，而右边属性不在X中，故可以将C加到X中。这样，X={A,B,C}。

同理，BC

AD的左边包含在X中，而右边属性不在X中，故X={A，B，C，D}。又因D

E，故X={A,B,C,D,E}。函数依赖CF

B不满足条件，所以{A,B}+＝{A，B，C，D，E}。

例5已知关系模式R(A,B,C,D)，其函数依赖集F={AB

C，C

D，D

A}，求蕴含于给定函数依赖的所有非平凡函数依赖。单属性：A+＝A，B+＝B，C+＝ACD，D+＝AD新依赖：C

A(1)双属性：AD+＝AD，AB+＝ABCD，

AC+＝ACD，BC+＝ABCD，

BD+＝ABCD，CD+＝ACD新依赖：AB

D，AC

D，BC

A，BC

D，

BD

A，BD

C，CD

A(2)三属性：ABC+＝ABCD，ABD+＝ABCD，

ACD+＝ACD，BCD+＝ABCD新依赖：ABC

D，ABD

C，BCD

A(3)四属性：ABCD+＝ABCD新依赖：无

从上面的分析可以得出，蕴含于给定函数依赖的非平凡函数依赖总共有11个。

因为键码函数决定所有其他属性，所以键码属性的闭包必然是属性全集。反之，若某属性集的闭包为属性全集，则该属性集即为键码。当然，判断时应从最小属性集开始，以区别键码和超键码。

在本例中，有3个键码AB，BC和BD，分别用黄色表示；有4个超键码ABC，ABD，BCD和ABCD，分别用兰色表示。

例6若R(A,B,C,D,E),F={AB→C,B→D,D→E,C→B}试求关系模式R的键码。解：（1）先考察单属性A+=A，B+=BDE，C+=CBDE，D+=DE，E+=E

（2）再考察双属性AB+=ABCDE，AC+=ACBDE，AD+=ADE，AE+=AE，BC+=BCDE，BD+=BDE，BE+=BED，CD+=CDEB，CE+=CEB，DE+=DE

由于AB和AC能确定ABCDE，所以AB和AC肯定是关系模式R的键码。若还有三属性的闭包满足XYZ+=ABCDE的话，且XYZ不是超键码的话，则XYZ也为键码。

4.4关系的规范化

4.4.1第一范式定义4.6设R是一个关系模式，如果R中每一个属性值域中的每一个值都是不可分解的，则称R是属于第一范式的，记作R∈1NF。例6

4.4.2第二范式定义4.7若R∈1NF，且每一个非主属性完全函数依赖于键码，则R∈2NF。例7（1）考察方案一：SA(SNO，SNAME，DEPT，MN，CNO，

CNAME，GRADE)SA的键码：(SNO，CNO)SA的函数依赖集：F={SNO→SNAME，SNO→DEPT，DEPT→MN,CNO→CNAME，(SNO，CNO)→GRADE}

SA的键码为：(SNO，CNO)；

SA的非主属性有：SNAME、DEPT、MN、CNO和GRADE；

因为SNAME部分函数依赖于(SNO，CNO)，所以由定义4.7可知，SA

2NF。（2）存在的问题一个关系模式R不属于2NF，就会产生以下问题：

(1)插入异常

(2)删除异常

(3)修改复杂

（3）解决的办法：用投影分解

通过投影分解，消除非主属性对键码的部分函数依赖。下面我们按“一事一地”原则将SA分解为三个关系模式：

SD(SNO,SNAME,DEPT,MN)2NFC(CNO,CNAME)2NFSC(SNO,CNO,GRADE)2NF

4.4.3第三范式定义4.8若R∈2NF，且每一个非主属性均不传递函数依赖于键码，则R∈3NF。

例8

（1）考察SD(SNO,SNAME,DEPT,MN)2NFC(CNO,CNAME)2NFSC(SNO,CNO,GRADE)2NF经过分析知：SD3NF，

C3NF，SC3NF

经过分析知道：

R1

3NF，R2

3NF，R3

3NF

（2）存在的问题。一个关系模式SD若不是3NF，就会产生与4.4.2中相类似的问题：（1）插入异常；（2）删除异常；（3）修改复杂（3）解决的办法：用投影分解。

我们按“一事一地”的原则将SD分解为两个关系模式：

S(SNO，SNAME，DEPT)和D(DEPT，MN)

在分解后的关系模式S与D中，已不存在部分函数依赖和传递函数依赖，故按定义知：

S

3NF，D

3NF。

4.4.4BCNF范式定义4.9关系模式R

U，F

∈1NF，若对于任一X→Y(Y

X)，X必含有键码，则R

U,F

BCNF。

也就是说，在关系模式R＜U，F＞中，若每一个决定因素都包含键码，则R<U,F>

BCNF。

由于3NF不能很好地处理含有多个键码和键码是组合项的情况，因此人们定义了一个更强的范式BCNF。

可以证明（略）：若R

BCNF，则R

3NF；但是若R

3NF，则未必属于BCNF。

例9考察SJP(学生，课程，名次)该模式中的元组含义是：每一个学生、每门课程有一定的名次；每门课程中每一名次只有一个学生。由语义得：

F={(学生，课程)→名次，(名次，课程)→学生}(学生，课程)与(名次，课程)均为键码，此关系模式中显然没有非主属性对键码的传递函数依赖或部分函数依赖，所以SJP

3NF。另外，除键码以外没有其它决定因素，所以SJP

BCNF。例10（1）考察SJT(学生，课程，教师)

该模式中的元组含义是：某个学生学习某个教师开设的某门课程。现假定存在以下附加条件：

1)对于每门课、每个学生的讲课老师只有一位；2)每位教师只讲授一门课；3)每门课可由不同教师讲授。由语义可得到如下的函数依赖：

F={(学生，课程)→教师，教师→课程}

用函数依赖图表示如下图所示：

该模式有二个键码(学生，课程)、(学生，教师)，由于没有任何非主属性对键码的部分函数依赖和传递函数依赖，所以SJT

3NF；但SJT

BCNF，因为教师→课程，而教师不是键码。

（2）存在的问题。不满足BCNF范式的关系模式同样存在着更新异常。例如在SJT(学生，课程，教师)中，如果存在元组(S4,J3,Tl)，当我们删除信息“学生S4学习J3课”时，将同时失去“T1教师主讲J3课”这一信息。产生更新异常的原因是，属性教师是决定因子，但却不是键码。（3）解决的办法：用投影分解。若按“一事一地”的原则将SJT分解为两个关系模式：ST（学生，教师）和TJ（教师，课程）则ST

BCNF，TJ

BCNF，并且如此分解确实解决了上述更新异常的问题。问题：上述分解是否唯一？4.4.5多值依赖和第四范式

从1NF—3NF、BCNF是在函数依赖的范畴内讨论问题，下面从多值依赖来考虑。例11

学校中某一门课程由多个教员讲授，他们使用相同的一套参考书，可以用一个非规范化的关系来表示教员、课程和参考书之间的关系。

将上表变成一张规范化的二维表，就成为：

上述二维表对应的关系模式CTB(课程、教员、参考书)的键码是全码，因而CTB

BCNF，但还存在很多问题。

1.多值依赖定义4.10设R(U)是属性集U上的一个关系模式，X、Y、Z是U的子集，并且Z=U-X-Y，多值依赖X→→Y成立当且仅当对R(U)的任一关系r，给定的一对(x,z)值有一组Y值，这组值仅仅决定于x值而与z值无关。

例12

关系模式WSC(W，S，C)中,W表示仓库,S表示保管员,C表示商品。假设每个仓库有若干个保管员，有若干种商品，每个保管员保管所在的仓库的所有商品，每种商品被所有保管员保管。经分析，W→→S成立，由于C与S的完全对称性，因此可以得到W→→C成立。

由于C与S的完全对称性，必然有W→→C成立。

多值依赖具有以下性质：(1)若X→→Y，则X→→Z，其中Z＝U-X-Y，即多值依赖具有对称性。(2)若X→Y，则X→→Y，即函数依赖可以看作多值依赖的特殊情况。这是由于当X→Y时，对X的每一个值x，Y有一个确定的值y与之对应，所以X→→Y。(3)若X→→Y，而Z＝Ф，则称X→→Y为平凡的多值依赖。

注意：函数依赖X→Y的有效性仅决定于X、Y这两个属性集的值，而多值依赖的有效性却与属性集的范围有关。

2.第四范式定义4.11关系模式R＜U,F＞

1NF，若X→→Y(Y

X)是非平凡的多值依赖，且X含有键码，则称R＜U，F＞

4NF。例13非4NF的关系模式的例子（1）以CTB(课程，教师，参考书)为例。在CTB中，课程→→教师，课程→→参考书，它们都是非平凡的多值依赖，而CTB的键码是全码，即键码为(课程，教师，参考书)，因课程不是键码，故按照4NF的定义有CTB

4NF。

(2)存在的问题

因CTB键码是全码，故CTB

BCNF，但CTB

4NF。对于CTB的某个关系，若某一课程有n个教员可以讲授，有m本参考书可供参考，则关系中有关该课程的元组数目一定有

m×n个。每个教员重复存储m次，每本参考书重复存储n次，数据的冗余度太大，从而造成了更新异常，因此还应该继续规范化，使关系模式CTB达到4NF。

(3)解决的办法:采用投影分解

将关系模式CTB分解为：

CT(课程，教师)，CB(课程，参考书)

在CT中虽然有课程→→教师，但这是平凡的多值依赖，CT中已不存在非平凡的多值依赖，所以CT

4NF。同理CB

4NF。

4.5模式分解的优劣4.5.1模式分解的等价性

当某些关系模式存在存储异常现象时，通过模式分解可使范式的级别提高，从而得到一个性能较好的关系数据库模式设计。

分解的等价性概念有三种不同的含义：分解具有“无损联接”分解要“保持函数依赖”

分解既要“保持函数依赖”，又要具有“无损联接性”

这三个含义是实行分解的三条不同的准则。定义4.12设R(W)是一个关系模式，ρ＝{R1(W1)，R2(W2)，…，Rk(Wk)}是一个关系模式的集合，如果

W1∪W2∪…∪Wk=W，则称ρ是R(W)的一个分解。

只要求R(W)分解后的各关系模式所含属性的“并”等于W，这个限定是很不够的，能否消除存储异常，不仅依赖于分解后各模式的范式程度，而且依赖于分解的方式。下面我们通过一个例子来说明。

例14已知一个学生(SNO)只在一个系(DEPT)学习，一个系只有一名系主任(MN)，关系模式R(SNO，DEPT，MN)上的函数依赖集为：

F={SNO→DEPT，DEPT→MN}

由于R中存在MN对SNO的传递函数依赖，故它会发生更新、删除和插入异常。

下面进行以下四种形式的分解：

1．将R(SNO，DEPT，MN)分解为：

R1(SNO),R2(DEPT)和R3(MN)

这三张表的模式显然全是BCNF范式。但这三张表互相之间没有任何联系，故此分解毫无意义。2.将R(SNO，DEPT，MN)分解为：

R1(SNO，MN)和R2(DEPT，MN)

此分解保持了无损联接性，但没有保持函数依赖。虽然分解后R1和R2均是BCNF范式，但仍有插入和删除异常问题。

3.将R(SNO，DEPT，MN)分解为：

R1(SNO，DEPT)和R2(SNO，MN)此分解保持了无损联接性，但没有保持函数依赖。虽然分解后R1和R2均是BCNF范式，但仍有插入和删除异常问题。

4.将R(SNO，DEPT，MN)分解为:

R1(SNO，DEPT)和R2(DEPT，MN)

可以证明该分解既具有无损联接性，又保持了函数依赖。此分解既解决了更新异常，又没有丢失原数据库的信息，这是人们所希望的分解。

4.5.2模式分解的规则和方法1、模式分解的两个规则（1）共享公共属性（2）合并相关属性例15SA(SNO，SNAME，DEPT，MN，CNO，CNAME，GRADE)为例进行说明。

分解后的两个模式R1和R2能实现无损连接的充分必要条件是：

(R1∩R2)

(R1-R2)或(R1∩R2)

(R2-R1)

2、模式分解的三种方法：（1）部分依赖归子集，完全依赖随键码。（2）基本依赖为基础，中间属性作桥梁。（3）找违例自成一体，舍其右全集归一；若发现仍有违例，再回首如法炮制。设关系模式为R(A，B，C)，其中A，B，C均为属性集，若存在违背BC范式的函数依赖A

B，则可以以BC范式的违例为基础把关系模式分解为：

{A，B}

{A，C}或{R-B}

例16设STC(Sname,Tname,Cname,Grade)，且

F={(Sname,Cname)

(Tname,Grade),(Sname,Tname)

(Cname,Grade),Tname

Cname}键码为：（Sname,Cname）和(Sname,Tname)。

由于决定因素Tname没有包含键码，故STC

BCNF。在分析函数依赖集后，可以确定如下函数依赖就是BC范式的违例：Tname

Cname

根据模式分解的方法，分解后二个关系模式如下：R1（Tname，Cname）

BCNFR2（Sname，Tname，Grade）

BCNF例17（略）4.6典型案例分析典型案例1---产品订货系统关系数据库模式的设计1、案例描述

在一个产品订货系统数据库中，有一个关系模式如下：订货(订单号，订购单位名，地址，产品型号，

产品名，单价，数量)要求：

（1）给出你认为合理的函数依赖；

（2）给出一组满足第三范式的关系模型。2、案例分析1）产品订货系统数据库的函数依赖如下：F={订单号

订购单位名，订单号

地址，

产品型号

产品名,产品型号

单价,(订单号，产品型号)

数量}2）根据函数依赖，可以画出函数依赖图如下：3、案例实现

根据函数依赖图，可以投影分解如下：R1（订单号，订购单位名，地址），F1={订单号

订购单位名，订单号

地址}R2（产品型号，产品名,单价），F2={产品型号

产品名,产品型号

单价}R3(订单号，产品型号，数量),F3={(订单号，产品型号)

数量}根据函数依赖和3NF的判定条件，可以确定R1、R2和R3均属于3NF。问题:R1、R2和R3也属于BCNF吗？典型案例2---在线考试系统关系数据库模式的设计1、案例描述

根据第3章3.5节典型案例2，考生信息属性包括：考生学号、考生姓名、考生密码、考生性别、考生班级和注册日期；试卷信息属性包括：试卷编号、判断题数量、判断题每题分数、选择题数量、选择题每题分数、填空题数量、填空题