数据库设计

        数据库设计的任务是针对一个给定的应用环境，在给定的硬件环境和操作系统及数据库管理系统等软件环境下，创建一个性能良好的数据库模式，建立数据库及其应用系统，使之能有效地存储和管理数据，满足各类用户的需求。数据库设计属于系统设计的范畴。通常把使用数据库的系统统称为数据库应用系统，把对数据库应用系统的设计简称为数据库设计。

• 数据库设计包括结构特性设计和行为特性设计
• 数据库设计的主要特点
  • 从数据结构即数据模型开始，并以数据模型为核心展开；
  • 静态结构设计与动态行为设计分离
  • 试探性
  • 反复性和多步性
• 数据库设计方法
  • 直观设计法
  • 规范设计法
  • 计算机辅助设计法
  • 自动化设计法
  • 基于3NF 的设计方法
  • 基于实体联系（E-R）模型的数据库设计方法
  • 基于视图概念的数据库设计方法
  • 面向对象的关系数据库设计方法
  • 计算机辅助数据库设计方法
  • 敏捷数据库设计方法

数据库设计基本步骤

        数据库设计的基本步骤包括：用户需求分析、概念结构设计、逻辑结构设计、物理结构设计、数据库实施阶段、数据库运行和维护阶段

用户需求分析

• 数据库设计人员采用一定的辅助工具对应用对象的功能、性能、限制等要求进行科学的分析。
• 用户和设计人员对数据库应用系统所要涉及的内容和功能的整理和描述，是以用户的角度来认知系统。
• 任务：综合各个用户的应用需求，对现实世界要处理的对象进行详细调查，在了解现行系统的概况，确定新系统功能的过程中，收集支持系统目标的基础数据及处理方法。
• 方法：自顶向下
  • 自顶向下的结构化分析（SA）方法是从最上层的系统组织机构入手，采用逐层分解的方式分析系统，并把每一层用数据流图和数据字典描述。需求分析的重点是调查组织机构情况、调查各部门的业务活动情况、协助用户明确对新系统的各种要求、确定新系统的边界，以此获得用户对系统的如下要求。
    • 信息要求。用户需要在系统中保存那些信息，由这些保存的信息要得到什么样的信息，这些信息以及信息间应当满足的完整性要求。
    • 处理要求。用户在系统中要实现什么样的操作功能，对保存信息的处理过程和方式，各种操作处理的频度、响应时间要求、处理方式等以及处理过程中的安全性要求和完整性要求。
    • 系统要求。包括安全性要求、使用方式要求和可扩充性要求。
      • 安全性要求：系统有几种用户使用，每一种用户的使用权限如何。
      • 使用方式要求：用户的使用环境是什么，平均有多少用户同时使用，最高峰时有多少用户同时使用，有无查询相应的时间要求等
      • 可扩充性要求：对未来功能、性能和应用访问的可扩充性的要求。

概念结构设计

• 概念结构设计是对信息分析和定义，如视图模型化、视图分析和汇总。对应用对象精确地抽象、概括而形成独立于计算机系统的企业信息模型。描述概念模型的较理想的工具是E-R 图。
• 概念结构设计的目标是产生反应系统信息需求的数据库概念结构，即概念模式。概念结构是独立于支持数据库的DBMS 和使用的硬件环境的。此时，设计人员从用户的角度看待数据以及数据处理的要求和约束，产生一个反应用户观点的概念模式，然后再把概念模式转换为逻辑模式。

工作步骤

• 概念结构设计工作步骤包括：选择局部应用、逐一设计分E-R 图和 E-R 图合并。

选择局部应用

        需求分析阶段会得到大量的数据，这些数据分散杂乱，许多数据应用于不同的处理，数据与数据间的关联关系也较为复杂，要最终确定实体、属性和联系，就必须根据数据流图这一线索，理清数据。

逐一设计分 E-R 图

        划分好各个局部应用之后，就要对每个局部应用逐一设计分E-R 图，又称为局部E-R 图。对于每一局部应用，其所用到的数据都应该收集在数据字典中了，依照该局部应用的数据流图，从数据字典中提取出数据，使用抽象机制，确定局部应用中的实体、实体的属性、实体标识符以及实体间的联系和类型。

