数据库第2篇设计chapter

第6章逻辑数据库设计

任务：把概念数据库设计阶段产生的概念数据库模式变换为逻辑数据库模式。目标：满足用户的完整性和安全性要求，动态关系至少具有第三范式形式，静态关系至少具有第一范式形式，能够在逻辑上高效率地支持各种数据库事务的运行，存储空间利用率高。逻辑数据库设计逻辑数据库设计步骤：形成初始关系数据库模式关系模式规范化关系模式优化定义关系上的完整性和安全性约束子模式定义性能估计6.1形成初始关系数据库模式ER图关系模型普通实体型的变换对于数据库概念模式中的每个普通实体型E，建立一个关系S。S包含E的所有简单属性和E的复合属性的简单子属性。E的键是S的主键。弱实体的变换多值属性的变换6.1形成初始关系数据库模式实例1)需求分析：①某公司有多个部门；(部门作实体)②每个部门承担多个工程项目；每个工程项目只属于一个部门;③每个部门有多名职工，而每个职工只属于一个部门④一个职工参加多个工程项目；每个工程项目可有多个职工参加6.1形成初始关系数据库模式⑤每个部门有一个经理，他是职工中的一员⑥一个职工可能有多名亲属，也可能没有亲属⑦一名职工有多种技能，多名职工可以有同一种技能2)概念设计：局部ER图全局ER图(1)局部ER图：共有6个，需求分析中的②—⑦条各对应一个图(2)全局ER图：6.1形成初始关系数据库模式部门领导聘用职工有亲属承担月薪工程参加具有从事年限技能工时m11m11mm1nmmm6.1形成初始关系数据库模式3)逻辑设计：⑴部门(部门号，名称，地址，电话，(领导)职工号)⑵工程(工程号，工程名，经费预算，部门号)⑶职工(职工号，姓名，生日，性别，部门号，月薪)⑷亲属(职工号，亲属的姓名，关系)⑸技能(技能号，技能名)⑹施工(职工号，工程号，工时)⑺人才(职工号，技能号，从事年限)6.1形成初始关系数据库模式4)实施---(SQL语句)eg：createtable部门(部门号char(3)notnullunique，名称char(10)notnull，地址char(30)，电话char(13)，职工号smallint，（主键）primarykey(部门号)

（外键）foreignkey(职工号)references职工)；6.1形成初始关系数据库模式弱实体型的变换设W是概念数据库模式中以实体型E为识别实体型的弱实体。建立一个与W对应的关系R。W的所有简单属性和复合属性的简单子属性映射为R的属性，E的键属性也是R的属性。R的键有E的键和W的部分键组合而成。E对应的关系的键是R的外部键。6.1形成初始关系数据库模式多值属性的变换设实体型E具有多值属性，S是E对应的关系。我们为E的每个多值属性A建立一个关系T，用T表示A。如果A是简单属性，T的属性为A与S的主键K，A和K形成T的主键。如果A是复合属性，T包含A的简单子属性和S的主键K。A的简单子属性和K形成T的键。S关系中忽略属性A。6.1形成初始关系数据库模式实体间联系的变换(1)1:1联系的变换设R是实体型E1和E2之间的1:1联系，S和T是E1和E2对应的关系T(或S)的主键作为外部键添入S(或T)R的简单属性和复合属性的简单子属性作为简单属性添入S(或T)(2)1:N联系的变换设R是实体型E1和E2之间的1:N联系，S和T是E1和E2对应的关系S的主键作为外部键添入TR的简单属性和复合属性的简单子属性作为简单属性添入T6.1形成初始关系数据库模式(3)M:N联系的变换设R是实体型E1和E2之间的M:N联系，S和T是E1和E2对应的关系建立一个新关系W来表示RS和T的主键添入W，既作为外部键，也组合起来作为W的主键.W要包含R的简单属性和复合属性的简单子属性。函数依赖(一对一映射)

范式(1NF，2NF，3NF，BCNF……)

模式分解

