数据库建表范式学习

数据库范式

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

基本概念

侯选关键字：又叫侯选码，惟一标识一行数据，其真子集不能是侯选关键字，一个表可以存在多个侯选关键字，如用户表的username，userid。
主关键字：又叫主键，主码，被选中的用来区分其它行的侯选关键字，一个表只有一个主关键字。
部分依赖：(A,B)->C,D,如A->C,则C部分依赖A。
传递依赖：A->B->C,则C传递依赖A。
属性：实体所具有的某一特性，属性一开始是个逻辑概念，在关系数据库中，属性又是个物理概念，属性可以看作是“表的一列”。
元组：表中的一行就是一个元组。
分量：元组的某个属性值。在一个关系数据库中，它是一个操作原子。
码：表中可以唯一确定一个元组的某个属性（或者属性组），如果这样的码有不止一个，那么大家都叫 候选码，我们从候选码中挑一个出来做老大，它就叫主码。
全码：如果一个码包含了所有的属性，这个码就是全码。

第一范式（1NF）

• 定义
  数据库的字段是单一属性，不可再分。

• 要求

  1. 不能是复合属性，如果存在，应该拆分为多个属性
  2. 不能是多值属性，如果存在，应该建立一个实体，而让此属性与其存在1对多的关系)
  3. 不能是重复属性

• 概述
  确保每列保持原子性，原子性意味着无法继续拆分，数据库表中的所有字段值都应是不可分解的原子值。
  即：每个表应该有唯一标识每一行的主键。

第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。
原文：https://blog.csdn.net/kenhins/article/details/51084815

第二范式（2NF）

• 定义
  任何非关键字段不能部分依赖任一侯选关键字（即必须完全依赖）。

• 要求

  1. 表中必须存在侯选关键字，即每一行不同于其他任一行，是惟一区分的
  2. 任何非关键字段不能依赖于侯选关键字的一部分

• 概述
  确保表中的每列都和主键相关，第二范式在满足了第一范式的基础上，确保表中的每列都和主键相关，而不是和主键（联合主键）的一部分相关。若表中某几个字段只是和联合主键的某部分相关，应将这几个字段拿出以联合主键的部分为主键另建新表。2NF在1NF的基础上消除了部分依赖。
  即：在联合主键的情况下，非主键部分不应该依赖于部分主键

• 实例
  假定选课关系表为SelectCourse(学号，姓名，年龄，课程名称,成绩，学分)，关键字为组合关键字(学号，课程名称)，因为存在如下决定关系：
  (学号，课程名称) → (姓名，年龄，成绩，学分)

学号	课程名称	姓名	年龄	成绩	学分

这个数据库表不满足第二范式，因为存在如下决定关系：

1. 学分只和课程相关
   (课程名称) → (学分)
2. 姓名、年龄、成绩只和学号相关
   (学号) → (姓名，年龄)

即存在组合关键字中的字段决定非关键字的情况。

由于不符合2NF，这个选课关系表会存在如下问题：

1. 数据冗余：
   同一门课程由n个学生选修，"学分"就重复n-1次；同一个学生选修了m门课程，姓名和年龄就重复了m-1次。
2. 更新异常：
   若调整了某门课程的学分，数据表中所有行的"学分"值都要更新，否则会出现同一门课程学分不同的情况。
3. 插入异常：
   假设要开设一门新的课程，暂时还没有人选修。这样，由于还没有"学号"关键字，课程名称和学分也无法记录入数据库。
4. 删除异常：
   假设一批学生已经完成课程的选修，这些选修记录就应该从数据库表中删除。但是，与此同时，课程名称和学分信息也被删除了。很显然，这也会导致插入异常。

把选课关系表SelectCourse改为如下三个表：

1. 学生表：Student(学号,姓名，年龄)

学号	姓名	年龄

2. 课程表：Course(课程名称，学分)

课程名称	学分

3. 选课关系：SelectCourse(学号，课程名称，成绩)

学号	课程名称	成绩

这样的数据库表是符合第二范式的， 消除了数据冗余、更新异常、插入异常和删除异常。
另外，所有单关键字的数据库表都符合第二范式，因为不可能存在组合关键字。

