数据库三范式和BCNF范式的理解：生动举例

通过生动形象的例子理解三大范式、BCNF范式以及反范式化，简单易懂。
本文用生活中易于理解的概念，直观地描述这些范式和它们的应用场景，同时也给出了相应的实际数据库案例。

目录

• 范式的概念
• 1. 第一范式（1NF）
• • 1.1 类比举例：
  • 1.2 真实举例：
• 2. 第二范式（2NF）
• • 2.1 类比举例：
  • 2.2 真实举例：
• 3. 第三范式（3NF）
• • 3.1 类比举例：
  • 3.2 真实举例：
• 4. BC范式（BCNF）
• • 4.1 类比举例：
  • 4.2 真实举例：
• 5. 反范式化
• • 5.1 类比举例：
  • 5.2 真实举例：

范式的概念

首先，什么是范式，范式的英文是 Normal Form，其实就是标准形式的意思，就是说数据库就该设计成这样，这就是数据库的标准形式（当然，并不总是要遵守，有些时候可能需要反范式化）。

除了范式的定义，还需要先理解以下几个概念：

• 属性： 把数据库中的一个表类比成一个公司，那么公司里的每个人都是一个“属性”（表中的一个字段视为一个属性），不管老板还是员工，只要是公司里的人，就都是一个属性。
• 主键： 老板就是“主键”，所有普通员工都得听他的（主键可以唯一地标识属性元组）。但是，老板未必是一个人，可能在法律上我和我兄弟同时是公司的老板（主键可能是多个属性的组合）。要指挥员工，需要我们俩一起指挥才行（主键中所有属性的组合才能唯一地标识属性元组）。
• 候选键： 公司不是独裁制的，老板这个位置有很多候选人，他们都有能力指挥所有普通员工（候选键都可以唯一标识属性元组），这些候选人就是“候选键”，但是所有候选人里只能选出一个做老板（主键是一个被选中的候选键），老板是特殊的候选人。当然，候选人可能也其实是几个人的组合，比如另外几个兄弟的组合，但他们也得一起行动才有能力指挥员工（候选键中所有属性的组合才能唯一地标识属性元组）。
• 超键： 有候选人（包括老板）的一堆人就是“超键”（能唯⼀标识元组的属性集）。当然这堆人也可以只包含候选人或老板，这样也叫“超键”。
• 主属性： 是候选人或老板的人（或是候选人组合中的一个）就是“主属性”（候选键中的属性称为主属性）。
• 非主属性： 不是候选人或老板的人（也不在任何候选人组合中）就是“非主属性”（不属于任何候选码的属性称为非主属性）。
• 外键： 公司里某个人（或某几个人的组合）是另一个公司的老板，那他或他们就是“外键”（一个表中存在的另一个表的主键称为此表的外键）。

这几个概念的正式定义是这样的：

• 属性（attribute）： 数据库中的字段，即数据库中表的列。
• 超键（super key）： 在关系中能唯⼀标识元组的属性集称为关系模式的超键
• 候选键（candidate key）： 不含有多余属性的超键称为候选键
• 主键（primary key）： ⽤户选作元组标识的⼀个候选键称为主键
• 主属性： 候选键中的属性称为主属性。
• 非主属性： 不属于任何候选键的属性称为非主属性。
• 外键（foreign key）： 在一个表中存在的另一个表的主键称为此表的外键。

注： 前面的类比定义中对几个概念的定义是乱序的，但这样更易于理解。

有了这几个概念和它们的类比，就可以开始描述三大范式、BC范式和反范式化的概念了。

1. 第一范式（1NF）

除非连体婴儿，否则各干各的：公司里的员工应该都是不可再分的，不是他们的肉体不可再分，而是他们的作用应该不可再分。
（列要符合原子性，即表中的每个属性都不可再分）

1.1 类比举例：

公司里有两个人，他们是搭档，一起面试一起进的公司，一个叫A ，一个叫B，他们说他俩不管干什么都得一起才行。
结果有一天，老板叫他俩去办公室，要给他俩安排一项工作，他俩一起去了之后发现这个工作其实只需要 A 自己就能做，并不需要 B 帮忙，于是 B 白跑一趟。
这就不满足第一范式了，显然 A 和 B 两个人是“可再分”的，不应该作为一个永不分离的组合存在。

这时候该怎么解决呢？
很简单，把 A 和 B 拆开变成两个员工就行了。

1.2 真实举例：

数据库中有如下一张表（其中主键为 姓名 ）

需求是经常要查询每个人所在的省，注意，往往只需要知道省份，不需要知道详细地址。
但是每次查询都需要把整个详细地址取出来，再进行划分，从而得到需要的省份信息。
这就像是把 A 和 B 同时叫到办公室再让 B 回去，无疑是一种浪费。

因此，应该把省份、城市、区分开存储，避免多余的查询。

2. 第二范式（2NF）

