数据库底层原理（1）

今天来讲讲数据库

数据库系统（DBMS）

分为关系型数据库（RDBMS）和非关系型（Nosql DBMS）

关系型数据库有:MySQL、Oracle、SQL Server、PostgreSQL、SQLite

非关系型数据库：MongoDB、Redis

数据库的三级模式，两级映像

1.绪论

发展历程 记住数据怎么保存，谁保存数据，共享性如何，独立性如何 人工管理阶段 数据不保存 应用程序管理数据 数据不共享 数据不具有独立性 文件系统阶段 数据可以长期保存 文件系统管理数据 数据共享性差，冗余度大 数据独立性差

数据库系统阶段

  1. 数据结构化
  2. DBMS统一管理系统
  3. 数据共享性高，冗余度低，易扩充
  4. 数据独立性高
数据模型
  • 数据模型的 三要素：数据结构(静态特性)，数据操作(动态特性)，数据的完整性约束
  • 数据模型是对现实世界 数据特征的抽象，
概念模型(E-R图)
  • 按照 用户的观点来对数据和信息建模，不依赖于具体的计算机系统，不是某一个DBMS支持的数据模型，主要用于 数据库设计
逻辑模型
  • 按照 计算机系统的观点对数据建模，主要用于 数据库管理系统的实现。
  • 层次模型
  • 网状模型
  • 关系模型
  • 面向对象模型
  • 对象关系模型

数据库系统概念

  1. 数据
    • 描述事物的 符号记录，如数字，图像，文本等，经过数字化处理后存入计算机。
  2. 数据库
    • 长期存储在计算机内，有组织，可共享的大量数据的集合。数据库中的数据按照一定的 数据模型 组织，描述，存储，具有较小的 冗余度，较高的 数据独立性，易扩展性。
  3. 数据库管理系统
    • 位于 用户 和 操作系统 之间的一层 数据管理软件。用于科学地组织和存储数据，高效地获取和维护数据。DBMS的主要功能包括: 1. 数据定义2. 数据操纵3. 数据库的运行管理功能4. 数据库建立与维护功能。
  4. 数据库系统
    • DBS是指在计算机系统中 引入数据库后的系统构成。数据库系统由 数据库，数据库管理系统，应用系统，数据库管理员构成。

三级模式

外模式
  • 外模式也称为用户模式。外模式是数据库 用户 能够看见和使用的 局部数据的逻辑结构和 特征的描述，是 数据库用户 的 数据视图，是与某一应用有关的数据的逻辑表示。
模式
  • 模式也称为逻辑模式，是数据库中 全体数据的逻辑结构和特性的描述，是 全体用户的公共数据视图。（外模式是模式的 子集）
内模式
  • 内模式也称为存储模式，是数据在数据库系统内部的表示，对数据的 物理结构 和 存储方式 的描述
优点
  • 数据有较高的逻辑独立性和物理独立性
  • 简化了用户窗口
  • 有利于数据共享
  • 数据的安全保密
  • 数据存储由DBMS管理(用户无需考虑存储细节)

数据与程序的独立性

  • 主要包括两个方面—— 逻辑独立性 ，物理独立性
逻辑独立性
  • 当 模式 改变时(增加新的属性列),由数据库管理员对 各个 外模式/模式映像作相应的改变，可以使得 外模式不变，从而应用程序不必修改，保证了 数据与程序的逻辑独立性 。
物理独立性
  • 当数据的 存储结构发生改变时，由 数据库管理员对 模式/内模式映像作出相应改变，可以使 模式保持不变，从而应用程序不必改变，从而保证了 数据与程序的物理独立性。

并发控制

并发控制是数据库管理系统（DBMS）中的一项关键技术，用于管理多个事务同时访问和修改数据库中的数据，以确保数据的一致性和完整性。并发控制的主要目标是防止多个事务之间的冲突，避免数据的不一致状态。常见的并发控制技术包括锁机制、时间戳排序、乐观并发控制和多版本并发控制（MVCC）。

1. 锁机制 (Locking)

锁机制是数据库管理系统（DBMS）中用于管理并发事务的一种重要技术。通过在数据项上加锁，可以防止多个事务同时访问和修改同一数据项，从而确保数据的一致性和完整性。锁机制主要包括共享锁（S锁）和排他锁（X锁），以及一些高级锁机制如意向锁、死锁检测和锁升级等。

1. 锁的类型

1.1 共享锁（Shared Lock, S锁）

  • 定义：允许多个事务同时读取数据项，但不允许任何事务修改数据项。
  • 特点：
    • 多个事务可以同时持有同一数据项的共享锁。
    • 如果一个事务已经持有了某个数据项的共享锁，其他事务可以请求并获得该数据项的共享锁。
    • 如果一个事务请求排他锁，必须等待所有共享锁释放。

1.2 排他锁（Exclusive Lock, X锁）

  • 定义：只允许一个事务读取和修改数据项，其他事务不能访问该数据项。
  • 特点：
    • 只有一个事务可以持有某个数据项的排他锁。
    • 如果一个事务已经持有了某个数据项的排他锁，其他事务不能请求该数据项的任何类型的锁。
    • 排他锁可以防止其他事务读取或修改数据项。

2. 锁的兼容性

锁的兼容性矩阵如下表所示，表示不同类型的锁在同一数据项上的兼容性：

S锁 X锁
S锁 兼容 不兼容
X锁 不兼容 不兼容
  • S锁与S锁：兼容，多个事务可以同时持有同一数据项的共享锁。
  • S锁与X锁：不兼容，如果一个事务已经持有了某个数据项的共享锁，其他事务不能请求该数据项的排他锁。
  • X锁与X锁：不兼容，只有一个事务可以持有某个数据项的排他锁。

3. 锁的请求和释放

3.1 请求锁

  • 请求共享锁：事务在读取数据项之前，请求共享锁。
  • 请求排他锁：事务在修改数据项之前，请求排他锁。

3.2 等待队列

  • 等待队列：如果数据项已经被其他事务锁定，请求锁的事务将进入等待队列。
  • 优先级：等待队列中的事务按照请求锁的时间顺序进行排队，也可以根据优先级进行调度。

3.3 释放锁

  • 自动释放：事务在提交或回滚时，自动释放持有的锁。
  • 显式释放：某些情况下，事务可以显式地释放锁。

