《系统架构设计师教程（第2版）》第6章-数据库设计基础知识-03-关系数据库设计

0. 设计的基本步骤

0.1 用户需求分析

• 概述：
  • 采用一定的辅助工具
  • 对应用对象的功能、性能、限制等要求进行科学的分析。

0.2 概念结构设计

• 概述：是对信息分析和定义

如视图模型化、视图分析和汇总对应用对象精确地抽象、概括而形成独立于计算机系统的企业信息模型

• 概念模型的描述工具: E-R图。

0.3 逻辑结构设计

• 概述：
  • 将抽象的概念模型转化为适合的逻辑模型

    即，与选用的 DBMS产品所支持的数据模型相符合

  • 是物理结构设计的基础
• 包括：
  • 模式初始设计
  • 子模式设计
  • 应用程序设计
  • 模式评价
  • 模式求精

0.4 物理结构设计

概念：是逻辑模型在计算机中的具体实现方案

0.5 数据库实施阶段

• 行为：
  • 建立数据库，
  • 编制与调试应用程序
  • 组织数据入库
  • 并进行试运行。

0.6 数据库运行和维护阶段

• 行为：
  • 数据库应用系统经过试运行即可投入运行
  • 不断地对系统进行评价、调整与修改

1. 数据需求分析

1.1 概述

• 概述：
  • 用户和设计人员对数据库应用系统所要涉及的内容(数据)和功能(行为)进行整理和描述
  • 是以用户的角度来认识系统
• 作用：后续开发的基础
• 参与人员：分析人员、用户

分析人员不了解业务，用户没有系统分析能力

• 该阶段的任务
  • 综合用户的应用需求，对现实世界要处理的对象进行详细调查
  • 了解现行系统的概况，确定新系统功能
• 分析和表达方法：
  • 自顶向下的机构化分析：
    • 从最上层的系统组织机构入手
    • 采用逐层分解的方式分析系统
    • 把每一层用数据流图和数据字典描述
  • 自底向上：
• 需求分析的重点：调查组织机构情况、调查各部门的业务活动情况、协助用户明确对新系统的各种要求、确定新系统的边界，以此获得用户对系统的如下要求。

1.2 需要获取的需求

• 信息要求

  • 要保存哪些信息
  • 根据这些信息得到什么信息
  • 信息的完整性需求

• 处理要求

  • 实现什么操作功能
  • 对保存信息的处理过程和方式
  • 各种操作处理的频度、响应时间要求、处理方式
  • 处理过程中的安全性要求和完整性要求

• 系统要求

  • 安全性要求
    • 系统有几种用户
    • 每一种用户的使用权限
  • 使用方式要求
    • 用户的使用环境
    • 用户并发数、最大并发数
    • 有无查询相应的时间要求
  • 可扩充性要求
    • 对未来功能、性能和应用访问的可扩充性的要求。

2. 概念结构设计

2.1 概述

• 目标：产生概念模式
• 概念：
  • 设计人员从用户的角度看待数据以及数据处理的要求和约束，产生一个反映用户观点的概念模式

2.2 E-R方法

• 概述：

  • 采用 E-R模型将现实世界的信息结构统一由实体、属性，以及实体之间的联系来描述。

• 对现实事物抽象认识的3种方法：

  • 分类 (Classification):对现实世界的事物，按照其具有的共同特征和行为，定义一种类型
  • 聚集 (Aggregation): 定义某一类型所具有的属性
  • 概括 (Generalization): 由一种已知类型定义新的类型
    • 超类 (Superclass)：上文中的已知类
    • 子类 (Subclass)：上文中新定义的类型
    • 关系：子类是超类的子集

    如：研究生是学生的一个子集

• E-R图三要素：实体、属性、联系

2.3 概念结构设计工作步骤

2.3.1 选择局部应用

• 操作：使用数据流图清理数据
• 目的：划分好各个局部应用
• 数据流图
  • 是对业务处理过程从高层到底层的一级级抽象
• 选择适当层次的数据流图，让这一层的每一部分对应一个局部应用，实现某一项功能
  • 高层抽象流图：一般反映系统的概貌，对数据的引用较为笼统
  • 底层数据流图：可能过于细致

2.3.2 逐一设计分 E-R图

• 目的：设计局部E-R图
• 操作：对每个局部应用逐一设计分E-R图（局部E-R图）
  • 每一个局部应用，所有数据都收集在数据字典中
  • 依照该局部应用的数据流图，从数据字典中提取出数据
  • 使用抽象机制，确定其实体、实体的属性、实体标识符、实体间的联系、实体类型