一山不容二虎：如果老板是多个人，他们的权力必须是同等的，不能有些员工只听一个老板的，而不用听另一个老板的。
（非主属性必须依赖于整个主键或候选键，不能只依赖于主键或候选键的一部分属性）

2.1 类比举例：

我和我兄弟同时是公司的老板，我俩本应该共同指挥员工，但是有几个员工只听我兄弟的，我根本管不着；同时又有几个员工只听我的，也不用听我兄弟的。
那这样我和我兄弟的权利就不是同等的了，公司就会出现分裂。

怎么解决呢？
一山不容二虎，我和我兄弟应该分家，听他话的员工跟他走，听我话的员工跟我走，变成两个公司。当然，我俩也不能彻底断了联系。

2.2 真实举例：

数据库中有如下一张表（其中主键为 员工ID 和 部门ID 的组合 ）

虽然 员工在该部门中的工作年数 需要 员工ID 和 部门ID 组合在一起才能确定，但是 姓名 和 年龄 只由 员工ID 就可以确定；而部门名称 和 部门地址 只由 部门ID 就可以确定。
这样数据的存储就是冗余的，增删改查也可能会出现很多麻烦的问题。

因此，应该把这张表拆分成如下三张表：
员工 表

部门 表

员工-部门 表

这样一来，即避免了数据冗余，又不会让员工与其所在的部门失去链接关系。

3. 第三范式（3NF）

不能架空老板：公司中不能有一个员工在听命于老板的同时，还听命于另一个员工。
（任何非主属性不能依赖于其他非主属性）

3.1 类比举例：

我作为公司的老板，指挥着公司的 100 个员工，可是居然有几个员工也同时听从着某一个员工的指挥。
这个员工成了中间管理者，让员工们不知道到底听老板的还是听他的，这是老板所不能接受的。

这时候怎么解决呢？
既然这个员工这么喜欢管人，那我干脆直接封他为“小组长”，让他去管理这些员工。
我则不再去管他们了，而是只管这个小组长就行了。

3.2 真实举例：

数据库中有如下一张表（其中主键为 员工ID ）

显然，职位名称 不仅可以由 员工ID 决定，也可以由 职位ID 决定，这造成了数据存储的冗余。

因此，应该把这张表拆分成如下两张表：
员工 表

职位 表

4. BC范式（BCNF）

在野者不能干政：老板不能受到候选人的制约。
（如果在关系R中，U为主键，A属性是主键的一个属性，若存在A->Y，Y为主属性，则该关系不属于BCNF）

4.1 类比举例：

还是我和我兄弟同时是公司的老板，当时我们两人一起从众多候选人中被选中做了老板。
但是我发现我兄弟仍然和一个候选人有着千丝万缕的联系，他总是会受到那个候选人的影响，让我感觉那个候选人在“垂帘听政”。

怎么解决？
我不管理这个候选人了，也不能让他再影响到公司，我直接让他离开公司，然后让兄弟跟他私下保持联系。

4.2 真实举例：

数据库中有如下一张表（其中主键为 仓库名 和 物品名 的组合，同时 管理员 和 物品名 的组合是候选键 ）

显然，管理员的名字可以决定仓库名，那么这个候选键中的主属性就影响到主键中的属性了。
主键中的主属性对于候选键是部分依赖关系，这可能导致插入、删除和更新数据时产生异常。

因此，应该把这张表拆分成如下两张表：
仓库 表

仓库-管理员 表

5. 反范式化

尽信书不如无书：有时候，一味地追求符合范式，可能会导致数据库在查询时的时间开销很大、效率很低。
（反范式是一种对范式化设计的数据库的性能优化策略，通过在表中增加冗余或重复的数据来提供数据库的读取性能）

5.1 类比举例：

我是公司的老板，我手下有一个小组长，他的手下又有一些员工。
根据第三范式，我不直接管理小组长手下的员工，而是只跟小组长对接工作。
但是我实际上总是需要看小组长手下的一个员工的工作报告，但是我又不能直接跟他沟通，每次还都需要让小组长做中介，让那个员工先把报告给小组长，小组长再转给我，这很没有效率。

怎么解决这一问题呢？
我决定还是直接管理那个员工，虽然这样让我和小组长在同时管理他，不符合公司的规矩，但是确实有效地提高了我们的工作效率。

5.2 真实举例：

数据库中有如下一张表（它们的主键分别为 员工ID 和 部门ID）
员工 表

部门 表

如果在实际需求中要频繁地查询某个员工所在的部门的名称，比如调用如下 SQL 语句

select employee_id,department_namefrom employees e join departments d on e.department_id = d.department_id;

那么每次都需要进行两个表的连接操作，会浪费大量时间资源和计算资源。

因此，可以在员工表中增加一个冗余的字段 部门名称 ，这样每次的查询就可以直接获取所需信息，而不用进行连接操作了。
员工 表