一级范式(1NF)
- 保证列的原子性
- 什么是列的原子性呢?就是每一列的数据(字段值)是不可再分的
- 比如说一个列名为address,value为“中国山东”,那么这就不满足1NF,因为“中国山东”可以再分为 [国家:中国]、[城市:山东]。没有保证列的 原子性
不满足第一范式:
create table form1(
id int(20) primary key,
name varchar(20),
address varchar(50)
);
insert into form1 values ("001","monkey1","中国山东");
insert into form1 values ("002","monkey2","中国四川");
insert into form1 values ("003","monkey3","中国南京");
满足第一范式:
-- 将上述的表格中的 address 进行拆分成 country 、 provinve
create table form2 (
id int(20) primary key,
name varchar(20),
country varchar(20),
province varchar(20)
);
insert into form1 values ("001","monkey1","中国","山东");
insert into form1 values ("002","monkey2","中国","四川");
insert into form1 values ("003","monkey3","中国","南京");
二级范式(2NF)
- 在满足1NF的前提下,一个表只描述一种事物。
- 非主属性完全依赖于主键[消除非主属性对主码的部分函数依赖]。
- 例如:一个表为 (学号, 姓名, 年龄, 课程名称, 成绩, 学分) 这个就不满足2NF。
首先他描述的是两种事务,一种是学生信息(学号, 姓名, 年龄)、另一种是课程信息(课程名称, 成绩, 学分);
其次没有满足非主属性完全依赖于主键,我们可以通过“学号和课程名得到成绩” (学号,课程名称) → (成绩),但是显然这个表的主键是“学号”
不满足第二范式:
create table table1(
S_id int() primary key,
S_name varchar(20).
S_age varchar(20),
C_name varchar(20),
C_score float(20),
C_credit int(20)
);
insert into table1 values ("001","hu1","18","math","100","4");
insert into table1 values ("002","hu2","16","Chinese","85","5");
insert into table1 values ("003","hu3","22","English","120","4");
满足第二范式:(将上述表进行拆分)
-- 将上述表格拆分中table1和table2
create table table1(
S_id int() primary key,
S_name varchar(20).
S_age varchar(20),
C_id int(20)
);
insert into table1 values ("001","hu1","18","123");
insert into table1 values ("002","hu2","16","456");
insert into table1 values ("003","hu3","22","789");
create table table2(
C_id int(20) primary key,
C_name varchar(20),
C_score float(20),
C_credit int(20)
);
insert into table1 values ("0001","math","100","4");
insert into table1 values ("0002","Chinese","85","5");
insert into table1 values ("0003","English","120","4");
三级范式(3NF)
- 在满足2NF的前提下,每列都与主键有直接关系,不存在传递依赖。任何非主键属性不依赖于其他非主属性。
- 例如:一个表为(“订单编号”,“订单日期”,“顾客编号”,“顾客手机号”)。
此表不满足3NF,因为 我们可以通过“ 订单编号 和 顾客编号 找到 顾客姓名“订单编号”——>“顾客编号”——>“顾客手机号”。存在着"传递依赖"
不满足三级范式:
create table orders(
order_id int(20) primary key,
order_date timestamp,
customer_id int(20),
customer_phone varchar(20)
);
insert into orders values("00001","2020-10-01","01","124578555");
insert into orders values("00002","2020-11-01","02","545558555");
insert into orders values("00003","2020-11-21","03","124578555");
满足三级范式:
-- 将上述的表格拆分成table和table2
create table orders(
order_id int(20) primary key,
order_date timestamp,
customer_id int(20),
);
insert into orders values("00001","2020-10-01","01");
insert into orders values("00002","2020-11-01","02");
insert into orders values("00003","2020-11-21","03");
create table orders2(
customer_id int(20),
customer_name varchar(20),
customer_phone varchar(20)
);
insert into orders2 values("01","xuehao1","1585555555");
insert into orders2 values("02","xuehao2","1785552525");
insert into orders2 values("03","xuehao3","1985526458");
BCNF
图析: