数据库建表范式学习

落日映苍穹つ 2022-05-15 15:21 311阅读 0赞

## 数据库范式 ##

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。  
目前关系数据库有六种范式：第一范式（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][https_blog.csdn.net_kenhins_article_details_51084815]

## 第二范式（2NF） ##

*  定义  
    任何非关键字段不能部分依赖任一侯选关键字（即必须完全依赖）。
 *  要求
    
    1.  表中必须存在侯选关键字，即每一行不同于其他任一行，是惟一区分的
    2.  任何非关键字段不能依赖于侯选关键字的一部分
 *  概述  
    **确保表中的每列都和主键相关**，第二范式在满足了第一范式的基础上，确保表中的每列都和主键相关，而不是和主键（联合主键）的一部分相关。若表中某几个字段只是和联合主键的某部分相关，应将这几个字段拿出以联合主键的部分为主键另建新表。
 *  实例  
    假定选课关系表为SelectCourse(学号，姓名，年龄，课程名称,成绩，学分)，关键字为组合关键字(学号，课程名称)，因为存在如下决定关系：  
    (学号，课程名称) → (姓名，年龄，成绩，学分)

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

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

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

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

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

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

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

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

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

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

## 第三范式（3NF） ##

*  定义  
    任何非关键字段不能传递依赖任一侯选关键字
 *  要求
    
    1.  非关键字字段必须直接依赖任一侯选关键字
    2.  非关键字段C不能依赖非侯选关键字B，因为样会形成传递依赖：侯选关键字A=>B=>C，因为这时的B往往是外键，即其他表的主键，也就是说表中不能含有其他表的非主属性
 *  概述  
    **确保表中的每列都和主键直接相关，而不是间接相关**。第三范式是第二范式的一个子集，即满足第三范式必须满足第二范式。第三范式在第二范式的基础上消除了**传递依赖**，即任何非主键属性不应依赖于其他非主键属性。
 *  实例  
    假定学生关系表为Student(学号，姓名，年龄，所在学院,学院地点，学院电话)，主键为"学号"，因为存在如下决定关系：  
    (学号) → (姓名，年龄，所在学院，学院地点，学院电话)  
    这个数据库是符合2NF的，但是不符合3NF，因为存在如下决定关系：  
    (学号) → (所在学院) → (学院地点，学院电话)  
    即存在非主键字段"学院地点"、"学院电话"对主键字段"学号"的**传递依赖**。  
    它也会存在数据冗余、更新异常、插入异常和删除异常的情况，读者可自行分析得知。  
    把学生关系表分为如下两个表：

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

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

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

## 巴斯-科德范式（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)

<table> 
 <thead> 
  <tr> 
   <th>仓库ID</th> 
   <th>管理员ID</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td></td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

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

<table> 
 <thead> 
  <tr> 
   <th>仓库ID</th> 
   <th>存储物品ID</th> 
   <th>数量</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

这样的数据库表是符合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。

> 参考链接  
> [https://baike.baidu.com/item/数据库范式/7309898?fr=aladdin][https_baike.baidu.com_item_7309898_fr_aladdin]  
> [https://blog.csdn.net/kenhins/article/details/51084815][https_blog.csdn.net_kenhins_article_details_51084815]  
> [https://blog.csdn.net/Dove\_Knowledge/article/details/71434960?utm\_source=blogxgwz0][https_blog.csdn.net_Dove_Knowledge_article_details_71434960_utm_source_blogxgwz0]  
> [https://yq.aliyun.com/ziliao/495680][https_yq.aliyun.com_ziliao_495680]

[https_blog.csdn.net_kenhins_article_details_51084815]: https://blog.csdn.net/kenhins/article/details/51084815
[https_baike.baidu.com_item_7309898_fr_aladdin]: 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_Dove_Knowledge_article_details_71434960_utm_source_blogxgwz0]: https://blog.csdn.net/Dove_Knowledge/article/details/71434960?utm_source=blogxgwz0
[https_yq.aliyun.com_ziliao_495680]: https://yq.aliyun.com/ziliao/495680