任务2-4 逻辑结构设计

【任务分析】

设计人员用E-R方法表示了数据和数据之间的联系。这种表示方法不能直接在计算机上实现，为了创建用户要求的数据库，需要把概念模型转换为某个具体的DBMS所支持的数据模型。设计人员使用关系数据库存储学生信息管理的数据，因此按照转换规则将E-R模型转换成关系模式（表），并将关系模式进行规范化，保证关系模式达到3NF。

【课堂任务】

掌握将E-R模型转换为关系模式的原则及关系模式的规范化。

● E-R模型转换为关系模式的原则

● 关系模式的规范化

● 非规范化关系模式存在的问题

● 第一范式、第二范式、第三范式

概念结构设计阶段得到的E-R模型是用户的模型，它独立于任何一种数据模型和任何一个具体的DBMS。为了创建用户要求的数据库，需要把上述概念模型转换为某个具体的DBMS支持的数据模型。数据库逻辑设计的过程是将概念结构转换成特定DBMS支持的数据模型的过程。从此开始便进入了“实现设计”阶段，需要考虑到具体DBMS的性能、具体的数据模型特点。

E-R图表示的概念模型可以转换成任何一种具体的DBMS所支持的数据模型，如网状模型、层次模型和关系模型。因为这里只讨论关系数据库的逻辑设计问题，所以只介绍E-R图如何向关系模型转换。

一般的逻辑设计分为以下3步，如图2.9所示。

（1）初始关系模式设计。

（2）关系模式规范化。

（3）模式的评价与改进。

（一）初始关系模式设计

1. 转换原则

概念设计中得到的E-R图是由实体、属性和联系组成的，而关系数据库逻辑设计的结果是一组关系模式的集合。因此将E-R图转换为关系模型实际上就是将实体、属性和联系转换成关系模式。在转换中要遵循以下规则。

规则2.1 实体类型的转换：将每个实体类型转换成一个关系模式，实体的属性即为关系的属性，实体的标识符即为关系模式的码。

规则2.2 联系类型的转换：根据不同的联系类型做不同的处理。

规则2.2.1 若实体间联系是1∶1，则可以在两个实体类型转换成的两个关系模式中的任意一个关系模式中加入另一个关系模式的码和联系类型的属性。

规则2.2.2 若实体间的联系是1∶n，则在n端实体类型转换成的关系模式中加入1端实体类型的码和联系类型的属性。

规则2.2.3 若实体间联系是m∶n，则将联系类型也转换成关系模式，其属性为两端实体类型的码加上联系类型的属性，而码为两端实体码的组合。

规则2.2.4 3个或3个以上的实体间的一个多元联系，不管联系类型是何种方法，总是将多元联系类型转换成一个关系模式，其属性为与该联系相连的各实体的码及联系本身的属性，其码为各实体码的组合。

规则2.2.5 具有相同码的关系可合并。

2. 实例

【例2.2】 将图2.10所示的含有1∶1联系的E-R图根据上述规则转换为关系模式。

该例包含两个实体，实体间存在1∶1联系，根据规则2.1和规则2.2.1可转换为如下关系模式（带下划线的属性为码）。

方案1：“负责”与“职工”两关系模式合并，转换后的关系模式如下。

职工（职工号，姓名，年龄，产品号）

产品（产品号，产品名，价格）

方案2：“负责”与“产品”两关系模式合并，转换后的关系模式如下。

职工（职工号，姓名，年龄）

产品（产品号，产品名，价格，职工号）

比较上面两个方案，在方案1中，由于不是每个职工都负责产品，会造成产品号属性的NULL值较多，所以方案2比较合理一些。

【例2.3】 将图2.11所示的含有1∶n联系的E-R图根据上述规则转换为关系模式。

例2.3包含两个实体，实体间存在1∶n联系，根据规则2.1和规则2.2.2可转换为如下关系模式（带下画线的属性为码）。

仓库（仓库号，地点，面积）

产品（产品号，产品名，价格，仓库号，数量）

【例2.4】 将图2.12所示的含有同实体集1∶n联系的E-R图根据上述规则转换为关系模式。

该例只有一个实体，实体集内部存在1∶n联系，根据规则2.1和规则2.2.2可转换为如下关系模式（带下画线的属性为码）。

职工（职工号，姓名，年龄，领导工号）

其中，“领导工号”就是领导的“职工号”，由于同一关系中不能有相同的属性名，故将领导的“职工号”改为“领导工号”。

