数据库建表和三大范式

ER 图

ER 图也称为实体-联系图，提供了标示实体类型，属性，和联系的方法，用来描述现实世界的概念模型

例子：

E-R图的绘制规范

只要有四个成分：

1、矩形框

表示实体，在框中计入实体名 （实际问题中客观存在的并且可以相互区别的事物称为实体）

2、菱形框

表示联系，在框中记入联系名。

椭圆形框

表示实体或联系的属性，将属性名记入框中。对于主属性名，则在其名称下划一下划线。

连线

实体与属性之间；实体与联系之间；联系与属性之间用直线相连，并在直线上标注联系的类型。（对于一对一联系，要在两个实体连线方向各写1； 对于一对多联系，要在一的一方写1，多的一方写N；对于多对多关系，则要在两个实体连线方向各写N，M。)

E-R模型转换为关系模式

实体集向关系模式的转换

一般转换遵循的原则
实体集的转换规则：一个实体型转换为一个关系模式。实体的属性就是关系的属性，实体的码就的码。
例：学生实体可以转换为如下关系模式：
学生（学号，姓名，性别，出生日期，所在系，年级）

两个实体型之间的联系集向关系模式的转换

1：1联系的转换方法

1:1联系可以转换为一个独立的关系模式，也可以与任意一端对应的关系模式合并。

如果转换为一个独立的关系模式，则与该联系相连的各实体的主码以及联系本身的属性均转换为关系的属性，每个实体的主码均是该关系的候选码。

如果与某一端对应的关系模式合并，则需要在该关系模式的属性中加入另一个关系模式的主码和联系本身的属性。
例子：

1:n联系的转换方法

1:n联系可以转换为一个独立的关系模式，也可以与n端对应的关系模式合并。

若转换为一个独立的关系模式，则与该联系相连的各实体的主码以及联系本身的属性均转换为关系的属性，而关系的主码为n端实体的主码。

若与n端关系模式合并，则在n端实体集中增加新属性，新属性由联系对应的1端实体集的主码和联系自身的属性构成，而关系模式的主码不变。

例子：

m:n联系的转换方法

与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性。而关系的码为各实体码的组合。

同一实体型之间的联系集向关系模式的转换

v同一实体型的实体间的联系即自联系，也可按上述1:1、1:n和m:n三种情况分别处理。

1：n

m:n

多实体型之间的联系向关系模式的转换

一般地，两个以上的实体型间也存在着1:1、1:n和m:n三种情况，转换方法一般遵循以下原则：

1：n的多元联系：修改1端实体集对应的关系，即将与联系相关的其他实体集的码和联系自身的属性作为新属性加入到1端实体集中。
m:n的多元联系：新建一个独立的关系，多元联系相连的各实体的码以及联系本身的属性均转换为关系的属性。而关系的码为各实体码的组合

弱实体集向关系模式的转换

强实体集和弱实体集的概念与存在依赖密切相关，强实体集的成员必然是支配实体，而弱实体集的成员是从属实体。
由于弱实体不能独立存在，它必须依附于一个所有者实体，因此将弱实体集转换成关系模式时，弱实体所对应的关系中必须包含所有者实体的主码。

说明

我们定义一张表，并添加一些数据，这个表 1 （选课表）有助于我们理解这些概念：

学号	姓名	系名	系主任	班级	选课	分数
0001	章三	历史系	李三阳	一年3班	高等数学	60
0002	样老二	历史系	李三阳	一年4班	计算机基础	80
0003	风旗	计算机系	赵飞	一年8班	计算机基础	80
0004	李显	计算机系	赵飞	一年8班	计算机基础	80

码

关系中的某个或者某几个属性的集合，用于唯一地标识每一条数据（这里的每一条数据就是数据库中的每一条记录）。

请注意：码可以是一个或多个属性，一个表中可能存在多个码。例如，{学号，选课}这两个属性组成的码可以唯一地确定数据库的条项。

候选码

在一个表的关系中，可以存在多个关系集合用于唯一确定一条记录，这些多个集合就称为候选码，也称为候选键。候选码可以存在多个，每一个候选码都可以唯一地确定一条记录。

我们换一种更加严谨的说法：假设 K 为某个表中的一个属性或者属性组，如果除去 K 之外的所有属性都完全函数依赖于 K，那么我们就称 K 为候选码。

根据上表的示例我们可以得出一个候选码：
（学号，课名）

主码

通常我们会从候选码中选择一个码作为主码，也就是我们通常所说的主键。

函数依赖

在数学上的解释是：y = f(x)，输入一个 X，可以得到一个确定的 Y。
对应到一个表上就是，在属性X 确定的情况下，必定能够确定某个属性 Y 的值，这就能够称作 Y 函数依赖于 X，写作 X -> Y 。
注意：X未必是一个属性，也可能是属性集合。
记作：

X -> Y

比如下面这些关系都存在函数依赖：
（学号）->（姓名）.
（学号，课名）->（分数）.
（系名）->（系主任）.

但是，下面这些关系就不存在函数依赖：
（姓名）->（学号），因为有可能会出现重名的情况，所以只依靠姓名是无法确定学号的。
（学号）->（分数），因为一个学号有多个科目，每一个科目都存在一个分数，不能只靠学号来确定分数。
…….