4. 意向锁 (Intent Locks)

意向锁用于表示事务对某个数据项的子集有锁定意图，分为意向共享锁（IS锁）和意向排他锁（IX锁）。

4.1 意向共享锁（IS锁）

  • 定义：表示事务打算对某个数据项的子集请求共享锁。
  • 特点：
    • 兼容S锁和IS锁。
    • 不兼容X锁和IX锁。

4.2 意向排他锁（IX锁）

  • 定义：表示事务打算对某个数据项的子集请求排他锁。
  • 特点：
    • 兼容IS锁和IX锁。
    • 不兼容S锁和X锁。

5. 死锁检测和预防

5.1 死锁检测

  • 死锁：多个事务互相等待对方释放锁，导致所有事务都无法继续执行。
  • 检测方法：通过构建等待图（Wait-for Graph）来检测死锁。
    • 等待图：每个事务是一个节点，如果事务A等待事务B的锁，则在A和B之间画一条有向边。
    • 检测环：如果等待图中存在环，则表示发生了死锁。

5.2 死锁预防

  • 超时：设置事务的超时时间，超过时间后自动回滚事务。
  • 锁顺序：要求事务按照固定的顺序请求锁，避免循环等待。
  • 死锁解除：选择一个或多个事务进行回滚，解除死锁。

6. 锁升级 (Lock Escalation)

锁升级是指将多个细粒度的锁合并为一个粗粒度的锁，以减少锁的数量，提高系统性能。

  • 目的：减少锁的管理和存储开销，提高并发性能。
  • 时机：当某个事务持有的锁数量超过一定阈值时，触发锁升级。
  • 类型：
    • 从行锁升级为表锁：将多个行锁合并为一个表锁。
    • 从页锁升级为表锁：将多个页锁合并为一个表锁。

示例

假设有一个银行账户表 Account，包含 account_id 和 balance 两个字段。有两个事务 T1 和 T2 同时操作同一个账户：

  • T1：从账户 A 中取出 100 元。
  • T2：向账户 A 中存入 50 元。
1
2
3
4
5
6
7
8
9
10
11
-- T1
BEGIN TRANSACTION;
SELECT balance FROM Account WHERE account_id = 1 FOR UPDATE; -- 请求 X 锁
UPDATE Account SET balance = balance - 100 WHERE account_id = 1;
COMMIT;

-- T2
BEGIN TRANSACTION;
SELECT balance FROM Account WHERE account_id = 1 FOR UPDATE; -- 请求 X 锁
UPDATE Account SET balance = balance + 50 WHERE account_id = 1;
COMMIT;

在这个例子中，T1 和 T2 都请求了排他锁（X锁）。假设 T1 先请求锁并获得锁，T2 将进入等待队列。当 T1 提交事务并释放锁后，T2 才能获得锁并继续执行。

总结

锁机制是数据库管理系统中确保数据一致性和完整性的关键技术。通过合理使用共享锁和排他锁，可以有效管理并发事务，避免数据冲突。此外，意向锁、死锁检测和预防、锁升级等高级锁机制进一步提高了系统的性能和可靠性。

2. 时间戳排序 (Timestamp Ordering)

时间戳排序是一种基于时间戳的并发控制技术，通过为每个事务分配一个唯一的时间戳，确保事务按照时间顺序执行。

工作原理

  • 分配时间戳：每个事务开始时，系统为其分配一个唯一的时间戳。
  • 读操作：事务在读取数据项时，检查数据项的时间戳。如果数据项的时间戳大于事务的时间戳，则事务回滚。
  • 写操作：事务在修改数据项时，检查数据项的时间戳。如果数据项的时间戳大于事务的时间戳，则事务回滚。

示例

假设有两个事务 T1 和 T2，分别有时间戳 1 和 2：

  • T1：从账户 A 中取出 100 元。
  • T2：向账户 A 中存入 50 元。
1
2
3
4
5
6
7
8
9
10
11
-- T1 (时间戳 1)
BEGIN TRANSACTION;
SELECT balance FROM Account WHERE account_id = 1; -- 读取数据项
UPDATE Account SET balance = balance - 100 WHERE account_id = 1; -- 修改数据项
COMMIT;

-- T2 (时间戳 2)
BEGIN TRANSACTION;
SELECT balance FROM Account WHERE account_id = 1; -- 读取数据项
UPDATE Account SET balance = balance + 50 WHERE account_id = 1; -- 修改数据项
COMMIT;

在这个例子中，T1 先开始并完成操作。当 T2 试图读取数据项时，发现数据项的时间戳小于 T2 的时间戳，因此 T2 可以继续执行。最终，T2 成功完成操作。

3. 乐观并发控制 (Optimistic Concurrency Control, OCC)

乐观并发控制假设事务不会发生冲突，只有在提交时才检查冲突。如果检测到冲突，事务将回滚并重新执行。

工作原理

  • 读操作：事务在读取数据项时，记录数据项的初始状态。
  • 写操作：事务在修改数据项时，记录数据项的新状态。
  • 提交：事务在提交时，检查数据项的当前状态是否与初始状态一致。如果不一致，事务回滚并重新执行。

示例

假设有两个事务 T1 和 T2，分别尝试修改同一个账户 A 的余额：

  • T1：从账户 A 中取出 100 元。
  • T2：向账户 A 中存入 50 元。
1
2
3
4
5
6
7
8
9
10
11
-- T1
BEGIN TRANSACTION;
SELECT balance FROM Account WHERE account_id = 1; -- 记录初始状态
UPDATE Account SET balance = balance - 100 WHERE account_id = 1; -- 修改数据项
COMMIT; -- 检查冲突并提交

-- T2
BEGIN TRANSACTION;
SELECT balance FROM Account WHERE account_id = 1; -- 记录初始状态
UPDATE Account SET balance = balance + 50 WHERE account_id = 1; -- 修改数据项
COMMIT; -- 检查冲突并提交

在这个例子中，T1 先开始并完成操作。当 T2 试图提交时，发现数据项的当前状态与初始状态不一致，因此 T2 回滚并重新执行。

4. 多版本并发控制 (Multi-Version Concurrency Control, MVCC)

多版本并发控制通过为每个数据项维护多个版本，允许多个事务同时访问不同版本的数据项，从而提高并发性能。

