第四章关系型数据库规范设计

第四章

关系型数据库规范设计本章主要内容4.1关系模式的设计问题4.2函数依赖4.3关系模式的分解特性4.4关系模式的范式SQL数据库的体系结构SQL用户BaseTableB1ViewV1ViewV2BaseTableB2BaseTableB3BaseTableB4StoredFileS1StoredFileS1StoredFileS1StoredFileS1外模式模式内模式SQL语言支持的关系数据库的三级模式结构S#、SNAME、SDEPT、

C#、CNAME、TIME、

S#、C#、GRADES(S#、SNAME、SDEPT)C（C#、CNAME、TIME）SC（S#、C#、GRADE）……S#SNAMESDEPTC#CNAMETIMEGRADECNAME_SNAME_GRADE(C#,S#,GRADE）4.1关系模式的设计问题对于一个现实问题，它有一个属性集U,其中每个属性Ai对应一个值域DOM（Ai），它由属性集U和U上成立的数据完整性约束集组成。关系r是关系模式R(U)的当前值，是一个元组的集合。这里的关系模式和关系一般称为泛关系模式和泛关系。R（S#、SNAME、SDEPT、C#、CNAME、TIME、S#、C#、GRADE）但是对于许多现实问题，往往R(U)不是恰当的形式，必须用一个关系模式的集合p＝｛R1,R2,……Rk}来代替R(U),这里的p称为数据库模式。对数据库模式的每一个关系模式赋予一个当前值，就得到一个数据库实例（数据库）。S(S#、SNAME、SDEPT)

C（C#、CNAME、TIME）

SC（S#、C#、GRADE）什么是好的数据库设计体现客观世界的信息无过度的冗余无插入异常无更新复杂无删除异常4.1关系模式的设计问题4.1关系模式的设计问题TNAMEADDRESSC#CNAMEt1a1c1n1t1a1c2n2t1a1c3n3t2a2c4n4t2a2c5n2t3a3c6n4关系模式R(TNAME,ADDRESS,C#,CNAME)4.1关系模式的设计问题对数据库操作时,会出现以下问题1、数据冗余：(数据重复存储:浪费存储空间,数据库维护困难）如一名教师教多门课程，他的地址要重复多次2、更新异常：如果一个教师教了三门课程，则如果他的地址变了，三个元组的地址信息都要变。若有一个没变，就会造成地址不唯一，产生错误信息。3、插入异常：主键为空的记录不能存在于数据库,导致不能进行插入操作如教师没有分配教学任务，就不能插入数据库。4.删除异常：删除操作后,会引起一些信息的丢失。如一个原有教学任务的教师教师现在没有教学任务，要把这个教师的所有元组都删去。这样就把这个教师的姓名和地址也从数据库中删去了，不合理。TNAMEADDRESSC#CNAMEt1a1c1n1t1a1c2n2t1a1c3n3t2a2c4n4t2a2c5n2t3a3c6n44.1关系模式的设计问题解决之道：分解!分解!!再分解!!!TNAMEADDRESSt1a1t2a2t3a3TNAMEC#CNAMEt1c1n1t1c2n2t1c3n3t2c4n4t2c5n2t3c6n4分解为两个模式：R1(TNAME,ADDRESS)R2(TNAME,C#,CNAME)考虑为学生的选课信息而设计一个关系模式。过度冗余——数据重复更新异常——更新代价大、可能导致数据不一致删除异常——部分信息的删除可能导致信息的丢失插入异常——必须有完整信息怎么分解最佳？4.1关系模式的设计问题4.2函数依赖(FD,FunctionDependence)y=f(x)y=3x4.2.1函数依赖定义设有关系模式R(A1，A2，...An)或简记为R(U)，X，Y是U的子集，r是R的任一具体关系，

如果对r的任意两个元组t1,t2,由t1[X]=t2[X]导致t1[Y]=t2[Y]，则称X函数决定Y，或Y函数依赖于X，

记为X→Y。X→Y为模式R的一个函数依赖。

如S#Sname，（S#，C#）Grade

该定义理解如下：有一张设计好的二维表，X，Y是表的某些列(可以是一列，也可以是多列)，若在表中的第t1行，和第t2行上的X值相等，

那么必有t1行和t2行上的Y值也相等，这就是说Y函数依赖于X。

比如，有如下二维表

在表中，凡成绩相同的，对应的“成绩等级”也必是相同的，因此，“成绩等级”函数依赖于"成绩"。

但是反过来则不成立。

Notice:

(1)在这张表中，任何一行的关系均应符合函数依赖的条件，如果有一行不符合函数依赖的条件，则函数依赖对于这个关系就不成立。

(2)函数依赖是否成立是不可证明的，只能通过属性的含义来判断.表例学号姓名成绩成绩等级00001李里77C00002丁力91A00003李小红85B00004马琳85B00005王佳怡66D00006胡林70C.....................举例:

