MySQLER图转化成关系模式 数据库er图转为关系模式

E-R模型，是下午题的一个必考题，遇到这样的题，最好是能脑补出数据库中的二维表格。

在要求添加关系时，一定要审好题，题目是要求添加几个关系。不同的要求，添加的关系也完全不一样。

1、E－R模型：实体一联系模型

实体是矩形来表示的。实体的特征就是属性。属性用椭圆表示。

联系，有一对多，一对一，一对多（E1每个实体与另一个实体集E2中任意个实体0或多个有联系，而E2中每个实体最多只有一个与E1中一个实体有联系），多对多联系。联系用菱形框来表示联系。（加上  每个  后再判断 实体之间的关系是不是更容易些呢）

画ER图的方法：先找出哪个是实体，再找出联系的类型，最后把属性放到这里面去，最后确定键值。

 

ER模型转换成关系模型的规则：

1:1

遇到1:1 关系的话在两个实体任选一个添加另一个实体的主键即可。比较专业点的说法是：可以在两个实体类型转换成的两个关系模式中任意一个关系模式的属性中加入另一个关系模式的键和联系类型的属性。

1:N

1:N 遇到 1:N 关系的话在N端添加另一端的主键，假如有学生和班级两个实体，一个班级可以容纳多个学生，但是一个学生只能选择一个班级， 因此班级和学生是1:N的关系，现在要转换为关系模型， 我们只需在学生的这端加上班级的唯一标识即可，这样做的原因是，因为一个学生只能有一个班级，班级是相对学生唯一的。

N:M

遇到N:M我们需要将联系转换为实体，然后在该实体上加上另外两个实体的主键，作为联系实体的主键，然后再加上该联系自身带的属性即可。例如有学生和老师两个实体， 一个学生可以由多名老师来授课，一名老师也可以授课多名学生，它们是M:N关系的，假如联系为授课，该联系上有成绩属性，因此当我们把它转换为关系模型时，我们把联系转换为联系实体，并添加学生实体的主键（学号）和教师实体的主键（教师编号）作为自己的主键，值得注意的是，授课实体的外键分别是学号和教师编号，但是它的主键是（学号，教师编号），另外它还拥有自己的一个属性成绩。

1:1:1:

可以在三个实体类型转换成的三个关系模式中任意一个关系模式的属性中加入另两个关系模式的键（作为外键）和联系类型的属性。

1:1:N

这是三元联系的对应关系，但是当转换为关系模型时，和1:N的情况是差不多的。我们只需将N端添加另外两端的主键即可。

1:M:N

则将联系类型也转换成关系模式，其属性为M端和N端实体类型的键（作为外键）加上联系类型的属性，而键为M端和N端实体键的组合。

M:N:P

这种三元联系的三种多对应关系，看上去很复杂，其实转换起来并不是那么复杂了，我们要做的仅仅是将其中的联系转换为联系实体，然后在联系实体上添加M端N端P端的主键，然后加上联系实体自身的属性，就行了。

ER实体扩展：这部分还挺重要的，2018年上半年软考下午第2题涉及到

弱实体：一个实体必须依赖于另一个实体存在，那么前者是弱实体，后者是强实体，弱实体必须依赖强实体存在，例如上图的学生实体和成绩单实体，成绩单依赖于学生实体而存在，因此学生是强实体，而成绩单是弱实体。

弱实体和强实体的联系必然只有1：N或者1：1，这是由于弱实体完全依赖于强实体，强实体不存在，那么弱实体就不存在，所以弱实体是完全参与联系的，因此弱实体与联系之间的联系也是用的双线菱形。

如下图所示：

复合实体：复合实体也称联合实体或桥接实体，常常用于实现两个或多个实体间的M：N联系，它由每个关联实体的主玛组成，用长方体内加一个菱形来表示。

下图就是一个典型的复合实体，因为只是举例，相对粗糙，用户和商品两个实体是M：N的关系，中间又订单这个实体联系，因此订单这个实体是一个复合实体，同时如果用户 实体不存在，就没有订单实体的存在，因此对于用户实体来讲订单是弱实体，同理商品实体如果不存在，同样不存在订单实体，因此对商品实体而言订单是弱实体，具体如图：

