数据库圣经--表的设计 范式

从需求到数据库表的设计流程

1. 从需求中提取类（实体）
   • 在面向对象分析与设计（OOAD）中，我们从需求中识别出关键概念，将其抽象为“类”。
   • 每个类对应数据库中的一个“实体”，也就是一张表。
   • 类的属性（成员变量）对应表中的字段（列）。
2. 确定类与类之间的关系
   • 类之间的关系（如一对一、一对多、多对多）决定了表之间的关联方式。
   • 例如：
     • 一对多：在“多”的一方表中添加外键指向“一”的一方。
     • 多对多：需要中间表（关联表）来存储两个表的主键作为外键。
3. 使用 SQL 创建表
   • 根据设计好的实体和关系，编写 CREATE TABLE 语句来建表。
   • 定义主键、外键、字段类型、约束等。

一、范式

1、什么是范式

数据库的范式是⼀组规则。在设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式。

关系数据库有六种范式：第⼀范式（1NF）、第⼆范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式（4NF）和第五范式（5NF，又称完美范式），越高的范式数据库冗余越小。然而，我们普遍认为范式越高虽然对数据关系有更好的约束性，但也可能导致数据库IO更繁忙，因此在实际应用中，数据库设计通常只需满足第三范式即可。

2、第一范式

2.1定义

数据库表的每⼀列都是不可分割的原子数据项，而不能是集合，数组，对象等非原子数据。

在关系型数据库的设计中，满足第⼀范式是对关系模式的基本要求。不满足第⼀范式的数据库就不能被称为关系数据库

2.2示例

定义⼀个学生表，需要记学生信息和学校信息

2.2.1反例

学校是一个对象，可以继续进行拆分，所以不满足第一范式

2.2.2正例

学校信息包含在一行中，每⼀列都不能再进行拆分，此时已满足第一范式

在关系型数据库中，每一列都可以用基本数据类型表示，天然满足第一范式

3、第二范式

3.1定义 在满足第一范式的基础上，不存在非关键字段对任意候选键的部分函数依赖。存在于表中定义了复合主键的情况下。

• 关键字段：构成 “候选键（包括主键）” 的字段（用于唯一标识一条记录的核心字段）；
• 非关键字段：不参与候选键构成的字段（仅存储业务信息）；
• 部分函数依赖：非关键字段只依赖候选键（通常是复合主键）中的某一个关键字段，而非整个候选键。

由于两个或多个关键字段决定一个一条记录的情况，有些非关键字段只与部分关键字段有关系，那么它存在部份依赖关系。

表中没有复合主键的（只有一个主键的），那么这种表天然满足第二范式

候选键：

可以唯⼀标识⼀行数据的列或列的组合，可以从候选键中选⼀个或多个当做表的主键

非关键字段：

不属于候选键的列

注意：

候选键和主键不是完全等同的概念—— 主键属于候选键的 “子集”，候选键是 “可选的主键集合”，主键是从候选键中选中并实际使用的那个。

一、核心关系

• 候选键：一个表中所有能唯一标识记录的字段（或字段组合），可以有多个；
• 主键：从表的多个候选键中，选择其中一个作为 “主要的唯一标识”，是实际用于表内唯一标识、表间关联的候选键。

二、示例（以book表为例）

假设book表有两个字段：

• BID（图书编号）：唯一不重复；
• ISBN（国际标准书号）：唯一不重复。

此时：

• BID和ISBN都是候选键（两者都能单独唯一标识一本图书）；
• 我们选择BID作为主键（实际在表中用BID做唯一标识、关联borrow表的外键）；
• ISBN则是 “未被选中的候选键”，仅作为备选的唯一标识。

3.2示例

需求：学生可以选修课程，课程有对应的学分，学生考试后每门课程会产生相应的成绩

3.2.1反例

用一张表记录所有信息

这张表中使用学号+课程名定义复合主键来唯⼀标识⼀个学生某门课程的成绩，这也是这张表的主要作用。

学生是通过学号来确定的，学⽣的姓名、年龄和性别和课程没有关系，即学生的信息只依赖学号， 不依赖课程名；学分是通过课程来确定的，课程的学分与学生没有关系，即学分只依赖课程名，不依赖学生。

对于使用复合主键的表，如果⼀行数据中的有些列只与复合主键中的⼀个或其中几个列有关系，那么就说他存在部分函数依赖，也就不满足第⼆范式。

