关系emp与规范化
关系emp
关系 emp
(Entity-Relationship Model with Participation)是一种用来描述实体和实体之间关系的模式,通常用于数据库设计。它特别强调参与的概念,即实体如何参与关系中的角色。这种关系在数据库设计中很常见,尤其是设计员工(emp
)系统时,常用来管理人员的各种信息和与其他实体的关联。
1. 实体(Entities)
在数据库中,实体可以理解为现实世界中的对象或事物。比如,emp
可能表示 “员工(employee)” 这样的实体。每个实体都会有一组属性(属性是对实体的描述)。
- 举例:对于员工实体,常见的属性有:
emp_id
:员工编号emp_name
:员工姓名emp_age
:员工年龄emp_salary
:员工薪资emp_dept
:员工所属部门
2. 关系(Relationships)
关系描述实体之间如何相互关联。在emp
系统中,员工可能与多个实体有关系,比如部门、项目、职位等。
- 举例:
- 员工和部门:一个员工属于某个部门,一个部门可以有多个员工。这种关系就是 一对多(1:N) 的关系。
- 员工和项目:一个员工可以参与多个项目,一个项目也可以有多个员工。这种关系就是 多对多(M:N) 的关系。
3. 参与(Participation)
参与表示实体是否必须出现在某个关系中。它可以分为全参与和部分参与。
- 全参与(Total Participation):某个实体中的每一个实例都必须参与某个关系。例如,在一个系统中,可能规定每个员工都必须属于某个部门。
- 部分参与(Partial Participation):实体中的部分实例可以不参与某个关系。例如,可能有些员工不参与任何项目。
4. ER图(实体-关系图)
为了表示 emp
的各种关系,可以通过 ER 图形象化表示。在 ER 图中:
- 实体用矩形表示。
- 关系用菱形表示。
- 属性用椭圆表示。
示例:
如果我们设计一个员工管理系统,可能涉及到的 ER 图如下:
- 实体:
Employee
、Department
、Project
- 关系:
Works_for
(员工属于部门)、Works_on
(员工参与项目) - 属性:
Employee
可能有emp_id
,emp_name
等属性,Department
可能有dept_id
,dept_name
等属性。
5. 关系的度数(Cardinality)
在 emp
系统中,关系的度数描述了每个实体能够关联其他实体的最大数目。常见的度数有:
- 一对一(1:1):每个员工只属于一个职位,每个职位只对应一个员工。
- 一对多(1:N):一个部门可以有多个员工,但一个员工只能属于一个部门。
- 多对多(M:N):一个员工可以参与多个项目,一个项目也可以有多个员工。
6. 示例:员工与部门的关系
假设我们有以下两个实体:
- Employee:员工实体,包含
emp_id
,emp_name
,emp_age
,emp_salary
- Department:部门实体,包含
dept_id
,dept_name
它们的关系可以是 Works_for
,表示一个员工属于某个部门。这个关系的 ER 图可以表示为:
- 实体 Employee 与 实体 Department 通过 关系 Works_for 相连,
Works_for
的度数为 1:N,表示一个员工只能属于一个部门,但一个部门可以有多个员工。
总结来说,关系 emp
主要用于表示数据库中的实体(如员工)的各种属性及其与其他实体的关系(如部门、项目等)。在数据库设计中,这样的关系建模可以帮助明确系统中的各类实体如何关联,并且确保数据结构合理性。
规范化
在数据库设计中,规范化(Normalization)是一种将数据结构组织得更高效的方法。其主要目标是减少数据冗余和提高数据完整性。规范化通常通过不同的范式(Normal Form, NF)实现,每种范式都有自己的规则和目标。常见的有第一范式(1NF)、第二范式(2NF)、第三范式(3NF) 和 BCNF(Boyce-Codd范式)。
1. 第一范式(1NF)
定义:第一范式要求数据库表中的每个字段都是原子的,即字段不可再分割,表中的每一列都只能存储一个单一值。
特点:
- 每个表格中的字段值必须是不可再分割的单一值。
- 数据表中不能有重复的列,也不能有多值的列。
- 行的顺序和列的顺序可以任意调换。
示例:
不符合1NF的表:
学生ID | 学生姓名 | 课程 |
---|---|---|
1 | 张三 | 数学, 物理 |
2 | 李四 | 英语 |
上表中的 课程
列包含多个值,这违反了 1NF。
符合1NF的表:
学生ID | 学生姓名 | 课程 |
---|---|---|
1 | 张三 | 数学 |
1 | 张三 | 物理 |
2 | 李四 | 英语 |
2. 第二范式(2NF)
定义:第二范式是在满足 1NF 的基础上,要求数据表中每个非主属性都完全依赖于主键,即没有部分依赖(partial dependency)。如果主键是组合主键(由多个字段组成),则所有非主属性必须依赖于整个组合主键,而不是组合主键中的某一部分。
特点:
- 满足 1NF。
- 消除部分依赖:非主属性不能依赖于组合主键中的某一个部分。
示例:
不符合2NF的表:
学生ID | 课程ID | 成绩 | 学生姓名 |
---|---|---|---|
1 | 101 | 90 | 张三 |
2 | 102 | 85 | 李四 |
在上面的表格中,主键是 学生ID
和 课程ID
的组合。但是,学生姓名
只依赖于 学生ID
,而与 课程ID
无关,这是一种部分依赖,不符合2NF。
符合2NF的表:
学生表:
学生ID | 学生姓名 |
---|---|
1 | 张三 |
2 | 李四 |
成绩表:
学生ID | 课程ID | 成绩 |
---|---|---|
1 | 101 | 90 |
2 | 102 | 85 |
通过将 学生姓名
提取到单独的学生表中,消除了部分依赖,符合2NF。
3. 第三范式(3NF)
定义:第三范式是在满足 2NF 的基础上,要求非主属性不传递依赖于主键,即非主属性不能依赖于其他非主属性。
特点:
- 满足 2NF。
- 消除传递依赖:非主属性不能通过其他非主属性间接依赖主键。
示例:
不符合3NF的表:
学生ID | 系ID | 系名称 |
---|---|---|
1 | 001 | 计算机系 |
2 | 002 | 数学系 |
在此表中,学生ID
是主键,系名称
依赖于 系ID
,而 系ID
又依赖于 学生ID
。因此,系名称
是通过 系ID
间接依赖于主键的,这违反了3NF。
符合3NF的表:
学生表:
学生ID | 系ID |
---|---|
1 | 001 |
2 | 002 |
系表:
系ID | 系名称 |
---|---|
001 | 计算机系 |
002 | 数学系 |
通过将 系名称
和 系ID
放在一个单独的系表中,消除了传递依赖,符合3NF。
4. Boyce-Codd范式(BCNF)
定义:BCNF 是 3NF 的一个强化版本。BCNF 要求数据库表中的每个决定码(determinant,决定其他字段的属性)必须是候选键(candidate key)。如果存在非主属性决定了主键的情况,即存在不依赖于候选键的决定码,就不符合 BCNF。
特点:
- 满足 3NF。
- 表中每个决定属性必须是候选键。
- 消除某些特殊的依赖情况,如当表中有多个候选键且部分候选键依赖于另一个候选键时。
示例:
不符合BCNF的表:
课程ID | 教师ID | 教师姓名 |
---|---|---|
101 | 10 | 张老师 |
102 | 20 | 李老师 |
在这个表中,课程ID
和 教师ID
共同组成候选键。然而,教师姓名
依赖于 教师ID
,而 教师ID
不是主键的一部分,这就违反了 BCNF 的规则。
符合BCNF的表:
教师表:
教师ID | 教师姓名 |
---|---|
10 | 张老师 |
20 | 李老师 |
课程表:
课程ID | 教师ID |
---|---|
101 | 10 |
102 | 20 |
通过将 教师姓名
从原表中移出,确保每个决定属性都是候选键,符合BCNF。
总结:
- 1NF:每列保持原子性,即字段不能有多个值。
- 2NF:消除部分依赖,即每个非主属性必须完全依赖于主键。
- 3NF:消除传递依赖,非主属性不依赖其他非主属性。
- BCNF:更严格,要求每个决定属性都必须是候选键,防止特殊依赖情况。
示例
结合 emp
(假设表示 “员工”)这一实体,讲解数据库的范式(1NF、2NF、3NF 和 BCNF)时,我们可以使用员工管理系统为例,逐步展示规范化如何改善数据结构、消除冗余和保证数据一致性。
1. 第一范式(1NF):数据原子化
要求:表中每个字段的值都是原子的,不可再分割,不能包含多个值。
不符合1NF的表:
emp_id | emp_name | emp_phone | emp_address |
---|---|---|---|
1 | 张三 | 13800000000, 13900000000 | 北京市海淀区, 北京市朝阳区 |
- 问题:
emp_phone
列和emp_address
列都包含多个值,这违反了 1NF。
符合1NF的表:
emp_id | emp_name | emp_phone | emp_address |
---|---|---|---|
1 | 张三 | 13800000000 | 北京市海淀区 |
1 | 张三 | 13900000000 | 北京市朝阳区 |
- 修改方式:我们将每个电话号码和地址都分成独立的行。这样,每个字段中的值是原子性的,即不可再分割的。
2. 第二范式(2NF):消除部分依赖
要求:在满足 1NF 的基础上,所有非主属性必须完全依赖于主键,而不是部分依赖于组合主键的一部分。换句话说,表中如果有组合主键,非主属性不能只依赖于其中的一个部分。
不符合2NF的表:
emp_id | dept_id | emp_name | dept_name | salary |
---|---|---|---|---|
1 | 10 | 张三 | 销售部 | 5000 |
2 | 20 | 李四 | 财务部 | 6000 |
- 问题:这里的组合主键是
emp_id
和dept_id
。但是,emp_name
只依赖于emp_id
,dept_name
只依赖于dept_id
,这是部分依赖,不符合 2NF。
符合2NF的表:
员工表:
emp_id | emp_name | salary |
---|---|---|
1 | 张三 | 5000 |
2 | 李四 | 6000 |
部门表:
dept_id | dept_name |
---|---|
10 | 销售部 |
20 | 财务部 |
关系表(员工所属部门):
emp_id | dept_id |
---|---|
1 | 10 |
2 | 20 |
- 修改方式:我们将员工信息(如
emp_name
)和部门信息(如dept_name
)分离到不同的表中。这样,非主属性不再部分依赖于组合主键的一部分,而是完全依赖于主键。
3. 第三范式(3NF):消除传递依赖
要求:在满足 2NF 的基础上,非主属性之间不能有传递依赖,非主属性不能通过另一个非主属性间接依赖主键。
不符合3NF的表:
emp_id | emp_name | dept_id | dept_name | dept_location |
---|---|---|---|---|
1 | 张三 | 10 | 销售部 | 北京 |
2 | 李四 | 20 | 财务部 | 上海 |
- 问题:
dept_location
依赖于dept_id
,而dept_id
又依赖于主键emp_id
,这是传递依赖,不符合 3NF。
符合3NF的表:
员工表:
emp_id | emp_name | dept_id |
---|---|---|
1 | 张三 | 10 |
2 | 李四 | 20 |
部门表:
dept_id | dept_name | dept_location |
---|---|---|
10 | 销售部 | 北京 |
20 | 财务部 | 上海 |
- 修改方式:我们将部门的位置信息提取到一个单独的
部门表
中。这样,消除了dept_location
的传递依赖,非主属性不再通过其他非主属性依赖主键,符合3NF。
4. Boyce-Codd范式(BCNF):消除候选键间的依赖
要求:在满足 3NF 的基础上,任何决定属性必须是候选键。BCNF 解决了 3NF 中候选键不一致的问题。
不符合BCNF的表:
emp_id | dept_id | emp_position | dept_manager |
---|---|---|---|
1 | 10 | 销售经理 | 张经理 |
2 | 20 | 财务主管 | 李经理 |
- 问题:这里存在两个候选键
emp_id
和dept_id
,其中dept_manager
依赖于dept_id
,但dept_id
不是主键。这不符合 BCNF 的要求。
符合BCNF的表:
员工表:
emp_id | dept_id | emp_position |
---|---|---|
1 | 10 | 销售经理 |
2 | 20 | 财务主管 |
部门表:
dept_id | dept_manager |
---|---|
10 | 张经理 |
20 | 李经理 |
- 修改方式:我们将
dept_manager
移到部门表
中,确保所有决定属性都是候选键,消除候选键间的依赖,符合 BCNF。
总结
通过规范化范式的应用,emp
(员工表)可以逐步优化:
- 1NF:每列必须保持原子性,不可包含多个值。
- 2NF:消除部分依赖,所有非主属性必须完全依赖主键。
- 3NF:消除传递依赖,非主属性不能通过其他非主属性间接依赖主键。
- BCNF:更严格的要求,确保所有决定属性必须是候选键,防止候选键之间的依赖。