工作原理

  • 版本生成：每次事务修改数据项时，生成一个新的版本。
  • 版本选择：事务在读取数据项时，选择符合其时间戳的版本。
  • 垃圾回收：定期回收不再需要的旧版本。

示例

假设有一个银行账户表 Account，包含 account_id、balance 和 version 三个字段。有两个事务 T1 和 T2 同时操作同一个账户：

  • T1：从账户 A 中取出 100 元。
  • T2：向账户 A 中存入 50 元。
1
2
3
4
5
6
7
8
9
10
11
-- T1
BEGIN TRANSACTION;
SELECT balance FROM Account WHERE account_id = 1 AND version <= 1; -- 读取版本 1
UPDATE Account SET balance = balance - 100, version = 2 WHERE account_id = 1 AND version = 1;
COMMIT;

-- T2
BEGIN TRANSACTION;
SELECT balance FROM Account WHERE account_id = 1 AND version <= 2; -- 读取版本 2
UPDATE Account SET balance = balance + 50, version = 3 WHERE account_id = 1 AND version = 2;
COMMIT;

在这个例子中，T1 和 T2 分别生成了新的版本。T1 生成版本 2，T2 生成版本 3。每个事务在读取数据项时，选择符合其时间戳的版本，从而避免了冲突。

总结

并发控制是数据库管理系统中确保数据一致性和完整性的关键技术。常见的并发控制技术包括锁机制、时间戳排序、乐观并发控制和多版本并发控制。每种技术都有其适用场景和优缺点，选择合适的并发控制技术可以显著提高数据库系统的性能和可靠性。

数据库恢复技术

数据库恢复技术是指在数据库系统发生故障时，通过一系列技术和方法，将数据库恢复到某个已知的正确状态，以确保数据的完整性和一致性。常见的数据库恢复技术包括事务日志、检查点、备份和恢复策略等。以下是数据库恢复技术的详细介绍。

1. 事务日志 (Transaction Log)

事务日志是数据库恢复中最常用的技术之一，用于记录数据库的所有事务操作。

功能

  • 记录事务操作：记录每个事务的开始、结束以及每个操作的详细信息。
  • 支持回滚和重做：在事务失败时，可以通过日志回滚未完成的操作；在系统崩溃后，可以通过日志重做已完成的操作。

工作原理

  • 日志记录：每个事务在执行时，都会在日志中记录其操作。
  • 日志类型：
    • 物理日志：记录每个数据块的变化。
    • 逻辑日志：记录每个事务的逻辑操作（如插入、删除、更新）。

优点

  • 数据完整性：确保事务的原子性和持久性。
  • 恢复速度快：通过日志可以快速恢复数据库状态。

缺点

  • 存储开销：日志文件会占用大量存储空间。
  • 性能影响：频繁的日志记录可能会降低系统性能。

2. 检查点 (Checkpoint)

检查点是数据库恢复中的一个重要概念，用于标记数据库的一个一致状态。

功能

  • 定期记录状态：定期记录数据库的一致状态，减少恢复时需要处理的日志量。
  • 加快恢复速度：通过检查点，可以快速定位到最近的一致状态，减少恢复时间。

工作原理

  • 创建检查点：在创建检查点时，记录当前所有活动事务的状态，并将脏页（修改过的数据页）写入磁盘。
  • 恢复过程：
    • 从最近的检查点开始恢复。
    • 重做检查点之后的所有事务日志。
    • 回滚未完成的事务。

优点

  • 减少恢复时间：通过检查点，可以显著减少恢复所需的时间。
  • 提高系统可用性：减少系统停机时间，提高系统的可用性。

缺点

  • 复杂性：检查点的创建和管理较为复杂。
  • 存储开销：检查点文件也会占用一定的存储空间。

3. 备份 (Backup)

备份是数据库恢复的基础，通过定期备份数据库，可以在系统发生故障时恢复数据。

类型

  • 完全备份：备份整个数据库。
  • 增量备份：备份自上次备份以来发生变化的数据。
  • 差异备份：备份自上次完全备份以来发生变化的数据。

工作原理

  • 备份过程：定期将数据库的数据文件和日志文件复制到备份介质上。
  • 恢复过程：
    • 从最近的完全备份开始恢复。
    • 应用增量备份或差异备份。
    • 重做事务日志，确保数据的一致性。

优点

  • 数据安全性：确保数据的安全性和完整性。
  • 灵活性：可以根据需要选择不同的备份策略。

缺点

  • 存储开销：备份文件会占用大量的存储空间。
  • 恢复时间：完全恢复可能需要较长的时间。

4. 恢复策略 (Recovery Strategy)

恢复策略是指在数据库系统发生故障时，采取的一系列措施，以尽快恢复系统的正常运行。

常见策略

  • 即时恢复：在系统崩溃后，立即启动恢复过程，确保系统尽快恢复正常运行。
  • 定期恢复：在预定的时间点进行恢复，适用于非关键系统。
  • 灾难恢复：在发生重大灾难时，通过备份和备用系统恢复数据和业务。

工作原理

  • 故障检测：通过监控系统状态，及时发现故障。
  • 故障隔离：隔离故障部分，防止故障扩散。
  • 故障恢复：根据恢复策略，启动相应的恢复过程。

优点

  • 高可用性：确保系统的高可用性和可靠性。
  • 灵活性：可以根据不同的业务需求选择不同的恢复策略。

缺点

  • 复杂性：恢复策略的制定和实施较为复杂。
  • 成本：需要投入额外的资源和成本。

示例

假设有一个数据库系统，使用事务日志、检查点和备份技术进行恢复。

  1. 事务日志：

    • 每个事务在执行时，记录其操作到事务日志中。
    • 例如，事务 T1 插入一条记录，事务 T2 更新一条记录。

  2. 检查点：

    • 每小时创建一次检查点，记录当前所有活动事务的状态，并将脏页写入磁盘。
    • 例如，检查点 C1 记录了 T1 和 T2 的状态。

  3. 备份：

    • 每天进行一次完全备份，每小时进行一次增量备份。
    • 例如，每天凌晨进行一次完全备份 B1，每小时进行一次增量备份 I1, I2, I3。

  4. 恢复过程：

    • 如果系统在某次崩溃后需要恢复：
      • 从最近的完全备份 B1 开始恢复。
      • 应用增量备份 I1, I2, I3。
      • 从最近的检查点 C1 开始，重做事务日志，确保数据的一致性。