【例2.5】 将图2.13所示的含有m∶n联系的E-R图根据规则转换为关系模式。

该例包含两个实体，实体间存在m∶n联系，根据规则2.1和规则2.2.3可转换为如下关系模式（带下画线的属性为码）。

商店（店号，店名，店址，店经理）

商品（商品号，商品名，单价，产地）

经营（店号，商品号，月销售量）

【例2.6】 将图2.14所示的同实体集间含有m∶n联系的E-R图根据规则转换为关系模式。

该例只有一个实体，实体集内部存在m∶n联系，根据规则2.1和规则2.2.3可转换为如下关系模式（带下画线的属性为码）。

零件（零件号，名称，价格）

组装（组装件号，零件号，数量）

其中，“组装件号”为组装后的复杂零件号，由于同一个关系中不允许存在同属性名，因而改为“组装件号”。

【例2.7】 将图2.15所示的多实体集间含有m∶n联系的E-R图根据规则转换为关系模式。

该例包含3个实体，3个实体间存在m∶n联系，根据规则2.1和规则2.2.4可转换为如下关系模式（带下画线的属性为码）。

供应商（供应商号，供应商名，地址）

零件（零件号，零件名，单价）

产品（产品号，产品名，型号）

供应（供应商号，零件号，产品号，数量）

【例2.8】 将图2.8所示的E-R图，根据转换规则转换为关系模式。

在图2.8所示的E-R图中，包含4个实体，实体间存在两个1∶n联系和两个m∶n联系，根据规则2.1、规则2.2.2和规则2.2.3转换为如下关系模式（带下画线的属性为码）。

系（系名，电话）

教师（教师号，姓名，性别，职称，系名）

学生（学号，姓名，性别，年龄，系名）

课程（课程号，课程名）

选修（学号，课程号，成绩）

讲授（教师号，课程号）

（二）关系模式的规范化

数据库逻辑设计的结果不是唯一的。为了进一步提高数据库应用系统的性能，还应该根据应用需要适当修改、调整数据模型的结构，这就是数据模型的优化。关系数据模型的优化通常以规范化理论为指导。关系模式设计的好坏将直接影响到数据库设计的成败。将关系模式规范化，使之达到较高的范式是设计好关系模式的唯一途径，否则，设计的关系数据库会产生一系列的问题。

1. 存在的问题及解决方法

（1）存在的问题。下面以一个实例说明一个关系没有经过规范化可能会出现的问题。

例如，要设计一个教学管理数据库，希望从该数据库中得到学生学号、姓名、年龄、性别、系别、系主任姓名、学生学习的课程名和该课程的成绩信息。若将此信息要求设计为一个关系，则关系模式如下。

S（sno，sname，sage，ssex，sdept，mname，cname，score）

该关系模式中各属性之间的关系为：一个系有若干个学生，但一个学生只属于一个系；一个系只能有一名系主任，但一个系主任可以同时兼几个系的系主任；一个学生可以选修多门课程，每门课程可被若干个学生选修；每个学生学习的每门课程都有一个成绩。

可以看出，此关系模式的码为（sno，cname）。仅从关系模式上看，该关系模式已经包括了需要的信息，如果按此关系模式建立关系，并对它进行深入分析，就会发现其中的问题。关系模式S的实例见表2.1。

从表2.1中的数据情况可以看出，该关系存在以下问题。

① 数据冗余太大。每个系名和系主任的名字存储的次数等于该系学生人数乘以每个学生选修的课程门数，系名和系主任数据重复量太大。

② 插入异常。一个新系没有招生时，或系里有学生但没有选修课程时，系名和系主任名无法插入数据库中。因为在这个关系模式中码是（sno，cname），这时没有学生而使得学号无值，或学生没有选课而使得课程名无值。但在一个关系中，码属性不能为空值，因此关系数据库无法操作，导致插入异常。

③ 删除异常。当某系的学生全部毕业而又没有招新生时，删除学生信息的同时，系及系主任名的信息随之删除，但这个系依然存在，而在数据库中却无法找到该系的信息，即出现了删除异常。

④ 更新异常。若某系换系主任，数据库中该系的学生记录应全部修改。如果稍有不慎，某些记录漏改了，就造成数据不一致，即出现了更新异常。

为什么会发生插入异常和删除异常？原因是该关系模式中属性与属性之间存在不好的数据依赖。一个“好”的关系模式应当不会发生插入和删除异常，冗余度要尽可能少。

