关系型数据库(五)数据库范式-3nf

范式:规范设计关系数据库时要遵从不同的规范要求设计出合理的表这些不同的规范要求被称为不同的范式就是前人总结出的良好数据库设计的经验。目前关系型数据库有六种常见范式按照范式级别从低到高分别是第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯科德范式(BCNF)、第四范式(4NF)、第五范式(5NF又称完美范式)数据库的范式设计越高阶数据的冗余度就越低同时高阶的范式一定符合低阶范式的要求满足最低要求的范式是第一范式(1NF)。在第一范式的基础上进一步满足更多规范要求的称为第二范式(2NF)以次类推。一般来说在关系型数据库设计中,最高也就遵循到BCNF我们普遍用到的还是3NF我们主要讲第一范式(1NF) 第二范式(2NF) 第三范式(3NF)第一范式(1NF)的要求是属性不可分割第二范式(2NF)的要求是满足第一范式的基础上不存在部分依赖第三范式(3NF)的要求是满足第二范式的基础上不存在传递依赖。范式的作用用来解决数据冗余和增加删除修改的异常要判断一个数据库设计的是否合理是否存在问题就从这两点判断。不遵循范式出现冗余的例子下面这张表存在什么问题学生信息表1.系名和系主任名相同的数据在这三条记录中重复出现数据出现冗余2.更新异常这是学生信息表但是在某系更换系主任后比如王三改成李四系统必须修改与该系学生有关的每一个行数据3.插入异常该插的数据插不进去如果一个系刚成立没有学生就无法把这个系及其系主任的信息存入数据库。比如计科刚成立没学生就没办法保存系主任信息4.删除异常 不该删除的数据不得不删如果某个系的学生全部毕业了比如小明小张小李毕业了在删除该系学生信息的同时把这个系及其系主任的信息也丢掉了。不考虑范式设计将信息全部存储在一张表里如下表就会出现数据冗余增删改异常。部门刚成立没有员工无法插入部门信息部门没有员工了要删掉部门名称好的关系的特点不发生插入异常、删除异常、更新异常数据冗余应尽可能少不良关系的原因由于存在于关系中的某些数据依赖引起的解决方法通过分解关系(表)多建立几张表来消除其中不合适的数据依赖。1.第一范式1NF保持列的原子性第一范式是指数据库表中的每个字段都是原子性的即不可再拆分。例1假设我们有一个学生表其中包含学生的姓名、电话和学校所在省县。省县不满足原子性。不拆分的话如果有一天这个县划归到另一个省那么相关记录都得改。如果有一天这个县改名那么相关记录都得改如果有一天这个省改名那么相关记录都得改按第一范式原子性的要求应该将学校所在省县拆分分学校所在省和学校所在县两列注意有些人说姓名不满足原子性能拆成姓氏和名。不要较真根据业务需求来拆分如果你的系统里物流信息需要按省市县划分那么右边的详细地址就不满足原子性根据业务来判断例2个人信息字段包含手机号职务 所以不满足第一范式可以拆分成手机号和职务可调整如下例子3 进货销售字段 不满足第一范式例子4“家庭信息”和“学校信息”列均不满足原子性的要求故不满足第一范式需将这两列进行拆分调整后每一列都是不可再分的调整如下例子5 电话不满足原子性总结 第一范式是指数据库表中的每个字段都是原子性的不可再拆分2.第二范式2NF目的就是消除部分依赖针对联合主键两个及以上字段在满足1NF的前提下表中不存在部分依赖非主键列要完全依赖于主键。(主要是说在联合主键的情况下非主键列不能只依赖于主键的一部分)说人话就是每张表只描述一件事情第二范式需要确保数据库表中的每一列都和主键相关而不能只与主键的某一部分相关主要针对联合主键而言如果有哪些数据只和主键的一部分有关的话就得把它们独立出来变成另一个数据表。注意第二范式是针对联合主键说的如果一个数据表的主键只有单一一个字段的话它就一定符合第二范式。例子1表中主键为“学生ID”和“课程ID”组成的联合主键主键是可以有多个。“学生ID”和“课程ID”两个值才能决定“得分”的值而“课程名称”只依赖于“课程ID”与“学生ID”没有依赖关系它不完全依赖于主键只依赖于主键的一部分不符合2NF。修改使表满足2NF后将原来的成绩表(score)拆分为成绩表(score)和课程表(kc)而且两个表都符合2NF。例子2在选课表中同学们可以一次选择多门课程因此主键是“选课编号”和“课程编号”的联合主键但是我们发现“课程名称”和“授课老师”两个字段只与“课程编号”相关也就是说不满足2nf 的规则表中存在部分依赖非主键列并没有按照2NF要求完全依赖于主键一张表描述了多件事。如果不拆分会出现什么问题授课老师和课程名称发生变化会去修改表中所有相关信息所以应该放到“课程信息表”中而不是“学生选课表”中。授课老师和课程名发生变化不用去修改选课表中所有相关信息表中不存在部分依赖非主键列要完全依赖于主键例子3 要设计一个订单信息表因为订单中可能会有多种商品所以要将订单编号和商品编号作为数据库表的联合主键这样就产生一个问题这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的联合主键相关而仅仅是与商品编号相关。所以在这里违反了第二范式的设计原则表中不存在部分依赖非主键列要完全依赖于主键。把这个订单信息表进行拆分把商品信息分离到另一个表中把订单项目表也分离到另一个表中 拆分后例子4同一个组件有可能由不同的供应商提供所以得把组件 ID 和供应商 ID 合在一起组成一个主键。但是供应商的名称和住址就只和供应商 ID 有关(部分依赖)这不符合第二范式的原则表中不存在部分依赖非主键列要完全依赖于主键。仔细看就会发现 Nick 这个名称和 VA 这个住址重复出现了两次要是它改名了或是被其他公司并购了怎么办这时候最好把这些数据存到第二个数据表中修改如下例子5成绩取决于学号课程号两个联合主键学分只于课程号有关联存在部分依赖总结2NF在1NF的基础之上消除了非主属性对于主键联合主键的部分依赖。注意只有一个主键的情况下肯定满足2NF3.第三范式3NF消除传递依赖第三范式是在满足第二范式的基础上消除非主键字段之间的传递关系。它要求每个非主键字段只依赖于主键而不依赖于其他非主键字段。也就是说非主键值不依赖于另一个非主键值。就是说数据不能存在传递关系即每个属性都跟主键有直接关系而不是间接关系。像a--b--c 属性之间含有这样的关系是不符合第三范式的。比如Student表学号姓名年龄性别所在院校院校地址院校电话这样一个表结构就存在传递关系。学号-- 所在院校 -- (院校地址院校电话)拆分如下。学号姓名年龄性别所在院校--所在院校院校地址院校电话例1假设我们有一个员工表其中包含员工ID、员工姓名、所属部门和部门负责人。以上表既满足第一范式也满足第二范式非主键字段也完全依赖于主键字段。但是,部门负责人字段其实是依赖所属部门字段的。也就是说部门负责人字段是非主键值而依赖了另一个非主键值-所属部门。所以就不符合第三范式.(3NF要求每个非主键字段只依赖于主键而不依赖于其他非主键字段)。在第三范式下我们应该将所属部门和部门负责人拆分为独立的表以避免员工表中的冗余数据并确保每个非主键字段只依赖于员工号。修改表使之满足3NF后例子2以上表既满足第一范式也满足第二范式非主键字段也完全依赖于主键字段。但是院系电话字段其实是依赖院系字段的。也就是说院系电话字段是非主键值而依赖了另一个非主键值-院系。所以就不符合第三范式(3NF要求每个非主键字段只依赖于主键而不依赖于其他非主键字段)。拆分后举例3产品名称依赖于非主键产品ID不依赖于ID(3NF要求每个非主键字段只依赖于主键而不依赖于其他非主键字段)。举例4表中存在一个传递依赖(学号-(班级-班主任。也就是说班主任这个非主键列依赖与另外一个非主键列 班级。所以不符号第三范式。(3NF要求每个非主键字段只依赖于主键而不依赖于其他非主键字段)。把这个表拆分成如下2个表这样对主键的传递依赖就消失了。上面的2个表都符合第3范式。举例5 最高/最低月薪依赖于职位不依赖于工号。不满足3NF要求每个非主键字段只依赖于主键而不依赖于其他非主键字段)。4.bcnf范式尽管BCNF和第三范式3NF有很多相似之处但BCNF比3NF更为严格。3NF要求每一个非主属性必须完全依赖于主键或依赖于候选键的一个非主属性。BCNF则要求每一个非平凡的函数依赖关系的左边必须是一个超键这意味着BCNF消除了所有非主属性对非候选键的依赖关系。换句话说所有的函数依赖关系在BCNF中都必须依赖于一个超键。这种更严格的要求使得BCNF能够进一步减少数据冗余和异常情况。为了更好地理解BCNF以下是一个实际案例分析。假设我们有一个学生选课数据库其中包括学生ID、课程ID和讲师ID三个属性。我们可以定义以下函数依赖关系学生ID - 课程ID课程ID - 讲师ID在这个例子中学生ID不是一个超键因为它不能唯一标识每一行。为了使数据库满足BCNF我们需要将其分解成两个表学生课程表学生ID课程ID课程讲师表课程ID讲师ID通过这种分解我们确保了每个表都满足BCNF的要求从而减少了数据冗余提高了数据的一致性和完整性。假设仓库管理关系表(仓库号存储物品号管理员号数量)满足一个管理员只在一个仓库工作一个仓库可以存储多种物品则存在如下关系(仓库号存储物品号)——(管理员号数量)(管理员号存储物品号)——(仓库号数量)所以(仓库号存储物品号)和(管理员号存储物品号)都是仓库管理关系表的候选码表中唯一非关键字段为数量它是符合第三范式的。但是由于存在如下决定关系(仓库号)——(管理员号)(管理员号)——(仓库号)即存在关键字段决定关键字段的情况因此其不符合BCNF。把仓库管理关系表分解为两个关系表仓库管理表(仓库号管理员号)和仓库表(仓库号存储物品号数量)这样这个数据库表是符合BCNF的并消除了删除异常、插入异常和更新异常。四、第四范式的优点和缺点第四范式的主要优点包括减少数据冗余、提高数据一致性、简化数据维护。由于消除了多值依赖数据冗余得以减少数据的一致性和完整性得以提高。例如在更新数据时只需要更新一个表中的数据而不需要同步更新多个表。这大大简化了数据的维护工作。然而第四范式也有一些缺点增加了表的数量、复杂了查询操作。由于将一个表拆分成多个表表的数量增加了查询操作也变得更加复杂。例如为了获取一个学生的所有信息需要进行多次表连接操作这会增加查询的复杂性和开销。5.第四范式4NF第一范式要求所有的字段都是原子的即每个字段只能包含一个值。第二范式在第一范式的基础上要求消除部分依赖即非键属性必须完全依赖于主键。第三范式要求消除传递依赖即非键属性不能依赖于其他非键属性。BCNF是第三范式的强化版本要求每个非键属性都完全依赖于候选键。第四范式则在这些基础上进一步消除多值依赖。多值依赖的定义和例子多值依赖是指在一个关系表中存在一个非键属性集合的值依赖于另一个非键属性集合的值而不是主键。例如考虑一个表格其中存储了学生、课程和兴趣爱好。如果一个学生可以选修多门课程同时也可以有多个兴趣爱好这样的表格就会存在多值依赖。假设一个学生可以选修多门课程同时也可以有多个兴趣爱好这种情况下一个学生的课程和兴趣爱好之间是独立的。如果我们将这些信息存储在一个表中会导致大量的冗余数据。例如在上面的表格中学生1选修了数学和英语并且有足球和篮球两个兴趣爱好。这个表格存在多值依赖因为课程和兴趣爱好是独立的但它们都依赖于学生ID。这种设计会导致数据冗余和更新异常。第四范式的要求为了消除多值依赖表格需要满足第四范式的要求。第四范式要求一个表格中不应存在多值依赖即一个属性的值不应依赖于另一个非键属性的值。换句话说每个非键属性必须直接依赖于主键而不能依赖于其他非键属性。要达到第四范式需要将具有多值依赖的表拆分成多个表使得每个表只包含一个非键属性集合。例如上述例子中的表格可以拆分成两个表通过这种拆分我们消除了多值依赖减少了数据冗余同时提高了数据的一致性和完整性。总结关于数据表的设计有三个范式需要遵循第一范式数据库的每一列都是不可分割的原子数据项不可再分的最小数据单元而不能是集合、数组、多条记录等非原子数据项。第二范式确保每列属性都和主键完全依赖在联合主键的情况下非主键部分不应该依赖于部分主键。每张表只描述一件事第三范式确保每列都和主键列直接相关不依赖于非主键列而不是间接相关不存在传递依赖扩充人们常说要遵循数据库方式教材讲义里也是。但是实际工作中为了提高查询速度需要反范式设计。反范式设计是一种与传统规范化设计相对的数据库设计方法它允许在数据库中引入冗余数据以提高查询性能或简化查询操作。反范式设计的主要思想是通过增加冗余数据来消除关系型数据库中的连接操作从而提高查询性能。大数据技术里面有时用到反范式 比如elasticsearch如大型数据仓库、报表生成和实时大数据处理等情况。然而需要注意的是反范式设计也带来了数据冗余和更新复杂性的问题需要在权衡性能和数据一致性之间进行合理的选择。