E-R 图合并

        根据局部应用设计好各局部E-R 图后，就可以对各分 E-R 图进行合并。合并的目的在于在合并过程中解决分E-R 图中相互间存在的冲突，消除在分E-R 之间存在的信息冗余，使之成为能够被全系统所有用户共同理解和接受的统一的、精炼的全局概念模型。

        合并的方法是将具有相同实体的两个或多个E-R 图合二为一，在合成后的E-R 图中把相同的实体用一个实体表示，合成后的实体的属性是所有分E-R 图中该实体的属性的并集，并以此实体为中心，并入其他所有分E-R 图。再把合成后的E-R 图以分E-R 图看待，合并剩余的分E-R 图，直至所有的E-R 图全部合并，就构成一张全局E-R 图。

• 合并冲突
  • 属性冲突
    • 属性域冲突
    • 属性值冲突
  • 命名冲突
    • 同名异义
    • 异名同义
  • 结构冲突
    • 同一对象在不同应用中的抽象不同
    • 同一实体在不同E-R 图中属性个数合排列次序不同
• 合并优化
  • 实体类型的合并。两个具有1：1联系或1：* 联系的实体，可以予以合并，使实体个数减少，有利于减少将来数据库操作过程中的连接开销。
  • 冗余属性的消除。一般在各分E-R 图中的属性是不存在冗余的，但合并后就能出线冗余。因为合并后的E-R 图中的实体继承了合并前该实体在分E-R 图中的全部属性，属性见就可能存在冗余，即某一属性可以由其他属性确定。
  • 冗余联系的消除。在分E-R 图合并过程中，可能会出现实体联系的环状结构，即某一实体A 与另一实体B 有直接联系，同时A 又通过其他实体与实体B 发生间接联系，通常直接联系可以通过间接联系所表达，可消除直接联系。

逻辑结构设计

        将抽象的概念模型转化为与选用的DBMS 产品所支持的数据模型相符合的逻辑模型，它是物理结构设计的基础。包括模式初始设计、子模式设计、应用程序设计、模式评价以及模式求精。

        逻辑结构设计的步骤包括：E-R 图转换为关系模式、关系模式规范化、确定完整性约束、确定用户视图、反规范化

E-R 图转换为关系模式

        E-R 图方法得到的全局概念模型是对信息世界的描述，并不适用于计算机处理，为适合关系数据库系统的处理，必须将E-R 图转换为关系模式。E-R 图是由实体、属性和联系三要素构成的，而关系模式中只有唯一的结构-关系模式，

• 通常采用下述方法加以转换。
  • 将E-R 图的实体逐一转换为一个关系模式，实体名对应关系模式的名称
  • E-R 图中的属性转换为关系模式的属性，实体标识符就是关系的码
  • E-R 图中的关键字转换为关系的关键字
• E-R 图的联系有3种：
  • 一对一联系的转换（1：1）：不需要将其转换为一个独立的关系模式，只需要将联系归并到两个实体的任一方，给待归并的一方实体属性集种增加另一方实体的码和该联系的属性即可，归并后的实体码保持不变。
  • 一对多联系的转换（1：*）：通常一对多联系也不需要将其转换为一个独立的关系模式，只需要将联系归并到关联的两个实体的多方，给带归并的多方实体属性集中增加一方实体的码和该联系的属性即可，归并后的多方实体的码保持不变。
  • 多对多联系的转换（*：*）：多对多联系只能转换成一个独立的关系模式，关系模式的名称取联系的名称，关系模式的属性取该联系所关联的两个多方实体的码及联系的属性，关系的码是多方实体的码构成的属性组。
• 复合属性转换
  • 将每个分量属性作为复合属性所在实体的属性
  • 将复合属性本身作为所在实体的属性
• 多值属性转换
  • 将多值属性与所在实体的主键一起组成一个新的关系
  • 将多值属性提升为一个实体，通常为弱实体
