数据库三大范式讲解

《数据库三大范式讲解》由会员分享，可在线阅读，更多相关《数据库三大范式讲解（8页珍藏版）》请在金锄头文库上搜索。

1、数据库三大范式说明数据库的设计范式是数据库设计所需要满足的规范，满足这些规范的数据库是简洁的、 结构明晰的，同时，不会发生插入（insert）、删除（delete）和更新（update）操作异常。反 之则是乱七八糟，不仅给数据库的编程人员制造麻烦，而且面目可憎，可能存储了大量不需 要的冗余信息。实质上，设计范式用很形象、很简洁的话语就能说清楚，道明白。本节课将对范式进行 通俗地说明，以一个简单论坛的数据库为例来讲解怎样将这些范式应用于实际项目中。范式说明：第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整 型、实数、字符型、逻辑型、日期型等。卿,肪

2、的粧库耗符鴿琢盹字昭字噩银1翩2很显然，在当前的任何关系数据库管理系统（DBMS）中，傻瓜也不可能做出不符合第 一范式的数据库，因为这些DBMS不允许你把数据库表的一列再分成二列或多列。因此， 你想在现有的 DBMS 中设计出不符合第一范式的数据库都是不可能的。第二范式（2NF）：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的 是存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖 于任意一组候选关键字。假定选课关系表为SelectCourse（学号，姓名，年龄，课程名称，成绩，学分），关键字为组 合关键字（学号, 课程名称），因为存在如

3、下决定关系：（学号，课程名称）-（姓名，年龄，成绩，学分）这个数据库表不满足第二范式，因为存在如下决定关系：（课程名称）-（学分）（学号）-（姓名，年龄）即存在组合关键字中的字段决定非关键字的情况。由于不符合2NF,这个选课关系表会存在如下问题：（1） 数据冗余：同一门课程由n个学生选修，学分就重复n-1次；同一个学生选修了 m门课程，姓名 和年龄就重复了 m-1 次。（2） 更新异常： 若调整了某门课程的学分，数据表中所有行的学分值都要更新，否则会出现同一门课 程学分不同的情况。（3） 插入异常： 假设要开设一门新的课程，暂时还没有人选修。这样，由于还没有学号关键字，课程 名称和学分也无法记

4、录入数据库。（4） 删除异常： 假设一批学生已经完成课程的选修，这些选修记录就应该从数据库表中删除。但是，与 此同时，课程名称和学分信息也被删除了。很显然，这也会导致插入异常。把选课关系表SelectCourse改为如下三个表：学生：Student（学号，姓名，年龄）；课程：Course（课程名称，学分）；选课关系：SelectCourse（学号，课程名称，成绩）。 这样的数据库表是符合第二范式的，消除了数据冗余、更新异常、插入异常和删除异常。 另外，所有单关键字的数据库表都符合第二范式，因为不可能存在组合关键字。第三范式（3NF）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关

5、键字段的传递函数 依赖则符合第三范式。所谓传递函数依赖，指的是如果存在A - B - C的决定关系，则 C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系：关键字段-非关键字段x -非关键字段y假定学生关系表为Student（学号，姓名，年龄，所在学院，学院地点，学院电话），关键字 为单一关键字学号，因为存在如下决定关系：（学号） - （姓名， 年龄， 所在学院， 学院地点， 学院电话）这个数据库是符合2NF的，但是不符合3NF,因为存在如下决定关系：（学号） - （所在学院） - （学院地点， 学院电话）即存在非关键字段学院地点、学院电话对关键字段学号的传递函数依赖。它也

6、会存在数据冗余、更新异常、插入异常和删除异常的情况，读者可自行分析得知。 把学生关系表分为如下两个表：学生： （学号， 姓名， 年龄， 所在学院）；学院： （学院， 地点， 电话）。这样的数据库表是符合第三范式的，消除了数据冗余、更新异常、插入异常和删除异常。鲍依斯-科得范式（BCNF）：假设仓库管理关系表为StorehouseManage（仓库ID，存储物品ID，管理员ID，数量），且 有一个管理员只在一个仓库工作；一个仓库可以存储多种物品。这个数据库表中存在如下决 定关系：（仓库 ID， 存储物品 ID） -（管理员 ID， 数量）（管理员 ID， 存储物品 ID） - （仓库 ID， 数

7、量）所以，（仓库ID,存储物品ID）和（管理员ID,存储物品ID）都是StorehouseManage的候 选关键字，表中的唯一非关键字段为数量，它是符合第三范式的。但是，由于存在如下决定 关系：（仓库 ID） - （管理员 ID）（管理员ID）-（仓库ID）即存在关键字段决定关键字段的情况，所以其不符合 BCNF 范式。它会出现如下异常 情况：（1） 删除异常：当仓库被清空后，所有存储物品ID和数量信息被删除的同时，呛库ID和管理员ID信息也被删除了。（2） 插入异常：当仓库没有存储任何物品时，无法给仓库分配管理员。（3） 更新异常：如果仓库换了管理员，则表中所有行的管理员ID都要修改。把仓