职工号(A) 基本工资(B) 奖金(C) 051 390 50052420 5005339080ABACBACA 4.2.2函数依赖－几点说明1.函数依赖是语义范畴的概念.它反映了一种语义完整性约束,只能根据语义来确定一个函数依赖.例如，“姓名”－“年龄”这个函数依赖只有在没有同名人的条件下成立，否则，此函数依赖不成立。2.函数依赖是指关系R模式的所有关系元组均应满足的约束条件,而不是关系模式中的某个或某些元组满足的约束条件。4.2.2函数依赖－几点说明3.函数依赖与属性间的联系类型有关(1)若属性X和Y之间有“一对一”的联系,

则XY,YX,XY.（如不存在同名的学号和姓名）(2)若属性X和Y之间有“多对一”的联系,

则XY,但YX.(3)若属性X和Y之间有“多对多”的联系,

则X与Y之间不存在任何函数依赖.当确定函数依赖关系时,可从属性间的联系入手函数依赖(FD,FunctionDependence)

y=f(x)问：X、Y是谁函数依赖谁？是X函数依赖Y？还是Y函数依赖X？答：Y函数依赖X（X函数决定Y）X=1y=3;X=2y=6;y=3x=1;y=6x=2;y=x2X=1y=1;X=-2y=4;y=1x=1orx=-1;y2=x2y=1x=1orx=-1;x=1y=1ory=-1;y=3x问：X、Y是谁函数依赖谁？是X函数依赖Y？还是Y函数依赖X？答：没有任何函数依赖关系4.2.2函数依赖－几点说明4.如果X→Y,并且Y不是X的子集,则称X→Y是非平凡的函数依赖;如果Y是X的子集,则称X→Y是平凡的函数依赖;

我们讨论的是非平凡的函数依赖.例如：（S#，SN）

SN是平凡的函数依赖5.函数依赖的存在,决定了自然连接的特性设关系模式R(X,Y,Z),X,Y,Z为不相交的属性集合,若X→Y,X→Z,

则有R(X,Y,Z)=R（X,Y)R(X,Z)

即用它们的自然连接可复员原关系模式举例：关系模式S(S#,SN,C#,G,CN,TN,TA)主键：（S#，C#）函数依赖：S#SN（每个学生只能有一个姓名）C#CN(每个课程号只能对应一门课程)TNTA(每个教师只能有一个年龄）（S#，C#）

G(每个学生学习一门课程只能有一个成绩）

4.2.2函数依赖ABC142356346738910说明关系是否满足下列函数依赖：ABACABCCAACB不满足AB，因为（3,5,6)和（3,4,6），t1(A)=t2(A)=3,但是t1(B)=5而t2(A)=4ACABCCA4.2.3键定义超键：设X为关系R的属性或属性组，U为R的元组若XU，则称X为R的超键。候选键：设X为R的超键，若X中不含多余属性，则称X为R的候选键。主键：若关系R有多个候选键，则可以从中选定一个作为R的主键。主属性：包含在任何一个候选键中的属性，称作主属性，不包含在任何键中的属性称为非主属性。全键：关系模式的键由整个属性组构成。 如（S#，C#，T#）4.2.3键候选键的两个性质:1、标识的唯一性:对于R(U)中的每一元组,X的值确定后,该元组就相应确定了.即：X函数决定该关系的所有其他属性(X→R)

XA1A2…An S#SD,SS,SBirthday2、无冗余性:X是属性组的情况下,X的任何真子集都不能唯一标识该元组

即：不存在X的真子集Y,使得YA1A2…An

(S#,C#)Grade4.2.3键举例：设关系模式R(XYZ)，已知FD是XY,YZ,那么可以推出XXYZ也在F+中，但X的真子集（此处是空值）不可能函数决定XYZ,所以X是模式R的键。职工关系模式ZG(工号，姓名，年龄，性别，工资）工号(工号，姓名，年龄，性别，工资），工号没有真子集，所以工号是键。(工号，姓名）(工号，姓名，年龄，性别，工资）

，但不是键。4.3关系模式的分解特性我们可以通过把一个关系模式的分解成多个关系模式，以解决它的插入、删除和更新操作所带来的一些问题。为了在分解要保证原来模式所满足的特性，要求分解处理具有无损联接和保持函数依赖。4.3.1模式分解中存在的问题设有关系R=A1A2…An，R1,R2…Rn是R的子集，R=R1UR2U…Rk,设有关系模式R1,R2…Rk的集合用p表示，p＝｛R1,R2…Rk｝。用p代替R的过程称为关系模式的分解。P称为R的一个分解，也称为数据库模式。R称为泛关系模式，R对应的当前值称为泛关系。数据库对应的当前值σ称为数据库实例，它是由数据库模式中的每一个关系模式的当前值组成，σ＝｛r1,r2…rk}来表示。4.3.1模式分解中存在的问题实际上，关系模式的分解，不仅仅是属性集合的分解，它是对关系模式上的函数依赖集，以及关系模式的当前值的分解的具体表现。4.3.1模式分解中存在的问题R(A,B,C)ABC112221AB1122BC1221ABC112221∏AB(R)∏BC(R)∏AB(R)∏BC(R)R(A,B,C)ABC111212AB1121BC1112ABC111112211212∏AB(R)∏BC(R)∏AB(R)∏BC(R)有损分解无损分解4.3.1模式分解中存在的问题关系模式的分解有几个衡量标准：分解具有无损连接分解保持函数依赖分解既要保持依赖，又要具有无损联接达到更高级范式4.3.2无损联接无损连接分解定义

关系模式R，分解成关系模式p={R1,R2…Rk},F是R上的一个函数依赖集。如果对R中满足F的每一个关系r都有：r=∏R1(r)∏R2(r)∏Rk(r)则称此分解p是相对于F是“无损连接分解”。即r为它在Ri上的投影的自然联接。∏Ri(r)表示关系r在模式Ri的属性上的投影。4.3.2无损联接的测试将R（A,B,C)分解为两个模式R1(A,B)和R2(B,C)ABCa1b1c1a2b1c2a7b3c3a8b4c4a9b5c5ABa1b1a2b1a7b3a8b4a9b5BCb1c1b1c2b3c3b4c4b5c5R(A,B,C)关系r1关系r2关系4.3.2无损联接的测试ABCa1b1c1a2b1c2a7b3c3a8b4c4a9b5c5R(A,B,C)关系ABCa1b1c1a1b1c2a2b1c1a2b1c2a7b3c3a8b4c4a9b5c5R1（A,B)R2(B,C)所以把R（A,B,C)分解为两个模式R1(A,B)和R2(B,C)不是具有无损联接性的分解。4.3.2无损联接的测试如果一个模式分解不是无损联接分解，那么分解不能通过自然联接运算恢复，因此要求分解时利用属性间的函数依赖的性质，才能保证此分解具有无损联接性。

