Chapter07 - 数据库设计

ch37 数据库设计概述

需求分析阶段：综合各个用户的应用需求
概念设计阶段： 形成独立于机器特点，独立于各个数据库管理系统产品的概念模式（E-R图）
逻辑设计阶段：

1. 首先将E-R图转换成具体的数据库产品支持的数据模型，如关系模型，形成数据库逻辑模式
2. 然后根据用户处理的要求、安全性的考虑，在基本表的基础上再建立必要的视图（View），形成数据的外模式

物理设计阶段：根据数据库管理系统特点和处理的需要，进行物理存储安排，建立索引，形成数据库内模式

ch38 需求分析

需求分析

需求分析就是分析用户的要求
是设计数据库的起点结果
是否准确地反映了用户的实际要求，将直接影响到后面各个阶段的设计，并影响到设计结果是否合理和实用

数据字典

• 数据字典是关于数据库中数据的描述，即元数据，不是数据本身
• 数据字典在需求分析阶段建立，在数据库设计过程中不断修改、充实、完善
• 数据字典是进行详细的数据收集和数据分析所获得的主要结果

ch39 概念模型和ER模型

概念模型

在需求分析阶段所得到的应用需求应该首先抽象为信息世界的结构，然后才能更好、更准确地用某一数据库管理系统实现这些需求。
概念模型的主要特点是:

• 能真实、充分地反映现实世界，包括事物和事物之间的联系，能满足用户对数据的处理要求，是现实世界的一个真实模型。
• 易于理解，可以用它和不熟悉计算机的用户交换意见。用户的积极参与是数据库设计成功的关键。
• 易于更改，当应用环境和应用要求改变时容易对概念模型修改和扩充。
• 易于向关系、网状、层次等各种数据模型转换。

概念模型是各种数据模型的共同基础，它比数据模型更独立于机器、更抽象，从而更加稳定。描述概念模型的有力工具是E-R模型。

E-R图

ISA联系

分类属性

1. 研究生和本科生放在同一张表中–数据的排他性，会导致空间的浪费
2. 研究生和本科生放在两张表中–进行共有的操作时，需要同时对两张表进行操作
3. 共有信息放在学生表中，特有信息放在研究生、本科生的两张表中–进行共有操作时，可以直接对学生表进行操作。进行特有操作时，只需要操作研究生或者本科生的一张表即可。但是如果需要一个完整的研究生记录，需要通过关键字进行连接（连接操作消耗时间大）

不相交约束与可重叠约束

不相交：需要使用完整性约束条件，来实现排他性
学生：研究生、本科生
是否重叠：

• 应用上进行检测
• 数据上自身的要求：可以使用触发器进行检测

可重叠的：
鸟：大鸟、小鸟、益鸟、害鸟（有可能对于南方来说是益鸟，对于北方来说是害鸟）、候鸟、留鸟
麻雀：可以同时存放在多张子类表中，如果放在一张表上，利用率较高

关键是时间复杂度和空间复杂度的平衡

完备性约束

如果是部分特化，则不能使用Union来操作研究生和本科生，因为研究生+本科生不等于学生

基数约束

30..40：

• 最少30个学生，最多40个学生，隶属于一个班级
• 即：一个班级最少30个学生，最多40个学生

Part-of 联系

可以用随机数、foreign key（指代所依赖的实体的关键字）来区分不同的弱实体

ch40 概念结构设计

概念结构设计的方法

自顶向下

用ER图描述数据字典
通过内模式、外模式、应用的概念来解耦，通过子概念来实现开发
缺点：不具有实际意义，难以估测系统的复杂程度

自底向上

逐步扩张

混合策略

将自顶向下和自底向上相结合，用自顶向下策略设计一个全局概念结构的框架，以它为骨架集成由自底向上策略中设计的各局部概念结构。
常用策略

• 自顶向下地进行需求分析
• 自底向上地设计概念结构