Amstrong公理系统6.1形成初始关系数据库模式(4)n元联系的变换设R是关联实体型E1，E2，…，En的n元联系。类似于M：N联系的表示方法，可以建立一个关系T,用T来表示R。所有Ei的主键都是T的外部键，也将它们组合起来作为T的主键。T还包括R的简单属性和复合属性的简单子属性。6.1形成初始关系数据库模式超类/子类联系的变换设实体型C是实体型E1,E2,…,En的超类，k,A1,A2,…,An,k是C的属性，k是C的键。可以使用4种方法把这个超类/子类联系变换为关系模式。方法1建立一个表示C的关系L。L的属性包括k,A1,A2,…,A,k是键。对于1<=i<=m,建立表示Ei的关系Li。Li的属性包括k和Ei的所有属性，而且k是Li的键。6.1形成初始关系数据库模式方法2对于1<=i<=m，建立与Ei对应的关系Li。Li的属性包括Ei和C的所有属性。K是Li的键。这个方法适用于具有正交约束的演绎。如果不满足全域约束，这种方法可能丢失不属于任何子类的实体。如果不满足正交约束，属于多个实体型的实体将重复地存储在不同的Li中。方法3建立一个关系L。L的属性包括所有C，E1,E2,…,Em的属性。此外，L还需要一个特殊属性t。T用来说明每个元组所属的子类。6.1形成初始关系数据库模式方法4建立一个关系L。L的属性包括属性包括所有C,E1,E2,…,Em的属性。类似于方法3，L需要包括m个特殊属性t1,t2,…,tm。ti用来说明一个元组是否属于子类Ei。这种方法适用于子类相交的情况。范畴和共享子类的变换确定函数依赖集6.2关系数据库设计理论6.2.1问题的提出

(1)插入异常

(2)删除异常

(3)数据冗余和更新问题6.2.2函数依赖定义6.2.1：设R是一个关系模式，U是R的属性集合，X和Y是U的子集。对于R的任意实例r，r中任意两个元组t1和t2，如果t1[X]=t2[X]，则t1[Y]=t2[Y]，则称X函数的确定Y，或Y函数地依赖于X，记做XY。6.2关系数据库设计理论如果XY，而且Y不是X的子集，则称XY是非平凡函数依赖。如果XY我们称X为这个函数依赖的决定属性集。定义6.2.2：设R是一个具有属性集合U的关系模式，如果XY，并且对于X的任何一个真子集Z，ZY都不成立，则称Y完全函数依赖于X。若XY，但Y不完全函数依赖于X，则称Y部分函数依赖于X。6.2关系数据库设计理论定义6.2.3：设R是一个具有属性集合U的关系模式，ＸU，YU，ZU，YX不成立，

ZX，ZY和YX不空。如果XY，YZ

则称Z传递地函数依赖于X。定义6.2.4：R是一个具有属性集合U的关系模式，KU。如果K满足下列两个条件，则称K是R的一个候选键：

1)KU2)不存在K的真子集Z使得ZU。候选键可以唯一地识别关系的元组。

6.2关系数据库设计理论一个关系模式中可能具有多个候选键。可以制定一个候选键作为识别关系元组的主键。包含在任何一个候选键中的属性称为键属性。不包含在任何候选键中的属性称为非键属性。在最简单的情况下，候选键只包含一个属性。在最复杂情况下，候选键包含关系模式的所有属性，称为全键。6.2关系数据库设计理论定义6.2.5：设X是关系模式R的属性子集合。如果X是另一个关系模式的候选键，则称X是R的外部键。6.2.3数据依赖的公理系统定义6.2.6：设R是一个具有属性集合U的关系模式，F是R的一个函数依赖集合。Armstrong公理系统设R是一个具有属性集合U的关系模式，F是R的一个函数依赖集合。

Armstrong公理系统包含如下三条推理规则：6.2关系数据库设计理论自反律：若YXU，则F蕴含XY增广律：若F蕴含XY，ZU，则F蕴含

XZYZ传递律：若F蕴含XY和YZ，则F蕴含XZ定理6.2.1：Armstrong推理规则是正确的。根据上述三条规则可以得到下面三条有用的推理规则：6.2关系数据库设计理论合并规则：如果XY，XZ，则XYZ伪传递规则：如果XY，YWZ，则XWZ分解规则：如果XY，ZY，则XZ定理6.2.2：合并规则，伪传递规则和分解规则是正确的。引理6.2.1：XA1A2…Ak成立的充分必要条件是，对于1<=i<=k，XAi成立。6.2关系数据库设计理论定义6.2.7：设R是一个具有属性集合U的关系模式，F是给定的函数依赖集合。有F逻辑蕴含的所有函数依赖称为F的闭包，记为：。定义6.2.8：设F为属性集U上的一组函数依赖，XU，={A|XA能由F根据Armstrong公理导出}称为属性集X关于函数依赖集F的闭包。引理6.2.2：设F为属性集U上的函数依赖，X，YU,XY能由F根据Armstrong公理导出的充分必要条件是Y。6.2关系数据库设计理论算法6.2.1：计算输入：X，F。输出：。方法：(1)X(1)：=空集合；X(0)：=X;(2)B：={A|VW∈F,V∈X(0),A∈W}(3)X(1)：=B∪X(0)⒋IFX(1)=X(0)THENX(0)：=X(1)；