问题提出：在关系模式的规范化处理过程中，不仅要知道一个给定的函数依赖集合，还要知道由给定的函数依赖集合所蕴涵（或推导出）的所有函数依赖的集合。为此，需要一个有效而完备的公理系统，Armstrong公理系统即是这样的一个系统。Armstrong公理系统

蕴含定义：设F是R上的函数依赖集合，X→Y是R的一个函数依赖。如果R的一个关系实例满足F，则必然满足X→Y，则称F逻辑蕴含(Imply)X→Y。

Armstrong公理：为从已知的函数依赖推导出其他的函数依赖，Armstrong提出了一套推理规则，称为Armstrong公理(Armstrong’sAxioms)。

闭包定义：函数依赖集合F所逻辑蕴含的函数依赖的全体，称为F的闭包(Closure)，记为F+。

(2)增广律(Augmentation)

：若X→Y，ZU，则XZ→YZ。

(1)自反律(Reflexivity)

：若YXU，则X→Y。

(3)传递律(Transitivity)

：若X→Y和Y→Z，则X→Z。引理1：Armstrong公理是正确的，即：如F成立，则由F根据Armstrong公理所推导的函数依赖总是成立的。引理2：如下三条推理规则是正确的：公理包含如下三条推理规则：

(2)

伪传递规则(PseudoTransitivity)：如果X→Y，YW→Z，则XW→Z。

(1)

合并规则(Union)：如果X→Y，X→Z，则X→YZ。

(3)

分解规则(Decomposition)：如果X→Y，ZY，则X→Z。或：如X→YZ，则X→Y，X→Z。F的闭包F+

F={XY,YZ},F+计算是NP完全问题，XA1A2...An

F+={Xφ,Yφ,Zφ,XYφ,XZφ,YZφ,XYZφ,XX,YY,ZZ,XYX,XZX,YZY,XYZX,XY,YZ,XYY,XZY,YZZ,XYZY,XZ,YYZ,XYZ,XZZ,YZYZ,XYZZ,XXY, XYXY,XZXY,XYZXY,XXZ,XYYZ,XZXZ,XYZYZXYZ,XYXZ,XZXY,XYZXZ,XZYZ,XYXYZ,XZXYZ,XYZXYZ}S(学号,系号,宿舍楼号)F={学号→系号,系号→宿舍楼号}显然，S不属于3NF。分解0：S01(学号),S02(系号,宿舍楼号)S(学号,系号,宿舍楼号)F={学号→系号,系号→宿舍楼号}显然，S不属于3NF。显然分解0是不可行的。分解0： S01(学号),S02(系号,宿舍楼号)

