目录
那你平时设计的时候都遵守三大范式吗?不遵守的话什么时候会去突破范式?
遵守范式化设计和不遵守范式化设计,也就是反范式化设计的优缺点?
目前关系数据库有六种范式:
- 第一范式(1NF)
- 第二范式(2NF)
- 第三范式(3NF)
- BCNF 又称巴斯-科德范式
- 第四范式 (4NF)
- 第五范式(5NF,又称完美范式)
最常接触到的是前三个范式
范式具体是用来干嘛的?
我们在设计关系数据库时,要遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式,各种范式呈递次规范,越高的范式数据库冗余越小,简单来说就是规范数据库的设计
重要的概念:
- 函数依赖(functional dependency) :若在一张表中,在属性(或属性组)X 的值确定的情况下,必定能确定属性 Y 的值,那么就可以说 Y 函数依赖于 X,写作 X → Y。
- 部分函数依赖(partial functional dependency) : 学生信息表 R 中(学号,身份证号,姓名)当然学号属性取值是唯一的,在 R 关系中,(学号,身份证号)->(姓名),(学号)->(姓名),(身份证号)->(姓名);所以姓名部分函数依赖与(学号,身份证号);
- 完全函数依赖(Full functional dependency) :学生基本信息表 R(学号,班级,姓名)假设不同的班级学号有相同的,班级内学号不能相同,在 R 关系中,(学号,班级)->(姓名),但是(学号)->(姓名)不成立,(班级)->(姓名)不成立,所以姓名完全函数依赖与(学号,班级);
- 传递函数依赖 : 在关系 R(学号 , 姓名, 系名,系主任)中,学号 → 系名,系名 → 系主任,所以存在非主属性系主任对于学号的传递函数依赖。
第一范式(1NF)
是对属性的 原子性 的要求,要求属性具有原子性,不可再分解;
如这个user表就不符合第一范式,因为region列不具有原子性,能拆分成省份、市和具体地址
正确做法:
第二范式(2NF)
2NF是对记录的 唯一性 ,要求记录有惟一标识,即不存在部分依赖;
这个表明显说明了两个事务:学生信息, 课程信息;
由于非主键字段必须依赖主键,这里学分依赖课程号,名字依赖学号,所以不符合二范式。
这么做可能会存在问题:
- 数据冗余:,每条记录都含有相同信息;
- 删除异常:删除所有学生成绩,就把课程信息全删除了;
- 插入异常:学生未选课,无法记录进数据库;
- 更新异常:调整课程学分,所有行都调整。
正确做法:
- 学生:Student(学号, 姓名);
- 课程:Course(课程号, 学分);
- 选课关系:StudentCourse(学号, 课程号, 成绩)。
第三范式(3NF)
3NF是对字段的 冗余性 ,要求任何字段不能由其他字段派生出来,它要求字段没有冗余,即不存在传递依赖;
因为存在依赖传递: (学号) → (姓名)→(所在学校) → (学校电话) 。
可能会存在问题:
-
数据冗余:有重复值;
-
更新异常:有重复的冗余信息,修改时需要同时修改多条记录,否则会出现数据不一致的情况 。
正确做法:
-
学生:(学号, 姓名, 年龄, 所在学院);
-
学院:(学院, 电话)。
BCNF:
某些特殊情况下,即使关系模式符合3NF的要求,仍然存在着插入异常,修改异常与删除异常的问题。BCNF由Boyce与Codd提出,通常被认为是修正的第三范式。
巴斯-科德范式即在满足第三范式(3NF)基础上,任何非主属性不能对主键子集依赖(即在3NF基础上,消除主属性对候选码的部分函数依赖和传递函数依赖)。
BC范式既检查非主属性,又检查主属性。当只检查非主属性时,就成了第三范式。满足BC范式的关系都必然满足第三范式。或者还可以换一种说法:若一个关系达到了第三范式,并且它只有一个候选码,或者它的每个候选码都是单属性,则该关系自然达到BC范式。
一般来说,一个数据库设计符合3NF或BCNF就可以了。
总结
- 1NF:属性不可再分。
- 2NF:1NF 的基础之上,消除了非主属性对于码的部分函数依赖。
- 3NF:3NF 在 2NF 的基础之上,消除了非主属性对于码的传递函数依赖
第一范式(1NF)
非码的非平凡 | ↓ 消除非主属性对码的部分函数依赖
第二范式(2NF)
↓ 消除非主属性对码的传递函数依赖
第三范式(3NF)
↓ 消除主属性对码的部分和传递函数依赖
BC范式(BCNF)
↓ 消除非平凡且非函数依赖的多值依赖
第四范式(4NF)
↓消除不是由候选码所蕴含的连接依赖
第五范式(5NF)
那你平时设计的时候都遵守三大范式吗?不遵守的话什么时候会去突破范式?
一般只遵守第三范式(3NF)。
没有冗余的数据库设计可以做到三大范式都遵守。但是,没有冗余的数据库未必是最好的数据库,有时为了提高运行效率,就必须降低范式标准,适当保留冗余数据。
具体做法:在概念数据模型设计时遵守第三范式,降低范式标准的工作放到物理数据模型设计时考虑。
降低范式就是增加字段,允许冗余,达到以空间换时间的目的。
遵守范式化设计和不遵守范式化设计,也就是反范式化设计的优缺点?
范式化设计的优点在于:
- 可以尽量的减少数据冗余,数据表更新快体积小
- 范式化的更新操作比反范式化更快
- 范式化的表通常比反范式化更小
缺点在于:
- 对于查询需要对多个表进行关联(导致性能降低)
- 更难进行索引优化
反范式化优点在于:
- 可以减少表的关联
- 可以更好的进行索引优化
缺点:
- 存在数据冗余及数据维护的异常
- 对数据的修改需要更多的成本
范式和反范式
街上新开了一家面馆,面馆有汤面和炒面。汤面统一价格是13元,炒面统一价格是15元。汤面里有牛肉拉面,西红柿鸡丝面。炒面有蛋炒面和尖椒炒面。我们来设计一份表。
CREATE TABLE `noodle` (
`name` varchar(100) NOT NULL COMMENT '名称',
`type` varchar(100) NOT NULL COMMENT '种类',
`price` int NOT NULL COMMENT '价格'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='拉面信息';
select * from noodle;
name |type|price|
-----+----+-----+
蛋炒面 |炒面 | 15|
尖椒炒面 |炒面 | 15|
牛肉面 |汤面 | 13|
鸡蛋肉丝面|汤面 | 13|
复制代码
上面这种设计形式就是反范式,其将包含有传递数据的所有数据都写在一张表中。简单直接。 我们可以直观的看到 名称-种类-价格之间的关联关系。但是有冗余数据不可避免。
范式
还是上面的例子,我们直接来设计表结构。这次将传递数据分离开来。
CREATE TABLE `noodle` (
`name` varchar(100) NOT NULL COMMENT '名称',
`type` varchar(100) NOT NULL COMMENT '种类',
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='拉面信息';
CREATE TABLE `noodle_type` (
`type` varchar(100) NOT NULL COMMENT '种类',
`price` int NOT NULL COMMENT '价格'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='拉面种类信息';
select * from noodle;
name |type|
-----+----+
蛋炒面 |炒面 |
尖椒炒面 |炒面 |
牛肉面 |汤面 |
鸡蛋肉丝面|汤面 |
select * from noodle_type;
name |type|price|
-----+----+-----+
炒面 | 15|
汤面 | 13|
复制代码
上面这种设计形式就是范式,每张表都是单独的。没有冗余数据和传递依赖。每次修改炒面价格的时候只需要修改type表。而不是像反范式一样修改每一行炒面的价格。
总结
设计数据库表时,一定要灵活。没必要将所有表都拆的零碎。但是也没必要将所有的数据都写到一张表中,几十列反而不方便查询数据。将范式和反范式相结合才是最好的设计方式。