实体关系模型
实体Entity:一个表就是一个实体。
关系Relationship:实体与实体之间的关系。
实体关系模型也称为ER模型。
用图形表示ER模型时,这个图就称为ER图。
用矩形表示实体,用椭圆形表示实体的属性,用菱形表示实体之间的关系,用直线连接各个图形。
实体之间的关系
一对一
实体A与实体B之间唯一对应。
如一个国家有一个领导人,一个人对应一个配偶。
ER图
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rOItXfkM-1672307371731)(D:\数据库\day4\数据库day4.assets\image-20221227141946224.png)]
在数据库中创建表的过程
可以使用一张表实现,但如果使用一张表保存时,后期维护扩展时较为不便。
最好使用两张表实现。
-
创建国家表
create table country( country_id int not null primary key auto_increment, country_name varchar(20) not null, country_population bigint not null, country_area int not null )
-
创建领导人表
create table leader( leader_id int not null primary key auto_increment, leader_name varchar(20) not null, leader_birthday date not null )
这时两张表是独立的,并不能体现一对一的管理关系,可以通过以下方式体现一对一。
-
方式一:
创建一个管理表,保存两张表中的主键,添加唯一约束
create table relation( relation_no int not null primary key auto_increment, leader_id int not null unique, country_id int not null unique, foreign key (leader_id) references leader(leader_id), foreign key (country_id) references country(country_id), )
-
方式二(建议使用):
在以上的两个实体表中,选择任意一张表中添加一列,保存另一张表的主键,添加唯一约束
create table leader( leader_id int not null primary key auto_increment, leader_name varchar(20) not null, leader_birthday date not null, country_id int not null unique, foreign key (country_id) references country(country_id) )
一对多/多对一
一对多:一个实体A对应多个实体B,一个实体B不能对应多个实体A
多对一:多个实体B对应一个实体A,多个实体A不能对应一个实体B
如一个人有多辆车,一辆车不能对应多个人;
一个教官训练多个学员,一个学员不能对应多个教官;
ER图
在数据库中创建表的过程
-
创建主表(一)
create table coach( coach_id int not null primary key auto_increment, coach_name varchar(20) not null, coach_leave varchar(20) not null )
-
创建从表(多),在表中添加字段关联主表中的主键字段
create table student( stu_id int not null primary key auto_increment, stu_name varchar(20) not null, stu_phone varchar(20) not null, coach_id int not null, foriegn key (coach_id) references coach(coach_id) )
多对多
一个实体A可以对应多个实体B,一个实体B也可以对应多个实体A。
如一个学生可以学习多门课程,一门课程也可以对应多个学习它的学生。
一个医生可以有多个病人,一个病人也可以对应多个医生。
ER图
在数据库中创建表的过程
-
创建课程表
create table course( course_id int not null primary key auto_increment, course_name varchar(20) not null, course_score int not null )
-
创建学生表
create table student( stu_id int not null primary key auto_increment, stu_name varchar(20) not null, stu_phone varchar(20) not null )
-
体现多对多关系的表:成绩表
该表中必须要包含以上两个实体表中的主键字段
create table score( s_id int not null, course_id int not null, stu_id int not null, cj int not null, -- 可以选择添加外键约束 foreign key (course_id) references course(course_id), foreign key (stu_id) references student(stu_id) )
总结
一对一:创建各自的实体表,在任意一张表中添加另一张表的主键字段,并添加唯一约束
一对多/多对一:先创建主表(一),再创建从表(多),在从表中添加主表中的主键字段,外键可选
多对多:创建各自的实体表,再创建第三张表:“联系表”,在"联系表"中添加两个实体表中的主键字段,外键可选
练习
“医院信息管理系统”。
医生模块:医生的CURD
病人模块:病人的CURD
诊断模块:哪个医生在什么时间诊断了哪个病人,记录信息
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bGvFRGdU-1672307371733)(D:\数据库\day4\数据库day4.assets\image-20221227153819746.png)]
-- 医生表
create table doctor(
id int not null primary key auto_increment,
name varchar(20) not null,
section varchar(20) not null,
major varchar(50) not null
)
-- 病人表
create table patient(
id int not null primary key auto_increment,
name varchar(20) not null,
age int not null,
phone varchar(20),
address varchar(50)
)
-- 诊断记录表
create table diagnose(
diagnose_no int not null primary key auto_increment,
doctor_id int not null,
patient_id int not null,
diagnose_date datetime not null,
diagnose_result text
)
诊断病人流程:
-- 1.管理员登录系统,向医生表中添加一条记录
insert into doctor values(9001,'张海梅','内科','肺炎治疗')
-- 2.医生登录系统,添加病人信息
insert into patient values(null,'王海',20,null,null)
-- 3.记录添加诊断记录
insert into diagnose values(null,9001,1,now(),'没啥问题')
数据库设计规范
数据库设计的规范,简称为范式(NF)。
范式分为第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、BC范式(BCNF)、第四范式(4NF)和第五范式(5NF)共六种。
这六种范式的级别越高,表示数据库设计的结果越规范,每一个高级版的范式都包含了低级别的范式。
通常设计数据库时,只需满足3NF即可。
如有如下数据,黄色背景为联合主键,暂时不满足任何范式。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-b0u4QJxF-1672307371733)(D:\数据库\day4\数据库day4.assets\原始表.png)]
当前表如果要做增删改查的操作,会有如下问题:
- 如果要加入一个新的系别,就要添加学生,系主任等数据
- 如果要删除"刘定宇",他所在的系也会被删除
- 如果要将"王海"的系别改为会计,相应的系主任也要修改
- 当前表中有大量冗余数据
第一范式1NF
数据表中的每一列都是不可再分的原子项。
关系型数据库起码要满足1NF,才能创建表。
上表中的联系方式列,可以再分为手机和QQ两列,所以它不满足1NF。根据1NF的要求,修改后
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-22GRlg2i-1672307371733)(D:\数据库\day4\数据库day4.assets\满足1NF后.png)]
第二范式2NF
在满足1NF的基础上,消除部分依赖。
对于联合主键而言,每一个非主属性字段都需要完全依赖于主属性,而不是依赖其中一部分。
在上图中,无法用学号做主键,需要将学号和科目组合为联合主键,通过联合主键才能得到分数。
出学号和科目外,其他字段都是非主键字段,分数完全依赖于联合主键,其他字段不满足,所以进行如下修改。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rfo80Y4o-1672307371734)(D:\数据库\day4\数据库day4.assets\满足2NF后.png)]
第三范式3NF
在满足2NF的基础上,消除传递依赖。
在上图中,系主任是通过学号->系别->系主任获取,系主任传递依赖于学号,消除这个传递依赖
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HaHmV7RR-1672307371734)(D:\数据库\day4\数据库day4.assets\满足3NF后.png)]
最终根据实体关系模型进行优化,体现对应关系。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wHglyIZy-1672307371734)(D:\数据库\day4\数据库day4.assets\最终优化.png)]
名词解释
主键/主码/主属性
用于唯一区分每条记录的一个字段,称为主键字段,如学号、身份证号。
通常选择能唯一且几乎不会改变的某个字段作为主键。如果没有,自定义一个id列作为主键。
联合主键
如果一个字段不能唯一区分每条记录,而是需要多个字段才能唯一区分时,这些字段一起组成了联合主键。
完全依赖
如果能通过A和B得到C,A和B都不能单独得到C时,称为C完全依赖于A和B。
如(学号+科目)联合主键->分数,分数完全依赖于联合主键。
其他字段完全依赖于学号。
部分依赖
通过A和B可以得到C,单独通过A或B也可以得到C,称为C部分依赖于A和B。
如(学号+科目)联合主键->姓名,其实只通过学号也可以得到姓名,称为姓名部分依赖于联合主键。
除了成绩外,其余字段都是部分依赖于(学号+科目)联合主键
2NF就是消除这种部分依赖,只保留完全依赖。
传递依赖
如果通过A可以得到B,通过B得到C,称为C传递依赖于A。
如学号->系->系主任,其实只通过系也能得到系主任,称为系主任传递依赖于学号。
如果能通过A和B得到C,A和B都不能单独得到C时,称为C完全依赖于A和B。
如(学号+科目)联合主键->分数,分数完全依赖于联合主键。
其他字段完全依赖于学号。
部分依赖
通过A和B可以得到C,单独通过A或B也可以得到C,称为C部分依赖于A和B。
如(学号+科目)联合主键->姓名,其实只通过学号也可以得到姓名,称为姓名部分依赖于联合主键。
除了成绩外,其余字段都是部分依赖于(学号+科目)联合主键
2NF就是消除这种部分依赖,只保留完全依赖。
传递依赖
如果通过A可以得到B,通过B得到C,称为C传递依赖于A。
如学号->系->系主任,其实只通过系也能得到系主任,称为系主任传递依赖于学号。
3NF就是消除这种传递依赖。