数据库系统简明教程课件11

第十一章

关系数据库设计理论

问题的提出如何把现实世界表达成数据库模式？针对一个具体应用，应该如何构造一个适合于它的数据库模式？这是数据库的逻辑设计问题。关系数据库的模式设计理论是数据库逻辑设计的理论基础。本章主要内容数据依赖对关系数据库的影响函数依赖关系模式的范式关系模式的规范化数据依赖的公理系统11.1数据依赖对关系模式的影响关系模式设计不规范会带来一系列的问题数据冗余、更新异常、插入异常、删除异常示例1:关系模式R(Tname,Addr,C#,Cname)一个教师只有一个地址（户口所在地）一个教师可教多门课程一门课程只有一个任课教师因此R的主码是（C＃）R的一个实例TnameAddrC#CnameT1A1C1N1T1A1C2N2T1A1C3N3T2A2C4N4T2A2C5N5T3A3C6N6问题（1）：数据冗余教师T1教了三门课程，他的地址被重复存储了2次。TnameAddrC#CnameT1A1C1N1T1A1C2N2T1A1C3N3T2A2C4N4T2A2C5N5T3A3C6N6问题（2）：更新异常如果T1的地址变了，则需要改变3个元组的地址；若有一个未更改，就会出现数据不一致。但DBMS无法获知这种不一致。TnameAddrC#CnameT1A1C1N1T1A1C2N2T1A1C3N3T2A2C4N4T2A2C5N5T3A3C6N6问题（3）：插入异常如果要增加一名教师，但他还未带课，则C#和Cname为空，但由于C＃是主码，为空违反了实体完整性，所以这名教师将无法插入到数据库中。TnameAddrC#CnameT1A1C1N1T1A1C2N2T1A1C3N3T2A2C4N4T2A2C5N5T3A3C6N6问题（4）：删除异常如果教师T3现在不带课了，则需将T3的元组删去，但同时也把他的姓名和地址信息删掉了。TnameAddrC#CnameT1A1C1N1T1A1C2N2T1A1C3N3T2A2C4N4T2A2C5N5T3A3C6N6如何解决？方法：模式分解方法1：R分解为R1(Tname,Addr)R2(C#,Cname)方法2R1(Tname,Addr,C#)R2(C#,

Cname)方法3R1(Tname,Addr)R2(Tname,C#,

Cname)授课信息丢失了R1中问题依然存在基本解决问题示例2：描述学校的数据库：学生的学号（Sno）、所在系（Sdept）系主任姓名（Mname）、课程名（Cname），成绩（Grade）单一的关系模式：

Student<U、F>其中：

U＝｛Sno,Sdept,Mname,Cname,Grade｝F＝｛Sno

→Sdept,Sdept

→Mname,