分解1：S11(学号,宿舍楼号),S12(系号,宿舍楼号)分解2：S21(学号,系号),S22(学号,宿舍楼号)分解3：S31(学号,系号),S32(系号,宿舍楼号)显然，都属于3NF（事实上，都属于BCNF、4NF）。学号系号宿舍楼号S1D1AS2D2BS3D2BS4D3A学号宿舍楼号S1AS2BS3BS4A系号宿舍楼号D1AD2BD3A投影操作（分解1:S11,S12）S学号宿舍楼号系号宿舍楼号S1AD1AS1AD2BS1AD3AS2BD1AS2BD2BS2BD3AS3BD1AS3BD2BS3BD3AS4AD1AS4AD2BS4AD3A学号系号宿舍楼号S1D1AS1D3AS2D2BS3D2BS4D1AS4D3A学号系号宿舍楼号S1D1AS2D2BS3D2BS4D3ASS’S≠S’结论：分解1不是好的分解3个或更多的关系模式的情况下要判别分解是否具有无损连接性要比较复杂的算法。2个则很容易。定理1 U1∩U2→U1-U2∈F+ 或 U1∩U2→U2-U1∈F+定理2如果FDX→Y在模式R上成立，且X∩Y=Ø,那么R分解成ρ

={R-Y,XY}是无损分解，例如：S(学号,系号,宿舍楼号)F={学号→系号,系号→宿舍楼号}X→Y，ρ

={R-Y,XY}S1(学号,宿舍楼号)S2(学号,系号)分解1：S11(学号,宿舍楼号),S12(系号,宿舍楼号)U1∩U2=宿舍楼号，U1-U2=学号（不成立）或U1∩U2=宿舍楼号，U1-U2=系号（不成立）（F={学号→系号,系号→宿舍楼号}，F+）分解2：S21(学号,系号),S22(学号,宿舍楼号)U1∩U2=学号，U1-U2=系号。 显然，U1∩U2→U1-U2。结论：分解2具有无损连接性. 但是分解2也不是好的方案。假设学生S3从D2系转到D3系，要修改两个关系，否则数据库中的信息会不一致。这是由于分解得到的两个关系模式不是互相独立造成的。系号→宿舍楼号没投影在同一个关系中。于是我们有要求模式分解保持函数依赖这条等价标准。结论：分解2虽然是无损分解，但丢失了函数依赖系号→宿舍楼号学号宿舍楼号S1AS2BS3BS4A学号系号S1D1S2D2S3D2S4D3分解3：S31(学号,系号),S32(系号,宿舍楼号)F={学号→系号,系号→宿舍楼号}1：保持函数依赖的。2：根据 U1∩U2→U1-U2∈F+ 或 U1∩U2→U2-U1∈F+U1∩U2=系号，U2-U1=宿舍楼号这一分解也具有无损连接性结论：分解3是一个好的分解4.3.3保持函数依赖的分解保持关系模式分解等价的另一个重要条件是关系模式的函数依赖集在分解后仍在数据库模式中保持不变。即关系模式R到={R1,R2…Rk}的分解，应使函数依赖集F,被F在这些Ri上的投影蕴涵。F在AB,AC,DB,CD上的投影分别为｛A→B｝,｛A→C｝,ø,｛D→C｝丢失了B→C,A→D,所以此分解不保持FD设关系模式R(A,B,C,D)F是R上成立的FD集，F={A→B,B→C,A→D,D→C},

={AB,AC,BD,CD}是R的一个分解。4.3.3保持函数依赖的分解4.3.4模式分解与模式等价问题模式分解的两个特性实际上涉及两个数据库模式的等价问题，这种等价数据等价和依赖等价两个方面。数据等价是指两个数据库实例应表示同样的信息内容，用无损分解衡量。依赖等价是指两个数据库模式应有相同的依赖集闭包。在依赖集闭包相等的情况下，数据的语义是不会出差错的。违反数据等价或语义等价的分解很难说是一个好的分解。 但同时达到也不是简单的事情。4.3.4关于模式分解的几个事实分解具有无损连接性和分解保持函数依赖是两个相互独立的标准。1.具有无损连接性性的分解不一定保持函数依赖例如分解2.2保持函数依赖的分解不一定具有无损连接性如SC(SNO,DNO,CNO,CREDIT)F={SNO→DNO,CNO→CREDIT}分解为两个关系模式：SC1(SNO,DNO)SC2(CNO,CREDIT)这个分解保持函数依赖的分解不具有无损连接性结论：关系模式的一个分解可能具有无损连接性，可能是保持函数依赖，有可能是即具有无损连接性也保持函数依赖4.3.4关于模式分解的几个事实若要求分解具有无损连接性，那么模式分解可以达到BCNF;若要求分解保持函数依赖,那么模式分解可以达到3NF,但不一定达到BCNF;若要求分解即具有无损连接性，又保持函数依赖,那么模式分解可以达到3NF,但不一定达到BCNF;4.4关系模式的范式

（NF，NormalForm）

第一范式（1NF）

第二范式（2NF）第三范式（3NF）

BCNF第四范式（4NF）第五范式（5NF）考核要求：了解考核要求：领会为什么要关系规范化？

关系模式的存储异常:数据冗余、更新异常、插入异常和删除异常