• 派生属性
  • 派生属性可由其他属性计算得到，因此，转化成关系模式时，通常不转换派生属性
• BLOB 属性
  • 典型的BLOB 是一张图片或一个声音文件，由于他们的容量比较大，必须用特殊的方式处理。
  • 将BLOB属性与关系主键独立为一个关系模式

关系模式规范化

        由E-R 转换得来的初始关系模式并不能完全符合要求，还会有数据冗余、更新异常存在，这就需要经过进一步的规范化处理。

1. 根据语义确定各关系模式的数据依赖
2. 根据数据依赖确定关系模式的范式
3. 如果关系模式不符合要求，要根据关系模式的分解算法对其进行分解，使其达到3NF 或 BCNF
4. 关系模式的评价及修正

确定完整性约束

        完整性约束包括数据项的约束、表级约束，及表间约束。可以参照SQL 标准来确定不同的约束，如检查约束、主码约束、参照完整性约束、以保证数据的正确性。

确定用户视图

        确定了整个系统的关系模式后，还要根据数据流图及用户信息建立视图模式，提高数据的安全性和独立性。

1. 根据数据流图确定处理过程使用的视图。
2. 根据用户类别确定不同用户使用的视图。

反规范化

        在关系模式的规范化过程中，会导致关系的概念越来越单一化，在响应用户查询时，往往需要涉及多表的关联操作，导致查询性能下降。为此需要对关系模式进行修正，对部分能影响性能的关系模式进行处理，包括分解、合并、增加冗余属性等。

        这种修正称之为反规范化设计，反规范化是加速读操作性能的方法，一般用这种方法有选择地在数据结构标准化后添加特定的冗余数据实例。反规范化数据库不应该与未进行过标准化的数据库相混淆。

• 常见的反规范化操作
  • 冗余列
  • 派生列
  • 表重组
  • 表分割
    • 水平分割
    • 垂直分割

        反规范化会在数据库中形成数据冗余，在提高查询性能的同时，也带来设计复杂和更新异常的问题。由于反规范化形成了数据冗余，为解决数据冗余带来的数据不一致性问题，设计人员往往需要额外采用数据同步的方法来解决这种数据不一致性。

• 数据同步方法：
  • 应用程序的同步
  • 批量处理同步
  • 触发器同步等

物理结构设计

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

        物理结构设计的步骤包括：确定数据分布、确定数据的存储结构、确定数据的访问方式

确定数据分布

1. 根据不同应用分布数据。
2. 根据处理要求确定数据的分布。
3. 对数据的分布存储必然会导致数据的逻辑结构的变化，要对关系模式做新的调整，需要回到数据库逻辑设计阶段作必要的修改。

确定数据的存储结构

        在文件中，数据是以记录为单位存储的，可以采用顺序存储、哈希存储、堆存储和B+ 树存储等方式。在实际应用中，要根据数据的处理要求和变更频度选定合理的物理结构。

        为提高数据的访问速度，通常会采用索引技术。在物理设计阶段，要根据数据处理和修改要求，确定数据文件的索引字段和索引类型。

确定数据的访问方式

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

• 存储记录结构设计
  • 存储记录结构包括记录的组成、数据项的类型、长度和数据项间的联系，以及逻辑记录到存储记录的映射。在设计记录的存储结构时，并不改变数据库的逻辑结构，但可以在物理上对记录进行分割。数据库中数据项的被访问频率是很不均匀的，基本上符合公认的“80/20规则”，即从数据库中检索的80% 的数据由其中的20% 的数据项组成。
  • 当多用户同时访问常用数据项时，往往会因为访盘冲突而等待。若将这些数据分布在不同的磁盘组上，当多用户同时访问常用数据项时，系统可并行地执行I/O，从而减少访盘冲突，提高数据库的性能。可见对于常用关系，最好将其水平分割成多个片，分布到多个磁盘组上，以均衡各个磁盘组的负荷，发挥多磁盘组并行操作的优势，提高系统性能。
