# 一、表设计(合)

# 1、表 与 表

  一个表代表一个实体,实体之间都有关联关系的

  • 一对一关系(优化访问效率)
  • 一对多关系(明确主从关系)
  • 多对多关系(体现实体联系)

  例如:设计一个简单新闻管理系统的数据库

答案
  • 新闻信息表:id、标题、内容、发布时间、作者 id(作者表主属性)、分类 id(分类表主属性)、阅读量、推荐数

  • 作者表:id、作者名字、作者来源 id(来源表)

  • 来源表:id、来源名字、来源描述

  • 分类表:id、分类名字、分类级别(父分类 id)

  • 评论表:id、评论人 id(评论表)、评论时间、评论内容(不回复)

# 1、一对一

TIP

  一张表中的一条记录与另外一张表中有且仅有一条记录有关系

  一对一关系通常是用来将一张原本就是一体的表拆分成两张表

产生场景

1、一张表的数据字段较多且数据量较大

2、表中有部分字段使用频次较高,而另一部分较少使用

3、将常用字段和不常用字段拆分成两张表,使用同样的主键对应

1、学生信息表

id 姓名 学号 密码 邮箱 性别 年龄 身高 体重 籍贯 政治面貌
1 张飞 20631727322 552372 630639343@qq.com 20 178 160 农民
2 武则天 20631727323 344231 253648362@qq.com 21 168 110 党员

2、一对一关系设计

常用表

id 姓名 学号 密码 邮箱
1 张飞 20631727322 552372 630639343@qq.com
2 武则天 20631727323 344231 253648362@qq.com

不常用表

id 性别 年龄 身高 体重 籍贯 政治面貌
1 20 178 160 农民
2 21 168 110 党员

# 2、一对多 ✨

TIP

  一张表中的一条记录与另外一张表的多条记录对应,反过来另外一张表的多条记录只能对应当前表的一条记录

1、确定实体间的关系为一对多(多对一)关系

2、在多表中增加一个字段记录一表中对应的主属性

一个 建筑(building)可以有多个 房间(rooms)

SQL 代码
CREATE TABLE IF NOT EXISTS buildings (
    building_no INT PRIMARY KEY AUTO_INCREMENT,
    building_name VARCHAR(255) NOT NULL,
    address VARCHAR(255) NOT NULL
) COMMENT = '建筑表';


INSERT INTO buildings(building_name,address)
VALUES('海南大厦','海口市国兴大道1234号'),
      ('万达水城','海口市大同路1200号'),
      ('中山大厦', '海口市骑楼街56号');
CREATE TABLE IF NOT EXISTS rooms (
    room_no INT PRIMARY KEY AUTO_INCREMENT,
    room_name VARCHAR(255) NOT NULL,
    building_no INT,
    FOREIGN KEY (building_no) REFERENCES buildings (building_no) ON UPDATE CASCADE ON DELETE CASCADE
) COMMENT = '房间表';

INSERT INTO rooms(room_name,building_no)
VALUES('Amazon',1),
      ('War Room',1),
      ('Office of CEO',1),
      ('Marketing',2),
      ('Lakeside Cottage',2);
ER-图表

建筑表 (1)

building_no building_name address
1 海南大厦 海口市国兴大道 1234 号
2 万达水城 海口市大同路 1200 号
3 中山大厦 海口市骑楼街 56 号

房间表 (n)

room_no room_name building_no
1 Amazon 1
2 War Room 1
3 Office of CEO 1
4 Marketing 2
5 Lakeside Cottage 2

# 3、多对多 ✨

TIP

  一张表中的一条记录对应另外一个表中多条记录,反过来一样

1、确定实体间的关系为多对多关系

2、设计中间表,记录两张表的对应关系

一个学生(student)可以选择多个课程(courses);一个课程(course)可以被多个学生(students)选中

SQL 代码
-- 创建学生表
CREATE TABLE IF NOT EXISTS `students`(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    age INT
) COMMENT = '学生表';

INSERT INTO `students` (name, age) VALUES('why', 18);
INSERT INTO `students` (name, age) VALUES('tom', 22);
INSERT INTO `students` (name, age) VALUES('lilei', 25);
INSERT INTO `students` (name, age) VALUES('lucy', 16);
INSERT INTO `students` (name, age) VALUES('lily', 20);
-- 创建课程表
CREATE TABLE IF NOT EXISTS `courses`(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    price DOUBLE NOT NULL
) COMMENT = '课程表';

