转载

【数据库策略 三】数据库三范式

转自http://blog.csdn.net/w__yi/article/details/19934319

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

#第一范式(1NF)无重复的列

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

例如以下这张表就不符合第一范式的要求:

这里写图片描述

要想符合,必须将进货和销售下边的拆分开来:

这里写图片描述

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

#第二范式(2NF)

属性完全依赖于主键 [ 消除部分子函数依赖 ]
如果关系模式R为第一范式,并且R中每一个非主属性完全函数依赖于R的某个候选键, 则称为第二范式模式。

第二范式(2NF)是在第一范式(1NF)的基础上建立起来的,即满足第二范式(2NF)必须先满足第一范式(1NF)。**第二范式(2NF)要求数据库表中的每个实例或行必须可以被惟一地区分。**为实现区分通常需要为表加上一个列,以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键、主码。

例如员工信息表中加上了员工编号(emp_id)列,因为每个员工的员工编号是惟一的,因此每个员工可以被惟一区分。

简而言之,第二范式(2NF)就是非主属性完全依赖于主关键字。

所谓完全依赖是指不能存在仅依赖主关键字一部分的属性(设有函数依赖W→A,若存在XW,有X→A成立,那么称W→A是局部依赖,否则就称W→A是完全函数依赖)。**若在一张表中,在属性(或属性组)X的值确定的情况下,必定能确定属性Y的值,那么就可以说Y函数依赖于X,写作 X → Y。**如果存在,那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体,新实体与原实体之间是一对多的关系。

假定选课关系表为SelectCourse(学号, 姓名, 年龄, 课程名称, 成绩, 学分),关键字为组合关键字(学号, 课程名称),因为存在如下决定关系:
(学号, 课程名称) → (姓名, 年龄, 成绩, 学分)
这个数据库表不满足第二范式,因为存在如下决定关系:
(课程名称) → (学分)
(学号) → (姓名, 年龄)
即存在组合关键字中的字段决定非关键字的情况。

##三类完整性约束
在关系模型中,关系完整性主要是指以下三方面:
###实体完整性
所谓的实体完整性就是指关系(所谓的关系就是表)的主码不能取空值
比如学生表的主码通常是取学号为主码
###参照完整性
是指参照关系中每个元素的外码要么为空(NULL),要么等于被参照关系中某个元素的主码;
比如今天是9月2日是开学日,大学新生刚来报道,在学生表里,有的学生可能还没来得及分配具体的班,所以这些还未来得及分班的学生教务处可以在学生表里的班级属性取空值NULL(空值代表“不确定”),而哪些已分了班的学生就必须取班级表里的某些属性,比如班级类别,即学生属于哪个班。比如取“软件工程”,”计算机技术应用“等等。
参照关系也称为外键表,被参照关系也称为主键表。

###用户定义的完整性
指对关系中每个属性的取值作一个限制(或称为约束)的具体定义。比如 性别属性只能取”男“或”女“ ,再就是年龄的取值范围,可以取值0-130 ,但不能取负数,因为年龄不可能是负数。
##不符合的情况
由于不符合2NF,这个选课关系表会存在如下问题:

图片来自https://www.zhihu.com/question/24696366

这里写图片描述

  1. 数据冗余
    每一名学生的学号、姓名、系名、系主任这些数据重复多次。每个系与对应的系主任的数据也重复多次——数据冗余过大

(2) 更新异常
假如李小明转系到法律系,那么为了保证数据库中数据的一致性,需要修改三条记录中系与系主任的数据。——修改异常。

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

(4) 删除异常:

假如将某个系中所有学生相关的记录都删除,那么所有系与系主任的数据也就随之消失了(一个系所有学生都没有了,并不表示这个系就没有了
###完全依赖,部分依赖,码
####完全函数依赖
在一张表中,若 X → Y,且对于 X 的任何一个真子集(假如属性组 X 包含超过一个属性的话),X ’ → Y 不成立,那么我们称 Y 对于 X 完全函数依赖,记作 X F→ Y。

  • 学号 F→ 姓名
  • (学号,课名) F→ 分数 (注:因为同一个的学号对应的分数不确定,同一个课名对应的分数也不确定)
    ####部分函数依赖
    假如 Y 函数依赖于 X,但同时 Y 并不完全函数依赖于 X,那么我们就称 Y 部分函数依赖于 X,记作 X P→ Y
    例如:(学号,课名) P→ 姓名 ,姓名只依赖于学号,所以部分依赖于(学号,课名)
    ####传递函数依赖
    假如 Z 函数依赖于 Y,且 Y 函数依赖于 X 。这里改为:『Y 不包含于 X,且 X 不函数依赖于 Y』这个前提),那么我们就称 Z 传递函数依赖于 X ,记作 X T→ Z,
    ####码
    设 K 为某表中的一个属性或属性组,**假如当 K 确定的情况下,该表除 K 之外的所有属性的值也就随之确定,那么 K 就是码。**一张表中可以有超过一个码。(实际应用中为了方便,通常选择其中的一个码作为主码)

例如:
对于表3,(学号、课名)这个属性组就是码。该表中有且仅有这一个码。(假设所有课没有重名的情况)
主属性
包含在任何一个码中的属性成为主属性。
例如:
对于表3,主属性就有两个,学号 与 课名。