概念结构设计的步骤

自底向上设计概念结构的步骤

• 第1步：抽象数据并设计局部视图
• 第2步：集成局部视图，得到全局概念结构

实体与属性

实体与属性的划分原则

• 为了简化E-R图的处置，现实世界的事物能作为属性对待的，尽量作为属性对待
• 两条准则：
  • 作为属性，不能再具有需要描述的性质。属性必须是不可分的数据项，不能包含其他属性。
  • 属性不能与其他实体具有联系，即E-R图中所表示的联系是实体之间的联系。

E-R图的集成

合并E-R图，生成初步E-R图
各个局部应用所面向的问题不同，各个子系统的E-R图之间必定会存在许多不一致的地方，称之为冲突。
子系统E-R图之间的冲突主要有三类：

• 属性冲突
• 命名冲突
• 结构冲突

属性冲突

属性冲突

• 属性域冲突，即属性值的类型、取值范围或取值集合不同
  • 例如零件号，有的部门把它定义为整数，有的部门把它定义为字符型。
  • 年龄，某些部门以出生日期形式表示职工的年龄，而另一些部门用整数表示职工的年龄。
• 属性取值单位冲突
  • 例如，零件的重量有的以公斤为单位，有的以斤为单位，有的以克为单位。

命名冲突

命名冲突

• 同名异义，即不同意义的对象在不同的局部应用中具有相同的名字
• 异名同义（一义多名），即同一意义的对象在不同的局部应用中具有不同的名字。
  • 如对科研项目，财务科称为项目，科研处称为课题，生产管理处称为工程
• 命名冲突
  • 可能发生在实体、联系一级上
  • 也可能发生在属性一级上
  • 通过讨论、协商等行政手段加以解决

结构冲突

结构冲突

• 同一对象在不同应用中具有不同的抽象
  • 例如，职工在某一局部应用中被当作实体，而在另一局部应用中则被当作属性。
  • 解决方法：把属性变换为实体或把实体变换为属性，使同一对象具有相同的抽象。
• 同一实体在不同子系统的E-R图中所包含的属性个数和属性排列次序不完全相同
  • 解决方法：使该实体的属性取各子系统的E-R图中属性的并集，再适当调整属性的次序。
• 实体间的联系在不同的E-R图中为不同的类型
  • 实体E1与E2在一个E-R图中是多对多联系，在另一个E-R图中是一对多联系
  • 解决方法：根据应用的语义对实体联系的类型进行综合或调整。

E-R图的修改和重构

消除不必要的冗余，设计基本E-R图
所谓冗余的数据是指可由基本数据导出的数据，冗余的联系是指可由其他联系导出的联系。
消除冗余主要采用分析方法，即以数据字典和数据流图为依据，根据数据字典中关于数据项之间逻辑关系的说明来消除冗余。

ch42 逻辑结构设计

把概念结构设计阶段设计好的基本E-R图转换为与选用数据库管理系统产品所支持的数据模型相符合的逻辑结构
转换内容

• E-R图由实体型、实体的属性和实体型之间的联系三个要素组成
• 关系模型的逻辑结构是一组关系模式的集合
• 将E-R图转换为关系模型：将实体型、实体的属性和实体型之间的联系转化为关系模式

一个实体型转换为一个关系模式。
关系的属性：实体的属性
关系的码：实体的码

关系数据模型的优化

确定数据依赖

• 按需求分析阶段所得到的语义，分别写出每个关系模式内部各属性之间的数据依赖以及不同关系模式属性之间数据依赖。

对于各个关系模式之间的数据依赖进行极小化处理，消除冗余的联系。
按照数据依赖的理论对关系模式进行分析，考察是否存在部分函数依赖、传递函数依赖、多值依赖等，确定各关系模式分别属于第几范式。
按照需求分析阶段得到的各种应用对数据处理的要求，分析对于这样的应用环境这些模式是否合适，确定是否要对它们进行合并或分解。
并不是规范化程度越高的关系就越优

