关系数据模式的规范化理论实用教案

1、 是不是从信息世界(shji)的模型中简单地转化为机器世界(shji)的数据就可以了呢?实体(sht)关系(gun x)表属性数据项4.1 问题的提出 第1页/共60页第一页，共61页。4.1 问题(wnt)的提出 如果要设计一个教学管理数据库，希望从数据库中得到学生学号(sno)、学生姓名(name)、性别(sex)、学生学习的课程号(cno)、课程名(cname)和该门课程的成绩(grade)。 如何设计该关系模式(msh)？ 主码是什么?(学号,姓名(xngmng),性别,课程号,课程名,成绩)(SNO,NAME,SEX,CNO,CNAME,GRADE)第2页/共60页第二页，共61页。

2、4.1 问题(wnt)的提出 问题1：数据(shj)冗余SNONAMESEXCNOCNAME GRADES0102王华男C108C语言84S0102王华男C206数据库92S0108李丽女C206数据库86S0108李丽女C207数学86第3页/共60页第三页，共61页。4.1 问题(wnt)的提出 问题(wnt)2：不一致性SNONAMESEXCNOCNAME GRADES0102王华男C108C语言84S0102张三男C206数据库92S0108李丽女C206数据库86S0108李丽女C207数学86第4页/共60页第四页，共61页。4.1 问题(wnt)的提出 问题3：插入(ch r)异

3、常SNONAMESEX CNOCNAMEGRADES0102王华男C108C语言84S0102王华男C206数据库92S0108李丽女C206数据库86S0108李丽女C207数学86（S0010，李四，男，null，null，null）第5页/共60页第五页，共61页。4.1 问题(wnt)的提出 问题4：删除异常（当某学号只有(zhyu)一条记录，并做删除操作时）SNONAME SEXCNOCNAMEGRADES0102 王华男C108C语言84S0103 张三男C206数据库92S0108 李丽女C206数据库86S0108 李丽女C207数学86第6页/共60页第六页，共61页。4.1

4、 问题(wnt)的提出 解决方案S1（SNO，NAME，SEX）S2（CNO，CNAME）S3（SNO，CNO，GRADE ）第7页/共60页第七页，共61页。4.2 函数(hnsh)依赖 定义1设R(U)是属性集U上的关系模式，X，Y是U的子集。若对于R(U)任意(rny)一个可能的关系r，r中不可能存在两个元组在X上的属性值相等，而在Y上的属性值不等，则称X函数确定Y或Y函数依赖于X，记作X-Y第8页/共60页第八页，共61页。4.2 函数(hnsh)依赖例子(l zi)职工号-姓名S1:SNO - NAME ;SNO- SEXS2:(SNO,CNO) - GRADES3: CNO - C

5、NAME第9页/共60页第九页，共61页。4.2 函数(hnsh)依赖 定义2 设X-Y是一个函数依赖(yli)，若 则称X-Y是一个平凡函数依赖(yli)。YXYX 设X-Y是一个函数依赖，若 则称X-Y是一个非平凡函数依赖。第10页/共60页第十页，共61页。4.2 函数(hnsh)依赖 例子 在S2中有 (SNO,CNO) -SNO (SNO,CNO) -CNO 所以这些都是平凡函数依赖关系 (SNO,CNO) - GRADE 这个(zh ge)是非平凡函数依赖关系第11页/共60页第十一页，共61页。4.2 函数(hnsh)依赖 定义3 设X-Y是一个(y )函数依赖，并且对于任何 则

6、称XY是一个(y )完全函数依赖。即Y函数依赖 于整个X，记,XX XY都不成立（记为XY）fXY第12页/共60页第十二页，共61页。4.2 函数(hnsh)依赖举例：在关系S (SNO,NAME,SEX,CNO,CNAME,GRADE)中： (SNO,CNO) - GRADE但SNO - GRADE；(CNO) - GRADE都不成立所以(suy)(SNO,CNO) - GRADE是完全函数依赖关系。第13页/共60页第十三页，共61页。4.2 函数(hnsh)依赖 定义4设X-Y是一个(y )函数依赖,但不是完全函数依赖,则称X-Y是一个(y )部分函数依赖,或称Y函数依赖于X的某个真子