第三范式（3NF）

• 定义
  任何非关键字段不能传递依赖任一侯选关键字

• 要求

  1. 非关键字字段必须直接依赖任一侯选关键字
  2. 非关键字段C不能依赖非侯选关键字B，因为样会形成传递依赖：侯选关键字A=>B=>C，因为这时的B往往是外键，即其他表的主键，也就是说表中不能含有其他表的非主属性

• 概述
  确保表中的每列都和主键直接相关，而不是间接相关。第三范式是第二范式的一个子集，即满足第三范式必须满足第二范式。第三范式在第二范式的基础上消除了传递依赖，即任何非主键之间不应该有依赖关系。

• 实例
  假定学生关系表为Student(学号，姓名，年龄，所在学院,学院地点，学院电话)，主键为"学号"，因为存在如下决定关系：
  (学号) → (姓名，年龄，所在学院，学院地点，学院电话)
  这个数据库是符合2NF的，但是不符合3NF，因为存在如下决定关系：
  (学号) → (所在学院) → (学院地点，学院电话)
  即存在非主键字段"学院地点"、"学院电话"对主键字段"学号"的传递依赖。
  它也会存在数据冗余、更新异常、插入异常和删除异常的情况，读者可自行分析得知。
  把学生关系表分为如下两个表：

1. 学生：(学号，姓名，年龄，所在学院)；

学号	姓名	年龄	所在学院

2. 学院：(学院，地点，电话)

学院	地点	电话

这样的数据库表是符合第三范式的，消除了数据冗余、更新异常、插入异常和删除异常。

巴斯-科德范式（BCNF）

• 定义
  任何字段都不能传递依赖任一侯选关键字

• 要求

  1. 与第三范式相比，一个是“任何非关键字段不能”，一个是“任何字段不能”，显然更严格了
  2. 侯选关键字或其部分字段不能传递依赖其他的侯选关关键字

• 概述
  在第三范式基础上，联合主键的各字段之间互不依赖。BC范式是对第三范式的修正补充，是指在第三范式的基础上进一步消除主属性对于码的部分函数依赖和传递依赖。BCNF需要符合3NF，并且，主属性不依赖于主属性。

• 实例1
  假设仓库管理关系表为StorehouseManage(仓库ID,存储物品ID,管理员ID,数量)，且有一个管理员只在一个仓库工作；一个仓库可以存储多种物品。这个数据库表中存在如下决定关系：
  (仓库ID,存储物品ID) →(管理员ID,数量)
  (管理员ID,存储物品ID) → (仓库ID,数量)
  所以，(仓库ID,存储物品ID)和(管理员ID,存储物品ID)都是StorehouseManage的候选关键字，表中的唯一非关键字段为数量，它是符合第三范式的。但是，由于存在如下决定关系：
  (仓库ID) → (管理员ID)
  (管理员ID) → (仓库ID)
  即存在关键字段决定关键字段的情况，所以其不符合BCNF范式。它会出现如下异常情况：

  1. 删除异常：
     当仓库被清空后，所有"存储物品ID"和"数量"信息被删除的同时，"仓库ID"和"管理员ID"信息也被删除了。
  2. 插入异常：
     当仓库没有存储任何物品时，无法给仓库分配管理员。
  3. 更新异常：
     如果仓库换了管理员，则表中所有行的管理员ID都要修改。

把仓库管理关系表分解为二个关系表：

1. 仓库管理：StorehouseManage(仓库ID,管理员ID)

仓库ID	管理员ID

2. 仓库：Storehouse(仓库ID,存储物品ID,数量)

仓库ID	存储物品ID	数量

这样的数据库表是符合BCNF范式的，消除了删除异常、插入异常和更新异常。

• 实例2

有一个配件管理表WPE(WNO,PNO,ENO,QNT),其中WNO表示仓库号，PNO表示配件号，ENO表示职工号，QNT表示数量。

有以下约束要求：

1. 一个仓库有多名职工；
2. 一个职工仅在一个仓库工作；
3. 每个仓库里一种型号的配件由专人负责，但一个人可以管理几种配件；
4. 同一种型号的配件可以分放在几个仓库中。