事实上，在形成数据字典的过程中，数据结构、数据流和数据存储都是根据现实事物来确定的，因此都已经基本上对应了实体及其属性，以此为基础，加以适当调整，增加联系及其类型，就可以设计分E-R图。

2.3.3 E-R图合并

1）概述

• 目的：得到统一、精炼的全局概念模型

  • 解决分E-R图中相互间存在的冲突
  • 消除冗余信息

• 合并方法：

  • 将具有相同实体的两个或多个E-R图合并

  合并后的E-R图是之前合并前分ER图的并集

  • 以此实体为中心，并入其他所有分E-R图
  • 直至所有的 E-R 图全部合并，得到一张全局E-R 图

2）分E-R 图进行合并的冲突

• 属性冲突
  • 同一属性存在于不同的分E-R图中，但属性的类型、取值范围、数据单位等可能会不一致。
• 命名冲突
  • 相同意义的属性，在不同的分 E-R图上有着不同的命名
  • 名称相同的属性在不同的分 E-R 图中代表着不同的意义
• 结构冲突
  • 同一实体在不同的分E-R 图中有不同的属性
  • 同一对象在某一分E-R图中被抽象为实体而在另一分E-R 图中又被抽象为属性

3）E-R图的优化

分E-R图的合并过程中要对其进行优化：

• 实体类型的合并

两个具有1:1联系或1:*联系的实体，可以予以合并，使实体个数减少，有利于减少将来数据库操作过程中的连接开销。

• 冗余属性的消除

一般在各分E-R图中的属性是不存在冗余的，但合并后就可能出现冗余。因为合并后的 E-R图中的实体继承了合并前该实体在分E-R图中的全部属性，属性间就可能存在冗余，即某一属性可以由其他属性确定。

• 冗余联系的消除

• 冗余现象：合并过程中出现实体联系的环状结构
  • 实体A 与实体B直接联系
  • 实体A 通过实体C与实体B 间接联系
• 消除：消除直接联系。

4. 逻辑结构设计

• 概念：在概念结构设计的基础上进行数据模型设计
  • 可以是层次模型、网状模型、关系模型
• 逻辑结构设计阶段的主要工作步骤如下：

4.1 E-R图转换为关系模式

4.1.1

• 转换原因：E-R图方法所得到的全局概念模型是对信息世界的描述，并不适用于计算机处理

4.1.2 转换方法

1）E-R的实体转换

将 E-R 图中的实体逐一转换成为一个关系模式：

• 实体名对应关系模式的名称
• 实体的属性转换为关系模式的属性
• 实体标识符就是关系的码

2）E-R图的关系转换

• 一对一联系的转换：
  • 将联系归并到关联的两个实体的任一方
    • 给待归并的一方实体属性集中增加另一方实体的码和该联系的属性
  • 归并后的实体码保持不变
• 一对多联系的转换
  • 将联系归并“多”的一方
    • “多”的一方实体属性中，增加另一方的码和属性
  • 归并后的多方实体的码保持不变
• 多对多联系的转换
  • 只能转换成一个独立的关系模式
    • 关系模式的名称：取联系的名称
    • 关系模式的属性：取两个实体的码及联系的属性
    • 关系的码：是多方实体的码构成的属性组

4.2 关系模式规范化

具体步骤如下：

1. 确定数据依赖
   • 根据语义确定各关系模式的数据依赖

在设计的前一阶段，只是从关系及其属性来描
述关系模式，并没有考虑到关系模式中的数据依赖。关系模式包含着语义，要根据关系模式所
描述的自然语义写出关系数据依赖。

2. 确定当前范式
   • 根据数据依赖确定关系模式的范式

由关系的码及数据依赖，根据规范化理论，就可以确定关系模式所属的范式，判定关系模式是否符合要求，即是否达到了3N F或 BCNF。

3. 分解关系以达到范式3NF或BCNF

如果关系模式不符合要求，要根据关系模式的分解算法对其进行分解，使其达到3NF或BCNF。

4. 关系模式的评价及修正
   • 部分关系模式的处理，分解、合并或增加冗余属性，提高存储效率和处理效率。

4.3 确定完整性约束

• 目的：保证数据的正确性

