计算机科班知识整理专栏系列文章：

设计与应用开发篇

第六章关系数据理论（关系规范化）

数据依赖与关系模式概述

数据依赖：数据依赖是一个关系内部属性与属性之间的一种约束关系。有多种类型的数据依赖，其中最重要的是函数依赖（Functional Dependency,FD）和多值依赖（Multivalued Dependency,MVD）

一个不好的关系模式会存在以下一些问题：

数据冗余太大：信息被重复存储，导致浪费大量存储空间
更新异常：当重复信息的一个副本被修改，所有副本都必须进行同样的修改。因此当更新数据时，系统要付出很大的代价来维护数据库的完整性，否则会面临数据不一致的危险。
插入异常：只有当一些信息事先已经存放在数据库中时，另外一些信息才能存入数据库中
删除异常：删除某些信息时可能丢失其它信息

规范化

概述

规范化理论是用来改造关系模式，通过分解关系模式来消除其中不合适的数据依赖，以解决数据冗余、插入异常、更新异常、删除异常这些问题，即改造逻辑结构，如何构造合适的数据逻辑结构。

范式：衡量关系模式达到的数据依赖程度的标准

多值依赖的定义：设R(U)是一个属性集U上的一个关系模式， X、 Y和Z是U的子集，并且Z＝U－X－Y。关系模式R(U)中多值依赖 X→→Y（Y多值依赖于X）成立，当且仅当对R(U)的任一关系r，给定的一对（x，z）值，有一组Y的值，这组值仅仅决定于x值而与z值无关。

多值依赖的性质包括：

（1）多值依赖具有对称性即若X→→Y，则X→→Z，其中Z＝U－X－Y

（2）多值依赖具有传递性即若X→→Y，Y→→Z，则X→→Z –Y

（3）函数依赖是多值依赖的特殊情况。即若X→Y，则X→→Y。

（4）若X→→Y，X→→Z，则X→→YZ。

（5）若X→→Y，X→→Z，则X→→Y∩Z。

（6）若X→→Y，X→→Z，则X→→Y-Z，X→→Z-Y。

定义：设R(U)是一个属性集U上的关系模式，X和Y是U的子集。若对于R(U)的任意一个可能的关系r，r中不可能存在两个元组在X上的属性值相等，而在Y上的属性值不等，则称“X函数确定Y” 或 “Y函数依赖于X”，记作X→Y。例如：

即Sno→Sname这个函数依赖只有在不可能存在两个元组在Sno上的值相等，而在Sname上的值不等时才成立。（左边相等右边必相等）

在关系模式R(U)中，对于U的子集X和Y，
如果X→Y，但Y不为X的子集，则称X→Y是非平凡的函数依赖
若X→Y，但Y为X的子集, 则称X→Y是平凡的函数依赖

完全函数依赖：

部分函数依赖：

上图中是完全函数依赖，是部分数依赖（即可分），因为Sno→Sdept成立，而Sno是（Sno，Cno）的真子集。

范式：

一个低一级范式的关系模式，通过模式分解可以转换为若干个高一级范式的关系模式的集合，这种过程就叫规范化

1NF

如果一关系模式r(R)的每个属性对应的域值都是不可分的(即原子的)，则称r(R)属于第一范式，记为r(R)Î1NF。

第一范式的目标是：将基本数据划分成称为实体集或表的逻辑单元，当设计好每个实体后，需要为其指定主码。

第一范式是对关系模式的最起码的要求。不满足第一范式的数据库模式不能称为关系数据库。

2NF

若关系模式R∈1NF，并且每一个非主属性都完全函数依赖于任何一个候选码，则R∈2NF。

第二范式的目标：将只部分依赖于候选码（即依赖于候选码的部分属性）的非主属性移到其他表中。

S-L-C这个函数依赖图中非主属性Sdept和Sloc部分函数依赖于码(Sno, Cno)

将其分为两个表后使得非主属性对其各自的码都是完全函数依赖。

2NF范式虽然消除了由于非主属性对候选码的部分依赖所引起的冗余及各种异常，但并没有排除传递依赖。因此，还需要对其进一步规范化。

3NF

若R∈3NF，则每一个非主属性既不部分依赖于码也不传递依赖于码

第三范式的目标：去掉表中不直接依赖于候选码的非主属性

Sno→Sdept Sdept → Sno Sdept→Sloc 可得： Sno→Sloc，即S-L中存在非主属性对码的传递函数依赖，S-L ∉ 3NF

采用投影分解法，把S-L分解为两个关系模式，以消除传递函数依赖：

BCNF

只有两个属性的关系最高可以是BCNF。

通常认为BCNF是修正的第三范式，有时也称为扩充的第三范式。

一个满足BCNF的关系模式有：

所有非主属性都完全函数依赖于每个候选码
所有的主属性都完全函数依赖于每个不包含它的候选码
没有任何属性完全函数依赖于非码的任何一组属性

BCNF范式排除了：