ER属性扩展：这部分还挺重要的

er图的属性还细分为复合属性、多值属性和派生属性、可选属性，同时还有用来表示联系的属性，称为联系属性。

 

复合属性(composite attribute)：复合属性是指具有多个属性的组合，例如名字属性，它可以包含姓氏属性和名字属性，如下图

复合属性也有唯一属性，例如学生的所在班级属性，由于多个年级都有班级，所以单单班级属性是不唯一的，但是和年级组成的复合属性后则可以匹配成唯一属性。

多值属性（multivalued attribute）：一个实体的某个属性可以有多个不同的取值，例如一本书的分类属性，这本书有多个分类，例如科学、医学等，这个分类就是多值属性， 用双线椭圆表示。

派生属性(derivers attribute)：是非永久性存于数据库的属性。派生属性的值可以从别的属性值或其他数据（如当前日期）派生出来，用虚线椭圆表示，如下图。

下面的小组人数就是典型的派生属性，随着学生实例的参加的兴趣小组变化，小组人数属性也会变化，一般来讲派生属性不存在于数据库中，而是通过相应的公式进行计算得到，如果要放到数据库中，那么隔一段时间就要进行更新，否则会出现数据错误。

可选属性(optional attribute)：并不是所有的属性都必须有值，有些属性的可以没有值，这就是可选属性，在椭圆的文字后用（O）来表示，如下图的地址就是一个可选属性。

联系属性：联系属于用户表示多个实体之间联系所具有的属性，一般来讲M:N的两个实体的联系具有联系属性，在1:1和1：M的实体联系中联系属性并不必要。

 

2、键、函数信赖范式：

概念：

超键：在关系模式中，能唯一标识元组的属性的集。

侯选键：能唯一标识元组的属性，并且不包含多余属性的集。

主键：从候选键中任意挑选一个作为主键。

外键：如果关系R1中的某个属性不是R1的候选键而是关系R2的候选键，这个属性对于R1而言就是外键。

主键与候选键的区别，主键是被数据库设计者所选中的，相当于执政党，而候选键是在野的，相当于在野党。

函数依赖关系：关系数据库设计理论的核心是数据间的函数依赖，衡量的标准是关系规范化的程度及分解的无损连接性和保持函     数依赖性

  数据依赖是通过一个关系中属性间值的相同与否体现出来的数据间的相互关系

函数依赖（FD）是关系模式内最常见的数据依赖，属于语义范畴的概念

若在一张表中，在属性（或属性组）X的值确定的情况下，必定能确定属性Y的值，那么就可以说Y函数依赖于X，写作 X → Y

例：

学生ID，学生姓名，所修课程ID，课程名称，成绩

（学生ID，所修课程ID）→成绩

成绩既不能单独依赖于学生ID，也不能单独依赖于所修课程ID，因此成绩完全函数依赖于关键字。

（学生ID，所修课程ID）→学生姓名

学生ID→学生姓名

学生姓名可以依赖于关键字的一个主属性——学生ID，因此学生姓名部分函数依赖于（学生ID，所修课程ID）。

码：
设 K 为某表中的一个属性或属性组，若除 K 之外的所有属性都完全函数依赖于 K（这个“完全”不要漏了），那么我们称 K 为候选码，简称为码。在实际中我们通常可以理解为：假如当 K 确定的情况下，该表除 K 之外的所有属性的值也就随之确定，那么 K 就是码。一张表中可以有超过一个码。（实际应用中为了方便，通常选择其中的一个码作为主码）

非主属性
不包含在任何一个码中的属性为非主属性，反之则为主属性。

范式：

第一范式（1NF）。符合1NF的关系（你可以理解为数据表。“关系模式”和“关系”的区别，类似于面向对象程序设计中”类“与”对象“的区别。”关系“是”关系模式“的一个实例，你可以把”关系”理解为一张带数据的表，而“关系模式”是这张数据表的表结构。1NF的定义为：符合1NF的关系中的每个属性都不可再分。范式一强调数据表的原子性。

