3.2 数据库逻辑设计
考点1 任 务
把数据库概念设计的结果（ER模型），转换为具体的数据库管理系统支持的数据模型。这个阶段将按照具体数据库管理系统支持的数据模型来组织和存储数据，包括定义和描述数据库的全局逻辑结构、数据之间的关系、数据的完整性及安全性要求等。
考点2 目 标
得到实际的数据库管理系统可处理的数据库模式，各模式结构合理，数据之间不存在不合理的数据依赖关系，且满足各类应用的处理和使用要求，为数据库的物理设计及数据库高效、正确地运行打下基础。
3.3 数据库物理设计
考点1 物理设计的目的
（1）将数据的逻辑描述转换为实现技术规范，设计数据存储方案，以便提供足够好的性能并确保数据库数据的完整性、安全性和可恢复性。
（2）选择最优数据存储方案以加快数据检索速度，了解不同文件组织方式、索引技术及其使用方法。
（3）确定数据库的物理结构。
考点2 数据库的物理结构
（1）概述
数据库中的应用数据是以文件形式存储在外设存储介质（如磁盘）上的，文件在逻辑上被组织成记录的序列，即每个DB文件可以看作是逻辑记录的集合。一个文件在磁盘上占有一定的物理存储空间，文件中的每个逻辑记录被映射存储到某个特定的磁盘块上。一个文件在物理上可以看作是由存放文件记录的一系列磁盘块组成的，称之为物理文件。
（2）逻辑结构与物理结构的映射方式
文件的逻辑记录与磁盘块间的映射关系是由操作系统或DBMS来管理的。当需要对一个文件逻辑记录查询、插入、删除、修改时，首先要根据这种映射关系找到该逻辑记录所在的磁盘块，然后再进行相应的操作。
（3）构建物理结构时需注意的问题
①文件的组织
如何将数据库映射到操作系统文件中，也即如何将关系数据库中的关系表映射为数据库文件，以及将关系表中的元组映射到文件的逻辑记录，如何管理这些数据库文件；数据库文件中逻辑记录采用何种格式。
②文件的结构
也称为文件中记录的组织。如何在外设磁盘上安排、存放数据库文件的逻辑记录，也即如何将DB文件的逻辑记录映射到物理文件中的磁盘块。
③文件的存取
对具有某种结构的DB文件，如何去查找、插入、删除和修改其中的逻辑记录。文件的存取与文件的结构密切相关，每种文件结构都有与之对应的文件存取方法。
④索引技术
当数据库中关系表的数目及关系表中的元组数目非常多时，可以利用索引技术提高DB文件的存取速度。
【真题演练】
下列不属于数据库物理设计内容的是（ ）。
A．确定数据库文件在磁盘上的物理存储位置
B．确定数据库文件的结构和具体实现细节
C．确定索引字段和索引类型
D．在进行数据库物理设计时要考虑空间利用率、数据访问效率
【答案】B
考点3 索 引
（1）概述
索引技术（Indexing）是一种快速数据访问技术，它将一个文件的每个记录在某个或某些域（或称为属性）上的取值与该记录的物理地址直接联系起来，提供了一种根据记录域的取值快速访问文件记录的机制。索引技术的关键是建立记录域取值到记录的物理地址间的映射关系，这种映射关系称为索引（Index）。
（2）索引的优缺点
①优点：提高数据库性能，加快访问速度。
②缺点：占用存储空间，维护需要时间。
（3）索引技术分类
根据索引的实现方式不同，索引技术被分成以下两类：
①有序索引技术（索引文件机制）
有序索引技术利用索引文件实现记录域取值到记录物理地址间的映射关系。
a．记录域
这里的记录域就是查找码（排序域），查找码可以是数据文件的主码或主属性，也可以是非主属性。
b．索引文件与索引项
索引文件由索引记录组成，每个记录中记载着一个索引项，索引项记录了某个特定的查找码值和具有该值的数据文件记录的物理地址。
c．使用
当需要访问数据文件中某个数据记录时，先根据记录域取值查阅索引文件，找到对应的索引项；从索引项中找出数据记录在数据文件中的物理地址，然后根据这个地址存取数据记录。
②散列技术（哈希索引机制）
散列技术利用一个散列函数实现记录域取值到记录物理地址间的直接映射关系。
a．记录域
这里的记录域就是查找码（散列函数的散列域或排序域）。
b．文件查找码
在一个文件中查找某个或某些特定文件记录时，需要给出记录应满足的查询条件，这些用于在数据文件中查找记录的属性或属性集合就是文件查找码。对于存储有关系表数据的数据库文件，该文件的查找码可以是关系表的主码或候选码，也可以是关系表的其他非主属性。
c．使用
当需要访问数据文件中查找码值为si的某个或某些目标记录时，将si作为散列函数h的输入，计算得出的散列函数输出值h（si）就是目标记录在数据文件中的物理地址。
（4）有序索引
①概述
数据库中的数据文件经常采用顺序文件结构（后续讨论的数据文件均假设采用此结构），文件的数据记录按照某个特定的查找码值的升序或降序顺序地存储在文件中。索引建立在查找码上。如果对一个数据文件需要从几个方面去查询文件记录，可以定义多个查找码，针对每个查找码建立相应的索引文件。一个数据文件可以有多个查找码和多个索引文件。
②主体
有序索引的两个主体：数据文件和索引文件。数据文件又称被索引文件或主文件。
③需要考虑的关键问题
a．如何组织索引文件中的索引记录；
b．如何从索引文件出发，访问数据文件中的数据记录。
④索引文件建立方法
a．选定数据文件中的某个或某些记录域作为查找码，然后建立起数据记录在查找码上的取值与该记录的物理地址间的映射关系，组成索引项。
b．所有索引项作为索引记录存储在索引文件中。
c．索引文件根据某个特定的查找码值的升序或降序存储索引记录，并且也组织为顺序文件。
对于存储有关系表数据的数据库文件，该文件的查找码可以是关系表的主码或候选码，也可以是关系表的其他非主属性。
⑤有序索引的分类及特点
a．聚集索引和非聚集索引
第一，定义
对数据文件和它的一个特定的索引文件，如果数据文件中数据记录的排列顺序与索引文件中索引项的排列顺序相一致，或者说索引文件按照其查找码指定的顺序与数据文件中数据记录的排列顺序相一致，则称该索引文件为聚集索引；否则，称该索引文件为非聚集索引。
第二，建立
在一个数据文件上除了可以建立一个聚集索引外，还可以建立多个非聚集索引。
例如：
索引文件中索引项按照“商品编号”排序，数据文件中的数据记录则按照“商品类别”排序，索引文件的排列顺序与数据文件的排列顺序不一致，因此该索引文件属于非聚集索引。
第三，索引顺序文件
如果一个数据文件按照某个查找码组织为顺序文件，同时在该数据文件上又建有聚集索引，则该数据文件称为索引顺序文件。对索引顺序文件而言，既可以对数据记录进行顺序访问，又可以根据数据文件上的聚集索引对数据记录进行直接访问。
b．稠密索引和稀疏索引
如果数据文件中的每个查找码值在索引文件中都对应一个索引记录，则该索引称为稠密索引，如果只是一部分查找码的值有对应的索引记录，则该索引称为稀疏索引。
第一，稠密索引
在带稠密索引的数据文件中，查找速度较快。
第二，稀疏索引
索引文件只对数据文件中查找码“类别”的3个取值“服装，酒类，鞋类”建立了索引项，因此该索引属于稀疏索引。
c．主索引和辅索引
在数据文件的主码属性集上建立的索引称为主索引。在数据文件的非主属性上建立的索引称为辅索引。
第一，主索引
第二，辅索引
两个索引文件均是建立在非主属性“类别”上，“类别”并不是数据文件“Goods”的主码，因此这两个索引都是辅索引。
在实际的关系数据库系统中，数据库管理系统通常按照主码值的顺序将元组有序存储在数据文件中，并且以主码作为查找码建立索引文件。该索引文件既是主索引，也是聚集索引。
d．唯一索引
唯一索引可以确保索引列不包含重复的值。在多列唯一索引的情况下，可以确保索引列中每个值的组合都是唯一的。例如，如果在last_name、first_name和middle_initial列的组合上创建了唯一索引full_name，则该表中任何两个人都不可以具有相同的全名。
e．单层索引和多层索引
第一，单层索引（线性索引）
特点是索引项根据键值在索引文件中顺序排列，组织成一维线性结构，每个索引项直接指向数据文件中的数据记录。
第二，多层索引
当数据文件很大时，可以对索引文件中的索引项本身再建立一级稀疏索引，组成二层索引结构，用于快速查找索引项，
如果有二层索引，则首先在外层索引中找到一个外层索引项，然后沿着外层索引项中的指针到达内层索引块；在内层索引块可用顺序查找或二分查找找到相应的内层索引项，这个索引项中的指针指向了需要访问的数据记录。
进一步地，可以建立多层树型索引结构来快速定位大数据量文件中的数据记录。多层索引的典型例子是数据库系统中广泛应用的B树和B+树索引。
需要注意的是，在现代关系型数据库系统中，很多数据库管理系统采用了基于成本的SQL优化器，这些优化器会根据数据的分布情况、数据块的多少等统计数据来判定查询中是否使用索引，所以，可能会出现建立索引后并未真正被使用的情况，一般需要数据库管理员使用数据库管理系统自带的工具查看执行计划，以确认索引是否被使用。
【真题演练】
下列关于索引的说法中，错误的是（ ）。
A．索引是典型的空间换取时间的方法，因此在设计索引时要在空间与时间两者之间进行权衡
B．建立聚集索引时，数据库管理系统会按照聚集索引键值对数据进行排序
C．聚集索引的键值不能重复
D．当经常进行范围查询时，在范围列上建立索引可以提高该类查询的效率
【答案】C
考点4 数据库物理设计
（1）物理设计的内容
数据库物理设计包括以下环节:
①数据库逻辑模式描述
将与平台无关的描述数据库逻辑结构的关系模式及其视图转换为所选定的具体DBMS平台可支持的基本表和视图，并利用DBMS提供的完整性机制（如触发器）设计定义在基本表上的面向应用的业务规则。
②文件组织与存取设计
选择或配置基本表的文件组织形式，如堆文件、顺序文件、索引文件、散列文件等；根据实际情况，为基本关系表设计合适的存取方法和存取路径，如设计索引字段和索引，以提高数据查询速度、
③数据分布设计
a．根据数据类型、作用和使用频率的不同，将数据库中应用数据、索引、日志、备份数据等不同类型数据合理安排在磁盘、磁带等不同存储介质中；
b．根据实际需要，对分布式数据库系统中的应用数据设汁合理的数据副本，通过水平划分或垂直划分分割成不同的数据片断，然后分布存储在各局部数据库中，以提高系统的数据访问效率和数据可靠性；
c．考虑关系模式中派生属性的存储方式，合理调整关系模式的规范化程度，以便在数据存储代价和数据访问效率之间作合理的权衡折中。
④确定系统配置
根据应用环境和物理设计结果，合理设置和调整DBMS和操作系统的存储分配参数，提供系统软硬件平台的初始配置信息。
⑤物理模式评估
对数据库物理设计结果从时间、空间、维护代价等方面进行评估，从多种可行方案中选择合理的数据库物理结构。
（2）数据库逻辑模式描述
数据库物理设计需要根据数据库逻辑结构信息设计目标DBMS平台可支持的关系表（这里称为基本表）的模式信息，这些模式信息代表了所要开发的具体目标数据库的结构。这个过程称为数据库逻辑模式描述，主要包括以下设计内容。
①面向目标数据库描述基本表和视图。采用目标DBMS所支持的建表语法，描述基本表及满足应用要求的完整性约束。
②设计基本表业务规则。使用CREAT TABLE语句可以定义主码、外码、值域、空值等较为简单的、面向模型的完整性约束。
在数据库逻辑设计阶段，已经面向各关系模式设计了面向应用的完整性约束，也就是关系表业务规则。进一步地，在数据库物理设计阶段，可以依据这些关系表业务规则，利用目标DBMS提供的完整性控制机制设计基本表应遵守的面向应用的完整性约束，也就是基本表业务规则。
（3）DB文件组织步骤
一个好的数据库物理模式应当满足存储空间占用少、数据访问效率高和维护代价低的要求。
为了进行有效的数据库文件组织和存取路径设计，必须分析和理解数据库事务的数据访问特性。
事务分析可以按照如下步骤进行：
①使用事务—基本表交叉引用矩阵，分析系统内（部分重要的）数据库事务对各个基本表的访问情况，确定事务访问了哪些基本表，对这些基本表执行了何种操作，并进一步分析各操作涉及的基本表属性。
事务-基本表交叉引用矩阵的例子如表3-1所示。其中事务1对基本表1和基本表3执行了插入和更新操作，对基本表2执行了读操作。
表3-1 事务—基本表交叉引用矩阵
②估计各事务的执行频率，即单位时间内事务的执行次数。在此基础上分析事务中的每个数据访问操作对各个基本表中的相关属性的操作频率。
例如事务1每天约运行100次，对基本表1的更新操作的频率为每天约40次，更新操作作用于基本表1中的属性A。
③对每张基本表，汇总所有作用于该表上的各事务的操作频率信息，得到如下数据访问估计信息：该表是否被频繁访问，该表中哪些属性列的访问频率较高和作用于这些属性上的操作类型和查询条件类型（等值查洵、范围查询）。
根据事务数据访问特性分析结果，可以对基本表设计成更为有效的文件组织和索引方式。每种DBMS平台都提供一种或若干种数据库文件结构（如堆文件、顺序文件、聚集文件、索引文件和散列文件）。数据库物理结构设计者应当了解目标DBMS提供的各种数据库物理结构机制，根据应用系统实际情况，为数据库和基本表选择合适的文件结构。
（4）为基本表选择合适的文件结构的原则
①如果数据库中的一个基本表中的数据量很少，并且插入、删除、更新等操作非常频繁，该基本表可以采用堆文件组织方式。因为堆文件无需建立索引，维护代价非常低。虽然堆文件的数据访问效率较低，但在数据量很少时，定位文件记录的时间非常短。
当需要向新创建的基本表批量加载数据时，可将表的文件结构先选为堆文件，向表中加载数据后再重新调整文件结构，如改为数据查询效率更高的B+树文件。
②顺序文件支持基于查找码的顺序访问，也支持快速的二分查找。如果用户的查询条件定义在查找码上，则顺序文件是比较合适的文件结构。
③如果用户查询是基于散列域值的等值匹配，特别是如果访问顺序是随机的，则散列文件比较合适。但散列文件组织不适合下述情况：
a．基于散列域值的非精确查询（如模糊查询、范围查询）。例如，在Goods散列文件中查找商品“类别”以“家”字开头的商品。
b．基于非散列域进行的查询。例如，在Goods散列文件中按商品“名称”进行查询。
④B-树和B+树文件是实际数据库系统中使用非常广泛的索引文件结构，适合于定义在大数据量基本表上、基于查找码的等值查询、范围查询、模糊查询和部分查询。B-树和B+树属于动态索引，可以随着数据文件的内容变化不断调整，保证数据查询的性能不会恶化。
⑤如某些频繁执行且需要进行多表连接操作的查询，可以考虑将这些基本表组织为聚集文件，以改善查询效率。
（5）DB文件存取设计的主要内容
对数据库中的基本表等数据库对象选择了合适的文件结构后还需要为其设计存取路径，设计内容包括：为数据库文件设计合理的物理存储位置和为基本表设计索引。在SQL Server中，可以利用CREATE DATABASE和CREATE TABLE创建数据库文件和基本表，并可将数据库文件分布到不同的磁盘上。
①索引设计的原则
a．对于经常需要进行查询、连接、统计操作，且数据量大的基本表可考虑建立索引；而对于经常执行插入、删除、更新操作或小数据量的基本表应尽量避免建立索引。
b．一个基本表上除了可以建立一个聚集索引外，还可以建立多个非聚集索引。多个索引为用户提供了根据多个查找码快速访问文件的手段。但是索引越多，对表内数据更新时为维护索引所需的开销就越大。因此，对于一个更新频繁的表应少建或不建索引。
c．索引可以由用户根据需要随时创建或删除，以提高数据查询性能。例如，对表进行大批量数据插入和更新时，可以先删除索引，以保证插入、更新操作的系统响应时间，因为在数据插入或更新过程中维护索引需要花费很大代价。数据插入或更新完成后再重建索引，以加快查询和统计等操作的系统响应时间。
②基本表可建立索引的属性
a．表的主码（大部分关系数据库管理系统会自动为主码建立唯一索引）。
b．在WHERE查询子句中引用率较高的属性。
c．参与连接操作的属性。
d．在Order By子句、Group By子句中出现的属性。
e．在某一范围内频繁搜索的属性，但只有当使用索引的查询其结果不超过记录总数的20％时索引才会有明显的效果。
f．如果在WHERE子句中同时包含一个表中的多个属性，则可以考虑在这些属性上建立多属性索引。此外，如果数据库文件需要频繁执行精确匹配查询（如等值查询），则可考虑建立散列索引。而B+树等有序索引更适合于范围查询。
g．当一个属性有相对较多的不同值时，使用索引有明显的作用；当一个属性的不同值很少时，使用索引没有好处。
h．对包含大量空值的属性建立索引时要仔细考虑，因为很多数据库管理系统中的索引不引用具有空值的行，对空值的查找需要使用全表扫描来实现。
（6）数据分布设计
①不同类型数据的物理分布
a．数据库系统中的各个类型的数据应根据实际使用情况存放在合适的物理介质上，以优化系统I/O性能。
b．数据库备份数据、日志文件备份数据用于故障恢复，使用频率低，而且数据量很大，可以存储在磁带中。而应用数据、索引和日志则使用频繁，要求的响应时间短，必须存放在支持直接存取的磁盘存储介质上。
c．当系统采用RAID等多磁盘存储系统时，可以将基本表和建立在表上的索引分别放在不同的磁盘上。这样在访问基本表时，存放数据和存放索引的磁盘驱动器并行工作，可以得到较快的文件读写速度；类似地，日志文件与数据库对象（表、索引等）也可分别存放在不同磁盘上以改善系统I/O性能。
②应用数据的划分与分布
实际数据库中有可能存在一些非常大的基本表。这些表的属性很多，表中的元组也非常多。为改善对这类大数据量基本表的访问性能，可将基本表划分为若干分区，各分区数据分别存储在不同位置的磁盘上，并可采用不同的物理组织方式。对基本表的划分可以依据以下不同原则：
a．根据数据的使用特征划分
例如，如果一个基本表中有部分数据被频繁查询，并且要求查询响应速度快，则可以将基本表分为频繁使用分区和非频繁使用分区，分别存储在不同磁盘上。对频繁使用分区中的数据可考虑建立B+树等多层索引，以提高数据访问速度；而对非频繁使用分区中的数据可以不建或只建立单层索引。从提高数据访问速度角度，可以将一个大的基本表划分为多个分区，每个分区作为一个数据文件分别存储在不同的磁盘上。对基本表的访问可以通过对分布在各磁盘上的数据文件的并行访问来实现。
b．根据时间、地点划分
例如，对商品可以按照商品的生产年份进行分区，生产年份相同的商品属于同一分区；或者按照商品生产厂商所在省份分区。
c．分布式数据库系统（DDBS）中的数据划分
DDBS采用水平划分或垂直划分两种方法，将基本表分为若干分区，并可根据需要为分区设计合适的副本。然后将各分区及其副本分别存储在不同的系统站点上，不同的分区可以有公共数据。数据划分和分布的原则是数据应靠近其使用者，以降低查询过程中的数据传输代价，提高数据访问响应速度，并且通过副本冗余机制提高数据的可靠性。
第一，水平划分
水平划分将一张基本表划分为多张具有相同属性、结构完全相同的子表，子表包含的元组是基本表中元组的子集。各子表可以有公共元组，也可以分别包含完全不同的元组。
如对商品按照商品的生产年份进行划分就属于水平划分。
第二，垂直划分
垂直划分则是将一张基本表划分为多张子表。每张子表包含的属性是原基本表的子集。例如，基本表Table_Goods可以垂直划分为两张子表：
Table1_Goods（GoodsID,GoodsClassID,GoodsName,ProductionDate,SaleUnitPrice）
Table2_Goods（GoodsID,TotalStorage,Description）
相对于是将多个属性指标进行了拆分，总表的结构属性没有发生改变
③派生属性数据分布
a．概述
基本表中的派生属性是指该属性的取值可根据表中其他属性的取值唯一确定。
例如，如图3-18（a）所示，对“顾客”基本表增加属性“年龄”（Age），得到Table_Customer（CustomerID,CardID,Name,Sex,Age,Birthdate,IdentityCard,Address,Postcode,
Tel），则Age即为派生属性，因为它的取值可以根据属性Birthdate的值推导出来。
b．实现方式
对带有派生属性的基本表可采用如下两种实现方式：
第一种方式将派生属性作为基本表中单独的一列，称为派生列。这种方式允许用户用select语句直接查询顾客的Age属性。
第二种方式是派生属性不出现在基本表中，如图3-18（b）所示。这种方式节省了基本表的存储空间，并且当年份发生变化时无需更新派生属性Age的值。但当用户查询某个顾客的Age属性时必须用如下的查询语句：
当派生属性与它所依赖的属性间的计算关系较为复杂，或当用户经常需要对派生属性进行查询时，为提高查询效率，可以采用第一种方法，将派生属性的数据直接存储在关系表中，并且利用触发器等机制随时更新派生属性的值。
④关系模式的去规范化
在数据库逻辑设计阶段，由ER图转换得到的关系模式必须经过规范化处理，以避免关系表使用过程中出现插入异常、删除异常、更新异常和数据冗余等问题。经过规范化处理后，关系应达到一定的规范化程度，如满足第三范式。关系模式规范化过程实质上是将一个关系模式分解为满足更高一级的范式要求的一系列子模式。
在数据库物理设计阶段，可以根据实际需要对数据库中某些3NF、BCNF模式考虑是否可以降低其规范化程度，以提高查询效率，这称为关系模式的去规范化处理。但不满足3NF的关系模式有可能导致数据库访问异常。因此，在设计基本表时，需要在模式规范化与查询效率之间折中权衡。
【真题演练】
如果关系模式设计不当，则有可能引起数据删除异常。这种删除异常是指（ ）。
A．两个事务同时对同一个关系进行删除而造成数据库不一致
B．由于删除关系中的元组而造成有用信息无法作为一个元组在数据库中存在
C．未经授权的用户对关系进行了删除
D．如果删除操作违反了完整性约束条件则会被拒绝
【答案】B
考点5 其他物理设计环节
（1）确定系统配置
①数据库系统配置参数的种类
数据库配置参数（如允许同时使用数据库的用户数、允许同时打开数据库对象数、数据库初始空间大小），磁盘块使用参数，内存缓冲区参数（如缓冲区个数和大小），时间片大小，装填因子，锁的数目等。
②配置合理参数的意义
合理的参数设置可以提高数据库的存储和查询效率，不合理的参数设置不仅会严重影响数据库的存储和查询效率，甚至可能导致系统运行时的数据库崩溃。存储分配参数的种类和合理的参数取值与具体的DBMS以及操作系统平台密切相关，同时依赖于具体的应用场景。数据库物理设计时设计人员对系统配置参数的设置和调整只是初步的。在系统运行时如果发现系统性能没有达到预期要求，而且原因与某些配置参数设置不当有关，那么需要DBA根据系统实际运行情况调整相关参数，以期改进系统性能：这属于数据库系统运行维护工作中的参数调整范畴。
（2）物理模式评估
前述各个步骤的设计结果定义了目标数据库的物理结构，组成了数据库内模式（或物理模式）。在设计过程中，通过对时间效率、空间效率、维护代价和各种用户要求权衡考虑，可能会产生多个设计备选方案，因此数据库设计人员必须对这些方案进行仔细评估，从中选择一个合理而且较优的方案作为数据库的物理结构。
评价物理数据库的方法完全依赖于所选用的DBMS，主要是从定量估算各种方案的存储空间、存取时间和维护代价入手，对估算结果进行权衡和比较，选择出一个较优的合理的物理结构。如果该结构不符合用户需求，则需要改进设计。
