【E-R图、关系数据模型、3NF】详细原理+大题练习+解析

一、原理

1. 实体联系模型（E-R模型）

实体-联系方法是数据库概念模型/信息模型最常用的表示方法，用以反映现实世界中的事物及其相互联系，相关概念有：

• 实体：是描述客观事物的概念。它可以是人、物或抽象的概念。
• 属性：实体具有的某种特性。如学生的学好、姓名、年龄、性别等。
• 联系：信息世界中联系分为两类：一是实体内部的联系，如组成实体的各属性之间的联系；二是实体之间的联系（包括一对一联系、一对多联系、多对多联系）。

用E-R图描述现实世界的概念模型，描述某一组织的信息模型。相关符号：

❑ E-R图向关系数据模型的转换：

• E-R图中每个实体，相应转换为一个关系，该关系包括对应实体的全部属性，并确定出该关系的关键字。
• 对于“联系集”，根据联系方式不同，采取不同手段以使被它联系的实体所对应的关系彼此实现某种联系。

❑ E-R图转换成相应的表：

• 表是行和列的集合，实体被表示成表的形式。
• 用列标题表示实体的属性。
• 用行表示关于实体的实际数据。

E-R图所表示的概念模型是各种数据模型的共同基础，因而是抽象和描述现实世界的有力工具。

2. 范式

E.F.Codd于1971年提出规范化理论。他定义了五种规范化模式（Normal Form, NF）, 简称范式。在五种范式中，通常只用到前三种。

范式表示的是关系模式的规范化程度，也即满足某种约束条件的关系模式，根据满足的约束条件的不同来确定范式。

❑ 第一范式(1NF)

• 一个关系模式中的属性都是单纯的，即：不可再分为更小的数据项。

❑ 第二范式(2NF)

• 一个关系模式属于1NF，并且所有的非关键字都完全地依赖于关键字，即：不存在部分依赖。

❑ 第三范式(3NF)

• 一个关系模式属于2NF，而且它的任何一个非主属性都不传递依赖于任何主关键字，即：不存在传递依赖。

规范化可以使关系的结构简化，存储尽量减少数据冗余，使数据库的设计更加合理。

3. E-R图的绘制

E-R模型设计步骤：

1. 确定实体类型
2. 确定联系
3. 把实体类型和联系类型组合成E-R图
4. 确定实体类型和联系类型的属性
5. 确定实体类型的关键键，在属于关键键的属性名下划一横线

注意事项：

• 只标识实体属性的关键字，关系属性没有标关键字。
• 如果处理对象是一个比较大的系统，则应该先画出各个部分的子E-R图，然后再合并同类实体，消除冗余。
• 对于一个特定的应用处理对象，所构造的E-R模型可能不唯一。

4. E-R图向关系数据模型的转换

E-R图转换成相应的关系的基本规则：

1.对E-R图中的每一个实体，分别建立一个“关系”，关系包含的属性，要包括E-R图中对应实体所具有的全部属性。

2.对E-R图中的每一个1:n的联系，分别让“1”的一方的关键字进入“n”的一方作为外部关键字。“联系”本生若具有属性，也让它们进入“n”的一方作为外部关键字。

3.对E-R图中的每一个m:n的二元或更多元的“联系”，则为这些联系分别建立一个“关系”，关系的属性要包括对应联系自身的全部属性。（若有的话）还要包括形成该联系的多方实体的关键字。

4.检查按照以上方法所形成的多个“关系”，如果发现有的“关系”最终只含有一个属性，则把这样的“关系”取消。

二、练习

1、 范式判断及改错：
（1） 判断下面的关系表是否满足 2NF， 若不满足， 将其修正为满足 3NF：
科研表（教师代码， 姓名， 职称， 研究课题号， 研究课题名）

参考答案： 不满足 2NF， 修正为：
参考答案 1（教师和课题是多对多关系）：
教师表(教师代码， 姓名， 职称)
课题表(课题号， 课题名)
科研表(教师代码， 课题号)
参考答案 2（教师和课题是一对多关系）：
教师表(教师代码， 姓名， 职称)
课题表(课题号， 课题名， 教师代码)

（2） 判断下面的关系表是否满足 3NF， 若不满足， 将其修正为满足 3NF：
学生关系表（学号, 姓名, 年龄, 所在学院, 学院地点, 学院电话）