• 当查询经常涉及两个或多个关系模式的属性时，系统必须经常地进行连接运算
• 连接运算的代价是相当高的
• 因此在这种情况下，第二范式甚至第一范式也许是适合的。
• 非BCNF的关系模式虽然会存在不同程度的更新异常，但如果在实际应用中对此关系模式只是查询，并不执行更新操作，就不会产生实际影响。
• 对于一个具体应用来说，到底规范化进行到什么程度，需要权衡响应时间和潜在问题两者的利弊才能决定

关系模式的分解

对关系模式进行必要分解，提高数据操作效率和存储空间的利用率。
常用分解方法

• 水平分解
• 垂直分解

水平分解

把(基本)关系的元组分为若干子集合，定义每个子集合为一个子关系，以提高系统的效率。
如何分解

• 对符合“80/20原则”的，把经常被使用的数据（约20%）水平分解出来，形成一个子关系。
• 水平分解为若干子关系，使每个事务存取的数据对应一个子关系。

垂直分解

把关系模式R的属性分解为若干子集合，形成若干子关系模式。
垂直分解的原则：经常在一起使用的属性从R中分解出来形成一个子关系模式
垂直分解的优点

• 可以提高某些事务的效率

垂直分解的缺点

• 可能使另一些事务不得不执行连接操作，降低了效率

垂直分解的适用范围

• 取决于分解后R上的所有事务的总效率是否得到了提高

进行垂直分解的方法

• 简单情况：直观分解
• 复杂情况：用模式分解算法垂直分解必须不损失关系模式的语义（保持无损连接性和保持函数依赖）

设计用户子模式

定义数据库模式主要是从系统的时间效率、空间效率、易维护等角度出发。
定义用户外模式时应该更注重考虑用户的习惯与方便。包括三个方面：

• 使用更符合用户习惯的别名

合并各分E-R图曾做了消除命名冲突的工作，以使数据库系统中同一关系和属性具有唯一的名字。这在设计数据库整体结构时是非常必要的。
用视图机制可以在设计用户视图时可以重新定义某些属性名，使其与用户习惯一致，以方便使用。

• 针对不同级别的用户定义不同的视图，以保证系统的安全性

假设有关系模式产品（产品号，产品名，规格，单价，生产车间，生产负责人，产品成本，产品合格率，质量等级），可以在产品关系上建立两个视图：
为一般顾客建立视图：产品1（产品号，产品名，规格，单价）
为产品销售部门建立视图：产品2（产品号，产品名，规格，单价，车间，生产负责人）

• 简化用户对系统的使用

如果某些局部应用中经常要使用某些很复杂的查询，为了方便用户，可以将这些复杂查询定义为视图

ch43 物理结构设计

数据库的物理设计

数据库在物理设备上的存储结构与存取方法称为数据库的物理结构，它依赖于选定的数据库管理系统。
为一个给定的逻辑数据模型选取一个最适合应用要求的物理结构的过程，就是数据库的物理设计。
数据库物理设计的步骤

1. 确定数据库的物理结构，在关系数据库中主要指存取方法和存储结构;
2. 对物理结构进行评价，评价的重点是时间和空间效率
3. 若评价结果满足原设计要求，则可进入到物理实施阶段。
4. 否则，就需要重新设计或修改物理结构，有时甚至要返回逻辑设计阶段修改数据模型。

关系模式存取方法选择

数据库系统是多用户共享的系统，对同一个关系要建立多条存取路径才能满足多用户的多种应用要求。
物理结构设计的任务之一是根据关系数据库管理系统支持的存取方法确定选择哪些存取方法。
数据库管理系统常用存取方法

• B+树索引存取方法
• Hash索引存取方法
• 聚簇存取方法

  数据库索引是存储引擎用于快速找到记录的一种数据结构。 《高性能MySQL》

