数据库范式设计

数据库设计范式实际上非常重要，但从实际开发的角度来说，如果全部按照范式去做，则很多程序都没法写了，即便是查询语名也是变得很复杂。

       在 Oracle 中的 scott 用户下的全部表，实际上已经很好地体现了一种设计思路，雇员 - 部门的关系。

-- 三范式
第一范式：要有主键，列不可分
第二范式：不能存在部分依赖，
第三范式：不能存在传递依赖

 

 

1.1 第一范式（ 1NF ）

  要有主键，列不可分

在任何一个关系数据库中，第一范式（1NF ）是对关系模式的基本要求，不满足第一范式（1NF ）的数据库就不是关系数据库。

所谓第一范式（1NF ）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如 果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式（1NF ）中表的每一行只包含一个实例的信息。简而言之，第一范式就是无重复的列。

 

例：有如下数据库创建脚本

 

CREATE TABLE person(

    pid NUMBER(4)   PRIMARY KEY NOT NULL,

    name    VARCHAR2(50),

    info    VARCHAR2(200)

  );

 

 

INSERT INTO person(pid,name,info)

VALUES(1111,' 张三 ','1990年 1月 2日 出生，现在住在贵阳市花溪区贵州大学教职工区十栋一单元 203 室 ')

 

SQL> select * from person;

 

 

 

       PID   NAME                                       INFO

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

      1111    张三                      1990年 1月 2日 出生，现在住在贵阳市花溪

区贵州大学教职工区十栋一单元 203 室

实际上以上人员信息应当由以下几部份组成 :

           生日 :1990年 1月 2日

           省市 : 贵阳

           地区 : 花溪区

        详细的地址 :

每列是不可再分的最小属性 , 因此，为了符合第一范式，以上数据库创建脚本应当修改为：

CREATE TABLE person(

    pid             NUMBER(4)   PRIMARY KEY NOT NULL,

    name            VARCHAR2(50),

    birthday           DATE,

    area                VARCHAR2(200),

    subarea            VARCHAR2(200),

    address            VARCHAR2(200)

  )

 

 

 

 

1.2 第二范式（ 2NF ）

      第二范式（2NF ）是在第一范式（1NF ）的基础上建立起来的，即满足第二范式（2NF ）必须先满足第一范式（1NF ）。第二范式（2NF ）要求数据库表 中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。如scott 用户下的emp 表中加上了员工编号（empno ）列，因为每个员工的员工编号是惟一的，因此每个员工可以被惟一区分。这个惟一属性列被称为主关键字或主键、主 码。

      第二范式（2NF ）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部 分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。简而言之，第二范式 就是非主属性非部分依赖于主关键字。

      

 

 

 

例：对于学生选课表 ( 学号，姓名，年龄，课程名称，成绩，学分 )

        创建数据库脚本如下 :

CREATE TABLE selectcourse

(

   stuno           VARCHAR2(50),

   stuname         VARCHAR2(50),

   stuage          NUMBER,

   cname           VARCHAR2(50),

   grade           NUMBER,

   credit          NUMBER

)

 

插入测试数据 :

INSERT INTO selectcourse  VALUES('S001',' 张三 ',20,'JAVA',89.0,3);

  INSERT INTO selectcourse  VALUES('S002',' 李四 ',20,'JAVA',89.0,3);

  INSERT INTO selectcourse  VALUES('S001',' 王五 ',20,'JAVA',89.0,3);

 

结果如下 :

从以上结果可以看出，所有的课程信息冗余了，而且存在如下问题：

          如果一门课程没有一个学生选择，则课程就从学校彻底消失了

     课程本身也应该包含一个课程编号，但如果按以上设计，则加入的课程编号也肯定重复。

 

 

使用第二范式的数据库创建脚本 :

学生是一个实体，因此要有一个表 :

CREATE TABLE student

  (

    stuno         VARCHAR2(50)  NOT NULL PRIMARY KEY,

     stuname       VARCHAR2(50),

    stuage          NUMBER

  )

 

 

 

 

 

课程也是一个具体的实体，因而应当有一个表来表示 :

CREATE TABLE course

(

   cid           NUMBER(5)  NOT NULL PRIMARY KEY,

    cname        VARCHAR2(50),

   credit        NUMBER

)

 

 

一个学生可以选多门课，一门课可以让多个学一选，多对多联系

选课表 :

CREATE TABLE selectcourse

  (

    stuno               VARCHAR2(50),

    cid                NUMBER(5),

    grade               NUMBER,

    CONSTRAINT      student_selectcourse_fk FOREIGN KEY(stuno) REFERENCES student(stuno),

    CONSTRAINT      student_course_fk FOREIGN KEY(cid) REFERENCES course(cid)

  )

 

插入数据测试

 

INSERT INTO student(stuno,stuname,stuage)  VALUES('S001',' 张三 ',20)

INSERT INTO student(stuno,stuname,stuage)  VALUES('S002',' 李四 ',20);

INSERT INTO student(stuno,stuname,stuage)  VALUES('S003',' 王五 ',20);

 

INSERT INTO course(cid,cname,credit) VALUES('00001','JAVA',3)

INSERT INTO course(cid,cname,credit) VALUES('00002','ORACLE',5)

