MySQL之数据库设计

前文我们了解了数据库中库、表等的操作，今天我们来学习如何设计数据库？？

在设计数据库前，我们先来了解什么是范式？？

1. 范式

数据库的范式是一组规则，在设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式。
关系数据库有六种范式：第⼀范式（1NF）、第⼆范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式（4NF）和第五范式（5NF，⼜称完美范式），越⾼的范式数据库冗余越⼩。然⽽，普遍认为范式越⾼虽然对数据关系有更好的约束性，但也可能导致数据库IO更繁忙（由于范式高，需要从多张表中查询，导致数据库IO繁忙），因此在实际应⽤中，我们为了效率和性能，我们一般折中采用第三范式设计数据库。

1.1 第一范式

1.1.1 定义：

• 数据库表的每⼀列都是不可分割的原⼦数据项，⽽不能是集合，数组，对象等⾮原⼦数据。
• 在关系型数据库的设计中，满⾜第⼀范式是对关系模式的基本要求。不满⾜第⼀范式的数据库就不 能被称为关系数据库

1.1.2 反例

上图中，学校也是一个对象，可以对其进行拆分，不满足第一范式的定义中每一列都是不可分割的原子数据项，所以不满足第一范式

1.1.3 正例

学校信息包含在⼀⾏中，每⼀列都不能再进⾏拆分，此时已满⾜第⼀范式

注意：在关系型数据库中，每⼀列都可以⽤基本数据类型表⽰，就天然满⾜第⼀范式

1.2 第二范式

1.2.1 定义

在满⾜第⼀范式的基础上，在表中定义了复合主键的情况下，不存在⾮关键字段对任意候选键的部分函数依赖

候选键：可以唯⼀标识⼀⾏数据的列或列的组合，可以从候选键中选⼀个或多个当做表的主键
非关键字段：没有被主键标识的字段

1.2.2 反例

• 这张表中使⽤学号+课程名定义复合主键来唯⼀标识⼀个学⽣某⻔课程的成绩，这也是这张表的主 要作⽤
• 学⽣是通过学号来确定的，学⽣的姓名、年龄和性别和课程没有关系，即学⽣的信息只依赖学号，
  不依赖课程名；学分是通过课程来确定的，课程的学分与学⽣没有关系，即学分只依赖课程名，不 依赖学⽣
• 对于使⽤复合主键的表，如果⼀⾏数据中的有些列只与复合主键中的⼀个或其中⼏个列有关系，那 么就说他存在部分函数依赖，也就不满⾜第⼆范式

1.2.3 不满⾜第⼆范式时可能出现的问题

1. 数据冗余
   学⽣的姓名、年龄、性别和课程的学分在每⾏记录中重复出现，造成了⼤量的数据冗余。
2. 更新异常
   如果要调整MySQL的学分，那么就需要更新表中所有关于MySQL的记录，⼀旦执⾏中断导致某些记录更新成功，某些数据更新失败，就会造成表中同⼀⻔课程出现不同学分的情况，出现数据不⼀致问题。
3. 插⼊异常
   ⽬前这样的设计，成绩与每⼀⻔课和学⽣都有对应关系，也就是说只有学⽣参加选修课程考试取得了成绩才能⽣成⼀条记录。当有⼀⻔新课还没有学⽣参加考试取得成绩之前，那么这⻔新课在数据库中是不存在的，因为成绩为空时记录没有意义。
4. 删除异常
   把毕业学⽣的考试数据全都删除，此时课程和学分的信息也会被删除掉，有可能导致⼀段时间内，数据库⾥没有某⻔课程和学分的信息。

1.2.4 正例

设计表：针对需求应该设计三张表，即：学⽣表、课程表和成绩表

注意：第二范式强调的是部分函数依赖，当表中的主键只有一列时，天然满足第二范式。

1.3 第三范式

1.3.1 定义

在满⾜第⼆范式的基础上，不存在⾮关键字段，对任⼀候选键的传递依赖。

1.3.2 反例

• 因为是要描述学⽣信息，并且在表中定义了Id为主键，Id可以明确的标识每条学⽣信息
• 在这个表结构中，可以看出学⽣的学号、姓名、年龄、性别与主键Id强相关；学院电话、学院地址与学院强相关；
  在⼀个表中出现了两个强相关的关系，⽽且这两个强相关关系⼜存在传递现象，即
  通过学⽣Id可以找到学⽣记录，学⽣记录中包含学院名，每个学院⼜有⾃⼰的电话和地址
• 这种传递现象称为传递依赖，所以当前的表不满⾜第三范式

1.3.3 正例

把学院信息拆分出来定义学院表，学⽣表与学院表做关联

2.设计过程

1. 从现实业务中抽象得到概念类 概念类是从现实世界中抽象出来的，在需求分析阶段就需要确定下来
2. 类对应了数据库设计中的实体，实体对应了数据库中的表
3. 类中的属性对应实体中的属性，实体的属性对应了表中的列
4. 确定实体与实体之间的关系，并画出E-R画，⽅便项⽬参与⼈员理解与沟通
5. 根据E-R图完成SQL语句的编号并创建数据库

