参照B站SQL案例分析:数据库三范式,不是你想的那样!所写笔记
现有如下例表(表一)
部门名称 | 部门地址 | 姓名 | 性别 | 经理 | 职位 | 最低月薪 | 最高月薪 | 电话 |
---|---|---|---|---|---|---|---|---|
行政管理部 | 办公楼一层 | 刘一 | 男 | 总经理 | 24000 | 50000 | 61238888,12312345678 | |
行政管理部 | 办公楼一层 | 刘二 | 男 | 刘一 | 副总经理 | 20000 | 30000 | 6123647,12312345656 |
行政管理部 | 办公楼一层 | 张三 | 男 | 刘一 | 副总经理 | 20000 | 30000 | 61238156 |
财政部 | 办公楼二层 | 王一 | 女 | 刘一 | 财务经理 | 10000 | 20000 | 61237825 |
财政部 | 办公楼二层 | 王二 | 女 | 王一 | 总经理 | 5000 | 8000 | 61238823 |
在这张表中有如下问题
- 数据冗余(重复存储数据,如部门名称中行政管理部存储多次)
数据冗余带来的问题
- 插入异常(该表以员工为对象,举例如新成立部门,但该部门无员工,则无法进行对该部门的信息管理与维护)
- 更新异常(在对信息进行更正时需要大量操作,容易出错,如部门地址信息更改,表中数据更正时会需要逐条更正,在更正过程中,容易漏掉信息未更正)
- 删除异常(与插入异常同理)
规范化(normalization)
(用于数据库设计的一系列原理与技术)
作用:减少数据冗余,增加数据完整性和一致性。
第一范式(1NF)
概念:表中字段都是不可再分的原子属性*,同时表要有一个主键。
(表二)
部门名称 | 部门地址 | 姓名 | 性别 | 经理 | 职位 | 最低月薪 | 最高月薪 | 电话 | 个人电话 |
---|---|---|---|---|---|---|---|---|---|
行政管理部 | 办公楼一层 | 刘一 | 男 | 总经理 | 24000 | 50000 | 61238888 | 12312345678 | |
行政管理部 | 办公楼一层 | 刘二 | 男 | 刘一 | 副总经理 | 20000 | 30000 | 6123647 | 12312345656 |
行政管理部 | 办公楼一层 | 张三 | 男 | 刘一 | 副总经理 | 20000 | 30000 | 61238156 | |
财政部 | 办公楼二层 | 王一 | 女 | 刘一 | 财务经理 | 10000 | 20000 | 61237825 | |
财政部 | 办公楼二层 | 王二 | 女 | 王一 | 会计 | 5000 | 8000 | 61238823 |
复合主键 | 拆分字段 |
---|
假设部门加姓名不重合,将部门加姓名作为复合主键
第一范式的标准(原子属性*根据业务需求进行判断)
例如:
编号 | 姓名 | 性别 | 编号 | 姓氏 | 名字 | 性别 | |
---|---|---|---|---|---|---|---|
1 | 刘备 | 男 | — | 1 | 刘 | 备 | 男 |
2 | 关羽 | 男 | — | 2 | 关 | 羽 | 男 |
3 | 张飞 | 男 | — | 3 | 张 | 飞 | 男 |
在不要求姓氏分开存储的业务中左边的是合理的,在要求姓氏分开储存的业务中右边是合理的。
像是原子属性*原子化学层面不可分,物理层面可分
符不符合,根据业务需求而定
像是快递地址,在填写时往往要求分开填写省份、市区、详细地址符合第一范式。
但当地址只是作为一项信息填写时,可以将整个地址作为一个符合原子属性的元素符合第一范式
第二范式(2NF)
(首先需要满足第一范式)
非主键字段必须完全依赖主键字段,不能只依赖主键的一部分
在(表二)中,我们将部门名称和姓名作为复合主键,但部门地址不完全依赖主键整体,它只依赖于部门名称这一部分信息。(冗余)
我们可以把部分依赖的部分单独提出新建一表,并进行优化(添加一个并没有实际业务意义的主键,来建立两表联系{在(表三)中为部门编号,(表三)中建立工号作为主键,对每个员工进行唯一标识})
(表三)(1)
部门编号 | 工号 | 姓名 | 性别 | 经理 | 职位 | 最低月薪 | 最高月薪 | 电话 | 个人电话 |
---|---|---|---|---|---|---|---|---|---|
1 | 1 | 刘一 | 男 | 总经理 | 24000 | 50000 | 61238888 | 12312345678 | |
1 | 2 | 刘二 | 男 | 1 | 副总经理 | 20000 | 30000 | 6123647 | 12312345656 |
1 | 3 | 张三 | 男 | 1 | 副总经理 | 20000 | 30000 | 61238156 | |
3 | 7 | 王一 | 女 | 1 | 财务经理 | 10000 | 20000 | 61237825 | |
3 | 8 | 王二 | 女 | 7 | 会计 | 5000 | 8000 | 61238823 |
(表三)(2)
部门编号 | 部门名称 | 部门地址 |
---|---|---|
1 | 行政管理部 | 办公楼一层 |
2 | 财务部 | 办公楼二层 |
第三范式(3NF)
(满足第二范式)
非主键字段不能依赖于其他非主键字段
在(表三)(1)中职位与最低月薪、最高月薪是相挂钩的,同第二范式一样,单独提出建表。
(表四)(1)
部门编号 | 工号 | 姓名 | 性别 | 经理 | 职位编号 | 电话 | 个人电话 |
---|---|---|---|---|---|---|---|
1 | 1 | 刘一 | 男 | 1 | 61238888 | 12312345678 | |
1 | 2 | 刘二 | 男 | 1 | 2 | 6123647 | 12312345656 |
1 | 3 | 张三 | 男 | 1 | 2 | 61238156 | |
3 | 7 | 王一 | 女 | 1 | 3 | 61237825 | |
3 | 8 | 王二 | 女 | 7 | 4 | 61238823 |
(表四)(2)
部门编号 | 部门名称 | 部门地址 |
---|---|---|
1 | 行政管理部 | 办公楼一层 |
2 | 财务部 | 办公楼二层 |
(表四)(3)
职位编号 | 职位 | 最低月薪 | 最高月薪 |
---|---|---|---|
1 | 总经理 | 24000 | 50000 |
2 | 副总经理 | 20000 | 30000 |
3 | 财务经理 | 10000 | 20000 |
4 | 会计 | 5000 | 8000 |
范式的NF增加———大表提小表
外键约束(数据过多,关系错杂,难以维护){类似上表中的部门编号、工号、职位编号}
是数据库用于参照完整型的约束,可以保证数据的完整性和一致性。
反规范化
为了提高查询性能可以降低规范化的级别,也就是反规范化(denormalization)
常用反规范化技术
- 增加冗余字段
- 增加计算列(直接查询,增长效率,增加内存压力)
- 将小表合成大表
其实就是将规范化的操作颠倒