(Sno，Cname）→Grade｝数据依赖对关系模式的影响（续）学校数据库的语义：⒈一个系有若干学生，一个学生只属于一个系；⒉一个系只有一名主任；⒊一个学生可以选修多门课程，每门课程有若干学生选修；⒋每个学生所学的每门课程都有一个成绩。

SnoCnameGradeSdeptMnameStudent关系模式上的一组函数依赖关系模式Student<U,F>中存在的问题⒈数据冗余太大浪费大量的存储空间

例：每一个系主任的姓名重复出现⒉更新异常（UpdateAnomalies）数据冗余，更新数据时，维护数据完整性代价大。

例：某系更换系主任后，系统必须修改与该系学生有关的每一个元组关系模式Student<U,F>中存在的问题⒊插入异常（InsertionAnomalies）该插的数据插不进去例，如果一个系刚成立，尚无学生，我们就无法把这个系及其系主任的信息存入数据库。⒋删除异常（DeletionAnomalies）不该删除的数据不得不删 例，如果某个系的学生全部毕业了，我们在删除该系学生信息的同时，把这个系及其系主任的信息也丢掉了。数据依赖对关系模式的影响（续）结论：Student关系模式不是一个好的模式。“好”的模式不会发生插入异常、删除异常、更新异常，数据冗余应尽可能少。原因：由存在于模式中的某些数据依赖引起的解决方法：通过分解关系模式来消除其中不合适的数据依赖。规范化理论

规范化理论正是用来改造关系模式，通过分解关系模式来消除其中不合适的数据依赖，以解决插入异常、删除异常、更新异常和数据冗余问题。要解决的内容到底什么样的模式才最佳？怎么分解才能达到要求？标准是什么？如何实现？11.2函数依赖关系模式是对关系的描述，它由五部分组成：R（U，D，Dom，F）R为关系模式名;U是一个属性集;D是U中属性的值所来自的域;Dom是属性向域的映射集合;F是属性间的依赖关系。11.2.1

函数依赖定义11.1

设R(U)是一个关系模式，U是R的属性集合，X和Y是U的子集。对于R(U)的任意一个可能的关系r,如果r中不存在两个元组，它们在X上的属性值相同，而在Y上的属性值不同，则称“X函数确定Y”或“Y函数依赖于X”，记做X→Y。11.2.1函数依赖换一种说法：设关系模式R(A1,A2,…,An)或简记为R(U)，U是R的属性集合,X和Y是U的子集。r是R的任意一个实例（关系），若r的任意两个元组t1、t2，由t1[X]=t2[X]可导致t1[Y]=t2[Y]，即如果X相等则Y也相等，则称Y函数依赖于X或称为X函数决定Y，记作X→Y。11.2.1函数依赖即R的X属性集上的值可唯一决定R的Y属性集上的值。也即对于R的任意两个元组，X上的值相等，则Y上的值也必相等。重要说明1.函数依赖不是指关系模式R的某个或某些关系实例满足的约束条件，而是指R的所有关系实例均要满足的约束条件。2.函数依赖是语义范畴的概念。只能根据数据的语义来确定函数依赖。

例如“姓名→年龄”这个函数依赖只有在不允许有同名人的条件下成立。11.2.1函数依赖3.若XY，则X叫做决定因素。4.若XY，YX，则记做XY。5.若Y不函数依赖于X，则记做XY。完全函数依赖与部分函数依赖定义11.2在R(U)中,如果XY，并且对于X的任何一个真子集X’，都有X’Y，则称Y完全函数依赖于X，记做XY。否则称

称Y部分函数依赖于X。记做XY。在[例1]中（Sno,Cname)Grade

是完全函数依赖；（Sno,Cname)Sdept

是部分函数依赖；

PFF传递函数依赖定义11.3

在关系模式R(U)中，如果XY，YZ，且YX,YX,则称Z传递函数依赖于X，记作XZ。在[例1]中Sno

→Sdept,Sdept

→Mname成立，

所以Sno→Mname传递函数定义中YX是必须的，因为如果YX，则XY，这实际上是Z直接依赖于X，而不是传递函数依赖了。传递传递函数依赖例如，在关系Student(Sno,Sname,Ssex,Sage,Sdept)中，有SnoSsex,SnoSage,SnoSdept,