8、库管理关系表分解为二个关系表：仓库管理：StorehouseManage（仓库ID,管理员ID）；仓库：Storehouse（仓库ID,存储物品ID,数量）。这样的数据库表是符合BCNF范式的，消除了删除异常、插入异常和更新异常。范式应用范式案例一我们来逐步搞定一个论坛的数据库，有如下信息：（1）用户：用户名，email，主页，电话，联系地址（ 2 ） 帖子：发帖标题，发帖内容，回复标题，回复内容第一次我们将数据库设计为仅仅存在表：貯1 ZemlC 騎畴尴 劉髓22躍醱 漲總 si齢無港舲篡-就,闊第囲俎儷咸報疑罐薛灌L旌-按游饒户馳不擬埶遷同龜讓总 加鈿nr醺I昨氐祸酚：|SP名御彌硕輛嶽働

9、懶id對瞬.隣壇 瀬id 麼體 刪这样数据表中的关键字（用户名，发帖ID,回复ID）能决定整行：（用户名，发帖ID,回复ID） - （email，主页，电话，联系地址，发帖标题，发帖内容，回复标题，回复内容）但是，这样的设计不符合第二范式，因为存在如下决定关系：（用户名）f （emai 1，主页，电话，联系地址）（发帖ID） f （发帖标题，发帖内容）（回复ID） f （回复标题，回复内容）即非关键字段部分函数依赖于候选关键字段，很明显，这个设计会导致大量的数据冗余和操作异常。我们将数据库表分解为（带下划线的为关键字）：1)用户信息：用户名， email，主页，电话，联系地址2)帖子信息：发帖

10、ID,标题，内容3)回复信息：回复ID,标题，内容4)发贴：用户名，发帖ID5)回复：发帖ID,回复ID这样的设计是满足第1、2、3范式和BCNF范式要求的，但是这样的设计是不是最好 的呢？不一定。观察可知，第4项发帖中的用户名和发帖ID之间是1： N的关系，因此我们可以 把”发帖”合并到第2项的帖子信息”中；第5项”回复”中的”发帖ID”和”回复ID之间也是1： N的关系，因此我们可以把回复合并到第3项的回复信息中。这样可以一定量地减少数 据冗余，新的设计为：（1）用户信息：用户名，email，主页，电话，联系地址（2）帖子信息：用户名，发帖ID,标题，内容（3）回复信息：发帖ID,回复ID

11、,标题，内容数据库表 1 显然满足所有范式的要求；数据库表2中存在非关键字段标题”、”内容”对关键字段”发帖ID”的部分函数依赖，即 不满足第二范式的要求，但是这一设计并不会导致数据冗余和操作异常；数据库表3中也存在非关键字段标题、内容对关键字段回复ID的部分函数依赖， 也不满足第二范式的要求，但是与数据库表2相似，这一设计也不会导致数据冗余和操作异 常。由此可以看出，并不一定要强行满足范式的要求，对于1： N关系，当1的一边合并到N 的那边后， N 的那边就不再满足第二范式了，但是这种设计反而比较好！对于M： N的关系，不能将M 一边或N 边合并到另一边去，这样会导致不符合范式 要求，同时导

12、致操作异常和数据冗余。对于1： 1的关系，我们可以将左边的1或者右边的1合并到另一边去，设计导致不符 合范式要求，但是并不会导致操作异常和数据冗余。结论 满足范式要求的数据库设计是结构清晰的，同时可避免数据冗余和操作异常。这并意味 着不符合范式要求的设计一定是错误的，在数据库表中存在1： 1或1： N关系这种较特殊 的情况下，合并导致的不符合范式要求反而是合理的。范式案例二第一范式(1NF):如果关系模式R的每个关系都是r的属性值不可分割的原子值，则称关系R是第一范式的 模式.如：不满足第一范式的情况：关系 R(name .address .phone)nameaddressphoneCC廈门

13、1231233CC厦门2312313说B:phone可以再分(可以分phonel和phu门巳2)第二范式(2NF):1)局部依赖 对于依赖关系W-A(A依赖于W),如果存在X归属于W,且X-A(A依赖 于 X), 那 么 称 W-A 是 局 部 依 赖 ; 否 则 称 W-A 是 完 全 依 赖 .比如：关系模式 R(sno,cno .grade ,tname,taddr)sri 学生学号;unci课程编号:graded绩;tri日m已老师姓各怕dd匚老师住址 (sno1cno)-(tname1taddr)(sno,cno 决定于 tnameL(tname,taddr).2)二范式定义:如果关

14、系模式R满足第一范式，且每个非主属性完全依赖于侯选键，则称R满足第二范式不满定第二范式的情况：关系模式 R(sno,cno .grade ,tname,taddr)sno学生学号工门谍程编号屯田北减绩;tn日m巳老师姓茗;怕dcT老师住址snoenogradetnametaddr101001100黄老师厦门.10200195黄老师厦门.10300198黄老师廈门.10400295李老师漳州.1050390刘老师漳州.说明:出现相同ffitname ,taddr3i： 消除右法:分解关系模式尺Ro.grade)snoenograde101001100102001951030019810400295105003907R2(cno,tname1taddr)enotnametaddr第三范式(3NF):1) ：传递依赖:如果X-Y,Y-A那么称X-A是传递依赖.(A传递依赖于X)2) :三范式定义:如果关系模式R是1NF,且每个非主属性都不依赖于R的侯选键,那么称R满足第三范式. 不满足第三范式的情况:关系模式 R2(cno,tname,taddr)是 2NF 模式,如果在 R2 中存在 cno-tname,tname-taddr那么 cno-taddr 就是个传递依赖,及不满足第三范式.enotnametaddr001