MySQL-数据库的设计范式

        

 

目录

一、键和相关属性的概念

二、第一范式

三、第二范式

四、第三范式

五、反范式化

5.1 规范与性能的关系

5.2 反范式带来的问题

5.3 适用场景

六、巴斯-科德范式BCNF

七、ER模型

7.1 三大要素

7.2 关系类型 

7.3 总结

---

        在关系型数据库中，关于数据表设计的基本原则、规则就称为范式。可以理解为，一张数据表的设计结构需要满足的某种设计标准的级别 。要想设计一个结构合理的关系型数据库，必须满足一定的范式。

        目前关系型数据库有六种常见范式，按照范式级别，从低到高分别是：第一范式（1NF）、第二范式 （2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

一、键和相关属性的概念

有两个表：

        球员表(player) ：球员编号 | 姓名 | 身份证号 | 年龄 | 球队编号

        球队表(team) ：球队编号 | 主教练 | 球队所在地

超键 ：对于球员表来说，超键就是包括球员编号或者身份证号的任意组合，比如（球员编号） （球员编号，姓名）（身份证号，年龄）等。

候选键 ：就是最小的超键，对于球员表来说，候选键就是（球员编号）或者（身份证号）。

主键 ：我们自己选定，也就是从候选键中选择一个，比如（球员编号）。

外键 ：球员表中的球队编号。

主属性 、 非主属性 ：在球员表中，主属性是（球员编号）（身份证号），其他的属性（姓名） （年龄）（球队编号）都是非主属性。

二、第一范式

        表的每个属性必须具有原子（单个）值，简单来说就是每个属性都不可再分。

        比如下面的表，就不符合第一范式，用户信息属性是可再分的。

 经过下面修改后的表满足第一范式：

三、第二范式

        完全依赖关系：

        成绩表 （学号，课程号，成绩）关系中，（学号，课程号）可以决定成绩，但是学号单独不能决定成绩，课程号单独也不能决定成绩，所以“（学号，课程号）→成绩”就是 完全依赖关系 。

        对于第二范式，要求数据表里的所有非主属性都要和该数据表的 主属性 有完全依赖关系；如果有哪些非主属性只和主属性的一部分有关的话，它就不符合第二范式。 

        假如有一个比赛表：

(球员编号, 比赛编号) → (姓名, 年龄, 比赛时间, 比赛场地，得分)

        它不满足第二范式，因为字段间存在下面的关系：

(球员编号) → (姓名，年龄)

(比赛编号) → (比赛时间, 比赛场地)

        这样可能导致数据冗余、插入异常、更新异常、删除异常的问题。

        我们重新设计，分为下面三个表，就都满足第二范式：

四、第三范式

         第三范式就是指表中的所有字段不但要能唯一地被主关键字所标识,而且它们之间还必须相互独立,不存在其他的函数关系。

        假如我们有下面的商品表：

         商品类别名称与商品类别id之间存在联系，该表不满足第三范式。

        修改之后分为两张表，分别为商品类别表、商品表。此时满足第三范式。

五、反范式化

5.1 规范与性能的关系

1. 为满足某种商业目标 , 数据库性能比规范化数据库更重要

2. 在数据规范化的同时 , 要综合考虑数据库的性能

3. 通过在给定的表中添加额外的冗余字段，以大量减少需要从中搜索信息所需的时间

4. 通过在给定的表中插入计算列，以方便查询

        假如员工的信息存储在 employees 表 中，部门信息存储在 departments 表 中。通过 employees 表中的 department_id字段与 departments 表建立关联关系。如果要查询一个员工所在部门的名称：

select employee_id,department_name
from employees e join departments d
on e.department_id = d.department_id;

        如果经常需要进行这个操作，连接查询就会浪费很多时间。可以在 employees 表中增加一个冗余字段 department_name，这样就不用每次都进行连接操作了。

        有时候如果我们想要提升查询效率，可以允许适当的数据冗余，就违反了范式。

5.2 反范式带来的问题

-存储空间变大了

-一个表中字段做了修改，另一个表中冗余的字段也需要做同步修改，否则 数据不一致

-若采用存储过程来支持数据的更新、删除等额外操作，如果更新频繁，会非常 消耗系统资源 -在 数据量小 的情况下，反范式不能体现性能的优势，可能还会让数据库的设计更加复杂

5.3 适用场景

         当冗余信息有价值或者能 大幅度提高查询效率 的时候，我们才会采取反范式的优化。比如历史数据、历史快照、频繁查询但不经常修改的信息

六、巴斯-科德范式BCNF

        它在 3NF 的基础上消除了主属性对候选键的部分依赖或者传递依赖关系。

        有一个 学生导师表 ，其中包含字段：学生ID，专业，导师，专业GPA，这其中学生ID和专业是联合主键。

         这个表的设计满足3NF，但是这里存在另一个依赖关系，“专业”依赖于“导师”，也就是说每个导师只做一个专业方面的导师，只要知道是哪个导师，我们自然就知道是哪个专业的了。

        所以这个表的部分主键Major依赖于非主键属性Advisor，那么我们可以进行以下的调整，拆分成2个表：

        学生导师表：

         导师表：

七、ER模型

7.1 三大要素

        ER 模型中有三个要素，分别是实体、属性和关系。

-实体 ，可以看做是数据对象，往往对应于现实生活中的真实存在的个体。在 ER 模型中，用 矩形 来表 示。实体分为两类，分别是 强实体 和 弱实体 。强实体是指不依赖于其他实体的实体；弱实体是指对另 一个实体有很强的依赖关系的实体。

-属性 ，则是指实体的特性。比如超市的地址、联系电话、员工数等。在 ER 模型中用 椭圆形 来表示。

-关系 ，则是指实体之间的联系。比如超市把商品卖给顾客，就是一种超市与顾客之间的联系。在 ER 模 型中用 菱形 来表示。

7.2 关系类型 

        关系又可以分为 3 种类型，分别是 一对一、一对多、多对多。

一对一 ：指实体之间的关系是一一对应的，比如个人与身份证信息之间的关系就是一对一的关系。一个 人只能有一个身份证信息，一个身份证信息也只属于一个人。

一对多 ：指一边的实体通过关系，可以对应多个另外一边的实体。相反，另外一边的实体通过这个关 系，则只能对应唯一的一边的实体。比如说，我们新建一个班级表，而每个班级都有多个学生，每个学 生则对应一个班级，班级对学生就是一对多的关系。

多对多 ：指关系两边的实体都可以通过关系对应多个对方的实体。比如在进货模块中，供货商与超市之 间的关系就是多对多的关系，一个供货商可以给多个超市供货，一个超市也可以从多个供货商那里采购 商品。再比如一个选课表，有许多科目，每个科目有很多学生选，而每个学生又可以选择多个科目，这 就是多对多的关系。

7.3 总结