【MySQL】E-R图-关系数据模型-3NF--精讲+练习（巨全面）

一.知识储备

E-R图

E-R图，即实体-关系图（Entity-Relationship Diagram），是数据库建模的一种工具，用于表示实体类型、属性以及它们之间的关系。

在E-R图中，实体用矩形表示，属性用椭圆表示，而它们之间的关系用菱形表示。实体之间通过线连接，并在连接线上标明它们之间的基数关系，如1:1、1:n或n:m等。

E-R图的设计是数据库逻辑结构设计的重要步骤，它能够帮助设计者清晰地理解数据间的关系，并为数据库的物理结构设计打下基础。

关系数据模型

关系数据模型是基于集合论的模型，它使用二维表格形式来表示数据，每个表包含多个记录，记录由多个数据项组成，每个数据项对应一个域。关系数据模型中最基本的操作包括选择、投影和连接等，这些都是关系代数的基本操作。

关系模型的核心是关系模式，它定义了表的架构，包括表名、属性名和它们的域。关系模型的优势在于其简洁性和强大的查询能力，它屏蔽了底层实现的细节，使得数据操作更加直观和易于理解。

3NF

第一范式

定义

第一范式（1NF）规定表中的每个列必须是不可分割的基本数据项，即表中的每个单元格必须包含单一的值。如果一个列中包含多个值，则需要将该列拆分为多个独立的列，以确保表结构的原子性。

应用

例如，一个名为“联系人”的表中包含“姓名”、“性别”和“电话”三个字段。如果一个人的联系信息包括家庭电话和公司电话，那么原来的表结构就不符合1NF，因为“电话”这一列包含了多个值。正确的做法是将“电话”字段拆分为“家庭电话”和“公司电话”两个独立的列。

第二范式

定义

第二范式（2NF）是在1NF基础上进一步要求，表中必须有主键，且非主键列必须完全依赖于整条主键，而不是主键的一部分。如果一个表有组合主键，则非主键列不能仅依赖于这个组合键的一部分。

应用

例如，一个订单明细表中包含“订单ID”、“商品ID”、“商品名称”和“商品价格”。如果“商品名称”和“商品价格”仅依赖于“商品ID”，而不仅仅是“订单ID”和“商品ID”的组合，那么这个表就不符合2NF。为了满足2NF，需要将订单明细表拆分为两个表：订单表和商品表，分别保存订单信息和商品信息，并通过外键关联。

第三范式

定义

第三范式（3NF）是在2NF基础上继续要求，表中任何非主属性不依赖于其他非主属性，即不存在传递依赖。这意味着每个非主属性必须直接依赖于主键，而不是通过其他非主属性间接依赖。

应用

例如，一个学生信息表中包含“学号”、“姓名”、“专业”和“学院”。如果“学院”依赖于“专业”，而“专业”又依赖于“学号”，那么这个表就不符合3NF。为了满足3NF，需要将学生信息表拆分为多个表，如学生表、专业表和学院表，通过外键关联来消除传递依赖。

二.绘制E-R图

确定实体
确定联系
把实体类型和联系类型组合，形成E-R图框架
确定实体类型和联系类型的属性
确定实体类型的关键键（主键和外键），在属于关键键的属性名下划一横线

注意事项：

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

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

实体类型的转换

对E-R图中的每一个实体建立一个关系——表，关系包含的属性，要包括E-R图中对应实体所具有的全部属性。实体的属性即为关系的属性，实体的标识符即为关系的键。

有必要的话，比如为了满足范式，图中的关系属性也可以建立一个表。

联系类型的转换

1：1

对于一对一（1:1）关系，可在两个实体类型转换成的两个关系模式中的任意一个关系模式的属性中加入另一个关系模式的键和联系类型的属性。

1：n

对于一对多（1:N）关系，在N端实体类型转换成的关系模式中加入1端实体类型转换成的关系模式的键和联系类型的属性。

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

m：n

对于多对多（M:N）关系，将联系类型转换成关系模式，其属性为两端实体类型的键加上联系类型的属性，而键为两端实体键的组合。

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

注

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

四.练习

范式判断及改错

（1）

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

2NF-->非主键列依赖于主键，本题中的一个课题中包含多个教师（代码），研究课题号， 研究课题名不依赖与主键，不满足第二范式。

教师表（教师代码， 姓名， 职称）

研究课题表（ 研究课题号， 研究课题名，教师代码）

 （2）

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

3NF-->属性之间不相互依赖，所在学院、学院地点和学院电话三者相互依赖，不满足第三范式的条件。

学生表（学号，姓名，年龄，所在学院）

学院表（学院名、学院地点，学院电话）

画E-R图

某工厂需建立一个产品生产管理数据库来管理如下信息:车间编号、车间主任姓名、车间电话，车间职工的职工号、职工姓名、性别、年龄、工种，车间生产的零件号、零件名称、零件的规格型号，车间生产一批零件有一个批号、数量、完成日期(同一批零件可以包括多种零件)。

（1）画E-R图

（2）试按规范化的要求给出关系数据库模式。

（1）做这种题的时候，要先明确各个实体和关系：

工厂

车间——生产——零件

车间——分配——职工

这是读题之后可以读到的大体，这样就可以构造出简单的关系

然后深入读题，读好各个属性

具体的属性如下图

本图画的有些许的崎岖哈哈哈，大家见谅 

 然后说明一下几个点：

1.先分辨好属性和关系，可以结合题目和具体情景，这会为你提供一个框架，对于后续的画图有很大的帮助

2.开始加属性，一个属性在图中只出现一次，当发现有属性重复出现时，就要首先想到需要通过关系来关联，例如本题的职工实体中，本来应包含车间编号属性，但是因为车间和职工之间的雇佣关系，所以可以直接省去

3.当把框架中实体的属性都对应完成后，如果还发现题目中还有其他属性没有对应，就应该想到：

1）在某些实体对应漏了；

2）某些关系需具备属性，例如本题的生产关系；

3）缺实体（一般不会）

4.对于主键的设定，主要是服务于建立数据库模式，每个表都需要主键，并且需要满足三范式。在需要时，一般定义主键。如果发现将一个属性定义为主键时，还会出现查询时出现一对多等的状况时，那此时需考虑建立联合主键，例如本题中，如果以批号为主键时，当我们查询批号，有可能会出现多个零件，不满足范式，此时可以将批号和零件合在一起做联合主键，这样可以避免这种情况。

（2）建立数据库模式，俗称建表

车间表（车间编号、车间主任姓名、车间电话）

职工表（职工号、职工姓名、性别、年龄、工种、车间号）

零件表（零件号、零件名称、零件的规格型号）

批次表（批次、零件号、数量、完成日期）

以上答案仅供参考，如果有问题，欢迎交流。