3. 实体-关系图

实体-关系图（Entity-Relationship Diagram）简称E-R图，也称作实体联系模型、实体关系模型，是⼀种⽤于描述数据模型的概念图，主要⽤于数据库设计阶段。

3.1 E-R图的基本组成

E-R图包含了以下三种基本成分：
• 实体：即数据对象，⽤矩形框表⽰，⽐如⽤⼾、学⽣、班级等。
• 属性：实体的特性，⽤椭圆形或圆⻆矩形表⽰，如学⽣的姓名、年龄等。
• 关系：实体之间的联系，⽤菱形框表⽰，并标明关系的类型，并⽤直线将相关实体与关系连接起来。

3.2 关系的类型

3.2.1 ⼀对⼀关系 (1 : 1)

• ⼀个⽤⼾实体包含的属性有：⽤⼾昵称，真实姓名，⼿机号，邮箱地址，性别，学校
• ⼀个账⼾实体包含的属性有：登录⽤⼾名，密码
• ⽤⼾实体与账⼾实体是⼀对⼀的关系，⽤E-R图表⽰如下：

3.2.2 一对多（多对一）关系

• ⼀个学⽣实体包含的属性有：真实姓名，学号，年龄，性别，⼊学时间
• ⼀个班级实体包含的属性有：班级名，学⽣⼈数
• ⼀个班级中有多个学⽣，所以班级实体与学⽣实体是⼀对多的关系，反过来说学⽣实体与班级实体
  是多对⼀着么，⽤E-R图表⽰如下：

3.3.3 多对多关系

• ⼀个学⽣实体包含的属性有：真实姓名，学号，年龄，性别，⼊学时间
• ⼀个课程实体包含的属性有：课程名
• ⼀个学⽣可以选修改多⻔课程，⼀⻔课程也可以被多名学⽣选修改，所以学⽣与课程之间是多对多关系，⽤E-R图表⽰如下：

• 对于多对多关系，可以使⽤中间表进⾏记录，⽐如⼀个学⽣参加了某⼀⻔课程的考试得到了相应的 成绩，⽤E-R图表⽰如下：

4. 根据实体关系图创建表

4.1 ⽤户与账户的⼀对⼀关系

# 在⽤⼾实体中添加对账⼾实体的关联
drop table if exists users;
create table users (
 id bigint primary key auto_increment,
 name varchar(20) not null, 
 nickname varchar(20),
 phone_num varchar(11), 
 email varchar(50),
 gender tinyint(1),
 account_id bigint
);
drop table if exists account;
create table account (
 id bigint primary key auto_increment,
 username varchar(20) not null,
 password varchar(32) not null
);

# 在账⼾实体中添加对⽤⼾实体的关联
drop table if exists users;
create table users (
 id bigint primary key auto_increment,
 name varchar(20) not null, 
 nickname varchar(20),
 phone_num varchar(11), 
 email varchar(50),
 gender tinyint(1)
);
drop table if exists account;
create table account (
 id bigint primary key auto_increment,
 username varchar(20) not null,
 password varchar(32) not null,
 users_id bigint
);

4.2 学⽣与班级的⼀对多关系

分别创建学⽣表和班级表，在学⽣表中添加⼀列与班级表建⽴关联关系

# 班级表
drop table if exists class;
create table class (
 id bigint primary key auto_increment,
 name varchar(20)
);

# 学⽣表
drop table if exists student;
create table student (
 id bigint primary key auto_increment,
 name varchar(20) not null, 
 sno varchar(10) not null,
 age int default 18,
 gender tinyint(1), 
 enroll_date date,
 class_id bigint
 # 不建议在学生表下用set集合来存放学生的ID信息 因为学生的人数是可能会发生变化，不方便，
 # 其次表中的字段不能用基本类型来修饰，就不满足第一范式
);

4.3 学⽣、课程与成绩的多对多关系

学⽣可以选修多⻔课程，每⻔课程考试后会产⽣⼀个成绩，两个表之间没有办法直接建⽴关系，所以
要⽤到⼀个记录成绩的中间表

# 学⽣表
drop table if exists student;
create table student (
 id bigint primary key auto_increment,
 name varchar(20) not null, 
 sno varchar(10) not null,
 age int default 18,
 gender tinyint(1), 
 enroll_date date,
 class_id bigint,
 foreign key (class_id) references class(id)
);

# 课程表
drop table if exists course;
create table course (
 id bigint primary key auto_increment,
 name varchar(20)
);

# 分数表
drop table if exists score;
create table score (
 id bigint primary key auto_increment,
 score float,
 student_id bigint,
 course_id bigint,
 foreign key (student_id) references student(id),
 foreign key (course_id) references course(id)
);