（2）解决方法。对于存在问题的关系模式，可以通过模式分解的方法使之规范化。

例如，将上述关系模式分解成3个关系模式。

S（sno，sname，sage，ssex，sdept）

SC（sno，cname，score）

DEPT（sdept，mname）

这样分解后，3个关系模式都不会发生插入异常、删除异常的问题，数据的冗余也得到了控制，数据的更新也变得简单。

“分解”是解决冗余的主要方法，也是规范化的一条原则，“关系模式有冗余问题，就分解它”。

2. 函数依赖基本概念

（1）规范化。规范化是指用形式更为简洁、结构更加规范的关系模式取代原有关系模式的过程。

（2）关系模式对数据的要求。关系模式必须满足一定的完整性约束条件以达到现实世界对数据的要求。完整性约束条件主要包括以下两个方面。

① 对属性取值范围的限定。

② 属性值间的相互联系（主要体现在值的相等与否），这种联系称为数据依赖。

（3）属性间的联系。项目1讲到客观世界的事物间存在着错综复杂的联系，实体间的联系有两类：一类是实体与实体之间的联系；另一类是实体内部各属性间的联系。这里主要讨论第二类联系。

属性间的联系可分为3类。

① 一对一联系（1∶1）。以学生关系模式S（sno，sname，sage，ssex，sdept，mname，cname，score）为例，如果学生无重名，则属性sno和sname之间是一对一联系，一个学号唯一地决定一个姓名，一个姓名也唯一地决定一个学号。

设X、Y是关系R的两个属性（集）。如果对于X中的任一具体值，Y中至多有一值与之对应；反之亦然，则称X、Y两属性间是一对一联系。

② 一对多联系（1∶n）。在学生关系模式S中，属性sdept和sno之间是一对多联系，即一个系对应多个学号（如计算机系可对应20060101、20060102等），但一个学号只对应一个系（如20060101只能对应计算机系）。同样，mname和sno、sno和score之间都是一对多联系。

设X、Y是关系R的两个属性（集）。如果对于X中的任一具体值，Y中至多有一个值与之对应，而Y中的一个值却可以和X中的n个值（n≥0）相对应，则称Y对X是一对多联系。

③ 多对多联系（m∶n）。在学生关系模式S中，cname和score两属性间是多对多联系。一门课程对应多个成绩，而一个成绩也可以在多门课程中出现。sno和cname、sno和score之间也是多对多联系。

设X、Y是关系R的两个属性（集）。如果对于X中的任一具体值，Y中有m（m≥0）个值与之对应，而Y中的一个值也可以和X中的n个值（n≥0）相对应，则称Y对X是多对多联系。

上述属性间的3种联系实际上是属性值之间相互依赖又相互制约的反映，称为属性间的数据依赖。

（4）数据依赖。数据依赖是指通过一个关系中属性间值的相等与否体现出来的数据间的相互关系，是现实世界属性间相互联系的抽象，是数据内在的性质。

数据依赖共有3种：函数依赖（Functional Dependency，FD）、多值依赖（Multivalued Dependency，MVD）和连接依赖（Join Dependency，JD），其中最重要的是函数依赖和多值依赖。

（5）函数依赖。在数据依赖中，函数依赖是最基本、最重要的一种依赖，它是属性之间的一种联系，假设给定一个属性的值，就可以唯一确定（查找到）另一个属性的值。例如，知道某一学生的学号，可以唯一地查询到其对应的系别，如果这种情况成立，就可以说系别函数依赖于学号。这种唯一性并非指只有一个记录，而是指任何记录。

定义1：设有关系模式R（U），X和Y均为U=｛A1，A2，…，An｝的子集，r是R的任一具体关系，r中不可能存在两个元组在X上的属性值相等，而在Y上的属性值不等（也就是说，如果对于r中的任意两个元组t和s，只要有t［X］=s［X］，就有t［Y］=s［Y］），则称X函数决定Y，或称Y函数依赖于X，记作X→Y，其中X叫作决定因素（Determinant），Y叫作依赖因素（Dependent）。

这里的t［X］表示元组t在属性集X上的值，s［X］表示元组s在属性集X上的值。FD是对关系模式R的一切可能的当前值r的定义，不是针对某个特定关系的。通俗地说，在当前值r的两个不同元组中，如果X值相同，就一定要求Y值也相同；或者说，对于X的每个具体值，都有Y唯一的具体值与之对应。

下面介绍一些相关的术语与记号。

① X→Y，但，则称X→Y是非平凡的函数依赖。

