数据库设计三范式

文章转载自http://blog.csdn.net/chinayaosir/article/details/4465002

满足设计范式的数据库是简洁的，结构明晰的。同时，不会发生插入（insert）、删除（delete）和更新（update）操作异常。反之则是乱七八糟，不仅给数据库的编程人员制造麻烦，而且可能存储了大量不需要的冗余信息。

     1NF：字段具有原子性,不可再分。所有关系型数据库系统都满足第一范式）
     2NF：对于具有组合关键字的表.不存在组合关键字中的任意字段决定其它非关键字字段的情况。
     3NF:在2N的基础上,每一个非主属性既不部分依赖于码也不传递依赖于码.

简单描述：
 
     第三范式的要求如下：
         1，每一列只有一个值
         2，每一行都能区分。
         3，每一个表都不包含其他表已经包含的非主关键字信息。

       实质上，设计范式用很形象、很简洁的话语就能说清楚。这里将对范式进行通俗地说明，以一个简单论坛的数据库为例讲解怎么样将这些范式应用于实际工程.

范式说明
         第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。
     例如，符合第一范式： 字段1 字段2 字段3 字段4
              不符合第一范式： 字段1 字段2 字段3 字段4  
                                                                    字段3.1 字段3.2
         很显然，在当前的任何关系数据库管理系统（DBMS）中，傻瓜也不可能做出不符合第一范式的数据库，因为这些DBMS不允许你把数据库表的一列再分成二列或多列。因此，你想在现有的DBMS中设计出不符合第一范式的数据库都是不可能的。

         第二范式（2NF）：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖于任意一组候选关键字。
     假定选课关系表为SelectCourse(学号, 姓名, 年龄, 课程名称, 成绩, 学分)，
     关键字为组合关键字(学号, 课程名称)，因为存在如下决定关系：
        (学号, 课程名称) → (姓名, 年龄, 成绩, 学分)
     这个表不满足第二范式，因为存在如下决定关系：
     (课程名称) → (学分) 

       (学号) → (姓名, 年龄)
     即存在组合关键字中的字段决定非关键字的情况。
      由于不符合2NF，这个选课关系表会存在如下问题：
        (1) 数据冗余：
                同一门课程由n个学生选修，"学分"就重复n-1次；同一个学生选修了m门课程，

         姓名和年龄就重复了m-1次。
       (2) 更新异常：
                 若调整了某门课程的学分，数据表中所有行的"学分"值都要更新，否则会出现

         同一门课程学分不同的情况。
        (3) 插入异常：
                假设要开设一门新的课程，暂时还没有人选修。这样，由于还没有"学号"关键字,

         课程名称和学分也无法记录入数据库。
       (4) 删除异常：
                假设一批学生已经完成课程的选修，这些选修记录就应该从数据库表中删除。

         但是，与此同时，课程名称和学分信息也被删除了。很显然，这也会导致插入异常。

         把选课关系表SelectCourse改为如下三个表：
              1).学生：Student(学号, 姓名, 年龄)；
              2).课程：Course(课程名称, 学分)；
             3).选课关系：SelectCourse(学号, 课程名称, 成绩)。
         这样的数据库表是符合第二范式的，消除了数据冗余、更新异常、插入异常和删除异常。 另外，所有单关键字的数据库表都符合第二范式，因为不可能存在组合关键字。


         第三范式（3NF）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。

         所谓传递函数依赖，指的是如果存在"A → B → C"的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系：
    关键字段 → 非关键字段x → 非关键字段y
    假定学生关系表为Student(学号, 姓名, 年龄, 所在学院, 学院地点, 学院电话)，
    关键字为单一关键字"学号"，因为存在如下决定关系：
     (学号) → (姓名, 年龄, 所在学院, 学院地点, 学院电话)
     这个数据库是符合2NF的，但是不符合3NF，因为存在如下决定关系：
     (学号) → (所在学院) → (学院地点, 学院电话)
即存在非关键字段"学院地点"、"学院电话"对关键字段"学号"的传递函数依赖。
     它也会存在数据冗余、更新异常、插入异常和删除异常的情况，读者可自行分析得知。
     把学生关系表分为如下两个表：
     学生：(学号, 姓名, 年龄, 所在学院)；
     学院：(学院, 地点, 电话)。
这样的数据库表是符合第三范式的，消除了数据冗余、更新异常、插入异常和删除异常。


     鲍依斯-科得范式（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范式的，消除了删除异常、插入异常和更新异常。

------------------------------------------------------------------------------------------------------------------------------------------

  标准化表示从你的数据存储中移去数据冗余的过程。如果数据库设计达到了完全的标准化，则把所有的表通过关键字连接在一起时，不会出现任何数据的复本。标准化的优点是明显的，它避免了数据冗余，自然就节省了空间，也对数据的一致性提供了根本的保障，杜绝了数据不一致的现象，同时也提高了效率。

范式，是关系型数据库关系模式规范化的标准，从规范化的宽松到严格，分别为不同的范式，通常使用的有第一范式(1NF)、第二范式(2NF)、第三范式(3NF)等。