对关系模式加以约束：包括数据项的约束、表级约束及表间约束
参照SQL 标准来确定不同的约束：如检查约束、主码约束、参照完整性约束

4.4 确定用户视图

• 概述：根据数据流图及用户信息建立视图模式
• 目的：提高数据的安全性和独立性
• 过程：
  • 根据数据流图确定处理过程使用的视图
    • 原因：数据流图是某项业务的处理
      • 只用到部分数据
      • 数据可能跨关系模式
    • 作用：降低应用程序的复杂性、提高数据的独立性
  • 根据用户类别确定不同用户使用的视图
    • 原因：不同用户应只能处理部分用户（而确定关系模式时没有考虑此因素）

      如：不同的院系只能访问和处理自己的学生信息

    • 作用：提高安全性

4.5 反规范化(Denormalization)

• 原因：规范化过程导致关系的单一化，增加了多表的关联操作，导致查询性能下降
• 目的：提高操作性能
• 操作：
  • 冗余列：避免关联查询
  • 派生列：常用计算结果直接存储，避免每次计算
  • 表重组
  • 表分割：
    • 水平分割：如，按日期分割历史数据
    • 垂直分割：如，分割经常访问列以提高查询效率
• 反规范化带来冗余问题的解决方法：
  • 应用程序同步
  • 批处理
  • 触发器同步

5. 物理设计

其主要工作步骤：

5.1 确定数据分布

5.1.1 概述

• 行为：确定是集中管理还是分布式管理
• 分布管理考虑的因素：

5.1.2 要考虑的因素

确定数据分布需要考虑的因素：

• 根据不同应用分布数据
  • 不同业务的数据存储在不同场地
  • 应用多个场地的业务通过网络处理数据
• 根据处理要求确定数据的分布
  • 对于使用频度高、响应时间短的数据，应存储在高速设备上。
• 逻辑设计阶段的必要修改
  • 原因：分布存储必然会导致数据的逻辑结构的变化
  • 操作：对关系模式作新的调整，需要回到数据库逻辑设计阶段

5.2 确定数据的存储结构

• 数据存储结构：数据文件中记录之间的物理结构
• 存储方式：
  • 顺序存储
  • 哈希存储
  • 堆存储
  • B⁺ 树存储等
• 选择存储结构的的依据：
  • 数据的处理要求
  • 变更频度
• 索引技术
  • 目的：提高数据访问速度
  • 本步骤要确定索引字段和索引类型

5.3 确定数据的访问方式

• 概述：由存储结构决定
  数据的访问方式是由其存储结构所决定的，采用什么样的存储结构，就使用什么样的访问
  方式。数据库物理结构主要由存储记录格式、记录在物理设备上的安排及访问路径(存取方法)
  等构成

5.3.1 存储记录结构设计

• 存储记录结构包括
  • 记录的组成
  • 数据项的类型、长度
  • 数据项间的联系
  • 辑记录到存储记录的映射
• 设计记录的存储结构时，并不改变数据库的逻辑结构，但可以在物理上对记录进行分割
• 性能优化：将常用关系分片，分布存储在多个磁盘上，以提高性能

5.3.2 存储记录布局

• 存储记录的布局：即，确定数据的存放位置。

  存储记录作为一个整体，如何分布在物理区域上

• 聚簇：

  • 采用聚簇功能可以大大提高按聚簇码进行查询的效率。
  • 可用于单个关系、多个关系

• 建立聚簇索引的原则：

  • 聚簇码的值相对稳定

  没有或很少需要进行修改。

  • 表主要用于查询，并且通过聚簇码进行访问或连接是该表的主要应用
  • 对应每个聚簇码值的平均元组数应事宜

5.3.3 存取方法的设计

1）概述

• 存取方法包括：
  • 存储结构：限定了可能访问的路径和存储记录
  • 检索机制：定义了每个应用的访问路径
• 在数据库中建立存取路径最普遍的方法是建立索引
• 索引的使用
  • 原理：改善存取路径
  • 优点：减少检索的CPU服务时间和 I/O服务时间，改善检索效率
  • 缺点：不能对进行频繁存储操作的关系建立过多的索引

2）确定索引的一般顺序

1. 确定关系的存储结构
   • 即记录的存放是无序的，还是按某属性聚簇存放
2. 确定不宜建立索引的属性或表
   • 太小的表
   • 经常更新的属性或表
   • 属性值很少的表
   • 过长的属性
   • 特殊数据类型的属性，如：大文本、多媒体数据
   • 很少作为查询条件的属性