7、集,记pXY第14页/共60页第十四页，共61页。4.2 函数(hnsh)依赖 在关系S (SNO,NAME,SEX,CNO,CNAME, GRADE)中： (SNO,CNO)-NAME,而对于每个学生都有唯一的SNO值,所以SNO-NAME,而CNO-NAME, 因此，(SNO,CNO)-NAME是部分(b fen)函数依赖(SNO,CNO) NAMEp第15页/共60页第十五页，共61页。4.2 函数(hnsh)依赖 设R(U)是一个关系(gun x)模式, 则称Z传递函数依赖于X,记, ,X Y ZUXYX Y如果（YX）,YZ成立tXZ第16页/共60页第十六页，共61页。4.2 函数

8、(hnsh)依赖 例子： 班级(bnj)(班号,专业名,系名,人数,入学年份)班号-专业名,专业名-系名,班号-人数, 班号-入学(r xu)年份班号-专业名, 专业名-系名t班级号系名第17页/共60页第十七页，共61页。4.2 函数(hnsh)依赖 函数依赖与属性关系 属性之间有3种关系,但并不是(b shi)每一种关系中都存在函数依赖。第18页/共60页第十八页，共61页。函数依赖(yli)与属性关系设R(U)是属性集U上的关系模式,X,Y是U的子集:若X和Y之间是1:1关系,则存在函数(hnsh)依赖 X-Y,Y-X;若X和Y之间是1:n关系,则存在函数(hnsh)依赖 X-Y;若X和

9、Y之间是m:n关系,则X,Y间不存在函数(hnsh)依赖.第19页/共60页第十九页，共61页。函数(hnsh)依赖与属性关系分析(fnx)下列关系中各种函数的依赖关系:学生(学号,姓名,出生年月,系名,班号,宿舍区)第20页/共60页第二十页，共61页。Armstrong公理(gngl)背景为了从一组函数依赖(yli)中求得逻辑蕴涵的函数依赖(yli),例如已知函数依赖(yli)集F,要问是否逻辑蕴涵X-Y,就需要一套推理规则.第21页/共60页第二十一页，共61页。Armstrong公理(gngl) 设A,B,C,D是给定关系模式(msh)R的属性集的任意子集,并把A和B的并集称为AB,则

10、其推理规则可归结为3条:自反律:如果(rgu),BAAB则这是一个平凡函数依赖增广律:如果ABACBC，则传递律:如果ABAC，且BC 则第22页/共60页第二十二页，共61页。Armstrong公理(gngl)推论自合规则(guz):A-A分解(fnji)规则:A-BC,则A-B且 A-C合并规则: A-B且 A-C,则A-BC复合规则: A-B且 C-D成立,则AC-BD第23页/共60页第二十三页，共61页。4.2 函数(hnsh)依赖4.2.6 4.2.6 闭包及其计算闭包及其计算定义定义6 6：设：设F F是关系模式是关系模式R R的一个函数依赖集，的一个函数依赖集，X X，Y Y是

11、是R R的属性子集，如果从的属性子集，如果从F F中的函数依赖中的函数依赖能够推出能够推出XYXY，则称，则称F F逻辑蕴涵逻辑蕴涵XYXY。定义定义7 7：被：被F F逻辑蕴涵的函数依赖的全体逻辑蕴涵的函数依赖的全体(qunt)(qunt)构成的集合，称为构成的集合，称为F F的闭包，记为的闭包，记为F+F+。练习练习1 1：设有关系：设有关系(gun x)(gun x)模式模式R(A,B,C,D,E)R(A,B,C,D,E)，F = AC,BC E,D C, E AF = AC,BC E,D C, E A，试求，试求F F的闭包的闭包F+F+。第24页/共60页第二十四页，共61页。4.2

12、 函数(hnsh)依赖定义定义(dngy)8(dngy)8：设：设F F是属性集是属性集U U上的一组函数依赖，上的一组函数依赖，X UX U，则属性集，则属性集X X关于关于F F的闭的闭包包X+X+定义定义(dngy)(dngy)为为X+=A|AUX+=A|AU且且XAXA在在F+F+中中 ，即，即X=A|XAF+X=A|XAF+。算法4.1 result=X do if F中有某个函数依赖(yli)YZ满足Y result then result=result Z while (result有所改变); 第25页/共60页第二十五页，共61页。XF+算法: 设i=0 令 X(0) = X