通过上述步骤，可以确保数据库在发生故障时能够快速恢复到一个已知的正确状态，保障数据的完整性和一致性。

查询优化

查询优化是数据库管理系统（DBMS）中的一个重要环节，旨在提高查询的执行效率，减少响应时间，降低资源消耗。查询优化涉及多个方面，包括索引优化、查询重写、执行计划选择等。以下是查询优化的主要方法和步骤。

1. 索引优化

索引是数据库中用于加速数据检索的重要工具。合理的索引设计可以显著提高查询性能。

创建合适的索引
  • 单列索引：为经常用于查询条件的列创建索引。
  • 复合索引：为多个列创建复合索引，特别是当这些列经常一起出现在查询条件中时。
  • 唯一索引：为需要保证唯一性的列创建唯一索引。
避免过度索引
  • 过多的索引会增加存储开销和维护成本，尤其是在插入、更新和删除操作时。
  • 定期评估索引的使用情况，删除不再需要的索引。
索引的选择性
  • 选择性高的索引（即索引列的值分布广泛）更有效。
  • 例如，CustomerID 列的选择性通常比 Gender 列高。

2. 查询重写

通过重写查询语句，可以使其更高效地执行。

使用合适的连接类型
  • 内连接（INNER JOIN）：只返回匹配的行。
  • 外连接（LEFT JOIN、RIGHT JOIN、FULL JOIN）：返回所有行，即使没有匹配的行。
  • 交叉连接（CROSS JOIN）：返回笛卡尔积，通常不推荐使用。
优化子查询
  • 尽量将子查询转换为连接查询。
  • 使用 EXISTS 或 IN 替代 NOT EXISTS 或 NOT IN，因为后者可能导致全表扫描。
避免不必要的计算
  • 尽量在 WHERE 子句中使用索引列，避免在索引列上进行计算。
  • 例如，WHERE age + 10 > 30 可以改写为 WHERE age > 20。

3. 执行计划选择

DBMS 通常会生成多个执行计划，并选择最优的执行计划。

使用 EXPLAIN 分析查询
  • 使用 EXPLAIN 关键字查看查询的执行计划，了解查询的执行过程。
  • 分析执行计划中的表扫描、索引使用、连接顺序等信息。
优化连接顺序
  • DBMS 通常会选择最优的连接顺序，但有时手动调整连接顺序可以提高性能。
  • 例如，先连接小表，再连接大表。
使用临时表
  • 对于复杂的查询，可以使用临时表存储中间结果，减少重复计算。
  • 临时表可以提高查询的可读性和维护性。

4. 数据库配置优化

合理的数据库配置可以显著提高查询性能。

调整缓存大小
  • 增加缓冲池（Buffer Pool）的大小，提高缓存命中率。
  • 例如，MySQL 中的 innodb_buffer_pool_size 参数。
优化并发控制
  • 调整事务隔离级别，平衡数据一致性和并发性能。
  • 例如，使用 READ COMMITTED 而不是 SERIALIZABLE。
分区表
  • 对于大表，可以使用分区表将数据分成多个小部分，提高查询效率。
  • 例如，按日期分区、按范围分区等。

5. 硬件和网络优化

硬件和网络环境也会影响查询性能。

增加内存
  • 增加服务器内存，提高缓存命中率，减少磁盘 I/O 操作。
使用 SSD
  • 使用固态硬盘（SSD）替代机械硬盘，提高 I/O 速度。
优化网络带宽
  • 确保数据库服务器和应用服务器之间的网络带宽足够，减少网络延迟。

示例

假设有一个查询语句：

1
SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01';
优化步骤

  1. 创建索引：

    1
    CREATE INDEX idx_customer_id_order_date ON orders (customer_id, order_date);

  2. 使用 EXPLAIN 分析：

    1
    EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01';

  3. 优化查询：

    • 确保 customer_id 和 order_date 列上有合适的索引。
    • 避免在索引列上进行计算。

  4. 调整数据库配置：

    • 增加缓冲池大小： 
      1
      innodb_buffer_pool_size = 4G

  5. 硬件优化：

    • 增加服务器内存。
    • 使用 SSD 硬盘。

通过以上步骤，可以显著提高查询的执行效率，减少响应时间，提高系统的整体性能。

数据库设计

规范设计方法

  • 基本思想: 过程迭代和 逐步求精
  • 新奥尔良方法:把数据库设计分为若干阶段和步骤，逐步实施
  • 基于 E-R模型的数据库设计方法
  • 3NF设计方法
  • ODL(面向对象)设计方法
  • 统一建模语言(UML)方法

数据库设计阶段

  1. 需求分析阶段：了解并且分析 客户需求，是最困难，最耗费时间的一步
    • 确定系统需求，确定系统功能，确定系统范围，确定系统边界，确定数据字典(数据字典的内容:数据项，数据结构，数据流,数据存储，处理过程五个部分)
    • 分析方法: 结构化分析方法(SA):从 最上层出发，采用自顶向下，逐层分解的方法分析系统。调查组织机构情况-熟悉业务活动-明确用户需求-确定系统边界
  2. 概念结构设计阶段：对用户需求进行 综合，归纳，抽象，形成一个独立于具体DBMS的 概念模型 （根据数据字典设计成E-R图)
    • 概念模型的特点 ：1.真实反应现实世界2.易于理解3.易于更改4.向关系，网状，层次等数据模型结构转化
    • E-R图: 实体型 使用 矩形 ，用 椭圆 表示 属性，用 菱形表示 联系
    • 设计概念的 方法：1.自顶向下2.自底向上3.逐步扩张4.混合策略
    • 抽象的三种方法：1.分类2.聚集3.概括
  3. 逻辑结构设计阶段：把 概念结构转换为某个DBMS所支持的数据模型，并且对它进行优化(根据ER图设计成数据库表)
  4. 物理设计阶段 ：为 逻辑数据模型选择一个适合应用环境的物理结构（存储安排，存取方法选择，存取路径建立）
  5. 数据库实施阶段：设计人员运用DBMS提供的语言和宿主语言，根据逻辑设计与物理设计建立数据库，调试并且运行（创建数据库模式，装入数据，数据库试运行)
  6. 数据库运行和维护阶段:(性能检测，转储/修复，数据库重组和重构)
    • 参与数据库设计的人员 系统分析人员和数据库设计人员 数据库管理员和用户代表 应用开发人员