3. 确定宜建立索引的属性
   • 关系的主码或外部码
   • 以查询为主的表、只读表
   • 范围查询
   • 聚集函数 (Min、Max、Avg、Sum、Count)
   • 需要排序输出的属性

6. 数据库实施

• 说句库的实施：根据逻辑和物理设计的结果，在计算机上建立起实际的数据库结构，数据加载，进行试运行、评价的过程

6.1 建立实际的数据库结构

用DBMS提供的数据定义语言 (DDL) 编写描述逻辑设计和物理设计结果的程序(一般称为数据库脚本程序),经计算机编译处理和执行后，就生成了实际的数据库结构。
所用 DBMS的产品不同，描述数据库结构的方式也不同。有的 DBMS 提供数据定义语言，有的提供数据库结构的图形化定义方式，有的两种方法都提供。

在定义数据库结构时，应包含以下内容：

6.1.1 数据库模式、子模式、空间等的描述

以Oracle为例：

• 数据库逻辑结果的描述包括：表空间 (Tablespace)、 段 (Segment)、 范围 (Extent)、数据块 (Datablock)
• DBA 或设计人员通过对数据库空间的管理和分配，可以控制数据库中数据的磁盘分配，将确定的空间份额分配给数据库用户，能够控制数据的可用性，将数据存储在多个设备上，以此提高数据库性能等。

6.1.2 数据库完整性描述

• 数据的完整性：指数据的有效性、正确性、一致性。
• 数据的完整性设计，应该贯穿在数据库设计的全过程中。例如：
  • 在数据需求分析阶段，收集数据信息时，应该向有关用户调查该数据的有效值范围
  • 在模式与子模式中，可以用DBMS提供的DDL 语句描述数据的完整性。

6.1.3 数据库安全性描述

• 数据安全性设计，应该贯穿在数据库设计的全过程中
  • 进行需求分析时，收集关于数据的安全性说明
  • 在设计数据库逻辑结构时，对于保密级别高的数据，可以单独进行设计。
• 子模式
  • 是实现安全性要求的一个重要手段
  • 可以为不同的应用设计不同的子模式
• 用户数据控制
  • 给合法用户授权，目前主要有
    身份验证和口令识别；二是给合法用户不同的存取权限。

6.1.4 数据库物理存储参数描述

物理存储参数因DBMS 的不同而不同。一般可设置的参数包括块大小、页面大小(字节数或块数)、数据库的页面数、缓冲区个数、缓冲区大小和用户数等

6.2 数据加载

6.2.1 数据加载程序的设计

• 在应用程序设计阶段完成
• 需建立严格的数据登录、录入和校验规范

设计完善的数据校验与校正程序，排除不合格数据

6.2.2 数据加载方法

• 手工录入
• 使用数据转换工具
  • 现有的DBMS 都提供了DBMS之间数据转换的工具

6.2.3 数据库的加载

在数据库的试运行和评价工作中分批进行

6.3 数据库试运行和评价

• 数据库的试运行：加载了部分必须的数据和应用程序后，开始对数据库系统进行的联合调试
• 运行和评价的目的： 测试应用程序的功能，以便发现问题（而不是说明达到哪些功能）
  • 测试中一定要有非设计人员的参与

7. 数据库运行维护

7.1 对数据库性能的监测和改善

• 性能度量： I/O 量、CPU时间、系统响应时间
• 数据库系统的运行性能会不断变化

  某些数据库结构(如数据页和索引)经过一段时间的使用以后，可能会被破坏

DBMS都提供系统监控或分析工具：在SQLServer中使用SQL Server Profiler组件、 Transaction-SQL工具和Query Analyzer组件等

7.2 数据库的备份及故障恢复

没有什么要记忆的

7.3 数据库重组和重构

数据库运行一段时间后，由于记录的增、删、改，数据库物理存储碎片记录链过多，影响
数据库的存取效率。这时，需要对数据库进行重组和部分重构。

• 数据库重组：指在不改变数据库逻辑和物理结构的情况下，去除数据库存储文件中的废弃空间以及碎片空间中的指针链，使数据库记录在物理上紧连。
• 数据库重构：对数据库的结构做修改
  • 表结构修改：数据列的增删和修改、约束的修改、表的分解与合并
  • 视图修改
    • 优点：可以实现数据的逻辑独立性，实现数据的安全性
    • 缺点：可能会影响数据的安全性

---