数据库设计多表关系、范式

数据库的设计

在实际项目开发的时候可能设计到多张表，表与表之间存在一定关系，如何设计表之间的关系让系统更加优化，需要考虑到多表之间关系。

1. 一对一(了解)
	* 如：人和身份证
	* 分析：一个人只有一个身份证，一个身份证只能对应一个人
2. 一对多(多对一)
	* 如：部门和员工
	* 分析：一个部门有多个员工，一个员工只能对应一个部门
3. 多对多
	* 如：学生和课程
	* 分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择

一对多关系

一个部门可以有多个员工，一个员工只能对应一个部门

-- 创建部门表(主表)
CREATE TABLE department(
	id INT PRIMARY KEY,
	NAME VARCHAR(20)
)
-- 向部门表中添加数据
INSERT INTO department(id,NAME) VALUES(1,"教研部");
INSERT INTO department(id,NAME) VALUES(2,"学工部");
INSERT INTO department(id,NAME) VALUES(3,"就业部");

SELECT * FROM department;

-- 创建员工表(从表)
CREATE TABLE employee(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20) NOT NULL,
	age INT,
	dep_id INT,
	CONSTRAINT emp_depid_fk FOREIGN KEY(dep_id) REFERENCES department(id)
)
-- 向员工表中添加数据
INSERT INTO employee(NAME,age,dep_id) VALUES("张三",18,1);
INSERT INTO employee(NAME,age,dep_id) VALUES("李四",20,2);
INSERT INTO employee(NAME,age,dep_id) VALUES("王五",21,1);
INSERT INTO employee(NAME,age,dep_id) VALUES("赵六",19,3);
INSERT INTO employee(NAME,age,dep_id) VALUES("田七",24,2);
INSERT INTO employee(NAME,age,dep_id) VALUES("周八",23,3);
INSERT INTO employee(NAME,age,dep_id) VALUES("黄九",25,1);

SELECT * FROM employee;

多对多关系

一个学生可以选择很多门课程，一个课程也可以被很多学生选择

-- 创建学生表
CREATE TABLE student(
	sid INT PRIMARY KEY,
	NAME VARCHAR(20),
	age INT
)
INSERT INTO student(sid,NAME,age) VALUES(1,"张三",20);
INSERT INTO student(sid,NAME,age) VALUES(2,"李四",19);
INSERT INTO student(sid,NAME,age) VALUES(3,"王五",18);
INSERT INTO student(sid,NAME,age) VALUES(4,"赵六",21);
INSERT INTO student(sid,NAME,age) VALUES(5,"田七",24);
INSERT INTO student(sid,NAME,age) VALUES(6,"周八",23);

-- 创建课程表
CREATE TABLE class(
	cid INT PRIMARY KEY,
	NAME VARCHAR(20)
)
INSERT INTO class(cid,NAME) VALUES(1,"Java基础");
INSERT INTO class(cid,NAME) VALUES(2,"HTML");
INSERT INTO class(cid,NAME) VALUES(3,"Javascript");
INSERT INTO class(cid,NAME) VALUES(4,"mysql");


-- 创建外键表
CREATE TABLE foreign_table(
	sid INT,
	cid INT,
	CONSTRAINT sid_fk FOREIGN KEY(sid) REFERENCES student(sid), -- 外键sid 关联 student sid主键
	CONSTRAINT cid_fk FOREIGN KEY(cid) REFERENCES class(cid)   -- 外键cid 关联 class cid主键
)
INSERT INTO foreign_table(sid,cid) VALUES(1,1);
INSERT INTO foreign_table(sid,cid) VALUES(1,2);
INSERT INTO foreign_table(sid,cid) VALUES(1,3);
INSERT INTO foreign_table(sid,cid) VALUES(1,4);
INSERT INTO foreign_table(sid,cid) VALUES(2,1);
INSERT INTO foreign_table(sid,cid) VALUES(2,2);
INSERT INTO foreign_table(sid,cid) VALUES(3,4);
INSERT INTO foreign_table(sid,cid) VALUES(3,1);

一对一关系

一个人有一个身份证号码，一个身份证号码只对应一个人

