DB - 数据库设计

一、多表之间的关系

1. 多表关系分类

• 一对一
  例如：公民和身份证号
  分析：一个人只能有一个身份证号，一个身份证号只能对应一个人。
  实现方式：一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键。
• 一对多 / 多对一
  例如：部门和员工
  分析：一个部门可以有多个员工，一个员工只能属于一个部门。
  实现方式：在多的一方建立外键，指向一的一方的主键。
• 多对多
  例如：学生和课程
  分析：一个学生可以选择很多们课，一门课程可以被很多学生选择。
  实现方式：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键。

2. 综合案例

案例分析：旅游网站的数据存储

• 创建旅游线路分类表 tab_category

  /* 
  cid旅游线路分类主键，自动增长
  cname 旅游线路分类名称非空，唯一，字符串 100
  */
  CREATE TABLE tab_category (
  	cid INT PRIMARY KEY AUTO_INCREMENT,
  	cname VARCHAR(100) NOT NULL UNIQUE
  );
  

• 创建旅游线路表 tab_route

  /* 
  rid 旅游线路主键，自动增长
  rname 旅游线路名称非空，唯一，字符串 100
  price 价格
  rdate 上架时间，日期类型
  cid 外键，所属分类
  */
  CREATE TABLE tab_route (
  	rid INT PRIMARY KEY AUTO_INCREMENT,
  	rname VARCHAR(100) NOT NULL UNIQUE,
  	price DOUBLE,
  	rdate DATE,
  	cid INT,
  	FOREIGN KEY (cid) REFERENCES tab_category(cid)
  );
  

• 创建用户表 tab_user

  /* 创建用户表 tab_user
  uid 用户主键，自增长
  username 用户名长度 100，唯一，非空
  password 密码长度 30，非空
  name 真实姓名长度 100
  birthday 生日
  sex 性别，定长字符串 1
  telephone 手机号，字符串 11
  email 邮箱，字符串长度 100
  */
  CREATE TABLE tab_user (
  	uid INT PRIMARY KEY AUTO_INCREMENT,
  	username VARCHAR(100) UNIQUE NOT NULL,
  	PASSWORD VARCHAR(30) NOT NULL,
  	NAME VARCHAR(100),
  	birthday DATE,
  	sex CHAR(1) DEFAULT '男',
  	telephone VARCHAR(11),
  	email VARCHAR(100)
  );
  

• 创建收藏表tab_favorite

  /* 创建收藏表tab_favorite
  rid 旅游线路 id，外键
  date 收藏时间
  uid 用户 id，外键
  rid 和 uid 不能重复，设置复合主键，同一个用户不能收藏同一个线路两次
  */
  CREATE TABLE tab_favorite (
  	rid INT, -- 线路id
  	DATE DATETIME,
  	uid INT, -- 用户id
  	-- 创建复合主键
  	PRIMARY KEY(rid,uid), -- 联合主键
  	FOREIGN KEY (rid) REFERENCES tab_route(rid),
  	FOREIGN KEY(uid) REFERENCES tab_user(uid)
  );
  

二、设计范式

1. 设计范式概念

（摘自百度百科）：设计数据库时，需要遵循的一些规范。要遵循后边的范式要求，必须先遵循前边的所有范式要求。

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。

2. 设计范式分类

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

以下仅介绍前三种设计范式。通常，满足前三种设计范式的数据库可以满足绝大多数的数据存储需求。

1. 第一范式（1NF）：每一列都是不可分割的原子数据项
2. 第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于码（在1NF基础上消除非主属性对主码的部分函数依赖）
3. 第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

3. 理解设计范式

案例分析：表格的设计和优化

观察以下表格，从设计范式的角度出发，分析表格数据的存储方式存在哪些问题？如何改进？

显然，这张表格不满足第一范式。因为，对于 “系” 字段，它不是一个原子数据项，它可以继续拆分成 “系名” 和 “系主任” 两个字段。所以，该表目前不满足所有的设计范式。

为了使表格满足第一范式，需要修改 “系” 字段：