13、 计算 A=B| 一切(yqi)W X(i) 且 W B F+ 令 X(i+1) = X(i)A 判断 X(i+1) = X(i) 是否成立，成立，转， 不成立，i = i + 1，转 算法结束，XF+=X(i)4.2 函数(hnsh)依赖第26页/共60页第二十六页，共61页。随堂练习(linx)练习练习(linx)1(linx)1：设有关系模式：设有关系模式R(A,B,C,D,E)R(A,B,C,D,E)，F = AB,C E,D ACF = AB,C E,D AC，试求，试求D D关于关于F F的闭包。的闭包。练习练习2 2：设有关系模式：设有关系模式(msh)R(A,B,C,D,E)(

14、msh)R(A,B,C,D,E)，F = ABC,BD,CE,EC,ACF = ABC,BD,CE,EC,AC，试求，试求BCBC关于关于F F的闭包的闭包(BC)F+(BC)F+。第27页/共60页第二十七页，共61页。4.2 函数(hnsh)依赖 关键码的定义 如果XU在R上成立（即XU在F +中），那么称X是R的一个超键。 如果XU在R上成立，但对X的任一真子集X都有XU不成立（即XU不在F+中，或者X U），那么称X是R上的一个候选键。 快速求解候选键的一个充分条件 对于(duy)给定的关系模式R(A1，An)和函数依赖集F，可将其属性分为以下四类： fL类R类 N类 LR类 第28页

15、/共60页第二十八页，共61页。定理4.4 对于给定(i dn)的关系模式R及其函数依赖集F（1）若X（XR）是L类属性，则X必为R的任一候选键的成员。（2）若X（XR）是L类属性，且X +包含了R的全部属性，则X必为R的惟一候选键。（3）若X（XR）是R类属性，则X不在任何候选键中。（4）若X（XR）是N类属性，则X包含在R的任一候选键中。（5）若X（XR）是R的N类和L类属性组成的属性集，且X +包含了R的全部属性，则X是R的惟一候选键。 第29页/共60页第二十九页，共61页。随堂练习(linx)练习练习1 1：设有关系模式：设有关系模式(msh)R(A,B,C,D,E)(msh)R(A

16、,B,C,D,E)，F = AB,CG,EAF = AB,CG,EA，CE DCE D，求，求R R的所有候选键。的所有候选键。练习练习(linx)2(linx)2：设有关系模式：设有关系模式R(A,B,C,D,E,P)R(A,B,C,D,E,P)，F = AD,ED,DB,BCD,DCAF = AD,ED,DB,BCD,DCA，求，求R R的所有候选键。的所有候选键。练习练习3 3：设有关系模式：设有关系模式R(A,B,C,D,E)R(A,B,C,D,E)，F = ABC,CDE,BD,EAF = ABC,CDE,BD,EA，求，求R R的所有候选键。的所有候选键。第30页/共60页第三十页

17、，共61页。 多属性函数依赖集候选键的求解算法 （1）属性分类（L、R、N和LR） （2）若X +包含了R的全部属性，转（5）；否则，转（3）。 （3）在Y中取一个属性A，求(XA) +，若它包含了R的全部属性，则转（4）；否则，调换一属性反复(fnf)进行这一过程，直到试完所有Y中的属性。 （4）如果已找出所有候选键，则转（5）；否则在Y中依次取两个、三个、，求它们的属性集的闭包，直到其闭包包含R的全部属性。 （5）停止，输出结果。令X代表(dibio)L和N类，Y代表(dibio)LR类第31页/共60页第三十一页，共61页。随堂练习(linx)设有关系模式设有关系模式(msh)R(A,B

18、,C,D,E,G)(msh)R(A,B,C,D,E,G)，F = BGC,BDE,DGC, ADGBC, AGB, BDF = BGC,BDE,DGC, ADGBC, AGB, BD，试求，试求R R的所有候选键。的所有候选键。第32页/共60页第三十二页，共61页。4.4 范式(fn sh)和规范化 要想设计一个好的关系,必须使关系满足一定的约束条件,此约束条件已经形成了规范(gufn),分成几个等级,一级比一级要求得严格。第33页/共60页第三十三页，共61页。4.4 范式(fn sh)和规范化 满足最低要求的关系称为第一范式,在此基础上又满足某条件,达到第二范式,如此类推,直到(zhdo

19、)第五范式.第34页/共60页第三十四页，共61页。4.4 范式(fn sh)和规范化 一个较低范式关系,可以通过关系的无损分解转换为若干较高范式关系的集合( jh),这一过程叫做关系规范化.第35页/共60页第三十五页，共61页。4.4 范式(fn sh)和规范化 一般情况下,第一范式和第二(d r)范式的关系存在许多缺点,实际的关系数据库一般使用第三范式以上的关系.第36页/共60页第三十六页，共61页。4.4.1 范式(fn sh) 范式是符合某一种级别的关系模式的集合。 关系数据库中的关系必须满足一定(ydng)的要求。满足不同程度要求的为不同范式。 范式的种类：第一范式(1NF)第二

