软考架构——数据库设计与建模
一、数据库结构
典型的数据库结构可归纳为“三级模式，两级映射”：
（一）三级模式
- 外模式：是数据库用户能看见和使用的局部数据的逻辑结构，与某一应用有关的数据逻辑表示。对应用户视图，面向数据库用户。
- 概念（逻辑）模式：是数据库中全体数据的逻辑结构和特征的描述，所有用户的公共数据视图。对应数据库表，面向数据库管理员。
- 内模式：是数据物理结构和存储方式的描述，数据在数据库内部的表示方式。对应物理文件，面向系统程序员。
（二）两级映射
- 外模式 - 模式映射 ：保证 逻辑独立性，即当逻辑模式发生改变时，应用程序不用修改。
- 模式 - 内模式映射 ：保证 物理独立性，即当内模式发生改变时，应用程序不用修改。
两级映射保证了逻辑独立性和物理独立性，逻辑独立比物理独立更难实现。
二、数据库理论
关系型数据库中的 关系 是指由行列组成的二维表文件，每行表示一条记录，每列表示一个属性。关系模型 是关系的逻辑描述，可以表示为：关系名(属性 1, 属性 2, …, 属性 N)。
（一）关系运算
关系代数的基本运算主要有交、并、差、笛卡尔积、选择、投影、连接和除法运算。
交、并、差、笛卡尔积 跟集合论中的运算是一个意思，就是把每张数据表视为行的集合，然后做运算。
选择 是指从数据表中找出符合条件的行（记录）。
投影 是指从数据表中抽取指定的列（属性）。
连接 分为两种：
- θ连接：从两个关系的笛卡尔积中选取属性满足一定条件的元组
- 自然连接（⊳⊲连接）：两个关系中进行比较的分量必须是相同的属性，并且在结果中将重复的属性去掉
（二）函数依赖
函数依赖（Functional Dependency）是数据库理论中的一个重要概念，用于描述属性之间的关系。在关系数据库中，如果属性集 X 的值能够唯一地决定属性集 Y 的值，我们就说Y 函数依赖于 X，记作 X→Y。
函数依赖的定义：
给定一个关系 R，如果对于关系 R 中的任意两个元组 t1 和 t2，若 t1 和 t2 在属性集 X 上的值相同，则它们在属性集 Y 上的值也必须相同，那么我们就说 Y 函数依赖于 X。
函数依赖的类型：
- 平凡函数依赖：如果属性集 Y 是属性集 X 的一个子集，则称 X 决定 Y 的依赖关系为平凡依赖。例如 X→X
- 非平凡函数依赖：属性 Y 不是属性 X 的子集，则称 X 决定 Y 的依赖关系为非平凡依赖。可以进一步分类：
- 完全函数依赖：如果在一个复合属性集 X 中，Y 仅依赖于 X 的全部属性，而不依赖于 X 的任何真子集。
- 部分函数依赖：如果 Y 依赖于 X 的某个真子集。
- 传递函数依赖：如果 X 函数依赖于 Y，而 Y 又函数依赖于 Z，那么可以说 X 传递函数依赖于 Z。
简单点说：左右包含是平凡，左右互斥非平凡，若无子集是完全，若有子集是部分，环环相连是传递。
（三）Armstrong 公理
Armstrong 公理是一组用于推导关系数据库中函数依赖的基本规则，通过这些规则，可以推导出任意一组函数依赖的所有隐含依赖。
- 自反性：若 X⊇Y，则 X→Y。即一个属性集必然决定其子集。
- 增广性：若 X→Y，则 XZ→YZ。即如果 X 决定 Y，那么 X 与 Z 的组合也决定 Y 与 Z 的组合。
- 传递性：若 X→Y 且 Y→Z，则 X→Z。即如果 X 决定 Y，且 Y 决定 Z，那么 X 也决定 Z。
- 合并性：若 X→Y 且 X→Z，则 X→YZ。即如果 X 决定 Y，且 X 也决定 Z，那么 X 决定 YZ。
- 分解性：若 X→Y 且 Y⊇Z，则存在 X→Z。即如果 X 决定 Y，而 Z 是 Y 的子集，那么 X 决定 Z。
（四）关系分解
在数据库关系分解中，有两个重要概念：
1. 无损连接
无损链接指的是将一个关系分解成多个子关系后，通过对这些子关系进行自然连接操作，可以无损地恢复原始关系。换句话说，在连接后得到的结果应该与原始关系完全相同，没有任何信息增加或丢失。
无损连接分解的 判定方法 为：
设ρ={R1, R2}是 R 的一个分解，F 是 R 上的函数依赖集。那么分解ρ相对于 F 是无损级联分解的充要条件为 (R1∩R2)→(R1−R2) 或 (R1∩R2)→(R2−R1) 。 注意两个条件只要有任意一个条件成立即可。
2. 保持依赖
保持依赖指的是在关系分解后，仍然能够通过子关系中的函数依赖来推导出原始关系中的所有函数依赖。换句话说，分解后的子关系应该能够保持原始关系中的所有重要的依赖关系。
举例来说，假设有一个关系 R(A, B, C)，并且它有函数依赖：A→B 和 B→C。
- 如果将关系 R 分解为 R1(A, B)和 R2(B, C):
- 通过属性 B 连接 R1 和 R2，可以恢复 R，因此是无损分解。
- 关系 R1 保留了 A→B，关系 R2 保留了 B→C，因此是保持依赖。
- 如果将关系 R 分解为 R1(A, B)和 R3(A, C):
- 通过属性 A 连接 R1 和 R3，可以恢复 R，因此是无损分解。
- 关系 R1 保留了 A→B，关系 R3 保留了 A→C，但是 B→C 丢失，因此不是保持依赖。
（五）设计范式
数据库设计范式是指在设计数据库时遵循的一系列规范和原则，以确保数据的组织、存储和管理高效、合理。数据库主要范式包括：
1. 第一范式（1NF）
每个字段的值都是原子值，不能有重复的列或多值字段。
不符合 1NF 的表：
| 学生 ID | 学生姓名 | 课程 |
|---|---|---|
| 1 | 张三 | 数学, 物理 |
在这个例子中，”课程”属性是个多值字段。需要重新设计成符合 1NF 的表：
| 学生 ID | 学生姓名 | 课程 |
|---|---|---|
| 1 | 张三 | 数学 |
| 1 | 张三 | 物理 |
2. 第二范式（2NF）
在满足 1NF 的基础上，要求消除部分依赖，即每个非主键字段必须完全依赖于主键，而不是主键的一部分。
不符合 2NF 的表：
| 学生 ID | 课程 | 学生姓名 |
|---|---|---|
| 1 | 数学 | 张三 |
| 1 | 物理 | 张三 |
| 2 | 语文 | 李四 |
在这个例子中，”学生姓名”只依赖于”学生 ID”，而不依赖于”课程”。我们可以分成两个符合 2NF 的表：
学生表：
| 学生 ID | 学生姓名 |
|---|---|
| 1 | 张三 |
| 2 | 李四 |
选课表：
| 学生 ID | 课程 |
|---|---|
| 1 | 数学 |
| 1 | 物理 |
| 2 | 语文 |
3. 第三范式（3NF）
在满足 2NF 的基础上，要求消除传递依赖，即非主键字段不能依赖于其他非主键字段。
不符合 3NF 的表：
| 学生 ID | 学生姓名 | 班级 | 班主任 |
|---|---|---|---|
| 1 | 张三 | 一班 | 王老师 |
| 2 | 李四 | 一班 | 王老师 |
在这个例子中，”班主任”依赖于”班级”，而不是”学生 ID”。可以进一步分成两个符合 3NF 的表：
学生表：
| 学生 ID | 学生姓名 | 班级 |
|---|---|---|
| 1 | 张三 | 一班 |
| 2 | 李四 | 一班 |
班级表：
| 班级 | 班主任 |
|---|---|
| 一班 | 王老师 |
4. 博茨 - 科得范式（BCNF）
3NF 的强化版本，要求每个决定因素（函数依赖的左侧）必须是超键，进一步消除所有依赖问题。
不符合 BCNF 的表：
| 学生 ID | 课程 | 教师 |
|---|---|---|
| 1 | 数学 | 李老师 |
| 1 | 物理 | 李老师 |
| 2 | 数学 | 张老师 |
在这个例子中，”教师”依赖于”课程”，而”课程”不是超键。可以将其分为两个符合 BCNF 的表：
课程表：
| 课程 | 教师 |
|---|---|
| 数学 | 李老师 |
| 物理 | 李老师 |
| 语文 | 张老师 |
选课表：
| 学生 ID | 课程 |
|---|---|
| 1 | 数学 |
| 1 | 物理 |
| 2 | 数学 |
5. 第四范式（4NF）
解决多值依赖的问题，要求每个表只能包含一个多值依赖。
不符合 4NF 的表：
| 学生 ID | 课程 | 兴趣 |
|---|---|---|
| 1 | 数学 | 足球 |
| 1 | 物理 | 篮球 |
在这个例子中，”课程”和”兴趣”之间存在多值依赖。可以将其分为两个符合 4NF 的表：
选课表：
| 学生 ID | 课程 |
|---|---|
| 1 | 数学 |
| 1 | 物理 |
兴趣表：
| 学生 ID | 兴趣 |
|---|---|
| 1 | 足球 |
| 1 | 篮球 |
三、数据库设计
数据库设计主要包含如下阶段：
- 需求分析阶段：通过结构化分析，得到数据流图和数据字典
- 概念设计阶段：根据功能模型，创建局部 ER 图，并合并为全局 ER 图（建立外模式）
- 逻辑设计阶段：将 ER 图转换为数据库的关系模型（建立逻辑模式）
- 物理设计阶段：根据数据逻辑模式，选择高效的存储结构和存储方法（建立内模式）
（一）概念设计
在结构化分析阶段，我们通过自顶向下的方式建立的系统的功能模型，以数据流图和数据字典表示。数据库的概念设计阶段，实际上就是建立系统的数据模型。
数据库的概念设计采用 自底向上 的方式，其任务是建立系统的数据模型（通常以 ER 图表示），主要步骤为：
- 选择局部应用
- 设计局部 ER 图
- 合并得到全局 ER 图
- 优化全局 ER 图
1. ER 图元素
ER 图用于描述系统的各种实体及其之间的关系，主要包含三个基本元素：
- 实体：系统中的对象或概念，用矩形框表示。
- 属性：实体具有的特性，用椭圆形表示。
- 联系：用菱形框表示，通过连线与有关的实体相连，在连线上注明对应关系
- 1:1 表示一对一关系
- 1:n 表示一对多关系
- m:n 表示多对多关系
2. ER 图合并
在合并 ER 图时，需要解决局部 ER 图之间的冲突问题，与合并之后全局 ER 图的冗余问题。
冲突有三种类型：
- 属性冲突：属性名和属性含义相同，但属性的取值类型或取值范围不同。
- 命名冲突 ： 异名同义 （不同属性名表示同一种属性含义），或是 同名异义（同一属性名表示不同的属性含义）。
- 结构冲突：
- 同一对象在不同 ER 图中具有不同的抽象。例如某个对象在一个 ER 图中是实体，在另一个 ER 图中是属性。
- 同一实体在不同 ER 图中所包含的属性个数和属性排列次序不完全相同。
- 实体之间的关系在不同 ER 图中呈现为不同的联系。
冗余有两种类型：
- 冗余属性：可由基本数据导出的属性
- 冗余联系：可由其他联系导出的联系
消除冲突通常是结合业务需求，通过讨论与协商来处理。
消除冗余通常是以数据流图和数据字典为依据，通过分析数据项之间的逻辑关系来处理。
（二）逻辑设计
数据库的逻辑设计是指将 ER 图中的实体、属性、联系转换为 DBMS 数据库的关系模式，并进行范式规约。
1. ER 图转换
ER 图中的实体和属性可以直接转换为数据库中的一个关系模式（一张表），主键为实体，其他字段为属性。
ER 图中的联系，根据不同的类型，有不同的转换方式：
(1) 1:1 联系
转换方式：
- 转换为一个独立的关系模式：联系两端的实体主键作为属性。ER 图转换结果：
- 员工(工号，姓名，性别)
- 办公电脑(电脑编号，电脑配置)
- 使用(工号，电脑编号)
- 与任意一端实体的关系模式合并：将一个实体的主键作为另一个实体的外键。ER 图转换结果：
- 员工(工号，姓名，性别，电脑编号)
- 办公电脑(电脑编号，电脑配置)
(2) 1:n 联系
转换方式：
- 转换为一个独立的关系模式：联系两端的实体主键作为属性。ER 图转换结果：
- 班级(编号，年级，人数)
- 学生(学号，姓名，性别)
- 隶属(学号，编号)
- 与多端的实体的关系模式合并：在多端实体关系模式中，添加一端实体主键作为外键。ER 图转换结果：
- 班级(编号，年级，人数)
- 学生(学号，姓名，性别，编号)
(3) m:n 联系
转换方式：
- 转换为一个独立的关系模式：联系两端的实体主键作为属性。ER 图转换结果：
- 员工(工号，姓名，性别)
- 角色(角色编号，角色名称，角色权限)
- 使用(工号，角色编号)
2. 范式规约
在转换为数据库的关系模式后，还要对关系模式进行规范化。不规范的关系模式会出现以下问题：
- 数据冗余：重复存储相同的数据，浪费存储空间，并增加维护的复杂性。
- 修改异常：数据在多个地方被重复存储时，若更新某一处而未同步其他地方，可能导致信息不一致。
- 插入异常：需要插入新数据时，可能由于缺乏某些信息而无法完成插入。
- 删除异常：删除某条记录可能会意外删除有用的信息。
数据库规范化，通常是从 1NF 开始，逐步推进到更高级的设计范式。主要步骤包括：
- 分析现有数据结构：查看当前的数据模型，识别实体、属性及其关系。
- 识别主键：为每个表选择适当的主键。
- 应用每一范式的规则：逐步应用上述每一范式的规则，检查和修改表结构。
- 拆分和重组表：根据依赖关系，将数据拆分到多个表中，确保每个表遵循相关的范式。
- 设置外键：为建立表之间的关系，添加外键约束，确保数据的完整性。
- 验证和测试：在设计完成后，进行测试，确保数据完整性、查询效率和操作便利性。
