数据库范式设计

古城微笑少年丶 2023-09-24 12:31 145阅读 0赞

导语数据库的设计范式数据表中的键从 1NF 到 3NFBCNF(巴斯范式)

在日常工作中，我们都需要遵守一定的规范，比如签到大卡、审批流程等，这些规范虽然有一定等约束感觉，却是非常有必要等，这样可以保证正确性和严谨性，但有些情况下，约束反而会带来效率的下降，比如一个可以直接操作的任务，却需要审批才能执行。

我们在设计关系型数据库模型的时候，需要对关系内部各个属性之间联系对合理化程度进行定义，这就有了不同等级的规范要求，这些规范要求被称为范式 (NF) 。可以理解为 ，一张表的设计结构需要满足某种设计标准目前关系型数据库一共有6种范式，按照范式级别，从低到高分别是 ：1NF(第一范式)、2NF(第二范式)、3NF(第三范式)、BCNF (巴斯 - 科德范式)、4NF(第四范式)和 5NF (第五范式，又叫完美范式)数据库的范式设计越高阶，冗余度就越低，同时高阶的范式一定符合低阶范式的要求，比如满足 2NF 的一定满足 1NF ，满足 3NF的一定满足 2NF ，以此类推一般来说数据表的设计应尽量满足 3NF 。但也不绝对，有时候为了提高某些查询功能，我们嗨需要破坏范式规则，也就是反规范化。

![数据库范式设计\_数据库范式设计][f7bdde9d204d00a2be4af92be91262d4.png]

范式的定义会使用到主键和侯选键(因为主键和候选键可以唯一标识元组)，数据库中的键 (key)由一个或者多个属性组成。我总结了下数据表中常用的几种键和属性的定义：

超键 ：能唯一标识元组的属性集叫做超键候选键：如果超键不包括多余的属性，那么这个上海超键就是候选键主键：用户可以从候选键中选择一个作为主键外键：如果数据表R1中某属性集不是R1的主键，而是另一个数据表R2的主键，那么这个属性集就是数据表R1的外键主属性：包含在任一候选键中的属性为主属性非主属性：与主属性相对，指的是不包含在任何一个候选键中的属性

我们之前用过 NBA 的球员表 (player)和 球队表 (team)。这里我可以把球员表定义为包含 球员编号、姓名、身份证号、年龄和球队编号 球队表包含球队编号、主教练和球队所在地。

球员表:

名称字段球员编号id姓名name身份证号card年龄age球队编号team\_id

球队表:

名称字段球队编号id主教练coach地址address

对于球员表来说，超键就是包括球员编号或者身份证号的任意组合，比如(球员编号)(球员编号，姓名)(身份证号，年龄)等。

候选键就是最小的超键，对于球员表来说，候选键就是(球员编号)或者(身份证号)。

主键是我们自己选定，也就是从候选键中选择一个，比如(球员编号)。

外键就是球员表中的球队编号。

在 player 表中，主属性是(球员编号)(身份证号)，其他的属性(姓名)(年龄)(球队编号)都是非主属性。

1NF 指的是数据库中的任何属性都是原子性的，不可再分。这很好理解，我们在设计某个字段的时候，对于字段 X 来说，就不能把字段 X 拆分成字段 X-1 和 X-2 。事实上，任何的DBMS都会满足第一范式的要求2NF 指的数据表里的非主属性都要和这个数据表的候选键又完全依赖关系。所谓完全依赖不同于部分依赖，也就是不能仅依赖候选键的一部分属性，而必须依赖全部属性

比如说我们设计的球员表 player\_game , 里面包含球员编号、姓名、年龄、比赛编号、比赛时间 和

场地等属性，这里候选键和主键都为 (球员编号，比赛编号)，

上面这个关系说明球员编号和比赛编号的组合决定了球员的姓名、年龄、比赛时间、比赛地点和该比赛的得分数据。但是这个数据表不满足第二范式，因为数据表中的字段之间还存在着如下的对应关系：

也就是说候选键中的某个字段决定了非主属性。你也可以理解为，对于非主属性来说，并非完全依赖候选键。这样会产生怎样的问题呢？