参考答案： 不满足 3NF， 修正为：
学生表(学号，姓名，年龄，学院名)
学院表(学院名，地点，电话)

2、选课管理。
一个班级有多个学生，一个学生只属于一个班级。一个学生可以选修多门课程，一门课程可允许多名学生选修。其中：
班级：班号，班名
课程：课程号，课程名，学分
学生：学号，姓名，性别，年龄，班级，课程，学分，成绩
请画出E-R图，并将E-R图转换成关系数据模型。

参考答案：E-R图：

将E-R图转换成关系数据模型：
班级表(班号，班名)
课程表(课程号，课程名，学分)
学生表(学号，姓名，性别，年龄，班号)
选课表(学号，课程号，成绩)

注意事项：注意区分是实体的属性还是关系的属性。如果是两个实体发生联系才有该属性，那么就是关系的属性，比如成绩不能是学生或课程的属性，两者发生联系才有成绩，而学分不需要发生联系才有，所以是课程的属性。
选课表（学号，课程号，成绩）中学号和课程号是主键，也是外键。

3、教学管理。
假设一个学生可选多门误程，而一门误程又有多个学生选修，一个教师至多可讲3门误程，一门课程至多只有一个教师讲授。试画出其教学管理的实体联系模型E-R图，将E-R图转换成关系模式，要求达到第三范式3NF，并标明主键。
已知：
学生信息：学号、姓名、性别、年龄、籍贯、系号、系名；
课程信息：课程号、课程名、学分；
教师信息：教师号、教师名、职称、年龄。

参考答案：E-R图：
参考答案 1：

参考答案 2：

将E-R图转换成关系数据模型：
学生表(学号，姓名，性别，年龄，籍贯，系号)
教师表(教师号，教师名，职称，年龄)
课程表(课程号，课程名，学分，教师号)
选课表(学号，课程号，成绩)
系表(系号，系名)

4、 仓库管理。
某仓库主要管理零件的入库、 出库和采购等事项。 仓库根据需要向外面厂家采购零件，而许多工程项目需要仓库供应零件。 相关信息如下：
工程项目： 编号， 名字， 日期， 零件；
零件： 编号， 名字， 颜色， 重量， 供应商；
供应商：编号， 名字， 地址， 零件。
一个工程需要多个零件， 零件可由多个供应商供应； 每个供应商可生产多种零件， 每种零件可供应给多项工程。
请画出 E-R 图， 并将 E-R 图转换成关系数据模型。

参考答案：E-R图：