关系模式的设计：把泛关系模式分解成规范化的数据库模式。二维表的例子学号姓名专业课程号课程名教师系办公室电话教室成绩01张计算机CS01数据库陈计算机13025894024940989张计算机CS02数学赵数学30235890323882376张计算机CS03英语陈外语5034589403050207102王网络CS01数据库陈计算机13025894024940973王网络CS02数学赵数学30235890323882388王网络CS03英语陈外语50345894030502003李通讯CS01数据库陈计算机130258940249409李通讯CS04C++周计算机303358903338832李通讯CS03英语陈外语503458940305020###########需要的知识 理解“非主属性”、“完全函数依赖”、“候选键”、“部分函数依赖”、“传递函数依赖”这五个名词的含义。

（1）候选键：可以唯一决定关系模式R中某元组值且不含有多余属性的属性集。

（2）非主属性：即非键属性，指关系模式R中不包含在任何建中的属性。

（3）完全函数依赖：设R为任意给定关系,x、y其属性集，若x→y，且对x中的任何真子集，x’都有x’→y。则称y完全函数依赖于x。

（4）部分函数依赖：设R为任意给定关系,x、y其属性集，若x→y，且x中存在一个真子集x’，x’满足x’→y。则称y部分函数依赖于x。

（5）传递函数依赖：设R为任意给定关系,x、y、z其属性集，若x→y，y→x，y→z，则有x→z，则称z传递函数依赖于x。1NF：第一范式1NF：第一范式——即关系模式中的属性的值域中每一个值都是不可再分解的值。

如果某个数据库模式都是第一范式的，则称该数据库模式是属于第一范式的数据库模式。

第二范式

如果关系模式R为第一范式，并且R中每一个非主属性完全函数依赖于R的某个候选键，

则称为第二范式模式。

在分析是否为第2范式时，应首先确定候选键，然后把关系模式中的非主属性与键的依赖关系进行考察，

是否都为完全函数依赖，如是，则此关系模式为2NF。

如果数据库模式中每个关系模式都是2NF的，则此数据库模式属于2NF的数据库模式。

第三范式3NF如果关系模式R是第二范式，且每个非主属性都不传递依赖于R的候选键，则称R为第三范式模式。

传递依赖的含义：在关系模式中，如果Y→X，X→A，且XY(X不决定Y)和AX(A不属于X)，那么Y→A是传递依赖。

Notice: 要求非主属性都不传递依赖于候选键。

把泛关系模式R用一组关系模式的集合ρ={R1,R2,...,Rk}来表示(R1,R2,...,Rk都是R的子集，ρ就是数据库模式)。

以ρ代替R的过程称为关系模式的分解。

实际上，关系模式的分解不仅仅是属性集合的分解，

它是对关系模式上的函数依赖集、以及关系模式的当前值分解的具体表现。

第一范式消去非主属性对键的部分函数依赖BCNF第二范式第三范式消去非主属性对键的传递函数依赖消去主属性对键的传递函数依赖普通二维表消去重复叠代不合理的分解会出现以下情况：失去函数依赖、或出现插入、删除异常等情况。

模式分解的衡量标准有三种：

（1）分解具有无损联接；

（2）分解要保持函数依赖；

（3）分解既要保持依赖，又要具有无损 联接。

学院具体情况：一种课由一位老师任教，每种课在一个教室上，每位老师有一独立的办公室。学生选课表数据资料初步统计如下：问题：这是一个关系表吗？不是学号姓名专业课程号课程名教师系办公室电话教室成绩01张计算机CS01数据库陈计算机13025894024940989张计算机CS02数学赵数学30235890323882376张计算机CS03英语陈外语5034589403050207102王网络CS01数据库陈计算机13025894024940973王网络CS02数学赵数学30235890323882388王网络CS03英语陈外语50345894030502003李通讯CS01数据库陈计算机130258940249409李通讯CS04C++周计算机303358903338832李通讯CS03英语陈外语503458940305020###########第一范式消去非主属性对键的部分函数依赖BCNF第二范式第三范式消去非主属性对键的传递函数依赖消去主属性对键的传递函数依赖普通二维表消去重复叠代学号姓名专业课程号课程名教师系办公室电话教室成绩01张计算机CS01数据库陈计算机1302589402494098901张计算机CS02数学赵数学3023589032388237601张计算机CS03英语陈外语5034589403050207102王网络CS01数据库陈计算机1302589402494097302王网络CS02数学赵数学3023589032388238802王网络CS03英语陈外语50345894030502003李通讯CS01数据库陈计算机13025894024940903李通讯CS04C++周计算机30335890333883203李通讯CS03英语陈外语503458940305020###########去掉重复叠代部分得到如下表：问题：1、这是一个关系表吗？3、还存在问题吗？2、这是第几范式？是是第一范式问题很多：1、插入问题，新同学小赵不能被插入表中，因为他还没有选课，课程号不能为空（实体完整性的要求）。2、删除问题，小李不能被删除，因为周老师将被删除。3、更新问题，修改陈老师相关信息很困难。如何结决这些问题呢？第一范式消去非主属性对键的部分函数依赖BCNF第二范式第三范式消去非主属性对键的传递函数依赖消去主属性对键的传递函数依赖普通二维表消去重复叠代方法：对关系进行分解！