E-R 图的集成

E-R 图（实体-关系图）是数据库设计中常用的一种图形工具，用于表示实体及其之间的关系。在实际项目中，通常会有多个子系统或模块，每个子系统或模块可能有自己的 E-R 图。为了构建一个完整的数据库模型，需要将这些 E-R 图进行集成。以下是 E-R 图集成的步骤和注意事项。

集成步骤

  1. 识别公共实体和属性

    • 比较各个子系统的 E-R 图，找出共同的实体和属性。
    • 确保这些公共实体和属性在不同子系统中的定义是一致的。

  2. 解决命名冲突

    • 如果不同的子系统中使用了相同的名称来表示不同的实体或属性，需要重新命名以避免冲突。
    • 例如，一个子系统中的 Customer 实体可能在另一个子系统中称为 Client，需要统一命名。

  3. 合并实体和关系

    • 将各个子系统的实体和关系合并到一个统一的 E-R 图中。
    • 确保合并后的 E-R 图逻辑清晰，没有冗余的实体或关系。

  4. 处理多对多关系

    • 如果合并后出现多对多关系，需要引入关联实体来转换为一对多关系。
    • 例如，如果 Student 和 Course 之间有多对多关系，可以引入 Enrollment 关联实体。

  5. 优化模型

    • 检查合并后的 E-R 图，确保模型的完整性和一致性。
    • 删除冗余的实体和关系，简化模型结构。

  6. 验证和调整

    • 与业务需求和技术团队沟通，验证合并后的 E-R 图是否满足所有需求。
    • 根据反馈进行必要的调整和优化。

注意事项

  1. 一致性

    • 确保所有实体和属性的定义在整个 E-R 图中保持一致。
    • 避免使用模糊或不明确的术语。

  2. 标准化

    • 使用标准的 E-R 图符号和约定，确保图的可读性和易理解性。
    • 例如，使用矩形表示实体，菱形表示关系，椭圆表示属性。

  3. 文档记录

    • 记录每个实体和关系的详细说明，包括其含义、用途和约束条件。
    • 生成详细的 E-R 图文档，方便后续开发和维护。

  4. 性能考虑

    • 在集成过程中，考虑数据库的性能和可扩展性。
    • 例如，避免创建过于复杂的关联关系，以免影响查询性能。

  5. 安全性

    • 确保敏感数据的安全性，如个人身份信息（PII）等。
    • 设计合适的数据访问控制机制，保护数据安全。

示例

假设有两个子系统的 E-R 图：

子系统1:

  • 实体 Customer
    • 属性：CustomerID, Name, Email
  • 实体 Order
    • 属性：OrderID, OrderDate
  • 关系 Customer - Order (一对多)

子系统2:

  • 实体 Client
    • 属性：ClientID, Name, Phone
  • 实体 Service
    • 属性：ServiceID, ServiceName
  • 关系 Client - Service (一对多)

集成后的 E-R 图:

  • 实体 Customer (统一命名)
    • 属性：CustomerID, Name, Email, Phone
  • 实体 Order
    • 属性：OrderID, OrderDate
  • 实体 Service
    • 属性：ServiceID, ServiceName
  • 关系 Customer - Order (一对多)
  • 关系 Customer - Service (一对多)

通过以上步骤和注意事项，可以有效地将多个子系统的 E-R 图集成到一个统一的 E-R 图中，确保数据库设计的完整性和一致性。 ​

数据库范式 (Database Normalization)

数据库范式是数据库设计中的一系列规则，用于减少数据冗余和提高数据完整性。范式分为多个级别，每个级别的范式都建立在前一级别的基础上，逐步消除数据冗余和异常。常见的范式包括第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、鲍科斯范式（BCNF）和第四范式（4NF）。

第一范式 (1NF)

定义

第一范式要求关系模式中的每个属性都是不可再分的基本数据项，即每个字段都必须是原子的，不能包含多个值。

目的
  • 消除重复组。
  • 确保每个字段都是单一值。
例子

假设有一个关系模式 ，其中 是学生的学号， 是学生的名字， 是学生选修的课程列表。

A B C
1 张三 [数学, 英语]
2 李四 [物理, 化学]

这个关系模式违反了1NF，因为 是一个复合属性。为了满足1NF，需要将其分解为：

A B C
1 张三 数学
1 张三 英语
2 李四 物理
2 李四 化学

第二范式 (2NF)

定义

第二范式要求关系模式满足1NF，并且所有的非主属性完全函数依赖于候选键。

目的
  • 消除非主属性对部分键的依赖。
  • 避免部分依赖导致的数据冗余。
例子

假设有一个关系模式 ，其中 和 组成候选键， 和 是非主属性，存在以下函数依赖：

这个关系模式违反了2NF，因为 部分依赖于候选键 。为了满足2NF，需要将其分解为：

第三范式 (3NF)

定义

第三范式要求关系模式满足2NF，并且所有的非主属性不传递依赖于候选键。

目的
  • 消除非主属性对候选键的传递依赖。
  • 避免传递依赖导致的数据冗余。
例子

假设有一个关系模式 ，其中 是候选键，存在以下函数依赖：

这个关系模式违反了3NF，因为 传递依赖于候选键 。为了满足3NF，需要将其分解为：

鲍科斯范式 (BCNF)

定义

鲍科斯范式要求关系模式满足3NF，并且所有的非平凡函数依赖的决定因素都是候选键。

目的
  • 消除非平凡函数依赖导致的数据冗余。
  • 避免更新异常。
例子

假设有一个关系模式 ，其中 和 都是候选键，存在以下函数依赖：

这个关系模式违反了BCNF，因为 和 都不是唯一的决定因素。为了满足BCNF，需要将其分解为：

第四范式 (4NF)

定义

第四范式要求关系模式满足BCNF，并且所有的非平凡多值依赖的决定因素都是超键。

目的
  • 消除非平凡多值依赖导致的数据冗余。
  • 避免插入和删除异常。
例子