将E-R图转换成关系数据模型：
PROJECT(J#, Jname, Date)
PART(P#, Pname, Color, Weight)
SUPPLIER(S#, Sname, Aaddr)
P-P(J#, P#, Total)
P-S(P#, S#, Quantity, Price)

注意事项：有些实体关系的属性要自己补充。虽然题目里没有明确说，但是如果不补充，就显得不完整。比如P-P之间需要有总共需要的零件数，P-S之间需要有零件的需求量和价格信息。

5、图书借阅系统管理。
某图书借阅计算机管理中需要如下信息：
读者：读者编号，姓名，读者类型，已借数量
图书：图书编号，书名，出版社，定价，出版日期
需要对读者借阅进行管里。一个读者可借阅多本图书，而一本图书又可借给多个读者阅读。
试画出其图书借阅管理的实体联系模型E-R图，将E-R图转换成关系模式，要求达到第三范式3NF，并标明主键。

参考答案：E-R图：

将E-R图转换成关系数据模型：
读者表(读者编号，姓名，读者类型，已借数量)
图书表(图书编号，书名，定价，出版社，出版日期)
借阅表(读者编号，图书编号，借期，还期)

注意事项：注意区分是实体的属性还是关系的属性。如果没有发生联系，学生也有已借数量这个属性的，可以为0。
同样注意有些实体关系的属性要自己补充，特别是多对多关系。图书和读者之间就会联想到借书日期和还书日期，有借有还。最后要检查下关系表，加了关系属性后是否可以唯一标识该记录？读者可以借多次相同的书籍，因此借期也应该作为借阅表的主属性。

6、图书管理。
一个图书馆借间管里数据库要求提供下述服务：
(1)可随时查询书库中现有书籍的品种、数量与存放位置。所有各类书籍均可由书号惟一标识。
(2)可随时查询书籍借还情况，包指借书人单位、姓名、借书证号、借书日期和还书日期。
约定：任何人可借多种书，任何一种书可为多个人所借，借书证号具有惟一性。
(3)当需要时，可通过数据库中保存的出版社的电报编号、电话、邮编及地址等信息下相应出版社增购有关书籍。我们约定，一个出版社可出版多种书籍，同一本书仅为一个出版社出版，出版社名具有惟一性。
根据以上情况和假设，试作如下设计：
(1)构造满足需求的E-R图。
(2)转换为等价的关系模式结构。

参考答案：E-R图：

将E-R图转换成关系数据模型：
借书人(借书证号，姓名，单位)
图书(书号，书名，数量，位置，出版社号)
出版社(出版社号，电报编号，电话，邮编，地址)
借阅(借书证号，书号，借书日期，还书日期)

7、运输管理。
设某汽车运输公司有三个实体集： 车队有车队号、 车队名； 车辆有车牌号、 厂家、 出厂
日期； 司机有司机编号、 姓名、 电话。 每个车队可聘用若干司机，有聘期，每个司机只应聘
于一个车队。 每个司机可使用多辆汽车，有使用日期和公里数，每辆车可被多个司机使用。
试画出其运输管理的实体联系模型 ER 图， 将 E-R 图转换成关系模式， 要求达到第三范式 3NF，
并标明主键和外键。

参考答案：E-R图：

将E-R图转换成关系数据模型：
车队(车队号，车队名)        主键：车队号
车辆(车牌照号，厂家，出厂日期)        主键：车牌照号
司机(司机编号，姓名，聘期，车队号)        主键：司机编号；外键：车队号
使用(司机编号，车牌照号，使用日期，车里数)        主键：(司机编号，车牌照号，使用日期)；外键1：司机编号，外键2：车牌照号

8、 医院管理。
某医院病房计算机管理中需要如下信息：
科室： 科名， 科地址， 科电话， 医生姓名
病房： 病房号， 床位号， 所属科室名
医生： 姓名， 职称， 所属科室名， 年龄， 工作证号
病人： 病历号,姓名,性别, 诊断,主管医生, 病房号
其中，一个科室有多个病房、 多个医生；一个医生可负责多个病人的诊治， 一个病人的主管医
生只有一个。 一个病房只能属于一个科室； 一个医生只属于一个科室。
完成如下设计： （1） 设计该计算机管理系统的 E－R 图。 （2） 将该 E-R 图转换为关系模型
结构。 （3） 指出转换结果中每个关系模式的候选码。

参考答案：E-R图：

将E-R图转换成关系数据模型：
科室(科名，科地址，科电话)
病房(病房号，床位号，科名)
医生(工作证号，姓名，职称，年龄，科名)
病人(病历号，姓名，性别，工作证号，病房号，床位号)
候选码已用下划线标注。

9、购物小票。
以下是一个顾客在超市购物的购物小票，请根据小票所提供的信息，设计一个E-R模型，并将这个E-R模型转换成关系数据模型，要求标注主码和外码。

参考答案：E-R图：
参考答案 1：

将E-R图转换成关系数据模型：
收银员表(收银员编号，收银员姓名)        主码：收银员编号
商品表(商品编号，商品名称，单价)        主码：商品编号
购物单表(购物单号，购物时间，收银员编号，总金额)        主码：购物单号；外码：收银员编号
销售表(购物单号，商品编号，数量，金额)        主码：(购物单号，商品编号)；外码1：购物单号，外码2：商品编号
参考答案 2：

将E-R图转换成关系数据模型：
商品表(商品编号，商品名称，单价)        主码：商品编号
购物单表(购物单号，购物时间，收银员编号，总金额)        主码：购物单号
销售表(购物单号，商品编号，数量，金额)        主码：(购物单号，商品编号)；外码1：购物单号，外码2：商品编号

10、成绩通知单。
以下是一个中学学生的成绩通知单所提供的信息， 设计一个 E-R 模型， 并将这个 E-R 模
型转换成关系数据模型， 要求标注主码。

参考答案：E-R图：

将E-R图转换成关系数据模型：
班级表(班级编号，班级名称，班主任)
课程表(课程编号，课程名称)
学生表(学号，姓名，家庭住址，电话，班级编号)
考试表(学号，课程编号，期中成绩，期末成绩)