此时，表格已经满足第一范式，所有字段都是不可分割的原子数据项。然而，这张表依然存在一些问题：

1. 存在非常严重的数据冗余（重复）：姓名、系名、系主任
2. 数据添加存在问题：添加新开设的系和系主任时，数据不合法
   
3. 数据删除存在问题：张无忌同学毕业了，删除数据，会将系的数据一起删除。
   

这些问题只有通过引入第二范式和第三范式才能解决。为了理解第二范式，需要先掌握以下概念：

1. 函数依赖：A–>B，如果通过A属性（属性组）的值，可以确定唯一B属性的值。则称B依赖于A。

   例如：学号-->姓名；（学号，课程名称） --> 分数
   

2. 完全函数依赖：完全函数依赖：A–>B， 如果A是一个属性组，则B属性值得确定需要依赖于A属性组中所有的属性值。

   例如：（学号，课程名称） --> 分数
   

3. 部分函数依赖：A–>B， 如果A是一个属性组，则B属性值得确定只需要依赖于A属性组中某一些值即可。

   例如：（学号，课程名称） -- > 姓名
   

4. 传递函数依赖：A–>B, B – >C . 如果通过A属性（属性组）的值，可以确定唯一B属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值，则称C传递函数依赖于A。

   例如：学号-->系名，系名-->系主任
   

5. 码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性（属性组）为该表的码。

   例如：该表中码为（学号，课程名称）
   

6. 主属性（码属性）：码属性组中的所有属性。
7. 非主属性（非码属性）：除去码属性组的属性。

结合第二范式的描述：在1NF基础上消除非主属性对主码的部分函数依赖。在满足INF的表中，码属性组是（学号，课程名称），只有 “分数” 完全依赖于主码；而 “姓名”、“系名” 和 “系主任” 字段只依赖于 “学号”，它们都属于部份依赖于主码，显然当前的表格不满足2NF。

为了使数据存储进一步满足第二范式，首先需要对表格的内容进行拆分：将原表中局部依赖于主码组的字段单独抽取出来，将这些字段与它们完全依赖的 “学号” 字段组在一起建一张新表：

此时，右表存在天然的数据冗余，将冗余记录去除后发现：左表存储了学生的课程和分数，右表存储了学生的基础信息。将左表称为 “选课表”，右表称为 “学生表”。

此时，对于这两张表而言，它们中的所有字段都属于原子数据项，且 “分数” 完全依赖于（“学号”，“课程名称”），“姓名”、“系名”、“系主任” 完全依赖于 “学号”。两张表均满足第二范式。

至此，回头思考之前发现的 3 个问题，发现第 1 个问题已经解决，数据不再有严重的冗余情况。但是，对于 2，3 问题，无论是添加新的专业还是去掉某个学生的记录，都会是表格再次处于不合法的状态。解决这两个问题，需要借助第三范式的设计思路。

根据第三范式的描述可知，需要在 2NF 基础上消除表格中的传递依赖。观察学生表发现：“系名” 完全依赖于 “学号”，而 “系主任” 虽然也完全依赖于 “学号”，但同时也完全依赖于 “系名”。因此，“学号”、“系名”、“系主任”构成了传递依赖。

为了进一步使数据存储满足第三范式，将学生表中的 “系名” 和 “系主任” 字段提取，构造一张新表称为 “系表”，主键设为 “系名”。原来的学生表只保存系表中的主键，并剔除系表中的冗余记录。

此时，在消除传递依赖后，对学生信息的增删改不再影响教师信息的存储，而对教师信息的增删改也不再影响学生信息的存储。整体数据的存储满足第三范式。

4. 设计范式小结

• 1NF 规定表格中的所有字段必须不可再分；
• 2NF 规定在 1NF 的基础上，消除所有关于主属性（组）的部份依赖。一张表只描述一件事情；
• 3NF 规定在 2NF 的基础上，消除所有关于主属性（组）的传递依赖。表中每一列都直接依赖于主键，而不是通过其它列间接依赖于主键。