INSERT INTO course(cid,cname,credit) VALUES('00003','NETWORK',5);

 

 

 

1.3 第三范式（ 3NF ）

      满足第三范式（3NF ）必须先满足第二范式（2NF ）。简而言之，第三范式（3NF ）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。例 如，存在一个部门信息表，其中每个部门有部门编号（dept_id ）、部门名称、部门简介等信息。那么在图3-2 的员工信息表中列出部门编号后就不能再将 部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF ）也应该构建它，否则就会有大量的数据冗余。简 而言之，第三范式就是属性不依赖于其它非主属性。

数据库设计三大范式应用实例剖析

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

设计范式是不是很难懂呢？非也，大学教材上给我们一堆数学公式我们当然看不懂，也记不住。所以我们很多人就根本不按照范式来设计数据库。

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

　　 范式说明

第一范式（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 改为如下三个表：

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

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

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

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

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

第三范式（3NF ）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如果 存在"A → B → C" 的决定关系，则C 传递函数依赖于A 。因此，满足第三范式的数据库表应该不存在如下依赖关系：

关键字段 → 非关键字段x → 非关键字段y

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

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

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

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

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

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

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

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

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

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

　　 范式应用

我们来逐步设计一个论坛的数据库，有如下信息：

（1 ） 用户：用户名，email ，主页，电话，联系地址

（2 ） 帖子：发帖标题，发帖内容，回复标题，回复内容

第一次我们将数据库设计为仅仅存在表：

用户名

email

主页

电话

联系地址

发帖标题

发帖内容

回复标题

回复内容

这个数据库表符合第一范式，但是没有任何一组候选关键字能决定数据库表的整行，唯一的关键字段用户名也不能完全决定整个元组。我们需要增加" 发帖ID" 、" 回复ID" 字段，即将表修改为：

用户名

email

主页

电话

联系地址

发帖ID

发帖标题

发帖内容

回复ID

回复标题

回复内容

这样数据表中的关键字( 用户名，发帖ID ，回复ID) 能决定整行：

( 用户名, 发帖ID, 回复ID) → (email, 主页, 电话, 联系地址, 发帖标题, 发帖内容, 回复标题, 回复内容)

但是，这样的设计不符合第二范式，因为存在如下决定关系：

( 用户名) → (email, 主页, 电话, 联系地址)

( 发帖ID) → ( 发帖标题, 发帖内容)

( 回复ID) → ( 回复标题, 回复内容)

即非关键字段部分函数依赖于候选关键字段，很明显，这个设计会导致大量的数据冗余和操作异常。

我们将数据库表分解为（带下划线的为关键字）：

（1 ） 用户信息：用户名 ，email ，主页，电话，联系地址

（2 ） 帖子信息：发帖ID ，标题，内容

（3 ） 回复信息：回复ID ，标题，内容

（4 ） 发贴：用户名，发帖ID

（5 ） 回复：发帖ID ，回复ID

这样的设计是满足第1 、2 、3 范式和BCNF 范式要求的，但是这样的设计是不是最好的呢？

不一定。

观察可知，第4 项" 发帖" 中的" 用户名" 和" 发帖ID" 之间是1 ：N 的关系，因此我们可以把" 发帖" 合并到第2 项的" 帖子信息" 中；第5 项" 回复" 中的" 发帖ID" 和" 回复ID" 之间也是1 ：N 的关系，因此我们可以把" 回复" 合并到第3 项的" 回复信息" 中。这样可以一定量地减少数据冗余，新的设计为：

（1 ） 用户信息：用户名，email ，主页，电话，联系地址

（2 ） 帖子信息：用户名，发帖ID ，标题，内容

（3 ） 回复信息：发帖ID ，回复ID ，标题，内容

数据库表1 显然满足所有范式的要求；

数据库表2 中存在非关键字段" 标题" 、" 内容" 对关键字段" 发帖ID" 的部分函数依赖，即不满足第二范式的要求，但是这一设计并不会导致数据冗余和操作异常；

数据库表3 中也存在非关键字段" 标题" 、" 内容" 对关键字段" 回复ID" 的部分函数依赖，也不满足第二范式的要求，但是与数据库表2 相似，这一设计也不会导致数据冗余和操作异常。

由此可以看出，并不一定要强行满足范式的要求，对于1 ：N 关系，当1 的一边合并到N 的那边后，N 的那边就不再满足第二范式了，但是这种设计反而比较好！

对于M ：N 的关系，不能将M 一边或N 一边合并到另一边去，这样会导致不符合范式要求，同时导致操作异常和数据冗余。
对于1 ：1 的关系，我们可以将左边的1 或者右边的1 合并到另一边去，设计导致不符合范式要求，但是并不会导致操作异常和数据冗余。

　　 结论

    满足范式要求的数据库设计是结构清晰的，同时可避免数据冗余和操作异常。这并意味着不符合范式要求的设计一定是错误的，在数据库表中存在1 ：1 或1 ：N 关系这种较特殊的情况下，合并导致的不符合范式要求反而是合理的。

     以上三范式只能作参考，如果在实际开发中全部按范式来设计数据库，则没法下入手。数据库设计的唯一原则是: 数据库表的关联查询越少越好，SQL 语句的复杂度越低越好.