1NF：字段具有原子性,不可再分。（所有关系型数据库系统都满足第一范式）

2NF：对于具有组合关键字的表.不存在组合关键字中的任意字段决定其它非关键字字段的情况。
      3NF：在2N的基础上,每一个非主属性既不部分依赖于码也不传递依赖于码.

第一范式（1NF）

        第一范式是最低的规范化要求，第一范式要求数据表不能存在重复的记录，即存在一个关键字。1NF的第二个要求是每个字段都不可再分，即已经分到最小。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。

例如：

符合第一范式：   字段1、字段2 、字段3 、字段4
        不符合第一范式： 字段1、 字段2 、字段3 、字段4   、字段3.1 、字段3.2
     很显然，在当前的任何关系数据库管理系统（DBMS）中，也不可能做出不符合第一范式的数据库，因为这些DBMS不允许你把数据库表的一列再分成二列或多列。1NF是关系模式应具备的最起码的条件，如果数据库设计不能满足第一范式，就不称为关系型数据库。关系数据库设计研究的关系规范化是在1NF之上进行的。满足1NF的关系模式有许多不必要的重复值，并且增加了修改其数据时疏漏的可能性。为了避免这种数据冗余和更新数据的遗漏，就引出了第二范式（2NF）。   

第二范式（2NF）

        如果一个关系属于1NF，且所有的非主关键字段都完全地依赖于主关键字，则称之为第二范式。简单的说，第二范式要满足以下的条件：首先要满足第一范式，其次每个非主属性要完全函数依赖与候选键，或者是主键。也就是说，每个非主属性是由整个主键函数决定的，而不能由主键的一部分来决定。
例如：

选课关系表SelectCourse中包括字段(学号, 姓名, 年龄, 课程名称, 成绩, 学分)，关键字为组合关键字(学号, 课程名称)，此表存在的决定关系：(学号, 课程名称) → (姓名, 年龄, 成绩, 学分)
，这个表是不满足第二范式的，因为存在如下决定关系：
      (课程名称) → (学分)  

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

如果把选课关系表改为如下三个表：
1）学生：Student(学号, 姓名, 年龄)；
2）课程：Course(课程名称, 学分)；
3）选课关系：SelectCourse(学号, 课程名称, 成绩)。

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

第三范式（3NF）
         如果一个关系属于2NF，且每个非关键字不传递依赖于主关键字，这种关系是3NF。
简单的说，第三范式要满足以下的条件：首先要满足第二范式，其次非主属性之间不存在函数依赖。由于满足了第二范式，表示每个非主属性都函数依赖于主键。如果非主属性之间存在了函数依赖，就会存在传递依赖，这样就不满足第三范式。
         所谓传递函数依赖，指的是如果存在"A → B → C"的决定关系，则C传递函数依赖于A。因此，满足第三范·式的数据库表应该不存在依赖关系：关键字段→非关键字段x→非关键字段y。

例如：

学生关系表Student中字段包括(学号, 姓名, 年龄, 所在学院, 学院地点, 学院电话)， 关键字为单一关键字"学号"，存在如下决定关系：
      (学号) → (姓名, 年龄, 所在学院, 学院地点, 学院电话)
      这个数据库是符合2NF的，但是不符合3NF，因为存在如下决定关系：
      (学号) → (所在学院) → (学院地点, 学院电话)
    即存在非关键字段"学院地点"、"学院电话"对关键字段"学号"的传递函数依赖。它也会存在数据冗余、更新异常、插入异常和删除异常的情况。
      如果把学生关系表分为如下两个表：
      学生：(学号, 姓名, 年龄, 所在学院)；
      学院：(学院, 地点, 电话)。
    这样的数据库表是符合第三范式的，消除了数据冗余、更新异常、插入异常和删除异常。

利弊

有一利必有一弊。它最大的不利是把信息放置在不同的表中，增加了操作的难度，同时把多个表连接在一起的花费也是巨大的。因为表和表的连接操作是做两个关系的笛卡儿积，必然会产生大量无用甚至无效的记录，性能的代价是巨大的。

规范化的优点也是明显的。避免了大量的数据冗余，节省了空间，保持了数据的一致性，如果完全达到3NF，就不会在超过一个地方更改同一个值。如果记录经常的改变，这个优点会超过所有可能的缺点！

即使花费很多的时间，作出一个完全规范化的数据库，它仍然不是完美的。规范化设计所带来的性能问题可能是无法承受的。如果出现这种情况，就要准备进行非规范化了。非规范化就是为了获得性能上的利益所进行的违反规范化规则的操作，不过大部分都与实际应用有关系，包括复制属性，复制外来关键字，表合并，表重新组合等等。满足范式要求的数据库设计是结构清晰的，同时可避免数据冗余和操作异常。这并意味着不符合范式要求的设计一定是错误的，在数据库表中存在1：1或1：N关系这种较特殊的情况下，合并导致的不符合范式要求反而是合理的。总之，要学会根据实际的应用选择最有效的方法。