2NF在1NF的基础之上，消除了非主属性对于码的部分函数依赖。注：非主属性
包含在任何一个码中的属性成为主属性。除了主属性以外的就是非主属性。例如：对于以上例子，主属性就有两个，学号 与 课名。

第二范式是数据库规范化中所使用的一种正规形式。它的规则是要求数据表里的所有非主属性都要和该数据表的主键有完全依赖关系；如果有哪些非主属性只和主键的一部份有关的话，它就不符合第二范式。同时可以得出：如果一个数据表的主键只有单一一个字段的话，它就一定符合第二范式(前提是该数据表符合第一范式)

判断的方法是：

第一步：找出数据表中所有的码。
第二步：根据第一步所得到的码，找出所有的主属性。
第三步：数据表中，除去所有的主属性，剩下的就都是非主属性了。
第四步：查看是否存在非主属性对码的部分函数依赖。

为了符合第2范式的要求，必须消除这些部分函数依赖；只有一个办法，就是将大数据表拆分成两个或者更多个更小的数据表，在拆分的过程中，要达到更高一级范式的要求，这个过程叫做”模式分解“。模式分解的方法不是唯一的，以下是其中一种方法：

选课（学号，课名，分数）
学生（学号，姓名，系名，系主任）

我们先来判断以下，选课表与学生表，是否符合了2NF的要求？

对于选课表，其码是（学号，课名），主属性是学号和课名，非主属性是分数，学号确定，并不能唯一确定分数，课名确定，也不能唯一确定分数，所以不存在非主属性分数对于码（学号，课名）的部分函数依赖，所以此表符合2NF的要求。

对于学生表，其码是学号，主属性是学号，非主属性是姓名、系名和系主任，因为码只有一个属性，所以不可能存在非主属性对于码 的部分函数依赖，所以此表符合2NF的要求。

表4表示了模式分解以后新的数据

表4

（这里还涉及到一个如何进行模式分解才是正确的知识点，先不介绍了）

现在我们来看一下，进行同样的操作，是否还存在着之前的那些问题？

1. 李小明转系到法律系
   只需要修改一次李小明对应的系的值即可。——有改进
2. 数据冗余是否减少了？
   学生的姓名、系名与系主任，不再像之前一样重复那么多次了。——有改进
3. 删除某个系中所有的学生记录
   该系的信息仍然全部丢失。——无改进
4. 插入一个尚无学生的新系的信息。
   因为学生表的码是学号，不能为空，所以此操作不被允许。——无改进

所以说，仅仅符合2NF的要求，很多情况下还是不够的，而出现问题的原因，在于仍然存在非主属性系主任对于码学号的传递函数依赖。为了能进一步解决这些问题，我们还需要将符合2NF要求的数据表改进为符合3NF的要求。

第三范式(Third Normal Form,3rd NF)
通俗的说，第三范式（3NF） 3NF在2NF的基础之上，消除了非主属性对于码的传递函数依赖。也就是说， 如果存在非主属性对于码的传递函数依赖，则不符合3NF的要求

接下来我们看看表4中的设计，是否符合3NF的要求。

对于选课表，主码为（学号，课名），主属性为学号和课名，非主属性只有一个，为分数，不可能存在传递函数依赖，所以选课表的设计，符合3NF的要求。

对于学生表，主码为学号，主属性为学号，非主属性为姓名、系名和系主任。因为 学号 → 系名，同时 系名 → 系主任，所以存在非主属性系主任对于码学号的传递函数依赖，所以学生表的设计，不符合3NF的要求。

为了让数据表设计达到3NF，我们必须进一步进行模式分解为以下形式：
选课（学号，课名，分数）
学生（学号，姓名，系名）
系（系名，系主任）

对于选课表，符合3NF的要求，之前已经分析过了。

对于学生表，码为学号，主属性为学号，非主属性为系名，不可能存在非主属性对于码的传递函数依赖，所以符合3NF的要求。

对于系表，码为系名，主属性为系名，非主属性为系主任，不可能存在非主属性对于码的传递函数依赖（至少要有三个属性才可能存在传递函数依赖关系），所以符合3NF的要求。

新的数据表如表5