SnoSname(无重名），但SsexSage。传递函数依赖在关系SC(Sno,Cno,Grade)中，有SnoGrade,CnoGrade,(Sno,Cno)Grade，(Sno,Cno)是决定属性集。在关系Std(Sno,Sdept,Mname)中，有

SnoSdept,SdeptMname,SnoMname。传递F函数依赖的多对一关系函数依赖实际上是指一个关系模式中一个属性集和另一个属性集间的多对一关系

例如选课关系SC(S#,C#,Score)存在由属性集{S#,C#}到属性集{Score}的函数依赖对于任意给定的S#值和C#值，只有一个Score值与其对应反过来，可以存在多个S#值和C#值，它们对应的Score值相等11.2.1函数依赖FD是否成立，唯一办法是仔细考察应用中属性的含义。FD实际上是对现实世界的断言。数据库设计者在设计时把应遵守的函数依赖告知DBMS，则DBMS会自动检查关系的合法性。11.2.1函数依赖例如Student关系模式中，{S#}→{Sname}（单个属性可去掉括号，简写成S#→Sname）

SC关系模式中，{S#,C#}→{Score}对于关系模式R(Tname,Addr,C#,Cname)若一门课只能有一个教师，则有

{C#}→{Tname}若一门课可有多个教师任教，则有

{C#}→{Tname}不成立因此FD是与具体应用相关的。11.2.2码定义11.4：设K为关系模式R(U,F)中的属性或属性集合，若KU，则K为R的候选码(CandidateKey)。若候选码多于一个，则选定其中一个为主码(PrimaryKey)。主属性、非主属性、全码。定义11.5：关系模式R中属性或属性组X并非R的码，但X是另一个关系模式的码，则称X是R的外码。F11.2.2码码是关系模式中的一个重要概念。候选码能够唯一地标识关系的元组；是关系模式中一组最重要的属性。主码又和外码一起提供了一个表示关系间联系的手段。11.2.2码例：

R(Tname,Addr,C#,Cname)F={Tname→Addr,C#→Cname,C#→Tname}C#→{Tname,Addr,C#,Cname}所以C＃是候选码，若C#→Tname不成立，则候选码为{Tname,C#}11.3范式（NormalForm)范式：满足特定要求的模式不同级别的范式要求各不相同范式可以作为衡量一个关系模式好坏的标准若关系模式R满足范式xNF，记RxNF规范化：将低一级范式的关系模式通过模式分解转换为高一级范式的关系模式集合的过程5NF4NF

BCNF

3NF

2NF

1NF1971～1972，E.F.Codd1974，BoyceandCodd1976~1979，Fagin11.3.1第一范式(1NF)定义11.6

如果一个关系模式R的所有属性都是不可分的基本数据项，（即对于关系模式R的任一实例，其元组的每一个属性值都只含有一个值），则

R1NF。1NF是关系的基本要求,否则不能称其为关系数据库。第一范式(1NF)-例满足第一范式的关系模式并不一定是一个好的关系模式。如：SLC(Sno,Sdept,Sloc,Cno,Grade)码为(Sno,Cno)第一范式(1NF)-例函数依赖包括：

(Sno,Cno)→Grade

Sno→Sdept(Sno,Cno)→Sdept

Sno→Sloc(Sno,Cno)→Sloc

Sdept→Sloc(因为每个系只住在一个地方）fpp

SnoCnoGradeSdeptSlocSLC关系模式上的一组函数依赖第一范式(1NF)-例对前例分析：非主属性Sdept

和Sloc

部分函数依赖于码（Sno,Cno)。SLC关系存在数据冗余、更新异常、插入异常、删除异常4个问题。因此SLC不是一个好的关系模式。应消除部分函数依赖，可用投影分解法分解为两个关系模式:第一范式(1NF)-例SC(Sno,Cno,Grade)SL(Sno,Sdept,Sloc)依赖关系为:

(Sno,Cno)→Grade

Sno→Sdept

Sno→Sloc显然,分解后非主属性都完全函数依赖于码了，使以上4个问题得到一定解决，SC，SL属于2NF。11.3.2第二范式(2NF)定义11.7：若关系模式R1NF，且每一非主属性完全函数依赖于R的码，则R2NF。2NF含义：R(A,B,C,D,E),{A,B}为主码，则有A,B→C，A,B→D，A,B→E但C、D、E都不局部函数依赖于A,B即A→C、B→C、A→D、B→D、A→E、B→E中任何一个均不成立

2NF例子供应关系R(S＃,P＃,city,status,Price,QTY)F={S#→city,S#→status,

P#→Price,

city→status,{S#,P#}→QTY}所以主码为{S＃,P＃}但city和Price都局部函数依赖于主码所以R2NF不满足2NF带来的问题R(S＃,P＃,city,status,Price,QTY)插入异常：没有供应零件的供应商无法插入删除异常：删除供应商的供货信息同时删除了供应商的其它信息更新异常：供应商的city修改时必须修改多个元组数据冗余：同一供应商的city被重复存储模式分解以满足2NFR(S＃,P＃,City,Status,Price,QTY)S＃P＃PriceCityQTYStatusR1(S#,City,Status)R2(P#,Price)R3(S#,P#,QTY)分解是否正确？无损联接和保持函数依赖2NF（SL）存在传递函数依赖前面2NF关系模式SL(Sno,Sdept,Sloc)中有下列函数依赖：

Sno→Sdept

Sdept→Sloc

Sno→SlocSloc传递函数依赖于Sno,即SL中存在非主属性对码的传递函数依赖。SL关系仍然不同测定存在上面4个问题，所以SL还不是一个好关系模式。SC、SL上的函数依赖SnoCnoGradeSLSnoSdeptSlocSC11.3.3第三范式(3NF)定义11.8：关系模式R(U,F)中，当且仅当R属于1NF，且R的每一个非主属性都不传递依赖于主码时，R3NF。传递依赖：若X→Y，Y→A，并且Y→X，A不是Y的子集，则称A传递依赖于X。第三范式(3NF)为了消除Sloc传递函数依赖于Sno问题,可将SL进一步分解为：

SD(Sno,Sdept)

DL(Sdept,Sloc)函数依赖为：

Sno→Sdept

Sdept→Sloc显然,分解后没有非主属性对码的部分和传递依赖了，使以上4个问题得到基本解决，SC，SL属于3NF。第三范式(3NF)将一个2NF关系分解为多个3NF的关系后,并不能完全消除关系模式中的各种异常情况和数据冗余。即满足3NF的关系模式不一定是一个好的关系模式。

如，P134STJ关系属3NF，但不是理想的关系模式。不满足3NF带来的问题R1(S#,City,Status)插入异常：不能插入一个具有status但没有供应商的city，例如Rome的status为50，但除非有一个供应商住在Rome,否则无法插入删除异常：删除供应商时会同时删除与该城市相关的status信息更新异常：一个城市中会有多个供应商，因此status更新时要更新多个元组数据冗余：同一城市的status冗余存储分解2NF到3NFR1(S#,City,Status)去掉传递依赖S＃CityStatusR4(City,Status)R5(S#,City)回顾1NF：任一关系的每个属性值不可分2NF：属于1NF，且非主属性完全函数依赖于码3NF：属于1NF，且不存在非主属性到码的传递依赖11.3.4BC范式(BCNF)BCNF(BoyceCoddNormalForm)是由Boyce和Codd提出的，比3NF更进了一步。通常认为BCNF是修正的第三范式。2NF和3NF的定义都假设了R只有一个候选码，但一般情况下R可能有多个候选码，并且不同的候选码之间还可能相互重叠。3NF不能处理R的一般情况（多个候选码）。BCNF扩充了3NF，可以处理R有多个候选码的情形。R只有一个候选码时BCNF等价3NF。（1）多候选码的例子假设供应商的名字是唯一的供应关系R(S#,SNAME,P#,QTY)存在两个候选码{S#,P#}和{SNAME,P#}R属于3NF，WHY？S#SNAMEP#QTYs1Intelp1300s1Intelp2200s1IntelP3400s2Acerp1200{SNAME,P#}→QTY,{S#,P#}→QTY,S#→SNAME,SNAME→S#（2）存在的问题数据冗余：s1的名字Intel重复存储更新异常：修改s1的名字时必须修改多个元组删除异常：若s2现在不提供任何零件，则须删除s2的元组，但同时删除了s2的名字插入异常：没有提供零件的供应商无法插入S#SNAMEP#QTYs1Intelp1300s1Intelp2200s1IntelP3400s2Acerp1200（3）解决方法（3NF->BCNF）SNameP＃S#QTYS＃P＃SnameQTYR1(S#,SNAME)R2(S#,P#,QTY)R1(SName,S#)R2(SName,P#,QTY)BC范式定义定义11.9：设关系模式R1NF，若R中的任一函数依赖X→Y，若YX时，X必含有候选码，则RBCNF。换句话说，在关系模式R中，如果每一个决定属性集都包含候选码，则RBCNF。BC范式（BCNF）（即：如果关系模式R的所有不平凡的、完全的函数依赖的决定因素（左边的属性集）都是候选码，则RBCNF）R的非主属性完全函数依赖于候选码所有的主属性对每一个不包含它的码，也是完全函数依赖没有任何属性完全函数依赖于非主属性集（5）BCNF例子1R(S#,SNAME,STATUS,CITY)设Sname唯一

BCNF模式的函数依赖图中，箭头都是从候选码中引出，所有不平凡FD的左边都是候选码S＃SnameStatuscityS#→Sname,S#→Status，S#

→city,

Sname→S#，Sname

→city,

Sname→Status,（6）BCNF例子2R(S,J,T)---学号，课程号，教师名每一教师只教一门课。每门课由若干教师教，某一学生选定某门课，就确定了一个固定的教师。某个学生选修某个教师的课就确定了所选课的名称：

(S，J)→T，(S，T)→J，T→JR属于3NFR不属于BCNF数据依赖SJTSTJSTJBCNF

解决方法：将STJ分解为二个关系模式：

SJ(S，J)∈BCNF，TJ(T，J)∈BCNF

没有任何属性对码的部分函数依赖和传递函数依赖SJSTTJTJ11.4多值依赖与第四范式（4NF）例:学校中某一门课程由多个教师讲授，他们使用相同的一套参考书。

关系模式Teaching(C,T,B)

课程C、教师T和参考书B………课程C教员T参考书B

物理

数学

计算数学李勇王军

李勇张平

张平周峰

普通物理学光学原理物理习题集

数学分析微分方程高等代数

数学分析

表5.1普通物理学光学原理物理习题集普通物理学光学原理物理习题集数学分析微分方程高等代数数学分析微分方程高等代数…李勇李勇李勇王军王军王军李勇李勇李勇张平张平张平

…物理物理物理物理物理物理数学数学数学数学数学数学

…参考书B教员T课程C用二维表表示Teaching

多值依赖与第四范式（续）Teaching∈BCNF:Teach具有唯一候选码(C，T，B)，即全码Teaching模式中存在的问题：

(1)数据冗余度大：

有多少名任课教师，参考书就要存储多少次

多值依赖与第四范式（续）

(2)插入操作复杂：

当某一课程增加一名任课教师时，该课程有多少本参照书，就必须插入多少个元组。例如：物理课增加一名教师刘关，需要插入两个元组：

（物理，刘关，普通物理学）

（物理，刘关，光学原理）多值依赖与第四范式（续）(3)删除操作复杂：

某一门课要去掉一本参考书，该课程有多少名教师，就必须删除多少个元组(4)修改操作复杂：某一门课要修改一本参考书，该课程有多少名教师，就必须修改多少个元组产生原因 存在多值依赖多值依赖定义11.10设R(U)是一个属性集U上的一个关系模式，X、Y和Z是U的子集，并且Z＝U－X－Y，多值依赖

X→→Y成立当且仅当对R的任一关系r，r在（X，Z）上的每个值对应一组Y的值，这组值仅仅决定于X值而与Z值无关 例Teaching（C,T,B）

对于C的每一个值，T有一组值与之对应，而不论B取何值表示：当且仅当对于R的任一实例r，若r上的两个元组t1和t2，满足t1[A]=t2[A]，则一定存在另外两个元组t3和t4，并且满足：t3[A]=t4[A]=t1[A]=t2[A]t3[B]=t1[B]并且t4[B]=t2[B]t3[C]=t2[C]并且t4[C]=t1[C]平凡多值依赖和非平凡的多值依赖

若X→→Y，而Z＝φ，则称

X→→Y为平凡的多值依赖 否则称X→→Y为非平凡的多值依赖MVD的性质多值依赖具有对称性。即若X→→Y，则

X→→Z，其中：Z=U-X-Y函数依赖可以看作是多值依赖的特殊情况。即若X→Y，则X→→Y。多值依赖的传递性。即若X→→Y,Y→→Z，则

X→→Z－Y。若X→→Y，X→→Z，则X→→YZ。MVD和FD的区别MVD的有效性与属性集的范围有关，若X→→Y在U上成立，则在W(XYWU)上一定成立，反之则不然。FD则不然，若X→Y在W上成立，则在U上也成立。若函数依赖X→Y在R(U)上成立，则对于任何Y’Y均有X→Y’成立。多值依赖X→→Y在R(U)上成立，却不能断言对于任何Y’Y均有X→→Y’成立。11.4.24NF定义11.10关系模式R(U,F)1NF,如果对于R的每个非平凡的多值依赖X→→Y(YX)，X都含有码，则R4NF。BCNF要求所有FD的左边都包含候选码。4NF要求所有不平凡MVD的左边都包含候选码，即要求不平凡MVD都是FD。4NF的模式必然属于BCNF。（1）例子R(Course,Teacher,Text)F={course→→Teacher,Course→→Text}候选码是{course,Teacher,text}每个MVD的左边不包含候选码因此不属于4NF分解后R1(Course,Teacher),R2(Course,Text)Course→→Teacher,Course→→Text都是平凡MVDR1和R2中都不存在不平凡的MVD，属于4NF（2）FD和MVD如果只考虑FD，BCNF是最高级别的范式若考虑多值依赖，则4NF是最高范式FD是MVD的特殊情形11.5关系模式的规范化一个关系只要其分量是不可分的数据项，就可称作规范化的关系，也称1NF。规范化程度可以有多个不同的级别规范化程度过低的关系不一定能够很好地描述现实世界，可能会存在插入异常、删除异常、修改复杂、数据冗余等问题一个低一级范式的关系模式，通过模式分解可以转换为若干个高一级范式的关系模式集合，这种过程就叫关系模式的规范化规范化的基本思想消除不合适的数据依赖的各关系模式达到某种程度的“分离”采用“一事一地”的模式设计原则让一个关系描述一个概念、一个实体或者实体间的一种联系。若多于一个概念就把它“分离”出去所谓规范化实质上是概念的单一化对1NF模式投影，消除非主属性对码的局部函数依赖，产生2NF。对2NF模式投影，消除非主属性对码的传递函数依赖，产生3NF。对3NF模式投影，消除左边不是候选码的函数依赖（或者消除主属性对码的局部函数依赖和传递函数依赖），产生BCNF。对BCNF模式投影，消除任何不是函数依赖的不平凡多值依赖，产生4NF。规范化（续）关系模式规范化的基本步骤

1NF ↓消除非主属性对码的部分函数依赖消除决定属性2NF集非码的非平↓消除非主属性对码的传递函数依赖凡函数依赖3NF ↓消除主属性对码的部分和传递函数依 赖

BCNF ↓消除非平凡且非函数依赖的多值依赖 4NF11.6数据依赖的公理系统逻辑蕴含 定义11.12对于满足一组函数依赖F的关系模式R<U，F>，其任何一个关系r，若函数依赖X→Y都成立,则称

F逻辑蕴含X→Y1.Armstrong公理系统一套推理规则，是模式分解算法的理论基础用途求给定关系模式的码从一组函数依赖求得蕴含的函数依赖Armstrong公理系统关系模式R<U，F>来说有以下的推理规则：Al.自反律（Reflexivity）：

若Y

X

U，则X→Y为F所蕴含。A2.增广律（Augmentation）：若X→Y为F所蕴含，且Z

U，则XZ→YZ为F所蕴含。A3.传递律（Transitivity）：若X→Y及Y→Z为F所蕴含，则X→Z为F所蕴含。 注意：由自反律所得到的函数依赖均是平凡的函数依赖，自反律的使用并不依赖于FArmstrong推理规则是正确的（1）（l）自反律:若Y

X

U，则