人人都能看懂的数据库六大范式

本篇文章带大家回顾一下数据库的六大范式，最重要的就是用例子说话。

第一范式（1NF）

重点强调数据表中每个字段的值必须具有原子性，主要体现在以下几点：

• 不允许使用行顺序传达信息

• 不允许在同一列使用混合数据类型

• 不允许没有主键的表格

• 不允许重复的数据组

我们以一个实际例子来看，假如有一个学生表（学号，姓名，年级，专业）：

学号	姓名	年级	专业
20220010101	张三	2022	软件工程
20220010202	赵四	2022	软件工程
20230020101	王五	2023	网络安全

1、不允许使用行顺序传达信息

数据表的行本身只有插入数据，没有逻辑顺序，我们无法根据“身高”给学生进行排序。如果想要按照某个属性排序，必须记录到数据表中，比如因为学生表有学号列，所以我们可以按照学号给学生进行排序

2、不允许在同一列使用混合数据类型

“亲爱的同学们”（咬牙切齿）在填写问卷星或者在线表格的时候，可能是这么填的：

学号	姓名	年级	专业
20220010101	张三	2022级	软件工程
20220010202	赵四	2022	软件工程
20230020101	王五	2023	网络安全

“张三”同学顶风作案、特立独行，年级就是写的跟别人不一样。这在在线表格里面这么填没问题，但是在数据表里，如果我们规定年级列是整型数据的话，这么填是不被允许的，即同一列的数据类型不能既是类型A又是类型B

3、不允许没有主键的表格

“张三”同学不仅乱填还粗心大意，忘记自己已经填过了，在最后面又填了一次：

学号	姓名	年级	专业
20220010101	张三	2022级	软件工程
20220010202	赵四	2022	软件工程
20230020101	王五	2023	网络安全
…	…	…	…
20220010101	张三	2022	软工

如果在数据库中，我们以学号作为主键的话，这样写入数据也是不被允许的，是不符合第一范式的

4、不允许重复的数据组

“张三”同学多次顶风作案，痛定思痛，决定辅修一门“法学”专业，提高一下自己的法律意识，于是他在表格中这么写：

学号	姓名	年级	专业
20220010101	张三	2022	软件工程,法学
20220010202	赵四	2022	软件工程
20230020101	王五	2023	网络安全

而这也是有问题的，他把专业列当成了多值列。那么，在数据库中，我们应该怎么设计表格呢？这样吗：

学号	姓名	年级	专业1	专业2
20220010101	张三	2022	软件工程	法学
20220010202	赵四	2022	软件工程
20230020101	王五	2023	网络安全

显然也不是的，这样其他同学的专业2是空的，而且万一有时间管理大师“三修”，又要再加一列。实际上，我们会这么设计：

学生表

学号	姓名	年级
20220010101	张三	2022
20220010202	赵四	2022
20230020101	王五	2023

学生专业表

学号	专业
20220010101	软件工程
20220010101	法学
20220010202	软件工程
20230020101	网络安全

第二范式（2NF）

在第一范式基础上，要求非主属性完全依赖于主键（非主属性不能部分依赖于主键）。

首先我们理清楚几个概念：

• 候选码/候选键：可以唯一标识一条数据的一个或一组属性
• 主码/主键：从候选码/候选键中任意选择一个
• 主属性：候选码/候选键所有属性的并集

学号	姓名	年级	专业	课程编号	课程名称
20220010101	张三	2022级	软件工程	1	数据库基础原理
20220010202	赵四	2022	软件工程	1	数据库基础原理
20230020101	王五	2023	网络安全	2	Web安全

对于以上这个表，候选码、主码和主属性都是{学号，课程编号}，而非主属性，例如姓名，依赖于学号，记作{学号} -> {姓名}，也就是姓名部分依赖于主键，而不是完全依赖于主键，所以这个关系表并不符合数据库第二范式。那么怎么改呢？拆表：

• 学生表（学号，姓名，年级，专业）

• 课程表（课程编号，课程名称）

• 学生课程表（学号，课程编号）

第三范式（3NF）

在第二范式基础上，要求不能存在传递依赖（A->B->C。其中，A是主键，C是非主键，且B->A不成立）。或者说，要求非主属性完全、直接且仅依赖于主键

考试成绩出来了，老师给同学们的成绩做了统计，并规定了成绩等级：

• 不及格：0-59
• 及格：60-75
• 良好：76-90
• 优秀：90以上

学号	课程编号	成绩	等级
20220010101	1	60	及格
20220010202	1	96	优秀
20230020101	2	80	良好

该关系的候选键只有一个，就是{学号，课程编号}，因此同时也是唯一主键，而非主属性是{成绩，等级}。

依赖关系是{学号，课程编号} -> {成绩，等级}，同时我们很容易发现还有一个依赖关系，就是{成绩} -> {等级}。因此，存在传递依赖关系{学号，课程编号} -> {成绩} -> {等级}，因此不符合第三范式。那么，如何设计关系呢？

我们可以设计一个百分制的成绩等级关联表，存储成绩和等级的关系，为所有课程成绩提供等级查询：

成绩等级关联表

成绩	等级
0	不及格
1	不及格
…	…
59	不及格
60	及格
…	…
75	及格
76	良好
…	…
90	良好
91	优秀
…	…
100	优秀

学生成绩表

学号	课程编号	成绩
20220010101	1	60
20220010202	1	96
20230020101	2	80