为了避免出现上述的情况，我们可以把球员比赛表设计为下面的三张表。

这样的话，每张数据表都符合第二范式，也就避免了异常情况的发生。某种程度上 2NF 是对 1NF 原子性的升级。1NF 告诉我们字段属性需要是原子性的，而 2NF 告诉我们一张表就是一个独立的对象，也就是说一张表只表达一个意思。

3NF 在满足 2NF 的同时，对任何非主属性都不传递依赖于候选键。也就是说不能存在非主属性 A 依赖于非主属性 B，非主属性 B 依赖于候选键的情况。

我们用球员 player 表举例子，这张表包含的属性包括球员编号、姓名、球队名称和球队主教练。现在，我们把属性之间的依赖关系画出来，如下图所示：

![数据库范式设计\_数据库范式设计\_02][02]

你能看到球员编号决定了球队名称，同时球队名称决定了球队主教练，非主属性球队主教练就会传递依赖于球员编号，因此不符合 3NF 的要求。

如果要达到 3NF 的要求，需要把数据表拆成下面这样：

总结：1NF 需要保证表中每个属性都保持原子性；2NF 需要保证表中的非主属性与候选键完全依赖；3NF

需要保证表中的非主属性与候选键不存在传递依赖。

![数据库范式设计\_数据库范式设计\_03][03]

如果数据表的关系模式符合 3NF 的要求，就不存在问题了吗？我们来看下这张仓库管理关系 warehouse\_keeper 表：

![数据库范式设计\_数据库范式设计\_04][04]

在这个数据表中，一个仓库只有一个管理员，同时一个管理员也只管理一个仓库。我们先来梳理下这些属性之间的依赖关系。

仓库名决定了管理员，管理员也决定了仓库名，同时(仓库名，物品名)的属性集合可以决定数量这个属性。

这样，我们就可以找到数据表的候选键是(管理员，物品名)和(仓库名，物品名)，

然后我们从候选键中选择一个作为主键，比如(仓库名，物品名)。

在这里，主属性是包含在任一候选键中的属性，也就是仓库名，管理员和物品名。非主属性是数量这个属性。

首先，数据表每个属性都是原子性的，符合 1NF 的要求；其次，数据表中非主属性”数量“都与候选键全部依赖，(仓库名，物品名)决定数量，(管理员，物品名)决定数量，因此，数据表符合 2NF 的要求；最后，数据表中的非主属性，不传递依赖于候选键。因此符合 3NF 的要求。存在问题 ：

增加一个仓库，但是还没有存放任何物品。根据数据表实体完整性的要求，主键不能有空值，因此会出现插入异常；如果仓库更换了管理员，我们就可能会修改数据表中的多条记录；如果仓库里的商品都卖空了，那么此时仓库名称和相应的管理员名称也会随之被删除。

即使符合3NF的要求，同样可能存在插入，更新和删除数据的异常情况

解决 ：

首先我们需要确认造成异常的原因：主属性仓库名对于候选键(管理员，物品名)是部分依赖的关系，这样就有可能导致上面的异常情况。人们在 3NF 的基础上进行了改进，提出了BCNF，也叫做巴斯 - 科德范式，它在 3NF 的基础上消除了主属性对候选键的部分依赖或者传递依赖关系。

根据 BCNF 的要求，我们需要把仓库管理关系 warehouse\_keeper 表拆分成下面这样：

个人博客：http://blog.yanxiaolong.cn/

[f7bdde9d204d00a2be4af92be91262d4.png]: https://img-blog.csdnimg.cn/img_convert/f7bdde9d204d00a2be4af92be91262d4.png
[02]: https://img-blog.csdnimg.cn/img_convert/61b1097609456a0bb70d069f1d48e4bb.png
[03]: https://img-blog.csdnimg.cn/img_convert/02158fc27efe5d6dd2d3e3bf833a5aa6.png
[04]: https://img-blog.csdnimg.cn/img_convert/586dc526b34710c8d04c24f3cfc35bbf.png