-- 创建身份证表
CREATE TABLE card(
	id INT PRIMARY KEY,
	number VARCHAR(18)
)
-- 设置身份证号码唯一
ALTER TABLE card MODIFY number VARCHAR(20) UNIQUE;

INSERT INTO card(id,number) VALUES(1,420923199211090512);
INSERT INTO card(id,number) VALUES(2,420923199310230807);
INSERT INTO card(id,number) VALUES(3,420923199007080908);

-- 创建人员表
CREATE TABLE person(
	id INT PRIMARY KEY,
	NAME VARCHAR(20),
	cid INT
)
-- 给人员表添加外键
ALTER TABLE person ADD CONSTRAINT cid_kf FOREIGN KEY(cid) REFERENCES card(id);
-- 设置外键唯一
ALTER TABLE person MODIFY cid INT UNIQUE
-- 向person表中添加数据
INSERT INTO person(id,NAME,cid) VALUES(1,"张三",1);
INSERT INTO person(id,NAME,cid) VALUES(2,"李四",2);
INSERT INTO person(id,NAME,cid) VALUES(3,"王五",3);

多表关系案例

旅游分类：如亲子游、爸妈游、蜜月游等
旅游线路：如普吉岛5日游、巴厘岛7日游等
用户：如老王、小王等

分析：
一个分类对应多个线路、一个线路可以被多用用户选择，一个用户也可以选择多个线路。

		多表关系
旅游分类表	旅游线路表	一对多关系
旅游线路表	用户表	多对多关系

-- 创建旅游线路分类表 tab_category
-- cid 旅游线路分类主键，自动增长
-- cname 旅游线路分类名称非空，唯一，字符串 100
CREATE TABLE tab_category (
 cid INT PRIMARY KEY AUTO_INCREMENT,
 cname VARCHAR(100) NOT NULL UNIQUE
);
-- 添加旅游线路分类数据：
INSERT INTO tab_category (cname) VALUES ('周边游'), ('出境游'), ('国内游'), ('港澳游');
SELECT * FROM tab_category;


-- 创建旅游线路表 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)
);


-- 添加旅游线路数据
INSERT INTO tab_route VALUES
(NULL, '【厦门+鼓浪屿+南普陀寺+曾厝垵 高铁 3 天 惠贵团】尝味友鸭面线 住 1 晚鼓浪屿', 1499,
'2018-01-27', 1),
(NULL, '【浪漫桂林 阳朔西街高铁 3 天纯玩 高级团】城徽象鼻山 兴坪漓江 西山公园', 699, '2018-02-22', 3),
(NULL, '【爆款￥1699 秒杀】泰国 曼谷 芭堤雅 金沙岛 杜拉拉水上市场 双飞六天【含送签费 泰风情 广州
往返 特价团】', 1699, '2018-01-27', 2),(NULL, '【经典•狮航 ￥2399 秒杀】巴厘岛双飞五天 抵玩【广州往返 特价团】', 2399, '2017-12-23',
2),
(NULL, '香港迪士尼乐园自由行 2 天【永东跨境巴士广东至迪士尼去程交通+迪士尼一日门票+香港如心海景酒店
暨会议中心标准房 1 晚住宿】', 799, '2018-04-10', 4);

SELECT * FROM tab_route;
/*
创建用户表 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)
);

-- 添加用户数据
INSERT INTO tab_user VALUES
(NULL, 'cz110', 123456, '老王', '1977-07-07', '男', '13888888888', '66666@qq.com'),
(NULL, 'cz119', 654321, '小王', '1999-09-09', '男', '13999999999', '99999@qq.com');
SELECT * FROM tab_user;

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

-- 增加收藏表数据
INSERT INTO tab_favorite VALUES
(2, '2018-02-11', 1), -- 老王选择桂林
(3, '2018-03-21', 1), -- 老王选择泰国
(2, '2018-04-21', 2), -- 小王选择桂林
(3, '2018-05-08', 2), -- 小王选择泰国
(4, '2018-06-02', 2); -- 小王选择迪士尼

SELECT * FROM tab_favorite;

范式【了解】

设计数据库时，需要遵循的一些规范。要遵循后边的范式要求，必须先遵循前边的所有范式要求

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

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

第一范式（1NF）
每一列都是不可分割的原子数据项

第二范式（2NF）

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

第三范式（3NF）
在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）