sql 三范式

数据库结构设计三范式

第一范式

是对属性的原子性，要求属性具有原子性，不可再分解。

错误设计：

create table Student -- 学生表
(
  StuId varchar(20) primary key,  -- 学号
  StuName varchar(20) not null,  -- 学生姓名
  StrContact varchar(50) not null  -- 联系方式
)

StuId	StuName	StrContact
001	刘备	QQ:85654743;Tel:48898373

上述设计不满足第一范式，联系方式这一列并不是不可再分的最小单元，应修改就够如下：

create table Student -- 学生表
(
  StuId varchar(20) primary key,  -- 学号
  StuName varchar(20) not null,  -- 学生姓名
  Tel varchar(20) not null,  -- 联系电话
  QQ varchar(20) not null  -- 联系QQ
)

StuId	StuName	Tel	QQ
001	刘备	48898373	85654743

第二范式

是对记录的唯一性，要求记录有唯一标识，实体的唯一性，不存在部分依赖。

错误设计：

create table studentCourse -- 选课成绩表
(
  StuId varchar(20), -- 学号
  StuName varchar(20) not null, -- 学生姓名
  CourseId varchar(20) not null, -- 课程编号
  CourseName varchar(20) not null, -- 选课课程名称
  CourseScore int not null -- 考试成绩
)

StuId	StuName	CourseId	CourseName	CourseScore
001	刘备	001	HTML	80
001	刘备	002	JAVA	70
002	关羽	003	SQL	80
003	张飞	003	SQL	90

• 上述设计中有两个事物，一个是学生信息，另一个是课程信息，图表中可以看到这两个事物都没保证实体的唯一性。
• 这样带来的问题：假如删除刘备这条记录，课程也被一起删除了，实际中是不需要删除课程的，选修课还是要存在的。

这属于多对多的关系，将两者的关系在第三张表中维护，修改如下：

create table Course -- 课程表
(
  CourseId int primary key identity(1,1), -- 课程编号
  CourseName varchar(30) not null,  -- 课程名称
  CourseContent text  -- 课程介绍
)

CourseId	CourseName	CourseContent
1	HTML	静态网页制作
2	WinForm	Windows 应用程序开发

create table Student -- 学生表
(
  StuId int primary key identity(1,1), -- 学生编号
  StuName varchar(50) not null,  -- 学生名称
  StuSex char(2) not null  -- 学生性别
)

StuId	StuName	StuSex
1	刘备	男
2	关羽	男

create table Exam -- 考试信息表
(
  ExamId int primary key identity(1,1),  -- 选课成绩编号
  StuId int not null,  -- 学生编号
  CourseId  int not null,  -- 课程编号
  Score  int not null  -- 考试分数
)

ExamId	StuId	CourseId	Score
1	1	1	90
2	1	2	80
3	2	2	85

第三范式

要求任何字段不能由其他字段派生出来，它要求字段没有冗余，即不存在传递依赖。

错误的写法：

create table Student 
(
  StuId varchar(20) primary key,  -- 学号
  StuName varchar(20) not null,  -- 学生姓名
  ProfessionalId int not null,  -- 专业编号
  ProfessionalName varchar(50), -- 专业名称
  ProfessionRemark varchar(200)  -- 专业介绍
)

StuId	StuName	ProfessionalId	ProfessionalName	ProfessionRemark
001	刘备	1	计算机	最牛的专业
002	关羽	2	工商管理	管理学的基础专业
003	张飞	1	计算机	最牛的专业

• 学生编号 -> 学生姓名，是直接的关系
• 学生编号 -> 专业编号，是直接关系
• 学生编号 -> 专业名称/专业介绍，是间接关系。因为专业名称/专业介绍和专业编号是直接关系，专业编号和学生编号是直接关系。

上述设计在数据库中会产生很多冗余数据，不满足第二范式，优化如下：

其实是一对多的关系，一个专业对应多个学生，拆成两张表就可以了：

create table Professional
(
  ProfessionalId int primary key identity(1,1),  --专业编号
  ProfessionalName varchar(50),  -- 专业名称
  ProfessionalRemark varchar(200)  -- 专业介绍
)

create table Student
(
  StuId varchar(20) primary key,  -- 学号
  StuName varchar(20) not null,  -- 学生姓名
  ProfessionalId int not null  -- 专业编号
)

ProfessionalId	ProfessionalName	ProfessionalRemark
1	计算机	最牛的专业
2	工商管理	管理学的基础专业

StuId	StuName	ProfessionalId
001	刘备	1
002	关羽	2
003	张飞	1

这样就不会产生冗余数据。