3.2.2正例

设计表：针对需求应该设计三张表，即：学生表、课程表和成绩表

第⼆范式强调的是部分函数依赖，当⼀张表中的主键只有⼀列时，天然满足第⼆范式

注意：复合主键

1. 候选键的定义：能唯一标识表中一条记录的字段（或字段组合），需满足「唯一性」（值不重复）和「最小性」（不能冗余字段）；
2. 复合主键的定义：由两个或多个字段组合而成的主键，核心要求是「组合后能唯一标识记录」（满足唯一性），且「去掉任意一个字段后就无法唯一标识」（满足最小性）；
3. 结论：复合主键完全符合候选键的定义，是「候选键集合中被选中作为 “主要标识” 的那个复合候选键」。

示例验证（沿用之前的场景）

以 订单明细表 为例：

• 复合候选键：订单ID+商品ID（组合唯一，且去掉任一字段后无法唯一标识一条订单商品记录）；
• 若将 订单ID+商品ID 设为表的主键（即复合主键），则它依然是候选键 —— 只是从 “候选键集合” 中被选中成为了 “主键”。

再比如 学生选课表：

• 复合候选键可能有两个：学生ID+课程ID、学号+课程编号；
• 若选择 学生ID+课程ID 作为复合主键，则它是候选键，另一个 学号+课程编号 是 “未被选中的复合候选键”。

总结：非关键字段只依赖「任意一个候选键（无论是否是主键）的组合中的某一个字段」（而非整个组合）—— 只要满足这个条件，就属于部分依赖，违反第二范式（2NF）。

3.3不满足第二范式时可能出现的问题

1：数据冗余（最根本的问题）

是什么？

同一业务信息被重复存储在多条记录中，导致表数据量膨胀、存储空间浪费。

为什么会造成？

根源是「部分依赖」：非关键字段仅依赖复合候选键的「单个字段」，而非整个组合。这意味着，只要这个 “单个字段” 的值不变，对应的非关键字段值就会重复出现在所有相关记录中。

示例（不满足 2NF 的学生选课表）：

• 冗余表现：“张三”（学生姓名）重复存储了 3 次（只要学生 ID=101，姓名就固定）；
• 根源：学生姓名仅依赖 “学生 ID”（复合候选键的一部分），而非 “学生 ID + 课程 ID” 整个组合 —— 同一学生选多门课，姓名就必须重复。

二、核心影响 2：更新异常（修改数据麻烦，易出错）

是什么？

修改某条冗余信息时，需要修改表中所有包含该信息的记录，否则会导致数据不一致；若漏改，会出现 “同一信息前后矛盾”。

为什么会造成？

因为冗余信息存储在多条记录中，而非集中存储在一处 —— 部分依赖让 “同一信息绑定到单个关键字段”，但该关键字段对应多条记录，修改时无法 “一键同步”。

示例延续：

若学生 101 改名为 “张三峰”，由于 “学生姓名” 在 3 条记录中都有存储，需要手动修改这 3 条记录的 “学生姓名” 字段：

• 若只改了前 2 条，第 3 条仍为 “张三”→ 出现 “学生 101 既叫张三峰，又叫张三” 的矛盾；
• 若记录数量庞大（比如学生选了 100 门课），修改成本极高，且极易漏改。

三、核心影响 3：插入异常（无法正常插入必要数据）

是什么？

想插入某类业务数据时，因 “复合候选键的部分字段缺失”，导致无法插入 —— 即使该数据本身是完整的。

为什么会造成？

不满足 2NF 的表中，复合候选键是 “唯一标识记录的依据”，插入记录时必须填写完整的复合候选键（否则无法唯一标识）；但部分依赖的非关键字段，本可以独立于复合候选键的其他字段存在，却被强制绑定。

示例延续：

若学校新增一门课程 “Python 基础”（课程 ID=204，课程名称 = Python 基础），但暂时没有学生选课：

• 想插入课程信息时，必须填写 “学生 ID + 课程 ID” 完整复合候选键，但此时没有学生 ID（无学生选课）→ 无法插入课程信息；
• 根源：课程名称仅依赖 “课程 ID”（部分依赖），本应独立存储，但被强制绑定到 “学生 ID + 课程 ID” 的复合候选键中 —— 没有学生 ID，就无法插入课程信息。