② X→Y，但Y⊆X，则称X→Y是平凡的函数依赖。因为平凡的函数依赖总是成立的，所以若不特别声明，本书后面提到的函数依赖，都不包含平凡的函数依赖。

③ 若X→Y，Y→X，则称X↔Y。

④ 若Y不函数依赖于X，则记作。

定义2：在关系模式R（U）中，如果X→Y，并且对于X的任何一个真子集X′，都有，则称Y对X完全函数依赖，记作。

若X→Y，如果存在X的某一真子集X′（X′⊆X），使X′→Y，则称Y对X部分函数依赖，记作。

定义3：在关系模式R（U）中，X、Y、Z是R的3个不同的属性或属性组，如果X→Y（，Y不是X的子集），且，Y→Z，则称Z对X传递函数依赖，记作。

加上条件，是因为如果Y→X，则X↔Y，实际上是X→Z，是直接函数依赖而不是传递函数依赖。

（6）属性间联系决定函数依赖。前面讨论的属性间的3种联系，并不是每种联系中都存在函数依赖。

① 1∶1联系。如果两属性集X、Y之间是1∶1联系，则存在函数依赖X↔Y。例如，学生关系模式S中，如果不允许学生重名，则有sno↔sname。

② 1∶n联系。如果两属性集X、Y之间是n∶1联系，则存在函数依赖X→Y，即多方决定一方，如sno→sdept、sno→sage、sno→mname等。

③ m∶n联系。如果两属性集X、Y之间是m∶n联系，则不存在函数依赖，如sno和cname之间、cname和score之间就是如此。

【例2.9】 设有关系模式S（sno，sname，sage，ssex，sdept，mname，cname，score），判断以下函数依赖的对错。

① sno→sname，sno→ssex，（sno，cname）→score。

② cname→sno，sdept→cname，sno→cname。

在①中，因为sno和sname之间存在一对一或一对多联系，sno和ssex、（sno，cname）和score之间存在一对多联系，所以这些函数依赖是存在的。

在②中，因为sno和cname、sdept和cname之间都是多对多联系，所以它们之间是不存在函数依赖的。

【例2.10】 设有关系模式：学生课程（学号，姓名，课程号，课程名称，成绩，教师，教师年龄），因为在该关系模式中，成绩要由学号和课程号共同确定，教师决定教师年龄。所以此关系模式中包含了以下函数依赖关系。

学号→姓名（每个学号只能有一个学生姓名与之对应）

课程号→课程名称（每个课程号只能对应一个课程名称）

（学号，课程号）→成绩（每个学生学习一门课只能有一个成绩）

教师→教师年龄（每一个教师只能有一个年龄）

识别函数依赖是理解数据语义的一个组成部分，依赖是关于现实世界的断言，它不能被证明，决定关系模式中函数依赖的唯一方法是仔细考察属性的含义。

3. 范式

利用规范化理论，使关系模式的函数依赖集满足特定的要求，满足特定要求的关系模式称为范式。

关系按其规范化程度从低到高可分为5级范式（Normal Form），分别称为1NF、2NF、3NF（BCNF）、4NF、5NF。规范化程度较高者必是较低者的子集，即

5NF⊆4NF⊆BCNF⊆3NF⊆2NF⊆1NF

一个低一级范式的关系模式，通过模式分解可以转换成若干个高一级范式的关系模式的集合，这个过程称为规范化。

（1）第一范式（1NF）。

定义4：如果关系模式R中不包含多值属性（每个属性必须是不可分的数据项），则R满足第一范式（First Normal Form），记作R∈1NF。

1NF是规范化的最低要求，是关系模式要遵循的最基本的范式，不满足1NF的关系是非规范化的关系。

关系模式如果仅仅满足1NF是不够的。尽管学生关系模式S满足1NF，但它仍然会出现插入异常、删除异常、更新异常及数据冗余等问题，只有对关系模式继续规范化，使之满足更高的范式，才能得到高性能的关系模式。

（2）第二范式（2NF）。

定义5：如果关系模式R（U，F）∈1NF，且R中的每个非主属性完全函数依赖于R的某个候选码，则R满足第二范式（Second Normal Form），记作R∈2NF。

【例2.11】 关系模式S-L-C（U，F）。

U=｛SNO，SDEPT，SLOC，CNO，SCORE｝，其中SNO是学号，SDEPT是学生所在系，SLOC是学生的宿舍（住处），CNO是课程号，SCORE是成绩。