20、范式(2NF)第三范式(3NF)BC范式(BCNF)第四范式(4NF)第五范式(5NF)第37页/共60页第三十七页，共61页。范式(fn sh)（续）各种范式之间存在(cnzi)联系：某一关系模式R为第n范式，可简记为RnNF。NF5NF4BCNFNF3NF2NF1第38页/共60页第三十八页，共61页。4.4 范式(fn sh)和规范化 n候选码定义n 设K为关系模式R(U，F)中的属性或属性组合，若K U，则k称为R的一个(y )候选码。n 若关系模式R中有多个候选码，则选定其中一个(y )作为主码。n n 组成候选码的属性称为主属性，不参加任何候选码的属性成为非主属性。f第39页/共6

21、0页第三十九页，共61页。第一(dy)范式4.4.2 4.4.2 范式的判定条件与规范化范式的判定条件与规范化1. 1. 第一范式（第一范式（1NF1NF）定义：设定义：设R R是一个关系模式，是一个关系模式，R R属于第一范式当且仅当属于第一范式当且仅当R R中每一个属性中每一个属性A A的值域只包含的值域只包含(bohn)(bohn)原原子项，即不可分割的数据项。子项，即不可分割的数据项。第40页/共60页第四十页，共61页。第一(dy)范式（续）例: 关系模式 SLC(Sno, Sdept, Sloc, Cno, Grade) Sloc为学生住处(zh ch)，假设每个系的学生住在同一个

22、地方。函数依赖包括： (Sno, Cno) f Grade Sno Sdept (Sno, Cno) P Sdept Sno Sloc (Sno, Cno) P Sloc Sdept Sloc第41页/共60页第四十一页，共61页。第一(dy)范式（续） SLC的码为(Sno, Cno)SnoCnoGradeSdeptSlocSLC第42页/共60页第四十二页，共61页。第一(dy)范式（续）结论: 1. SLC满足第一(dy)范式。2. 非主属性Sdept和Sloc部分函数依赖于码(Sno, Cno)。snocnosdeptslocgrade第43页/共60页第四十三页，共61页。第一(dy

23、)范式（续）第一范式存在的问题第一范式存在的问题插入异常：若插入一个插入异常：若插入一个(y )(y )学生，但未选课，这样的元祖不能插入。学生，但未选课，这样的元祖不能插入。删除异常：如果某个学生只选修了一门课，如果他不选该课程了，则在删除课程号的同时会把删除异常：如果某个学生只选修了一门课，如果他不选该课程了，则在删除课程号的同时会把该学生也一起删除。该学生也一起删除。数据冗余大：如果一个数据冗余大：如果一个(y )(y )学生选修了学生选修了1010门课程，则他的门课程，则他的DEPTDEPT和和SLOCSLOC值就要重复存储值就要重复存储1010次。次。修改复杂：如果要修改修改复杂：如

24、果要修改DEPTDEPT和和SLOCSLOC的值，则必须修改的值，则必须修改1010次。次。第44页/共60页第四十四页，共61页。第一(dy)范式（续） 原因 Sdept、 Sloc部分函数依赖于码。 解决方法 采用投影分解法，把SLC分解为两个关系模式，以消除这些(zhxi)部分函数依赖。 SC（Sno， Cno， Grade） SL（Sno， Sdept， Sloc）第45页/共60页第四十五页，共61页。第一(dy)范式（续）SnoCnoGradeSdeptSlocSLC第46页/共60页第四十六页，共61页。第一(dy)范式（续）函数(hnsh)依赖图：SnoCnoGradeSCSL

25、SnoSdeptSloc第47页/共60页第四十七页，共61页。第二(d r)范式2. 2. 第二范式第二范式(fn sh)(fn sh)（2NF2NF）定义：设定义：设R R是一个关系模式，是一个关系模式，R R属于第二范式属于第二范式(fn sh)(fn sh)当且仅当当且仅当R R是是1NF1NF，且每个非主，且每个非主属性都完全函数依赖于主码。属性都完全函数依赖于主码。第48页/共60页第四十八页，共61页。 第二(d r)范式（续）例：2NF关系模式SL(Sno, Sdept, Sloc)中函数(hnsh)依赖： SnoSdept SdeptSloc SnoSlocSLSnoSdep