四、核心影响 4：删除异常（删除无用数据时，误删必要数据）

是什么？

删除某条冗余记录时，会连带删除 “部分依赖的非关键字段信息”—— 即使该信息仍需保留（其他记录可能需要用到）。

为什么会造成？

部分依赖的非关键字段，与复合候选键的 “单个字段” 绑定，但该字段的信息仅存储在关联的记录中，没有独立的存储载体；删除关联记录，就等于删除了该字段的信息。

示例延续：

若学生 101 毕业了，需要删除他的所有选课记录（3 条记录）：

• 删除这 3 条记录后，“学生 101 = 张三” 的关联信息也被一并删除→ 若后续想查询 “学生 101 是谁”，表中已无数据；
• 根源：学生姓名仅依赖 “学生 ID”，但没有独立的 “学生表” 存储该信息，只能依附于选课记录 —— 删除选课记录，就等于删除了学生的姓名信息。

4、第三范式

4、第三范式

4.1定义

在满足第⼆范式的基础上，不存在非关键字段，对任一候选键的传递依赖。

消除 “传递依赖”—— 非关键字段只能直接依赖于候选键，不能通过其他中间字段间接依赖。

例子：

• 不满足 3NF：存在传递依赖 ——学号 → 系别 → 系主任（“系主任” 不直接依赖学号，而是通过 “系别” 间接依赖）。

4.2示例

要求学生表中记录学生所属的学院，在满足第⼆范式的基础上对学生表做出修改

4.2.1反例

因为是要描述学生信息，并且在表中定义了Id为主键，Id可以明确的标识每条学生信息

在这个表结构中，可以看出学生的学号、姓名、年龄、性别与主键Id强相关；学院电话、学院地址与学院强相关；在⼀个表中出现了两个强相关的关系，而且这两个强相关关系又存在传递现象，即通过学生Id可以找到学生记录，学生记录中包含学院名，每个学院又有自己的电话和地址

这种传递现象称为传递依赖，所以当前的表不满足第三范式

4.2.2正例

把学院信息拆分出来定义学院表，学生表与学院表做关联

此时所有表设计满足第三范式

二、数据库设计过程 1、从现实业务中抽象得到概念类

概念类是从现实世界中抽象出来的，在需求分析阶段就需要确定下来：

类对应了数据库设计中的实体，实体对应了数据库中的表

类中的属性对应实体中的属性，实体的属性对应了表中的列

2、确定实体与实体之间的关系，并画出E-R画，方便项目参与人员理解与沟通

3、根据E-R图完成SQL语句的编号并创建数据库

三、实体-关系图 实体-关系图（Entity-RelationshipDiagram）简称E-R图，也称作实体联系模型、实体关系模型，是一种用于描述数据模型的概念图，主要用于数据库设计阶段。

1、E-R图的基本组成 E-R图包含了以下三种基本成分：

实体：即数据对象，用矩形框表示，如用户、学生、班级等。

属性：实体的特性，用椭圆形或圆⻆矩形表示，如学生的姓名、年龄等。

关系：实体之间的联系，用菱形框表示，并标明关系的类型，并用直线将相关实体与关系连接起来。

2、关系的类型 2.1一对一关系 (1:1) 一个用户实体包含的属性有：用户昵称，真实姓名，手机号，邮箱地址，性别，学校

一个账户实体包含的属性有：登录用户名，密码

用户实体与账户实体是一对一的关系，用E-R图表示如下：

2.2一个对多关系 (1:N) 一个学生实体包含的属性有：真实姓名，学号，年龄，性别，入学时间

一个班级实体包含的属性有：班级名，学生人数

一个班级中有多个学生，所以班级实体与学生实体是一对多的关系，反过来说学生实体与班级实体是多对一的关系，用E-R图表示如下：

2.3多对多关系 (M:N) 一个学生实体包含的属性有：真实姓名，学号，年龄，性别，入学时间

一个课程实体包含的属性有：课程名

一个学生可以选修多门课程，一门课程也可以被多名学生所选择，所以学生与课程之间是多对多关系，用E-R图表示如下：

对于多对多关系，可以使用中间表进行记录，比如⼀个学生参加了某一门课程的考试得到了相应的成绩，用E-R图表示如下：