《[PPT模板]第六章sharepoint.ppt》由会员分享，可在线阅读，更多相关《[PPT模板]第六章sharepoint.ppt（39页珍藏版）》请在三一办公上搜索。
1、6 数据库设计,对于一个给定的应用环境，构造最优的数据库模式，建立数据库及其应用系统，使之能有效地存储数据，满足各种用户的应用需求。,2,主要内容,6.1关系数据库理论 6.2E-R图6.3数据库设计的基本步骤6.4数据库设计工具,3,重、难点,重点规范化理论E-R图数据库设计的基本步骤难点2NF、3NF,6.1关系数据库理论,5,6.1.1关系模式规范化,关系模式设计得不好的问题冗余存储更新异常插入异常删除异常,emp_idemp_nameemp_phonedept_namedept_phonedept_mgrnameskill_idskill_nameskill_dateskill_lvl
2、,Key:emp_id,skill_id,6,6.1.2函数依赖（Functional Dependencies，FD）,设R(U)是属性集U上的关系模式。X，Y是U的子集。若对于R(U)的任意一个可能的关系r，r中不可能存在两个元组在X上的属性值相等，而在Y上的属性值不等，则称X函数确定Y或Y函数依赖于X，记作XY。,emp_id emp_name emp_id emp_phone emp_id dept_name emp_phone emp_id,emp_id emp_name emp_phone dept_name,7,函数依赖的规则,设关系模式 R（ABCD），属性值间联系：A值与B值
3、有一对多联系，即每个A值有多个B值与之联系，而每个B值只有一个A值与之联系；C值与D值之间有一对一联系，即每个C值只有一个D值与之联系，每个D值只有一个C值与之联系。根据这些规则写出相应的函数依赖。从 A值与 B值有一对多联系，可写出函数依赖:B A从 C值与 D值有一对一联系，可写出两个函数依赖:C D和 D C,8,例,有一个关于学生选课、教师任课的关系模式：R（S#，SNAME，C#，GRADE，CNAME，TNAME，TAGE）,如果规定：每个学号只能有一个学生姓名，每个课程号只能决定一门课程，可写成FD：S#SNAME C#CNAME每个学生每学一门课程，有一个成绩，可写出 FD：S
4、#C#GRADE还可以写出其他一些 FD：C#CNAME TNAME TAGE TNAME TAGE,9,6.1.3阿姆斯特朗公理,T，属性集：X,Y,Z包含规则:If YX,then XY.XY 是平凡依赖.,emp_id emp_id emp_id skill_id skill_id emp_id emp_name emp_phone dept_nameemp_id skill_id skill_date skill_lvl,10,传递规则:If X Y and Y Z,then X Z.增广规则:If X Y,then XZ YZ.Note：XZ=X UNION ZProve two r
5、ows u and v of XZ,if u(XZ)=v(XZ)then u(X)=v(X)and u(Z)=v(Z)If XY then when u(X)=v(X)then u(Y)=v(Y)u(YZ)=v(YZ),6.1.3阿姆斯特朗公理,11,阿姆斯特朗公理的蕴涵,T，属性集：X,Y,and Z合并规则(Union）:If XY and XZ,then XYZ.分解规则（Decomposition）:If X YZ,then X Y and X Z.伪传递（Pseudotransitivity）:If XY and WYZ,then XW Z.集合累积规则（Set accumulati
6、on）:If X YZ and ZW,then X YZW.,12,6.1.4函数依赖的闭包（Closure）,定义 F是函数依赖集，F的闭包(closure)是指被F逻辑蕴涵的所有函数依赖的集合，记做F+。,例 F=AB,BC,CD,DE,EF,FG,GH,AA,B B,etc.,by transitivity，AB and BC,then AC;BC and CD,then BDby union get AAB,AABC,.AABCDEFGH By decomposition,A(any non-empty subset of ABCDEFGH),13,6.1.5属性集的闭包,定义 设F为
7、属性集U上的一组函数依赖，X是U的子集，那么属性集X的闭包用X+表示，它是一个从函数依赖集使用FD推理规则推出的所有满足XA的属性A的集合：X+=A|XA在F+中,14,属性集的闭包算法,属性集 X,函数依赖集 F.X+=?I=0;X0=X;/*integer I,attr.set X0*/REPEAT/*loop to find larger XI*/I=I+1;/*new I*/XI=XI-1;/*initialize new XI*/FOR ALL ZW in F/*loop on all FDs ZW in F*/IF ZXI/*if Z contained in XI*/THEN X
8、I=XIW;/*add attributes in W to XI*/END FOR/*end loop on FDs*/UNTIL XI=XI-1;/*loop till no new attributes*/RETURN X+=XI;/*return closure of X*/,集合累积规则:If XYZ and ZW then XYZW.,例 the set F of FDs:F=BCD,ADE,BA Given X=B,X+=?X0=B.X1=BBCDX1=BCDADEBA X1=ABCD.X2=X1=ABCDADE X2=ABCDE.X3=X2,15,6.1.5函数依赖集的最小依赖
9、集,设F是属性集 U上的 FD集，如果Fmin是 F的最小依赖集，则Fmin应满足：（1）F+min=F+；（2）Fmin中没有冗余的 FD（即在 F中不存在这样的函数依赖 X Y，使得 F与F-X Y等价）；（3）每个 FD的左边没有冗余的属性（即 F中不存在这样的函数依赖 X Y，X有真子集 W使得 F-X Y W Y与 F等价）。,16,下面求最小函数依赖集的具体步骤：,1.从函数依赖集F，创建函数依赖一个等价集H，它的函数依赖的右边只有单个属性。,F:(1)AB,(2)CB,(3)DABC,(4)ACDstep 1.H:(1)AB,(2)CB,(3)DA,(4)DB,(5)DC,(6)
10、ACD,17,2.从函数依赖集H，顺次去掉在H中非关键的单个函数依赖。一个函数依赖XY在一个函数依赖集H中是非关键的，是指如果XY从H中去掉，得到结果J，仍然有H+=J+。,step 1.H:(1)AB(2)CB(3)DA(4)DB(5)DC(6)ACD,remove(1)ABleaving only(2)CB(3)DA(4)DB(5)DC(6)ACD.Take X=A,X+=A,So need(1).,Step 2.,H:(1)AB,remove(2)C Bleaving only(1)AB(3)DA(4)DB(5)DC(6)ACD.Take X=C,X+=C,So need(2).,(2)
11、CB,(3)DA,(5)DC,(6)ACD,remove(3)DAleaving only(1)AB(2)CB(4)DB(5)DC(6)ACD.Take X=D,X+=BCD,So need(3).,remove(4)D Bleaving only(1)AB(2)CB(3)DA(5)DC(6)ACD.Take X=D,X+=ABCD,So(4)can leave out.,remove(5)DCleaving only(1)AB(2)CB,(3)DA(6)ACD.Take X=D,X+=ABD,So(5)needed,remove(6)ACD leaving only(1)AB(2)CB(3)
12、DA(5)DC.Take X=AC,X+=ABC,So(6)needed.,下面求最小函数依赖集的具体步骤：,18,3.从函数依赖集H，顺次用左边具有更少属性的函数依赖替换原来的函数依赖，只要不会导致H+改变。,Step 2H:(1)AB,(2)CB,(3)DA,(4)DC,(5)ACD(Renumber),Step 2.,H:(1)AB(2)CB(3)DA(5)DC(6)ACD,(5)Drop C?J:(1)AB,(2)CB,(3)DA,(4)DC,(5)AD A+UNDER H=AB,A+UNDER J=ABDC.(5)Drop A?J:(1)AB,(2)CB,(3)DA,(4)DC,(5
13、)CD C+UNDER H=BC,C+UNDER J=CBDA.(NO NEED TO TRY STEP 2 AGAIN.),19,4.从剩下的函数依赖集中收集所有左边相同的函数依赖，使用合并规则创建一个等价函数依赖集Fmin，它的所有函数依赖的左边是唯一的。,Step 3H:(1)AB,(2)CB,(3)DA,(4)DC,(5)ACD,(3)DA(4)DC DAC,Step 4H:(1)AB,(2)CB,(3)DAC,(4)ACD,20,6.1.6无损分解,定义：表T分解为：T1,T2,Tk(1)Ti,Head(Ti)Head(T)(2)Head(T)=Head(T1)Head(T2)Hea
14、d(Tk)如果T=T1T2Tk,则是无损分解。,21,有损分解,Table T,T1,T2,T1 JOIN T2,有损分解,22,Table T,T1,T2,无损分解,BC,Table T,23,无损分解的判断,表 T，函数依赖集FT的分解：T1,T2,Head(T)=Head(T1)Head(T2)T1,T2 是无损分解Head(T1)Head(T2)Head(T1)orHead(T1)Head(T2)Head(T2),24,例,设关系模式R，其中U=A，B，C，D，E，F=ABC，CD，BCE，EA，则分解P=R1（ABCE），R2（CD）是否具有无损连接性？Head(R1)Head(R2
15、)=ABCE CD=CC C D所以P=R1（ABCE），R2（CD）满足无损连接性,25,保持函数依赖的分解,如果F上的每一个函数依赖都在其分解后的某一个关系上成立，则这个分解是保持依赖的。例：设关系模式R，其中U=A，B，C，D，E，F=ABC，CD，BCE，EA，则分解P=R1（ABCE），R2（CD）是否保持函数依赖？对于ABC，A BC Head(R1)对于CD，C D Head(R2)对于BCE，BC E Head(R1)对于EA，E A Head(R1)所以，P=R1（ABCE），R2（CD）保持函数依赖。,26,6.2.3 范式（Normal Forms）,First Norm
16、al Form(1NF),27,第二范式（Second Normal Form，2NF),定义 若关系模式R1NF，而且每一个非主属性完全函数依赖于键，则R2NF。局部依赖:对于 FD，W A，如果存在 X W，有 X A成立，称 W A是局部依赖；否则，W A是完全依赖。设关系模式 R（S#，C#，GRADE，TNAME，TADDR）的属性分别表示学生学号、选修课程的编号、成绩、任课教师姓名和教师地址。（S#，C#）是 R的候选键。R上有两个 FD：S#，C#TNAME TADDR和 C#TNAME TADDR，因此前一个 FD是局部依赖。,28,第二范式（Second Normal For
17、m，2NF),定义 若关系模式R1NF，而且每一个非主属性完全函数依赖于键，则R2NF。局部依赖:对于 FD，W A，如果存在 X W，有 X A成立，称 W A是局部依赖；否则，W A是完全依赖。设关系模式 R（S#，C#，GRADE，TNAME，TADDR）的属性分别表示学生学号、选修课程的编号、成绩、任课教师姓名和教师地址。（S#，C#）是 R的候选键。R上有两个 FD：S#，C#TNAME TADDR和 C#TNAME TADDR，因此前一个 FD是局部依赖。,29,emp_idemp_nameemp_phone dept_name dept_phone dept_mgrname sk
18、ill_id skill_name skill_dateskill_lvl,emp_id emp_name emp_phone dept_namedept_name dept_phone dept_mgrnameskill_id skill_nameemp_id skill_id skill_date skill_lvl,emp_info,Key:emp_id skill_id X=emp_id skill_id.X+=emp_id skill_id skill_date skill_lvl skill_name emp_name emp_phone dept_name dept_phone
19、dept_mgrnameX head(emp_info)then X is a superkey,30,emp_idemp_nameemp_phonedept_namedept_phonedept_mgrname,emp_idskill_id skill_name skill_date skill_lvl,emps,skills,emp_id emp_name emp_phone dept_namedept_name dept_phone dept_mgrnameskill_id skill_nameemp_id skill_id skill_date skill_lvl,Key of emp
20、s:emp_idKey of skills:emp_id skill_id,Table emps and skills is a lossless decomposition of emp_info,PROOF.Head(emps)Head(skills)=Head(emp_info)Head(emps)Head(skills)=emp_idemp_idHead(emps),2NF？,31,emp_idemp_nameemp_phonedept_namedept_phonedept_mgrname,emp_idskill_id skill_date skill_lvl,emps,emp_ski
21、lls,emp_id emp_name emp_phone dept_namedept_name dept_phone dept_mgrnameskill_id skill_nameemp_id skill_id skill_date skill_lvl,Key of emps:emp_idKey of emp_skills:emp_id skill_idKey of skills:skill_id,skill_id skill_name,skills,2NF,32,Third Normal Form(3NF),如果 X Y，Y A，且 Y X和 A Y，那么称 X A是传递依赖（A传递依赖于
22、 X）。,关系模式R2NF，且它的任何一个非主属性(组)都不传递依赖于任何候选键，则称R3NF。,33,emp_idemp_nameemp_phonedept_namedept_phonedept_mgrname,emp_idskill_id skill_date skill_lvl,emps,emp_skills,emp_id emp_name emp_phone dept_namedept_name dept_phone dept_mgrnameskill_id skill_nameemp_id skill_id skill_date skill_lvl,Key of emps:emp_i
23、dKey of emp_skills:emp_id skill_idKey of skills:skill_id,skill_id skill_name,skills,3NF？,34,emp_idemp_nameemp_phonedept_name,emp_idskill_id skill_date skill_lvl,emps,emp_skills,emp_id emp_name emp_phone dept_namedept_name dept_phone dept_mgrnameskill_id skill_nameemp_id skill_id skill_date skill_lvl
24、,Key of emps:emp_idKey of depts:dept_nameKey of emp_skills:emp_id skill_idKey of skills:skill_id,skill_id skill_name,skills,dept_namedept_phonedept_mgrname,depts,3NF,35,Def.6.8.4.Boyce-Codd Normal Form（BCNF）,定义 设关系模式R（U，F）1NF，如果对于R的每个函数依赖XY(Y X)，X必包含键，则RBCNF，又称修正(或扩充)的第三范式。,36,emp_idemp_nameemp_phon
25、edept_name,emp_idskill_id skill_date skill_lvl,emps,emp_skills,emp_id emp_name emp_phone dept_namedept_name dept_phone dept_mgrnameskill_id skill_nameemp_id skill_id skill_date skill_lvl,Key of emps:emp_idKey of depts:dept_nameKey of emp_skills:emp_id skill_idKey of skills:skill_id,skill_id skill_na
26、me,skills,dept_namedept_phonedept_mgrname,depts,BCNF,37,取消表中主属性对码的部分和传递函数依赖,取消表中非主属性对码的传递函数依赖,3NF,取消表中非主属性对码的部分函数依赖,1NF,2NF,BCNF,38,Algorithm 6.8.8 a table T and set F of FDs,generate a lossless join decomposition of T that is in 3NF and preserves all FDs of F.,REPLACE F WITH MINIMAL COVER OF F;S=;F
27、OR ALL XY in FIF,FOR ALL ZS,XYZTHEN S=S HEADING(X Y);END FORIF,FOR ALL CANDIDATE KEYS K FOR TFOR ALL Z S,K ZTHEN CHOOSE A CANDIDATE KEY K ANDSET S=SHeading(K);,39,练习：假设某商业集团数据库中有一关系模式 R如下：R（商店编号，商品编号，商品库存数量，部门编号，负责人）如果规定：（1）每个商店的每种商品只在该商店的一个部门销售；（2）每个商店的每个部门只有一个负责人；（3）每个商店的每种商品只有一个库存数量。试回答下列问题（1）根据上述规定，写出关系模式 R的基本函数依赖；（2）找出关系模式 R的候选键；（3）试问关系模式 R最高已经达到第几范式？为什么？（4）如果 R不属于 3NF，请将 R分解成 3NF模式集。,(1)有三个函数依赖：(商店编号，商品编号)部门编号(商店编号，部门编号)负责人(商店编号，商品编号)商品库存数量(2)R的候选键是(商店编号，商品编号)(3)因为R中存在着非主属性“负责人”对候选键(商店编号、商品编号)的传递函数依赖，所以R属于2NF，R不属于3NF。(4)将R分解成：R1(商店编号，商品编号，数量，部门编号)R2(商店编号，部门编号，负责人),