学号课程号成绩姓名专业系办公室教师课程名教室电话学号姓名专业01张计算机02王网络03李通讯课程号课程名教室教师系办公室电话CS01数据库9409陈计算S02数学8823赵数学30235890323CS03英语5020陈计算S04C++3033周计算机88325890333学号课程号成绩01CS018901CS027601CS037102CS017302CS028802CS0303CS0103CS0403CS02问题：1、这是一个关系表吗？是第二范式是2、这是第几范式？3、还存在问题吗？存在，冗余、更新、删除问题如何结决这一问题呢？学号课程号成绩姓名专业系办公室教师课程名教室电话学号课程号学号课程号成绩姓名专业系办公室教师课程名教室电话学号课程号第一范式消去非主属性对键的部分函数依赖BCNF第二范式第三范式消去非主属性对键的传递函数依赖消去主属性对键的传递函数依赖普通二维表消去重复叠代方法：对关系进行进一步的分解！

学号姓名专业01张计算机02王网络03李通讯教师系办公室电话陈计算计算机88325890333赵数学30235890323课程号课程名教师教室CS01数据库陈9409CS02数学赵8823CS03英语陈5020CS04C++周3033学号课程号成绩01CS018901CS027601CS037102CS017302CS028802CS0303CS0103CS0403CS02问题：这一关系还有问题吗？ 这一关系与原关系等价吗？与原关系的信息仍然一致吗？学号姓名专业01张计算机02王网络03李通讯教师系办公室电话陈计算计算机88325890333赵数学30235890323学号课程号成绩01CS018901CS027601CS037102CS017302CS028802CS0303CS0103CS0403CS02学号姓名专业课程号课程名教师系办公室电话教室成绩01张计算机CS01数据库陈计算机1302589402494098903李通讯CS03英语陈外语503458940305020###########我们得到了可行的关系集！课程号课程名教师教室CS01数据库陈9409CS02数学赵8823CS03英语陈5020CS04C++周3033BCNFBCNF范式是第三范式的改进形式，建立在第一范式的基础上。如果关系模式R是第一范式，且所有的函数依赖X→Y(Y不属于X)，决定因素X都包含了R的一个候选键，那么称R是BCNF范式。BCNF从BCNF的定义中可以明显得出如下结论：1、所有非主属性对键是完全依赖。2、所有主属性对不包含它的键是完全函数依赖。3、没有属性完全函数依赖于非键的任何的属性组。BCNFsct(s,c,t)//学生，课程，教师t→c//每位教师只上一门课s,c→t//某学生选定一门课，就对应一位老师s,t→c //每门课有若干位教师sctstcsct∈3NF，sct∈BCNF（S，t），（S，C）为候选键。t→cSCT张数据库陈张数学赵张英语钱王数据库陈王数学郑王英语孙李数据库吴李英语钱###SCT张数据库陈张数学赵张英语钱王数据库陈王数学郑王英语孙李数据库吴李英语钱###从BCNF的定义中可以明显得出如下结论：1、所有非主属性对键是完全依赖。2、所有主属性对不包含它的键是完全函数依赖。课程号课程名教室教师系办公室电话CS01数据库9409陈计算S02数学8823赵数学30235890323CS03英语5020陈计算S04C++3033周计算机883258903333、没有属性完全函数依赖于非键的任何的属性组。stcBCNF非BCNF的不良特性插入异常：如果没有学生选修某位老师的任课，则该老师担任课程的信息就无法插入。删除异常：删除学生选课信息，会删除掉老师的任课信息。更新异常：如果老师所教授的课程有所改动，则所有选修该老师课程的学生元组都要做改动。数据冗余：每位学生都存储了有关老师所教授的课程的信息。症由：