GOTO2；ELSE：=X(1)ENDIF。6.2关系数据库设计理论定理6.2.3：算法6.2.1正确地计算出了X关于F的闭包。定理6.2.4：Armstrong公理系统是有效且完备的。定义6.2.9：设G和F是两个函数依赖集。如果

=则称F与G等价。引理6.2.3：设G和F是两个函数依赖集。F与G等价的充分必要条件是F且G。6.2关系数据库设计理论定义6.2.10：函数依赖集F称为极小(或最小)函数依赖集，如果F满足下列条件：F中任一函数依赖的右部仅含有一个属性；F中不存在这样的函数依赖XA，使得F与F-{XA}等价；F中不存在这样的函数依赖XA，X包含真子集Z使得(F-{XA})∪{ZA}与F等价。定理6.2.5：每一个函数依赖集F都等价于一个极小函数依赖集。6.2关系数据库设计理论6.2.4关系模式的规范形式把一个给定关系模式转化为某种凡是的过程称为关系模式的规范化过程，简称规范化。1.第一范式(1NF)定义6.2.11：设R是一个关系模式。如果R的每个属性的值域都是不可分的简单数据项的集合，则称这个关系模式为第一范式关系模式，及做1NF。6.2关系数据库设计理论2.第二范式(2NF)定义6.2.12:若关系模式R是1NF，而且每个非主属性都完全函数依赖于R的键，则R称为第二范式关系模式，记做2NF。3.第三范式(3NF)定义6.2.13：如果关系模式R是2NF，而且它的任何一个非键属性都不传递地依赖于任何候选键，则R称为第三范式关系模式，记做3NF。6.2关系数据库设计理论4.BCNF范式定义6.2.14：设关系模式R是1NF。如果对于R的每个函数依赖XY，X必为候选键，则R是BCNF范式。BCNF的性质：所有非键属性都完全函数依赖于每个候选建所有键属性都完全函数依赖于每个不包含它的候选建没有任何属性完全函数依赖于非键的任何一组属性6.2关系数据库设计理论注：是BCNF一定是3NF，但是3NF不一定是BCNF。6.2.5多值依赖与第四范式定义6.2.15：设R是一个具有属性集合U的关系模式，X，Y和Z是U的子集，并且Z=U-X-Y，多值依赖XY成立当且仅当对Ｒ的任一关系r，r在(X,Z)上的每个值对应一组Y的值，这组值仅仅决定于X值而与Z值无关。6.2关系数据库设计理论定理6.2.6：多值依赖公理是有效的和完备的。定理6.2.7：多值依赖的推理规则是正确的。定义6.2.16：设R是一个关系模式，D是R上的多值依赖集。如果对于R的每个多值依赖

XY(Y-X=非空集，XY未包含R的全部属性)，X都含有R的候选键，则R是第四范式关系模式，简记为4NF。当D包含函数依赖是，如果关系模式R是4NF，则R必为BCNF，反之不然。6.2关系数据库设计理论6.2.6连接依赖与第五范式定义6.2.17:设R,R1,R2,…,Rn是关系模式,U,U1,U2,…,Un分别是R,R1,R2,…,Rn的属性集合，而且U=U∪U1∪…∪Un。如果R的任意关系实例r满足

r=……

则称R满足连接依赖，记做(R1,R2,…,Rn)。上述定义中，如果存在一个i使得Ri=R，则对应的连接依赖称为平凡连接依赖。6.2关系数据库设计理论定义6.2.18：设R,R1,R2,…,Rn是关系模式，Ri的属性集合包含在R的属性集合中，D是R上的函数依赖、多值依赖和连接依赖的集合。R称为满足D的第五范式，如果对于中的每个连接依赖(R1,R2,…,Rn)下面两条至少一条成立：(1)(R1,R2,…,Rn)是平凡连接依赖；(2)每个Ri都包含R的一个候选键。第五范式简称PJNF。6.3关系模式规范化方法6.3.1无损连结性与函数依赖保持性定义6.3.1：设R是具有属性集合U的关系模式。R的一个分解定义为

ρ={R1,R2,…,Rn}

其中，Ri的属性集合是Ui,而且U=。定义6.3.2：设R是一个具有属性集合U和函数依赖集合F的关系模式，ρ={R1,R2,…,Rn}是R的一个分解。F在Ri的属性集合Ui上的投影室Fi={XY|XY∈，X，Y∈Ui}。6.3关系模式规范化方法定义6.3.3：设ρ={R1,R2,…,Rn}是关系模式R的一个分解，r是R的一个关系。定义

(r)=……