假设有一个关系模式 ，存在以下多值依赖：

这个关系模式违反了4NF，因为 的值决定了多个独立的值集 和 。为了满足4NF，需要将其分解为：

总结

数据库范式是数据库设计中的一系列规则，用于减少数据冗余和提高数据完整性。通过逐步消除部分依赖、传递依赖和多值依赖，可以确保数据库的设计更加合理和高效。每个范式都建立在前一级别的基础上，逐步提高数据的规范化程度。

函数依赖 (Functional Dependency)

定义

函数依赖（Functional Dependency, FD）是一种数据库中的数据依赖关系，用于描述一个或多个属性值唯一确定另一些属性值的情况。形式上，如果在一个关系模式 中，对于属性集 和 ，存在函数依赖 ，则意味着对于每个 的值，都有一个唯一的 的值与之对应。

符号表示

  • 表示 函数依赖于 。
  • 称为决定因素（Determinant）， 称为依赖项（Dependent）。

例子

假设有一个关系模式 ，并且存在以下函数依赖：

这意味着：

  • 对于每个 的值，都有一个唯一的 的值。
  • 对于每个 的值，都有一个唯一的 的值。
  • 对于每个 的值，都有一个唯一的 的值。

函数依赖的性质

  1. 自反性：如果 ，则 。
  2. 增广性：如果 ，则 。
  3. 传递性：如果 且 ，则 。
  4. 合并规则：如果 且 ，则 。
  5. 分解规则：如果 ，则 且 。
  6. 伪传递性：如果 且 ，则 。

闭包

闭包（Closure）是指在给定函数依赖集 下，属性集 能够决定的所有属性的集合，记作 。

求解闭包的步骤
  1. 初始化：令 。
  2. 迭代：重复以下步骤，直到 不再发生变化。
    • 对于每一个函数依赖 在 中：
      • 如果 \( Y \subseteq X_F+ \)，则将 添加到 \( X_F+ \) 中。
  3. 返回结果：最终的 即为所求的闭包。

例子

假设有一个关系模式 和一组函数依赖 ：

求解 的步骤如下：

  1. 初始化：

  2. 迭代：

    • 第一次迭代：

      • 检查 ：因为 ，所以将 添加到 中。

      • 检查 ：因为 ，所以将 添加到 中。

      • 检查 ：因为 ，所以将 添加到 中。

      • 检查 ：因为 ，所以将 添加到 中。

      • 检查 ：因为 ，所以不需要添加新的属性。

    • 第二次迭代：

      • 再次检查所有函数依赖，发现 已经包含所有属性，不再发生变化。

  3. 返回结果：

函数依赖的应用

函数依赖主要用于数据库的规范化过程中，特别是在第一范式（1NF）、第二范式（2NF）、第三范式（3NF）和鲍科斯范式（BCNF）中。通过识别和消除冗余的函数依赖，可以提高数据库的规范化程度，减少数据冗余和异常。

总结

函数依赖是数据库中一种重要的数据依赖关系，用于描述一个或多个属性值唯一确定另一些属性值的情况。通过识别和消除冗余的函数依赖，可以提高数据库的规范化程度，减少数据冗余和异常。

多值依赖 (Multivalued Dependency)

定义

多值依赖（Multivalued Dependency, MVD）是一种数据库中的数据依赖关系，用于描述属性之间的独立性。形式上，如果在一个关系模式 中，对于属性集 和 ，存在多值依赖 ，则意味着对于每个 的值，都有一个 的值集，这些值集与 的值无关，即 的值集可以在不同的 值之间自由组合。

符号表示

  • 表示 多值依赖于 。
  • 成立的充分必要条件是：对于 的任意两个元组 和 ，如果 ，则存在另一个元组 ，使得 ，，并且 ，其中 。

例子

假设有一个关系模式 ，并且存在以下多值依赖：

这意味着对于每个 的值，都有一个 的值集和一个 的值集，这两个值集可以独立变化。

多值依赖的性质

  1. 平凡多值依赖：如果 或 ，其中 ，则 是平凡的。
  2. 传递性：如果 且 ，则 。
  3. 对称性：如果 ，则 ，其中 。

多值依赖的应用

多值依赖主要用于数据库的规范化过程中，特别是在第四范式（4NF）中。4NF 要求关系模式中不能存在非平凡的多值依赖。

示例

假设有一个关系模式 ，并且存在以下多值依赖：

A B C
1 b1 c1
1 b2 c2
2 b3 c3

在这个关系中，对于每个 的值，都有一个 的值集和一个 的值集，这两个值集可以独立变化。例如，对于 ， 可以取 或 ，而 可以取 或 。

规范化

为了消除多值依赖，可以将关系模式分解为更小的关系模式。例如，将 分解为：

这样，每个关系模式都只包含一个多值依赖，从而满足 4NF 的要求。

总结

多值依赖是数据库中一种重要的数据依赖关系，用于描述属性之间的独立性。通过识别和消除多值依赖，可以提高数据库的规范化程度，减少数据冗余和异常。

多值依赖与函数依赖的区别

函数依赖 (Functional Dependency, FD)

定义

函数依赖是一种数据库中的数据依赖关系，用于描述一个或多个属性值唯一确定另一些属性值的情况。形式上，如果在一个关系模式 中，对于属性集 和 ，存在函数依赖 ，则意味着对于每个 的值，都有一个唯一的 的值与之对应。

符号表示
  • 表示 函数依赖于 。
  • 称为决定因素（Determinant）， 称为依赖项（Dependent）。
例子

假设有一个关系模式 ，并且存在以下函数依赖： 这意味着对于每个 的值，都有一个唯一的 的值。

A B C
1 b1 c1
2 b2 c2
3 b3 c3

在这个关系中，每个 的值唯一确定了一个 的值。

多值依赖 (Multivalued Dependency, MVD)

定义

多值依赖是一种数据库中的数据依赖关系，用于描述属性之间的独立性。形式上，如果在一个关系模式 中，对于属性集 和 ，存在多值依赖 ，则意味着对于每个 的值，都有一个 的值集，这些值集与 的值无关，即 的值集可以在不同的 值之间自由组合。

符号表示
  • 表示 多值依赖于 。
  • 称为决定因素， 称为依赖项， 称为剩余属性集。
例子