INSERT INTO `courses` (name, price) VALUES ('英语', 100);
INSERT INTO `courses` (name, price) VALUES ('语文', 666);
INSERT INTO `courses` (name, price) VALUES ('数学', 888);
INSERT INTO `courses` (name, price) VALUES ('历史', 80);
INSERT INTO `courses` (name, price) VALUES ('物理', 100);
-- 创建学生-课程关系表
CREATE TABLE IF NOT EXISTS `students_select_courses`(
	id INT PRIMARY KEY AUTO_INCREMENT,
	student_id INT NOT NULL,
	course_id INT NOT NULL,
	FOREIGN KEY (student_id) REFERENCES students(id) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (course_id) REFERENCES courses(id) ON UPDATE CASCADE ON DELETE CASCADE
) COMMENT = '学生-课程关系表';

# why 选修了 英文和数学
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (1, 1);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (1, 3);
# lilei选修了 语文和数学和历史
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (3, 2);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (3, 3);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (3, 4);
ER-图表

学生表 (n)

id name age
1 why 18
2 tom 22
3 lilei 25
4 lucy 16
5 lily 20
5 Lakeside Cottage 2

课程表 (n)

id name price
1 英语 100
2 语文 666
3 数学 888
4 历史 80
5 物理 100

学生-课程关系表

拓展:👏 联合主键
  • id 主键
-- 创建学生-课程关系表
CREATE TABLE IF NOT EXISTS `students_select_courses`(
	id INT PRIMARY KEY AUTO_INCREMENT,
	student_id INT NOT NULL,
	course_id INT NOT NULL,
	FOREIGN KEY (student_id) REFERENCES students(id) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (course_id) REFERENCES courses(id) ON UPDATE CASCADE ON DELETE CASCADE
) COMMENT = '学生-课程关系表';
  • 联合主键
-- 创建学生-课程关系表
CREATE TABLE IF NOT EXISTS `students_select_courses`(
	student_id INT NOT NULL,
	course_id INT NOT NULL,
    PRIMARY KEY(student_id, course_id),
	FOREIGN KEY (student_id) REFERENCES students(id) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (course_id) REFERENCES courses(id) ON UPDATE CASCADE ON DELETE CASCADE
) COMMENT = '学生-课程关系表';
id student_id course_id
1 1 1
2 1 3
3 3 2
4 3 3
5 3 4

# 二、设计范式(拆)

# 1、范式概述

  范式是离散数学里的概念,目标是在满足组织和存储的前提下使数据结构冗余最小化,范式级别越高,表的级别就越标准

简要

1、范式是一种数学理论,在关系型数据库上用来==减少数据冗余==

2、满足的范式越多,越符合高标准表设计

3、范式一共有 6 层,但是数据库的设计通常只要求满足 3 层即可

  目前数据库应用到的范式有以下几层

  • 第一范式:1NF
  • 第二范式:2NF
  • 第三范式:3NF
  • 逆规范化

# 2、使用示例

  一张员工表

工号 姓名 部门 入职时间
0001 杨戬 武装部 0001-01-01
0002 李白 书院部 1500-12-12

  每个员工都是与部门挂钩的,但是部门不可能很多,所以上述表中会有很多数据重复,此时应该将部门单独维护出来,减少数据冗余

部门编号 部门名称
1 武装部
2 书院部
工号 姓名 部门编号 入职时间
0001 杨戬 1 0001-01-01
0002 李白 2 1500-12-12

# 3、第一范式 1NF

第一范式

  1NF,数据字段设计时必须满足原子性

  1NF 要求字段数据是不需要拆分就可以直接应用,即要求将数据拆分到最小单位(使用),然后设计成字段

如果数据使用的时候需要进行拆分那么就违背 1NF

  • 步骤
1、设计的字段是否在使用的时候还需要再拆分?
2、将数据拆分到最小单位(使用),然后设计成字段
3、满足1NF

1、设计一张学生选修课成绩表