表5

 

现在我们来看一下，进行同样的操作，是否还存在着之前的那些问题？

1. 删除某个系中所有的学生记录
   该系的信息不会丢失。——有改进
2. 插入一个尚无学生的新系的信息。
   因为系表与学生表目前是独立的两张表，所以不影响。——有改进
3. 数据冗余更加少了。——有改进

 

结论
由此可见，符合3NF要求的数据库设计，基本上解决了数据冗余过大，插入异常，修改异常，删除异常的问题。当然，在实际中，往往为了性能上或者应对扩展的需要，经常 做到2NF或者1NF，但是作为数据库设计人员，至少应该知道，3NF的要求是怎样的。

注：

传递函数依赖：在关系模式R(U)中，设X，Y，Z是U的不同的属性子集，如果X确定Y、Y确定Z，且有X不包含Y，Y不确定X，（X∪Y）∩Z=空集合，则称Z传递函数依赖(transitive functional dependency) 于X。
例子：
学号|宿舍|费用
-|
062201|A| 900
062230| B |1200
062240| B| 1200
表7
学号确定宿舍、宿舍确定费用，且有学号不包含宿舍，宿舍不确定学号，符合传递函数依赖条件。

所以以上关系R存在添加异常（建了C宿舍但是没人住无法添加了）删除异常（学生062201退学了宿舍A也删除掉）如果存在传递函数依赖，如下更改:将上表拆解为两个表

非常容易忽视的知识点：冗余的判断:

如2016年下半年第53题

若给定关系模式R（U，F），其中U为关系模式R中的属性集，F是函数依赖，若U＝{A1,A2,A3,A4};F={A1->A2,A1A2->A3,A1－>A4,A2->A4},则R的主键是A1，F上的冗余是（）

A、A1->A2

B、A1A2->A3

C、A1－>A4

D、A2->A4

答：若不清楚规则，则很容易答错，理解成这样 A1->A2，A1－>A4后面又出现个A2->A4，所以认为A2->A4是冗余。这样是大错特错的。先了解判断冗余的规则：

冗余的快速判断：

重复属性组＋属性组中函数依赖＝冗余

依据：

函数依赖属性组重复，提供的是相同的信息，这些信息我们只需要保存一个就好。

所以此题正确解法是这样的。由于 A1->A2，A2->A4可以推出A1->A4，有两个A1->A4，所以A1->A4是冗余的。选C

---

 

BCNF范式

要了解 BCNF 范式，那么先看这样一个问题：

若：

1. 某公司有若干个仓库；
2. 每个仓库只能有一名管理员，一名管理员只能在一个仓库中工作；
3. 一个仓库中可以存放多种物品，一种物品也可以存放在不同的仓库中。每种物品在每个仓库中都有对应的数量。

那么关系模式 仓库（仓库名，管理员，物品名，数量） 属于哪一级范式？

答：已知函数依赖集：仓库名 → 管理员，管理员 → 仓库名，（仓库名，物品名）→ 数量
码：（管理员，物品名），（仓库名，物品名）
主属性：仓库名、管理员、物品名
非主属性：数量
∵ 不存在非主属性对码的部分函数依赖和传递函数依赖。∴ 此关系模式属于3NF。

基于此关系模式的关系（具体的数据）可能如图所示：

 

好，既然此关系模式已经属于了 3NF，那么这个关系模式是否存在问题呢？我们来看以下几种操作：

1. 先新增加一个仓库，但尚未存放任何物品，是否可以为该仓库指派管理员？——不可以，因为物品名也是主属性，根据实体完整性的要求，主属性不能为空。
2. 某仓库被清空后，需要删除所有与这个仓库相关的物品存放记录，会带来什么问题？——仓库本身与管理员的信息也被随之删除了。
3. 如果某仓库更换了管理员，会带来什么问题？——这个仓库有几条物品存放记录，就要修改多少次管理员信息。

从这里我们可以得出结论，在某些特殊情况下，即使关系模式符合 3NF 的要求，仍然存在着插入异常，修改异常与删除异常的问题，仍然不是 ”好“ 的设计。