该关系模式的码=（SNO，CNO）

函数依赖集F=｛（SNO，CNO）→SCORE，SNO→SDEPT，SNO→SLOC，SDEPT→SLOC｝非主属性=｛SDEPT，SLOC，SCORE｝

非主属性对码的部分函数依赖=｛（SNO，CNO）SDEPT，（SNO，CNO）SLOC｝

显然，该关系模式不满足2NF。

不满足2NF的关系模式，会产生以下几个问题。

① 插入异常。插入一个新学生，若该生没有选课，则CNO为空，但因为码不能为空，所以不能插入。

② 删除异常。某学生只选择了一门课，现在该门课要删除，该学生的基本信息也将删除。

③ 更新异常。某个学生要从一个系转到另一个系，若该生选修了K门课，则必须修改的该学生相关的字段值为2K个（系别、住处），一旦有遗漏，将破坏数据的一致性。

造成以上问题的原因是SDEPT、SLOC部分函数依赖于码。

解决的办法是用投影分解把关系模式分解为多个关系模式。

投影分解是把非主属性及决定因素分解出来构成新的关系，决定因素在原关系中保持，函数依赖关系相应分开转化（将关系模式中部分依赖的属性去掉，将部分依赖的属性单独组成一个新的模式）。

上述关系模式分解的结果如下。

S-C（SNO，CNO，SCORE）

码=｛（SNO，CNO）｝ F=｛（SNO，CNO）→SCORE ｝

S-L（SNO，SDEPT，SLOC）

码=｛SNO｝ F=｛SNO→SDEPT，SNO→SLOC，SDEPT→SLOC｝

因为经过模式分解，两个关系模式中的非主属性对码都是完全函数依赖，所以它们都满足2NF。

（3）第三范式（3NF）。

定义6：如果关系模式R（U，F）∈2NF，且每个非主属性都不传递函数依赖于任何候选码，则R满足第三范式（Third Normal Form），记作R∈3NF。

在例2.11中，因为关系S-L（SNO，SDEPT，SLOC），SNO→SDEPT，SDEPT→SLOC，SLOC传递函数依赖于码SNO，所以S-L不满足3NF。

解决的方法同样是将S-L进行投影分解，结果如下。

S-D（SNO，SDEPT）码=｛SNO｝F=｛SNO→SDEPT｝

D-L（SDEPT，SLOC）码=｛SDEPT｝F=｛SDEPT→SLOC｝

分解后的关系模式中不再存在传递函数依赖，即关系模式S-D和D-L都满足3NF。

3NF是一个可用的关系模式应满足的最低范式，也就是说，一个关系模式如果不满足3NF，则实际上它是不能使用的。

（4）BCNF。BCNF（Boyce Codd Normal Form）是由Boyce和Codd提出的，比上述的3NF又进了一步，通常认为BCNF是修正的第三范式，有时也称为扩充的第三范式。

定义7：关系模式R（U，F）∈1NF，若X→Y且时，X必含有码，则R（U，F）∈BCNF。

也就是说，在关系模式R（U，F）中，若每个决定因素都包含码，则R（U，F）∈BCNF。

由BCNF的定义可以得出结论，一个满足BCNF的关系模式有以下特点。

① 所有非主属性对每一个码都是完全函数依赖。

② 所有的主属性对每一个不包含它的码也是完全函数依赖。

③ 没有任何属性完全函数依赖于非码的任何一组属性。

【例2.12】 设关系模式SC（U，F），其中U=｛SNO，CNO，SCORE｝

F=｛（SNO，CNO）→SCORE ｝

因为SC的候选码为（SNO，CNO），决定因素中包含码，没有属性对码传递依赖或部分依赖，所以SC∈BCNF。

【例2.13】 设关系模式STJ（S，T，J），其中S是学生，T是教师，J是课程。每位教师只教一门课，每门课有若干教师，某一学生选定某门课，就对应一位固定的教师。

由语义可得到如下的函数依赖。

（S，J）→T，（S，T）→J，T→J

该关系模式的候选码为（S，J）、（S，T）。

因为该关系模式中的所有属性都是主属性，所以，但，因为T是决定因素，但T不包含码。

不属于BCNF的关系模式，仍然存在数据冗余问题。如例2.13中的关系模式STJ，如果有100个学生选定某一门课，则教师与该课程的关系就会重复存储100次。STJ可分解为如下两个满足BCNF的关系模式，以消除此种冗余。

TJ（T，J）

ST（S，T）