mysql学习-多表操作和三大范式

数据库的设计

多表之间的关系

一对一的关系(了解)

例如: 人和身份证之间的关系
分析: 
这么读: 一个人只有一个身份证,一个身份证只对应一个人
如果两句话都没有问题,那就是一对一的关系.

一对多(多对一)的关系

例如: 部门和员工
分析:
	一个部门有多个员工, 一个员工只能对应一个部门.

多对多的关系

例如: 学生和课程
分析:
	一个学生可以选择多门课程,而一个课程也可以被很多学生选择.

一对多(多对一)关系实现

例如: 部门和员工
在多的地方建立外键,指向为一的另一方的主键
在本例中就是在员工表中建立外键,指向部门表的主键id

多对多关系实现

例如: 学生和课程
我们需要借助一张中间表,中间表至少包含两个字段,这两个字段作为中间表的外键,分别指向两张表的主键
这里还可以有一个联合主键的知识点,即某一个学生的id对应于某一门课程的id只能出现一次
例如id为1的学生只能选一门id为1的课,不能选多门id为1的课.

create table test(
cid int,-- 课程id
sid int,-- 学生id
primary key(rid, uid), -- 联合主键
foreign key(cid) reference course(id),
foreign key(sid) reference student(id),
);
这里省略了添加外键约束时的constraint关键字,使用系统给的名字

一对一关系实现

例如: 人和身份证
在任意一张表中添加外键,指向另一张表的主键
同时还需要让外键唯一,防止重复.

多表关系案例

有三个实体: 
	旅游路线分类, 旅游路线, 用户
有三张表: 
	旅游路线分类(cid,name), 旅游路线(rid, name, price), 用户(uid, name, password)
分析:
	一个分类对应多条线路,而一条线路只对应一个分类.
	所以分类和线路之间是一对多的关系,我们在线路中添加一列作为外键,指向分类的主键就可以了
	一个用户可以收藏多条线路,而一个线路可以有多个用户选择
	所以线路和用户之间是多对多的关系
	我们需要一张中间表,包含rid和uid,分别作为外键指向两张表的主键.

数据库设计的范式(只学前三个)

概念: 范式即设计数据库时需要遵循的一些规范,要遵循后面的范式,就必须先遵循前面的所有范式.

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

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

第一范式(1NF)

定义: 即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。

第二范式(2NF)

定义: 在1NF的基础上，非码属性必须完全依赖于码（在1NF基础上消除非主属性对主码的部分函数依赖）

函数依赖: A—>B,如果A属性(属性组)的值可以确定唯一B属性的值,则称B依赖于A

例如: 学号—>姓名.
例如: (学号, 课程名称)这个属性组—>分数.

完全函数依赖: A—>B,如果A是一个属性组,则B属性值的确定需要依赖于A属性组中所有的属性值,即B列完全函数依赖A列.

例如: (学号, 课程名称)这个属性组—>分数,单单靠学号或者课程名称是无法唯一确定分数的.

部分函数依赖: A—>B,如果A是一个属性组,则B属性值的确定只需要A属性组中某一些值即可,这就表示B属性部分函数依赖于A.

例如: (学号, 课程名称)—>姓名.

传递函数依赖: A—>B, B—>C,如果通过A属性(属性组)可以唯一确定B属性(属性组)的值,然后再通过B属性(属性组)的值,可以唯一确定C属性(属性组)的值,则称C传递函数依赖于A.

例如: 学号—>姓名, 姓名 —>系主任.

码: 在一张表中,如果一个属性或属性组被其它所有属性完全函数依赖,则称这个属性(属性值)为该表的码

主属性: 即码属性组中的所有属性.
非主属性: 即除码属性组的其它属性.
例如: 在给定图片中,码为(学号, 课程名称)

第三范式(3NF)

定义: 在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

.

分析:
	1. 该图片中,系这一列包含两列,则表明该表不满足第一范式.
	2. 要解决该问题,就要把系名和系主任两列拆开来作为两列,就变成了下面这样.

.

分析:
	1. 存在非常严重的冗余: 学号,姓名,系名,系主任等重复的很高.
	2. 数据添加存在问题.
		1. 比如添加一个新开设的系时,数据不合法,因为根本没有学生.
	3. 数据删除存在问题.
		1. 比如张无忌毕业了,删除张无忌的数据会删除它所在的系和系主任
		2. 要是只有他一个学生的话,那这个系就不存在了,这是不合理的.
	4. 该表中,(学号,课程名称)为码,只有分数是完全函数依赖于码的,姓名,系名,系主任都是部分依赖,我们消除部分依赖将其升级为第二范式,如下图:

.

分析:
	1. 解决了数据冗余的问题,但是数据添加和数据删除的问题仍然没有解决.
	2. 我们的第二张表中,系名依赖于学号,而系主任依赖于系名,所以系主任传递依赖于学号.
	3. 我们消除传递依赖将其升级为第三范式,如图:

.

分析:
	1. 解决了数据添加和数据删除存在的问题.
	2. 这就是我们设计出来的可以用的数据库表.