假设有一个关系模式 ，并且存在以下多值依赖： 这意味着对于每个 的值，都有一个 的值集，这些值集与 的值无关。

A B C
1 b1 c1
1 b2 c2
2 b3 c3
2 b4 c4

在这个关系中，对于每个 的值，都有一个 的值集，这些值集可以独立变化。

区别

  1. 定义上的区别

    • 函数依赖：一个属性集 的值唯一确定另一个属性集 的值。
    • 多值依赖：一个属性集 的值确定另一个属性集 的值集，这些值集与 的值无关。

  2. 符号表示上的区别

    • 函数依赖：使用 表示。
    • 多值依赖：使用 表示。

  3. 性质上的区别

    • 函数依赖：具有自反性、增广性、传递性、合并规则、分解规则和伪传递性。
    • 多值依赖：具有平凡多值依赖、传递性和对称性。

  4. 应用上的区别

    • 函数依赖：主要用于第一范式（1NF）、第二范式（2NF）、第三范式（3NF）和鲍科斯范式（BCNF）的规范化过程中。
    • 多值依赖：主要用于第四范式（4NF）的规范化过程中，以消除非平凡的多值依赖。

示例对比

函数依赖示例

假设有一个关系模式 ，并且存在以下函数依赖：

A B C
1 b1 c1
2 b2 c2
3 b3 c3

在这个关系中，每个 的值唯一确定了一个 的值。

多值依赖示例

假设有一个关系模式 ，并且存在以下多值依赖：

A B C
1 b1 c1
1 b2 c2
2 b3 c3
2 b4 c4

在这个关系中，对于每个 的值，都有一个 的值集，这些值集可以独立变化。

总结

函数依赖和多值依赖是数据库中两种重要的数据依赖关系。函数依赖描述的是一个属性集的值唯一确定另一个属性集的值，而多值依赖描述的是一个属性集的值确定另一个属性集的值集，这些值集与决定因素的值无关。理解这两种依赖关系的区别有助于更好地进行数据库的规范化设计，减少数据冗余和异常。

