数据库表设计五大范式所解决的问题

上学时学得《数据库系统概念》，一致似懂非懂，停留在定义和证明层面。最近在做项目，认真的了解了下数据库的范式问题，只有潜意识懂得了其原理和应用场合才能较快设计出合理的表。

 

首先，明确概念如下：

主码　　　　也就是主键

候选码 　　 若关系中的某一属性组的值能唯一的标识一个元组,而其任何真子集都不能再标识,则称该属性组为候选码。候选码不唯一，主码是其中一个而已。

主属性 　　 包含在任一候选关键字中的属性称主属性

 

其次，也是本文重头戏，结合例子，讲一下各大范式对前者的改进和应用场景。

 

范式在现实中解决的问题

 

1、数据冗余

 

   所谓数据冗余，是指一个表中的一个或多个属性发生改变的时候，会出现多个或更多的属性的数据重复出现。也就是说，属性与属性之间、属性与属性组之间或者属性组与属性组之间存在着一对多或者多对多的数据关系。如下表：

 

课程C 教员T 参考书B
------------------------
物 理 李 勇 普通物理学 
物 理 李 勇 光学原理 
物 理 李 勇 物理习题集 
物 理 王 军 普通物理学 
物 理 王 军 光学原理 
物 理 王 军 物理习题集 
数 学 李 勇 数学分析 
数 学 李 勇 微分方程 
数 学 李 勇 高等代数 
数 学 张 平 数学分析 
数 学 张 平 微分方程 
数 学 张 平 高等代数 
. . .
2、插入异常

 

   所谓插入异常，是指某个属性，特别是主键，为空，则尽管其他的属性有数据，也无法插入。

 

   假定选课关系表为SelectCourse(学号，姓名，年龄，课程名称,成绩，学分)，关键字为组合关键字(学号，课程名称)，即(学号，课程名称) → (姓名，年龄，成绩，学分) 。同时其存在一下联系：

 

　　(课程名称) → (学分)

 

　　(学号) → (姓名，年龄)

 

   对于这个表SelectCourse，假设要开设一门新的课程，暂时还没有人选修。这样，由于还没有"学号"关键字，课程名称和学分也无法记录入数据库。

 

3、删除异常

 

   所谓删除异常，是指由于某个原因，需要删除表中的某些没用的属性，但是同时也会把其他的一些有用的属性的数据也同时删除掉。

 

   同样是对于上面的表SelectCourse，当学生毕业时，需要从系统里面删除掉毕业学生的所有信息，同时也会把课程的信息也一并删除掉。

 

4、更新异常

 

   所谓更新异常，是指如果某个属性的数据发生改变的时候，就需要对应属性修改很多的数据。

 

   同样是对于上面的表SelectCourse，若调整了某门课程的学分，数据表中所有行的"学分"值都要更新，否则会出现同一门课程学分不同的情况。

 

   总之，这些属性的数据之间存在的这些问题，一来可能会占用过多的空间，影响查询的效率，二来也会为后面的编程带来不必要的麻烦，三来还会影响数据库的完整性。为此，有必要对这些表格进行范式的优化。

 

 

 

二、六大范式的说明

 

总的范式优化图：

 

　   消除决定因素

 

    | 1NF

 

　　非码的非平凡 | ↓ 消除非主属性对码的部分函数依赖

 

　　函数依赖

 

    | 2NF

 

　　| ↓ 消除非主属性对码的传递函数依赖

 

　　| 3NF

 

　　| ↓ 消除主属性对码的部分和传递函数依赖

 

　　| BCNF

 

　　| ↓ 消除非平凡且非函数依赖的多值依赖

 

　　| 4NF

 

　　| ↓消除不是由候选码所蕴含的连接依赖

 

　　| 5NF

 

1.1NF（第一范式）

 

  所谓第一范式，是指表中的每一个属性都是不可再分的，也就是不能一列再分成两列。这是关系数据库的基础。

 

 

 

2.2NF（第二范式）

 

  所谓第二范式，是指所有的非主属性都完全依赖于关键字。从这个定义可以看出，第二范式不存在非主属性对于部分候选关键字的部分依赖，不过允许非主属性之间存在着传递依赖。

 

  下面是第二范式的优化实例：

 

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

 

　　(学号，课程名称) → (姓名，年龄，成绩，学分)

 

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

 

　　(课程名称) → (学分)

 

　　(学号) → (姓名，年龄)

 

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

 

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

 

　　(1) 数据冗余：

 

　　同一门课程由n个学生选修，"学分"就重复n-1次；同一个学生选修了m门课程，姓名和年龄就重复了m-1次。

 

　　(2) 更新异常：

 

　　若调整了某门课程的学分，数据表中所有行的"学分"值都要更新，否则会出现同一门课程学分不同的情况。

 

