数据库六大范式(关系型数据库)
数据库需要满足一些规则(约定)来优化数据的设计和存储,这些规则就称为范式。
关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)
相互关系:满足5的一定满足前面的范式,以此类推。(子集)
第一范式
第一范式每一列不可再拆分(原子性)。
在此基础上继续规范化,优化,解耦,既
第二范式
在第一范式的基础上所有列完全依赖于主键列
如下图:借书证名称依赖于借书证号,并没有完全依赖于主键(学生证号);
第三范式
在第二范式的基础上任何非主列不得传递依赖于主键。
以上三种范式较为常用
巴斯-科德范式
针对某一列与复合主键中的某一列有关,而与其他主键无关
(仓库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。
第四范式
在BC范式的基础上,消除属性间非平凡且非函数依赖的多值依赖,这里提的多值依赖于之前的函数依赖不同。多值依赖标识属性间的一种依赖关系,比如有属性X、Y、Z,对于X的每个值,Y有一个值集,Z有一个值集,并且Y的值集合Z的值集彼此独立。
比如:
课程表(课程名,修读该课程的学生,教授该课程的教师)
上述关系满足BCNF,且可知一个课程对应N个学生的同时对应N个教师,且学生与教师是独立的,故不满足4NF,可以改造为如下的两个表结构,使之符合4NF:
学生-课程表(课程名,修读该课程的学生)
教师-课程表(课程名,教授该课程的教师)
第五范式
第五范式(5NF):是最终范式。消除了4NF中的连接依赖。
第五范式有以下要求:
(1)必须满足第四范式
(2)表必须可以分解为较小的表,除非那些表在逻辑上拥有与原始表相同的主键。
第五范式是在第四范式的基础上做的进一步规范化。第四范式处理的是相互独立的多值情况,而第五范式则处理相互依赖的多值情况。
在第四范式的基础上,消除关系中的所有冗余。5NF避免了所有的数据冗余,节省了存储空间,同时保持了数据的一致性,但是也付出了效率上的代价。在实际应用中,数据库适当的冗余其实有助于提高查询的效率,减少过多的表连接操作,所以5NF的应用少之又少。
比如:
选修课表(学生,课程,教师)(多个老师可以同时教授同一门选修课,多个学生能选修同一门选修课)
可知(学生,课程,教师)就是唯一一个候选键,所有属性都是主属性,且不满足属性间的多值依赖,故满足4NF,但是存在大量冗余,可以改造为以下三个表结构,使之符合5NF:
学生-课程表(学生,课程)
课程-教师表(课程,教师)
学生-教师表(学生,教师)
以上为六大范式的整理