完全函数依赖

在一个表中，如果存在 X -> Y，那么对于 X 下的任何一个真子集（X’），X’ -> Y都不成立，那么我们就说 Y 对于 X 完成函数依赖。
如果X可以唯一确定Y，并且找不到X中的更少的属性可以唯一确定Y，那么称之为完全函数依赖。
记作：
X ->F Y

通俗的说，必须通过码中的所有属性才可以唯一确定一个值。比如：
（学号）->F（姓名）.
（学号，课名）->F（分数)

部分函数依赖

在一个表中，如果存在 X -> Y，但是 Y 并不完成依赖于 X。存在一些 X 的子集 X’，X‘ -> Y成立，那么我们就说 Y 对于X 部分函数依赖。
记作：
X ->P Y

通俗的说，只需要码中的部分属性即可唯一确定一个值。比如：
（学号，课名）-> （姓名），只需要根据码中的学号即可唯一确定姓名。
它跟完全函数依赖的区别在于，完全函数依赖必须要通过码中的所有属性才可以唯一确定一个值，而部分函数依赖只需要码中的部分属性即可。

传递函数依赖

如果 Z 函数依赖于 Y，Y 函数依赖于 X，并且 X 不函数依赖于 Y，那么我们就说 Z 传递函数依赖于 X。
记作：

X ->T Z

通俗的说，通过码可以唯一确定一个属性，然后通过该属性可以唯一确定另一个属性，所以就演变为了可以通过码唯一确定一个无函数依赖的属性。

属性

属性就是我们在表中定义的每一个列。

主属性

在码中的所有属性（每一列）都称为主属性

非主属性

除了主属性之外的其他属性，都称为非主属性。

范式的作用

数据库范式作用是什么。数据库范式是符合某一种设计要求的数据库设计结构的集合，这种设计结果与应用程序的实际情况有关，有利于数据库设计优化，保证数据库表中数据的完整性和一致性，并且能够加速查询执行

关系数据库六种范式

第一范式（1NF）、
第二范式（2NF）、
第三范式（3NF）、
巴斯-科德范式（BCNF）、
第四范式(4NF）、
第五范式（5NF，又称完美范式）。
而通常我们用的最多的就是：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）。

第一范式（1NF）

要求数据库表的每一列都是不可分割的原子数据项
例如：

学号	姓名	学生信息
0001	张三	硕士 研一
0002	李四	本科 大一

分析，学生信息中，不满足原子性，不满足第一范式

调整为

学号	姓名	学历	所在年级
0001	张三	硕士	研一
0002	李四	本科	大一

第二范式：

第二范式在第一范式的基础上，所有的非主键字段，完全依赖主键，不能产生部分依赖。容易产生冗余数据

例如：

订单号	产品号	产品数量	产品折扣	产品价格	订单金额	订单时间
20201011	204	100	7.8	500	4000	2022-10-14
20201012	290	100	7.8	500	4000	2022-10-14
20201012	230	100	7.8	500	4000	2022-10-14
20201013	400	100	7.8	500	4000	2022-10-14

在上图所示的情况中，同一个订单中可能包含不同的产品，因此主键必须是 “订单号” 和 “产品号” 联合组成，
但可以发现，产品数量、产品折扣、产品价格与 “订单号” 和 “产品号” 都相关，但是订单金额和订单时间仅与 “订单号” 相关，与 “产品号” 无关，
这样就不满足第二范式的要求，调整如下，需分成两个表：

订单号	产品号	产品数量	产品折扣	产品价格
20201011	204	100	7.8	500
20201012	290	100	7.8	500
20201012	230	100	7.8	500
20201013	400	100	7.8	500

订单号	订单金额	订单时间
20201011	4000	2022-10-14
20201012	4000	2022-10-14
20201012	4000	2022-10-14
20201013	4000	2022-10-14

第三范式：

在第二大范式的基础上，所有非主键字段直接依赖主键，不能产生传递依赖
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关
举例说明：

学号	姓名	性别	家庭人口	班主任姓名	班主任性别	班主任年龄
0001	里斯	男	3口人	张南风	男	29
0002	王飞	男	4口人	张南风	男	29
0003	宋丽丽	女	3口人	陈洁	女	25
0004	宋小非	女	6口人	陈晨	男	24

所有属性都完全依赖于学号，所以满足第二范式，但是 “班主任性别” 和 “班主任年龄” 直接依赖的是 “班主任姓名”，而不是主键 “学号”，所以需做如下调整：

学号	姓名	性别	家庭人口
0001	里斯	男	3口人
0002	王飞	男	4口人
0003	宋丽丽	女	3口人
0004	宋小非	女	6口人

班主任姓名	班主任性别	班主任年龄
张南风	男	29
张南风	男	29
陈洁	女	25
陈晨	男	24

参考资料

1、https://zhuanlan.zhihu.com/p/375068363
2、https://www.zhihu.com/question/321279262/answer/1401307563
3、https://blog.csdn.net/kupepoem/article/details/123799084
4、复合主键 https://www.php.cn/mysql-tutorials-418852.html