索引可以根据一定的规则和算法，把数据的关键字或者范围映射到一个特定的数据结构中，比如B+树或者哈希表。这样，当我们需要查找数据时，就不需要遍历整个数据库表，而是可以根据索引的规则和算法，快速地定位到数据所在的位置。

B+树索引存取方法

选择索引存取方法的主要内容：根据应用要求确定

• 对哪些属性列建立索引
• 对哪些属性列建立组合索引
• 对哪些索引要设计为唯一索引

选择索引存取方法的一般规则

• 如果一个（或一组）属性经常在查询条件中出现，则考虑在这个（或这组）属性上建立索引（或组合索引）
• 如果一个属性经常作为最大值和最小值等聚集函数的参数，则考虑在这个属性上建立索引
• 如果一个（或一组）属性经常在连接操作的连接条件中出现，则考虑在这个（或这组）属性上建立索引

关系上定义的索引数过多会带来较多的额外开销（维护、查找索引的开销)

为什么官方建议使用自增长主键作为索引。
结合B+Tree的特点，自增主键是连续的，在插入过程中尽量减少页分裂，即使要进行页分裂，也只会分裂很少一部分。并且能减少数据的移动，每次插入都是插入到最后。总之就是减少分裂和移动的频率。
插入连续的数据：

插入非连续的数据

HASH存取方法的选择

选择Hash存取方法的规则
如果一个关系的属性主要出现在等值连接条件中或主要出现在等值比较选择条件中，而且满足下列两个条件之一

• 该关系的大小可预知，而且不变；
• 该关系的大小动态改变，但所选用的数据库管理系统提供了动态Hash存取方法。

基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），并且Hash索引将所有的哈希码存储在索引中，同时在索引表中保存指向每个数据行的指针。

哈希索引优点

1. 快速查询：参与索引的字段只要进行Hash运算之后就可以快速定位到该记录，时间复杂度约为1

哈希索引缺点

1. 哈希索引只包含哈希值和行指针，所以不能用索引中的值来避免读取行
2. 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序和范围查询
3. 哈希索引也不支持部分索引列查询，因为哈希索引始终是使用索引列的全部数据进行哈希计算的。
4. 哈希索引只支持等值比较查询，如=，IN()，<=>操作
5. 如果哈希冲突较多，一些索引的维护操作的代价也会更高

聚簇

mysql 聚簇索引
为了提高某个属性（或属性组）的查询速度，把这个或这些属性（称为聚簇码）上具有相同值的元组集中存放在连续的物理块中称为聚簇。
该属性（或属性组）称为聚簇码（cluster key）
聚簇的用途：大大提高按聚簇属性进行查询的效率
[例] 假设学生关系按所在系建有索引，现在要查询信息系的所有学生名单。

• 计算机系的500名学生分布在500个不同的物理块上时，至少要执行500次I/O操作。
• 如果将同一系的学生元组集中存放，则每读一个物理块可得到多个满足查询条件的元组，从而显著地减少了访问磁盘的次数。

聚簇存取方法的选择

聚簇
聚簇索引的叶子节点就是数据节点，也就是说索引和数据行在一起；反之，如果叶子节点没有存储数据行，那么就是非聚簇索引。
既适用于单个关系独立聚簇
也适用于经常进行连接操作的多个关系

• 把多个连接的元组按连接属性值聚集存放
• 从而实现多个关系的“预连接”，提高连接操作的效率。

选择聚簇存储方法，即确定需要建立多少个聚簇，每个聚簇中包含哪些关系

• 一个数据库可以建立多个聚簇，一个关系只能加入一个聚簇。

  我们知道聚簇索引中包含了数据行，那么如果有多个聚簇索引，就说明存储了多份相同的数据行，岂不浪费空间？

设计候选聚簇

• 常在一起进行连接操作的关系可以建立组合聚簇
• 如果一个关系的一组属性经常出现在相等比较条件中，则该单个关系可建立聚簇；
• 如果一个关系的一个（或一组）属性上的值重复率很高，则此单个关系可建立聚簇。