主属性对键的传递(不良）依赖。sct(s,c,t)

BCNF如sctBCNF，因为tC，而t不含有键。改造 将sct分解为（S，t），（t，c）。TC陈数据库赵数学钱英语郑数学孙英语吴数据库##sct(s,c,t)ST张陈张赵张钱王陈王郑王孙李吴李钱##思考

(S#,C#,ORDER)，表示学生选修课程的名次，有函数依赖(S#，C#)ORDER，(C#，ORDER)S#，它属于BCNF吗？

？？？(S#，ORDER)C#

学号课程号单科排名01CS01101CS02201CS0302CS01202CS02302CS0303CS01303CS0403CS0212、所有主属性对不包含它的键是完全函数依赖。SCT张数据库陈张数学赵张英语钱王数据库陈王数学郑王英语孙李数据库吴李英语钱###BCNF最高范式BCNF是基于函数依赖的最高范式但不是数据库模式设计的最高范式6.2规范化6.2.1函数依赖6.2.2码6.2.3范式6.2.42NF6.2.53NF6.2.6BCNF6.2.7多值依赖6.2.84NF6.2.9规范化小结6.2.7多值依赖[例9]学校中某一门课程由多个教师讲授，他们使用相同的一套参考书。每个教员可以讲授多门课程，每种参考书可以供多门课程使用。

………课程C教员T参考书B

物理

数学

计算数学李勇王军

李勇张平

张平周峰

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

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

数学分析...…

多值依赖（续）非规范化关系普通物理学光学原理物理习题集普通物理学光学原理物理习题集数学分析微分方程高等代数数学分析微分方程高等代数…李勇李勇李勇王军王军王军李勇李勇李勇张平张平张平

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

参考书B教员T课程C多值依赖（续）用二维表表示Teaching多值依赖（续）Teaching∈BCNFTeaching具有唯一候选码(C，T，B)，即全码

多值依赖（续）

Teaching模式中存在的问题(1)数据冗余度大(2)插入操作复杂(3)删除操作复杂(4)修改操作复杂存在多值依赖多值依赖（续）定义6.9

设R(U)是一个属性集U上的一个关系模式，X、Y和Z是U的子集，并且Z＝U－X－Y。关系模式R(U)中多值依赖

X→→Y成立，当且仅当对R(U)的任一关系r，给定的一对（x，z）值，有一组Y的值，这组值仅仅决定于x值而与z值无关例Teaching（C,T,B）

(X,Z,Y)给定的一对(x,z)有一组y的值,这组值仅仅决定于x值而与z值无关,X→→Y(C,T,B)给定的一对(c,t)有一组b的值,这组值仅仅决定于c值而与t值无关,C→→B给定一对（c,b）有一组t的值

?C→→T问题：还有那些多值依赖？多值依赖（续）多值依赖的另一个等价的形式化的定义：在R（U）的任一关系r中，如果存在元组t，s使得t[X]=s[X]，那么就必然存在元组w，vr，（w，v可以与s，t相同），使得w[X]=v[X]=t[X]，而w[Y]=t[Y]，w[Z]=s[Z]，v[Y]=s[Y]，v[Z]=t[Z]（即交换s，t元组的Y值所得的两个新元组必在r中），则Y多值依赖于X，记为X→→Y。这里，X，Y是U的子集，Z=U-X-Y。多值依赖（续）平凡多值依赖和非平凡的多值依赖

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

X→→Y为平凡的多值依赖 否则称X→→Y为非平凡的多值依赖多值依赖的性质（1）多值依赖具有对称性 若X→→Y，则X→→Z，其中Z＝U－X－Y（2）多值依赖具有传递性 若X→→Y，Y→→Z，则X→→Z–Y（3）函数依赖是多值依赖的特殊情况。 若X→Y，则X→→Y。（4）若X→→Y，X→→Z，则X→→YZ。（5）若X→→Y，X→→Z，则X→→Y∩Z。（6）若X→→Y，X→→Z，则X→→Y-Z，X→→Z-Y。多值依赖与函数依赖的区别(1)多值依赖的有效性与属性集的范围有关(2)

若函数依赖X→Y在R（U）上成立，则对于任何Y'Y均有X→Y'成立多值依赖X→→Y若在R(U)上成立，不能断言对于任何Y'Y有X→→Y'成立多值依赖（续）［例10］关系模式WSC（W，S，C）W表示仓库，S表示保管员，C表示商品假设每个仓库有若干个保管员，有若干种商品每个保管员保管所在的仓库的所有商品每种商品被所有保管员保管

多值依赖（续）WSCW1S1C1W1S1C2W1S1C3W1S2C1W1S2C2W1S2C3W2S3C4W2S3C5W2S4C4W2S4C5多值依赖（续）W→→S且W→→C用下图表示这种对应

6.2.84NF定义6.10关系模式R<U，F>∈1NF，如果对于R的每个非平凡多值依赖X→→Y（YX），X都含有码，则R∈4NF。如果R∈4NF，则R∈BCNF不允许有非平凡且非函数依赖的多值依赖允许的非平凡多值依赖是函数依赖4NF（续）例：Teaching(C,T,B)∈4NF

存在非平凡的多值依赖C→→T，且C不是码用投影分解法把Teaching分解为如下两个关系模式：

CT(C,T)∈4NF CB(C,B)∈4NFC→→T，C→→B是平凡多值依赖

4NF（续）例：关系模式WSC（W，S，C）∈4NF

存在非平凡的多值依赖W→→C，W→→S用投影分解法把WSC分解为如下两个关系模式：

CT(W,S)∈4NF CB(W,C)∈4NFW→→S，W→→C是平凡多值依赖

6.2规范化6.2.1函数依赖6.2.2码6.2.3范式6.2.42NF6.2.53NF6.2.6BCNF6.2.7多值依赖6.2.84NF6.2.9规范化小结6.2.9规范化小结关系数据库的规范化理论是数据库逻辑设计的工具目的：尽量消除插入、删除异常，修改复杂，数据冗余基本思想：逐步消除数据依赖中不合适的部分实质：概念的单一化规范化小结（续）不能说规范化程度越高的关系模式就越好在设计数据库模式结构时，必须对现实世界的实际情况和用户应用需求作进一步分析，确定一个合适的、能够反映现实世界的模式上面的规范化步骤可以在其中任何一步终止范式之间的关系5NF

4NF

BCNF3NF2NF1NF

第一范式消去非主属性对键的部分函数依赖BCNF第二范式第三范式消去非主属性对键的传递函数依赖消去主属性对键的传递函数依赖

出现问题的可能性逐步减少第四范式消除非平凡且非函数依赖的多值依赖关系范式的优点及缺点优点：随着级别的提高，出现问题（数据冗余、 更新异常、插入异常和删除异常）的可能性减少，甚至杜绝。表现方式直观和灵活。缺点：运行速度（相对）较慢。规范化基本步骤：

2.关系模式的分解及其指标

投影分解法：一个关系模式R<U,F>，其中，U为该关系R的属性集，F为该关系R上的数据依赖，分解为若干个关系模式R1<U1,F1>，R2<U2,F2>…，Rn<Un,Fn>，其中，U=U1∪U2∪…∪Un，且UiUj，Ri为R在Ui上的投影，此即意味着将存储于一张表T中的数据分散到若干张表T1，T2，…，Tn中去，其中，Ti是T在属性集Ui上的投影。关系模式分解的一般要求：关系模式经分解后，应与原来的关系等价。等价是指两者对数据的使用者来说是等价的，即：对分解前后的数据，做同样内容的查询，会产生同样的结果。分解的两个指标：无损分解和函数依赖保持性。分解不能丢失任何信息，否则就没有意义。无损分解：若R与R1，R2，…，Rn自然连接的结果相等，则称关系模式R的这个分解具有无损连接性（Loss-lessJoin）。只有具有无损连接性的分解才能够保证不丢失信息，无损连接性的分解即为无损分解。

保持函数依赖：若F所逻辑蕴含的函数依赖一定也由分解得到的某个关系模式中的函数依赖Fi所逻辑蕴含，则称关系模式R的这个分解是保持函数依赖（PreserveDependency）的。关系模式的分解准则：①

只满足无损分解要求；

②

既满足无损分解要求，又满足保持依赖要求。BCNF、第四范式、第五范式不是考试内容4.4.7模式设计方法的原则关系模式R相对于函数依赖集F分解成数据库模式={R1,R2,…Rk},一般应具有下面四项特性:1、中每个关系模式Ri上应有某种范式性质２、无损联接３、保持函数依赖集４、最小性，指中模式个数应最少和模式中属性总数应最少。第四章主要内容1、函数依赖的定义2、键（候选键）的定义和判定3、最小函数依赖集的求法4、关系模式的分解（无损联接和保持函数依赖）5、关系模式的范式及范式级别判定的依据6、分解成BCNF和3NF的算法7、多值依赖补充材料FINISH实例

建立关于系、学生、班级、社团等信息的一个关系数据库，一个系有若干个专业，每个专业每年只招一个班，每个班有若干个学生，一个系的学生住在同一宿舍区，每个学生可以参加若干个社团，每个社团有若干学生。

描述学生的属性有：学号、姓名、出生年月、系名、班级号、宿舍区。

描述班级的属性有：班级号、专业名、系名、人数、入校年份。

描述系的属性有：系名、系号、系办公地点、人数。

描述社团的属性有：社团名、成立年份、地点、人数、学生参加某社团的年份。请给出关系模式，写出每个关系模式的最小函数依赖集，指出是否存在传递函数依赖，对于函数依赖左部是多属性的情况，讨论函数依赖是完全函数依赖还是部分函数依赖。指出各关系的候选键、外部键?

各关系模式如下：

学生(学号,姓名,出生年月,系名,班级号,宿舍区)

班级(班级号,专业名,系号,人数,入校年份)

系(系名,系号,系办公地点,人数)

社团(社团名,成立年份,地点,人数）

加入社团（社团名，学号，学生参加社团的年份）

学生(学号,姓名,出生年月,系名,班级号,宿舍区)

“学生”关系的最小函数依赖集为：

Fmin={学号→姓名,学号→班级号,学号→出生年月,notice:在关系模式中，如果Y→X，X→A，且X→

Y（X不决定Y），A不属于X,那么称Y→A是传递依赖。系名，系名学号→→宿舍区}以上关系模式中存在传递函数依赖，如:学号→系名，系名→宿舍区候选键是？学号。外部键是？班级号,系名。班级(班级号,专业名,系名,人数,入校年份)

“班级”关系的最小函数依赖集为：

Fmin={(系名,专业名)→班级号,班级号→人数，班级号→入校年份，班级号→系名，班级号→专业名}(假设没有相同的系，不同系中专业名可以相同)

以上关系模式中是否存在传递函数依赖？不存在传递函数依赖。

“(系名,专业名)→班级号”是完全函数依赖。候选键是？(系名,专业名),班级号。外部键是？系名。系(系名,系号,系办公地点,人数)

“系”关系的最小函数依赖集为：Fmi