造成此问题的原因：存在着主属性对于码的部分函数依赖与传递函数依赖。（在此例中就是存在主属性【仓库名】对于码【（管理员，物品名）】的部分函数依赖。

解决办法就是要在 3NF 的基础上消除主属性对于码的部分与传递函数依赖。

仓库（仓库名，管理员）
库存（仓库名，物品名，数量）

这样，之前的插入异常，修改异常与删除异常的问题就被解决了。

以上就是关于 BCNF 的解释。

---

无损联接分解：可以还原成原来的关系就是无损的。

首先了解一下几个概念：

1）把一个关系模式分解成若干个关系模式的过程，称为关系模式的分解。

2）把低一级的关系模式分解为若干个高一级的关系模式的方法不是唯一的。

譬如U={A,B,C}，根据不同原则（随便你自己定），

可能分成（A,B）（A,C）也可能分成（B,C）（A,C）。

3）只有能够保证分解后的关系模式与原关系模式等价，分解方法才有意义。

为了保证分解后的关系模式与原关系模式等价，我们要判定 1）分解后形成的行的关

系模式中是否为无损连接  2）是否保持函数依赖

一、无损连接的判定：

1）如果分解后的的关系模式是形如{U1，U2}这，里面只有两个，那很好做，就判断

       或 是否成立，成立的话肯定是

      无损连接。

2）如果是两个以上{U1，U2，U3....}这种，那就比较麻烦了，比如，有属性集，

ABCDEF，存在这样的函数依赖集{A->BC , CD->E , B->D , BE->F , EF->A},然后有

这样的分解{ABC , BD , BEF}。

例如：

设U1=ABC，U2=BD，U3=BEF，根据提供的函数依赖集，我们可得U1存在这样的

函数依赖A->BC，U2上的函数依赖是 B->D， U3的函数依赖是BE->F。

 

1）于是可构造这样的表格。

G	A	B	C	D	E	F
A->BC
B->D
BE->F

       
 

2）各自判断A,B,C,D,E,F是否有在G列的函数依赖中，如果有记为ai，i表示第几列，否

则记为bji，表示第j行第i列

如：

 

G	A	B	C	D	E	F
A->BC	a1	a2	a3	b14	b15	b16
B->D	b21	a2	b23	a4	b25	b26
BE->F	b31	a2	b33	b34	a5	a6

 

 

)接下来是关键的，如果我们经过一系列变换得到有一行是这样的排序

 

{a1,a2,a3,a4,a5,a6...}，即不存在bji，那我们就认为，该分解是无损连接。

 

变换过程：为了方便，我们可以按A->BC， B->D，BE->F这个顺序来（随你喜

欢）。所谓计算机中的抽象比较重要，其实就是用前人总结好的抽象方法来实现相应的算法的过程。

第一遍，根据A->BC，我们知道主健是能唯一标识一个元组的，也就是说如果

A中存在着两个属性值是相同的，毫无疑问，他们推出的BC的值肯定也是相同的。从

表格中我们遍历A列，没有发现有相同的属性组，那就跳过。

 

G	A	B	C	D	E	F
ABC	a1	a2	a3	b14	b15	b16
BD	b21	a2	b23	a4	b25	b26
BEF	b31	a2	b33	b34	a5	a6

第二遍， 根据B->D，因为B列属性值相同，那我们修改D列。修改时遵照这样的一个

规则，如果D中有ai这样的值，那么宣布修改为ai，如果没有，修改成该列的第一行的

第一个值。从表格中我们可以发现D中有a4，那么修改后变成：

G	A	B	C	D	E	F
ABC	a1	a2	a3	a4	b15	b16
BD	b21	a2	b23	a4	b25	b26
BEF	b31	a2	b33	a4	a5	a6

 

第三遍，根据BE->F，找一组BE相同的值，发现不存在，即不存在类似

G	A	B	C	D	E	F
A->BC	a1	a2	a3	b14	b15	b16
B->D	b21	a2	b23	a4	b25	b26
BE->F	b31	a2	b33	b34	a5	a6

{a1,a2,a3,a4,a5,a6}这样的序列，即该分解不是无损连接分解。

---