这样，我们只需要使用连表查询就可以得到结果：

SELECT 学生成绩表.学号, 学生成绩表.课程编号, 学生成绩表.成绩, 成绩登记表.等级
FROM 学生成绩表, 成绩等级表
WHERE 学生成绩表.成绩 = 成绩等级表.成绩

并且，录入成绩的时候也不再需要将等级一同录入了，也就大大减少了数据冗余

BCNF（3.5范式）

第三范式的非主属性忽略“非主”，也就是所有属性都完全、直接且仅依赖于主键

学号	课程编号	成绩	身份证
20220010101	1	60	xxx
20220010202	1	96	xxx
20230020101	2	80	xxx

候选键有：{学号，课程编号}、{身份证，课程编号}

依赖关系：

• {学号，课程编号} -> {成绩}
• {身份证，课程编号} -> {成绩}
• {学号} -> {身份证}
• {身份证} -> {学号}

根据BC范式的定义，身份证号也必须完全、直接且仅依赖于不包含它的主键，也就是{学号，课程编号}，而这里明显是不满足的，也就是不符合BC范式。因此，我们的学生成绩表中学生的唯一标识只能保留一个（学号或身份证号），这样才满足BC范式。其实这也是BC范式为什么是3.5范式的原因，它只是对第三范式的一种增强，或者说“FIX BUG”也可以，并不存在新的使用场景

第四范式（4NF）

消除非平凡非函数依赖的多值依赖。换句话说，表中允许的多值依赖只能是对键的多值依赖

多值依赖：如果属性集x可以得到多个属性集y，那么就说x对y是多值依赖的，记作x->>y。例如，一个学生参加了多个社团。

非平凡的多值依赖：所谓“非平凡的”，是“平凡的”的相反。如果存在x->>y，平凡的多值依赖有两种情况：1、y是x的一部分；2、x和y的全集互补相等。y的全集互补指的是这个表中不属于y的其他列的集合。x和y的全集互补相等意味着除了主键x外就只有一列了。

非函数依赖的多值依赖：“非函数依赖”也是相对于“函数依赖”的。如果属性集x对另外一个属性集y存在依赖，但不是函数依赖（函数依赖指的是x能通过函数计算推导出y），那么就是“非函数依赖”

第四范式没有那么好理解，我们同样举个例子。有一家服装店，他们卖不同款式的衣服，每个衣服有不同的颜色和图案，定义的衣服样式表如下：

款式	颜色	图案
A	黑色	小猫
A	黑色	小狗
A	绿色	小猫
A	绿色	小狗
B	红色	小羊
B	蓝色	小羊

这里的候选键和主键都是唯一的：{款式}

如果我们现在要给款式A加一个小羊图案，那么就需要插入两条数据（黑色和绿色）。这就会出问题。根据第四范式的理论知识，我们梳理一下多值依赖关系：

• {款式} ->> {颜色}
• {款式} ->> {图案}

我们会发现，任何一组多值依赖关系都是非平凡（颜色的全集互补{款式，图案}不等于{款式}）且非函数依赖（款式和颜色/图案没有函数逻辑关系）的，所以不符合第四范式。那么，怎样才能符合第四范式呢？拆表！

款式颜色表

款式	颜色
A	黑色
A	绿色
B	红色
B	蓝色

款式图案表

款式	图案
A	小猫
A	小狗
B	小羊

第五范式（5NF）

也叫投影-连接范式（PJNF）和域/键范式（DKNF），它针对的是数据库中的连接依赖。它要求任何一个能通过投影和连接操作得到的表都不能添加到数据库中。简单地说，如果一张表能够通过多张表连接而成，那么它就应该被拆分成多张表

一个很常见的例子就是每个人可能会有多个电话号码：

用户	号码
张三	001-123456
张三	001-123455
张三	002-123456
李四	001-654321
李四	003-543210

该表符合第四范式。但是，电话号码的格式为“区号+号码”（实际的电话号码比这还要复杂），如果没有把区号和号码拆分开来，也会导致大量的数据冗余，并且根据定义来讲，这是不符合第五范式的，因此，我们继续——拆表！

• 用户表（用户id，用户名，…）

• 区号表（区号id，区号，地区）

• 号码表（号码id，区号id，号码）

• 用户号码表（用户id，号码id）

我们只需要使用数据库连接语句就可以把数据连接起来

SELECT 用户表.用户名, concat(区号表.区号, "-", 号码表.号码)
FROM 用户表, 区号表, 号码表, 用户号码表
WHERE (
    用户表.用户id = 用户号码表.用户id AND
    区号表.区号id = 电话表.区号id AND
    号码表.号码id = 用户号码表.号码id
)

总结

至此，我们完成了数据库六大范式的讲解。范式为我们提供了数据库设计的工程蓝图，保障了数据的安全和完整，避免数据的冗余。但是，如果不结合实际使用的话，便是无根之木、无源之水。在我们的实际使用中，实际上很少涉及到第四第五范式，并且很多时候我们需要反范式设计，因为绝对的数据安全和不冗余，会带来大量的联表查询，而实际业务场景下我们是需要性能的，需要用“空间”换“时间”，还要综合考虑数据库的性能压力，所以实际工作中会有很多反范式的设计。那么，我们下期就来讲讲工作中的那些“反范式设计”以及深入剖析这么做的原因！