任何属性(包括主属性和非主属性)对候选码的部分依赖和传递依赖；
主属性之间的传递依赖。

例子：

关系模式STJ(S,T,J)中，S表示学生，T表示教师，J表示课程。每一教师只教一门课，每门课有若干教师，某一学生选定某门课，就对应一个固定的教师。\

由语义可得到函数依赖：(S,J)→T；(S,T)→J；T→J

因为没有任何非主属性对码传递依赖或部分依赖，

STJ ∈ 3NF。

因为T是决定因素，而T不包含码，所以STJ 不属于 BCNF 关系。

补充例子:

对于关系R（仓库号，管理员，物品名，数量

其中的函数依赖有：仓库名->管理员，管理员->仓库名，(仓库名，物品名)->数量

则：看第一个，存在仓库名->管理员，结束了，不是BCNF，因为条件一显然不满足，条件二仓库名不是超码。超码必须包含(仓库名，物品名) 或者（管理员，物品名)。

补充例子：

R(职工号,仓库号，设备号)

职工号->仓库号,(仓库号，设备号)->职工号

看第一个，职工号->仓库号，结束了，不是BCNF，条件一显然不满足，且职工号不是超码。

作为一个BCNF，必须满足以下三个性质：

1. 所有非主属性都完全依赖于每个候选码。

