重学MySQL之范式

重新学习 MySQL 的基础知识,本章内容为数据库的第一范式,第二范式和第三方式的讲解

数据库设计的范式

简要的地说,范式就是设计数据库时,需要遵循的一些规范。通过遵从不同的规范要求,设计出合理的关系型数据库。各种范式呈递次规范,越高的范式数据库冗余越小,要遵循后面的范式,必须先遵循前边的所有范式。

目前数据库共有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BNF)、第四范式(4NF)、第五范式(5NF,又称完美范式)。

在实际数据库设计过程中,符合前三个范式就可以解决大部分的问题,因此本章着重讲解前三个范式。

例子分析

在讲解各个范式的概念之前,我决定先引出需要讲解的例子,通过问题来引出范式的概念

学号 姓名 系主任 课程名称 分数
10010 张三 计算机系陈续缘 数据结构 90
10010 张三 计算机系陈续缘 数据库 85
10010 张三 计算机系陈续缘 编译原理 77
10020 李四 经管系刘快计 大学英语 86
10020 李四 经管系刘快计 高等数学 80
10020 李四 经管系刘快计 工商管理 82
10030 王五 自动化系赵是福 大学物理 72
10030 王五 自动化系赵是福 电路 75
10030 王五 自动化系赵是福 单片机技术 79

第一范式

上表为学生的选课表。观察表的结构,可以发现学号,姓名、课程名称和分数,在字面意义上都是无法继续被分割的数据项,倘如我们观察 系主任 这一列,就会发现系名和主任名称是连在一起的,很明显可以对该列进行简化,比如新增一个 系名

学号 姓名 系名 系主任 课程名称 分数
10010 张三 计算机系 陈续缘 数据结构 90
10010 张三 计算机系 陈续缘 数据库 85
10010 张三 计算机系 陈续缘 编译原理 77
10020 李四 经管系 刘快计 大学英语 86
10020 李四 经管系 刘快计 高等数学 80
10020 李四 经管系 刘快计 工商管理 82
10030 王五 自动化系 赵是福 大学物理 72
10030 王五 自动化系 赵是福 电路 75
10030 王五 自动化系 赵是福 单片机技术 79

此时,该表的每一列都成了不可分割的原子数据项,对于这类数据表,称之为符合第一范式(1NF)

这也是绝大部分数据表都符合的基本范式。那么再次观察这张数据表存在哪些问题?

  1. 存在非常严重的数据冗余(重复):姓名系名系主任
  2. 数据添加存在问题:添加新开设的系和系主任时,数据不合法(因为新增一条数据还需要 学号姓名课程分数
  3. 数据删除存在问题:张三毕业时,删除数据会把 系名系主任 的数据一起删除

第二范式

在介绍第二范式之前,通过观察符合第一范式的数据表来弄清楚几个概念:

  • 函数依赖:可以发现通过 学号,可以确定 姓名。因此说 姓名 依赖于 学号,记为 学号 --> 姓名

    概念 如果通过 A 属性(属性组)的值,能够唯一确定 B 属性的值,则称 B 依赖于 A

  • 完全函数依赖:通过 学号课程名称 可以唯一确定该学生的 分数。因此说 分数 完全函数依赖于属性组 学号课程名称,记为 (学号, 课程名称) --> 分数

    概念 如果属性值 B 的确定需要依赖于属性组 A 的所有值,则称 B 完全依赖于属性组 A

  • 部分函数依赖:因为 学号 可以唯一确定 姓名,那么属性组 学号课程名称 也可以确定 姓名,但是 课程名称 不能确定 姓名,因此说 姓名 部分函数依赖于属性组 学号课程名称,记为 (学号, 课程名称) --> 姓名

    概念 如果属性值 B 的确定需要依赖于属性组 A 的部分值,则称 B 部分依赖于属性组 A

  • 传递函数依赖: 学号 可以确定 系名系名 可以确定 系主任 , 因此通过传递关系 学号 可以确定 系主任, 因此称 系主任 传递函数依赖与 学号 ,记为 学号 --> 系名, 系名 --> 系主任

    概念 如果通过 A 属性(属性组)可以唯一确定 B 属性(属性组),再通过 B 属性(属性组)可以唯一确定 C 属性(属性组),则称 C 传递函数依赖于 A

  • (候选)码:类似于数据表的主键,数据表中的其他所有属性都完全依赖于(候选)码,或者说完全依赖于主键。观察符合第一范式的数据表,可以发现上表的(候选)码为 (学号,课程名称)
    • 主属性:(候选)码属性组中的所有属性
    • 非主属性:除(候选)码属性组以外的属性

我们要设计数据表符合第二范式,就需要在符合第一范式的基础上,消除非主属性对(候选)码的部分函数依赖

在上表中, 分数 完全函数依赖于 (学号,课程名称)姓名系名系主任 部分依赖于 (学号,课程名称) ,因此我们需要将 姓名系名系主任 三列从原表中提出,以次来消除选课表中非主属性对(候选)码的部分函数依赖,并另建一个学生表来存放这些数据,修改如下

选课表

学号 姓名 课程名称 分数
10010 张三 数据结构 90
10010 张三 数据库 85
10010 张三 编译原理 77
10020 李四 大学英语 86
10020 李四 高等数学 80
10020 李四 工商管理 82
10030 王五 大学物理 72
10030 王五 电路 75
10030 王五 单片机技术 79

学生表

学号 姓名 系名 系主任
10010 张三 计算机系 陈续缘
10020 李四 经管系 刘快计
10030 王五 自动化系 赵是福

此时第二张表的(候选)码就为 学号,其他属性都完全依赖于 学号,因此现在这两张表的设计都符合第二范式,此外我们还解决了之前遇到的第一个问题,解决了数据冗余的问题。但是后两个问题任然存在,如果要添加新的 系名系主任 ,任然无法添加到学生表中,此外当张三毕业后,对应的 系名系主任 的数据就会被删除。

第三范式

符合第三范式,就需要在符合第二范式的基础之上消除传递依赖

观察上面新建的学生表,可以发现,其实 系主任 依赖于 系名 , 而 系名 又依赖于 学号 ,因此学生表中存在 系主任学号 的传递函数依赖

因此我们需要将 系名系主任 从原表中提出,以此来消除学生表中的传递函数依赖,并新建一个系表来存储数据

学生表

学号 姓名 系名
10010 张三 计算机系
10020 李四 经管系
10030 王五 自动化系

系表

系名 系主任
计算机系 陈续缘
经管系 刘快计
自动化系 赵是福

此时,如果我们需要新添系名和系主任的数据,就不会出现错误了;当张三毕业后,系名和系主任的数据也不会消失,从而最后两个问题也得到了解决。

文章作者: Inno Fang
文章链接: https://innofang.github.io/2020/03/28/重学MySQL之范式/
版权声明: 本博客所有文章除特别声明外,均采用 CC BY-NC-ND 4.0 许可协议。转载请注明来自 Inno's Blog