• 存储记录布局
  • 聚簇功能 可以大大提高按聚簇码进行查询的效率
  • 建立聚簇索引的原则如下
    • 聚簇码的值相对稳定，没有或很少需要进行修改。
    • 表主要用于查询，并且通过聚簇码进行访问或连接是该表的主要应用
    • 对应每个聚簇码值的平均元组数既不太多，也不太少。
• 存取方法的设计
  • 存储结构：限定了可能访问的路径和存储记录
  • 检索机制：定义了每个应用的访问路径
    • 建立存取路径最普遍的方法是建立索引
      • 确定关系的存储结构，即记录的存放是无序的，还是按某属性聚簇存放
      • 确定不宜建立索引的属性或表
      • 确定宜建立索引的属性

数据库实施

        数据库设计人员根据逻辑设计和物理设计阶段的结果建立数据库、编制与调式应用程序，组织数据入库，并进行试运行。

        数据库实施的步骤包括：建立实际的数据库结构、数据加载、数据库试运行和评价

建立实际的数据库结构

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

• 数据库模式与子模式，以及数据库空间等的描述。
• 数据库完整性描述；指的是数据的有效性、正确性和一致性。
• 数据库安全性描述。
  • 系统可以对用户的数据操纵进行两方面的控制：
    • 给合法用户授权：身份验证和口令识别
    • 给合法用户不同的存取权限
• 数据库物理存储参数描述：一般可设置的参数包括块大小、页面大小、数据库的页面数、缓冲区个数、缓冲区大小和用户数等

数据加载

        数据加载前要建立严格的数据登录、录入和校验规范，设计完善的数据校验与矫正程序，排除不合格数据

• 数据加载可以分为手工录入和使用数据转换工具两种

数据库试运行和评价

        一般数据库的试运行和评价结合起来的目的是测试应用程序的功能、测试数据库的运行效率是否达到设计目标，是否为用户所容忍。

子模式

        子模式（Subschema）是一个数据库用户或应用程序看到的数据库的逻辑视图。它定义了用户可以看到和操作的数据的一部分，包括表、视图和索引等。子模式也被称为用户视图（User View）或外模式（External Schema）。

        在SQL数据库中，子模式通常通过视图（View）来实现。视图是一个虚拟表，它基于一个或多个底层表的定义，并且可以包含数据的子集或经过计算的数据。用户可以查询视图，就像查询普通表一样，但实际上他们只是在访问底层数据的一个逻辑表示。

数据库运行和维护阶段

        数据库应用系统经过试运行即可投入运行，但该阶段需要不断地对系统进行评价、调整与修改。

数据库性能的监测和改善

        性能可以用处理一个事物的I/O量、CPU 时间和系统响应时间来度量。由于数据库应用环境、物理存储的变化，特别是用户数和数据量的不断增加，数据库系统的运行性能会发生变化。某些数据库结构（如数据页和索引）经过一段时间的使用以后，可能会破坏。所以，DBA 必须利用系统提供的性能检测和分析工具，经常对数据库的运行、存储空间及响应时间进行分析，结合用户的反映确定改进措施。

数据库的备份及故障恢复

        数据库是企业的一种资源，所以在数据库设计阶段，DBA 应根据应用要求制定不同的备份方案，保证一旦发生故障能很快地将数据库恢复到某种一致性状态，尽量减少损失。

数据库备份方式

按照备份内容分为：物理备份和逻辑备份

• 物理备份：物理备份是在操作系统层面上对数据库的数据文件进行备份
  • 物理备份分为冷备份和热备份两种
    • 冷备份。关闭数据库进行备份
    • 热备份。不关闭数据库进行备份，可使用备份软件
  • 为了提高物理备份的效率，通常将完全，增量，差异三种备份方式组合
    • 完全备份 ：是将数据库的内容全部备份
    • 增量备份：只备份上次完全，增量或差异备份以来修改的数据
    • 差异备份：备份自上次完全备份后发生变化的所有数据
• 逻辑备份。利用DBMS 自带的工具软件备份和恢复数据库的内容

故障恢复

事务故障的恢复

        事务故障是指事务未运行至正常终点前被撤销，这时恢复子系统应对此事务做撤销处理。