这个性质主要是确保2NF(消除了部分依赖)和3NF(消除了传递依赖）。

2. 所有主属性都完全函数依赖于每个不包含它的候选码。

消除了主属性对每个不包含它的候选码的部分依赖和传递依赖。就是我们上面第二个例子。

3. 没有任何属性完全函数依赖于非码的任何一组属性。

总结

候选码可以有多个，如上面（S,J）和（S,T）都是候选码，包含在任一一个候选码中的属性称为主属性，那么S,J,T都是主属性。

决定因素就是有其他属性对这个属性产生依赖，则此属性就是一个决定因素，和这个属性是否为候选码无关。

实际上，在函数依赖集中，只要满足下面两个条件其中一个，就是BCNF了：

X->Y是平凡的函数依赖，即Y属于X
X是R的超码

数据依赖的公理系统及模式分解相关内容

关系模式R <U，F >来说有以下的推理规则：

自反律（Reflexivity）：若Y Í X Í U，则X →Y为F所蕴含（平凡函数依赖）
增广律（Augmentation）：若X→Y为F所蕴含，且Z Í U，则XZ→YZ为F所蕴含
传递律（Transitivity）：若X→Y及Y→Z为F所蕴含，则X→Z为F所蕴含（传递函数依赖）
合并规则：由X→Y，X→Z，有X→YZ (2,3)
伪传递规则：由X→Y，WY→Z，有XW→Z (2,3)
分解规则：由X→Y及 ZÍY，有X→Z (1,3)

Armstrong公理系统是有效的、完备的。

有效性：由F出发根据Armstrong公理推导出来的每一个函数依赖一定在F+中；

完备性：F+中的每一个函数依赖，必定可以由F出发根据Armstrong公理推导出来

注：F={XàA1, …… , XàAn}的闭包F+计算是一个NP完全问题

数据库闭包

由一个属性直接或间接推导出的所有属性的集合。

例：设有关系模式R(U，F)，其中U={A，B，C，D，E，I}，F={A→D，AB→E，BI→E，CD→I，E→C}，计算(AE)+

解: （滚雪球算法）

(1) 令X={AE}，X(0)=AE，将F中的所有依赖右边化为单一元素

(2)在F中寻找尚未使用过的左边是A,E或AE的函数依赖，结果是: A→D， E→C；所以 X(1)=X(0)DC=ACDE，显然 X(1)≠X(0).

(3) 在F中寻找尚未使用过的左边是ACDE的子集的函数依赖，结果是: CD→I；所以 X(2)=X(1)I=ACDEI。虽然X（2）≠X(1)，但F中寻找尚未使用过函数依赖的左边已经没有X（2）的子集，所以不必再计算下去，即(AE)+=ACDEI。故AE的闭包为ACDEI，不包含所有的属性集合，

例如：f={a->b，b->c，a->d，e->f}；由a可直接得到b和d，间接得到c，则a的闭包就是{a，b，c，d}

候选码

对于给定的关系R（A1，A2，…An）和函数依赖集F，可将其属性分为4类：

L类仅出现在函数依赖左部的属性。
R 类仅出现在函数依赖右部的属性。
N 类在函数依赖左右两边均未出现的属性。
LR类在函数依赖左右两边均出现的属性。

定理：对于给定的关系模式R及其函数依赖集F，若X（X∈R）是L类属性，则X必为R的任一候选码的成员。

推论：对于给定的关系模式R及其函数依赖集F，若X（X∈R）是L类属性，且X+包含了R的全部属性；则X必为R的唯一候选码。

具体的步骤：

（1）将R 的所有属性分为L、R、LR 和N 四类，并令X 代表L、N 类，Y 代表LR 类。 R类只在右边出现，一定不为候选码

（2）根据L类，求X+。若X+包含了R 的全部属性，则即为R 的唯一候选码，转（5）；否则，转（3）。

（3）候选码可能的组合有L+LR中的任何一个；在Y 中取一属性A，求（XA）+ ，若它包含了R 的全部属性，则是候选码，转（4）；否则，调换一属性反复进行这一过程，直到试完所有Y 中的属性。

（4）如果已找出所有候选码，则转（5）；否则在Y 中依次取2 个、3 个、…，求它们的属性闭包，若其 闭包包含R 的全部属性，则是候选码 。

（5）结束。

例1：

R<U,F>,U=(A,B,C,D,E,G),F={AB-->C,CD-->E,E-->A.A-->G},求候选码。

因G只在右边出现,所以G一定不属于候选码;而B,D只在左边出现,所以B,D一定属于候选码;BD的闭包还是BD,则对BD进行组合,除了G以外,BD可以跟A,C,E进行组合

先看ABD

ABD本身自包ABD,而AB-->C,CD-->E,A-->G,所以ABD的闭包为ABDCEG=U

再看BDC

CD-->E,E-->A,A-->G,BDC本身自包,所以BDC的闭包为BDCEAG=U

最后看BDE

E-->A,A-->G,AB-->C,BDE本身自包,所以BDE的闭包为BDEAGC=U

因为(ABD)、(BCD)、(BDE)的闭包都是ABCDEG所以本问题的候选码有3个分别是ABC、BCD和BDE

最小函数依赖集

1.将F中的所有依赖右边化为单一元素

2.去掉F中的所有依赖左边的冗余属性.

如：X={AE}->Y

①找到左边A,E或AE的函数依赖，循环遍历求X的闭包，如果(x)+中最终包含Y，则X->Y为冗余函数依赖，删除 部分函数依赖X->Y 。（ 即x的闭包中包含Y则冗余 ）

模式的分解

当模式不符合关系范式时，需要进行模式分解。

1NF--存在部分依赖
2NF--完全依赖、存在传递依赖A->B B->C A->C（基本可消除插入异常）
3NF--完全依赖、没有传递依赖 (保持函数依赖，最高可以达到3NF)（属性全部是主属性，则R的最低范式3NF）（仍然存在一定的插入和删除异常）
BCNF--每一个表中只有一个候选码（全码组成的关系模式，最高可以达到BCNF）

3NF分解

分为保持依赖的分解与无损连接的分解。

分解口诀：

保函依赖分解题，先求最小依赖集。
依赖两侧未出现，分成子集放一边，剩余依赖变子集。
若要连接成无损，再添候选做子集。

例子：

已知R(ABCDE), F={A ->D,E->D,D->B,BC->D,DC->A}，求保持函数依赖的3NF分解，和具有无损连接性及保持函数依赖的3NF分解。

解：

第一步：保函依赖分解题，先求最小依赖集。

先求出R的最小依赖集，可得F={A ->D,E->D,D->B,BC->D,DC->A}

第二步：依赖两侧未出现，分成子集放一边。

首先可以发现没有不出现在两侧的元素不用单独分出一个子集，“剩余依赖变子集”然后我们将各依赖分

别划分为子集得到：{AD} {ED} {DB} {BCD} {DCA}，即为所求保持函数依赖的3NF分解

第三步：若要连接成无损，再添候选做子集。

候选码的求解：所谓候选码即能决定整个关系的，我们通过找未出现在依赖右边的和两侧均未出现的元素

即可求得：

可以发现C E未出现在右边，因此候选码为{CE}。故所求具有无损连接性及保持函数依赖的3NF分解为{AD} {ED} {DB} {BCD} {DCA} {CE}。

BCNF分解

分解口诀：

先求最小依赖集，候码非码成子集
余下左侧全候码，完成BCNF题

例子：

关系模式R,有U={A,B,C,D,E,G},F={B->G,CE->B,C->A,CE->G,B->D,C->D}，将关系模式分解为BCNF。

解：

第一步：先求最小依赖集。

可以发现CE->G多余，因此最小依赖集为F={B->G,CE->B,C->A,B->D,C->D}。

第二步：候码非码成子集。

由于候选码为(CE)，因此将CE->B划分出子集(BCE)，而B->G，B->D左侧均不含主属性(C、E)中的任何一个故划分出(BG),(BD)

第三步：此时剩余依赖F={C->A,C->D}剩余元素{A,C,D}检查发现函数依赖左侧都是候选码即完成BCNF分解，如果不满足则继续分解余下的。

于是BCNF分解的最后结果为{(BG),(BD),(ACD),(BCE)}。划分出(BG),(BD)

第七章数据库设计（ER模型，ER模型向关系模型转化）

数据库设计可以分为以下6个阶段：

需求分析：准确了解和分析用户需求（包括数据和处理）
概念结构设计：通过对用户需求进行综合、归纳与抽象，形成一个独立于具体DBMS的概念模型
逻辑结构设计：将概念结构转换为某个DBMS所支持的数据模型，并对其进行优化
物理结构设计：为逻辑数据模型选取一个最适合应用环境的物理结构（包括存储结构和存取方法）
数据库实施：根据逻辑设计和物理设计的结果建立数据库，编制与调试应用程序，组织数据入库并试运行
数据库运行和维护：在数据库投入正式使用后不断地对其进行评价、调整与修改

其具体要做的事情如下图：

需求分析

详细调查现实世界要处理的对象，充分了解原系统，获得用户的信息要求、处理要求、安全性与完整性要求
调查的重点是“数据”和“处理”，通过调查、收集和分析，获得用户对数据库的信息要求、处理要求、安全性和完整性要求
具体步骤：
1. 调查组织机构情况
2. 调查各部门的业务活动情况。
3. 在熟悉业务活动的基础上，协助用户明确对新系统的各种要求。
4. 确定新系统的边界
结构化分析方法（SA）：自顶向下、逐层分解分析系统

数据字典：数据收集分析的成果，是关于数据的描述即元数据
数据字典通常包括数据项、数据结构、数据流、数据存储、处理过程5个部分：
1. 数据项：数据项描述 = {数据项名，数据项含义说明，别名，数据类型，取值范围……}
2. 数据结构：数据结构反映了数据之间的组合关系。数据结构描述 = {数据结构名，含义说明，组成：{数据项或数据结构}}
3. 数据流：数据流是数据结构在系统内传输的路径。数据流描述 = {数据流名，数据流来源，数据流去向，平均流量，高峰期流量，组成：{数据结构}}
4. 数据存储：数据存储是数据结构停留或保存的地方。数据存储描述 = {数据存储名，说明，编号，输入的数据流，输出的数据流，组成：{数据结构}，数据量，存取频度，存取方式}
5. 处理过程：处理过程的具体处理逻辑一般用判定表或判定树来描述。处理过程描述 = {处理过程名，说明，输入：{数据流}，输出：{数据流}，处理：{简要说明}}概念结构设计
需求分析阶段完成数据字典（用于表示收集到的数据）和一组数据流程图（Data Flow Diagram，DFD）

概念结构设计

E-R图大题博客：blog.csdn.net/zxq11386346…

将用户需求(综合、归纳与抽象)抽象为信息结构
能较真实对的模拟现实世界、易理解、易修改、易在计算机上实现
工具：E-R模型、对E-R模型的拓展
联系：
- 联系是在n>=2个实体(可能相同)上的数学关系。
- 联系也可以有属性联系的度：参与联系的实体型的数目，如2个实体型之间的联系度为2，也称为二元联系；3个实体型之间的联系度为3，称为三元联系；N个实体型之间的联系度为N，也称为N元联系。
- 实体之间的联系1:1 1:n m:n（一对一、一对多、多对多）：

1:1：

1:n：

m:n：

步骤：

抽象数据并设计局部视图：
- 实体（矩形）：属性的划分：尽量当作属性，但属性不可再分、不能和其他实体有联系
- 属性（椭圆）：用无向边将其与对应的属性连接起来
- 联系（菱形）：连接实体
集成局部视图，得到全局概念结构
- 集成E-R图
- 合并：消除属性冲突、命名冲突(同名异意和异名同义)、结构冲突，将各部分的图合并 - 修改和重构：消除冗余
验证整体概念结构

设计概念结构通常有四类方法：

自顶向下：即首先定义全局概念结构的框架，然后逐步细化
自底向上：即首先定义各局部应用的概念结构，然后将它们集成起来，得到全局概念结构
逐步扩张：首先定义最重要的核心概念结构，然后向外扩充
混合策略：即将自顶向下和自底向上相结合，用自顶向下策略设计一个全局概念结构的框架，以它为骨架集成由自底向上策略中设计的各局部概念结构。

逻辑结构设计

逻辑结构设计：把基本E-R图转换为与选用DBMS所支持的数据模型相符合的逻辑结构，并对其进行优化
考察E-R图转换为关系模式，、其规则如下：注意联系独自成表（关系模式）时合并的是实体的码（而不是所有属性）
1. 一个1:1联系可以转换为一个独立的关系模式，也可以与任意一端对应的关系模式合并。如果转换为一个独立的关系模式，则与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性，每个实体的码均是该关系的码；如果与某一端实体对应的关系模式合并，则需要在该关系模式的属性中加入另一个关系模式的码和联系本身的属性。
2. 一个1：n联系可以转换为一个独立的关系模式，也可以与n端对应的关系模式合并。如果转换为一个独立的关系模式，则与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性，而关系的码为n端实体的码，在“n”端实体转换的关系中加入“1”端实体转换的关系的码
3. 一个m：n联系转换为一个关系模式。与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性，各实体的码组成关系的码或关系码的一部分
转换原则：

在ER模型中，如果有3个不同的实体集，3个M：N联系，根据ER模型转换为关系模型的规则，转换为

关系的数目是（6）。

优化：确定函数依赖（关系内、关系间）、消除冗余的联系、确定范式、根据实际情况确定是否要对关系模式进行合并或分解。
设置用户子模式（外模式）
- 用视图实现
- 使用更符合用户习惯的别名；
- 针对不同级别的用户定义不同的外模式，以满足系统对安全性的要求。
- 简化用户对系统的使用

物理结构设计

为一给定的逻辑数据模型选取一个最适合应用要求的物理结构，包括数据库在物理设备上的存储结构和存取方法。
设计过程中需要对时间效率、空间效率、维护代价和各种用户要求进行权衡

第八章数据库编程（实际操作了解即可，重实践轻理论）

系统篇

第九章关系查询处理和查询优化

查询处理步骤

关系数据库管理系统查询处理阶段：

查询分析
查询检查
查询优化
查询执行

选择操作的实现

简单的全表扫描方法：对查询的基本表顺序扫描，逐一检查每个元组是否满足选择条件
索引（或散列）扫描方法：如果选择条件的属性上有索引（如B+树索引或Hash索引），可以用索引扫描方法，通过索引先找到满足条件的元组主码或元组指针，再通过元组指针直接在查询的基本表中找到元组

连接操作的实现

例如：

SELECT * FROM Student, SC WHERE Student.Sno = SC.Sno;

方法有以下几种：

嵌套循环方法（nested loop）：对外层循环(Student)的每一个元组，检索内层循环(SC)中的每一个元组，并检查这两个元组在连接属性(sno)上是否相等
排序-合并方法（sort-merge join 或 merge join）：尤其适合连接的表已排好序的情况

步骤如下：

①如果连接的表没有排好序，首先对Student表和SC表按连接属性Sno排序

②取Student表中第一个Sno，依次扫描SC表中具有相同Sno的元组，把它们连接起来

③当扫描到Sno不相同的第一个SC元组时，返回Student表扫描它的下一个元组，再扫描SC表中具有相同Sno的元组，把它们连接起来。

④重复以上步骤直到Student表扫描完

索引连接方法（index join）：如果内层循环的连接属性上有索引，对于外层关系r的每一个元组tr，利用索引来搜索内层关系s 中与元组tr满足连接条件的元组。

步骤：

① 在SC表上已经建立属性Sno的索引。

② 对Student中每一个元组，由Sno值通过SC的索引查找相应的SC元组。

③ 把这些SC元组和Student元组连接起来循环执行②③，直到Student表中的元组处理完

Hash Join方法

把连接属性作为hash码，用同一个hash函数把Student表和SC表中的元组散列到hash表中。
划分阶段(building phase, 也称为partitioning phase)：
对包含较少元组的表(如Student表)进行一遍处理把它的元组按hash函数分散到hash表的桶中
试探阶段(probing phase,也称为连接阶段join phase)
对另一个表(SC表)进行一遍处理
把SC表的元组也按同一个hash函数（hash码是连接属性）进行散列
把SC元组与桶中来自Student表并与之相匹配的元组连接起来

查询优化策略

查询优化的优点：用户不必考虑如何最好地表达查询以获得较好的效率，系统可以比用户程序的“优化”做得更好。

原因如下：

优化器可以从数据字典中获取许多统计信息，而用户程序则难以获得这些信息。
如果数据库的物理统计信息改变了，系统可以自动对查询重新优化以选择相适应的执行计划。在非关系系统中必须重写程序，而重写程序在实际应用中往往是不太可能的。
优化器可以考虑数百种不同的执行计划，程序员一般只能考虑有限的几种可能性。
优化器中包括了很多复杂的优化技术，这些优化技术往往只有最好的程序员才能掌握。系统的自动优化相当于使得所有人都拥有这些优化技术。

代数优化

基于关系代数等价变换规则的优化方法，称为代数优化

①XXX结合律、交换律…

②查询树的启发式优化

关系代数表达式典型的启发式规则有：

选择运算应尽可能先做。这在优化策略中是最重要、最基本的一条，因为选择运算一般使计算的中间结果大大变小。
把投影运算和选择运算同时进行
把投影同其前或其后的双目运算结合起来，没有必要为了去掉某些字段而扫描一遍关系
把某些选择同在它前面要执行的笛卡尔积结合起来成为一个连接运算
找出公共子表达式

五大步：

选择变串连 (σF1(σF2(E)) = σF1∧F2(E) 反向使用)
尽可能先做选择 (等价变换规则4~9)
尽可能先做投影 (等价变换规则3,5,10,11)
同时执行多个选择和投影(等价变换规则3~5)
分组

例子：

与查询：

SELECT Student.Sname

FROM Student，SC

WHERE Student.Sno=SC.Sno AND SC.Cno='2'；

等价的关系代数表达式为：

Q1=пSname(δStudent.sno=sc.sno∧sc.cno='2'(Student×SC))

对该表达式进行优化。

解：

初始化查询语法树：
选择变串连 (σF1(σF2(E)) = σF1∧F2(E) 反向使用)，即将δStudent.sno=sc.sno∧sc.cno='2'变成δStudent.sno=sc.sno(δsc.cno='2')：

尽可能先做选择，即规则6：选择与笛卡儿积的交换律，把δsc.cno='2'(Student X SC)变成

Student X δsc.cno='2'(SC)：

尽可能先做投影，即规则3：投影的串接规律 ΠA1,A2,…,An(ΠA1,A2,…,An,B1,B2,…,Bn(E))=ΠA1,A2,…,An(E)反向使用:

补充例子：

物理优化

物理优化就是要选择高效合理的操作算法或存取路径，求得优化的查询计划，达到查询优化的目标。

选择操作的启发式规则：

对于小关系，使用全表顺序扫描，即使选择列上有索引
对于选择条件是主码 = 值的查询，查询结果最多是一个元组，可以选择主码索引
对于选择条件是非主属性 = 值的查询，并且选择列上有索引，则要估算查询结果的元组数目，如果比例较小（< 10%）可以使用索引扫描，否则还是使用全表顺序扫描
对于选择条件是属性上的非等值查询或范围查询，并且选择列上有索引，同样要估算查询结果的元组数目。
对于用AND连接的合取选择条件，如果有涉及这些属性的组合索引，则优先采用组合索引扫描方法；如果某些属性上有一般的索引？否则使用全表顺序扫描
对于用OR连接的析取选择条件，一般使用全表顺序扫描

连接操作的启发式规则：

如果2个表都已经按照连接属性排序，则选用排序-合并方法
如果一个表在连接属性上有索引，则可以选用索引连接方法
如果上面2个规则都不适用，其中一个表较小，可以选用Hash Join
最后可选用循环嵌套，选择其中较小的表作为外表

第十章数据库恢复技术

事务的基本概念

事务的概念：事务是用户定义的一个数据库操作序列，这些操作要么全做要么全不做，是一个不可分割的工作单位。例如在关系数据库中，一个事务可以是一条SQL语句、一组SQL语句。
事务是恢复和并发控制的基本单位。
事务通常是以BEGIN TRANSACTION开始，以COMMIT或ROLLBACK结束。COMMIT表示提交，ROLLBACK表示回滚，在事务运行的过程中发生某种故障事务不能继续执行，系统就会将事务对数据库的已完成操作全部撤销，从而回滚到事务开始时的状态。
保证事务ACID特性是事务处理的重要任务。
事务的特性：

原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持续性（Durability），简称ACID

原子性：事务是数据库的逻辑工作单位，事务中包括的操作要么都做，要么都不做

一致性：事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。事务执行过程中出现故障则称这时的数据库处于不一致性状态。

隔离性：一个事务的执行不能被其他事务干扰，并发执行的各个事务之间不能互相干扰

持续性（永久性）：一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。

事务的ACID特性可能遭到破坏的因素有：
1. 多个事务并行运行时，不同事务的操作交叉执行
2. 事务在运行过程中被强制停止
事务隔离的级别：
- 第一种隔离级别：Read uncommitted(读未提交)
  
  如果一个事务已经开始写数据，则另外一个事务不允许同时进行写操作，但允许其他事务读此行数据，该隔离级别可以通过“排他写锁”，但是不排斥读线程实现。这样就避免了更新丢失，却可能出现脏读，也就是说事务B读取到了事务A未提交的数据。
  
  解决了更新丢失，但还是可能会出现脏读。
- 第二种隔离级别：Read committed(读提交)
  
  如果是一个读事务(线程)，则允许其他事务读写，如果是写事务将会禁止其他事务访问该行数据，该隔离级别避免了脏读，但是可能出现不可重复读。事务A事先读取了数据，事务B紧接着更新了数据，并提交了事务，而事务A再次读取该数据时，数据已经发生了改变。
  
  解决了更新丢失和脏读问题。
- 第三种隔离级别：Repeatable read(可重复读取)
  
  可重复读取是指在一个事务内，多次读同一个数据，在这个事务还没结束时，其他事务不能访问该数据(包括了读写)，这样就可以在同一个事务内两次读到的数据是一样的，因此称为是可重复读隔离级别，读取数据的事务将会禁止写事务(但允许读事务)，写事务则禁止任何其他事务(包括了读写)，这样避免了不可重复读和脏读，但是有时可能会出现幻读。(读取数据的事务)可以通过“共享读镜”和“排他写锁”实现。
  
  解决了更新丢失、脏读、不可重复读、但是还会出现幻读。
- 第四种隔离级别：Serializable(可序化)
  
  提供严格的事务隔离，它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行，如果仅仅通过“行级锁”是无法实现序列化的，必须通过其他机制保证新插入的数据不会被执行查询操作的事务访问到。序列化是最高的事务隔离级别，同时代价也是最高的，性能很低，一般很少使用，在该级别下，事务顺序执行，不仅可以避免脏读、不可重复读，还避免了幻读。
  
  解决了更新丢失、脏读、不可重复读、幻读(虚读)。

故障的种类

事务内部的故障
系统故障：如操作系统故障，CPU故障，系统断电
介质故障：如磁盘损坏、磁头碰撞、瞬时强磁场干扰等
计算机病毒

恢复的实现技术及策略

恢复原理：冗余，即利用存储在系统其它地方的冗余数据来重建数据库中已被破坏或不正确的那部分数据。

建立冗余数据的两大方法：数据转储、登记日志文件

数据转储

转储即DBA定期地将整个数据库复制到磁带或另一个磁盘上保存起来的过程。这些备用的数据称为后被副本。
转储又分为静态转储和动态转储：静态转储必须等待正在运行的用户事务结束才能进行；动态转储是指转储期间允许对数据库进行存取或修改，即转储和用户事务可以并发执行。
也可分为海量转储和增量转储：海量转储即每次转储全部数据库，增量转储即每次只转储上一次转储后更新的数据。

登记日志文件（Logging）

审计

数据库的审计即每当用户对数据库执行操作（CRUD）时就在审计文件中增加一条记录（包含谁执行了什么操作），启用一个专用的审计日志（Audit Log），将用户对数据库的所有操作记录在上面。

审计的作用：

(1) 保证数据库的安全性，数据库管理员可以根据审计文件观察是否有人执行了不安全的操作。审计员利用审计日志监控数据库中的各种行为，找出非法存取数据的人、时间和内容

(2) 利于查错，可以通过观察执行了哪些操作来找出错误的来源

审计很费时间和空间
DBA可以根据应用对安全性的要求，灵活地打开或关闭审计功能
审计功能主要用于安全性要求较高的部门

日志文件的概念

日志文件是用来记录事务对数据库的更新操作的文件。不同数据库系统采用的日志文件格式并不完全一样，主要有两种格式：以记录为单位的日志文件和以数据块为单位的日志文件

对以记录为单位的日志文件，日志文件中需要登记的内容包括：

各个事务的开始（BEGIN TRANSACTION）标记
各个事务的结束（COMMIT或ROLLBACK）标记
各个事务的所有更新操作

以上每一条内容记为一个日志记录（log record）

每个日志记录的内容主要包括：

事务标识（标明是哪个事务）
操作的类型（插入、删除或修改）
操作对象（记录内部标识）
更新前数据的旧值（对插入操作而言，此项为空值）
更新后数据的新值（对删除操作而言，此项为空值）

对以数据块为单位的日志文件，日志记录的内容包括事务标识和被更新的数据块。由于将更新前的整个块和更新后的整个块都放入日志文件中，操作的类型和操作对象等信息就不必放入日志记录中了。

日志文件的作用

日志文件用于事务故障恢复和系统故障恢复，并协助后备副本进行介质故障恢复

具体作用如下：

事务故障恢复和系统故障恢复必须用日志文件。
在动态转储方式中必须建立日志文件，备份副本和日志文件结合起来才能有效地恢复数据库。
在静态转储方式中，也可以建立日志文件。

登记日志文件的规则

为保证数据库是可恢复的，登记日志文件时必须遵循两条规则：

(1) 登记的次序严格按并发事务执行的时间次序

(2) 必须先写日志文件，后写数据库

如果先写了数据库修改，但是没有登记这个日志，那么中途运行故障就无法恢复这个修改了。

恢复策略

REDO：重做，正向扫描日志文件，对每个REDO事务重新执行日志文件登记的操作
UNDO：撤销，反向扫描日志文件，对每个UNDO事务的更新操作执行逆操作
COMMIT：提交，将事务中所有对数据库的更新写回到磁盘上的物理数据库中，事务正常结束
ROLLBACK：回滚，事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成操作全部撤销，回滚到事务开始时的状态

具有检查点的恢复技术

在故障发生时还未完成的事务需要撤销，在检查点和故障点之间完成的事务需要重做，因为它们对数据库所做的修改在故障发生时可能还在缓冲区中。

检查点记录的内容包括：

①建立检查点时刻所有正在执行的事务清单

②这些事务最近一个日志记录的地址

第十一章并发控制## 第十一章并发控制

在进行授权时，数据对象的粒度越小，授权子系统就越灵活。

并发控制概述：事务是并发控制的基本单位，并发控制用于保证事务的隔离性和一致性。

事务读数据x一般记为R(x)，写数据x一般记为W(x)。

如果不对并发操作进行正确调度，可能导致数据的不一致性问题，主要包括丢失修改、不可重复读和读“脏”数据。

丢失修改：两个事务读入同一数据并修改，其中一个事务的修改会丢失
不可读重复：事务T1读取数据后，T2执行更新操作，使T1无法再现前一次读取结果
读脏数据：“脏”数据指事务T1修改某一数据，并将其写回磁盘，事务T2读取同一数据后，T1由于某种原因被撤销，则T2读取到的数据就为“脏”数据，即不正确的数据。

封锁

封锁即事务T在对某个数据对象例如表、记录等操作之前，先向系统发出请求，对其加锁。加锁后事务T就对该数据对象有了一定的控制，在事务T释放它的锁之前，其他的事务不能更新此数据对象。
排它锁（写锁，X锁）：若事务T对数据对象A加上X锁，则只允许T读取和修改A，其他任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。这就保证了其他事务在T释放A上的锁前不能再读取和修改A。
共享锁（读锁，S锁）：若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁而不能加X锁。

活锁和死锁

活锁即一个事务可能永远等待（系统总是先批准其他事务的锁请求），可以采用先来先服务的策略解决。
死锁即两个事务互相申请对方锁住的资源导致两个事务永远不能结束。
预防策略：
1. 一次性封锁法：一次性封锁发要求每个事务必须一次将所有要使用的数据全部加锁
缺点：势必扩大封锁的范围，从而降低了系统的并发度；
1. 顺序封锁法：预先对数据对象规定一个封锁顺序，所有事务都按这个顺序实行封锁。
缺点；难以事先确定封锁顺序
死锁解除策略：
1. 超时法：如果一个事务的等待时间超过了规定的时限，就认为发生了死锁；
缺点：可能误判死锁，事务因为其他原因使等待时间超过时限；若时限设置得太长，死锁发生后不能及时发现
1. 等待图法：并发控制子系统周期性地生成事务等待图，并进行检测，如果发现图中存在回路，则发生了死锁。

如果检测到死锁，一般采用的方法是选择一个处理死锁代价较小的事务，将其撤销，释放此事务持有的

所有的锁，之后对撤销的事务所执行的数据修改操作必须加以恢复。

并发调度的可串行性

定义：多个事务的并发执行是正确的，当且仅当其结果与按某一次序串行地执行这些事务时的结果相同，称这种调度策略为可串行化的调度。

可串行性（Serializability）是并发事务正确调度的准则，按这个准则规定，一个给定的并发调度，当且仅当它是可串行化的，才认为是正确调度。

冲突可串行化调度

冲突操作是指不同的事务对同一个数据的读写操作和写写操作：

Ri (x)与Wj(x) /* 事务Ti读x，Tj写x，其中i≠j*/
Wi(x)与Wj(x) /* 事务Ti写x，Tj写x，其中i≠j*/

不能交换(Swap)的动作：

同一事务的两个操作
不同事务的冲突操作

一个调度Sc在保证冲突操作的次序不变的情况下，通过交换两个事务不冲突操作的次序得到另一个调度Sc’，如果Sc’是串行的，称调度Sc 为冲突可串行化的调度。

一个调度时冲突可串行化的，那么它一定是可串行化的调度，因此，可以用这种方法来判断一个调度是否是冲突可串行化的。

冲突可串行化调度是可串行化调度的充分条件，不是必要条件，因为还有不满足冲突可串行化条件的可串行化调度。

例子：

Sc1=r1(A)w1(A)r2(A)w2(A)r1(B)w1(B)r2(B)w2(B)

把w2(A)与r1(B)w1(B)交换，得到：

r1(A)w1(A)r2(A)r1(B)w1(B)w2(A)r2(B)w2(B)

再把r2(A)与r1(B)w1(B)交换：

Sc2＝r1(A)w1(A)r1(B)w1(B)r2(A)w2(A)r2(B)w2(B)

Sc2等价于一个串行调度T1，T2，Sc1冲突可串行化的调度

两段锁协议

两段锁协议：是指所有的事务必须分两个阶段对数据项加锁和解锁。即事务分两个阶段，第一个阶段是获得封锁。事务可以获得任何数据项上的任何类型的锁，但是不能释放；第二阶段是释放封锁，事务可以释放任何数据项上的任何类型的锁，但不能申请。

第一阶段是获得封锁的阶段，称为扩展阶段：其实也就是该阶段可以进入加锁操作，在对任何数据进行读操作之前要申请获得S锁，在进行写操作之前要申请并获得X锁，加锁不成功，则事务进入等待状态，直到加锁成功才继续执行。就是加锁后就不能解锁了。
第二阶段是释放封锁的阶段，称为收缩阶段：当事务释放一个封锁后，事务进入封锁阶段，在该阶段只能进行解锁而不能再进行加锁操作。

例子：

事务T1遵守两段锁协议，其封锁序列是：

Slock A Slock B Xlock C Unlock B Unlock A Unlock C；

l← 扩展阶段 →l l← 收缩阶段 →l

事务T2不遵守两段锁协议，其封锁序列是：

Slock A Unlock A Slock B Xlock C Unlock C Unlock B

程序员不得不会的计算机科班知识——数据库原理篇（下）