学生 性别 课程 教室 成绩 学习时间
张三 PHP 101 100 2 月 1 日,2 月 28 日
李四 Java 102 90 3 月 1 日,3 月 31 日
张三 Java 102 95 3 月 1 日,3 月 31 日

2、满足 1NF 的设计:字段颗粒度应用层最小(不需要拆分)✨

学生 性别 课程 教室 成绩 开始时间 结束时间
张三 PHP 101 100 2 月 1 日 2 月 28 日
李四 Java 102 90 3 月 1 日 3 月 31 日
张三 Java 102 95 3 月 1 日 3 月 31 日

# 4、第二范式 2NF

第二范式

  2NF,字段设计不能存在部分依赖

  部分依赖:表存在复合主键,并且有的字段不是依赖整个主键,而只是依赖主键中的一部分

解决方案

  让所有非主属性都依赖一个候选关键字

  • 最简单方式:取消复合主键(一般选用逻辑主键替代,但是本质依然是复合主键做主),所有非主属性都依赖主属性(逻辑主键)
  • 正确方式:将部分依赖关系独立成表
1、表中是否存在复合主键?
2、其他字段是否存在依赖主键中的一部分?
3、如果存在部分依赖,将部分依赖的关系独立拆分成表
4、满足 2NF

1、学生成绩表

学生(P) 性别 课程(P) 教室 成绩 开始时间 结束时间
张三 PHP 101 100 2 月 1 日 2 月 28 日
李四 Java 102 90 3 月 1 日 3 月 31 日
张三 Java 102 95 3 月 1 日 3 月 31 日
  • 成绩是由学生和课程决定的,是完全依赖主属性(复合主键
  • 性别只依赖学生(部分依赖)
  • 教室、开始时间和结束时间依赖课程(部分依赖)

2、解决方案:将学生信息维护到一张表,课程信息维护到一张表,成绩表取两个表的主属性即可

学生表

Stu_id(P) 姓名 性别
1 张三
2 李四
  • Stu_id 是姓名的代指属性(逻辑主键)
  • 性别只依赖姓名(主属性)

课程表

Class_id(P) 课程 教室 开始时间 结束时间
1 PHP 101 2 月 1 日 2 月 28 日
2 Java 102 3 月 1 日 3 月 31 日
  • Class_id 是课程的代指属性(逻辑主键)
  • 教室、开始时间和结束时间都依赖课程(主属性)

成绩表

Stu_id(P) Class_id(P) 成绩
1 1 100
2 2 90
1 2 95
  • Stu_id 和 Class_id 共同组成主属性

# 5、第三范式 3NF

第二范式

  3NF,字段设计不能存在传递依赖

  传递依赖:字段某个非主属性不直接依赖主属性,而是通过依赖某个其他非主属性而传递到主属性之上

解决方案

  让依赖非主属性的字段与依赖字段独立成表

  • 步骤
1、确定表中的所有字段都是🚩依赖主属性的
2、如果存在不直接依赖主属性,而是通过依赖其他属性产生依赖的,形成独立的表
3、满足 3NF

1、学生表:包括所在系信息

学号(P) 姓名 专业编号 专业名字
205821181 张三 0001001 软件工程
205822081 李四 0001002 土木工程
  • 专业名字 ----> 专业编号 和 姓名 ----> 学号
  • 专业名字 间接依赖 学号:传递依赖

随着学生增加,专业名字 会出现大量数据冗余

2、解决方案:将存储传递依赖部分的字段(非主属性)独立成表,然后在需要使用相关信息的时候,引入即可

专业表

专业编号(P) 专业名字
0001001 软件工程
0001002 土木工程

学生表

学号(P) 姓名 专业编号
205821181 张三 0001001
205822081 李四 0001002

# 6、逆规范化

逆规范化

  为了提升数据查询的效率而刻意违背范式的规则(增加表之间的关联)

1、逆规范化只有在数据量大,查询效率低下的时候为了提升查询效率而牺牲磁盘空间的一种做法

2、逆规范化后数据表的设计必然是不完全符合范式要求的(2NF/3NF)

学号(P) 学生姓名 课程号(P) 课程名字 成绩
1 张三 1 PHP 100
1 张三 2 Java 90
更新于 : 7/8/2024, 10:21:14 AM