即(r)是r在ρ中各个关系模式上的投影的连接。引理6.3.1：设R是一个关系模式，ρ={R1,R2,…,Rn}是R的一个分解，r是R的一个关系实例，则

(1)r(r)6.3关系模式规范化方法(2)若s=(r),则=。

(3)((r))=(r)。定义6.3.4：设ρ={R1,R2,…,Rn}是R的一个分解。若对R的任何一个关系r均有r=成立，则称分解ρ具有无损连结性。简称ρ为无损连接分解。6.3关系模式规范化方法算法6.3.1：判别一个分解的无损连接性。输入：R(A1，A2，……，An)，

R的函数依赖集FR的分解={R1，R2，……，Rk}。输出：分解是否具有无损连接性。方法：(1)建立矩阵S，列j对应属性Aj，行i对应Ri；6.3关系模式规范化方法(2)FORi=1TOkDOFORj=1TOnDOS[i,j]：=bij；

ENDFOR；

ENDFOR；(3)FORi=1TOkDOFORj=1TOnDOIFRi包含属性AjTHENS[i,j]:=aj；

ENDFOR；

ENDFOR；6.3关系模式规范化方法(4)DOUNTILS无改变

FOR每个XYFDOFORS中所有在X对应的列上具有相同符号的行i1、……、imDO按照下列规则修改Y所对应列的符号：①FOR每个具有“a”类符号的Y对应列DO

把该列i1、……、im行的符号改为相同的“a”

类符号；

ENDFOR；6.3关系模式规范化方法②FOR每个不具有“a”类符号的Y对应列DO

在该列上选择一个“b”类符号；

ENDFOR；

ENDFOR；

ENDFOR；

ENDUNTIL；(5)如果S中存在一行全为“a”类符号，则ρ具有无损连接性；否则ρ不具有无损连接性。6.3关系模式规范化方法例6.3.2：已知：关系模式R的属性集合U={A，B，C，D，E}，函数依赖集合F={AC，BC，CD，DEC，CEA}，分解={R1(A,D)，R2(A,B)，R3(B,E)，R4(C,D,E)，R5(A,E)}判断是否具有无损连接性。解：使用以上算法检验是否具有无损连接性。①根据算法的⑴至⑶步构造矩阵S6.3关系模式规范化方法ABCDER1a1b12b13a4b15R2a1A2b23b24b25R3b31A2b33b34a5R4b41b42a3a4a5R5a1b52b53b54a5②根据算法第⑷步进行矩阵变换。AC使b13，b23，b53相等，用b13代替。然后BC使b33=b13。结果矩阵为：6.3关系模式规范化方法ABCDER1a1b12b13a4b15R2a1a2b13b24b25R3b31a2b13b34a5R4b41b42a3a4a5R5a1b52b13b54a5③CD使a4=b24=b34=b54，都变为a4。

DEC使b13=a3，用a3代替。

CEA使b31=b41=a1，用a1代替。结果如下:6.3关系模式规范化方法ABCDER1a1b12a3a4b15R2a1a2a3a4b25R3a1a2a3a4a5R4a1b42a3a4a5R5a1b52a3a4a5④由于第三行已经成为a1、a2、a3、a4、a5，所以具有无损连接性。6.3关系模式规范化方法定理6.3.1：关系R的分解ρ具有无损连接性的充分必要条件是算法6.3.1终止时，矩阵S中有一行为(a1、a2、……、an)。定理：设={R1,R2}是关系模式R的一个分解U1、U2和U分别是R1、R2和R的属性集合，F是R的函数依赖集合。ρ具有无损连接性的充分必要条件是：

U1U2U1－U2或

U1U2U2－U1。6.3关系模式规范化方法定义6.3.5:设R是具有属性集合U和函数依赖集合F的关系模式，ρ={R1,R2,…,Rn}是R的一个分解，Ui是Ri的属性集合，Fi是F在Ui上的投影。如果则称ρ具有函数依赖保持性。

6.3关系模式规范化方法6.3.2关系模式分解算法1.3NF分解算法算法6.3.3是一个只能保证函数依赖保持性的分解算法。算法6.3.4是一个既能保证函数依赖保持性也能保证无损连接性的分解算法。定理6.3.3：设关系模式R的分解ρ={R1,R2,…,Rn}是算法6.3.3的输出结果，G是F的最小函数依赖集，则ρ具有函数依赖保持性，而且ρ中每个关系模式都是3NF。6.3关系模式规范化方法算法6.3.43NF分解算法2。输入:关系模式R，R的属性集合U和最小函数依赖集F。输出:具有函数依赖保持性和无损连接性的分解τ,τ中所有关系模式都是3NF。方法：(1)调用算法6.3.3产生R的分解