关系数据库设计理论培训教程

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,关系数据库设计理论,本章学习目标,本章主要讲解函数依赖和以函数为基础的几种关系范式。通过本章的学习，读者应掌握以下内容：,函数依赖,关系范式1NF、2NF、3NF,7.1 引言,7.1.1 讨论范围,数据库是一组相关数据的集合。它不仅包括数据本身，而且还包括数据之间的联系，即数据模型。给出一组数据，如何构造一个适当的数据模型，在关系数据库中应该组织成几个关系模式，每个关系模式包括哪些属性，这些都是数据库逻辑设计要解决的问题。在具体数据库系统实现之前，尚未录入实际数据时，组建较好的数据模型是关系到整个系统运行效率，甚至系统成败的关键。,在以关系模型为基础的数据库中，用关系来描述现实世界。关系具有概念单一性的特点，一个关系既可以描述一个实体，也可以描述实体间的联系。一个关系模型包括一组关系模式，各个关系不是完全孤立的。只有它们相互间存在关联，才能构成一个模型。这些关系模式的全体定义构成关系数据库模式。,关系模型有严格的理论基础，也是目前应用最广泛的数据模型，指导数据库逻辑设计的是关系数据库规范化理论。关系数据库设计理论主要包括三个方面的内容：数据依赖、范式和模式设计方法。数据依赖起着核心的作用。我们重点讨论函数依赖的概念，然后再介绍模式分解的标准，即范式，为数据库的设计准备一定的基本理论基础。这里力求以大家都熟悉的实例说明问题。,7.1 引言,7.1.2 存储异常问题,有“教学”关系模式： 教学（教师号，教师姓名，职称，教师地址，系号，系名称，系地址，课程号，课程名，教学水平，学分）。,表7-1 “教学”关系,教师号,教师,姓名,职称,教师,地址,系号,系名称,系地址,课程号,课程名,教学,水平,学分,T1,MA,PRF,A1,D1,DEPT1,L1,C1,COMPU,GOOD,3,T1,MA,PRF,A1,D1,DEPT1,L1,C2,PROGR,EXCEL,3,T1,MA,PRF,A1,D1,DEPT1,L1,C3,DB,OK,4,T2,LI,AP,A2,D1,DEPT1,L1,C3,DB,GOOD,4,T2,LI,AP,A2,D1,DEPT1,L1,C4,OS,GOOD,4,T3,CHEN,PRF,A3,D1,DEPT1,L1,C4,OS,OK,2,T3,CHEN,PRF,A3,D1,DEPT1,L1,C1,COMPU,OK,3,T3,CHEN,PRF,A3,D1,DEPT1,L1,C5,DSTRU,EXCEL,3,T4,ZHOU,AP,A4,D2,DEPT2,L2,C6,MATH,GOOD,5,7.1 引言,7.1.2 存储异常问题,主码是（教师号，课程号），但该关系在使用过程中存在以下几个问题：,（1）数据冗余。每当教师开设一门课程时，该教师的职称、地址等信息就重复存储一次。一般一个系有很多教师，每位教师都开设多门课，每位教师的职称、地址要多次重复存储，这样会使关系中的数据冗余度很大。,（2）更新异常。由于数据的重复存储，会给更新带来麻烦。如果一位任3门课的教师改变了地址，3个元组的地址都要更新，一旦一个元组的地址未修改就会导致数据不一致。如果某个系改变办公地址，所要修改的数据量会更大。,（3）插入异常。如果学校新调入一个教师，暂时未主讲任何课程。由于缺少主码的一部分，而主码不允许出现空值，新教师的相关信息就不能插入到此关系中去。只有当他开设了课程之后才能插入，这是不合理的。,（4）删除异常。与插入异常相反，如果某些教师致力于科研，不担任教学任务了，就要从当前数据库中删除有关记录。那么关于这些教师的其他信息将无法记载，这也是极不合理的。,7.1 引言,7.1.2 存储异常问题,用下面4个关系模式代替原来的一个关系模式，上述4个方面的问题就基本解决了。,教师（教师号，教师姓名，职称，教师地址，系号）,系（系号，系名称，系地址）,课程（课程号，课程名，学分）,选修（教师号，课程号，教学水平）,新关系模型包括4个关系模式，“教师”和“系”通过“教师”中的外码“系号”相联系；“教师”与“课程”之间多对多的联系可以通过“选修”中的外码（教师号，课程号）相联系，需要时再进行自然联接，则恢复了原来的关系。但是将“教学”分解成“教师”、“系”、“课程”、“选修”四个模式是否最佳，并不是绝对的。 例如，要频繁地查询讲授某门课程教师的情况，就要对这两个关系做联接操作，而联接是以机时为代价的。在原来的关系模式中则可以直接查到。到底哪个关系模型更好，要根据数据库的规模、数据的共享程度和应用需求来权衡。,7.2 函数依赖,7.2.1 属性之间的联系,实体内部各属性间的联系分为3类：一对一、一对多和多对多。设图书管理关系数据模型包括以下3个关系模式：,图书（总编号，分类号，书名，作者，出版单位，单价）,读者（借书证号，姓名，性别，单位，职称，地址）,借阅（借书证号，总编号，借阅日期，备注）,1一对一联系,在读者关系中，借书证号是惟一的。,设X、Y为关系中的属性或属性组，它们的所有可能取值组成两个集合。为简便起见，也叫X、Y，如果对于X中的任一具体值，Y中至多有一个值与之对应，并且对于Y中的任一具体值，X中也至多有一个值与之对应，称X、Y这两个属性之间是一对一联系。,7.2 函数依赖,7.2.1 属性之间的联系,2一对多联系,在图书关系中，一本书有若干副本，它们有相同的书名、作者、分类号等，但每本书有惟一的总编号。书名与总编号之间是1:m，即同一个书名有多个总编号与之对应。,如果属性值集合X中的任一具体值，至多与Y中的一个值对应，而Y中的任一具体值却可以和X中的多个值相对应，则称两个属性间从X到Y为m:1的联系。或从Y到X是1:m的联系。,3多对多联系,在借阅关系中，一个读者可以借阅多本书，即同一个借书证号有若干个图书总编号与之对应。由总编号标识的一本书在不同日期可以被不同的读者借阅。又如在选修关系中一个学生可以选修几门课，同一门课有多个学生同时选修。,在X、Y两个属性值集中，如果任一个值都可以至多和另一个属性集中的多个值对应，反之亦然，则称属性X和Y是m:n关系。,显然，3类联系之间存在着包含关系，1:1是1:m的特例；1:m又是m:n的特例。,关系中属性值之间这种相互依赖又相互制约的联系称为数据依赖。数据依赖主要有两种：函数依赖和多值依赖。,7.2 函数依赖,7.2.2 函数依赖,1函数依赖的概念,定义7.1 用U表示属性集的全集A1，A2，An，设R(U)是属性集U上的关系模式，X和Y是U的子集。对于R(U)的所有具体关系r都满足如下约束：对于X的每一个具体值，Y有惟一的具体值与之对应，则称“X函数确定Y”或“Y函数依赖于X”，记作XY，X称做决定因素。,在关系模式R(U)中，对于U的子集X和Y，如果XY，但Y,X，则称XY是非平凡依赖。若Y,X，则称XY为平凡依赖。,例1 有关系 工资（职工号，基本工资，奖金），一个职工号惟一确定一个基本工资数额或一个奖金额，即一个人不能拿两种工资或奖金，但几个人的工资可能相同。具体数字如表7-2所示。,表7-2 “工资”表,职工号,基本工资,奖金,051,390,50,052,420,50,053,270,70,054,390,70,7.2 函数依赖,7.2.2 函数依赖,设属性A是职工号，属性B是基本工资，属性C是奖金，可以看出，每个A的值对应一个B的值和一个C的值。因此，属性B和C都函数依赖于属性A。但反过来则不存在这种关系，如基本工资390对应两个职工号051和054。用符号表示为：AB，AC，B A，C A，B C。,根据函数依赖的定义，可以找出下面规律：,（1）在一个关系模式中，如果属性X、Y有1:1联系，则存在函数依赖XY、YX。可记作X,Y。,（2）如果属性X、Y是1:m的联系，则存在函数依赖YX，但X Y。,（3）如果属性X、Y是n:m的联系，则X与Y之间不存在任何函数依赖。,必须注意，函数依赖是指关系R模式的所有元组均应满足的约束条件，而不是指关系模式R的某个或某些元组满足的约束条件。当关系中的元组增加或者更新后都不能破坏函数依赖。因此，必须根据语义来确定数据之间的函数依赖，而不能单凭某一时刻关系中的实际数据值来判断。,7.2 函数依赖,7.2.2 函数依赖,2完全函数依赖与部分函数依赖,定义7.2 设XY是关系模式R(U)的一个函数依赖，如果存在X的真子集X,，使得X,Y成立，则称Y部分依赖于X，记作。否则称Y完全依赖于X，记作。,例2 设有关系模式 选课（学号，课程号，成绩，学分）,在“选课”关系中，由于一个学生可以选修多门课程，一门课程可有多个学生选修，因此“学号”或“课程号”都不能单独确定“成绩”。“成绩”只能由属性组合（学号，课程号）来确定。“学分”可由“课程号”来确定，由此可知：（学号，课程号）成绩 （学号，课程号）学分,3传递依赖,定义7.3 在同一关系模式R(U)中，如果存在非平凡函数依赖XY，YZ，且Y X，则XZ，即称Z传递依赖于X。,例3 设关系模式 学生（学号，姓名，系号，系名，系地址）。,存在函数依赖：学号系号，系号 学号，系号系地址。,根据传递依赖的定义可知：学号系地址 实际上，部分依赖必然是传递依赖。在选修（学号，课程号，成绩，学分）关系中：（学号，课程号）课程号，课程号学分，形成传递依赖（学号，课程号）学分。,7.2 函数依赖,7.2.4 码,1主码、候选码、外码,定义7.5 关系模式R(U)中，K是U中的属性或属性组，如果KU，则称K为R(U)的一个候选码。若关系模式R(U)中有多个候选码，可以选中其中的一个做为主码。若K不是本关系的主码，而是其他关系的主码，则称K为外码。,候选码有两个性质：,（1）标识的惟一性：对于R(U)中的每一个元组，K的值确定后，该元组就相应地确定了。,（2）无冗余性：当K是属性组的情况下，K的任何一部分都不能惟一标识该元组。这是定义中完全依赖的意义。,2主属性和非主属性,定义7.6 包含在任意一个候选码中的属性，称为主属性。不包含在任何候选码中的属性称为非主属性。,关系 选修（学号，课程号，成绩），（学号，课程号）是候选码也是主码。“学号”，“课程号”是主属性，“成绩”是非主属性。,7.3 关系模式的规范化,7.3.1 第一范式（INF）,定义7.7 在关系模式R中的每一个具体关系r中，如果每个属性值都是不可再分的最小数据单位，则称R是第一范式的关系，记为R,1NF。,不是1NF的关系称为非规范化关系。数据库理论研究的都是规范化关系，如何将普通的复式表格规范为关系呢？下面通过例子来说明。,例1 表7-3所示的数据不是1NF，因为“电话号码”可以分为“办公室电话”和“家庭电话”两个属性。请将表7-3规范成1NF。,表7-3 非规范化“职工”表,职工号,姓名,电话号码,1001,李明,5362633（O）、5366677（H）,1002,张敏,5001277,1003,刘大维,5677901（O）、5679971（H）,1004,章良弟,6543210,1005,何为民,4776532,7.3 关系模式的规范化,7.3.1 第一范式（INF）,将上表规范成1NF可以有三种方法。,第一种方法：重复存储职工号和姓名，如表7-3（a）所示。在这样的关系中，主码只能是电话号码。如果单独查阅此关系问题不大，若通过职工号与其他关系联接，由于职工号不是主码，则可能造成大量冗余。,表7-3（a） 规范化“职工1”表,职工号,姓名,电话号码,1001,李明,5362633,1001,李明,5366677,1002,张敏,5001277,1003,刘大维,5677901,1003,刘大维,5679971,1004,章良弟,6543210,1005,何为民,4776532,7.3 关系模式的规范化,7.3.1 第一范式（INF）,第二种方法：保留职工号的主码地位，把电话号码拆分成单位电话和住宅电话两个属性,如表7-7（b）所示。这样会使只有一个电话号码的元组出现空属性值，由于电话号码不是候选码，允许有空值。,表7-7（b） 规范化“职工2”表,职工号,姓名,单位电话,住宅电话,1001,李明,5362633,5366677,1002,张敏,5001277,1003,刘大维,5677901,5679971,1004,章良弟,6543210,1005,何为民,4776532,7.3 关系模式的规范化,7.3.1 第一范式（INF）,第三种方法：保留职工号的主码地位，维持原模式不变，但强制每个元组只能录入一个电话号码，如表7-3（c）所示。,表7-3（c） 规范化“职工3”表,职工号,姓名,电话号码,1001,李明,5362633,1002,张敏,5001277,1003,刘大维,5677901,1004,章良弟,6543210,1005,何为民,4776532,7.3 关系模式的规范化,7.3.2 第二范式（2NF）,满足第一范式的关系仍有可能出现问题。,例2 设有关系模式 选修（学号，课程号，成绩，学分）,存在函数依赖：（学号，课程号）成绩，（学号，课程号）学分，主码是（学号，课程号）。如表3-4所示。,7.3 关系模式的规范化,7.3.2 第二范式（2NF）,满足第一范式的关系仍有可能出现问题。,例2 设有关系模式 选修（学号，课程号，成绩，学分）,存在函数依赖：（学号，课程号）成绩，（学号，课程号）学分，主码是（学号，课程号）。如表3-4所示。,表3-4 选修,学号,课程号,成绩,学分,S1,C1,90,4,S1,C2,75,3,S1,C3,70,2,S2,C2,70,3,S2,C3,75,2,S2,C4,95,3,S3,C2,75,3,7.3 关系模式的规范化,7.3.2 第二范式（2NF）,该关系模式在实际应用中会再现下列问题：,（1）数据冗余。每当一名学生选修一门课程时，该课程的“学分”就重复存储一次。假设同一门课被40名学生选修，学分就重复40次。,（2）更新异常。如果调整了课程的学分，每个相应元组的“学分”值都必须更新。这不仅增加了更新代价，更严重的是有潜在的数据不一致性。,（3）插入异常。如果学校计划增开新课，应当把新课的课程号及学分数插入到“选修”中。但是由于缺少主码的一部分，将不能插入。,（4）删除异常。如果学生已经结业，从当前数据库中删除选修记录，某些课程新生尚未选修，那么关于这些课程的学分记载将无法保留。这是极不合理的。,定义3.7 若关系模式R,1NF，并且每一个非主属性都完全依赖于R的任一候选码，则称关系R是第二范式的，记为R,2NF。,将上述非2NF的关系SC1规范化为2NF关系，通过投影分解为以下两个关系模式：,选修2（学号，课程号，成绩）,课程（课程号，学分）,7.3 关系模式的规范化,7.3.3 第三范式（3NF）,满足第二范式的关系仍然可能出现问题，其原因是由于关系中存在传递依赖：,定义3.9 如果关系模式R（U，F）中的所有非主属性对任何码都不存在传递依赖，则称关系R是第三范式的，记为R,3NF。,通过投影分解将“学生2”分解成如下两个关系后，则满足3NF的要求。,学生（学号，姓名，系号）,系（系号，系名，系地址）,由于部分依赖必然是传递依赖，所以如果一个关系模式不存在传递依赖，则必不存在部分依赖。换言之，满足3NF的关系模式必然满足2NF。,对关系规范化的分解过程体现了“一事一地”的原则，即一个关系反映一个实体或一个联系，不应当把几样东西混放在一起。基本关系模式切忌“大而全”，在若干个基本关系模式组成的关系模型的基础上，“教学”关系模型包括如下4个关系模式：,选修（学号，课程号，成绩）,课程（课程号，课程名，学分）,学生（学号，姓名，系号）,系（系号，系名，系地址）,7.3 关系模式的规范化,7.3.4 BCNF范式,3NF消除了大部分存储异常。但3NF模式仍然可能发生存储异常现象。,有关系：仓库保管（仓库号，器件号，职工号，数量），表中数据如表3-5所示：,表3-5 仓库保管,仓库号,器件号,职工号,数量,W1,P1,E1,20,W1,P2,E3,15,W1,P3,E2,17,W1,P4,E1,10,W1,P5,E3,20,W1,P6,E2,15,W1,P7,E3,17,W2,P4,E4,12,W2,P1,E5,20,W2,P2,E4,15,W2,P3,E4,17,W2,P6,E5,15,W3,P2,E6,20,W3,P3,E6,15,W3,P4,E7,17,7.3 关系模式的规范化,7.3.4 BCNF范式,一个仓库有多个职工；一个职工仅在一个仓库工作；每个仓库里一种器件由专人负责，但一个人可以管理几种器件。根据语义可以分析出以下函数依赖：,（仓库号，器件号）数量 （仓库号，器件号）职工号,职工号仓库号 （职工号，器件号）数量,该关系模式属于3NF。如果新职工被分配到仓库工作，处于实习阶段，缺少码的一部分“器件号”而无法插入到关系中。要消除这些异常，提出更高的要求BCNF范式。,定义3.10 如果关系模式R(U，F)的所有属性都不传递依赖于R的任何候选码，那么称关系R是属于BCNF的，记为R,BCNF。与其等价的定义是：对于关系模式R，如果每个因素都包含码（而不是被码所包含），则R是BCNF的关系模式。,将仓库保管（仓库号，器件号，职工号，数量）关系模式分解成工作和保管两个关系模式，则符合BCNF的要求。,保管（职工号，器件号，数量）其主码是：（职工号，器件号）,工作（职工号，仓库号） 其主码是：职工号,分解后的两个关系可以通过自然联系恢复原来的关系。这种分解具有无损联接性。,判定无损分解的法则，即无损分解的充分必要条件是：R1R2R1-R2或R1R2R2-R1,7.3 关系模式的规范化,7.3.5 多值函数依赖,如果仅考虑函数依赖这一种数据依赖，属于BCNF的关系模式已经很完美了。但如果考虑其他数据依赖，例如多值依赖，属于BCNF的关系模式仍然存在问题，不能算是一个完美的关系模式。,例1 设学校中某一门课程由多个教师讲授，他们使用一套相同的参考书。可以用一个关系教学2（课程，教师，参考书），该关系如图所示。,数学,邓军,陈斯,数学分析,高等代数,微分方程,物理,李平,王强,刘明,普通物理,光学原理,3.3 关系模式的规范化,3.3.5 多值函数依赖,图3-2 课程-教师-参考书之间的关系,该关系可用二维表表示，如表3-6所示。,表3-6 教学2,课程,教师,参考书,数学,数学,数学,数学,数学,数学,物理,物理,物理,物理,物理,物理,邓军,邓军,邓军,陈斯,陈斯,陈斯,李平,李平,王强,王强,刘明,刘明,数学分析,高等代数,微分方程,数学分析,高等代数,微分方程,普通物理,光学原理,普通物理,光学原理,普通物理,光学原理,7.3 关系模式的规范化,7.3.5 多值函数依赖,“教学2”模式中存在一些问题：,（1）数据冗余大。每一门课程的参考书是固定的，但在“教学2”关系中，有多少名任课教师，参考书就要存储多少次，造成大量的数据冗余。,（2）插入操作复杂。当某一课程增加一名任课教师时，该课程有多少本参考书，就必须插入多少个元组。,（3）删除操作复杂。某一门课要去掉一本参考书，该课程有多少名教师，就必须删除多少个元组。,（4）修改操作复杂。某一门课要修改一本参考书，该课程有多少名教师，就必须删除多少个元组。,定义3.11 设R（U）是属性集U上的一个关系模式，X，Y和Z是U的子集，并且Z=U-X-Y，多值依赖XY成立当且仅当对R的任一关系r，r在（X，Y）上的每个值对应一组Y的值，这组值仅仅决定于X值而与Z值无关。,若XY，而Z=,，则称XY为平凡的多值依赖。否则称XY为非平凡的多值依赖。,在“教学2”关系中，每个（课程，参考书）上的值对应一组值，而且这种对应与“参考书”无关。例如（课程，参考书）上的一个值（物理，光学原理）对应一组T值李平，王强，刘明，这组值仅仅决定于“课程”上的值，也就是说对于（课程，参考书）上的另一个值（物理，普通物理），它对应一组T值仍是李平，王强，刘明，尽管这时“参考书”的值已经改变了。因此“教师”多值依赖于“课程”，即 课程教师。,7.3 关系模式的规范化,3.3.5 多值函数依赖,多值依赖具有下列性质：,（1）对称性：若XY，则XZ，其中Z=U-X-Y。例如，在关系模式Teach（C，T，B）中，已经知道CT，必然有CB。,（2）传递性：若XY，YZ，则XYZ。,（3）函数依赖可以看作是多值依赖的特殊情况。即若XY，则XY。这是因为当XY时，对X中的每一个值，Y都有一个确定的值与之对应，所以XY。,（4）若XY，XZ，则XYZ。,（5）若XY，XZ，则XYZ。,（6）若XY，XZ，则XY-Z，XZ-Y。,（7）多值依赖的有效性与属性集的范围有关。即如果XY在U上成立，则在W（XY,W,U）上一定成立；但XY在W（W,U）上成立，在U上并不一定成立。,（7）若多值依赖XY在R（U）上成立，对于Y,Y，并不一定有XY,成立。但是如果函数依赖XY在R上成立，则对于任何Y,Y均有X,Y,。,7.3 关系模式的规范化,7.3.6 第四范式（4NF）,定义3.12 关系模式R（U，F）,1NF，如果对于R的每个平凡多值依赖XY（Y不是X的子集），X都含有候选码，则R,4NF。,4NF就是限制关系模式的属性之间不允许有非平凡且非函数依赖的多值依赖。因为根据定义，对于每一个非平凡的多值依赖XY，X都含有候选码，于是当然XY，所以4NF所允许的非平凡多值依赖实际上是函数依赖。,显然，如果一个关系模式是4NF，则必满足BCNF。,3.3 关系模式的规范化,3.3.6 第四范式（4NF）,前面讨论过的关系模式“教学2”中存在非平凡的多值依赖 课程教师，且“课程”不是候选码，因此“教学”不属于4NF。这正是它存在数据冗余大、插入和删除操作复杂等弊病的根源。可以用投影分解法把“教学2”分解为如下两个4NF关系模式以减少数据冗余。,授课（课程，教师）,资料（课程，参考书）,“授课”中虽然有 课程教师，但这是平凡多值依赖，即“授课”中已不存在既非平凡也非函数依赖的多值依赖。所以“授课”属于4NF。同理，“资料”也属于4NF。分解后，“教学2”关系中的几个问题可以得到解决。,（1）参考书只需要在“资料”关系中存储一次。,（2）当某一课程增加一名任课教师时，只需要在“授课”关系中增加一个元组。,（3）某一门课要去掉一本参考书，只需要在“资料”关系中删除一个相应的元组。,7.3 关系模式的规范化,7.3.7 关系模式小结,一个低一级范式的关系模式，通过模式分解可以转换为若干个高一级范式的关系模式集合，这种过程就叫关系模式的规范化。,1关系模式规范化的步骤,1NF, 消除非主属性对码的部分函数依赖,2NF, 消除非主属性对码的传递函数依赖,3NF, 消除非主属性对码的部分传递函数依赖,BCNF, 消除非平凡且非函数依赖的多值依赖,4NF, 消除不是候选码所蕴含的连接依赖,5NF,7.3 关系模式的规范化,2关系模式的分解,判断一个分解是否与原关系模式等价可以有三种不同的标准：,（1）分解具有无损连接性。,（2）分解要保持函数依赖。,（3）分解既要保持函数依赖，又要具有无损连接性。,如果一个分解具有无损连接性，则它能够保证不丢失信息。如果一个分解保持了函数依赖，则它可以减轻或解决各种情况。,分解具有无损连接性和分解保持函数依赖是两个互相独立的标准。具有无损连接性的分解不一定能够保持函数依赖。同样，保持函数依赖的分解也不一定具有无损连接性。规范化的理论提供了一套完整的模式分解算法，按照这套算法可以做到：,（1）若要求分解具有无损连接性，则模式分解一定能够达到4NF。,（2）若要求分解保持函数依赖，则模式分解一定能够达到3NF，但不一定能够达到BCNF。,（3）若要求分解既具有无损连接性，又保持函数依赖，则模式分解一定能够达到3NF，但不一定能够达到BCNF。,