事务恢复是系统自动完成的，不许用户干预。步骤如下：

1. 反向扫描文件日志，查找该事务的更新操作
2. 对该事务的更新操作执行逆操作
3. 继续反向扫描日志文件，查找该事务的其他更新操作，并作同样处理
4. 继续如此，直至读到此事务的开始标记，事务故障恢复完成

系统故障的恢复

• 系统故障，造成数据库不一致的原因有两个：
  • 一是由于一些未完成事务对数据库的更新已写入数据库；
  • 二是由于一些已提交事务对数据库的更新还保留在缓冲区，还没有写入数据库。
• 系统故障的恢复是在重新启动时完成的，用户不需要干预； 步骤如下

1. 正向扫描日志文件，找出故障发生前已经提交的事务，将其事务标记入重做队列。同时找出故障发生时尚未完成的事务，将其事务标志记入撤销队列。
2. 对撤销中的各个事务进行撤销处理：反向扫描日志文件，对每个Undo事务的更新操作执行逆操作
3. 对重做队列中的各个事务进行重做处理：正向扫描日志文件，对每个Redo事务重新执行日志文件登记的操作

介质故障与计算机病毒恢复

        在发生介质故障和遭病毒破坏时，磁盘上的物理数据库被破坏，这时的操作分为三步：

1. 装入最新的数据库备份副本，是数据库恢复到最近一次转储时的一致性状态
2. 从故障点开始反向读日志文件，找出已提交的事务将其计入重做队列
3. 从起始点开始正向阅读日志文件，根据重做队列中的记录，重做所有已完成事务，将数据库恢复至故障前某一时刻的一致状态

具有检查点的恢复技术

        检查点记录的内容包括：

1. 建立检查点时刻所有正在执行的事务清单。
2. 这些事务最近一个日志记录的地址。采用检查点的恢复步骤如下
3. 从重新开始文件中找到最后一个检查点记录在日志文件中的地址，由该地址在日志文件中找到最后一个检查点记录。
4. 由该检查点记录得到检查点建立时所有正在执行的事务清单队列 (A)。
5. 建立重做队列 (R) 和撤销队列 (U) ，把A 队列放入U 队列中，R 队列为空

数据库重组和重构

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

事务管理

• 数据库运行的基本单位是事务，事务相当于操作系统的进程，是用户定义的一个数据库操作序列，这些操作要么全做要么全不做，是一个不可分割的工作单位。
• 事务通常以 BIGEN TRANSACTION （事务开始）语句开始，以 COMMIT 或ROLLBACK 结束。COMMIT 称为 事务提交语句，表示事务执行成功的结束。ROLLBACK 称为事务回退语句，表示事务执行不成功的结束。

事务的四大特性

• 原子性（Atomicity）： 操作序列要么全做，要么全不做
• 一致性（Consistency）： 数据库从一个一致性状态变到另一个一致性状态
• 隔离性（Isolation）： 不能被其他事务干扰
• 持续性（Durability） 一旦提交就是永久性的

事务引发的问题

1. 丢失更新
   丢失更新是指在完全未隔离事务的情况下，两个事务更新同一条数据资源，某一事务完成，另一事务异常终止，回滚造成第一个完成的更新也同时丢失。
2. 不可重复读
   不可重复读是指在一个事务内，多次读取同一数据，由于其他事务的修改，导致第一次读取的结果与第二次、第三次等后续读取的结果不一致
3. 读“脏”数据
   读脏数据是指在一个事务内，另一个事务修改了数据并且未提交，第一个事务读取了这些未提交的数据，导致第一个事务读取到了脏数据。

锁

• 排他型封锁（X封锁，写锁）
  如果事务T对A 实现了X封锁，那么只允许事务T读取和修改数据A，其他事务要等事务T解除X封锁后，才能对A实现任何类型的封锁
• 共享型封锁（S封锁，读锁）如果事务T 对数据A 实现了S封锁，那么允许事务T 读取数据A，但不能修改数据A，在所有S封锁解除之前绝不允许任何事务对数据A 实现X 封锁