检查候选聚簇中的关系，取消其中不必要的关系

• 从聚簇中删除经常进行全表扫描的关系
• 从聚簇中删除更新操作远多于连接操作的关系
• 从聚簇中删除重复出现的关系
• 当一个关系同时加入多个聚簇时，必须从这多个聚簇方案（包括不建立聚簇）中选择一个较优的，即在这个聚簇上运行各种事务的总代价最小。

聚簇的局限性

• 聚簇只能提高某些特定应用的性能
• 建立与维护聚簇的开销相当大

  • 对已有关系建立聚簇，将导致关系中元组的物理存储位置移动，并使此关系上原有的所有索引无效，必须重建。

    如果我们在Cars表上创建一个聚簇索引在Id列上，那么Cars表的数据行将根据Id列的值重新排序和存储。这样就改变了数据行的物理存储位置。同时，由于数据行的位置发生了变化，Cars表上原来的非聚簇索引也将失效，因为它指向的数据行的位置不对了。因此，需要重新创建这个非聚簇索引，以便它能正确地指向数据行的新位置。

  • 当一个元组的聚簇码改变时，该元组的存储位置也要做相应改变。

假设我们有一个Cars表，它有一个聚簇索引在Id列上。Cars表的数据行是以B-tree的方式存储的，它们按照Id列的值排序。现在，如果我们修改一个数据行的Id列的值，例如把第一行的Id从1改为9，那么这个数据行将根据新的Id值重新排序和存储。这样就改变了数据行的物理存储位置。这是因为聚簇索引要求表中的数据行按照聚簇索引键值的顺序存储，如果键值发生了变化，就必须调整数据行的位置，以保持顺序。

• 当通过聚簇码进行访问或连接是该关系的主要应用，与聚簇码无关的其他访问很少或者是次要的时，可以使用聚簇
  • 尤其当SQL语句中包含有与聚簇码有关的ORDER BY, GROUP BY, UNION, DISTINCT等子句或短语时，使用聚簇特别有利，可以省去或减化对结果集的排序操作

小结

1. B+树索引是一种将数据的键值按照一定的顺序存储在一个多层的树形结构中的存储方式，它的特点是支持范围查询和排序，也适合频繁更新的数据，但是查询速度不如哈希索引快。B+树索引可以是基于主键或非主键的，也可以是单列或多列的组合。

2. Hash索引是一种将数据的键值通过哈希函数映射到一个哈希表中的存储方式，它的特点是查询速度非常快，但是不支持范围查询和排序，也不适合频繁更新的数据。Hash索引通常是基于某个单列或多列的组合的，因为哈希函数需要输入一个确定的值。

   Hash索引找到了索引并不一定就找到了数据，因为Hash索引的哈希表中存储的是数据的键值和指向数据的指针，而不是数据本身。
   如果查询的列就是Hash索引的列，那么可以直接返回键值，不需要再访问数据。
   但是如果查询的列不是Hash索引的列，那么还需要根据指针去访问数据，这就需要回表查询。
   例如，一个用户表，如果以用户名作为Hash索引，那么当查询用户名时，可以直接返回键值，不需要回表查询。但是如果查询用户的其他信息，比如年龄、性别等，那么还需要根据指针去访问数据，这就需要回表查询。

3. 聚簇索引是一种将数据和索引放在一起的存储方式，它的特点是数据的物理存放顺序和索引的顺序是一致的，找到索引就找到了数据。聚簇索引通常是基于主键的，因为主键是唯一且不变的，适合作为聚簇索引

4. 聚簇索引是可以使用B+树索引的，但不能使用Hash索引的。因为Hash索引的特点是将数据的键值通过哈希函数映射到一个哈希表中，而哈希表中的数据是无序的，不能保证数据的物理存放顺序和索引顺序一致。