26、tSlocSloc传递函数依赖于传递函数依赖于Sno，即，即SL中存在非中存在非主属性主属性(shxng)对码的传递函数依赖。对码的传递函数依赖。第49页/共60页第四十九页，共61页。第二(d r)范式（续）第二范式存在的问题第二范式存在的问题删除异常：如果删除异常：如果(rgu)(rgu)某个系的学生全部毕业了，在删除该系学生信息的同时，某个系的学生全部毕业了，在删除该系学生信息的同时，把这个系的信息也丢掉了。把这个系的信息也丢掉了。数据冗余大：每一个系的学生都住在同一个地方，系的住处重复了多次。数据冗余大：每一个系的学生都住在同一个地方，系的住处重复了多次。修改复杂：当学校调整学生住处，

27、如信息系的学生全部迁到另一地方，修改时修改复杂：当学校调整学生住处，如信息系的学生全部迁到另一地方，修改时必须同时修改该系的所有学生的必须同时修改该系的所有学生的SLOCSLOC属性值。属性值。原因是还存在原因是还存在slocsloc对对snosno的传递函数依赖的传递函数依赖第50页/共60页第五十页，共61页。 第二(d r)范式（续） 原因Sloc传递函数依赖于Sno 解决方法 采用投影(tuyng)分解法，把SL分解为两个关系模式，以消除传递函数依赖： SD（Sno， Sdept） DL（Sdept， Sloc） SD的码为Sno， DL的码为Sdept。SLSnoSdeptSloc第

28、51页/共60页第五十一页，共61页。 第二(d r)范式（续）SD的码为Sno， DL的码为Sdept。SnoSdeptSDSdeptSlocDL第52页/共60页第五十二页，共61页。第三(d sn)范式3. 3. 第三范式（第三范式（3NF3NF）定义：设定义：设R R是一个关系模式是一个关系模式(msh)(msh)，R R属于第三范式当且仅当属于第三范式当且仅当R R是是2NF2NF，且每个非主属，且每个非主属性都非传递函数依赖于主码。性都非传递函数依赖于主码。第53页/共60页第五十三页，共61页。第三(d sn)范式（续）例如(lr)(lr)：模型SC(SNO,SNAME,CNO,

29、GRADE)SC(SNO,SNAME,CNO,GRADE) 模型的候选码是：(SNO,CNO)(SNO,CNO)、(SNAME,CNO)(SNAME,CNO) 非主属性只有GRADEGRADE，对这两个候选码都是完全函数依赖，因此SC3NFSC3NF。存在的问题存在的问题删除异常：当学生退选了课程，元组被删除也失去学生学号和姓名删除异常：当学生退选了课程，元组被删除也失去学生学号和姓名(xngmng)(xngmng)的对应关的对应关系。系。数据冗余：学生选课多时，姓名数据冗余：学生选课多时，姓名(xngmng)(xngmng)会被重复存储。会被重复存储。第54页/共60页第五十四页，共61页。

30、BC范式(fn sh)4. BC4. BC范式范式(fn sh)(fn sh)（BCNFBCNF）定义：对于关系模式定义：对于关系模式R R，对任何非平凡的函数依赖，对任何非平凡的函数依赖X X Y Y ，X X均包含码，则均包含码，则R R属于属于BCNFBCNF。第55页/共60页第五十五页，共61页。BC范式(fn sh)（续）例：STJSTJ（S S，T T，J J）S S表示学生，T T表示教师(jiosh)(jiosh)，J J表示课程。假若每一教师(jiosh)(jiosh)只教一门课，每门课有若干教师(jiosh)(jiosh)教，某一学生选定的某门课就确定了一个固定的教师(j

31、iosh)(jiosh)。 STJ STJ中F=SJ F=SJ T T，ST ST J J，T T JJ 存在两个候选码SJSJ和STST 没有非主属性，所以STJ 3NF STJ 3NF 但是非平凡的函数依赖T T J J中T T不包含码，故STJSTJ不属于BCNFBCNF。 对STJSTJ分解，变为：STST（S S，T T），TJTJ（T T，J J）第56页/共60页第五十六页，共61页。规范化步骤(bzhu) 消除消除(xioch)(xioch)非主属性对码的部分函数依赖非主属性对码的部分函数依赖BCNFBCNF2NF2NF3NF3NF1NF1NF消除非主属性消除非主属性(shxng)(shxng)对码的