###检查是否符合第二范式
终于可以回过来看2NF了。首先,我们需要判断,表是否符合2NF的要求?根据2NF的定义,判断的依据实际上就是看数据表中是否存在非主属性对于码的部分函数依赖。若存在,则数据表最高只符合1NF的要求,若不存在,则符合2NF的要求。判断的方法是:
第一步:找出数据表中所有的码
表3的码只有一个,就是(学号、课名)。

第二步:根据第一步所得到的码,找出所有的主属性。
主属性有两个:学号 与 课名

第三步:数据表中,除去所有的主属性,剩下的就都是非主属性了。
非主属性有四个:姓名、系名、系主任、分数

第四步:查看是否存在非主属性对码的部分函数依赖。
对于(学号,课名) → 姓名,有 学号 → 姓名,存在非主属性 姓名 对码(学号,课名)的部分函数依赖。
对于(学号,课名) → 系名,有 学号 → 系名,存在非主属性 系名 对码(学号,课名)的部分函数依赖。
对于(学号,课名) → 系主任,有 学号 → 系主任,存在非主属性 对码(学号,课名)的部分函数依赖。所以表3存在非主属性对于码的部分函数依赖,最高只符合1NF的要求,不符合2NF的要求。
###修改以达到第二范式
为了让表3符合2NF的要求,我们必须消除这些部分函数依赖,只有一个办法,就是将大数据表拆分成两个或者更多个更小的数据表,在拆分的过程中,要达到更高一级范式的要求,这个过程叫做”模式分解“。模式分解的方法不是唯一的,以下是其中一种方法:

选课(学号,课名,分数)
学生(学号,姓名,系名,系主任)
我们先来判断以下,选课表与学生表,是否符合了2NF的要求?对于选课表,其码是**(学号,课名),主属性是学号和课名,非主属性是分数,学号确定,并不能唯一确定分数,课名确定,也不能唯一确定分数,所以不存在非主属性分数对于码 (学号,课名)的部分函数依赖,所以此表符合2NF的要求。对于学生表,其码是学号**,主属性是学号,非主属性是姓名、系名和系主任,因为码只有一个属性,所以不可能存在非主属性对于码 的部分函数依赖,所以此表符合2NF的要求。

这里写图片描述

这里写图片描述

这里还涉及到一个如何进行模式分解才是正确的知识点,先不介绍了)现在我们来看一下,进行同样的操作,是否还存在着之前的那些问题?李小明转系到法律系

只需要修改一次李小明对应的系的值即可。——有改进

数据冗余是否减少了,学生的姓名、系名与系主任,不再像之前一样重复那么多次了。——有改进

删除某个系中所有的学生记录,该系的信息仍然全部丢失。——无改进

插入一个尚无学生的新系的信息。因为学生表的码是学号,不能为空,所以此操作不被允许。——无改进

所以说,仅仅符合2NF的要求,很多情况下还是不够的,而出现问题的原因,**在于仍然存在非主属性系主任对于码学号的传递函数依赖。**为了能进一步解决这些问题,我们还需要将符合2NF要求的数据表改进为符合3NF的要求。

##第三范式
###存在问题

3NF在2NF的基础之上,消除了非主属性对于码的传递函数依赖。也就是说, 如果存在非主属性对于码的传递函数依赖,则不符合3NF的要求。

对于选课表,主码为(学号,课名),主属性为学号和课名,非主属性只有一个,为分数,不可能存在传递函数依赖,所以选课表的设计,符合3NF的要求。

对于学生表,主码为学号,主属性为学号,非主属性为姓名、系名和系主任。因为 学号 → 系名,同时 系名 → 系主任,所以存在非主属性系主任对于码学号的传递函数依赖,所以学生表的设计,不符合3NF的要求。
###如何解决
为了让数据表设计达到3NF,我们必须进一步进行模式分解为以下形式:
选课(学号,课名,分数)
学生(学号,姓名,系名)
(系名,系主任)对于选课表,符合3NF的要求,之前已经分析过了。对于学生表,码为学号,主属性为学号,非主属性为系名,不可能存在非主属性对于码的传递函数依赖,所以符合3NF的要求。对于系表,码为系名,主属性为系名,非主属性为系主任,不可能存在非主属性对于码的传递函数依赖(至少要有三个属性才可能存在传递函数依赖关系),所以符合3NF的要求。+

这里写图片描述

这里写图片描述

现在我们来看一下,进行同样的操作,是否还存在着之前的那些问题?

删除某个系中所有的学生记录
该系的信息不会丢失。——有改进
插入一个尚无学生的新系的信息。
因为系表与学生表目前是独立的两张表,所以不影响。——有改进
数据冗余更加少了。——有改进

#总结
第一范式:属性字段不可分

第二范式: 不能存在部分依赖,非主属性必须完全依赖于主码,不能部分依赖于主码
例如(课程名,学号)—>学院,学院只依赖于学号,所以存在部分依赖

第三范式:不能存在传递依赖,也就是非主属性不能依赖其它非主属性
也可以这么理解:每一个表中都不能包含其它表中非主属性的关键字信息
例如:(学号) → (姓名, 年龄, 所在学院, 学院地点, 学院电话)这个数据库是符合2NF的,但是不符合3NF,因为存在如下决定关系:(学号) → (所在学院) → (学院地点, 学院电话)
所以修改为学生:(学号, 姓名, 年龄, 所在学院),学院:(学院, 地点, 电话)。

文章最后发布于: 2017-09-20 15:52:42
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: Age of Ai 设计师: meimeiellie

分享到微信朋友圈

×

扫一扫,手机浏览