　　(3) 插入异常：

 

　　假设要开设一门新的课程，暂时还没有人选修。这样，由于还没有"学号"关键字，课程名称和学分也无法记录入数据库。

 

　　(4) 删除异常：

 

　　假设一批学生已经完成课程的选修，这些选修记录就应该从数据库表中删除。但是，与此同时，课程名称和学分信息也被删除了。很显然，这也会导致插入异常。

 

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

 

　　学生：Student(学号,姓名，年龄)；

 

　　课程：Course(课程名称，学分)；

 

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

 

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

 

　　另外，所有单关键字的数据库表都符合第二范式，因为不可能存在组合关键字。

 

3、第三范式（3NF）

 

   所谓第三范式，是指每一个非主属性既不部分依赖于也不传递依赖于关键字，也就是在第二范式的基础上消除传递依赖（A＞B＞C）。

 

   假定学生关系表为Student(学号，姓名，年龄，所在学院,学院地点，学院电话)，关键字为单一关键字"学号"，因为存在如下决定关系：

 

　　(学号) → (姓名，年龄，所在学院，学院地点，学院电话)

 

　　这个数据库是符合2NF的，但是不符合3NF，因为存在如下决定关系：

 

　　(学号) → (所在学院) → (学院地点，学院电话)

 

　　即存在非关键字段"学院地点"、"学院电话"对关键字段"学号"的传递函数依赖。

 

　　它也会存在数据冗余、更新异常、插入异常和删除异常的情况，读者可自行分析得知。

 

　　把学生关系表分为如下两个表：

 

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

 

　　学院：(学院，地点，电话)。

 

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

 

4、BCNF

 

   所谓BCNF，是指在第三范式的基础上进一步消除主属性对于码的部分函数依赖和传递依赖。

 

    假设仓库管理关系表为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都要修改。

 

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

 

　　仓库管理：StorehouseManage(仓库ID,管理员ID)；

 

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

 

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

 

    又如，有这样一个配件管理表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。

 

5、4NF（第四范式）

 

   对于第四范式，从理论层面来讲是，关系模式R∈1NF,如果对于R对于R的每个非平凡多值依赖X→→Y(Y不属于X),X都含有候选码，则R∈4NF。4NF就是限制关系模式的属性之间不允许有非平凡且非函数依赖的多值依赖。显然一个关系模式是4NF，则必为BCNF。

 

   也就是说，当一个表中的非主属性互相独立时（3NF），这些非主属性不应该有多值。若有多值就违反了第四范式。 

 

   这里提到的多值依赖详见博客：

 

   有这样一个用户联系方式表TELEPHONE(CUSTOMERID,PHONE,CELL)。CUSTOMERID为用户ID,PHONE为用户的固定电话,CELL为用户的移动电话。

 

    本来，这是一个非常简单的第3范式表。主键为CUSTOMERID，不存在传递依赖。但在某些情况下，这样的表还是不合理的。比如说，用户有两个固定电话，两个移动电话。这时，表的具体表示如下：

 

CUSTOMERID	PHONE	CELL
1000	8828-1234	149088888888
1000	8838-1234	149099999999

 

    由于PHONE和CELL是互相独立的，而有些用户又有两个和多个值。这时此表就违反第四范式。

 

    在这种情况下，此表的设计就会带来很多维护上的麻烦。例如，如果用户放弃第一行的固定电话和第二行的移动电话，那么这两行会合并吗？等等

 

    解决问题的方法为，设计一个新表NEW_PHONE(CUSTOMERID,NUMBER,TYPE).这样就可以对每个用户处理不同类型的多个电话号码，而不会违反第四范式。

 

    显然，第四范式的应用范围比较小，因为只有在某些特殊情况下，要考虑将表规范到第四范式。所以在实际应用中，一般不要求表满足第四范式。

 

 

 

6、5NF（第五范式）

 

   第五范式（5NF）：是最终范式。消除了4NF中的连接依赖。

 

   第五范式有以下要求：

 

  （1）必须满足第四范式

 

  （2）表必须可以分解为较小的表，除非那些表在逻辑上拥有与原始表相同的主键。 

 

   第五范式是在第四范式的基础上做的进一步规范化。第四范式处理的是相互独立的多值情况，而第五范式则处理相互依赖的多值情况。

 

   有一个销售信息表SALES（SALEPERSON，VENDOR，PRODUCT）。SALEPERSON代表销售人员，VENDOR代表供和商，PRODUCT则代表产品。

 

   在某些情况下，这个表中会产生一些冗余。可以将表分解为PERSON_VENDOR表（SALEPERSON，VENDOR）；PERSON_PRODUCT表（SALEPERSON，PRODUCT）；VENDOR­_PRODICT表（VENDOR，PRODUCT）