分析表中的函数依赖关系，可以得到：

1. ENO->WNO;
2. （WNO，PNO）->QNT
3. （WNO，PNO）->ENO
4. （ENO，PNO）->QNT

可以看到，候选键有：（ENO,PNO）;(WNO,PNO)。所以，ENO,PNO,WNO均为主属性，QNT为非主属性。显然，非主属性是直接依赖于候选键的。所以此表满足第三范式。
而我们观察一下主属性：（WNO,PNO）->ENO;ENO->WNO。显然WNO对于候选键（WNO,PNO）存在传递依赖，所以不符合BCNF。
解决这个问题的办法是分拆为两个表：
管理表EP（ENO，PNO，QNT）；

职工号	配件号	数量

工作表EW（ENO，WNO）。

职工号	仓库号

但这样做会导致函数依赖（WNO,PNO）->ENO丢失。
虽然，不满足BCNF会导致一些冗余和一致性的问题。但是，将表分解成满足BCNF的表又可能丢失一些函数依赖。所以，一般情况下不会强制要求关系表要满足BCNF。

第四范式（4NF）

• 定义
  非主属性不应该有多值
  已经是BC范式，并且不包含多值依赖关系

• 要求

  1. 已经是BC范式
  2. 不包含多值依赖关系

• 概述
  限制关系模式的属性间不允许有非平凡且非函数依赖的多值依赖。第四范式在BC范式的基础上消除了多值依赖。

• 实例1
  用户联系方式表（用户id，固定电话，移动电话），其中用户id是主键，这个满足了BCNF,但是一个用户有可能会有多个固定电话或者多个移动电话，那么这种设计就不合理，应该改为（用户id，联系方式类型，电话号码）。

• 实例2
  比如我们建立课程教师和教材的模型，我们规定，每门课程有对应的一组教师，每门课程也有对应的一组教材，一门课程使用的教程和教师没有关系。这样我们首先肯定有三个实体表，分别表示课程，教师和教材。现在我们要建立这三个对象的关系，于是我们建立的关系表，定义如下：
  课程ID，教师ID，教程ID；这三列作为联合主键。
  以下是示例，为了表述方便，我们用Name代替ID，这样更容易看懂：

Course	Teacher	Book
英语	Bill	人教版英语
英语	Bill	美版英语
英语	Bill	美版英语
英语	Jay	美版英语
高数	William	人教版高数
高数	Dave	美版高数

这个表除了主键，就没有其他字段了，所以肯定满足BC范式，但是却存在多值依赖导致的异常。

我们先来看看多值依赖的定义：

一个关系，至少存在三个属性（A、B、C），才能存在这种关系。对于每一个A值，有一组确定的B值和C值，并且这组B的值独立于这组C的值。

假如我们下学期想采用一本新的英版高数教材，但是还没确定具体哪个老师来教，那么我们就无法在这个表中维护Course高数和Book英版高数教材的的关系。

解决办法是我们把这个多值依赖的表拆解成2个表，分别建立关系。这是我们拆分后的表：

Course	Teacher
英语	Bill
英语	Jay
高数	William
高数	Dave

Course	Book
英语	人教版英语
英语	美版英语
高数	人教版高数
高数	美版高数

第五范式（5NF）

• 要求
  1. 必须满足第四范式
  2. 表必须可以分解为较小的表，除非那些表在逻辑上拥有与原始表相同的主键
• 概述
  第五范式是在第四范式的基础上做的进一步规范化。第四范式处理的是相互独立的多值情况，而第五范式则处理相互依赖的多值情况。

参考链接
https://baike.baidu.com/item/%E6%95%B0%E6%8D%AE%E5%BA%93%E8%8C%83%E5%BC%8F/7309898?fr=aladdin
https://blog.csdn.net/kenhins/article/details/51084815
https://blog.csdn.net/Dove_Knowledge/article/details/71434960?utm_source=blogxgwz0
https://yq.aliyun.com/ziliao/495680
https://baijiahao.baidu.com/s?id=1683424856311376285&wfr=spider&for=pc
https://www.cnblogs.com/studyzy/p/5823224.html