逻辑蕴含

  1. 定义:对于任意一个关系模式R<U,F>,其任何一个关系r,若[[#函数依赖]] X → Y X \to YX→Y 都成立,则称 F逻辑蕴含 X → Y X \to YX→Y

ArmStrong公理系统

目的：求给定关系模式的 码，从一组函数依赖球的蕴含的函数依赖 阐述: 设U是属性集总体，F是U上的一组 [[#函数依赖]] ,有以下规则

推广规则:

求解闭包 的步骤

定义

闭包 是指在给定函数依赖集 下，属性集 能够决定的所有属性的集合。

求解步骤
  1. 初始化：令 。
  2. 迭代：重复以下步骤，直到 不再发生变化。
    • 对于每一个函数依赖 在 中：
    • 如果 ，则将添加到中。
  3. 返回结果：最终的 即为所求的闭包。
示例

假设有一个关系模式 和一组函数依赖 ：

求解 的步骤如下：

  1. 初始化：

  2. 迭代：

    • 第一次迭代：

      • 检查 ：因为 ，所以将 添加到 ( X_F^+ ) 中。
      • 检查 ：因为 ，所以将 添加到 中。
      • 检查 ：因为 ，所以将 添加到 中。
      • 检查 ：因为 ，所以将 添加到 中。
      • 检查 ：因为 ，所以不需要添加新的属性。

    • 第二次迭代：

      • 再次检查所有函数依赖，发现 已经包含所有属性，不再发生变化。

  3. 返回结果：

总结

通过上述步骤，我们可以求解给定属性集 在函数依赖集 下的闭包 \( X_F+ \)。这个过程通过不断扩展 \( X_F+ \)，直到不能再添加新的属性为止。最终的 就是 能够决定的所有属性的集合。

最小依赖集/最小覆盖/极小函数依赖集求法：

口号： ①右切②除本求包,右部在包要除③左部最小化，分别考察了左部右部传递的冗余性

最小依赖集/最小覆盖/极小函数依赖集求法

定义

最小依赖集（Minimal Cover）是指一组函数依赖，其中每个依赖都是不可再简化的，并且这组依赖能够等价地表示原始的函数依赖集。具体来说，最小依赖集满足以下条件：

  1. 每个函数依赖的右部只有一个属性。
  2. 每个函数依赖的左部没有冗余的属性。
  3. 没有冗余的函数依赖。

求解步骤

  1. 分解右部：将每个函数依赖的右部分解为单个属性。
  2. 消除冗余属性：对于每个函数依赖的左部，检查是否有冗余的属性可以删除。
  3. 消除冗余依赖：检查是否有冗余的函数依赖可以删除。

示例

假设有一个关系模式 R(A, B, C, D, E) 和一组函数依赖 ：

步骤 1: 分解右部

将每个函数依赖的右部分解为单个属性：

步骤 2: 消除冗余属性

对于每个函数依赖的左部，检查是否有冗余的属性可以删除。

  • 对于 ，检查 是否成立：

    • 计算 的闭包：，所以 成立。
    • 删除，保留。

  • 对于 ，检查 或 是否成立：

    • 计算 的闭包：，不包含 。
    • 计算 的闭包：，包含 ，所以 成立。
    • 删除 ，保留 。

更新后的函数依赖集：

步骤 3: 消除冗余依赖

检查是否有冗余的函数依赖可以删除。

  • 检查 是否冗余：

    • 从 中移除 ，得到 。
    • 计算 在 下的闭包：，不包含 ，所以 不冗余。

  • 检查 是否冗余：

    • 从中移除，得到。
    • 计算在下的闭包：，不包含，所以不冗余。

  • 检查 是否冗余：

    • 从 中移除 ，得到 。
    • 计算 在 下的闭包：，不包含 ，所以 不冗余。

  • 检查 是否冗余：

    • 从 中移除 ，得到 。
    • 计算 在 下的闭包：，不包含 ，所以 不冗余。

  • 检查是否冗余：

    • 从中移除，得到。
    • 计算在下的闭包：，不包含，所以不冗余。

最终的最小依赖集：

候选键求法

  1. 分类:把属性分为L(仅在左部),R(仅在右部),LR(左右都有),N(两边都没有)
  2. 令 ,若X的 闭包=U ，则X一定是R的唯一候选键
  3. 从 LR 中选择 一个属性Y,若，则(Y ‘ 是候选键)
  4. 从L , R L,RL,R中选择2 , 3 , . . . 个属性 2,3,…个属性2,3,…个属性，执行第三步步骤。 请注意：候选键对于 相同长度的要全部求闭包

3NF求法

求最小依赖集 合并左部相同，构成模式 模式中若 都不包含R的候选键， 要单独加入R的候选键(多个候选键只放一个) 数据库完整性 维护完整性，DBMS提供的功能

  1. 提供定义完整性约束条件的机制
  2. 提供完整性检查的方法
  3. 违约处理

实体完整性

1
2
3
4
5
# 把Student表中的Sno定义为码
Create table student (
Sno char(9) primary key,
Sname char(20) ,...
)
1
2
3
4
# 把(Sno,Cno)定义为码
Sno char(9) not null,
Cno char(4) not null,
Primary Key (Sno,Cno)

参照完整性 在定义表的时候加 Foreign key (属性列) references Student(别的表的主码)

1
2
3
4
Sno char(9) not null,
Cno char(4) not null,
Foreign key (Sno) references Student(Sno)
Foregin key (Cno) references Course(Cno)

用户定义的完整性 数据库安全性 数据保护的功能 数据的 安全性保护 数据的 完整性保护 并发控制 数据的恢复 实现数据库安全性的方法(五种) 用户标识与鉴别 存取控制

自主存取控制方法(DAC)
  • 定义 存取权限称为授权。
  • 使用grant进行授权
1
2
3
grant <权限>（update,insert,delete)
on 对象类型
to 用户

使用revoke

视图

审计

数据加密

数据库的安全性

  • 数据库的安全性是指 保护数据库以防止 不合法的使用 以造成的 数据泄露，更改或破坏。

关系数据库标准语言SQL

数据定义(DDL)
1
2
3
4
5
Create
CREATE TABLE <表名>
(
列名1，属性值，约束
);
1
2
3
Alter(修改)
Alter table <表名>
[add 列名, 数据类型]
1
2
3
4
5
Drop(删除)
修改功能
update 表名
set 列名 = 列的值
where 条件
1
2
3
4
# 把计算机学生的成绩全部置零
update sc
set Grade = 0
where Sdept = 'CS'

删除功能

1
2
# 删除学号为213的学生记录
delete from Student where Sno = 213

SQL查询的转化

全程量词转化

由于SQL中没有全称量词，所以对于 全部字样的查询，要进行否定的转化 例题：查询选修了全部课程的学生姓名。之前的逻辑是，查询学生p,对于所有的课x它都选修了。 现在是，查询学生p，没有一门课是该学生 不选修的

1
2
3
4
5
select sname from Student Where not exists (
select * from Course where not exists (
select * from sc where Cno = Course.Cno and student.Cno = cno
)
)

SQL的特点

  1. 综合统一：SQL集数据定义语言 DDL,数据操纵语言DML,数据控制语言DCL，数据查询语言DQL功能于一体
  2. 高度非过程化：用SQL进行数据操纵，只用提出 做什么，无需指出 怎么做，因此无需了解存取路径，存取路径的选择和SQL语句的执行过程由系统完成。
  3. 面向集合的操作方式：SQL采用 集合操作方式，操作对象，查找结果可以是元组的集合。
  4. 以同一种语法结构提供两种使用方式：SQL既是 自含式语言，又是 嵌入式语言
  5. 语言简洁，易学易用

视图

视图是一张 虚表，只存放 定义，不会存放数据，数据仍然存放在原本的表中 视图一经定义，就可以被删除查询，但视图的更新(增，删，改)有一定限制

建立视图

1
2
3
CREATE VIEW <视图名> 
AS <子查询>
[WITH CHECK OPTION]

其中，若有 WITH CHECK OPTION表示进行update，insert，delete操作时要保证操作的行 满足视图定义 视图不仅可以建立在一个或多个 基本表中，也可以建立在一个或多个 视图上

删除视图

1
2
DROP VIEW <视图名> CASCADE
# CASCADE指明级联删除视图

查询视图

  1. 首先进行 有效性检查，检查查询中的表，视图是否存在
  2. 从数据字典中取出视图的定义，把定义的 子查询和 用户的查询结合，转换成等价的 基本表查询。这一转换过程称为: 视图消解

更新视图

由于视图是不实际存储数据的 虚表，因此对视图的更新，最终要转换为对基本表的更新，对视图的更新操作也是通过对视图的消解，转换为对基本表的更新操作。

视图的作用

  1. 视图能简化用户的操作：通过定义视图，让数据库看起来更加清晰，简化用户的查询工作
  2. 视图使用户能以多种角度看待同一数据
  3. 视图对重构数据库提供了一定程度的逻辑独立性
  4. 视图能够对机密数据提供安全保护：通过限制每个用户看到的视图，可以限制用户仅修改子集定义的视图
  5. 适当利用视图可以进行更加清晰的表达

关系数据库

关系的属性

  • 候选码：若关系中的某一个 属性组能唯一地标识某一个元组，则称该属性组为 候选码
  • 主码：一个关系中可以从 候选码中选择一个成为主码
  • 主属性：候选码的诸属性称为 主属性，不包含在任何候选码的属性称为 非主属性
  • 全码：关系模式所有的属性是这个关系模式的码

关系系统

支持 选择，投影，连接

分类

  1. 表示系统
  2. 最小关系系统
  3. 关系上完备的系统
  4. 全关系系统

关系的完整性

实体完整性

若属性是 主属性，则不能为空

参照完整性

若属性 F 是基本关系R的 外码，与关系 S的主码对应，则R中每个元组在F上必须为:1.空值(F的每个属性都是空值) 2. 或者等于S中某个元组的主码

用户参照完整性

某一具体应用所涉及的数据必须满足语义需求

关系运算

选择 选择条件表的名字

  • 实际上是查询满足条件的行（元组）
  • 查询信息系全体学生：
  • 查询年龄小于20岁的学生:

投影 属性列表的名字

  • 实际上是查询新的关系的子集
  • 查询学生关系中都有哪些系:

连接

  • 自然连接(要求连接的两个属性组有相同的分量) 除法
  • 关系X,Y,X÷Y=(X中拥有Y列的属性列)