看一个是无损连接分解的例子：

题目：U=(A,B,C,D,E)    F={A->D,E->D,D->B,BC->D,DC->A}

判断ρ={AB,AE,CE,BCD,AC}是否为无损连接分解。

解：

先求出候选键为CE。

然后画一个初始判定表如下图所示。

G	A	B	C	D	E
AB	a1	a2	b13	b14	b15
AE	a1	b22	b23	b24	a5
CE	b31	b32	a3	b34	a5
BCD	b41	a2	a3	a4	b45
AC	a1	b52	a3	b54	b55

 

解释一下这张初始判定表的含义：

直接看ρ，比如第一个是AB，那么就在A列写a1，在B列写a2，其余都写b1j。

然后开始计算。这时候要看F中的函数依赖。

比如，第一个是A->D，看A列有没有ai，看到有a1，再看D列中对应的，如果不相同，也有aj的话，就把其他换成aj，如果没有，就以第一个ai对应的bij为基准，其他都换成bij。

所以判定表应换成：

G	A	B	C	D	E
AB	a1	a2	b13	b14	b15
AE	a1	b22	b23	b14	a5
CE	b31	b32	a3	b34	a5
BCD	b41	a2	a3	a4	b45
AC	a1	b52	a3	b14	b55

接下来看E->D：

以前面修改后的表为基准。

G	A	B	C	D	E
AB	a1	a2	b13	b14	b15
AE	a1	b22	b23	b14	a5
CE	b31	b32	a3	b14	a5
BCD	b41	a2	a3	a4	b45
AC	a1	b52	a3	b14	b55

D->B：

这里看D列，因为b14对应B列有a2，所以就不以D列中a4为基准，而是以b14为基准，改B列对应位置为a2。

G	A	B	C	D	E
AB	a1	a2	b13	b14	b15
AE	a1	a2	b23	b14	a5
CE	b31	a2	a3	b14	a5
BCD	b41	a2	a3	a4	b45
AC	a1	a2	a3	b14	b55

BC->D：

当左边出现两个元素时，看B列和C列相同的行，发现是a2、a3，然后修改D列对应位置为a4。

G	A	B	C	D	E
AB	a1	a2	b13	b14	b15
AE	a1	a2	b23	b14	a5
CE	b31	a2	a3	a4	a5
BCD	b41	a2	a3	a4	b45
AC	a1	a2	a3	a4	b55

DC->A：

D列和C列有相同的行，是a3、a4，然后修改A列对应位置为a1。

 

G	A	B	C	D	E
AB	a1	a2	b13	b14	b15
AE	a1	a2	b23	b14	a5
CE	a1	a2	a3	a4	a5
BCD	a1	a2	a3	a4	b45
AC	a1	a2	a3	a4	b55

再看表中全部为a的行，发现是第三行，所以ρ为无损连接分解。

 

---

二、是否保持函数依赖？

 

这个的判断方法就比较简单了，还是这道题，有属性集，ABCDEF，存在这样

的函数依赖集{A->BC , CD->E , B->D , BE->F , EF->A},然后有这样的分解

{ABC , BD , BEF}。

设U1=ABC，A->BC，U2=BD，B->D ，U3=BEF，BE->F ，即我们不能推出 CD->E 

，EF->A，所以也不具有保持函数依赖的特性。
 

---

依据的规则就是函数依赖。

 

3、关系代数及元组演算

关系代数是一种抽象的查询语言，是一种代数的符号。

并U：两个表理论上的联合。

差－：计算两个表差别的集合。R1－S1代表在R1中但不在S1里的元素集合。

笛卡尔积X：RXS代表R中元素R＋S 中的S元素之积。

投影：记为：ΠA(R)。 
其中A是属性名（即列名）表，R是表名。

选择σ：选择运算是选择关系中行的子集。如σ Sdept=‘IS’(Student)查询信息系（IS系）全体学生

交操作：取两个关系中相同的部分。

联接⋈：通过共同的属性联接两个表。

一个元组演算表达式 {t|i(t)}表示使i（t）为真的元组。

4、SQL语言

看之前关于《数据库原理与应用》的描述