SqlServer三范式、表关系

11.1三范式

--数据库结构设计三范式
--第一范式:是对属性的原子性，要求属性具有原子性，不可再分解。
create table Student --学生表
(
	StuId varchar(20) primary key,--学号
	StuName varchar(20) not null,--学生姓名
	StuContact varchar(50) not null --联系方式
)
insert into Student(StuId,StuName,StuContact)
values('001','刘备','QQ:189217357;Tel:27613890912')
select * from Student
--上例中联系方式不具原子性，不符合第一范式

----------------------------------------------------------------------------------
--第二范式：是对记录的唯一性，要求记录有唯一标识，即实体的唯一性，不存在部分依赖
create table Course --课程
(
	CourseId int primary key identity(1,1),--课程编号
	CourseName varchar(30) not null,--课程名称
	CourseContent text --课程介绍
)
insert into Course values('编程基础','C++高级语言')
insert into Course values('数据库','SQL server')


create table Student --学生
(
	StuId int primary key identity(1,1),--学生编号
	StuName varchar(20) not null,--学生姓名
	StuSex char(2) not null --学生性别
)
insert into Student values('刘备','男')
insert into Student values('关羽','男')

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

select * from Course
select * from Student
select * from Exam
----维护更容易，查询语句变复杂----
select Student.StuId,StuName,CourseName,Score from Student
inner join Exam on Student.StuId = Exam.StuId
inner join Course on Course.CourseId = Exam.CourseId

------------------------------------------------------------------------------------
--第三范式：要求任何字段不能由其他字段派生出来，它要求字段没有冗余，即不存在传递依赖
create table Student
(
	StuId varchar(20) primary key,--学号
	StuName varchar(20) not null,--学生姓名
	ProfessionalId int not null,--专业编号
	ProfessionalName varchar(50),--专业名称
	ProfessionalRemark varchar(200)--专业介绍
)
--上述设计中专业名称字段和专业介绍字段，在数据库中会产生很多冗余数据，不满足第三范式，优化如下
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,--专业编号
)

11.2表关系

create database DBTest2
use DBTest2
--表关系（一对一，一对多，多对多）---------------------------------
--一对多（专业表，学生表，一个专业含多个学生）
create table Professional --专业表
(
	ProId int primary key identity(1,1),--专业编号
	ProName varchar(50) not null --专业名称
)
create table Student --学生表
(
	StuId int primary key identity(1,1),--学生编号
	ProId int not null,--专业编号（此字段名用于关联专业表,可使用外键）
	StuName varchar(50) not null,--学生姓名
	StuSex varchar(2) not null --学生性别
)
insert into Professional(ProName) values('语文')
insert into Professional(ProName) values('计算机')
insert into Student values(1,'刘备','男')
insert into Student values(1,'关羽','男')
insert into Student values(2,'张飞','男')
insert into Student values(2,'赵云','男')
select * from Professional
select * from Student 


--一对一关系
--例如学生基本信息（学号，姓名，性别），学生详细信息（电话，邮箱，地址）
--合并成一张表表示 或者 分成两张表主键关联（类似一对多）


--多对多（学生信息，课程信息）（添加一张表来表示关系）
create table Student --学生
(
	StuId int primary key identity(1,1),--学生编号
	StuName varchar(20) not null,--学生姓名
	StuSex char(2) not null --学生性别
)
insert into Student values('刘备','男')
insert into Student values('关羽','男')

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

select * from Course
select * from Student
select * from Exam