MySQL【知识改变命运】09

1. 范式

范式总共有六种范式，分别是第一范式、第二范式、第三范式、巴斯-科德范式、第四范式、第五范式，越高的范式数据库的冗余越低，但是数据库的IO更复杂（原来从一个表中可以查找，现在需要从多个表查询），因此一般情况之下，我们使用第三范式就可以了

1.1. 第一范式

1.1.1. 定义

• 数据库的每一列都是不可再原子项，不可以是集合，数组，对象的非原子数据。
• 在关系型数据库种，满足第一范式是关系型数据库的基本要求，不满足第一范式的数据库就不能被称为关系型数据库。

1.1.2. 反例

上面这个表就不符合第一范式，因为学校是一个对象，学校还可以再分，并且学校无法用基本数据类型表示。

1.1.3. 正例

学校信息的每一行种，每一列都不能再进行拆分，此时已满足第一范式。

但是每一列都可以使用sql内置基本数据类型表示，数据冗余非常大

• 在关系型数据库中每一列都可以用基本类型数据表示，就天然满足第一范式。

1.2. 第二范式

1.2.1. 定义

• 满足第一范式的基础上，不存在非关键字段（非主键字段）对任意候选键（主键，唯一键，用来标识数据行）部分依赖。存在与表中定义的复合主键（主键中包含多个列）的情况下。
• 候选键：可以唯⼀标识⼀⾏数据的列或列的组合，可以从候选键中选⼀个或多个当做表的主键

1.2.2. 反例

上述表中

• 学生的信息是根据学号确定的
• 学分是根据课程名确定的
• 成绩是通过学号+课程名来共同区分的，一个学生可以选修课程，经过一次考试之后才会生成成绩
• 也就是说这个表可以通过学号+课程名作为复合主键来确定同学当前选修课的成绩（主要作用）
• 学生的姓名、年龄、性别只跟学好有关，不依赖课程名
• 学分和学生信息没有关系，只依赖课程名
• 对于由两个或两个以上的关键字段决定一条记录情况下，如果一行数据中有些字段只与关键字段中的某一个有关系，那么就说这个表中存在部分函数依赖，不满足第二范式

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

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

1.2.4. 正例

1. 设计表：针对需求应该设计三张表：学生表，课程表，成绩表。
   

• 解决部分函数依赖，可以通过，每个实体定义单独表，再用一个单独关系表来组织需要多个主键描述的数据行。
• -第⼆范式强调的是部分函数依赖，当⼀张表中的主键只有⼀列时，天然满⾜第⼆范式

1.3 第三范式

1.3.1 定义

• 在满足第二范式的基础上，不存在非关键字段，对任意一候选键的传递依赖

1.3.2. 写一个实例

要求学生表中记录学生所属的学院，在满足第二范式的基础上对学生表做出修改。
-上述描述中由两个实例，一个是学生，一个是学院

1.3.3.反例

• 在这张表中明显是描述的学生，所以判断出，学号或者id是这个表的主键。
• 在这个表中，学号，学生姓名，年龄，性别这些学生信息，与主键强相关
• 学院名和学院地址，学校电话，与学院这个实体强相关的。
• 一个表中表现出两种强相关，这两种强相关关系存在传递现象（学号–>学生信息–>学院名–>学院电话–>学院地址）
• 这种的传递关系就是传递依赖，这种表设计不满足第三范式

1.3.4. 正例

• 把学院信息拆分出来定义学院表，学生表与学院表做关联
• -由于反例中存在两个实体，所以把不同的实体拆分为不同表的表即可。
  
• 此时的设计表满足第三范式

3. 设计过程

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

• 上述在，类、实体、表，其实是在不同场景下同一个事物的不同称呼
• 上述属性、字段、列，也是同一个事物的不同称呼。

4. 实体-关系图

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

5.1 E-R图的基本组成

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

-关系一般包括四种：
1对1，1对多，多对多，没有关系（不考虑）

5.2 关系的类型

5.2.1 ⼀对⼀关系 (1 : 1)

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

5.2.2 ⼀对多关系 (1 : N)

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

5.2.3 多对多关系 (M : N)

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

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

5.练习设计表

5.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//account里的id
);
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//user表里的id
);

5.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
);

5.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)
);