数据库系统概论（第五版）王珊学习笔记

泛华赏

已于 2024-03-23 10:02:48 修改

阅读量655

点赞数 8

文章标签：数据库

于 2023-12-25 12:00:25 首次发布

本文链接：https://blog.csdn.net/yk071300/article/details/135191322

版权

第一章绪论

1.1 数据库系统概述

1.1.1 四个概念

数据（data）：描述事物的符号记录称为数据，数据的含义称为数据的语义，数据与其语义是不可分的

数据库（DataBase,DB）：数据库是长期存储在计算机内、有组织的、可共享的大量数据的集合。数据库中的数据按一定的数据模型组织、描述和存储，具有较小的冗余度（redundancy /rɪˈdʌn.dən.si/）、较高的数据独立性（data independency）和易扩展性（scalability /ˌskeɪ.ləˈbɪl.ə.ti/），并可为各种用户共享

数据库管理系统（DataBase Management System,DBMS）：位于用户和操作系统之间的一层数据管理软件。主要功能为:（1）数据定义功能：提供数据定义语言（Data Definition Language,DDL） (2)数据组织、存储和管理（3）数据操纵功能：提供数据操纵语言(Data Manipulation Language)（4）数据库的事务管理和运行管理（5）数据库的建立和维护功能（6）其他功能

数据库系统（DataBase System,DBS）：数据库系统是由数据库、数据库管理系统（及其应用开发工具）、应用程序和数据库管理员（DataBase Administrator,DBA）组成的存储、管理、处理和维护数据的系统

1.1.2 数据管理技术的产生和发展

1.人工管理阶段

特点:(1)数据不保存

(2)应用程序管理数据

(3)数据不共享

(4)数据不具有独立性

2.文件系统阶段

优点:(1)文件可以长期保存(2)由文件系统管理数据

缺点:(1)数据共享性差，冗余度大(2)数据独立性差

3.数据库系统阶段

特点:(1)数据结构化

数据库系统实现整体数据的结构化，所谓“整体”结构化是指数据库中的数据不在仅仅针对某一个应用，而是面向整个组织或企业，不仅数据内部是结构化的，而且整体式结构化的，数据之间是具有联系的

(2)数据的共享性高、冗余度低且易扩充

(3)数据独立性高

(高度的)物理独立性是指用户的应用程序与数据库中数据的物理存储是相互独立的；(一定的)逻辑独立性是指用户的应用程序与数据库的逻辑结构是相互独立的

(4)数据由数据库管理系统统一管理和控制

数据库管理系统必须提供以下几方面的数据控制功能:①数据的安全性保护：保护数据以防止不合法使用造成的数据泄密和破坏②数据的完整性检查：数据的正确性、有效性和相容性③并发控制：控制和协调多用户的并发操作④数据库恢复：从错误状态恢复到正确状态

1.2数据模型

1.2.1 两类数据模型(data model)

1.第一类概念模型：也叫信息模型，它是按用户的观点来对数据和信息建模，主要用于数据库设计

2.第二类中的逻辑模型主要包括层次模型、网状模型、关系模型、面向对象数据模型和对象关系数据模型、半结构化数据模型等。物理模型是对数据最底层的抽象，它描述数据在系统内部的表示方式和存储方法，或在磁盘或磁带上的存储方式和存取方法，是面向计算机系统的

1.2.2 概念模型

1.基本概念

(1)实体(entity)

客观存在并可相互区别的事务物称为实体，例如一个学生、一个部门

(2)属性(attribute)

实体所具有的某一特性称为属性，例如学生实体的学号、姓名等属性

(3)码(key)

唯一标识实体的属性集称为码，例如学号

(4)域（domain）

是一组具有相同数据类型的值的集合。属性的取值范围来自某个域

(5)实体型(entity type)

用实体名及其属性名集合来抽象和刻画同类实体，称为实体型。例如学生（学号、姓名）

(6)实体集(entity set)

同一类型实体的集合称为实体集。例如全体学生

(7)联系(relationship)

包括实体（型）内部的联系和实体（型）外部的联系，前者通常是指组成实体的各属性之间的联系，后者通常指不同实体集之间的联系。实体之间的联系有一对一、一对多和多对多等多种类型。

2.概念模型的一种表示方法：实体-联系法

实体-联系方法（Entity-Relationship approach）用E-R图来描述现实世界的概念模型

1.2.3 数据模型的组成要素

1.数据结构

数据结构描述数据库的组成对象以及对象之间的联系

2.数据操作

数据操作是指对数据库中各种对象（型）的实例（值）允许执行的操作的合集，包括操作及有关的操作规则

数据库主要由查询和更新（插入、删除、修改）两类操作。（增删改查）

3.数据的完整性约束条件

数据的完整性约束条件是一组完整性规则，以保证数据的正确、有效和相容，此外数据模型还应提供定义完整性约束条件的机制，以反映具体应用所涉及的数据必须遵守的特定的语义约束条件

1.2.4 常用的数据模型

常用的逻辑数据模型有：层次模型、网状模型、关系模型、面向对象数据模型、对象关系数据模型、半结构化数据模型

其中层和网状模型统次模型称为格式化模型，在格式化模型中数据结构的单位是基本层次联系，所谓的基本层次联系是指两个记录以及它们之间一对多（一对一）的联系

1.2.5 层次模型

1.层次模型的数据结构

(1)有且只有一个结点没有双亲结点，这个结点称为根结点

(2)根以外的其他结点有且仅有一个双亲结点

类似数据结构里的树，用指针表示结点之间的联系

2.层次模型的数据操纵与完整性约束

如果没有相应的双亲结点值就不能插入它的子女结点值

如果删除双亲结点值，则相应的子女结点值也将同时被删除

3.层次模型的优缺点

优点:(1)层次模型的数据结构比较简单清晰

(2)层次数据库的查询效率高，用指针实现记录之间的联系，也及记录之间的存取路径，所以层次数据库的性能优于关系数据库，不低于网状数据库

(3)层次数据库提供了良好的完整性支持

缺点:(1)表示联系不自然，不适合表示结点之间的多对多联系

(2)如果一个结点具有多个双亲结点，层次模型就很笨拙，只能通过引入冗余数据或创建非自然的数据结构解决，对插入和删除的限制比较多，应用程序的编写会比较复杂

(3)查询子女结点必须通过双亲结点

(4)由于结构严密，层次命令趋于程序化

1.2.6 网状模型

1.网状模型的数据结构

(1)允许一个以上的结点没有双亲

(2)一个结点可以有多于一个的双亲

2.网状模型的数据操纵与完整性约束

一般来说没有层次模型那样严格的完整性约束条件，但具体的网状数据库系统对数据操纵都加了一些限制，提供了一定的完整性约束

3.网状模型的优缺点

优点:(1)能够更为直接的描述现实世界

(2)具有良好的性能、存取效率较高

缺点:(1)结构比较复杂，且随着应用环境的扩大，数据库的结构就变得越来越复杂

(2)网状模型的DDL和DML复杂，并要嵌入一种高级语言（如C）,用户不易掌握和使用

(3)优于记录之间的联系是通过存取路径来实现的，应用程序在访问数据时必须选择适当的存取路径，因此用户必须了解系统结构的细节，加重了编写应用程序的负担

1.2.7 关系模型

1.关系模型的数据结构

每个关系的数据结构是一张规范化的二维表

关系(relation):一个关系对应通常说的一张表

元组(tuple):表中的一行即为一个元组

属性(attribute):表中的一列即为一个属性，其名称即为属性名

码(key):也称为码键，表中的某个属性组，它可以唯一确定一个元组

域(domain):是一组具有相同数据类型的值的集合，属性的取值范围来自某个域

分量:元组中的一个属性值

关系模式:对关系的描述，一般表示为：关系名（属性1，属性2，···，属性n）

关系模型要求关系必须是规范化的，关系的每一个分量必须是不可分的数据项

2.关系模型的数据操纵与完整性约束

数据操纵主要有增、删、改、查

数据的完整性约束条件包括三大类：实体完整性、参照完整性和用户定义的完整性

关系模型中的数据操作是集合操作，操作对象和操作结果都是关系；关系模型把存取路径向用户隐蔽起来，用户只要指出“干什么”或“找什么”，不必详细说明“怎么干”和“怎么找”，从而提高了数据独立性，提高了用户生产率

3.关系模型的优缺点

优点:(1)建立在严格的数学概念的基础上

(2)关系模型的概念单一

(3)关系模型的存取路径对用户透明

缺点:(1)查询效率往往不如格式化模型

(2)为提高效率，增加了开发数据库管理系统的难度

1.3 数据库系统的结构

从数据库应用开发人员角度看，数据库通常采用三级模式结构，即数据库系统是由外模式、模式和内模式三级构成，是数据库系统内部的系统结构。从数据库最终用户角度看，数据库系统的结构分为单用户结构、主从式结构、分布式结构、客户-服务器、浏览器-应用服务器/数据库服务器多重结构，这是数据库系统外部的体系结构。

1.3.1 数据库系统模式的概念

数据模型中由“型(type)”和“值(value)”的概念。型是对某一数据的结构和属性的说明，值是型的一个具体赋值

模式(schema)是数据库中全体数据的逻辑结构和特征的描述，它仅仅涉及型的描述，不涉及具体的值。模式的一个具体值称为模式的一个实例(instance)

模式是相对稳定的，而实例是相对变动的

1.3.2 数据库系统的三级模式结构

1.模式(schema)

模式也称逻辑模式，是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图

2.外模式(exteral schema)

外模式也称子模式(subschema)或用户模式，它是数据库用户(包括应用程序员和最终用户)能够看见和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据视图，是与某一应用有关的数据的逻辑表示

3.内模式(interal schema)

内模式也称存储模式(storage schema)，一个数据库只有一个内模式。它是数据物理结构和存储方式的描述，是数据在系统内部的组织方式

1.3.3 数据库的二级印象功能与数据独立性

1.外模式/模式映象

模式描述的是数据的全局逻辑结构，外模式描述的是数据的局部逻辑结构。对于同一个模式可以有任意个外模式。对于每一个外模式，数据库系统都有一个外模式/模式映象，它定义了该外模式和模式之间的对应关系。这些映像定义通常包含在各自外模式的描述中。

当模式改变时（例如增加新的关系、新的属性、改变属性的数据类型等），由数据库管理员对各个外模式/内模式的映像作相应的改变，可以使外模式保持不变。应用程序是根据数据的外模式来编写的，从而应用程序不必修改，保证了数据与应用程序的逻辑独立性，简称数据的逻辑独立性

2.模式/内模式映象

数据库中只有一个模式，也只有一个内模式，所以模式/内模式映像是唯一的，它定义了数据全局逻辑结构与存储结构之间的对应关系。该映像定义通常包含在模式当中。当数据库的存储结构改变时，由数据库管理员对模式/内模式映像作出相对的改变，可以使模式保持不变，从而应用程序也不必改变。保证了数据与程序的物理独立性，简称数据的物理独立性

1.4 数据库系统的组成

1.硬件平台及数据库

数据库对硬件资源的要求：

(1)要有足够大的内存存放操作系统、数据库管理系统的核心模块、数据缓冲区和应用程序

(2)要有足够大的磁盘或磁盘阵列等设备存放数据库，有足够大的磁带（或光盘）做数据备份

(3)要求系统有较高的通道能力，以提高数据传输率

2.软件

(1)数据库管理系统

(2)支持数据库管理系统运行的操作系统

(3)具有与数据库接口的高级语言及其编译系统，便于开发应用程序

(4)以数据库管理系统为核心的应用开发工具

(5)为特定应用环境开发的数据库应用系统

3.人员

(1)数据库管理员(DataBase Administrator,DBA)

①决定数据库中的信息内容和结构

②决定数据库的存储结构和存取策略

③定义数据的安全性要求和完整性约束条件

④监控数据库的使用和运行

⑤数据库的改进和重组、重构

(2)系统分析员和数据库设计人员

系统分析员负责应用系统的需求分析和规范说明，要和用户及数据库管理员相结合，确定系统的硬件软件配置，并参与数据库系统的概要设计

数据库设计人员负责数据库中数据的确定及数据库各级模式的设计。数据库设计人员必须参加用户需求调查和系统分析，然后进行数据库设计。很多情况下，数据库设计人员由数据库管理员担任

(3)应用程序

应用程序员负责设计和编写应用系统的程序模块，并进行调试和安装

(4)用户

指最终用户，可分为：

①偶然用户：这类用户不经常访问数据库，但每次访问数据库时往往需要不同的数据库信息，一般为企业或机构的高中级管理人员

②简单用户：多数最终用户都是简单用户，主要工作是查询和更新数据库，一般都是通过应用程序员精心设计并具有友好界面的应用程序存取数据库。如银行的职员、机票预定工作人员、宾馆总台服务人员

③复杂用户：这类用用户都比较熟悉数据库管理系统的各种功能，能够直接使用数据库语言访问数据库，甚至能够基于数据库管理系统的应用程序接口编制自己的应用程旭。如工程师、科学家、经济学家

第二章关系数据库

2.1 关系数据结构及形式化定义

2.1.1 关系

关系数据结构的形式化定义

1.域(domain)

域是一组具有相同数据类型的值的集合，例如自然数、整数、{男，女}

2.笛卡尔积(cartesian product)

笛卡尔积是域上的一种集合运算，定义：给定一组域 $D_{1}$ , $D_{2}$ ,• • •, $D_{n}$ ,允许其中某些域是相同的， $D_{1}$ , $D_{2}$ ,• • •, $D_{n}$ 的笛卡尔积为 $D_{1}$ × $D_{2}$ ×•••× $D_{n}$ ={( $d_{1}$ , $d_{2}$ ,• • •, $d_{n}$ )| $d_{i}$ ∈ $D_{i}$ , $i$ = 1,2,• • •,n}其中每一个元素( $d_{1}$ , $d_{2}$ ,• • •, $d_{n}$ )叫作一个n元组(n-tuple),或简称元组(tuple),元素中的每一个值 $d_{i}$ 叫作一个分量(component)。这里的迪卡尔基严格的讲应该是广义的笛卡尔积，因为这里笛卡尔积的元素为元组，两个分别为n目和m目的关系R和S的笛卡尔积是(n+m)目关系

一个域允许的不同取值个数称为这个域的基数(cardinal number)

若 $D_{i}$ ( $i$ = 1,2,• • •,n)为有限集,则其基数为 $m_{i}$ ( $i$ = 1,2,• • •,n)，则 $D_{1}$ × $D_{2}$ ×•••× $D_{n}$ 的基数M为 $M=\underset{i=1}{\overset{n}{\prod }}m_{i}$

3.关系(relation)

(1)定义： $D_{1}$ × $D_{2}$ ×•••× $D_{n}$ 的子集叫作在域 $D_{1}$ , $D_{2}$ ,• • •, $D_{n}$ 上的关系，表示为 $R(D_{1},D_{2},\cdots D_{n})$ 这里R表示关系的名字，n是关系的目或度(degree)

关系中的每个元素是关系中的元组，通常用t表示

当n=1时，称该关系为单元关系(unary relation),或一元关系

当n=2时，称该关系为二元关系(binary relation)

关系是一张二维表，每行对应一个元组，每列对应一个域，由于域可以相同，所以必须对每列起一个名字，称为属性(attribute)。n目关系必有n个属性

若关系中的某一属性组的值能唯一标识一个元组，而其子集不能，则称该属性组为候选码(candinate key)

若一个关系有多个候选码，则选定其中的一个为主码(primary key)

候选码的诸属性称为主属性(prime attribute)。不包含在任何候选码中的属性称为非主属性(non-prime attribute)或非码属性(non-key attribute)

在最简单的情况下，候选码只包含一个属性。在最极端的情况下，关系模式的所有属性是这个关系模式的候选码，称为全码(all-key)

(2)关系的三种类型

①基本关系(通常又称为基本表或基表)：基本表是实际存在的表，它是实际存储数据的逻辑表示

②查询表：是查询结果对应的表

③视图表：是由基本表或其他视图表导出的表，是虚表，不对应实际存储的数据

(3)基本关系具有的6条性质

①列是同质的每一列中的分量是同一类型的数据，来自同一个域

②不同的列可出自同一个域，称其中的每一列为一个属性，不同的属性要给予不同的属性名

③列的顺序无所谓，列的次序可以任意交换

④任意两个元组的候选码不能取相同的值

⑤行的顺序无所谓，行的次序可以任意交换

⑥分量必须取原子值，每一个分量都必须是不可分的数据项

2.1.2 关系模式

定义:关系的描述称为关系模式(relation schema)它可以形式化的表示为 $R(U,D,DOM,F)$ ,其中R为关系名，U为组成该关系的属性名集合，D为U中属性所来自的域，DOM为属性向域的映像集合，F为属性间数据的依赖关系集合

2.1.3 关系数据库

所有关系的集合构成一个关系数据库

关系数据库也有型和值的之分。关系数据库的型也称为关系数据库模式，是对关系数据库的描述；关系数据库的值是这些关系模式在某一时刻对应的关系的集合，通常就称为关系数据库

2.2 关系操作

2.2.1 基本的关系操作

查询(query)、插入(insert)、删除(delete)、修改(updata)

关系的查询表达能力很强，是关系操作中最主要的部分。查询操作又可分为选择(select)、投影(project)、连接(join)、除(divide)、并(union)、差(except)、交(intersection)和笛卡尔积等。其中选择、投影、并、差、笛卡尔积是5种基本操作，其他操作可由基本操作来定义和导出。

关系操作的特点是集合操作方式，即操作的对象和结果是集合，也称为一次一集合(set-at-a-time)的方式，相应的，非关系数据模型的数据操作方式则为一次一记录(record-at-a-time)的方式

2.2.2 关系数据语言的分类

1.关系代数(relational algebra):用对关系的运算来表达查询要求

2.关系演算(relational calculus)：用谓词来表达查询要求，按谓词变元的基本对象是元组变量还是域变量分为元组关系演算和域关系演算

3.结构化查询语言(Structured Query Language,SQL)：是集数据查询、数据定义语言、数据操纵语言和数据控制语言(Data Contral Language,DCL)于一体的关系数据语言。是一种高度非过程化语言

2.3 关系的完整性

关系模型中有三类完整性约束：实体完整性、参照完整性和用户定义的完整性，其中实体完整性和参照完整性是关系模型必须满足的完整性约束条件，被称为是关系的两个不变性

2.3.1 实体完整性

规则2.1 实体完整性规则 若属性(指一个或一组属性)A是基本关系R的主属性，则A不能取空值(null value)。所谓空值就是“不知道”或“不存在”或“无意义”的值

说明：1.实体完整性规则是针对基本关系而言的

2.现实世界的实体是可以区分的，即它们具有某种唯一性标识

3.相应地，关系模型中以主码作为唯一性标识

4.主码中的属性不能为空值

2.3.2 参照完整性

设F是基本关系R的一个或一组属性，但不是关系R的码， $K_{s}$ 是基本关系S的主码。如果F与 $K_{s}$ 相对应，则称F是R的外码(foreign key),并称基本关系R为参照关系(referencing relation),基本关系S为被参照关系(referenced relation)或目标关系(target relation)

规则2.2 参照完整性规则 若属性(或属性组)F是基本关系R的外码，它与基本关系S的主码 $K_{s}$ 相对应(基本关系R和S不一定是不同的关系)，则对于R中每个元组在F上的值必须：

•或者取空值(F的每个属性值均为空值)不是主码所以可以取空值例如表示未分配专业

•或者等于S中某个元组的主码值

2.2.3 用户定义的参照完整性

不同的关系数据库系统根据其应用环境的不同，往往还需要一些特殊的约束条件。用户定义的完整性就是针对某一具体关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求。

2.4 关系代数

用对关系的运算来表达查询，按运算符的不同可以分为传统的集合运算和专门的关系运算两类

关系代数运算符
运算符		含义
集合运算符	$\cap$	并
	-	差
	$\cup$	交
	$\times$	笛卡尔积
专门的关系运算符	$\sigma$	选择
	$\Pi$	投影
	$\bowtie$	连接
	$\div$	除

条件表达式中的运算符
运算符		含义
比较运算符	$>$	大于
	$\geq$	大于等于
	$<$	小于
	$\leqslant$	小于等于
	$=$	等于
	$< >$	不等于
逻辑运算符	┐	非
	$\wedge$	与
	$\vee$	或

2.4.1 传统的集合运算

1.并(union)

关系R与关系S的并记作： $R\cup S=\left \{ t|t\in R\vee t\in S \right \}$ ,其结果仍为n目关系，由属于R或S的元组组成

2.差(except)

关系R与关系S的并记作， $R-S=\left \{ t|t\in R\wedge t\notin S \right \}$ ，其结果仍为n目关系，由属于R而不属于S的所有元组组成

3.交(intersection)

关系R与关系S的并记作， $R\cap S=\left \{ t|t\in R\wedge t\in S \right \}$ ，其结果仍为n目关系，由既属于R又属于S的元组组成，关系的交可以用差来表示，即 $R\cap S=R-(R-S)$

4.笛卡尔积(cartesian product)

这里的笛卡尔积是广义的笛卡尔积，因为这里的笛卡尔积的元素为元组。

两个分别为n目和m目的关系R和S的笛卡尔积是一个(n+m)列的元组的集合，元组的前n列是关系R的一个元组，后m列是关系S的一个元组。若R有 $K_{1}$ 个元组，S有 $K_{2}$ 个元组，则关系R和关系S的笛卡尔积有 $K_{1} \times K_{2}$ 个元组。记作 $R\times S=\left \{ \overset{\frown }{t_{r}t_{s}} |t_{r}\in R\wedge t_{s}\in S\right \}$

2.4.2 专门的关系运算

引入

(1)设关系模式 $R(A_{1},A_{2},\cdot \cdot \cdot ,A_{n})$ ,它的一个关系设为 $R$ 。 $t\in R$ 表示 $t$ 是 $R$ 的一个元组。 $t\left [ A_{i} \right ]$ 则表示元组 $t$ 中相对应于属性 $A_{i}$ 的一个分量

(2)若 $A=\left \{ A_{i1},A_{i2},\cdot \cdot \cdot ,A_{ik} \right \}$ ,其中 $A_{i1},A_{i2},\cdot \cdot \cdot ,A_{ik}$ 是 $A_{1},A_{2},\cdot \cdot \cdot ,A_{n}$ 中的一部分则 $A$ 称为属性列或属性组。 $t\left [ A \right ]=\left ( t\left [ A_{i1} \right ],t\left [ A_{i2} \right ],\cdot \cdot \cdot ,t\left [ A_{ik} \right ] \right )$ 表示元组 $t$ 在属性列 $A$ 上诸分量的集合， $\bar{A}$ 则表示 $\left\{ A_{1},A_{2},\cdot \cdot \cdot ,A_{n} \right\}$ 中去掉 $\left\{ A_{i1},A_{i2},\cdot \cdot \cdot ,A_{ik} \right\}$ 后剩余的属性组

(3) $R$ 为n目关系， $S$ 为m目关系， $t_{r}\in R$ , $t_{s}\in S$ , $\overset{\frown }{t_{r}t_{s}}$ 称为元组的连接或元组的串接，前n个分量是 $R$ 的一个n元组，后m个分量为 $S$ 中的一个m元组

(4)给定一个关系 $R\left ( X,Z \right )$ , $X$ 和 $Z$ 为属性组。当 $t\left [ X \right ]=x$ 时， $x$ 在 $R$ 中的象集(images set)定义为 $Z_{x}=\left \{ t\left [ Z \right ]|t\in R,t\left [ X \right ]=x \right \}$ ,它表示 $R$ 中属性组 $X$ 的诸元素在 $Z$ 上分量的集合

1.选择(selection)

选择又称为限制(restriction)，它是在关系 $R$ 中选择满足给定条件的诸元组，记作 $\sigma _{F}(R)=\left \{ t|t\in R\wedge F(t)='TRUE' \right \}$ ，其中 $F$ 表示选择条件，它是一个逻辑表达式，取逻辑值“真”或“假”，逻辑表达式 $F$ 的基本形式为 $X_{1}\theta Y_{1}$ ,其中 $\theta$ 表示比较运算符， $X_{1},Y_{1}$ 等为属性名，或为常量，或为简单函数

选择操作是从行的角度来进行的运算

2.投影(projection)

关系 $R$ 上的投影是从 $R$ 中选择出若干属性列组成新的关系，记作 $\prod _{A}(R)=\left \{ t\left [ A \right ]|t\in R \right \}$ ,其中 $A$ 为 $R$ 的属性列。

投影操作是从列的角度来进行的运算

取消原关系中的属性列后，还可能取消某些元组，因为取消了某些重复列后，可能会出现重复行，投影操作应取消这些重复行，只保留一个

3.连接(join)

连接也称为 $\theta$ 连接。它是从两个关系的笛卡尔积中选取属性间满足一定条件的元组。记作 $R\underset{A\theta B}{\bowtie}S=\left \{ \overset{\frown }{t_{r}t_{s}} |t_{r}\in R\wedge t_{s}\in S\wedge t_{r}\left [ A \right ]\theta t_{s}\left [ B \right ]\right \}$ ,其中, $A$ 和 $B$ 分别为 $R$ 和 $S$ 上列数相等且可比的属性组， $\theta$ 是比较运算符。连接运算从 $R$ 和 $S$ 的笛卡尔积 $R\times S$ 中选取 $R$ 关系在 $A$ 属性组上的值与 $S$ 关系在 $B$ 属性组上的值满足比较关系 $\theta$ 的元组。

连接中两种最为长用且最为重要的两种连接，一是等值连接，一是自然连接

$\theta$ 为“=”的连接运算称为等值连接。它是从关系 $R$ 和 $S$ 的广义笛卡尔积中选取 $A$ 、 $B$ 属性值相等的那些元组，即等值连接为 $R\underset{A= B}{\bowtie}S=\left \{ \overset{\frown }{t_{r}t_{s}} |t_{r}\in R\wedge t_{s}\in S\wedge t_{r}\left [ A \right ]= t_{s}\left [ B \right ]\right \}$

自然连接是一种特殊的等值连接。它要求两个关系中进行比较的分量必须是同名的属性组，并且在结果中把重复的属性列去掉。即若 $R$ 和 $S$ 中具有相同的属性组 $B$ ， $U$ 为 $R$ 和 $S$ 的全体属性集合，则自然连接可记作 $R\bowtie S=\left \{ \overset{\frown }{t_{r}t_{s}} \left [ U-B \right ]|t_{r}\in R\wedge t_{s}\in S\wedge t_{r}\left [ B \right ]= t_{s}\left [ B \right ]\right \}$

两个关系 $R$ 和 $S$ 在做自然连接时，选择两个关系在公共属性上值相等的元组构成新的关系。此时，关系 $R$ 中某些元组可能在 $S$ 中不存在公共属性上值相等的元组，从而造成 $R$ 中这些元组在操作时被舍弃了，同样 $S$ 中某些元组也可能被舍弃。这些被舍弃的元组称为悬浮元组(dangling tuple)

如果把悬浮元组也保存在结果关系中，而在其他属性上填空值(NULL),那么这种连接就叫做外连接（outer join），记作 $R\Join S$ (两边都出头)，如果只保留左边关系 $R$ 中的悬浮元组叫就作左外连接（left outer join或left join），记作 $R\bowtie S$ (左边出头右边不出头)如果只保留左边关系 $S$ 中的悬浮元组叫就作右外连接(right outer join或right join)，记作 $R\bowtie S$ (左边不出头右边出头)

一般的连接操作是从行的角度进行运算，但自然连接还需要取消重复列，所以是同时从行和列的角度进行运算

4.除运算(division)

设关系 $R$ 除以关系 $S$ 的结果为关系 $T$ ，则 $T$ 中包含所有在 $R$ 而不再 $S$ 中的属性及其值，且 $T$ 的元组与 $S$ 的元组的所有组合都在 $R$ 中

用象集来定义除法，给定关系 $R(X,Y)$ 和 $S(Y,Z)$ ，其中 $X$ 、 $Y$ 、 $Z$ 为属性组。 $R$ 中的 $Y$ 与 $S$ 中的 $Y$ 可以有不同的属性名，但必须出自相同的域集。 $R$ 与 $S$ 的除运算得到一个新的关系 $P(X)$ ， $P$ 是 $R$ 中满足下列条件的元组在 $X$ 属性列上的投影：元组在 $X$ 上分量值 $x$ 的象集 $Y_{x}$ 包含 $S$ 在 $Y$ 上投影的集合。记作 $R\div S=\left \{ t_{r}\left [ X \right ]|t_{r}\in R\wedge \prod _{Y}(S)\subseteq Y_{x} \right \}$ ,其中 $Y_{x}$ 为 $x$ 在 $R$ 中的象集， $x=t_{r}\left [ X \right ]$ 。

除操作是同时从行和列角度进行运算

2.5 关系演算

按谓词变元的不同分为，以元组变量为谓词变元的元组关系演算，以元组变量的分量(即域变量)为谓词变元的域关系演算

2.5.1 元组关系演算语言ALPHA

ALPHA语言主要有GET、PUT、HOLD、UPDATE、DELETE、DROP 6条语句，语句的基本格式为操作语句工作空间名 (表达式): 操作条件,其中表达式用于指定语句的操作对象，它可以是关系名或属性名，一条语句可以同时操纵多个关系或多个属性。操作条件是一个逻辑表达式，用于将操作结果限定在满足条件得到元组中，操作条件可以为空。这一语言没有实际实现

1.检索操作

(1)简单检索(即不带条件的检索)

(2)限定的检索(即带条件的检索)

(3)带排序的检索

(4)指定返回元组的条数的检索

(5)用元组变量的检索

(6)用存在量词的检索

(7)带有多个关系的表达式的检索

(8)用全称量词的检索

(9)用两种量词的检索

(10)用蕴涵的检索

(11)聚集函数

关系演算中的聚集函数
函数名	功能
COUNT	对元组计数
TOTAL	求总和
MAX	求最大值
MIN	求最小值
AVG	求平均值

2.更新操作

(1)修改

修改操作用UPDATA语句实现，步骤为：

①首先用HOLD语句将要修改的元组从数据库中读到工作空间

②然后用宿主语言修改工作空间中元组的属性值

③最后用UPDATA语句将修改后的元组送回数据库中

不允许修改关系的主码，只能删除后在添加

(2)插入

插入操作用PUT语句实现，步骤为：

①首先用宿主语言在工作空间中建立新元组

②然后用PUT语句把该元组存入指定的关系中

(3)删除

删除操作用DELETE语句实现，步骤为：

①用HOLD语句把要删除的元组从数据库中读到工作空间中

②用DELETE语句删除该元组

2.5.2 元组关系演算

2.5.3 域关系演算语言QBE

1.检索操作

(1)简单查询

操作步骤为：

①用户提出要求

②屏幕显示空白格

③用户在最左边一栏输入关系名

④系统显示该关系的属性名

⑤用户在上面构造查询要求

P.是操作符，表示打印(Print)。QBE要求在示例元素下面一定要加上下划线。查询条件不需要加下划线

(2)条件

表示两个条件“与”的方法：①将两个条件写在同一行②把两个条件写在不同行上，但使用相同的示例元素值

表示条件“或”：两个条件写在不同的行上，并使用不同的示例元素值

表示“连接”的方法：相同的连接属性值把多个关系连接起来，连接属性的值在不同表中的值要相同

表示“非”：将逻辑非(符号为：┐)写在关系名的下面

(3)聚集函数

QBE中的聚集函数
函数名	功能
CNT	对元组计数
SUM	求总和
AVG	求平均值
MAX	求最大值
MIN	求最小值

(4)对查询结果排序

按某个属性值的升序，相应列中填入“AO.”，按降序则填“DO.”。如果按多列排序，则用“AO(i).”“DO(i).”来表示，i表示优先级，值越小优先级越高

2.更新操作

(1)修改操作

操作符为“U.”,不允许修改主码

(2)插入操作

操作符为“I.”

(3)删除操作

操作符为“D.”

第三章关系数据库标准语言SQL

3.1 SQL概述

3.1.1 SQL的特点

1.综合统一
2.高度非过程化
只要提出“做什么”，而无须指明“怎么做”，因此无须了解存取路径。存取路径的选择以及SQL的操作过程由系统自动完成
3.面向集合的操作方式
4.以同一种语法结构提供多种使用方式
SQL既是独立的语言，又是嵌入式语言
5.语言简洁，易学易用

3.1.2 SQL的基本概念

支持SQL的关系数据库管理系统同样支持关系数据库三级模式结构，如下图，外模式包扩若干视图和部分基本表，数据库模式包括若干基本表。内模式包括若干存储文件

基本表是本身独立存在的表，在关系数据库管理系统中一个关系就对应一个基本表。一个基本表或多个基本表对应一个存储文件，一个表可以带若干索引，索引也存放在存储文件中。
存储文件的逻辑结构组成了关系数据库的内模式。存储文件的物理结构对最终用户是透明的
视图是从一个或几个基本表导出的表。它本身不独立存放在数据库中，即数据库中只存放视图的定义而不存放视图对应的数据。这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。视图在概念上与基本表相同，用户可以在视图上再定义视图

3.2 数据定义

SQL的数据定义语句
操作对象	操作方式
操作对象	创建	删除	修改
模式	CREATE SCHEMA	DROP SCHEMA
表	CREATE TABLE	DROP TABLE	ALTER TABLE
视图	CREATE VIEW	DROP VIEW
索引	CREATE INDEX	DROP INDEX	ALTER INDEX

一个关系数据库管理系统的实列中可以建立多个数据库，一个数据库中可以建立多个模式，一个模式下通常包扩多个表、视图和索引等数据库对象

以上为学生-课程数据库的例子，之后的几章讲解以此为例，其中有下划线的列为该表主码

3.2.1 模式的定义与删除

1.定义模式

模式的定义语句如下：CERATE SCHEMA<模式名>AUTHORIZATION<用户名>
如果没有指定模式名则模式名隐含为用户名

用户可以在定义模式的同时在这个模式定义中进一步创建基本表、视图，定义授权。即：
CERATE SCHEMA<模式名>AUTHORIZATION<用户名>[<表定义子句>|<视图定义子句>|<授权定义子句>]

2.删除模式

删除模式语句如下：DROP SCHEMA<模式名><CASCADE|RESTRICT>，其中CASCADE和RESTRICT二者必选其一。CASCADE（级联）代表在删除模式的同时把该模式中所有的数据库对象全部删除，RESTRICT（限制）代表如果该模式中已经定义了下属的数据库对象(如表、视图等)，则拒绝该删除语句的执行，只有该模式中没有任何下属的对象时才能执行DROP SCHEMA语句

3.2.2 基本表的定义、删除与修改

1.定义基本表

创建了一个模式就建立了一个数据库的命名空间，一个框架。在这个空间中首先要定义的是该模式包含的数据库基本表。

基本格式为：CERATE TABLE<表名>(<列名><数据类型>列级完整性约束条件[]
[,<列名><数据类型>[列级完整性约束条件]]
﹒﹒﹒﹒﹒﹒
[,<表级完整性约束条件>])；
建表的同时还可以定义与该表有关的完整性约束条件，这些完整性约束条件被存入系统的数据字典中，由关系数据库管理系统自动检查该操作是否违背这些完整性约束条件

2.数据类型

SQL中域的概念用数据类型来实现，常见的数据类型有：

数据类型
数据类型	含义
CHAR(n),CHARACTER(n)	长度为n的定长字符串
VACHAR(n)，CHARACTERVARYING(n)	最大长度为n的变长字符串
CLOB	字符串大对象
BLOB	二进制大对象
INT,INTEGER	长整数(4字节)
SMALLINT	短整数(2字节)
BIGINT	大整数(8字节)
NUMERIC(p,d)	定点数，由p位数字(不包括符号、小数点)组成，小数点后面有d位数字
DECIMAL(p,d),DEC(p,d)	同NUMERIC
REAL	取决于机器精度的单精度浮点数
DOUBLE PRECISION	取决于机器精度的双精度浮点数
FLOAT(n)	可选精度的浮点数，精度至少为n位数字
BOOLEAN	逻辑布尔值
DATE	日期，包含年、月、日，格式为YYYY-MM-DD
TIME	时间，包含一日的时、分、秒，格式为HH:MM:SS
TIMESTAMP	时间戳类型
INTERVAL	时间间隔类型

3.模式与表

当定义基本表时有三种方法定义它所属的模式：
①在表名中明显的地给出模式名
②在创建模式语句中同时创建表
③设置所属的模式

当用户创建基本表时没有指定模式，系统根据搜索路径来确定该对象所属的模式。搜索路径包含一组模式列表，关系数据库管理系统会使用模式列表中第一个存在的模式做为数据库对象的模式名。若搜索路径中的模式名不存在，则系统会给出错误。

显示当前的搜索路径：SHOW search_path;
数据库管理员设置搜索路径：SET search_path TO "$user",PUBLIC;//首先搜索与用户名$user相同的模式名，如果不存在则使用PUBLIC模式

4.修改基本表

一般格式为：
ALTER TABLE<表名>
[ADD[COLUMN]<新列名><数据类型>[完整性约束]]
[ADD<表级完整性约束>]
[DROP[COLUMN]<列名>[CASCADE|RESTRICT]]
[DROP CONSTRAINT<完整性约束名>[RESTRICT|CASCADE]]
[ALTER COLUMN<列名><数据类型>];
先指出是哪一个表，再执行什么操作

5.删除基本表

DROP TABLE<表名>[RESTRICT|CASCADE],默认情况是RESTRICT

3.2.3 索引的建立与删除

常见索引包括顺序文件上的索引、B+树索引、散列索引、位图索引。位图索引是引用位向量记录索引中可能出现的值，每个位向量对应一个可能的值

1.建立索引

CERATE[UNION][CLUSTER] INDEX<索引名> ON <表名>(<列名>[<次序>][,<列名>[<次序>]]﹒﹒﹒)
索引可建立在该表的一列或多列上，各列名用逗号隔开，还可以用次序指定索引值的排列次序，次序可选ASC(升序)和DESC(降序)，默认ASC。

UNION表明此索引的每一个索引值只对应唯一的数据记录

CLUSTER表示要建立的索引是聚簇索引

2.修改索引

索引重命名ALTER INDEX<旧索引名>RENAME TO<新索引名>

3.删除索引

DROP INDEX<索引名>

3.2.4 数据字典

是数据库管理系统内部的一组系统表，它记录了数据库中所有的定义信息，包括关系模式定义、视图定义、索引定义、完整性约束定义、各类用户对数据库的操作权限、统计信息等。关系数据库管理系统在执行SQL的数据定义语言时，实际上就是在更新数据字典中的相应信息。在进行查询优化和查询处理时，数据字典中的信息是其重要依据

3.3 数据查询

其一般格式为:SELECT [ALL|DISTINCT]<目标列表达式>[,<目标列表达式>] • • •
                               FORM<表名或视图名>[,<表名或视图名> • • • ] | (<SELECT 语句>)[AS]<别名>
                               [WHERE<条件表达式>]
                               [GROUP BY<列名1>[HAVING<条件表达式>]]
                               [ORDER BY<列名2>[ASC|DESC]];

整个SELECT语句的含义是，根据WHERE子句的条件表达式从FROM子句指定的基本表、视图或派生表中找出满足条件的元组，再按SELECT子句中的目标列表达式选出元组中的属性值形成结果表。
如果有GROUP BY语句，则将结果按<列名1>的值进行分组，该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。如果GROUP BY子句有HAVING短语，则只有满足指定条件的组才会予以输出。
如果有ORDER BY子句，则结果表还要按<列名2>的值升序或降序排列

3.3.1 单表查询

针对一个表进行查询

1.选择表中的若干项

(1)查询指定列

在SELECT的<目标列表达式>中指定要查询的属性列

(2)查询全部列

一是在SELECT的<目标列表达式>中列出所有列名，这样就按输入列名的顺序排列。二是将<目标列表达式>简单定为“*”，这样就以默认的顺序输出

(3)查询经过计算的值

在SELECT的<目标列表达式>不仅可以是表中的属性，也可以是表达式(算术表达式、字符串常量、函数)

2.选择表中的若干元组

(1)消除取值重复的行

两个本来并不完全相同的元组在投影到指定的列上后，可能会变成相同的行，可以用DISTINCT消除它们，默认为ALL保留全部

(2)查询满足条件的元组
        通过WHERE子句实现
①比较大小
         $=,>,<,>=,<=,!=,<>,!>,!<$ ； $!=$ 和 $< >$ 均表示不等于
②确定范围
        BETWEEN • • • AND • • •和NOT BETWEEN • • • AND • • •可以用来查找属性值在指定范围内的元组，BETWEEN后是范围的下限，AND后是范围的上限
③确定集合
谓词IN可以用来查找属性值属于指定集合的元素
④字符匹配
谓词LIKE可以用来进行字符串的匹配，一般语法格式如下：
        [NOT] LIKE'<匹配串>' [ESCAPE'<换码字符>']，其含义是查找指定的属性列值与<匹配串>相匹配的元组。<匹配串>可以是一个完整的字符串，也可以含有通配符“%”（百分号）和“_”（下划线）“%”代表任意长度的字符串,“_”代表任意单个字符。
若LIKE后匹配串不含%或_，可以用“=”代替LIKE，用“!=”或“<>”取代NOT LIKE；ASCALL码的中文一个汉字占两个“_”；ESCAPE后的换码字符相当于转义字符，'<换码字符>'不再有其原先的意义，使得匹配串中紧跟在换码字符后的字符被当作普通字符对待；
⑤涉及空值的查询
        用IS NULL（是空值）或IS NOT NULL（不是空值）
⑥多重条件查询
逻辑运算符AND和OR连接多个查询条件，优先级AND高于OR，但可以用括号改变

常用的查询条件
查询条件	谓词
比较	$=,>,<,>=,<=,!=,<>,!>,!<;NOT+$ 上述比较符
确定范围	BETWEEN AND,NOT BETWEEN AND
确定集合	IN,NOT IN
字符匹配	LIKE,NOT LIKE
空值	IS NULL,IS NOT NULL
多重条件(逻辑运算)	AND,OR,NOT

3.ORDER BY语句

用户可以使用ORDER BY子句对查询结果按照一个或多个属性列的升序(ASC)或降序(DESC)排列，默认是ASC。对于空值，排序显示的次序由具体系统实现来确定

4.聚集函数

COUNT(*)	统计元组个数
COUNT([DISTINCT \| ALL]<列名>)	统计一列中值的个数
SUM([DISTINCT \| ALL]<列名>)	计算一列值的总和(此列必须是数值型)
AVG([DISTINCT \| ALL]<列名>)	计算一列值的平均值(此列必须是数值型)
MAX([DISTINCT \| ALL]<列名>)	求一列值中的最大值
MIN([DISTINCT \| ALL]<列名>)	求一列值中的最小值

当聚集函数遇到空值时，除了COUNT(*)外，其余都跳过空值而只处理非空值。聚集函数只能用在SELECT子句和GROUP BY中的HAVING子句，WHERE子句中是不能用聚集函数作为条件表达式的

5.GROUP BY语句

该子句将查询结果按某一列或多列的值分组，值相等的为一组。对查询结果分组的目的是为了细化聚集函数的作用对象。如果未对查询结果分组，聚集函数将作用于整个查询结果，分组后聚集函数将作用于每一组，即每一组都有一个函数值

3.3.2 连接查询

同时涉及两个以上的表查询

1.等值与非等值连接查询

一般格式：[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>，其中比较运算符主要有 $=,>,<,>=,<=,!=,<>$ 等。此外连接谓词还可以使用下面形式：
[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>，当连接运算符为“=”号时称为等值连接，其他的称为非等值连接。连接谓词中的列名称为连接字段，列名必须可比但不必相同。若在等值连接中把目标列中重复的属性列去掉则为自然连接。一条SQL语句可以同时完成选择和连接操作，这是WHERE子句由连接谓词和选择谓词组成的复合条件

例：SELECT Student.Sno,Sname
FROM Student,SC
WHERE Student.Sno=SC.Sno AND //连接谓词
SC.Cno='2' AND SC.Grade>90; //其他限定条件

2.自身连接

连接操作不仅可以在两个表之间进行，也可以一个表和它自己进行连接，称为表的自身连接。此时可为那一个表取两个别名，一个是FIREST一个是SECOND，进行操作

例：SELECT FIRST.cno,SECOND.cno

FROM Course FIRST,Course SECOND

WHERE FIRST.cpno = SECONT.cno;//查询某课程的先行课

3.外连接

仍把悬浮元组保留在结果关系中，用NULL取代，称外连接。左外连接和右外连接等概念可见第2.4.2章

例： SELECT Student.sno,Sname,Ssex,Sage,Sdept,Cno,Grade

FROM Student LEFT OUTER JOIN SC ON(Student.Sno = SC.Sno)

4.多表连接

两个以上的表进行连接

3.3.3 嵌套查询

一个SELECT-FROM-WHERE语句称为一个查询块，将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短句的条件中的查询称为嵌套查询，第一个查询块称为父查询或外层查询，第二个查询块称为子查询或内层查询。SQL允许多重嵌套，但ORDER BY语句只能对最终的查询结果进行排序

1.带有IN谓词的子查询

子查询的查询条件不依赖于父查询，称为不相关子查询。有些嵌套查询可以用连接查询代替，某些不能代替。如果子查询的查询条件依赖于父查询，这类子查询称为相关子查询。相关子查询可能的一种求解方法就是从外层查询取出一个元组带入内层查询返回一个结果，然后反复执行此过程，直至外层循环的可取元组取完，最后返回结果

2.带有比较运算符的子查询

3.带有ANY(SOME)或ALL谓词的子查询

使用ANY(某些是SOME)或ALL时必须同时使用比较运算符。ANY表示查询结果中的某个值，ALL表示查询结果的所有值。通常使用聚集函数实现的效率相较于ANY或ALL更高

ANY(SOME)、ALL谓词与聚集函数、IN谓词的等价关系转换
	=	<>或!=	<	<=	>	>=
ANY	IN	--	<MAX	<=MAX	>MIN	>=MIN
ALL	--	NOT IN	<MIN	<=MIN	>MAX	>=MAX

4.带有EXISTS谓词的子查询

EXISTS代表存在量词 $\exists$ 。带有EXISTS谓词的子查询结果不返回任何数据，只产生逻辑真值“true”或“false”。与EXISTS相对应的为NOT EXISTS，使用存在量词EXISTS后，若内层查询结果非空，则外层的WHERE子句返回真值（输出存在的），否则返回假值；使用存在量词NOT EXISTS后，若内层查询结果为空，则外层的WHERE子句返回真值（输出不存在的），否则返回假值。SQL不提供全称量词，但可用存在量词代替

3.3.4 集合查询

集合操作主要包括并操作(NUION)、交操作(INTERSECT)和差操作(EXCEPT)

3.3.5 基于派生表的查询

子查询不仅可以出现在WHERE子句中，还可以出现在FROM子句中，这时子句查询生成的临时派生表成为主查询的查询对象。要想派生查询就要通过FROM生成派生表，且必须指明派生表的别名(用AS指明，AS可省)

例查询所有选修了1号课程的学生姓名：

        SELECT Sname
        FROM Student,(SELECT Sno FROM SC WHERE Cno='1') AS SC1
        WHERE Student.Sno=SC1.Sno;

3.3.6 SELECT语句的一般格式

1.目标列表达式的可选格式

(1)*
(2)<表名>.*
(3)COUNT([DISTINCT|ALL]*)
(4)[<表名>.]<属性列名表达式>[,[<表名>.]<属性列名表达式>]

2.聚集函数的一般格式

3.WHERE子句的条件表达式的可选格式

3.4 数据更新

3.4.1 插入数据

1.插入元组

        语句格式为：INSERT
        INTO<表名>[<属性列1>[,<属性列2>] • • • ]
          VALUES(<常量1>[,<常量2>] • • • )

其中新元组的属性列1的值为常量1，属性列2的值为常量2，以此类推，INTO中没有出现的属性列赋空值。若在表定义时说明了NOT　NULL的属性列不能取空值，就不能取空值，否则会出错。VALUES中的字符串常量要用单引号括起来

2.插入子查询结果

子查询不仅可以嵌套在SELECT语句中用以构造父查询的条件，也可以嵌套在INSTER语句中用以生成要插入的批量数据。语句格式为: INSTER
INTO<表名>[(<属性列1>[,<属性列2> • • • ])]
子查询;

例：CREATE TABLE Dept_age

(Sdept CHAR(15)

Avg_age SMALLINT);//创建一个新表，一列为系名，一列为平均年龄

INSERT

INTO Dept_age(Sdept,Avg_age)

SELECT Sdept,AVG(Sage)//子查询按系分组求平均年龄，再插入新表中

FROM Student

GROUP BY Sdept;

3.4.2 修改数据

语句的一般格式为：UPDATE<表名>
SET<列名>=<表达式>[,<列名>=<表达式>] • • •
[WHERE<条件>];

修改指定表中满足WHERE子句条件的元组，其中SET语句给出<表达式>的值用于取代相应的属性列值

1.修改某一个元组的值

2.修改多个元组的值

3.带子查询的修改语句

3.4.3 删除数据

一般格式：DELETE
FROM<表名>
[WHERE<条件>];

1.删除某一个元组的值

2.删除多个元组的值

3.带子查询的删除语句

3.5 空值的处理

取空值的几种情况：
①该属性应该有一个值，但目前不知道它的值
②该属性不应该有值
③由于某种原因不便于填写

1.空值的产生

2.空值的判断

用IS NOT NULL和IS NULL判断

3.空值的约束条件

属性定义中有NOT　NULL约束条件的不能取空值，加了UNIQUE限制的属性不能取空值，码属性不能取空值

4.空值的算术运算、比较运算和逻辑运算

空值与另一个值(包括空值)的算术运算的结果为空值，空值与另一个值的(包括空值)的比较运算结果为UNKNOWN。有了UNKNOWN后，传统的逻辑运算符中二值逻辑就扩展为三值逻辑。

逻辑运算符真值表
x y	x AND y	x OR y	NOT x
T T	T	T	F
T U	U	T	F
T F	F	T	F
U T	U	T	U
U U	U	U	U
U F	F	U	U
F T	F	T	T
F U	F	U	T
F F	F	F	T

3.6 视图

3.6.1 定义视图

1.建立视图

        一般格式为：CREATE VIEW<视图名>[(<列名>[,<列名>] • • •)]
          AS<子查询>
          [WITH CHECK OPTION];

子查询可以是任意的SELECT语句，是否可有ORDER BY和DISTINCT短句看具体实现。
WITH CHECK OPTION表示对视图进行UPDAT、INSERT和DELETE操作时要保证更新、插入和删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）
组成视图的属性列名或者全部给出或者全部省略没有第三种选项。如果省略了视图的各个属性列名，则隐含该视图由子查询中SELECT子句目标列中的诸字段组成。在以下情况必须明确指定组成视图的所有列名：①某个目标列不是单纯的属性名，而是聚集函数或列表达式
②多表连接时选出了几个同名列作为视图的字段
③需要在视图中为某个列启用新的更合适的名字

关系数据库管理系统执行CREATE VIEW语句的结果只是把视图的定义存入数据字典，并不执行SELECT语句。只是在查询视图的时候才按视图的定义从基本表中查出数据。

若一个视图是从单个基本表导出的，并且只是去掉了某些行和某些列，但保留了主码，则称这类视图为行列子集视图

视图不仅可以建立在一张或多张基本表上，也可以建立在多张视图上，或建立在基本表和视图上。

由各种计算派生出来的数据一般是不存放在基本表中，由于视图中的数据并不实际存储，所以可以在定义视图是根据实际需要设置一些派生属性列，这些派生属性列称为虚拟列。带虚拟列的视图也称为带表达式的视图

可以用带有聚集函数和GROUP BY子句的查询来定义视图，这种视图称为分组视图

2.删除视图

一般格式为：DROP VIEW<视图名>[CASCADE];默认不是级联删除，加CASCADE后会删除该视图及由其导出的所有视图一起删除。删除基本表后，由其导出的视图无法使用但其定义仍然在数据字典中，要想删除需要显示的调用DROP VIEW语句。

3.6.2 查询视图

关系数据库管理系统执行对视图的查询时，首先进行有效性检查，检查查询中涉及的视图、基本表等是否存在。如果存在则从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，然后执行修正了的查询。这一转换过程称为视图消解

目前多数数据库系统对行列子集视图的查询均能进行正确的转换。但对非行列子集视图的查询就不一定能做转换了。定义视图并查询视图与基于派生表的查询是有区别的，视图一旦定义，就存在数据字典中，之后的所有查询均可以直接引用该视图，而派生表只是在语句的执行时临时定义的，语句执行完毕后该定义即被删除

3.6.3 更新视图

由于视图不是实际存在的虚表，对视图的更新最终要转换为对基本表的更新。

在关系数据库中并不是所有的视图都是可以更新的，因为有些视图的更新并不能唯一地有意义地转换成对相应基本表的更新。一般的，行列子视图都是可以更新的。目前，不同系统对视图更新的规定不尽相同。

不可更新与不允许更新的视图是两个不同的概念

3.6.4 视图的作用

1.视图能够简化用户的操作

2.视图使用户能以多种角度看待同一数据

3.视图对重构数据库提供一定程度的逻辑独立性

数据的物理独立性是指用户的应用程序不依赖于数据库的物理结构。数据的逻辑独立性是指当数据库重构时，如增加新的关系或对原有关系增加新的字段等，用户的应用程序不会受影响

4.视图能够对机密数据提供安全保护

5.适当利用视图可以更清晰地表达查询

第四章数据库安全性

4.1 数据库安全性概述

数据库的安全性是指保护数据库以防止不合法使用所造成的数据泄露、更改或破坏。

4.1.1 数据库的不安全因素

1.非授权用户对数据库的恶意存取和破坏

2.数据库中重要或敏感的数据被泄露

3.安全环境的脆弱性

4.1.2 安全标准简介

两个标准TCSEC和通用准则CC(common criteria)，目前CC已经基本取代了TCSESC。

TCSEC/TDI(即紫皮书)安全级别划分
安全级别	定义
A1	验证设计
B3	安全域
B2	结构化保护
B1	标记安全保护
C2	受控的存取保护
C1	自主安全保护
D	最小保护

D、C1、C2、B1、B2、B3、A1依次按系统可靠或可信程度逐渐增高

CC评估保证级(EAL)的划分
评估保证级	定义	TCSEC安全级别(近似相当)
ELA1	功能测试
ELA2	结构测试	C1
ELA3	系统地测试和检查	C2
ELA4	系统地设计、测试和复查	B1
ELA5	半形式化设计和测试	B2
ELA6	半形式化验证的设计和测试	B3
ELA7	形式化验证的设计和测试	A1

从EL1到ELA7按保证程度逐渐升高

4.2 安全控制

4.2.1 用户身份鉴别

1.静态口令鉴别：如密码

2.动态口令鉴别：如短信密码和动态令牌方式

3.生物特征识别：如虹膜、指纹和掌纹

4.智能卡识别：智能卡是一种不可复制的硬件，内置集成电路的芯片，具有硬件加密功能。一般采取PIN和智能卡结合的方式。

4.2.2 存取控制

存取控制机制主要包括两部分：1.定义用户权限，并将用户权限登记到数据字典中
2.合法权限检查

自主存取控制(Discretionary Access Control,DAC)方法中，用户对于不同的数据库对象有不同的存取权限，不同的用户对同一对象也有不同的权限，而且用户还可将其拥有的存取权限转授给其他用户。相对灵活

强制控制存取(Mandatory Access Control,MAC)方法中，每一个数据库对象被标以一定的密级，每一个用户也被授予某一级别的许可证。对于任意一个对象，只要具有合法许可证的用户才可以存取。相对严格

4.2.3 自主存取控制方法

用户权限是由两个要素构成的：数据库对象和操作类型。定义一个用户的存取权限就是要定义这个用户可以在哪些数据库上进行哪些类型的操作。将定义存取权限称为授权。

关系数据库系统中的存取权限
对象类型	对象	操作类型
数据库模式	模式	CREATE SCHEMA
	基本表	CREATE TABLE,ALTER TABLE
	视图	CREATE VIEW
	索引	CREATE INDEX
数据	基本表和视图	SELECT,INSERT,UPDATE,DELETE,REFERENCES,ALL PRIVILEGES
数据	属性列	SELECT,INSERT,UPDATE,REFERENCES,ALL PRIVILEGES

4.2.4 授权：授予与收回

1.GRANT

一般格式：GRANT<权限>[,<权限>] • • •
ON<对象类型><对象名>[,<对象类型><对象名>] • • •
TO<用户>[,<用户>] • • •
[WITH GRANT OPTION];

语义：将指定操作对象的指定操作权限授予指定的用户。可由数据库管理员、数据库对象的创建者或已经有该权限的用户授予给一个或多个用户

如果指定了WITH GRANT OPTION子句，则用户则获得某种权限的用户还可以把这种权限再授予其他用户。如若未指定WITH GRANT OPTION语句，则不可传播该权限。SQL不允许循环授权。

2.REVOKE

        一般格式为：REVOKE<权限>[,<权限>]• • •
          ON<对象类型><对象名>[,<对象类型><对象名>]• • •
          FROM<用户>[,<用户>]• • •[CASCADE|RESTRICT];

级联收回只收回该用户和其他直接或间接从要收回的用户处获得权限的用户权限，若还从其他地方获得了权限，则还拥有权限。

3.创建数据库模式的权限

一般格式如下：CREATE USER<username>[WITH] [DBA|RESOURCE|CONNECT]
只有系统的超级用户才有权创建一个新的数据库用户，新创建的数据库用户有三种权限：CONNECT,RESOURCE和DBA

权限与可执行的操作对照表
拥有的权限	可否执行的操作
拥有的权限	CREATE USER	CAEATE SCHEMA	CREATE TABLE	登录数据库，执行数据查询和操纵
DBA	可以	可以	可以	可以
RESOURCE	不可以	不可以	可以	可以
CONNECT	不可以	不可以	不可以	可以，但必须拥有相应权限

4.2.5 数据库角色

数据库角色是被命名的一组与数据库操作相关的权限，角色是权限的集合

1.角色的创建

SQL语句为：CREATE ROLE<角色名>

2.给角色授权

GRANT<权限>[,<权限>] • • •
ON<对象类型>对象名
TO<角色>[,<角色>] • • •

3.将一个角色授予其他的角色或用户

GARNT<角色1>[,<角色2>]• • •
TO<角色3>[,<用户1>]• • •
[WITH ADMIN OPTION]

4.角色权限的收回

REVOKE<权限>[,<权限>]• • •
ON<对象类型><对象名>
FROM<角色>[,<角色>]• • •

4.2.6 强制存取控制方法

所谓的强制存取控制是指系统为保证更高程度的安全性，按照TID/TCSEC标准中安全策略的要求所采取的强制存取检查手段。此时，数据库管理系统所管理的全部实体分为两部分实体和客体。主体是系统中的活动实体，既包括数据库管理系统所管理的实际用户，也包括用户的各进程。客体是系统中的被动实体，是受主体操纵的，包括文件、基本表、索引表、视图等。对于主题和客体，数据库管理系统为它们每个实例指派一个敏感度标记，敏感度标记被分为若干级别，例如绝密(Top Secret,TS)、机密(Secret,S)、可信(Confidential,C)、公开(Public,P).密级的次序为TS>=S>=C>=P,主体的敏感度标记称为许可证级别，客体的敏感度标记称为密级。系统要求用户对任何客体的存取必须遵循以下规则：
①仅当主体的许可证级别大于或等于客体的密级时，该主体才可以读取相应的客体
②仅当主体的许可证级别小于或等于客体的密级时，该主体才可以写相应的客体

主体可以赋给其写的客体以高于自身许可证级别的密级，也就是说一旦该主体录入数据后就无法在继续查看数据。如果违反了②就可能把数据的密级从高流向低，造成数据的泄露

强制存取控制是对数据本身进行密级标记，无论数据如何复制，标记与数据是一个不可分割的整体，只有符合密级标记要求的用户才可以操纵数据，从而提供了更高级别的安全性。

4.3 视图机制

为不同的用户定义不同的视图，把数据对象限制在一定范围内，也就是说，通过视图机制把要保密的数据对无权存取的用户隐藏起来，从而自动对数据提供一定程度的安全保护。

4.4 审计、数据加密以及其他安全性保护

4.4.1 审计

审计功能将用户对数据库的所有操作自动记录下来放入审计日志中。审计员可以利用审计日志监控数据库中的各种行为，重现导致数据库现有状况的一系列事件，找出非法存取数据的人、时间和内容等。

1.审计事件

①服务器事件：审计数据库服务器发生的事件，包含数据库服务器的启动、停止、数据库服务器配置文件的重新加载

②系统权限：对系统拥有的结构或模式对象进行操作的审计，要求该操作的权限是通过系统权限获得的

③语句事件：对SQL语句，如DDL、DML、DQL(Date Query Language,数据查询语言)及DCL语句的审计

④模式对象事件：对特定模式对象上进行的SELECT或DML操作的审计。模式对象包括表、视图、存储过程、函数等。模式对象不包括依附于表的索引、约束、触发器、分区表等。

2.审计功能

①基本功能，提供多种审计查阅方式：基本的、可选的、有限的等

②提供多套审计规则

③提供审计分析和报表功能

④审计日志管理功能

⑤系统提供查询审计设置及审计信息的专门视图

3.AUDIT和NOTAUDIT语句

对修改SC表结构修改SC表数据的操作进行审计

AUDIT ALTEER，UPDATE
ON SC;

取消对SC表的一切审计

NOAUDIT ALTER，UPDATE
ON SC;

审计一般分为用户级审计和系统级审计。系统级审计只能由数据库管理员设置，用以监测成功或失败的登陆要求、检测授权和收回操作以及其他数据库级权限下的操作。用户级审计是任何用户都可以设置的审计，主要是针对自己创建的数据库或视图进行审计

4.4.2 数据加密

加密的基本思想是根据一定的算法将原始数据——明文(plain text)变换为不可直接识别的格式——密文(cipher text)。数据加密主要包括存储加密和传输加密

1.存储加密

分为透明加密和非透明加密。透明存储加密是内核级加密保护方式，对用户完全透明；非透明加密方式则是通过多个加密函数实现的

2.传输加密

常用的传输加密方式如链路加密和端到端加密。其中链路加密对传输数据在链路层进行加密，它的传输信息由报头和报文组成，前者是路由选择信息，后者是传送的数据信息，这种方式对报文和报头都进行加密。端到端的加密对传输数据在发送端加密，接收端解密，只加密报文不加密报头。

4.4.3 其他安全性保护

①推理控制（inference control）：推理控制处理的是强制存取控制未解决的问题，例如用列的函数依赖关系，用户能够从低安全等级信息推出其无权访问的高安全等级信息，进而导致信息泄露。数据库推理控制机制用来避免用户利用其能够访问的数据推知更高密集的数据，即用户看利用其被允许的多次查询结果，结合相关的领域背景知识和数据之间的约束，推导出其不能访问的数据

②隐蔽信道（covert channal）：处理的也是强制存取控制未解决的问题。利用未被强制存取控制的SQL执行后反馈的信息进行间接信息传递，例insert语句对unique属性列写入重复值，则系统会报错且操作失败，那么针对unique约束列，高安全等级用户（发送者）可先向该列插入（或不插入）数据，而低安全等级用户（接收者）向该列插入先相同的数据，两者协商插入成功与失败的含义，从而导致高安全等级敏感信息泄露

③数据隐私（data privacy）：控制不愿被他人知道或他人不便知道的个人数据的能力

第五章数据库完整性

1.数据库的完整性是指数据的正确性和相容性

数据的正确性是指数据是符合现实世界语义，反映了当前实际状况的

数据的相容性是指数据库同一对象在不同关系表中的数据是符合逻辑的

例如，学生的学号必须唯一、性别只能是男或女等

2.数据的完整性与安全性是两个不同的概念

(1)数据的完整性

防止数据库中存在不符合语义的数据，也就是防止数据库中存在不正确的数据

防范对象：不合语义的、不正确的数据

(2)数据的安全性

保护数据库，防止恶意的破坏和非法的存取

防范对象：非法用户和非法操作

3.为维护数据库的完整性，数据库管理系统必须：

(1)提供定义完整性约束条件的机制

完整性约束条件也称为完整性规则，是数据库中的数据必须满足的语义约束条件

SQL标准使用了一系列概念来描述完整性，包括关系模型的实体完整性、参照完整性和用户定义完整性

这些完整性一般由SQL的数据定义语言语句来实现

(2)提供完整性检查的方法

数据库管理系统中检查数据是否满足完整性约束条件的机制称为完整性检查

一般在INSERT、UPDATE、DELETE语句执行后开始检查，也可以在事务提交时检查

(3)违约处理

数据库管理系统若发现用户的操作违背了完整性约束条件，就采取一定的动作

①拒绝（NO ACTION）执行该操作

②级连（CASCADE）执行其他操作

5.1 实体完整性

5.1.1 定义实体完整性

关系模型的实体完整性在CREATE TABLE中用PRIMARY KEY定义

单属性构成的码有两种说明方法

①定义为列级约束条件

CREATE TABLE Student

(Sno CHAR(9) PRIMARY KEY, /*在列级定义主码*/

Sname CHAR(20) NOT NULL,

Ssex CHAR(2),

Sage SMALLINT,

Sdept CHAR(20)

);

②定义为表级约束条件

CREATE TABLE Student

(Sno CHAR(9),

Sname CHAR(20) NOT NULL,

Ssex CHAR(2),

Sage SMALLINT,

Sdept CHAR(20),

PRIMARY KEY(Sno) /*在表级定义主码*/

);

对多个属性构成的码只有一种说明方法

①定义为表级约束条件

CREATE TABLE Student

(Sno CHAR(9) PRIMARY KEY,

Sname CHAR(20) NOT NULL,

Ssex CHAR(2),

Sage SMALLINT,

Sdept CHAR(20),

PRIMARY KEY(Sno，Cno) /*主码包含多个属性列只能定义为表级约束条件*/

);

5.1.2 实体完整性检查和违约处理

插入或对主码列进行更新操作时，关系数据库管理系统按照实体完整性规则自动进行检查。包括：

①检查主码值是否唯一，如果不唯一则拒绝插入或修改

②检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改

检查记录中主码值是否唯一的一种方法是全表扫描，缺点是比较费时。改进方法是建立一个主码的索引，可以采用B+树的索引结构进行查询。

5.2 参照完整性

5.2.1 定义参照完整性

关系模型的参照完整性在CREATE TABLE中用FOREIGN KEY短语定义哪些列为外码。用REFERENCES短语指明这些外码参照哪些表的主码。只能定义在表级上

例：CREATE TABLE SC

(Sno CHAR(9) NOT NULL,

Cno CHAR(4) NOT NULL,

Grade SMALLINT,

PRIMARY KEY(Sno，Cno), /*在表级定义实体完整性*/

FOREIGN KEY (Sno) REFERENCES Student(Sno) /*在表级定义参照完整性*/

ON DELETE CASCADE /*当删除Student表中的元组时，级联删除SC表中相应的元组*/

ON UODATE CASCADE, /*当更新Student表中的元组时，级联更新SC表中相应的元组*/

FOREIGN KEY (Cno) REFERENCES Course(Cno) /*在表级定义参照完整性*/

ON DELETE NO ACTON /*当删除Course表中的元组造成与表SC不一致时，拒绝删除*/

ON UODATE CASCADE, /*当更新Course表中的元组时，级联更新SC表中相应的元组*/

);

5.2.2 参照完整性检查和违约处理

1.一个参照完整性将两个表中的相应元组联系起来，对被参照表和参照表进行增删改操作时有可能破坏参照完整性，必须进行检查。2.参照完整性违约处理

(1)拒绝（NO ACTION）执行

不允许该操作执行。该策略一般设置为默认策略

(2)级联（CASCADE）操作

当删除或修改被参照表（Student）的一个元组造成了与参照表（SC）的不一致，则删除或修改参照表中的所有造成不一致的元组

(3)设置为空值（SET-NULL）

当删除或修改被参照表的一个元组时造成了不一致，则将参照表中的所有造成不一致的元组的对应属性设置为空值。

对于参照完整性，除了应该定义外码，还应定义外码列是否允许空值。一般系统选用默认的策略即拒绝执行来处理违反了参照完整性的情况，其他的策略需要显示地说明参照完整性的违约处理方式

5.3 用户定义的完整性

用户定义的完整性是：针对某一具体应用的数据必须满足的语义要求。关系数据库管理系统提供了定义和检验用户定义完整性的机制，不必由应用程序承担

5.3.1 属性上的约束条件

1.属性上约束条件的定义

在CREATE TABLE时定义属性上的约束条件：

(1)列值非空（NOT NULL）

(2)列值唯一（UNIQUE）

(3)检查列值是否满足一个条件表达式（CHECK）

2.属性上的约束条件检查和违约处理

插入元组或修改属性的值时，关系数据库管理系统检查属性上的约束条件是否被满足,如果不满足则操作被拒绝执行

5.3.2 元组上的约束条件

1.元组上约束条件的定义

在CREATE TABLE时可以用CHECK短语定义元组上的约束条件，即元组级的限制，同属性值限制相比，元组级的限制可以设置不同属性之间的取值的相互约束条件

2.属性上的约束条件检查和违约处理

插入元组或修改属性的值时，关系数据库管理系统检查元组上的约束条件是否被满足,如果不满足则操作被拒绝执行

5.4 完整性约束命名子句

1.完整性约束命名子句

在CREATE TABLE语句中定义，为:CONSTRAINT <完整性约束条件名><完整性约束条件>

<完整性约束条件>包括NOT NULL、UNIQUE、PRIMARY KEY短语、FOREIGN KEY短语、CHECK短语等

2.修改表中的完整性限制

在ALTER TABLE语句中修改表的完整性限制，如：
①删除：DROP CONSTRAINT<完整性约束条件名>
②添加：ADD CONSTRAINT<完整性约束条件名>

5.5 域中的完整性限制

用CREATE DOMAIN语句建立一个域及该域应该满足的完整性约束条件，然后就可以用域来定义属性。当域上的完整性约束条件改变时只要修改域的定义即可，而不必一一修改域上的各个属性

5.6 断言

SQL中，可以使用CREATE ASSERTION语句，通过声明性断言来指定更具一般性的约束。

可以定义涉及多个表的或聚集操作的比较复杂的完整性约束。

断言创建以后，任何对断言中所涉及的关系的操作都会触发关系数据库管理系统对断言的检查，任何使断言不为真值的操作都会被拒绝执行

1.创建断言的语句格式

CREATE ASSERTION<断言名><CHECK子句>

每个断言都被赋一个名字，<CHECK 子句>中的约束条件与WHERE子句的条件表达式类似。

2.删除断言的语句格式

DROP ASSERTION<断言名>

如果断言很复杂，则系统在检测和维护断言上的开销较高

例：限制每个学期每门课程最多60名学生选修

ALTER TABLE SC ADD TERM DATE; //先修改SC表，添加学期TEAM属性，类型是DATA

CREATE ASSERTION ASSE_SC_CUNM //添加名为ASSE_SC_CUNM的断言

CHEK(60>=ALL(select count(*) from SC group by cno,TERM ));

5.7 触发器

触发器（Trigger）是用户定义在关系表上的一类由事件驱动的特殊过程，触发器保存在数据库服务器中，任何用户对表的增、删、改操作均由服务器自动激活相应的触发器，触发器可以实施更为复杂的检查和操作，具有更精细和更强大的数据控制能力

5.7.1 定义触发器

1.CREATE TRIGGER语法格式

CREATE TRIGGER <触发器名> /*每当触发事件发生时，该触发器被执行*/

{BEFORE | AFTER} <触发事件> ON <表名>

/*指明触发器激活的时间是在执行触发事件前或后*/

REFERENCING NEW|OLD ROW AS<变量> /*REFERENCING指出引用的变量*/

FOR EACH {ROW | STATEMENT} /*定义触发器的类型，指明动作体执行的频率*/

[WHEN <触发条件>]<触发动作体> /*仅当触发条件为真时才执行触发动作体*/

触发器又叫做事件-条件-动作（event-condition-action）规则。

当特定的系统事件发生时，对规则的条件进行检查，如果条件成立则执行规则中的动作，否则不执行该动作。规则中的动作体可以很复杂，通常是一段SQL存储过程。

2.语法说明

(1)表的拥有者才可以在表上创建一定量的触发器

(2)触发器名

触发器名可以包含模式名，也可以不包含模式名。同一模式下，触发器名必须是唯一的。触发器名和表名必须在同一模式下

(3)表名

触发器只能定义在基本表上，不能定义在视图上。当基本表的数据发生变化时，将激活定义在该表上相应触发事件的触发器，因此该表也称为触发器的目标表

(4)触发事件

触发事件可以是INSERT、DELETE或UPDATE,也可以是这几个事件的组合,还可以UPDATE OF<触发列，• • • >，即进一步指明修改哪些列时激活触发器。AFTER/BEFORE是触发的时机，AFTER表示在触发事件的操作执行之后激活触发器，BEFORE表示在触发事件的操作执行之前激活触发器

(5)触发器类型

行级触发器（FOR EACH ROW）

语句级触发器（FOR EACH STATEMENT）

(6)触发条件

触发器被激活时，只有当触发条件为真时触发动作体才执行;否则触发动作体不执行。如果省略WHEN触发条件，则触发动作体在触发器激活后立即执行

(7)触发动作体

触发动作体可以是一个匿名PL/SQL过程块也可以是对已创建存储过程的调用，如果是行级触发器，用户都可以在过程体中使用NEW和OLD引用事件UPDATE/INSERT之后的新值和事件UPDATE/DELECT之前的旧值，如果是语句级触发器，则不能在触发动作体中使用NEW或OLD进行引用

如果触发动作体执行失败，激活触发器的事件就会终止执行，触发器的目标表或触发器可能影响的其他对象不发生任何变化

注意：不同的DBMS产品触发器语法各不相同、互不兼容

例：当对表SC的Grade属性进行修改时，若分数增加了10%，则将此次操作记录到另一个表SC_U(Sno,Cno,Oldgrade,Newgrade)中，，其中是Oldgrade修改前的分数，Newgrade是修改后的分数

CRATE TRIGGER SC_T //触发器名称为SC_T

AFTER UPDATE OF GRADE OF SC//在触发事件update发生后，触发下面的规则

REFERENCING

OLDROW AS OldTuple,

NEWROW AS NewTuple

FOR EACH ROW //行级触发器，每执行一次update,触发一次下面的规则

WHERE(NewTuple.Grade >= 1.1*OldTuple.Grade) //只有该条件为真时，才执行

INSERT INTO SC_U(Sno,Cno,Oldgrade,Newgrade) //insert操作

VALUES(OldTuple.Sno,OldTuple.Cno,OldTuple.Grade,NewTuple.Grade)

5.7.2 激活触发器

触发器的执行，是由触发事件激活的，并由数据库服务器自动执行，一个数据表上可能定义了多个触发器，遵循如下的执行顺序:

(1)执行该表上的BEFORE触发器;

(2)激活触发器的SQL语句;

(3)执行该表上的AFTER触发器

对于同一个表上的多个BEFORE(AFTER)触发器，遵循“谁先创建谁先执行”的原则。有些数据库管理系统是按照触发器名称的字母排序顺序执行的。

5.7.3 删除触发器

语句为：DROP TRIGGER<触发器名>ON<表名>

触发器必须是一个已经创建的触发器，并且只能由具有相应权限的用户删除

第六章关系数据理论

6.1 问题的提出

1.在第二章简单介绍了关系模式的简单概念，一个关系模式应当是一个五元组：

R(U, D, DOM, F)

关系名R是符号化的元组语义

U为一组属性

D为属性组U中的属性所来自的域

DOM为属性到域的映射

F为属性组U上的一组数据依赖

当且仅当U上的一个关系r满足F时，r称为关系模式R<U,F>的一个关系。

作为二维表，关系要符合一个最基本的条件：每个分量必须是不可分开的数据项。满足了这个条件的关系模式就属于第一范式（1NF）,第一范式通俗来说就是表中不能有表，是一个平表。

2.数据依赖

是一个关系内部属性与属性之间的一种约束关系，通过属性间值的相等与否体现出来的数据间相互联系，是现实世界属性间相互联系的抽象，是数据内在的性质。其中最重要的是函数依赖(Functional Dependency,FD)和多值依赖(Multi-Valued Dependency,MVD)。

3.一个不是很好的关系模式可能存在的问题

(1)数据冗余

(2)更新异常(update anomalies)

(3)插入异常(insertion anomalies)

(4)删除异常(delection anomalies)

一个好的关系模式应当不会发生更新异常、插入异常和删除异常，数据冗余应尽可能少

6.2 规范化

6.2.1 函数依赖

1.设R(U)是属性集U上的关系模式，X、Y是U的子集。若对于R(U)的任意一个可能的关系r，r中不可能存在两个元组在X上的属性值相等，而在Y上的属性值不等，则称X函数确定Y或Y函数依赖于X，记作 $X\rightarrow Y$ 。

函数依赖和别的数据依赖一样是语义范畴的概念，只能根据语义来确定一个函数依赖。

一些术语：① $X\rightarrow Y$ ，但 $Y\nsubseteq X$ ，则称 $X\rightarrow Y$ 是非平凡的函数依赖

② $X\rightarrow Y$ ，但 $Y\subseteq X$ ，则称 $X\rightarrow Y$ 是平凡的函数依赖，对于任一关系模式，平凡函数依赖都是必然成立的

③若 $X\rightarrow Y$ ，则X称为这个函数依赖的决定属性组，也称为决定因素

④若 $X\rightarrow Y$ ， $Y\rightarrow X$ ，则记作 $X\leftarrow \rightarrow Y$

⑤若Y不函数依赖于X，则记作

2. 在R(U)中，如果 $X\rightarrow Y$ ，并且对于X的任何一个真子集 $X{}'$ , 都有, 则称Y对X完全函数依赖，记作 $X\overset{F}{\rightarrow}Y$ 。

若 $X\rightarrow Y$ ，但Y不完全函数依赖于X，则称Y对X部分函数依赖，记作 $X\overset{P}{\rightarrow}Y$

3.在R(U)中，如果X→Y(Y⊈X)，，Y→Z，Z⊈Y, 则称Z对X传递函数依赖(transitive functional dependency)。记为：。注: 如果Y→X, 即X←→Y，则Z直接依赖于X即，而不是传递函数依赖。

6.2.2 码

1.用函数依赖的概念来定义码:设K为R<U,F>的中的属性或属性组合，若 $K\overset{F}{\rightarrow}U$ ,则K为R的候选码

其中U是完全依赖于K。如果U的部分函数依赖于K，即 $K\overset{P}{\rightarrow}U$ ，则K称为超码。候选码是最小的超码，即K的任意一个真子集都不是候选码。

2.若候选码多于一个，则选定其中的一个为主码

3.包含在任意一个候选码中的属性称为主属性，不包含在任何候选码中的属性称为非主属性或非码属性。最极端的情况，所有的属性组是码，称为全码。

4.关系模式R中属性或属性组X并非R的码，但X是另一个关系模式的码，则称X是R的外部码，也称外码

6.2.3 范式

关系数据库中的关系是要满足一定要求的，满足不同程度要求的为不同范式。对于各种范式之间的关系有 $5NF\subset 4NF\subset BCNF\subset 3NF\subset 2NF\subset 1NF$

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

规范化的基本思想是逐步消除数据依赖中不合适的部分，使模式中的个关系模式达到某种程度的分离，即“一事一地”的模式设计原则，让一个关系描述一个概念、一个实体或者实体间的一种联系。若多于一个概念就把它分离出去。因此所谓的规范化实质上就是概念的单一化

函数依赖是特殊的多值依赖，多值依赖又是特殊的连接依赖，如果消除了4NF的关系模式中存在的连接依赖，则可以进一步达到5NF的关系模式。

6.2.4 2NF

1.定义：若 $R\subseteq 1NF$ ，且每一个非主属性完全函数依赖于任何一个候选码，则 $R\subseteq 2NF$

非2NF的例子：关系模式S-L-C(Sno,Sdept,Sloc,Cno,Grade),Sloc为学生的住处，同一个系的学生住在同一个地方。则函数依赖有： $\begin{matrix} \\(Sno,Cno)\overset{F}{\rightarrow }Grade \\Sno\rightarrow Sdept,(Sno,Cno)\overset{P}{\rightarrow }Sdept \\Sno\rightarrow Sloc,(Sno,Cno)\overset{P}{\rightarrow }Sloc \\Sdept\rightarrow Sloc \end{matrix}$ ,如图：非主属性Sdept、Sloc并不完全函数依赖于码，因此S-L-C不符合2NF的定义

解决办法：投影分解把关系模式S-L-C分解为两个关系模式: $SC(Sno,Cno,Grade)$ 和 $S-L(Sno,Sdept,Sloc)$

2.一个关系模式如果不属于2NF，就会产生以下几个问题：
①插入异常
②删除异常
③修改复杂

6.2.5 3NF

定义：设关系模式 $R< U,F> \in 1NF$ ,若R中不存在这样的码X，属性组Y及非主属性Z( $Z\nsupseteq Y$ )使得 $X\rightarrow Y,Y\rightarrow Z$ 成立，，则称 $R< U,F> \subseteq 3NF$ 。

在关系模式 $SC(Sno,Cno,Grade)$ 和 $S-L(Sno,Sdept,Sloc)$ 中，在 $S-L(Sno,Sdept,Sloc)$ 里有： $Sno\rightarrow Sdept(Sdept^{\not{\rightarrow }}Sno),Sdept\rightarrow Sloc$ ,可得,因此 $S-L\notin 3NF$ 。解决办法：将 $S-L(Sno,Sdept,Sloc)$ 分解为 $S-D(Sno,Sdept)$ 和 $D-L(Sdept,Sloc)$

6.2.6 BCNF

1.定义：关系模式 $R< U,F> \in 1NF$ ，若 $X\rightarrow Y$ ，且 $Y\nsubseteq X$ 时 $X$ 必含有码，则 $R< U,F> \in BCNF$ 。也就是说，关系模式 $R< U,F>$ 中，若每一个决定因素都包含码，则 $R< U,F> \in BCNF$ 。由此一个满足BCNF的关系模式有：
        ①所有非主属性对每一个码都是完全函数依赖
        ②所有主属性对每一个不包含它的码也是完全函数依赖
        ③没有任何属性完全函数依赖于非码的任何一组属性

6.2.7 多值依赖

1.定义：设R(U)是属性集U上的一个关系模式。X、Y、Z是U的子集，并且Z=U-X-Y。关系模式R(U)中多值依赖 $X\rightarrow \rightarrow Y$ 成立，当且仅当对R(U)的任一关系r,给定的一对(x,z)值，有一组Y的值，这组值仅仅决定于x值而与z的值无关

对于多值依赖的另一个等价定义：在 $R(U)$ 的任一关系 $r$ 中，如果存在元组 $t$ 、 $s$ 使得 $t[X] = s[X]$ ,那么就必然存在元组 $w$ 、 $v$ $\in r$ ( $w$ 、 $v$ 可以与 $s$ 、 $t$ 相同),使得 $W[X]=V(X)=t[X]$ ,而 $w[Y]=t[Y],w[Z]=s[Z],v[Y]=s[Y],v[Z]=t[Z]$ (即交换 $s$ 、 $t$ 元组的 $Y$ 值所得的两个新元组必在 $r$ 中)，则 $Y$ 多值依赖于 $X$ ，记为 $X\rightarrow \rightarrow Y$

若 $X\rightarrow \rightarrow Y$ ，而 $Z= \varnothing$ ，即Z为空，则称 $X\rightarrow \rightarrow Y$ 为平凡的多值依赖。

例：关系模式 $WSC(W,S,C)$ 中 $W$ 表示仓库， $S$ 表示保管员， $C$ 表示商品。假设每个仓库有若干保管员，有若干商品。每个保管员保管仓库的所有商品，每种商品被所有保管员保管,如下表。按照语义对于 $W$ 的每一个值 $W_{i}$ ， $S$ 有一个完整的集合与之对应而不问 $C$ 取何值。所以 $W\rightarrow \rightarrow S$

W	S	C
W1	S1	C1
W1	S1	C2
W1	S1	C3
W1	S2	C1
W1	S2	C2
W1	S2	C3
W2	S3	C4
W2	S3	C5
W2	S4	C4
W2	S4	C5

2.多值依赖具有以下性质：
        (1)多值依赖具有对称性,若 $X\rightarrow \rightarrow Y$ 则 $X\rightarrow \rightarrow Z,Z=U-X-Y$
(2)多值依赖具有传递性,若 $X\rightarrow \rightarrow Y$ , $X\rightarrow \rightarrow Z$ ,则 $X\rightarrow \rightarrow Z-Y$
(3)函数依赖可以看作是多值依赖的特殊情况，即若 $X\rightarrow Y$ ，则 $X\rightarrow \rightarrow Y$
        (4)若 $X\rightarrow \rightarrow Y$ ， $X\rightarrow \rightarrow Z$ 则 $X\rightarrow \rightarrow YZ(Y\cup Z)$
        (5)若 $X\rightarrow \rightarrow Y$ ， $X\rightarrow \rightarrow Z$ 则 $X\rightarrow \rightarrow Y\cap Z$
        (6)若 $X\rightarrow \rightarrow Y$ ， $X\rightarrow \rightarrow Z$ 则 $X\rightarrow \rightarrow Y-Z,X\rightarrow \rightarrow Z-Y$

3.多值依赖与函数依赖相比具有以下两个基本的区别：
(1)多值依赖的有效性与属性集的范围有关

若 $X\rightarrow \rightarrow Y$ 在U上成立，则在 $W(XY\subseteq W\subseteq U)$ 上一定成立；反之则不然，即 $X\rightarrow \rightarrow Y$ 在W上成立，在U上并不一定成立。一般的，在R(U)上若有 $X\rightarrow \rightarrow Y$ 在 $W(W\subset U)$ 上成立，则称 $X\rightarrow \rightarrow Y$ 为R(U)的嵌入式多值依赖。
(2)若函数依赖 $X\rightarrow Y$ 在R(U)上成立，则对于任何 $Y{}'\subset Y$ 均有 $X\rightarrow {Y}'$ 成立。而多值依赖 $X\rightarrow \rightarrow Y$ 若在R(U)上成立，却不能断言对于任何 $Y{}'\subset Y$ 均有 $X\rightarrow \rightarrow {Y}'$ 成立

6.2.8 4NF

1.定义：关系模式 $R< U,F> \in 1NF$ ，如果对于R的每个非平凡多值依赖 $X\rightarrow \rightarrow Y(Y\nsubseteq X)$ , $X$ 都含有码，则称 $R< U,F> \in 4NF$

4NF就是限制关系模式的属性之间不允许有非平凡且非函数依赖的多值依赖。

例：上文提到的 $WSC(W,S,C)$ ，虽然 $WSC\in BCNF$ ，但是 $WSC\notin 4NF$ 。将 $WSC$ 分解为 $WS(W,S)$ 和 $WC(W,C)$ 。在 $WS(W,S)$ 中虽然 $W\rightarrow \rightarrow S$ 成立但是是平凡的多值依赖， $WS(W,S)$ 中已不存在非平凡的非函数依赖的多值依赖，所以 $WS\in 4NF$ ，同理 $WC\in 4NF$

6.3 数据依赖的公理系统

1.定义1：对于满足一组函数依赖F的关系模式R<U,F>,其中任何一个关系r，若函数依赖 $X\rightarrow Y$ 都成立（即r中任意两元组t,s,若t[X]=s[X],则t[Y]=s[Y]）,则称F逻辑蕴涵 $X\rightarrow Y$ 。

        为了求得给定关系模式的码，有Armstrong公理系统：设U为属性集总体，F是U上的一组函数依赖，于是有关系模式R<U,F>,对R<U,F>来说有以下的推理规则：
        ①自反律：若 $Y\subseteq X\subseteq U$ ，则 $X\rightarrow Y$ 为F所蕴涵
        ②增广律：若 $X\rightarrow Y$ 为F所蕴涵，且 $Z\subseteq U$ ，则 $XZ\rightarrow YZ$ $(X\cup Z\rightarrow Y\cup Z)$ 为F所蕴涵
③传递律：若 $X\rightarrow Y$ 及 $Y\rightarrow Z$ 为F所蕴涵，则 $X\rightarrow Z$ 为F所蕴涵

由自反律所得到的函数依赖均是平凡的函数依赖，自反律的使用并不依赖于F

定理1：Armstrong推理规则是正确的
证明：证明自反律：设 $Y\subseteq X\subseteq U$
对 $R<U,F>$ 的任一关系 $r$ 中的任意两个元组 $t$ 、 $s$ :
若 $t[X]=s[X]$ ,由于 $Y\subseteq X$ ，有 $t[Y]=s[Y]$
所以 $X\rightarrow Y$ 成立，得证
证明增广律：设 $X\rightarrow Y$ 为 $F$ 所蕴涵，且 $Z\subseteq U$
设 $R<U,F>$ 的任一关系 $r$ 中任意两个元组 $t$ 、 $s$
若 $t[XZ]=s[XZ]$ ,则有 $t[X]=s[X]$ 和 $t[Z]=s[Z]$
由 $X\rightarrow Y$ ，于是有 $t[Y]=s[Y]$ ，所以 $t[YZ]=s[YZ]$ ， $XZ\rightarrow YZ$ 为 $F$ 所蕴涵
证明传递律：设 $X\rightarrow Y$ 和 $Y\rightarrow Z$ 为 $F$ 所蕴涵
对 $R<U,F>$ 的任意关系 $r$ 中任意两个元组 $t$ 、 $s$
若 $t[X]=s[X]$ ,由于 $X\rightarrow Z$ ，有 $t[Y]=s[Y]$
再由 $Y\rightarrow Z$ ，有 $t[Z]=s[Z]$ ，所以 $X\rightarrow Z$ 为 $F$ 所蕴涵

根据Armstrong三条推理规则可得：
①合并规则：由 $X\rightarrow Y$ ， $X\rightarrow Z$ ，有 $X\rightarrow YZ$
②伪传递规则：由 $X\rightarrow Y$ ， $WY\rightarrow Z$ ，有 $XW\rightarrow Z$
③分解规则：由 $X\rightarrow Y$ 及 $Z\subseteq Y$ ，有 $X\rightarrow Z$

引理1： $X\rightarrow A_{1}A_{2}\cdots A_{k}$ 成立的充分必要条件是 $X\rightarrow A_{i}$ 成立 $\left( i=1,2,\cdots ,k \right )$

2.定义2：在关系模式R<U,F>中为F所逻辑蕴涵的函数依赖全体叫作F的闭包，记为 $F^{+}$

定理2：Armstrong公理是有效的和完备的，有效性是指：由F出发根据Armstrong公理推导出来的每一个函数依赖一定在 $F^{+}$ 中；完备性是指 $F^{+}$ 中的每一个函数依赖，必定可以由F出发根据Armstrong公理推导出来。

证明Armstrong公理的有效性和完备性：有效性可由定理1的证明得出
完备性证明：反证法证明命题的逆否命题即若函数依赖 $X\rightarrow Y$ 不能由 $F$ 从Armstrong公理导出，则它必然不为 $F$ 所蕴涵，证明分三步：
①若 $V\rightarrow W$ 成立，且 $V\subseteq X_{F}^{+}$ ，则 $W\subseteq X_{F}^{+}$ ，因为 $V\subseteq X_{F}^{+}$ 所以有 $X\rightarrow V$ 成立，于是 $X\rightarrow W$ 成立
②构造一张二维表 $r$ ,它由下列两个元组构成，可以证明 $r$ 必是 $R(U,F)$ 的一个关系，即 $F$ 中的全部函数依赖在 $r$ 上成立。

$\overset{X_{F}^{+}}{\overbrace{\begin{matrix} 11..................1\\ 11..................1 \end{matrix}}}$ $\overset{U-X_{F}^{+}}{\overbrace{\begin{matrix} 00..................1\\ 11..................1 \end{matrix}}}$

若 $r$ 不是 $R<U,F>$ 的关系，则必由于 $F$ 中有某一个函数依赖 $V\rightarrow W$ 在 $r$ 上不成立所致，由 $r$ 的构成可知， $V$ 必定是 $X_{F}^{+}$ 的子集，而 $W$ 不是 $X_{F}^{+}$ 的子集，可是由第①步， $W\subseteq X_{F}^{+}$ 矛盾，所以 $r$ 必是 $R<U,F>$ 的一个关系。
③若 $X\rightarrow Y$ 不能由 $F$ 从Armstrong公理导出，则Y不是 $X_{F}^{+}$ 的子集，因此必须有Y的子集 $Y{}'$ 满足 ${Y}'\subseteq U-X_{F}^{+}$ ，则 $X\rightarrow Y$ 在 $r$ 中不成立，即 $X\rightarrow Y$ 必不为 $R<U,F>$ 蕴涵

3.定义3：设F为属性集U上的一组函数依赖， $X_{_{\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}}}\ Y\subseteq U$ ,

$X_{F}^{+}$ 称为属性集X关于函数依赖集F的闭包

引理2：设F为属性集上的一组函数依赖， $X_{_{\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}\grave{}}}\ Y\subseteq U$ ， $X\rightarrow Y$ 能由F根据Armstrong公理导出的充分必要条件是 $Y\subseteq X_{F}^{+}$

算法1:求属性集 $X\left ( X\subseteq U \right )$ 关于U上的函数依赖集F 的闭包 $X_{F}^{+}$
输入：X、F
输出： $X_{F}^{+}$
步骤：
①令 $X^{(0)}=X,i=0$
                        ②求B,这里 $B=\left \{ A|\left ( \exists V \right ) \left ( \exists W \right )\left ( V\rightarrow W\in F\wedge V\subseteq X^{\left ( i \right )} \wedge A\in W\right )\right \}$                         ③ $X^{\left ( i+1 \right )}=B\cup X^{i}$
                        ④判断 $X^{\left ( i+1 \right )}= X^{i}$
                        ⑤若 $X^{\left ( i+1 \right )}$ 与 $X^{i}$ 相等或 $X^{i}=U$ ，则 $X^{i}$ 就是 $X_{F}^{+}$ ，算法终止
⑥若否，则 $i=i+1$ ,返回第②步

例：已知关系模式 $R<U,F>$ ,其中 $U=\left \{ A,B,C,D,E \right \},F=\left \{AB\rightarrow C,B\rightarrow D,C\rightarrow E,EC\rightarrow B,AC\rightarrow B\right \}$ ,求 $\left ( AB \right )_{F}^{+}$

由算法1，设 $X^{(0)} = AB$

计算 $X^{(1)}$ :逐一扫描 $F$ 集合中1各个函数依赖，找左部为 $A$ 、 $B$ 或 $AB$ 的函数依赖。得到两个： $AB\rightarrow C,B\rightarrow D$ 。于是 $X^{(1)}=AB\cup CD=ABCD$

因为 $X^{(0)}\neq X^{(1)}$ ，所以再找出左部为 $ABCD$ 子集的那些函数依赖，又得到： $C\rightarrow E,AC\rightarrow B$ ,于是 $X^{(2)}=X^{(1)}\cup BE=ABCDE$

因为 $X^{(2)}$ 已等于全部属性集合，所以 $\left ( AB \right )_{F}^{+}=ABCDE$

4.定义4：如果 $G^{+}=F^{+}$ ，就说明函数依赖集F覆盖G(F是G的覆盖，或G是F的覆盖)，或F与G等价

引理3： $G^{+}=F^{+}$ 的充分必要条件是 $F^{+}\subseteq G^{+}andG^{+}\subseteq F^{+}$

5.定义5：如果函数依赖集F满以下条件，则称F为一个极小函数依赖集，亦称为最小依赖集或最小覆盖：①：F中任意函数依赖的右部仅含有一个属性
②：F中不存在这样的函数依赖 $X\rightarrow A$ ，使得F与 $F-\left \{ X-A \right \}$ 等价
③：F中不存在这样的函数依赖 $X\rightarrow A$ ，X有真子集Z使得 $F-\left \{ X-A \right \}\cup \left \{ Z\rightarrow A \right \}$ 与F等价(即左部要尽可能地简）

定理3：每一个函数依赖集F均等价于一个极小函数依赖集 $F_{m}$ ,此 $F_{m}$ 称为F的最小依赖集

6.4 模式的分解

1.定义6：关系模式 $R<U,F>$ 的一个分解是指
$\rho =\left \{ R_{1}<U_{1},F_{1}>,R_{2}<U_{2},F_{2}>,\cdots ,R_{n}<U_{n},F_{n}> \right \}$ 其中 $U=\underset{i=1}{\overset{n}{\bigcup }}U_{i}$ ,并且没有 $U_{i}\subseteq U_{j},1\leq i,j\leq n$ , $F_{i}$ 是 $F$ 在 $U_{i}$ 上的投影

2.定义7：函数依赖集合 $\left \{ X\rightarrow Y|X\rightarrow Y\in F^{+} \wedge XY\in U_{i}\right \}$ 的一个覆盖叫作F在属性上 $U_{i}$ 的投影

6.4.1 模式分解的三个定义

        对等价的概念的三个不同的定义:①分解具有无损连接性(lossless join)：不能丢数据也不能多
                                                           ②分解要保持函数依赖(preserve functional dependency)
                                                           ③分解既要保持函数依赖，又要具有无损连接性
这三个定义是实行分解的三条不同的准则，按照不同的分解准则，模式所能达到的分离程度各不相同，各种范式就是对分离程度的测度。

6.4.2 分解的无损连接和保持函数依赖性

先定义一个记号：设 $\rho =\left \{ R_{1}<U_{1},F_{1}>,\cdots ,R_{k}<U_{k},F_{k}> \right \}$ 是 $R<U,F>$ 的一个分解， $r$ 是 $R<U,F>$ 的一个关系。定义 $m_{p}(r)= \overset{k}{\underset{i=1}{\bowtie}}\pi _{R_{i}}(r)$ ,即 $m_{p}(r)$ 是 $r$ 在 $R$ 中各关系模式上投影的连接。这里 $\pi_{R_{i}}(r)=\left \{ t.U_{i}|t\in r \right \}$
说明：符号 $\bowtie$ 与第二章自然连接含义稍有不同，如果两个关系当中有相同的属性列就按自然连接定义执行，如果没有相同的属性列，则按笛卡尔积运算进行。

引理4：设 $R<U,F>$ 是一个关系模式， $\rho =\left \{ R_{1}<U_{1},F_{1}>,\cdots ,R_{k}<U_{k},F_{k}> \right \}$ 是 $R$ 的一个分解， $r$ 是 $R$ 的一个关系， $r_{i}=\pi _{R_{i}}(r)$ ,则：
① $r\subseteq m_{p}(r)$ 即 $r$ 的投影连接包含 $r$
$r$ 的投影连接包含 $r$ ，分解后在连接起来的 $r$ 肯定不会比原来的小(可能会多)
②若 $s=m_{p}(r)$ ，则 $\pi _{R_{i}}(s)=r_{i}$
投影连接后再投影到子关系模式=直接投影到该子关系模式即 $\pi _{R_{i}}(r)=\pi _{R_{i}}(m_{p}(r))$
③ $m_{p}(m_{p}(r))=m_{p}(r)$
多次投影连接的结果等于一次投影连接的结果

分解后的关系进行自然连接必然包含分解前的关系，即分解不会丢失信息，但可能增加信息，只有 $r=m_{p}(r)$ 时，分解才具有无损连接性。
定义8： $\rho =\left \{ R_{1}<U_{1},F_{1}>,\cdots ,R_{k}<U_{k},F_{k}> \right \}$ 是 $R<U,F>$ 的一个分解，若对 $R<U,F>$ 的任何一个关系 $r$ 均有 $r=m_{p}(r)$ 成立，则称分解 $\rho$ 具有无损连接性。简称 $\rho$ 为无损分解
算法2：判别一个分解的无损连接性

$\rho =\left \{ R_{1}<U_{1},F_{1}>,\cdots ,R_{k}<U_{k},F_{k}> \right \}$ 是 $R<U,F>$ 的一个分解， $U=\left \{ A_{1},\cdots ,A_{n} \right \},F=\left \{ FD_{1},FD_{2},\cdots,FD_{\rho } \right \}$ ,不妨设 $F$ 是一组极小依赖集，记 $FD_{i}$ 为 $X_{i}\rightarrow A_{li}$

(1)建立一张 $n$ 列 $k$ 行表，每一列对应一个属性，每一行对应分解中的一个关系模式。若属性 $A_{j}\in U_{i}$ ,则在 $j$ 列 $i$ 行交叉处填上 $a_{j}$ ,否则填上 $b_{ij}$
(2)根据 $F$ 中的函数依赖修改表的内容，对每一个 $FD_{i}$ 做下列操作：找到 $X_{i}$ 所对应的列中具有相同符号的那些行，考察这些行中 $li$ 列的元素。若其中有 $a_{li}$ ,则全部改为 $a_{li}$ ，否则全部改为 $b_{mli}$ 。其中 $m$ 是这些行的行号最小值
应当注意的是，若某个 $b_{tli}$ 被更改，那么该表的 $li$ 列中凡是 $b_{tli}$ 的符号（不管它是否开始找到的那些行）均应作相应的更改
如果在某次更改之后，有一行成为 $a_{1},a_{2},\cdots,a_{n}$ ,则算法终止， $\rho$ 具有无损连接性，否则 $\rho$ 不具有无损连接性
对 $F$ 中 $p$ 个 $FD$ 逐一进行一次这样的处置，称为对 $F$ 的一次扫描
(3)比较扫描前后表有无变化，如果变化则返回第(2)步,否则算法终止

定理4：如果算法2终止时表中有一行为 $a_{1},a_{2},\cdots,a_{n}$ ，则 $\rho$ 为无损连接分解

定理5：对于 $R<U,F>$ 的一个分解 $\rho =\left \{ R_{1}<U_{1},F_{1}>,R_{2}<U_{2},F_{2}> \right \}$ (分解为了两个关系模式)如果 $U_{1}\cap U_{2}\rightarrow U_{1}-U_{2}\in F^{+}$ 或 $U_{1}\cap U_{2}\rightarrow U_{2}-U_{1}\in F^{+}$ ，则 $\rho$ 具有无损连接性

定义9：若 $F^{+}=\left (\underset{i=1}{ \overset{k}{\bigcup }}F_{i} \right )^{+}$ ,（总的闭包=分解的每个关系模式的闭包的并集）则 $R<U,F>$ 的分解 $\rho =\left \{ R_{1}<U_{1},F_{1}>,\cdots ,R_{k}<U_{k},F_{k}> \right \}$ 保持函数依赖

6.4.3 模式分解的算法

三个重要事实：(1)若要求分解保持函数依赖，那么模式总可以达到3NF，但不一定达到BCNF
(2)若要求分解既要保持函数依赖，又有无损连接性，可以达到3NF，不一定到BCNF
(3)若要求分解具有无损连接性，那一定可以到达4NF

算法3（合成法）：转换为3NF的保持函数依赖的分解

(1)对 $R<U,F>$ 中的函数依赖集 $F$ 进行“极小化处理”(处理后得到的依赖集仍记为 $F$ )
(2)找出所有不在 $F$ 中出现的属性(记作 $U_{0}$ ),把这样的属性构成一个关系模式 $R_{0}<U_{0},F_{0}>$ 。把这些属性从 $U$ 中去掉，剩余的属性仍记为 $U$
(3)若有 $X\rightarrow A\in F$ ，且 $XA=U$ ，则 $\rho =\left \{ R\right \}$ ,算法终止
(4)否则，对 $F$ 按具有相同左部的原则分组(假定分为 $k$ 组)，每一组函数依赖所涉及的全部属性形成一个属性集 $U_{i}$ 。若 $U_{i}\in U_{j}(i\neq j)$ 就去掉 $U_{i}$ 。由于经过了步骤(2),故 $U= \overset{k}{\underset{i=1}{\bigcup }}U_{i}$ 于是 $\rho =\left \{ R_{1}<U_{1},F_{1}>,\cdots ,R_{k}<U_{k},F_{k}> \right \}\cup R_{0}<U_{0},F_{0}>$ 构成 $R<U,F>$ 的一个保持函数依赖的分解，并且每个 $R_{i}<U_{i},F_{i}>$ 均属于3NF。这里 $F_{i}$ 是 $F$ 在 $U_{i}$ 上的投影，并且 $F_{i}$ 不一定与 $F_{i}^{'}$ 相等，但一定被 $F_{i}$ 所包含，因此保持函数依赖是显然的

算法4：转换为3NF既有无损连接性又保持函数依赖的分解
(1)设 $X$ 是 $R<U,F>$ 的码。 $R<U,F>$ 已由算法3分解为 $\rho =\left \{ R_{1}<U_{1},F_{1}>,\cdots ,R_{k}<U_{k},F_{k}> \right \}\cup R_{0}<U_{0},F_{0}>$ ，令 $\tau =\rho\cup \left \{ R^{*}<X,F_{x}> \right \}$
(2)若有某个 $U_{i}$ , $X\subseteq U_{i}$ ,将 $R^{*}<X,F_{x}>$ 从 $\tau$ 中去掉，或者 $U_{i}\subseteq X$ ,将 $R<U_{i},F_{x}>$ 从 $\tau$ 中去掉
(3) $\tau$ 即为所求的分解
算法5(分解法)：转换为BCNF的无损连接分解
(1)令 $\rho =\left \{ R<U,F> \right \}$
(2)检查 $\rho$ 中各关系模式是否均属于BCNF。若是则算法终止
(3)设 $\rho$ 中 $R_{i}<U_{i},F_{i}>$ 不属于BCNF，那么必有 $X\rightarrow A\in F_{i}^{+}(A\notin X)$ ,且 $X$ 非 $R_{i}$ 的码。因此， $XA$ 是 $U_{i}$ 的真子集。对 $R_{i}$ 进行分解： $\sigma =\left \{ S_{1},S_{2} \right \},U_{S1}=XA,U_{S2}=U_{1}-\left \{ A \right \}$ ,以 $\sigma$ 代替 $R_{i}<U_{i},F_{i}>$ ，返回第(2)步
由于 $U$ 中属性有限，经过有限次循环后算法5必会终止。

引理5：若 $\rho =\left \{ R_{1}<U_{1},F_{1}>,\cdots ,R_{k}<U_{k},F_{k}> \right \}$ 是 $R<U,F>$ 的一个无损连接的分解， $\sigma =\left \{ S_{1},S_{2},\cdots,S_{m} \right \}$ 是 $\rho$ 中的一个无损连接分解，那么
${\rho }'=\left \{ R_{1},R_{2},\cdots,R_{i-1},S_{1},\cdots ,S_{m},R_{i+1},\cdots,R_{K} \right \}$
${\rho }''=\left \{ R_{1},\cdots,R_{K},R_{K+1},\cdots ,R_{n} \right \}$ ( ${\rho }''$ 是 $R<U,F>$ 包含 $\rho$ 的关系模式集合的分解)均是 $R<U,F>$ 的无损连接分解

引理6： $\left ( R_{1}\bowtie R_{2} \right )\bowtie R_{3}=R_{1}\bowtie (R_{2}\bowtie R_{3})$

定理6：关系模式 $R<U,D>$ 中， $D$ 为 $R$ 中函数依赖 $FD$ 和多值依赖MVD集合。则 $X\rightarrow \rightarrow Y$ 成立的充要条件是 $R$ 的分解 $\rho =\left \{ R_{1}(X,Y),R_{2}(X,Z) \right \}$ 具有无损连接性，其中 $Z=U-X-Y$

算法6：达到4NF的具有无损连接性的分解

首先使用算法5得到 $R$ 的一个达到了BCNF的无损连接分解 $\rho$ ，然后对某一 $R_{i}<U_{i},D_{i}>$ ,若不属于4NF，则可按定理6进行分解，直到每一个关系模式均属于4NF为止。

关于包含函数依赖和多值依赖的有效且完备的公理系统
①若 $Y\subseteq X\subseteq U,$ 则 $X\rightarrow Y$
        ②若 $X\rightarrow Y$ ，且 $Z\subseteq U$ ，则 $XZ\rightarrow YZ$
        ③若 $X\rightarrow Y$ ， $Y\rightarrow Z$ 则 $X\rightarrow Z$
        ④若 $X\rightarrow\rightarrow Y$ ， $V\subseteq W\subseteq U$ ,则 $XW\rightarrow YV$
        ⑤若 $X\rightarrow\rightarrow Y$ ，则 $X\rightarrow \rightarrow U-X-Y$
        ⑥若 $X\rightarrow \rightarrow Y$ ， $Y\rightarrow \rightarrow Z$ 则 $X\rightarrow \rightarrow Z-Y$
        ⑦若 $X\rightarrow Y$ ，则 $X\rightarrow \rightarrow Y$
        ⑧若 $X\rightarrow\rightarrow Y$ ， $W\rightarrow Z$ , $W\cap Y\neq \phi ,Z\subseteq Y$ 则 $X\rightarrow Z$
由8条公理可得如下4条有用的推理规则
        ①合并规则： $X\rightarrow\rightarrow Y$ ， $X\rightarrow\rightarrow Z$ ，则 $X\rightarrow\rightarrow YZ$
        ②伪传递规则： $X\rightarrow\rightarrow Y$ ， $WY\rightarrow Z$ ，则 $WX\rightarrow \rightarrow Z-WY$
        ③混合伪传递规则： $X\rightarrow\rightarrow Y$ ， $XY\rightarrow Z$ ，则 $X\rightarrow Z-Y$
        ④分解规则： $X\rightarrow\rightarrow Y$ ， $X\rightarrow Z$ ，则 $X\rightarrow\rightarrow Y\cap Z,X\rightarrow \rightarrow Y-Z,X\rightarrow \rightarrow Z-Y$

6.5 小结

第7章数据库设计

7.1 数据库设计概述

定义：数据库设计是指对于一个给定的应用环境，构造(设计)优化的数据库逻辑模式和物理结构，并据此建立数据库及其应用系统，使之能够有效地存储和管理数据，满足各种用户的应用需求，包括信息管理要求和数据操作要求

信息管理要求是指在数据库中应该存储和管理哪些数据对象

数据操作要求是指对数据对象需要进行哪些操作，如查询、增、删、改、统计等操作

设计的目标是为用户和各种应用系统提供一个信息基础设施和高效的运行环境

高效的运行环境指数据库数据的存取效率高、数据库存储空间的利用率高、数据库系统运行管理的效率高

7.1.1 数据库设计的特点

1.数据库建设的基本规律

三分建设、七分管理、十二分基础数据
管理：数据库建设项目管理和企业（应用部门）的业务管理
基础数据：数据的收集、整理、组织和不断更新

2.结构（数据）设计和行为（处理）设计相结合

传统的软件工程：注重行为设计
早期的数据库设计：注重结构设计

7.1.2 数据库设计方法

需要的知识和技术主要包括：计算机的基础知识
                             软件工程的原理和方法
                               程序设计的方法和技巧
                             数据库的基本知识
                             数据库设计技术
                               应用领域的知识

1.手工试凑法

设计质量与设计人员的经验和水平有直接关系

缺乏科学理论和工程方法的支持，工程的质量难以保证

数据库运行一段时间后常常又不同程度地发现各种问题，增加了维护代价

2.规范设计法

(1)基本思想

过程迭代和逐步求精

(2)典型方法

①新奥尔良（New Or leans）方法

②基于E-R模型的数据库设计方法

③3NF（第三范式）的设计方法

④面向对象的数据库设计方法

⑤统一建模语言（UML）方法

7.1.3 数据库设计的基本步骤

1.分为以下6各阶段：需求分析、概念结构设计、逻辑结构设计、物理结构设计、数据库实施、数据库运行和维护

需求分析和概念设计独立于任何数据库管理系统

逻辑设计和物理设计与选用的数据库管理系统密切相关

2.参加数据库设计的人员

(1)系统分析人员和数据库设计人员

自始至终参与数据库设计，其水平决定了数据库系统的质量

(2)数据库管理员和用户代表

主要参加需求分析与数据库的运行和维护

(3)应用开发人员

包括程序员和操作员，在实施阶段参与进来，分别负责编制程序和准备软硬件环境

3.各阶段的主要任务

(1)需求分析阶段

是否做得充分与准确，决定了构建数据库的速度和质量

(2)概念结构设计阶段

通过对用户需求进行综合、归纳与抽象,形成一个独立于具体数据库管理系统的概念模型

(3)逻辑结构设计阶段

将概念结构转换为某个数据库管理系统所支持的数据模型，并对其进行优化

(4)物理结构设计阶段

为逻辑数据结构选取一个最适合应用环境的物理结构,包括存储结构和存取方法

(5)数据库实施阶段

根据逻辑设计和物理设计的结果构建数据库,编写与调试应用程序,组织数据入库并进行试运行

(6)数据库运行和维护阶段

经过试运行后即可投入正式运行,在运行过程中必须不断对其进行评估、调整与修改

7.1.4 数据库设计过程中的各级模式

7.2 需求分析

7.2.1 需求分析过程

结构化分析方法（Structured Analysis，简称SA方法）：SA方法从最上层的系统组织机构入手，采用自顶向下、逐层分解的方式分析系统。需求分析过程如下图:

7.2.2 数据字典

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

1.数据字典的内容
(1)数据项

数据项是数据的最小组成单位，对数据项的描述通常包含以下内容：

数据项描述={数据项名,数据项含义说明,别名,数据类型,长度,取值范围,取值含义,与其他数据项的逻辑关系,数据项之间的联系}

“取值范围”、“与其他数据项的逻辑关系”定义了数据的完整性约束条件，是设计数据检验功能的依据，可以用关系规范化理论为指导，用数据依赖的概念分析和表示数据项之间的联系
(2)数据结构

数据结构反映了数据之间的组合关系。一个数据结构可以由若干个数据项组成，也可以由若干个数据结构组成，或由若干个数据项和数据结构混合组成。对数据结构的描述为
数据结构描述={数据结构名，含义说明，组成:{数据项或数据结构}}
(3)数据流

数据流是数据结构在系统内传输的路径。对数据流的描述：

数据流描述={数据流名,说明,数据流来源,数据流去向,组成:{数据结构},平均流量,高峰期流量}

数据流来源：说明该数据流来自哪个过程

数据流去向：说明该数据流将到哪个过程去

平均流量：在单位时间（每天、每周、每月等）里的传输次数

高峰期流量：在高峰时期的数据流量
(4)数据存储

数据存储是数据结构停留或保存的地方，也是数据流的来源和去向之一。对数据存储的描述

数据存储描述={数据存储名,说明,编号,输入的数据流 ,输出的数据流,组成:{数据结构},数据量,存取频度,存取方式}

存取频度：每小时、每天或每周存取次数，每次存取的数据量等信息

存取方法：批处理 / 联机处理；检索 / 更新；顺序检索 / 随机检索

输入的数据流：数据来源

输出的数据流：数据去向
(5)处理过程

处理过程的具体处理逻辑一般用判定表或判定树来描述。数据字典中只需要描述处理过程的说明性信息。处理过程说明性信息的描述：

处理过程描述={处理过程名,说明,输入:{数据流}, 输出:{数据流},处理:{简要说明}}

简要说明：说明该处理过程的功能及处理要求

功能：该处理过程用来做什么

处理要求：处理频度要求，如单位时间里处理多少事务，多少数据量、响应时间要求等

处理要求是后面物理设计的输入及性能评价的标准

7.2.3 小结

把需求收集和分析作为数据库设计的第一阶段是十分重要的。第一阶段收集的基础数据（用数据字典来表达）是下一步进行概念设计的基础。

强调两点：（1）设计人员应充分考虑到可能的扩充和改变，使设计易于更改，系统易于扩充

（2）必须强调用户的参与

7.3 概念结构设计

7.3.1 概念模型

将需求分析得到的用户需求抽象为信息结构（即概念模型）的过程就是概念结构设计

概念模型的特点：(1)能真实、充分地反映现实世界，是现实世界的一个真实模型。

(2)易于理解，从而可以用它和不熟悉计算机的用户交换意见。

(3)易于更改，当应用环境和应用要求改变时，容易对概念模型修改和扩充

（4)易于向关系、网状、层次等各种数据模型转换

描述概念模型的工具：E-R模型

7.3.2 E-R模型

1.实体之间的联系

(1)两个实体型之间的联系：

①一对一联系（1∶1）

如果对于实体集A中的每一个实体，实体集B中至多有一个（也可以没有）实体与之联系，反之亦然，则称实体集A与实体集B具有一对一联系，记为1∶1。

例如，学校里一个班级只有一个正班长，而一个班长只在一个班中任职，则班级与班长之间具有一对一联系。

②一对多联系（1∶n）

如果对于实体集A中的每一个实体，实体集B中有n个实体（n≥0）与之联系，反之，对于实体集B中的每一个实体，实体集A中至多只有一个实体与之联系，则称实体集A与实体集B有一对多联系，记为1∶n。

例如，一个班级中有若干名学生，而每个学生只在一个班级中学习，则班级与学生之间具有一对多联系。

③多对多联系（m∶n）

如果对于实体集A中的每一个实体，实体集B中有n个实体（n≥0）与之联系，反之，对于实体集B中的每一个实体，实体集A中也有m个实体（m≥0）与之联系，则称实体集A与实体集B具有多对多联系，记为m∶n。

例如，一门课程同时有若干个学生选修，而一个学生可以同时选修多门课程，则课程与学生之间具有多对多联系。下图为两个实体型之间的三类联系

(2）两个以上的实体型之间的联系

一般地，两个以上的实体型之间也存在着一对一、一对多、多对多联系。

对于课程、教师与参考书3个实体型，如果一门课程可以有若干个教师讲授，使用若干本参考书，而每一个教师只讲授一门课程，每一本参考书只供一门课程使用，则课程与教师、参考书之间的联系是一对多的。下图为三个实体型之间的联系

(3)单个实体型内的联系

同一个实体集内的各实体之间也可以存在一对一、一对多、多对多的联系。

例如，职工实体型内部具有领导与被领导的联系，即某一职工（干部）“领导”若干名职工，而一个职工仅被另外一个职工直接领导，因此这是一对多的联系。下图为单个实体型内部一对多的联系示例

(4)联系的度：参与联系的实体型的数目

2个实体型之间的联系度为2，也称为二元联系；

3个实体型之间的联系度为3，称为三元联系；

N个实体型之间的联系度为N，也称为N元联系

2.E-R图

E-R图提供了表示实体型、属性和联系的方法：

实体型：用矩形表示，矩形框内写明实体名

属性：用椭圆形表示，并用无向边将其与相应的实体型连接起来

联系：用菱形表示，菱形框内写明联系名，并用无向边分别与有关实体型连接起来，同时在无向边旁标上联系的类型（1∶1，1∶n或m∶n）。联系可以具有属性

3.一个实例

1.某个工厂物资管理的概念模型。物资管理涉及的实体有：

仓库：属性有仓库号、面积、电话号码

零件：属性有零件号、名称、规格、单价、描述

供应商：属性有供应商号、姓名、地址、电话号码、账号

项目：属性有项目号、预算、开工日期

职工：属性有职工号、姓名、年龄、职称

2.这些实体之间的联系如下：

(1)一个仓库可以存放多种零件，一种零件可以存放在多个仓库中，因此仓库和零件具有多对多的联系。用库存量来表示某种零件在某个仓库中的数量

(2)一个仓库有多个职工当仓库保管员，一个职工只能在一个仓库工作，因此仓库和职工之间是一对多的联系

(3)职工之间具有领导与被领导关系。即仓库主任领导若干保管员，因此职工实体型中具有一对多的联系

(4)供应商、项目和零件三者之间具有多对多的联系。即一个供应商可以供给若干项目多种零件，每个项目可以使用不同供应商供应的零件，每种零件可由不同供应商供给

7.3.3 扩展的E-R模型

1.ISA联系
用E-R方法构建一个项目的模型时，经常会遇到某些实体型是某个实体型的子类型。例如研究生和本科生是学生的子类型，学生是父类型。这种父类-子类联系称为ISA联系，表示“is a”的语义。用三角形来表示

ISA联系一个重要的性质是子类继承父类所有的属性，同时子类也可以有自己的属性

(1)分类属性

根据分类属性的值把父实体型中的实体分派到子实体型中

(2)不相交约束与可重叠约束

不相交约束描述父类中的一个实体不能同时属于多个子类中的实体集，即一个父类中的实体最多属于一个子类实体集，用ISA联系三角形符号内加一个叉号“×”来表示

可重叠约束允许父类中的一个实体能同时属于多个子类中的实体集，子类符号中没有叉号“×”

(3)完备性约束

完备性约束描述父类中的一个实体是否必须是某一个子类中的实体。如果是则为完全特化，用双线连接来表示，否则为部分特化，用单线连接来表示

2.基数约束

基数约束是对实体之间一对一、一对多和多对多联系的细化。用基数约束说明实体型中的任何一个实体可以在联系中出现的最少次数和最多次数。约束用一个数对 $min..max$ 来表示, $0\leq min\leq max$ ,例如，0..1,1..3,1..*(*表示无穷大)
$min=1$ 时叫作强制参与约束，即被施加基数约束的实体型中的每个实体都要参与联系；
$min=0$ 时叫作非强制参与约束，即被施加基数约束的实体型中的实体可以出现在联系中，也可以不出现在联系中

3.Part-of约束

即部分联系，它表名某个实体型是另一个实体型的一部分。可以分为两种情况，一种是整体实体如果被破坏，部分实体仍然可以独立存在，称为非独占的Part-of联系，可通过基数约束来表达

一种是独占联系，即整体实体被破坏，实体不能存在，在E-R图中用弱实体类型和识别联系来表示独占联系。如果一个实体型的存在依赖于其他实体型的存在，则这个实体型叫作弱实体型，否则叫作强实体型

判断方法：如果不能从一个实体型的属性中找出可以作为码的属性，则这个实体型就是弱实体型。在E-R图中用双矩形表示弱实体型，用双棱形表示识别联系。下图中房间为弱实体(不指定楼房给定房间号也不能确定该房间到底是哪个楼房的)

7.3.4 UML

        UML是统一建模语言或标准建模语言，可以作为表示E-R图的一种方法。
UML表示E-R图的说明：
                ①实体型：用类来表示，矩形框上部写实体名，下面列出属性名
                ②实体的码：在类图中属性后面加“PK”(primary key)
                ③联系：用类图之间的“关联”来表示
                ④基数约束：UML中关联类之间基数约束概念、表示和E-R图中的基数约束类似
                ⑤UML中的子类：面向对象技术支持超类-子类概念，子类可以继承超类的属性，也可以有自己的属性

7.3.5 概念结构设计

1.实体与属性的划分原则

为了简化E-R图的处置，现实世界的事物能作为属性对待的，尽量作为属性对待

事物可以作为属性对待的两条准则：

①作为属性，不能再具有需要描述的性质。属性必须是不可分的数据项，不能包含其他属性

②属性不能与其他实体具有联系，即E-R图中所表示的联系是实体之间的联系

2.E-R图的集成

E-R图的集成一般需要分两步

①合并。解决各分E-R图之间的冲突，将分E-R图合并起来生成初步E-R图

②修改和重构。消除不必要的冗余，生成基本E-R图

(1)合并E-R图，生成初步E-R图

各个局部应用所面向的问题不同，各个子系统的E-R图之间必定会存在许多不一致的地方，称之为冲突

子系统E-R图之间的冲突主要有三类：

①属性冲突

•属性域冲突，即属性值的类型、取值范围或取值集合不同

例如零件号，有的部门把它定义为整数，有的部门把它定义为字符型

年龄，某些部门以出生日期形式表示职工的年龄，而另一些部门用整数表示职工的年龄

•属性取值单位冲突

例如，零件的重量有的以公斤为单位，有的以斤为单位，有的以克为单位

②命名冲突

•同名异义，即不同意义的对象在不同的局部应用中具有相同的名字

•异名同义（一义多名），即同一意义的对象在不同的局部应用中具有不同的名字

如对科研项目，财务科称为项目，科研处称为课题，生产管理处称为工程

命名冲突可能发生在实体、联系一级上,也可能发生在属性一级上,通过讨论、协商等行政手段加以解决

③结构冲突

•同一对象在不同应用中具有不同的抽象

例如，职工在某一局部应用中被当作实体，而在另一局部应用中则被当作属性

解决方法：把属性变换为实体或把实体变换为属性，使同一对象具有相同的抽象

•同一实体在不同子系统的E-R图中所包含的属性个数和属性排列次序不完全相同

解决方法：使该实体的属性取各子系统的E-R图中属性的并集，再适当调整属性的次序

•实体间的联系在不同的E-R图中为不同的类型

例如：实体E1与E2在一个E-R图中是多对多联系，在另一个E-R图中是一对多联系

解决方法：根据应用的语义对实体联系的类型进行综合或调整

(2)消除不必要的冗余，设计基本E-R图

所谓冗余的数据是指可由基本数据导出的数据，冗余的联系是指可由其他联系导出的联系

消除冗余主要采用分析方法，即以数据字典和数据流图为依据，根据数据字典中关于数据项之间逻辑关系的说明来消除冗余

并不是所有的冗余数据与冗余联系都必须加以消除，有时为了提高效率，不得不以冗余信息作为代价

除了分析方法外，还可以用规范化理论来消除冗余

①确定分E-R图实体之间的数据依赖

实体之间一对一、一对多、多对多的联系可以用实体码之间的函数依赖来表示。于是有函数依赖集 $F_{L}$

②求 $F_{L}$ 的最小覆盖 $G_{L}$ ，差集为 $D=F_{L}-G_{L}$

逐一考察 $D$ 中的函数依赖，确定是否是冗余的联系，若是，就把它去掉

由于规范化理论受到泛关系假设的限制，应注意下面两个问题：冗余的联系一定在D中，而D中的联系不一定是冗余的；当实体之间存在多种联系时，要将实体之间的联系在形式上加以区分

7.4 逻辑结构设计

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

7.4.1 E-R图向关系模型的转换

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

2.转换原则：一个实体型转换为一个关系模式，关系的属性就是实体的属性，关系的码就是实体的码

3. 实体型间的联系有以下不同情况

(1)一个1:1联系可以转换为一个独立的关系模式，也可以与任意一端对应的关系模式合并

① 如果转换为一个独立的关系模式

关系的属性：与该联系相连的各实体的码以及联系本身的属性

关系的候选码：每个实体的码均是该关系的候选码

②如果与某一端实体对应的关系模式合并

合并后关系的属性：加入对应关系的码和联系本身的属性

合并后关系的码：不变

(2)一个1:n联系可以转换为一个独立的关系模式，也可以与n端对应的关系模式合并

①如果转换为一个独立的关系模式

关系的属性：与该联系相连的各实体的码以及联系本身的属性

关系的码：n端实体的码

②如果与n端对应的关系模式合并(可以减少系统中的关系个数，一般情况下更倾向于采用这种方法)

合并后关系的属性：在n端关系中加入1端关系的码和联系本身的属性

合并后关系的码：不变

(3)一个m:n联系转换为一个关系模式

关系的属性：与该联系相连的各实体的码以及联系本身的属性

关系的码：各实体码的组合

(4)三个或三个以上实体间的一个多元联系转换为一个关系模式。

关系的属性：与该多元联系相连的各实体的码以及联系本身的属性

关系的码：各实体码的组合

(5)具有相同码的关系模式可合并

目的：减少系统中的关系个数

合并方法：将其中一个关系模式的全部属性加入到另一个关系模式中;然后去掉其中的同义属性（可能同名也可能不同名）;适当调整属性的次序

例：将下面的E-R图转换为关系模式为

部门（部门号，部门名，经理的职工号，…）

此为部门实体所对应的关系模式。该关系模式已经包含了联系“领导”所对应的关系模式。经理的职工号是关系的候选码

职工（职工号、部门号，职工名，职务，…）

此为职工实体所对应的关系模式。该关系模式已包含了联系“属于”所对应的关系模式

产品（产品号，产品名，产品组长的职工号，…）

供应商（供应商号，姓名，…）

零件（零件号，零件名，…）

职工工作（职工号，产品号，工作天数，…）

供应（产品号，供应商号，零件号，供应量）

7.4.2 数据模型的优化

优化数据模型的方法:

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

(2)对于各个关系模式之间的数据依赖进行极小化处理，消除冗余的联系

(3)按照数据依赖的理论对关系模式进行分析，考察是否存在部分函数依赖、传递函数依赖、多值依赖等，确定各关系模式分别属于第几范式

(4)按照需求分析阶段得到的各种应用对数据处理的要求，分析对于这样的应用环境这些模式是否合适，确定是否要对它们进行合并或分解

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

①水平分解

什么是水平分解：把(基本)关系的元组分为若干子集合，定义每个子集合为一个子关系，以提高系统的效率

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

②垂直分解

什么是垂直分解：把关系模式R的属性分解为若干子集合，形成若干子关系模式。

垂直分解的原则：经常在一起使用的属性从R中分解出来形成一个子关系模式

垂直分解的优点：可以提高某些事务的效率

垂直分解的缺点：可能使另一些事务不得不执行连接操作，降低了效率

垂直分解的适用范围：取决于分解后R上的所有事务的总效率是否得到了提高

进行垂直分解的方法

简单情况：直观分解

复杂情况：用第6章中的模式分解算法

垂直分解必须不损失关系模式的语义（保持无损连接性和保持函数依赖）

7.4.3 设计用户子模式

定义数据库全局模式主要是从系统的时间效率、空间效率、易维护等角度出发。

定义用户外模式(设计用户子模式)时应该更注重考虑用户的习惯与方便。包括三个方面：

(1)使用更符合用户习惯的别名

(2)可以对不同级别的用户定义不同的视图

(3)简化用户对系统的使用

7.5 物理结构的设计

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

数据库的物理设计通常分为两步：

(1)确定数据库的物理结构，在关系数据库中主要指存取方法和存储结构

(2)对物理结构进行评价，评价的重点是时间和空间的效率

7.5.1 数据库物理设计的内容和方法

1.设计物理数据库结构的准备工作

(1)充分了解应用环境，详细分析要运行的事务，以获得选择物理数据库设计所需参数

(2)充分了解所用关系型数据库管理系统的内部特征，特别是系统提供的存取方法和存储结构

2.选择物理数据库设计所需参数

(1)对于数据库查询事务，需要得到以下信息：

查询的关系

查询条件所涉及的属性

连接条件所涉及的属性

查询的投影属性

(2)对于数据更新事务，需要得到以下信息：

被更新的关系

每个关系上的更新操作条件所涉及的属性

修改操作要改变的属性值

(3)每个事务在各关系上运行的频率和性能要求

3.关系数据库物理设计的内容

(1)为关系模式选择存取方法（建立存取路径）

(2)设计关系、索引等数据库文件的物理存储结构

7.5.2 关系模式存取方法选择

数据库管理系统常用存取方法为索引方法和聚簇方法

1. B+树索引存取方法

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

①如果一个（或一组）属性经常在查询条件中出现，则考虑在这个（或这组）属性上建立索引（或组合索引）

②如果一个属性经常作为最大值和最小值等聚集函数的参数，则考虑在这个属性上建立索引

③如果一个（或一组）属性经常在连接操作的连接条件中出现，则考虑在这个（或这组）属性上建立索引

2.Hash索引存取方法

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

①该关系的大小可预知，而且不变

②该关系的大小动态改变，但所选用的数据库管理系统提供了动态Hash存取方法

3.聚簇存取方法

(1)聚簇：为了提高某个属性（或属性组）的查询速度，把这个或这些属性上具有相同值的元组集中存放在连续的物理块中称为聚簇。该属性（或属性组）称为聚簇码（cluster key）

说明：①聚簇对某些类型的查询，可以提高查询速率

②在一个基本表上只能建立一个聚簇索引

③聚簇索引的适用条件：很少对基表进行增删操作；很少对其中的变长列进行修改操作

(2)选择聚簇存取方法：

首先设计候选聚簇

①常在一起进行连接操作的关系可以建立组合聚簇

②如果一个关系的一组属性经常出现在相等比较条件中，则该单个关系可建立聚簇

③如果一个关系的一个（或一组）属性上的值重复率很高，则此单个关系可建立聚簇

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

①从聚簇中删除经常进行全表扫描的关系

②从聚簇中删除更新操作远多于连接操作的关系

③不同的聚簇中可能包含相同的关系，一个关系可以在某一个聚簇中，但不能同时加入多个聚簇。必须从这多个聚簇方案（包括不建立聚簇）中选择一个较优的，即在这个聚簇上运行各种事务的总代价最小

7.5.3 确定数据库的存储结构

确定数据库物理结构主要指确定数据的存放位置和存储结构，包括：确定关系、索引、聚簇、日志、备份等的存储安排和存储结构，确定系统配置等

确定数据的存放位置和存储结构要综合考虑存取时间、存储空间利用率和维护代价3个方面的因素。这三个因素往往是相互矛盾的，必须进行权衡选择一个折中的方案

1.确定数据的存放位置

基本原则：根据应用情况将易变部分与稳定部分分开存放；经常存取部分与存取频率较低部分分开存放

2.确定系统配置

关系数据库管理系统产品一般都提供了一些系统配置配置变量和存储分配参数，一开始系统为这些配置变量赋予了合理的缺省值（默认值），例如：同时使用数据库的用户数、同时打开的数据库对象数、内存分配参数、缓冲区分配参数（使用的缓冲区长度、个数）、存储分配参数、物理块的大小、物理块装填因子、时间片大小、数据库的大小、锁的数目等

在进行物理设计时需要根据应用环境确定这些参数值，以使系统性能最优

7.5.4 评价物理结构

评价方法：定量估算各种方案的：①存储空间

②存取时间

③维护代价

对估算结果进行权衡、比较，选择出一个较优的合理的物理结构

7.6 数据库的实施和维护

7.6.1 数据的载入和应用程序的调试

1.数据的载入

数据装载方法：①人工方法

②计算机辅助数据入库

为提高数据输入工作的效率和质量，应针对具体的应用环境设计一个数据录入子系统，由计算机来完成数据入库的任务。在源数据入库之前还要采用多种方法对其进行检验，以防止不正确的数据入库。若原来是数据库系统，就要充分利用新系统的数据转换工具

2.应用程序的调试

数据库应用程序的设计应该与数据库设计同时进行，因此在组织数据入库的同时还要调试应用程序

7.6.2 数据库的试运行

主要的工作：功能测试和性能测试。

说明：①数据的分期入库②做好数据的转储和恢复

7.6.3 数据库的运行和维护

主要工作：1.数据库的转储和恢复

2.数据库的安全性、完整性控制

3.数据库性能的监督、分析和改进

4.数据库的重组织和重构造

(1)重组织：重组织的工作：按原设计要求，重新安排存储位置，回收垃圾，减少指针链。数据库的重组织不会改变原设计的数据逻辑结构和物理结构

(2)重构造：数据库重构造的主要工作:根据新环境调整数据库的模式和内模式,增加或删除某些数据项,改变数据项的类型,增加或删除某个表,改变数据库的容量,增加或删除某些索引。重构造数据库的程度是有限的,若应用变化太大，已无法通过重构数据库来满足新的需求，或重构数据库的代价太大，则表明现有数据库应用系统的生命周期已经结束，应该重新设计新的数据库应用系统了

第8章数据库编程

8.1 嵌入式SQL

8.1.1 嵌入式SQL的处理过程

嵌入式SQL是将SQL语句嵌入程序设计语言中，被嵌入的程序设计语言，如C、C++、Java，称为宿主语言，简称主语言

为了区分SQL语句与主语言语句，所有SQL语句必须加前缀：

①主语言为C语言时，语句格式：

EXEC SQL <SQL语句>;

②主语言为Java,则嵌入式SQL称为SQLJ，语法格式为：

#SQL {<SQL语句>}

对嵌入式SQL，数据库管理系统一般采用预编译的处理方法，过程如下图：

8.1.2 嵌入式SQL语句与主语言之间的通信

1.将SQL嵌入到高级语言中混合编程，程序中会含有两种不同计算模型的语句：

(1)SQL语句

①描述性的面向集合的语句

②负责操纵数据库

(2)高级语言语句

①过程性的面向记录的语句

②负责控制逻辑流程

2.数据库工作单元与源程序工作单元之间的通信主要包括：

（1）向主语言传递SQL语句的执行状态信息，使主语言能够据此控制程序流程，主要用SQL通信区(SQL Communication Area,SQLCA)实现

（2）主语言向SQL语句提供参数，主要用主变量实现

（3）将SQL语句查询数据库的结果交主语言处理，主要用主变量和游标实现

3.SQL通信区

(1)SQLCA的用途

SQL语句执行后，系统反馈给应用程序信息：描述系统当前工作状态，描述运行环境。这些信息将送到SQL通信区中，应用程序从SQL通信区中取出这些状态信息，据此决定接下来执行的语句

(2)定义SQLCA

用EXEC SQL INCLUDE SQLCA定义

(3)使用SQLCA

SQLCA中有一个存放每次执行SQL语句后返回代码的变量SQLCODE。如果SQLCODE等于预定义的常量SUCCESS，则表示SQL语句成功，否则表示出错。应用程序每执行完一条SQL 语句之后都应该测试一下SQLCODE的值，以了解该SQL语句执行情况并做相应处理

4.主变量

嵌入式SQL语句中可以使用主语言的程序变量来输入或输出数据。在SQL语句中使用的主语言程序变量简称为主变量

(1)主变量的类型

输入主变量:由应用程序对其赋值，SQL语句引用

输出主变量:由SQL语句对其赋值或设置状态信息，返回给应用程序

指示变量:是一个整型变量，用来“指示”所指主变量的值或条件。一个主变量可以附带一个指示变量（Indicator Variable）。指示变量的用途：指示输入主变量是否为空值；检测输出变量是否为空值，值是否被截断

(2)在SQL语句中使用主变量和指示变量的方法

①说明主变量和指示变量

BEGIN DECLARE SECTION

...

... （在省略号里说明主变量和指示变量，方法和主语言定义变量一样）

...

END DECLARE SECTION

②使用主变量

说明之后的主变量可以在SQL语句中任何一个能够使用表达式的地方出现

为了与数据库对象名（表名、视图名、列名等）区别，SQL语句中的主变量名前要加冒号":"作为标志

③使用指示变量

指示变量前也必须加冒号标志

必须紧跟在所指主变量之后

5.游标(cursor)

(1)为什么要使用游标
数据库查询一般查出的都不是单个数据，而是集合。一个主变量一次只能储存一个数据，因此需要借助游标来协调两者之间的关系。相当于一个指针。

(2)什么是游标

游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。每个游标区都有一个名字。用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理

6.建立和关闭数据库连接

(1)建立数据库连接

EXEC SQL CONNECT TO target[AS connection-name][USER user-name];

target是要连接的数据库服务器。常见的服务器标识串，如①<dbname>@<hostname>:<port> ②包含服务器标识的SQL串常量 ③DEFAULT

connection-name是可选的连接名，连接名必须是一个有效的标识符。在整个程序内只有一个连接时可以不指定连接名

程序运行过程中可以修改当前连接：

EXEC SQL SET CONNECTION connection-name|DEFAULT;

(2)关闭数据库连接

格式：EXEC SQL DISCONNECT [connection]；其中connection是EXEC SQL CONNECT所建立的数据库连接

例：查询数据库里的学生信息并修改年龄

EXEC SQL BEGIN DECLARE SECTION;    /*主变量说明开始*/ 
	char deptname[20];
	char hsno[9];
	char hsname[20]; 
	char hssex[2];
	int HSage;
	int NEWAGE;
EXEC SQL END DECLARE SECTION;       /*主变量说明结束*/
long SQLCODE;
EXEC SQL INCLUDE SQLCA;               /*定义SQL通信区*/
int main(void)                           		/*C语言主程序开始*/
{
	int  count = 0;
	char  yn;                              		/*变量yn代表yes或no*/
	printf("Please choose the department name(CS/MA/IS): "); 
	scanf("%s",deptname);                 	/*为主变量deptname赋值*/
	EXEC SQL CONNECT TO TEST@localhost:54321 USER "SYSTEM"/"MANAGER";         /*连接数据库TEST*/
	EXEC SQL DECLARE SX CURSOR FOR      /*定义游标SX*/
		SELECT Sno,Sname,Ssex,Sage     /*SX对应的语句*/
		FROM Student
		WHERE SDept = :deptname;
	EXEC SQL OPEN SX;       /*打开游标SX，指向查询结果的第一行*/
for ( ; ; )                      			/*用循环结构逐条处理结果集中的记录*/
{ 
	EXEC SQL FETCH SX INTO :HSno,:Hsname,:HSsex,:HSage;/*推进游标，将当前数据放入主变量*/
	if (SQLCA.SQLCODE!= 0)    	 /*SQLCODE != 0，表示操作不成功*/
		break;           	 /*利用SQLCA中的状态信息决定何时退出循环*/
	if(count++ == 0)             	      /*如果是第一行的话，先打出行头*/
	        printf("\n%-10s %-20s %-10s %-10s\n","Sno“,"Sname“,"Ssex", "Sage");/*%-10s表示左对齐占10个字符,/s是字符串*/
	printf("%-10s %-20s %-10s %-10d\n“,HSno,Hsname,Hssex,HSage);                 /*打印查询结果*/
	printf(“UPDATE AGE(y/n)?”);    /*询问用户是否要更新该学生的年龄*/
	do{scanf("%c",&yn);}           /*%c表示单个字符输出的意思*/
	while(yn != 'N' && yn != 'n' && yn != 'Y' && yn != 'y');
          if (yn == 'y' || yn == 'Y')                          /*如果选择更新操作*/
	  {
              printf("INPUT NEW AGE:");
	          scanf("%d",&NEWAGE);       /*用户输入新年龄到主变量中*/
              EXEC SQL UPDATE Student          /*嵌入式SQL更新语句*/
	              SET Sage = :NEWAGE
		      WHERE CURRENT OF SX;        /*对当前游标指向的学生年龄进行更新*/
	    }       	                 
 }//for循环结束
  EXEC SQL CLOSE SX;               /*关闭游标SX，不再和查询结果对应*/
  EXEC SQL COMMIT WORK;                                     /*提交更新*/
  EXEC SQL DISCONNECT TEST;                         /*断开数据库连接*/
}

8.1.3 不用游标的SQL语句

1.不用游标的SQL语句的种类

(1)说明性语句

(2)数据定义语句

(3)数据控制语句

(4)查询结果为单记录的SELECT语句

这类语句不需要使用游标，只需用INTO子句指定存放查询结果的主变量。

例:根据学生号码查询学生信息

EXEC SQL SELECT Sno,Sname,Ssex,Sage,Sdept 
    INTO :Hsno,:Hname,:Hsex,:Hage,:Hdept
    FROM Student
WHERE Sno=:givensno;/*把要查询的学生的学号赋给为了主变量givensno*/

说明：①INTO子句、WHERE子句和HAVING短语的条件表达式中均可以使用主变量

②查询返回的记录中，可能某些列为空值NULL。为了表示空值，在INTO子句的后面跟有指示变量，当查询得出的某个数据项为空值时系统会自动将相应的主变量后面的指示变量设置为负值，而不在向该主变量赋值。所以当指示变量为负值时不论主变量为何值，均认为主变量值位NULL

③如果查询结果实际上并不是单条记录，而是多条记录，则程序出错，关系数据库管理系统会在SQLCA中返回错误信息

(5)非CURRENT形式的增删改语句

在UPDATE的SET子句和WHERE子句中可以使用主变量，SET子句还可以使用指示变量

8.1.4 使用游标的SQL语句

必须使用游标的SQL语句为查询结果为多条记录的SELECT语句、CURRENT形式的UPDATE语句和DELETE语句

1.查询结果为多条记录的SELECT语句

使用游标的步骤：Ⅰ.说明游标

语句格式：EXEC SQL DECLARE <游标名> CURSOR FOR <SELECT语句>;

功能：是一条说明性语句,这时关系数据库管理系统并不执行SELECT语句

Ⅱ.打开游标

语句格式：EXEC SQL OPEN <游标名>;

功能：打开游标实际上是执行相应的SELECT语句,把查询结果取到缓冲区
中这时游标处于活动状态，指针指向查询结果集中的第一条记录

Ⅲ.推进游标指针并取当前记录

语句格式：EXEC SQL FETCH <游标名> INTO
<主变量>[<指示变量>][,<主变量>[<指示变量>]]...;

功能：指定方向推动游标指针，同时将缓冲区中的当前记录取出来送至
主变量供主语言进一步处理

Ⅳ.关闭游标

语句格式：EXEC SQL CLOSE <游标名>;

功能：关闭游标，释放结果集占用的缓冲区及其他资源

说明：游标被关闭后，就不再和原来的查询结果集相联系被关闭的游标可
以再次被打开，与新的查询结果相联系

2.CURRENT形式的UPDATE语句和DELETE语句

UPDATE语句和DELETE语句中要用子句：WHERE CURRENT OF <游标名>来表示修改或删除的是最近一次取出的记录，即游标指针指向的记录

当游标定义中的SELECT语句带有UNION或ORDER BY子句，或者该SELECT语句相当于定义了一个不可更新的视图，不能使用CURRENT形式的UPDATE语句和DELETE语句

8.1.5 动态SQL

前面所讲的嵌入式SQL语句中使用的主变量、查询目标列、条件等都是固定的，属于静态SQL语句

静态嵌入式SQL：静态嵌入式SQL语句能够满足一般要求，无法满足要到执行时才能够确定要提交的SQL语句、查询的条件

动态嵌入式SQL：允许在程序运行过程中临时“组装”SQL语句。支持动态组装SQL语句和动态参数两种形式

动态SQL

1. 使用SQL语句主变量

程序主变量包含的内容是SQL语句的内容，而不是原来保存数据的输入或输出变量，这样的变量被称为SQL语句主变量。 SQL语句主变量在程序执行期间可以设定不同的SQL语句，然后立即执行

例：创建基本表test

	EXEC SQL BEGIN DECLARE SECTION;//声明是嵌入式SQL，定义主变量
    		   const char *stmt="CREATE TABLE test(a int);";  /*SQL语句主变量，内容是创建表的SQL语句*/
	EXEC SQL END DECLARE SECTION;
    	... 
	EXEC SQL EXECUTE IMMEDIATE :stmt;		/*执行动态SQL语句*/

2. 动态参数

(1)SQL语句中的可变元素

使用参数符号（?）表示该位置的数据在运行时设定

(2)和主变量的区别

动态参数的输入不是编译时完成绑定，而是通过 PREPARE语句准备主变量和执行语句EXECUTE绑定数据或主变量来完成

(3)使用动态参数的步骤

①声明SQL语句主变量

SQL语句主变量的值包含动态参数(?)

②准备SQL语句（PREPARE）

PREPARE将分析含主变量的SQL语句内容，建立语句中包含的动态参数的内部描述符，并用<语句名>表示它们的主体

EXEC SQL PREPARE <语句名> FROM <SQL语句主变量>;

③执行准备好的语句（EXECUTE）

EXECUTE将SQL语句中分析出的动态参数和主变量或数据常量绑定，作为语句的输入或输出变量

EXEC SQL EXECUTE <语句名> [INTO <主变量表>] [USING <主变量或常量>];//用using指
明（?）的内容

例：向TEST中插入元组

EXEC SQL BEGIN DECLARE SECTION;

    const char *stmt = "INSERT INTO test VALUES(?);";/*声明SQL主变量内容是INSERT语句 */

EXEC SQL END DECLARE SECTION;

...

EXEC SQL PREPARE mystmt FROM :stmt; /*准备语句*/

...

EXEC SQL EXECUTE mystmt USING 100;/*执行语句，设定INSERT语句插入值100 */

EXEC SQL EXECUTE mystmt USING 200; /* 执行语句，设定INSERT语句插入值200 */

8.2 过程化SQL

SQL可以使用程序设计语言来定义过程和函数，也可以用关系数据库管理系统自己的过程语言来定义

8.2.1 过程化SQL的块结构

基本的SQL是高度非过程化的语言。嵌入式SQL将SQL语句嵌入程序设计语言借助高级语言的控制功能实现过程化。过程化SQL程序的基本结构是块。所有的过程化SQL程序都是由块组成的，这些块之间可以互相嵌套，每个块完成一个逻辑操作

8.2.2 变量和常量的定义

1. 变量定义

变量名数据类型 [[NOT NULL]:=初值表达式]或

变量名数据类型 [[NOT NULL] 初值表达式]

2. 常量定义

常量名数据类型 CONSTANT :=常量表达式

常量必须要给一个值，并且该值在存在期间或常量的作用域内不能改变。如果试图修改它，过程化SQL将返回一个异常

3. 赋值语句

变量名称 :=表达式

8.2.3 流程控制

1. 条件控制语句

IF-THEN，IF-THEN-ELSE和嵌套的IF语句

(1) IF语句

IF condition THEN

Sequence_of_statements; /*条件为真时语句序列才被执行*/

END IF; /*条件为假或NULL时什么也不做，控制转移至下一个语句*/

(2)IF THEN语句

IF condition THEN

Sequence_of_statements1; /*条件为真时执行语句序列1*/

ELSE

Sequence_of_statements2; /*条件为假或NULL时执行语句序列2*/

END IF;

(3)在THEN和ELSE子句中还可以再包含IF语句，即IF语句可以嵌套

2. 循环控制语句

LOOP，WHILE-LOOP和FOR-LOOP

(1)最简单的循环语句LOOP

LOOP

Sequence_of_statements; /*循环体，一组过程化SQL语句*/

END LOOP;

多数数据库服务器的过程化SQL都提供EXIT、BREAK或LEAVE等循环结束语句，保证LOOP语句块能够结束

(2)WHILE-LOOP循环语句

WHILE condition LOOP

Sequence_of_statements; /*条件为真时执行循环体内的语句序列*/

END LOOP;

每次执行循环体语句之前，首先对条件进行求值,如果条件为真，则执行循环体内的语句序列,如果条件为假，则跳过循环并把控制传递给下一个语句

(3)FOR-LOOP循环语句

FOR count IN [REVERSE] bound1 … bound2 LOOP

Sequence_of_statements;

END LOOP;

FOR循环的基本执行过程是：将count设置为循环的下界bound1，检查它是否小于上界bound2.当指定REVERSE时则将count设置为循环的上界bound2，检查count是否大于下界bound1。如果越界则执行跳出循环。否则执行循环体，然后按照步长（+1或-1）更新count值重新判断条件

3. 错误处理

如果过程化SQL在执行时出现异常，则应该让程序在产生异常的语句处停下来，根据异常的类型去执行异常处理语句

SQL标准对数据库服务器提供什么样的异常处理做出了建议，要求过程化SQL管理器提供完善的异常处理机制

8.3 存储过程和函数

8.3.1 存储过程

1.过程化SQL块类型

(1)命名块:编译后保存在数据库中，可以被反复调用，运行速度较快，过程和函数是命名块

(2)匿名块:每次执行时都要进行编译,它不能被存储到数据库中,也不能在其他过程化SQL块中调用

2.存储过程：由过程化SQL语句书写的过程，经编译和优化后存储在数据库服务器中，使用时只要调用即可

3.存储过程的用户接口

(1)创建存储过程

CREATE OR REPLACE PROCEDURE 过程名([参数1,参数2,...]) AS <过程化SQL块>；

过程名：数据库服务器合法的对象标识

参数列表：用名字来标识调用时给出的参数值，必须指定值的数据类型。参数也可以定义输入参数、输出参数或输入/输出参数，默认为输入参数，也可以无参数

过程体：是一个<过程化SQL块>，包括声明部分和可执行语句部分

例：利用存储过程来实现下面的应用：从账户1转指定数额的款项到账户2中

CREATE OR REPLACE PROCEDURE TRANSFER(inAccount INT,outAccount  INT,amount FLOAT) /*定义存储过程TRANSFER，其参数为转入账户、转出账户、转账额度*/
AS DECLARE		/*定义变量*/
	    totalDepositOut Float;
        totalDepositIn Float;
	    inAccountnum INT;
BEGIN                         	    /*检查转出账户的余额 */	                       
	    SELECT Total INTO totalDepositOut 
            FROM Account 
	    WHERE accountnum=outAccount; 
	        IF totalDepositOut IS NULL THEN   /*如果转出账户不存在或账户中没有存款*/
	            ROLLBACK; 	   /*回滚事务*/
	            RETURN;
	        END IF; 
            IF totalDepositOut < amount THEN    	/*如果账户存款不足*/
	            ROLLBACK; 				/*回滚事务*/
	            RETURN;
            END IF;
        SELECT Accountnum INTO inAccountnum 
            FROM Account
        WHERE accountnum=inAccount;
        IF inAccount IS NULL THEN  		/*如果转入账户不存在*/                        
	        ROLLBACK; 	         	 		/*回滚事务*/
	        RETURN;
        ENDIF;
        UPDATE Account SET total=total-amount 
            WHERE accountnum=outAccount;/* 修改转出账户余额，减去转出额 */
        UPDATE Account SET total=total + amount 
            WHERE   accountnum=inAccount; /* 修改转入账户余额，增加转入额 */
        COMMIT;                       	/* 提交转账事务 */
END;

(2)执行存储过程

CALL/PERFORM PROCEDURE 过程名([参数1,参数2,...])；

使用CALL或者PERFORM等方式激活存储过程的执行

在过程化SQL中，数据库服务器支持在过程体中调用其他存储过程

例：从账户01003815868转10000元到01003813828账户中

CALL PROCEDURE TRANSFER(01003813828,01003815868,10000);

(3)修改存储过程

重新命名存储过程：ALTER PROCEDURE 过程名1 RENAME TO 过程名2;

重新编译一个存储过程：ALTER PROCEDURE 过程名 COMPILE;

(4)删除存储过程

DROP PROCEDURE 过程名()；

8.3.2 函数

1.函数和存储过程的异同

同：都是持久性存储模块

异：函数必须指定返回的类型

2. 函数的定义语句格式

CREATE OR REPLACE FUNCTION 函数名 ([参数1,参数2,…]) RETURNS <类型> AS <过程化SQL块>;

3. 函数的执行语句格式

CALL/SELECT 函数名 ([参数1,参数2,…]);

4. 修改函数

重命名：ALTER FUNCTION 过程名1 RENAME TO 过程名2;

重新编译：ALTER FUNCTION 过程名 COMPILE;

8.3.3 过程化SQL中的游标

当查询结果返回多条记录时，就要使用游标对结果进行处理。在存储过程种可以定义普通游标、REFCURSOR类型游标和带参数的游标。

例：定义一个存储过程，多次打开游标并获取游标的当前记录

CREATE OR REPLACE PROCEDURE proc_cursor() AS
DECLARE
        cno CHAR(3)
        cname CHAR(8)
CURSOR mycursor(leaderno CHAR(3))FOR                      //说明带参数游标mycurcor
        SELECT Ino,lname FROM leader WHERE lno=leadreno;
BEGIN                                                     //mycurcor能检索leader表中具有参数leaderno的记录
        OPEN mycoursor('L01');                            //使用参数L01打开游标
        FETCH mycursor INTO cno,cname;                    //获取lno='L01'的游标元组
        INSERT INTO temp (lno,lname) VALUES(cno,cname);   //将游标元组插入临时表中
        CLOSE mycurcor;                                   //关闭游标
        OPEN mycurcor('L02');                             //使用新的参数'L02'重新打开游标
        FETCH mycurcor INTO cno,cname;
        INSERT INTO temp(lno,lname) VALUES(cno,cname);
        CLOSE mycurcor;
END;

8.4 ODBC编程

提出和产生ODBC的原因是存在不同的数据库管理系统。ODBC优点：移植性好；能同时访问不同的数据库；共享多个数据资源

8.4.1 ODBC概述

为了使数据库系统“开放”，能够实现“数据库互连”，微软公司推出了一个接口标准ODBC，ODBC提供了一组访问数据库的应用程序编程接口（Application Programming Interface，API ）以规范应用开发，规范关系数据库管理系统应用接口

8.4.2 ODBC工作原理概述

ODBC应用系统的体系结构如下图，包括4部分：用户应用程序、ODBC驱动程序管理器、数据库驱动程序、数据源

1. 用户应用程序

用户应用程序的主要工作：

①请求连接数据库

②向数据源发送SQL语句

③为SQL语句执行结果分配存储空间，定义所读取的数据格式

④获取数据库操作结果或处理错误

⑥进行数据处理并向用户提交处理结果

⑦请求事务的提交和回滚操作

⑧断开与数据源的连接

2. ODBC驱动程序管理器

驱动程序管理器：用来管理应用程序和驱动程序之间的通信，对用户透明

主要功能：①装载ODBC驱动程序

②选择和连接正确的驱动程序

③管理数据源

④检查ODBC调用参数的合法性

⑤记录ODBC函数的调用等

3. 数据库驱动程序

ODBC通过驱动程序来提供应用系统与数据库平台的独立性。ODBC应用程序不能直接存取数据库，其各种操作请求由驱动程序管理器提交给某个关系数据库管理系统的ODBC驱动程序，通过调用驱动程序所支持的函数来存取数据库，数据库的操作结果也通过驱动程序返回给应用程序，如果应用程序要操纵不同的数据库，就要动态地链接到不同的驱动程序上

ODBC驱动程序类型：

①单束:数据源和应用程序在同一台机器上

驱动程序直接完成对数据文件的I/O操作

驱动程序相当于数据管理器

②多束:支持客户机-服务器、客户机-应用服务器/数据库服务器等网络环境下的数据访问

由驱动程序完成数据库访问请求的提交和结果集接收

应用程序使用驱动程序提供的结果集管理接口操纵执行后的结果数据

4. 数据源

(1)数据源：是最终用户需要访问的数据，包含了数据库位置和数据库类型等信息，是一种数据连接的抽象

(2)数据源对最终用户是透明的

ODBC给每个被访问的数据源指定唯一的数据源名（Data Source Name，简称DSN），并映射到所有必要的、用来存取数据的低层软件

在连接中，用数据源名来代表用户名、服务器名、所连接的数据库名等

最终用户无须知道数据库管理系统或其他数据管理软件、网络以及有关ODBC驱动程序的细节

8.4.3 ODBC API 基础

1.各个数据厂商的ODBC 应用程序编程接口都要符合两方面的一致性：

(1)API一致性:包含核心级、扩展1级、扩展2级

(2)语法一致性:包含最低限度SQL语法级、核心SQL语法级、扩展SQL语法级

2.函数概述

ODBC 3.0 标准提供了76个函数接口，大致分为：

①分配和释放环境句柄、连接句柄、语句句柄

②连接函数（SQLDriverconnect等）

③与信息相关的函数（SQLGetinfo、SQLGetFuction等）

④事务处理函数（如SQLEndTran）

⑤执行相关函数（SQLExecdirect、SQLExecute等）

⑥编目函数，ODBC 3.0提供了11个编目函数，如SQLTables、SQLColumn等。应用程序可以通过对编目函数的调用来获取数据字典的信息，如权限、表结构等

3.句柄及其属性

句柄是32位整数值，代表一个指针。句柄分类：环境句柄、连接句柄、语句句柄、描述符句柄

说明：(1)每个ODBC应用程序需要建立一个ODBC环境，分配一个环境句柄，存取数据的全局性背景，如环境状态、当前环境状态诊断、当前在环境上分配的连接句柄等

(2)一个环境句柄可以建立多个连接句柄，每一个连接句柄实现与一个数据源之间的连接

(3)在一个连接中可以建立多个语句句柄，它不只是一个SQL语句，还包括SQL语句产生的结果集以及相关的信息等

(4)在ODBC 3.0中又提出了描述符句柄的概念，它是描述SQL语句的参数、结果集列的元数据集合

4.ODBC数据类型

SQL数据类型：用于数据源

C数据类型：用于应用程序的C代码

SQL数据通过SQLBindcol从结果集列中返回到应用程序变量；如果SQL语句含有参数，应用程序为每一个参数调用SQLBindparameter，并把它们绑定至应用程序变量。应用程序可以通过SQL GetTypeInfo来获取不同的驱动程序对于数据类型的支持情况

8.4.4 ODBC的工作流程

例：将KingbaseES数据库中Student表的数据备份到SQL Server数据库中

该应用涉及两个不同的RDBMS中的数据源,使用ODBC来开发应用程序，只要改变应用程序中连接函数（SQLConnect）的参数，就可以连接不同RDBMS的驱动程序，连接两个数据源。在应用程序运行前，已经在KingbaseES和SQL SERVER中分别建立了STUDENT关系表

CREATE TABLE Student

(Sno CHAR(9) PRIMARY KEY,

Sname CHAR(20) UNQUE

Ssex CHAR(2),

Sage SMALLINT,

Sdept CHAR(20)

);

应用程序要执行的操作是：在KingbaseES上执行SELECT * FROM STUDENT；把获取的结果集，通过多次执行INSERT INTO STUDENT （Sno,Sname,Ssex,Sage,Sddept） VALUES （?,?,?, ?,?);插入到SQL SERVER的STUDENT表中

1.配置数据源

配置数据源两种方法:(1)运行数据源管理工具来进行配置；

(2)使用Driver Manager提供的ConfigDsn函数来增加、修改或删除数据源

在本例中采用了第一种方法创建数据源。因为要同时用到KingbaseES和SQL Server，所以分别建立两个数据源，将其取名为KingbaseES ODBC和SQLServer

2.初始化环境

没有和具体的驱动程序相关联，由Driver Manager来进行控制，并配置环境属性

应用程序通过调用连接函数和某个数据源进行连接后，Driver Manager才调用所连的驱动程序中的SQLAllocHandle，来真正分配环境句柄的数据结构

3.建立连接

应用程序调用SQLAllocHandle分配连接句柄，通过SQLConnect、SQLDriverConnect或SQLBrowseConnect与数据源连接。SQLConnect连接函数，输入参数为：配置好的数据源名称，用户ID，口令。本例中KingbaseES ODBC为数据源名字，SYSTEM为用户名，MANAGER为用户密码

4.分配语句句柄

处理任何SQL语句之前，应用程序还需要首先分配一个语句句柄。语句句柄含有具体的SQL语句以及输出的结果集等信息

本例中分配了两个语句句柄：一个用来从KingbaseES中读取数据产生结果集(kinghstmt)，一个用来向SQLSERVER插入数据（serverhstmt）。应用程序还可以通过SQLtStmtAttr来设置语句属性（也可以使用默认值）。本例结果集绑定的方式为按列绑定

5.执行SQL语句

应用程序处理SQL语句的两种方式：(1)预处理（SQLPrepare、SQLExecute适用于语句的多次执行)
(2)直接执行（SQLExecdirect）

如果SQL语句含有参数，应用程序为每个参数调用SQLBindParameter，并把它们绑定至应用程序变量，应用程序可以直接通过改变应用程序缓冲区的内容从而在程序中动态的改变SQL语句的具体执行

应用程序根据语句的类型进行的处理：有结果集的语句（select或是编目函数），则进行结果集处理。没有结果集的函数，可以直接利用本语句句柄继续执行新的语句或是获取行计数（本次执行所影响的行数）之后继续执行。

在本例中，使用SQLExecdirect获取KingbaseES中的结果集，并将结果集根据各列不同的数据类型绑定到用户程序缓冲区

在插入数据时，采用了预编译的方式，首先通过SQLPrepare来预处理SQL语句，将每一列绑定到用户缓冲区

应用程序可以直接修改结果集缓冲区的内容

6.结果集处理

应用程序可以通过SQLNumResultCols来获取结果集中的列数;通过SQLDescribeCol或是SQLColAttrbute函数来获取结果集每一列的名称、数据类型、精度和范围\

ODBC中使用游标来处理结果集数据。ODBC中游标类型：

(1)forward-only游标，是ODBC的默认游标类型

(2)可滚动（scroll）游标：①静态（static）②动态（dynamic）③码集驱动（keyset-driven）④混合型（mixed）

结果集处理步骤：(1)ODBC游标打开方式不同于嵌入式SQL，不是显式声明而是系统自动产生一个游标（Cursor），当结果集刚刚生成时，游标指向第一行数据之前

(2)应用程序通过SQLBindCol，把查询结果绑定到应用程序缓冲区中，通过SQLFetch或是SQLFetchScroll来移动游标获取结果集中的每一行数据

(3)对于如图像这类特别的数据类型当一个缓冲区不足以容纳所有的数据时，可以通过SQLGetdata分多次获取

· (4)最后通过SQLClosecursor来关闭游标

7.中止处理

应用程序中止步骤：首先释放语句句柄;释放数据库连接;与数据库服务器断开;释放ODBC环境

#include <stdlib.h>
#include <stdio.h>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <Sqltypes.h>
#define SNO_LEN 30
#define NAME_LEN 50
#define DEPART_LEN 100
#define SSEX_LEN 5
int main()
{
	/* Step 1 定义句柄和变量 */
	//以king开头的表示的是连接KingbaseES的变量
	//以server开头的表示的是连接SQLSERVER的变量
	SQLHENV kinghenv,serverhenv;                   	//环境句柄
	SQLHDBC kinghdbc,serverhdbc;                	//连接句柄
	SQLHSTMT kinghstmt，serverhstmt;	            //语句句柄
	SQLRETURN ret;
	SQLCHAR sName[NAME_LEN], sDepart[DEPART_LEN], sSex[SSEX_LEN], sSno[SNO_LEN];
	SQLINTEGER sAge;
	SQLINTEGER cbAge=0, cbSno=SQL_NTS, cbSex=SQL_NTS, cbName=SQL_NTS, cbDepart=SQL_NTS;
    /* Step 2 初始化环境 */
	ret=SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &kinghenv);
	ret=SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &serverhenv);
	ret=SQLSetEnvAttr(kinghenv,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
	ret=SQLSetEnvAttr(serverhenv,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
    /* Step 3 :建立连接 */
    ret=SQLAllocHandle(SQL_HANDLE_DBC,kinghenv,&kinghdbc);
    ret=SQLAllocHandle(SQL_HANDLE_DBC,serverhenv,&serverhdbc);
    ret=SQLConnect(kinghdbc,"KingbaseES ODBC",SQL_NTS,"SYSTEM",SQL_NTS,"MANAGER",SQL_NTS);
    if (!SQL_SUCCEEDED(ret))            //连接失败时返回错误值
	    return-1;
    ret=SQLConnect(serverhdbc,"SQLServer",SQL_NTS,"sa",SQL_NTS,"sa",SQL_NTS);
    if (!SQL_SUCCEEDED(ret))		    //连接失败时返回错误值
	    return -1;
    /* Step 4 :初始化语句句柄 */
    ret=SQLAllocHandle(SQL_HANDLE_STMT,kinghdbc,&kinghstmt);
    ret=SQLSetStmtAttr(kinghstmt,SQL_ATTR_ROW_BIND_TYPE,(SQLPOINTER)
                       SQL_BIND_BY_COLUMN,SQL_IS_INTEGER);
    ret=SQLAllocHandle(SQL_HANDLE_STMT,serverhdbc,&serverhstmt);
    /* Step 5 :两种方式执行语句 */
    /* 预编译带有参数的语句 */
    ret=SQLPrepare(serverhstmt,"INSERT INTO STUDENT(SNO,SNAME,SSEX,SAGE,SDEPT) VALUES (?,?,?,?,?)",SQL_NTS);
    if (ret==SQL_SUCCESS || ret==SQL_SUCCESS_WITH_INFO)
    {
        ret=SQLBindParameter(serverhstmt,1,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,SNO_LEN,0,sSno, 0,&cbSno); 
        ret=SQLBindParameter(serverhstmt,2,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,NAME_LEN,0,sName,0,&cbName);
        ret=SQLBindParameter(serverhstmt,3,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,2,0,sSex,0,&cbSex);
        ret=SQLBindParameter(serverhstmt,4,SQL_PARAM_INPUT,SQL_C_LONG,SQL_INTEGER,0,0,&sAge， 0， &cbAge);
        ret=SQLBindParameter(serverhstmt,5,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,DEPART_LEN,0,sDepart,0,&cbDepart);
    }
    /*执行SQL语句*/
    ret=SQLExecDirect(kinghstmt,"SELECT * FROM STUDENT",SQL_NTS);
    if (ret==SQL_SUCCESS || ret==SQL_SUCCESS_WITH_INFO) 
    {
        ret=SQLBindCol(kinghstmt,1,SQL_C_CHAR,sSno,SNO_LEN,&cbSno);
        ret=SQLBindCol(kinghstmt,2,SQL_C_CHAR,sName,NAME_LEN,&cbName);
        ret=SQLBindCol(kinghstmt,3,SQL_C_CHAR,sSex,SSEX_LEN,&cbSex);
        ret=SQLBindCol(kinghstmt,4,SQL_C_LONG,&sAge,0,&cbAge);
        ret=SQLBindCol(kinghstmt,5,SQL_C_CHAR,sDepart,DEPART_LEN,&cbDepart);
    }

    /* Step 6 ：处理结果集并执行预编译后的语句*/
     while ( (ret=SQLFetch(kinghstmt) ) !=SQL_NO_DATA_FOUND) 
     {  
		if(ret==SQL_ERROR) printf("Fetch error＼n");
		else ret=SQLExecute(serverhstmt);
    }
    /* Step 7 中止处理*/
    SQLFreeHandle(SQL_HANDLE_STMT，kinghstmt);
    SQLDisconnect(kinghdbc);
    SQLFreeHandle(SQL_HANDLE_DBC,kinghdbc);
    SQLFreeHandle(SQL_HANDLE_ENV,kinghenv);
    SQLFreeHandle(SQL_HANDLE_STMT,serverhstmt);
    SQLDisconnect(serverhdbc);
    SQLFreeHandle(SQL_HANDLE_DBC,serverhdbc);
    SQLFreeHandle(SQL_HANDLE_ENV,serverhenv);
    return 0;
    }

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

9.1 关系数据库系统的查询处理

9.1.1 查询处理步骤

1.查询分析

词法分析：从查询语句中识别出正确的语言符号

语法分析：进行语法检查

2.查询检查

(1)合法性检查：根据数据字典中有关的模式定义检查语句中的数据库对象，如关系名、属性名是否存在和有效

(2)视图转换：如果是对视图的操作，则要用视图消解方法把对视图的操作转换成对基本表的操作

(3)安全性和完整性初步检查：根据数据字典中的用户权限和完整性约束定义对用户的存取权限进行检查

检查通过后把SQL查询语句转换成内部表示，即等价的关系代数表达式。关系数据库管理系统一般都用查询树，也称为语法分析树来表示扩展的关系代数表达式

3.查询优化

(1)查询优化：选择一个高效执行的查询处理策略

(2)查询优化分类

代数优化/逻辑优化：指关系代数表达式的优化

物理优化：指存取路径和底层操作算法的选择

(3)查询优化的选择依据

①基于规则(rule based)

②基于代价(cost based)

③基于语义(semantic based)

4.查询操作

依据优化器得到的执行策略生成查询执行计划，代码生成器(code generator)生成执行查询计划的代码。两种执行方法：①自顶向下②自底向上

9.1.2 实现查询操作的算法示例

1.选择操作典型实现方法：

（1)全表扫描方法 (Table Scan)

对查询的基本表顺序扫描，逐一检查每个元组是否满足选择条件，把满足条件的元组作为结果输出。适合小表，不适合大表

(2)索引扫描方法 (Index Scan)

适合于选择条件中的属性上有索引(例如B+树索引或Hash索引) 。通过索引先找到满足条件的元组主码或元组指针，再通过元组指针直接在查询的基本表中找到元组

2.连接操作的实现

连接操作是查询处理中最耗时的操作之一,本节只讨论等值连接(或自然连接)最常用的实现算法

例：SELECT * FROM Student,SC WHERE Student.Sno=SC.Sno

(1)嵌套循环算法(nested loop join)

①对外层循环(Student表)的每一个元组(s)，检索内层循环(SC表)中的每一个元组(sc)

②检查这两个元组在连接属性(Sno)上是否相等

③如果满足连接条件，则串接后作为结果输出，直到外层循环表中的元组处理完为止

(2)排序-合并算法(sort-merge join 或merge join)

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

②取Student表中第一个Sno，依次扫描SC表中具有相同Sno的元组

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

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

(3)索引连接(index join)算法

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

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

③ 把这些SC元组和Student元组连接起来

④循环执行②③，直到Student表中的元组处理完为止

(4)Hash Join算法

把连接属性作为hash码，用同一个hash函数把Student表和SC表中的元组散列到hash表中

第一阶段：划分阶段(building phase, 也称为创建阶段partitioning phase)

对包含较少元组的表(如Student表)进行一遍处理，把它的元组按hash函数分散到hash表的桶中

第二阶段：试探阶段(probing phase,也称为连接阶段join phase)

对另一个表(SC表)进行一遍处理，把SC表的元组也按同一个hash函数（hash码是连接属性）进行散列，把SC元组与桶中来自Student表并与之相匹配的元组连接起来

算法前提：假设两个表中较小的表在第一阶段后可以完全放入内存的hash桶中

9.2 关系数据库系统的查询优化

9.2.1查询优化概述

1.用户不必考虑如何最好地表达查询以获得较好的效率；系统可以比用户程序的“优化”做得更好。也就是收说优化是在系统级上的优化，使每一个用户都拥有这些优化技术。有些优化不是所有人都能干的，只有优秀的程序员才可以

2.查询代价

(1)集中式数据库

其执行开销主要包括:磁盘存取块数(I/O代价)、处理机时间(CPU代价)、查询的内存开销 。I/O代价是最主要的

(2)分布式数据库

其总代价=I/O代价+CPU代价+内存代价＋通信代价

9.2.2一个实例

求选修了2号课程的学生姓名

用SQL表达：

SELECT Student.Sname

FROM Student, SC

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

假定学生-课程数据库中有1000个学生记录，10000个选课记录

选修2号课程的选课记录为50个

可以用多种等价的关系代数表达式来完成这一查询

1. $Q_{1}=\pi _{Sname}(\sigma _{student.Sno=SC.Sno\wedge SC.Cno='2' }(Student\times SC))$

(1)计算广义笛卡尔积

算法：

①在内存中尽可能多地装入某个表(如Student表)的若干块，留出一块存放另一个表(如SC表)的元组。

②把SC中的每个元组和Student中每个元组连接，连接后的元组装满一块后就写到中间文件上

③从SC中读入一块和内存中的Student元组连接，直到SC表处理完。

④再读入若干块Student元组，读入一块SC元组

⑤重复上述处理过程，直到把Student表处理完

设一个块能装10个Student元组或100个SC元组，在内存中存放5块Student元组和1块SC元组，则读取总块数为 $\frac{1000}{10}+\frac{1000}{10\times 5}\times \frac{10000}{100}=100+20\times 100=2100$

读Student表100块，读SC表20遍，每遍100块，则总计要读取2100数据块

连接后的元组数为 $10^{3}\times 10^{4}=10^{7}$ 。设每块能装10个元组，则写出 $10^{6}$ 块

(2)作选择操作

依次读入连接后的元组，按照选择条件选取满足要求的记录

假定内存处理时间忽略。读取中间文件花费的时间(同写中间文件一样)需读入 $10^{6}$ 块。

若满足条件的元组假设仅50个，均可放在内存

(3)作投影操作

把第(2)步的结果在Sname上作投影输出，得到最终结果

第一种情况下执行查询的总读写数据块=2100+ $10^{6}$ + $10^{6}$

2. $Q_{2}=\pi_{Sname}(\sigma _{SC.Cno='2'} (Student\bowtie SC))$

(1)计算自然连接

执行自然连接，读取Student和SC表的策略不变，总的读取块数仍为2100块

自然连接的结果比第一种情况大大减少，假设为 $10^{4}$ 个元组

写出数据块= $10^{3}$ 块

(2)读取中间文件块，执行选择运算，读取的数据块= $10^{3}$ 块

(3)把第2步结果投影输出

第二种情况下执行查询的总读写数据块=2100+ $10^{3}$ + $10^{3}$

3. $Q_{3}=\pi_{Sname}(Student\bowtie \sigma _{SC.Cno='2'}(SC))$

(1)先对SC表作选择运算，只需读一遍SC表，存取100块，因为满足条件的元组仅50个，不必使用中间文件。

(2)读取Student表，把读入的Student元组和内存中的SC元组作连接。也只需读一遍Student表共100块。

(3)把连接结果投影输出

第三种情况总的读写数据块=100+100

9.3 代数优化

9.3.1 关系代数表达式等价变换规则

两个关系表达式 $E_{1}$ 和 $E_{2}$ 是等价的，记为 $E_{1}\equiv E_{2}$

1.连接、笛卡尔积的交换律

$\begin{matrix} E_{1}\times E_{2}\equiv E_{2}\times E_{1}\\ E_{1}\bowtie E_{2}\equiv E_{2}\bowtie E_{1}\\ E_{1}\underset{F}{\bowtie }E_{2}\equiv E_{2}\underset{F}{\bowtie }E_{1} \end{matrix}$

2.连接、笛卡尔积的结合律

$\begin{matrix} (E_{1}\times E_{2})\times E_{3}\equiv E_{1}\times (E_{2}\times E_{3}) \\ (E_{1}\bowtie E_{2})\bowtie E_{3}\equiv E_{1}\bowtie (E_{2}\bowtie E_{3}) \\ (E_{1}\underset{F_{1}}{\bowtie } E_{2})\underset{F_{2}}{\bowtie } E_{3}\equiv E_{1}\underset{F_{1}}{\bowtie } (E_{2}\underset{F_{2}}{\bowtie } E_{3})\end{matrix}$

3.投影的串接定律

$\prod _{A_{1},A_{2},\cdots ,A_{n}}(\prod _{B_{1},B_{2},\cdots ,B_{m}}(E))\equiv \prod _{A_{1},A_{2},\cdots ,A_{n}}(E)$

条件： $A_{i}$ 的属性构成 $B_{j}$ 的子集

4.选择的串接定律

$\sigma _{F_{1}}(\sigma _{F_{2}}(E))\equiv \sigma _{F_{1}\wedge F_{2}}(E)$

5.选择与投影操作的交换律

$\sigma _{F}(\prod _{A_{1},A_{2},\cdots ,A_{n}}(E))\equiv \prod _{A_{1},A_{2},\cdots ,A_{n}}(\sigma _{F}(E))$ ，选择条件F只涉及属性 $A_{1},A_{2},\cdots ,A_{n}$ ,若F中有不属于 $A_{1},A_{2},\cdots ,A_{n}$ 的属性 $B_{1},B_{2},\cdots ,B_{m}$ 有更一般的规则: $\prod _{A_{1},A_{2},\cdots ,A_{n}}(\sigma _{F}(E))\equiv \prod _{A_{1},A_{2},\cdots ,A_{n}}(\sigma _{F}(\prod _{A_{1},A_{2},\cdots ,A_{n},B_{1},B_{2},\cdots ,B_{m}}(E)))$

6.选择与笛卡尔积的交换律

① $F$ 中所涉及的属性都是 $F_{1}$ 的属性则： $\sigma _{F}(E_{1}\times E_{2})\equiv \sigma _{F}(E_{1})\times E_{2}$

②如果 $F=F_{1}\wedge F_{2}$ ,并且 $F_{1}$ 只涉及 $E_{1}$ 中的属性， $F_{2}$ 只涉及 $E_{2}$ 中的属性，则

$\sigma _{F}(E_{1}\times E_{2})\equiv \sigma _{F_{1}}(E_{1})\times \sigma _{F_{2}}(E_{2})$

③若 $F_{1}$ 只涉及 $E_{1}$ 中的属性， $F_{2}$ 涉及 $E_{1}$ 和 $E_{2}$ 中的属性，则 $\sigma _{F}(E_{1}\times E_{2})\equiv \sigma _{F_{2}}(\sigma _{F_{1}}(E_{1})\times E_{2})$

7.选择与并的分配律

设 $E=E_{1}\cup E_{2}$ , $E_{1}$ 、 $E_{2}$ 有相同的属性名则 $\sigma _{F}(E_{1}\times E_{2})\equiv \sigma _{F}(E_{1})\cup \sigma _{F}(E_{2}),E=E_{1}\cup E_{2}$

8.选择与差运算的分配律

设 $E_{1}$ 、 $E_{2}$ 有相同的属性名则 $\sigma _{F}(E_{1}-E_{2})\equiv \sigma _{F}(E_{1})-\sigma _{F}(E_{2})$

9.选择对自然连接的分配律

$\sigma _{F}(E_{1}\bowtie E_{2})\equiv \sigma _{F}(E_{1})\bowtie \sigma _{F}(E_{2})$ ； $F$ 只涉及 $E_{1}$ 和 $E_{2}$ 的公共属性

10.投影与笛卡尔积的分配律

$A_{1},A_{2},\cdots,A_{n}$ 是 $E_{1}$ 的属性， $B_{1},B_{2},\cdots,B_{m }$ 是 $E_{2}$ 的属性则

$\prod _{A_{1},A_{2},\cdots ,A_{n},B_{1},B_{2},\cdots ,B_{m}}(E_{1}\times E_{2})\equiv \prod _{A_{1},A_{2},\cdots ,A_{n}}(E_{1})\times \prod _{B_{1},B_{2},\cdots ,B_{m}}(E_{2})$

11.投影与并的分配律

设 $E_{1}$ 、 $E_{2}$ 有相同的属性名则 $\prod _{A_{1},A_{2},\cdots ,A_{n}}(E_{1}\cup E_{2})\equiv \prod _{A_{1},A_{2},\cdots ,A_{n}}(E_{1})\times \prod _{A_{1},A_{2},\cdots ,A_{n}}(E_{2})$

9.3.2 查询树的启发式优化

1.选择运算应尽可能先做

2.把选择运算和投影运算同时进行

3.把投影同其前或其后的双目运算结合起来，没有必要为了去掉某些字段而扫描一遍关系

4.把某些选择同在它前面要执行的笛卡尔积结合起来成为一个连接运算，连接特别是等值连接运算要比同样关系上的笛卡尔积省很多时间

5.找出公共子表达式

如果这种重复出现的子表达式的结果不是很大的关系，并且从外存中读入这个关系比计算该子表达式的时间少得多，则先计算一次公共子表达式并把结果写入中间文件是合算的。

当查询的是视图时，定义视图的表达式就是公共子表达式的情况

6.遵循这些启发式规则，应用9.3.1的等价变换公式来优化关系表达式的算法。方法：

(1)利用等价变换规则4把形如 $\sigma _{F_{1}\wedge F_{2}\wedge \cdots \wedge F_{n}}(E)$ 变换为 $\sigma _{F_{1}}\left ( \sigma _{F_{2}}( \cdots (\sigma _{F_{n}}(E))\cdots )\right )$

(2)对每一个选择，利用等价变换规则4～9尽可能把它移到树的叶端(先做)

(3)对每一个投影利用等价变换规则3，5，10，11中的一般形式尽可能把它移向树的叶端

注意：等价变换规则3使一些投影消失或使一些投影出现

规则5把一个投影分裂为两个，其中一个有可能被移向树的叶端

(4)利用等价变换规则3～5，把选择和投影的串接合并成单个选择、单个投影或一个选择后跟一个投影，使多个选择或投影能同时执行，或在一次扫描中全部完成

(5)把上述得到的语法树的内节点分组

每一双目运算 $(\times ,\bowtie ,\cup, -)$ 和它所有的直接祖先为一组(这些直接祖先是( $\sigma ,\prod$ 运算)。

如果其后代直到叶子全是单目运算，则也将它们并入该组

但当双目运算是笛卡尔积( $\times$ )，而且后面不是与它组成等值连接的选择时，则不能把选择与这个双目运算组成同一组

7.优化示例：对9.2.2的例子：选修了2号课程的学生姓名进行优化

SELESCT Student.name FROM Student,SC WHERE Student.Sno=SC.Sno AND SC.Cno='2';

(1)把SQL语句转换成查询树

为了使用关系代数表达式的优化法，不妨假设内部表示是关系代数语法树，则查询树图转换为关系代数语法树图

(2)对查询数进行优化

利用规则4、6把选择 $\sigma _{SC.Cno='2'}$ 移到叶端

9.4 物理优化

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

物理优化方法:

(1)基于规则的启发式优化

启发式规则是指那些在大多数情况下都适用，但不是在每种情况下都是适用的规则。

(2)基于代价估算的优化

优化器估算不同执行策略的代价，并选出具有最小代价的执行计划

(3)两者结合的优化方法

常常先使用启发式规则，选取若干较优的候选方案，减少代价估算的工作量,然后分别计算这些候选方案的执行代价，较快地选出最终的优化方案

9.4.1 基于启发式规则的存取路径选择优化

1.选择操作的启发式规则

(1)对于小关系，使用全表顺序扫描，即使选择列上有索引

(2)对于大关系，启发式规则有：

①对于选择条件是“主码＝值”的查询

查询结果最多是一个元组，可以选择主码索引，一般的关系数据库管理系统会自动建立
主码索引

②对于选择条件是“非主属性＝值”的查询，并且选择列上有索引

要估算查询结果的元组数目，如果比例较小(<10%)可以使用索引扫描方法，否则还是使
用全表顺序扫描

③对于选择条件是属性上的非等值查询或者范围查询，并且选择列上有索引

要估算查询结果的元组数目，如果比例较小(<10%)可以使用索引扫描方法，否则还是使
用全表顺序扫描

④对于用AND连接的合取选择条件

如果有涉及这些属性的组合索引：优先采用组合索引扫描方法

如果某些属性上有一般的索引，可以用索引扫描方法

其他情况使用全表顺序扫描

⑤对于用OR连接的析取（即或者“ $\vee$ ”）选择条件，一般使用全表顺序扫描

2.连接操作的启发式规则

(1)如果2个表都已经按照连接属性排序:选用排序-合并算法

(2)如果一个表在连接属性上有索引:选用索引连接算法

(3)如果上面2个规则都不适用，其中一个表较小:选用Hash join算法

(4)可以选用嵌套循环方法，并选择其中较小的表，确切地讲是占用的块数(b)较少的表，作为外表(外循环的表)

理由：设连接表R与S分别占用的块数为Br与Bs,连接操作使用的内存缓冲区块数为K,分配K-1块给外表,如果R为外表，则嵌套循环法存取的块数为Br+BrBs/(K-1),显然应该选块数小的表作为外表

9.4.2 基于代价的优化

启发式规则优化是定性的选择，适合解释执行的系统。解释执行的系统，优化开销包含在查询总开销之中

编译执行的系统中查询优化和查询执行是分开的，可以采用精细复杂一些的基于代价的优化方法

1.统计信息

基于代价的优化方法要计算查询的各种不同执行方案的执行代价，它与数据库的状态密切相关

优化器需要的统计信息

(1)对每个基本表

该表的元组总数(N)、元组长度(l)、占用的块数(B)、占用的溢出块数(BO)

(2)对基表的每个列

该列不同值的个数(m)、该列最大值和最小值、该列上是否已经建立了索引是哪种索引(B+树索引、Hash索引、聚集索引)

由以上信息可以计算选择率(f)：如果不同值的分布是均匀的，f＝1/m。如果不同值的分布不均匀，则要计算每个值的选择率，f＝具有该值的元组数/N

(3)对索引

索引的层数(L)、不同索引值的个数、索引的选择基数S(有S个元组具有某个索引值)、索引的叶结点数(Y)

2.代价估算示例

(1)全表扫描算法的代价估算公式

如果基本表大小为B块，全表扫描算法的代价 cost＝B

如果选择条件是“码＝值”，那么平均搜索代价 cost＝B/2

(2)索引扫描算法的代价估算公式

如果选择条件是“码＝值”，则采用该表的主索引

若为B+树，层数为L，需要存取B+树中从根结点到叶结点L块，再加上基本表中该元组所在的那一块，所以cost=L+1

如果选择条件涉及非码属性

若为B+树索引，选择条件是相等比较，S是索引的选择基数(有S个元组满足条件)。满足条件的元组可能会保存在不同的块上，所以(最坏的情况)cost=L+S

如果比较条件是＞，＞＝，＜，＜＝操作

假设有一半的元组满足条件，就要存取一半的叶结点，通过索引访问一半的表存储块，cost=L+Y/2+B/2，如果可以获得更准确的选择基数，可以进一步修正Y/2与B/2

(3)嵌套循环连接算法的代价估算公式

嵌套循环连接算法的代价cost＝Br+BrBs/(K-1)

如果需要把连接结果写回磁盘cost＝Br+BrBs/(K-1)+(Frs*Nr*Ns)/Mrs

其中Frs为连接选择性(join selectivity)，表示连接结果元组数的比例。Mrs是存放连接结果的块因子，表示每块中可以存放的结果元组数目

(4)排序-合并连接算法的代价估算公式

如果连接表已经按照连接属性排好序，则cost＝Br+Bs+(Frs*Nr*Ns)/Mrs

如果必须对文件排序，还需要在代价函数中加上排序的代价，对于包含B个块的文件排序的代价大约 $(2*B)+(2*B*log_{2}B)$

9.5 查询计划的执行

查询计划的执行可以分为自顶向下和自底向上两种执行方法

自顶向下：系统反复向查询计划顶端的操作符发出要查询结果元组的请求，从操作符收到请求后，试图计算下一个（几个）元组并返回这些元组

自底向上：查询计划从叶节点开始执行，叶节点操作符不断产生元组并放入缓冲区，直到缓冲区满，这时必需等操作符将元组从缓冲区取走才可以继续进行

第10章数据库恢复技术

10.1 事务的基本概念

1.事务

(1)事务(Transaction)是用户定义的一个数据库操作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单位

(2)事务和程序是两个概念

在关系数据库中，一个事务可以是一条SQL语句，一组SQL语句或整个程序。一个程序通常包含多个事务

(3)事务是恢复和并发控制的基本单位

2.事务的定义方式

(1)显式定义方式：

①事务正常结束

BEGIN TRANSACTION；

SQL 语句；

COMMIT；

COMMIT表示提交。即提交事务的所有操作，就是将事务中所有对数据库的更新写回到磁盘上的物理数据库中

②事务异常终止

BEGIN TRANSACTION；

SQL 语句；

ROLLBACK；

ROLLBACK表示回滚，事务运行的过程中发生了故障，不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，事务滚回到开始时的状态

(2)隐式定义方式：当用户没有显式地定义事务时，数据库管理系统按缺省规定自动划分事务

3.事务的ACID特性

(1)原子性（Atomicity）

事务是数据库的逻辑工作单位，指事务所包含的所有操作要么都做，要么都不做

(2)一致性（Consistency）

事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态

一致性状态：数据库中只包含成功事务提交的结果

不一致状态：数据库系统运行中发生故障，有些事务尚未完成就被迫中断；这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态

(3)隔离性（Isolation）

一个事务的执行不能被其他事务干扰。一个事务内部的操作及使用的数据对其他并发事务是隔离的，并发执行的各个事务之间不能互相干扰

(4)持续性（Durability ）

持续性也称永久性（Permanence），一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其执行结果有任何影响

4.事务是恢复和并发控制的基本单位，保证事务ACID特性是事务管理的任务

5.破坏事务ACID特性的因素

(1)多个事务并行运行时，不同事务的操作交叉执行

数据库管理系统必须保证多个事务的交叉运行不影响这些事务的隔离性

(2)事务在运行过程中被强行停止

数据库管理系统必须保证被强行终止的事务对数据库和其他事务没有任何影响

10.2 数据库恢复概述

1.故障是不可避免的：计算机硬件故障、软件的错误、操作员的失误、恶意的破坏

2.故障的影响

(1)运行事务非正常中断，影响数据库中数据的正确性

(2)破坏数据库，全部或部分丢失数据

3.数据库的恢复

数据库管理系统必须具有把数据库从错误状态恢复到某一已知的正确状态(亦称为一致状态或完整状态)的功能

数据库恢复子系统是数据库管理系统的一个重要组成部分，且代码相当庞大，代码占整个系统的10%，恢复技术是衡量系统优劣的重要指标，因为对系统的可靠程度起到决定性作用

10.3 故障的种类

1.事务内部的故障

有的是可以通过事务程序本身发现的，有的是非预期的不能由事务程序处理。(非预期的就像是bug)

2.系统故障

称为软故障，是指造成系统停止运转的任何事件，使得系统要重新启动。

故障表现：

整个系统的正常运行突然被破坏，所有正在运行的事务都非正常终止，不破坏数据库但内存中数据库缓冲区的信息全部丢失

常见原因：

特定类型的硬件错误（如CPU故障）、操作系统故障、数据库管理系统代码错误、系统断电

表现一：发生系统故障时，一些尚未完成的事务的结果可能已送入物理数据库，造成数据库可能处于不正确状态。

恢复策略：系统重新启动时，恢复程序让所有非正常终止的事务回滚，强行撤消（UNDO）所有未完成事务

表现二：发生系统故障时，有些已完成的事务可能有一部分甚至全部留在缓冲区，尚未写回到磁盘上的物理数据库中，系统故障使得这些事务对数据库的修改部分或全部丢失

恢复策略：系统重新启动时，恢复程序需要重做（REDO）所有已提交的事务

3.介质故障

称为硬故障，指外存故障。如磁盘损坏、磁头碰撞、瞬时强磁场干扰

故障表现：介质故障破坏数据库或部分数据库，并影响正在存取这部分数据的所有事务。介质故障比前两类故障的可能性小得多，但破坏性大得多

4.计算机病毒

计算机病毒：一种人为的故障或破坏，是一些恶作剧者研制的一种计算机程序。可以繁殖和传播，造成对计算机系统包括数据库的危害

计算机病毒的特点：隐蔽性、潜伏性、传染性、破坏性、寄生性

计算机病毒种类：小的病毒只有20条指令，不到50B。大的病毒像一个操作系统，由上万条指令组成

计算机病毒的危害：有的病毒传播很快，一旦侵入系统就马上摧毁系统；有的病毒有较长的潜伏期，计算机在感染后数天或数月才开始发病；有的病毒感染系统所有的程序和数据；有的只对某些特定的程序和数据感兴趣。计算机病毒已成为计算机系统的主要威胁，自然也是数据库系统的主要威胁。数据库一旦被破坏仍要用恢复技术把数据库加以恢复

5.小结

(1)各类故障，对数据库的影响有两种可能性:一是数据库本身被破坏;二是数据库没有被破坏，但数据可能不正确，这是由于事务的运行被非正常终止造成的

(2)恢复操作的基本原理：冗余。利用存储在系统别处的冗余数据来重建数据库中已被破坏或不正确的那部分数据。恢复的实现技术很复杂：一个大型数据库产品，恢复子系统的代码要占全部代码的10%以上

10.4 恢复的实现技术

1.恢复机制涉及的关键问题

(1)如何建立冗余数据

①数据转储（backup）

②登记日志文件（logging）

(2)如何利用这些冗余数据实施数据库恢复

10.4.1 数据转储

1.什么是数据转储

转储是指数据库管理员定期地将整个数据库复制到磁带、磁盘或其他存储介质上保存起来的过程。备用的数据文本称为后备副本(backup)或后援副本

数据库遭到破坏后可以将后备副本重新装入，重装后备副本只能将数据库恢复到转储时的状态，要想恢复到故障发生时的状态，必须重新运行自转储以后的所有更新事务

2.数据转储的方法

(1)静态存储和动态存储

①静态转储：在系统中无运行事务时进行的转储操作，转储开始时数据库处于一致性状态，转储期间不允许对数据库的任何存取、修改活动。得到的一定是一个数据一致性的副本

优点：实现简单

缺点：转储必须等待正运行的用户事务结束，新的事务必须等转储结束，降低了数据库的可用性

②动态转储：转储操作与用户事务并发进行，转储期间允许对数据库进行存取或修改

优点：不用等待正在运行的用户事务结束，不会影响新事务的运行

缺点：不能保证副本中的数据正确有效

利用动态转储得到的副本进行故障恢复：需要把动态转储期间各事务对数据库的修改活动登记下来，建立日志文件(log file)。后备副本加上日志文件就能把数据库恢复到某一时刻的正确状态

(2)海量转储与增量转储

①海量转储: 每次转储全部数据库

②增量转储: 只转储上次转储后更新过的数据

海量转储与增量转储比较：从恢复角度看，使用海量转储得到的后备副本进行恢复往往更方便；如果数据库很大，事务处理又十分频繁，则增量转储方式更实用更有效

(3)转储方法小结

转储方法分类

10.4.2 登记日志文件

1.日志文件的格式和内容

(1)日志文件的格式:分为以记录为单位的日志文件和以数据块为单位的日志文件两种

①以记录为单位的日志文件需要登记的内容：

Ⅰ各个事务的开始标记(BEGIN TRANSACTION)

Ⅱ各个事务的结束标记(COMMIT或ROLLBACK)

Ⅲ各个事务的所有更新操作

以上均作为日志文件中的一个日志记录 (log record)

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

Ⅰ事务标识（标明是哪个事务）

Ⅱ操作类型（插入、删除或修改）

Ⅲ操作对象（记录内部标识）

Ⅳ更新前数据的旧值（对插入操作而言，此项为空值）

Ⅴ更新后数据的新值（对删除操作而言, 此项为空值）

②以数据块为单位的日志文件内容：

Ⅰ事务标识

Ⅱ被更新的数据块

2.日志文件的作用

用途：进行事务故障恢复和系统故障恢复，协助后备副本进行介质故障恢复

具体作用：Ⅰ事务故障恢复和系统故障恢复必须用日志文件

Ⅱ在动态转储方式中必须建立日志文件，后备副本和日志文件结合起来才能有效地恢复数据库

Ⅲ在静态转储方式中，也可以建立日志文件。当数据库毁坏后可重新装入后援副本把数据库恢复到转储结束时刻的正确状态，利用日志文件，把已完成的事务进行重做处理，对故障发生时尚未完成的事务进行撤销处理，不必重新运行那些已完成的事务程序就可把数据库恢复到故障前某一时刻的正确状态

3.登记日志文件

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

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

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

写日志文件操作：把表示这个修改的日志记录写到日志文件中

写数据库操作：把对数据的修改写到数据库中

为什么要先写日志文件?

写数据库和写日志文件是两个不同的操作，在这两个操作之间可能发生故障，如果先写了数据库修改，而在日志文件中没有登记下这个修改，则以后就无法恢复这个修改了，如果先写日志，但没有修改数据库，按日志文件恢复时只不过是多执行一次不必要的UNDO操作，并不会影响数据库的正确性

10.5 恢复策略

10.5.1 事务故障的恢复

1.事务故障：事务在运行至正常终止点前被终止

2.恢复方法：由恢复子系统利用日志文件撤消（UNDO）此事务已对数据库进行的修改。事务故障的恢复由系统自动完成，对用户是透明的，不需要用户干预

3.事务故障的恢复步骤

(1)反向扫描文件日志（即从最后向前扫描日志文件），查找该事务的更新操作

(2)对该事务的更新操作执行逆操作。即将日志记录中“更新前的值” 写入数据库

插入操作， “更新前的值”为空，则相当于做删除操作

删除操作，“更新后的值”为空，则相当于做插入操作

若是修改操作，则相当于用修改前值代替修改后值

(3)继续反向扫描日志文件，查找该事务的其他更新操作，并做同样处理

(4)如此处理下去，直至读到此事务的开始标记，事务故障恢复就完成了

10.5.2 系统故障的恢复

1.系统故障造成数据库不一致状态的原因

①未完成事务对数据库的更新可能已写入数据库

②已提交事务对数据库的更新可能还留在缓冲区没来得及写入数据库

2.恢复方法

(1)Undo 故障发生时未完成的事务

(2)Redo 已完成的事务

系统故障的恢复由系统在重新启动时自动完成，不需要用户干预

3.系统故障的恢复步骤

(1)正向扫描日志文件（即从头扫描日志文件）

重做队列(REDO-LIST): 在故障发生前已经提交的事务。这些事务既有BEGIN TRANSACTION记录，也有COMMIT记录

撤销队列(UNDO-LIST):故障发生时尚未完成的事务。这些事务只有BEGIN TRANSACTION记录，无相应的COMMIT记录

(2)对撤销队列事务进行撤销(UNDO)处理

反向扫描日志文件，对每个撤销事务的更新操作执行逆操作,即将日志记录中“更新前的值”写入数据库

(3)对重做队列事务进行重做(REDO)处理

正向扫描日志文件，对每个重做事务重新执行登记的操作,即将日志记录中“更新后的值”写入数据库

10.5.3 介质故障的恢复

这是最严重的一种故障，恢复的方法为重装数据库，然后重做已完成的事务。具体恢复步骤：

(1)装入最新的后备数据库副本(离故障发生时刻最近的转储副本) ，使数据库恢复到最近一次转储时的一致性状态。

对于静态转储的数据库副本，装入后数据库即处于一致性状态

对于动态转储的数据库副本，还须同时装入转储时刻的日志文件副本，利用恢复系统故障的方法（即REDO+UNDO），才能将数据库恢复到一致性状态。

(2)装入有关的日志文件副本(转储结束时刻的日志文件副本) ，重做已完成的事务。

首先扫描日志文件，找出故障发生时已提交的事务的标识，将其记入重做队列。

然后正向扫描日志文件，对重做队列中的所有事务进行重做处理。即将日志记录中“更新后的值”写入数据库

介质故障的恢复需要数据库管理员介入。数据库管理员的工作：重装最近转储的数据库副本和有关的各日志文件副本，执行系统提供的恢复命令。具体的恢复操作仍由数据库管理系统完成

10.6 具有检查点的恢复技术

1.问题的提出

(1)两个问题

搜索整个日志将耗费大量的时间

重做处理：重新执行，浪费了大量时间

(2)具有检查点（checkpoint）的恢复技术

在日志文件中增加检查点记录（checkpoint）

增加重新开始文件

恢复子系统在登录日志文件期间动态地维护日志

2.检查点技术

(1)检查点记录的内容

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

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

(2)重新开始文件内容

记录各个检查点记录在日志文件中的地址

(3)动态维护日志文件的方法

周期性地执行如下操作：建立检查点，保存数据库状态。具体步骤是：

①将当前日志缓冲区中的所有日志记录写入磁盘的日志文件上

②在日志文件中写入一个检查点记录

③将当前数据缓冲区的所有数据记录写入磁盘的数据库中

④把检查点记录在日志文件中的地址写入一个重新开始文件

说明：恢复子系统可以定期或不定期地建立检查点,保存数据库状态。定期：按照预定的一个时间间隔，如每隔一小时建立一个检查点。不定期：按照某种规则，如日志文件已写满一半建立一个检查点

3.利用检查点的恢复策略

利用检查点的恢复步骤：

(1)从重新开始文件中找到最后一个检查点记录在日志文件中的地址，由该地址在日志文件中找到最后一个检查点记录

(2)由该检查点记录得到检查点建立时刻所有正在执行的事务清单ACTIVE-LIST

建立两个事务队列

UNDO-LIST

REDO-LIST

把ACTIVE-LIST暂时放入UNDO-LIST队列，REDO队列暂为空。

(3)从检查点开始正向扫描日志文件，直到日志文件结束

如有新开始的事务 $T_{i}$ ，把 $T_{i}$ 暂时放入UNDO-LIST队列

如有提交的事务 $T_{j}$ ，把 $T_{j}$ 从UNDO-LIST队列移到REDO-LIST队列;直到日志文件结束

(4)对UNDO-LIST中的每个事务执行UNDO操作,对REDO-LIST中的每个事务执行REDO操作

10.7 数据库镜像

数据库镜像:数据库管理系统自动把整个数据库或其中的关键数据复制到另一个磁盘上,数据库管理系统自动保证镜像数据与主数据的一致性,每当主数据库更新时，数据库管理系统自动把更新后的数据复制过去

(1)出现介质故障时

可由镜像磁盘继续提供使用，同时数据库管理系统自动利用镜像磁盘数据进行数据库的恢复，不需要关闭系统和重装数据库副本

(2)没有出现故障时

可用于并发操作。一个用户对数据加排他锁修改数据，其他用户可以读镜像数据库上的数据，而不必等待该用户释放锁

10.8 小结

1.事务的概念和性质

事务是数据库的逻辑工作单位

数据库管理系统保证系统中一切事务的原子性、一致性、隔离性和持续性，就保证了事务处于一致状态

2.故障的种类

事务故障

系统故障

介质故障

3.恢复中最经常使用的技术

数据库转储

登记日志文件

4.恢复的基本原理

利用存储在后备副本、日志文件和数据库镜像中的冗余数据来重建数据库

5.事务

不仅是恢复的基本单位,也是并发控制的基本单位

第11章并发控制

1.多用户数据库系统：允许多个用户同时使用的数据库系统。例：飞机定票数据库系统、银行数据库系统

特点：在同一时刻并发运行的事务数可达数百上千个

2.多事务执行方式

(1)事务串行执行

每个时刻只有一个事务运行，其他事务必须等到这个事务结束以后方能运行。不能充分利用系统资源，发挥数据库共享资源的特点

(2)交叉并发方式（Interleaved Concurrency）

在单处理机系统中，事务的并行执行是这些并行事务的并行操作轮流交叉运行。单处理机系统中的并行事务并没有真正地并行运行，但能够减少处理机的空闲时间，提高系统的效率

(3)同时并发方式（simultaneous concurrency）

多处理机系统中，每个处理机可以运行一个事务，多个处理机可以同时运行多个事务，实现多个事务真正的并行运行。是最理想的并发方式，但受制于硬件环境。其并发方式机制更复杂

11.1 并发控制概述

事务是并发控制的基本单位

1.并发控制机制的任务

(1)对并发操作进行正确调度

(2)保证事务的隔离性

(3)保证数据库的一致性

2.并发操作带来的数据不一致性

(1)丢失修改（Lost Update）

两个事务T1和T2读入同一数据并修改，T2的提交结果破坏了T1提交的结果，导致T1的修改被丢失

(2)不可重复读（Non-repeatable Read）

不可重复读是指事务T1读取数据后，事务T2,执行更新操作，使T1无法再现前一次读取结果

不可重复读包括三种情况：

①事务T1读取某一数据后，事务T2对其做了修改，当事务T1再次读该数据时，得到与前一次不同的值

②事务T1按一定条件从数据库中读取了某些数据记录后，事务T2删除了其中部分记录，当T1再次按相同条件读取数据时，发现某些记录神秘地消失了。

③事务T1按一定条件从数据库中读取某些数据记录后，事务T2插入了一些记录，当T1再次按相同条件读取数据时，发现多了一些记录。

后两种不可重复读有时也称为幻影现象

(3)读“脏”数据（Dirty Read）

读“脏”数据是指：

①事务T1修改某一数据，并将其写回磁盘

②事务T2读取同一数据后，T1由于某种原因被撤销

③这时T1已修改过的数据恢复原值，T2读到的数据就与数据库中的数据不一致

④T2读到的数据就为“脏”数据，即不正确的数据

并发控制的主要技术有封锁(locking)、时间戳(timestamp)、乐观控制法(optimistic scheduler)和多版本并发控制(multi-version concurrency control,MVCC)

11.2 封锁

1.什么是封锁

封锁就是事务T在对某个数据对象（例如表、记录等）操作之前，先向系统发出请求，对其加锁。加锁后事务T就对该数据对象有了一定的控制，在事务T释放它的锁之前，其它的事务不能更新此数据对象。封锁是实现并发控制的一个非常重要的技术

2.基本的封锁类型

一个事务对某个数据对象加锁之后究竟拥有什么样的控制由封锁的类型决定

基本的封锁类型:

(1)排它锁（Exclusive Locks，简记为X锁）

排它锁又称为写锁。若事务T对数据对象A加上X锁，则只允许T读取和修改A，其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。保证其他事务在T释放A上的锁之前不能再读取和修改A

(2)共享锁（Share Locks，简记为S锁）

共享锁又称为读锁。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其它事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。保证其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改

3.锁的相容矩阵

在锁的相容矩阵中：

最左边一列表示事务T1已经获得的数据对象上的锁的类型，其中横线表示没有加锁。

最上面一行表示另一事务T2对同一数据对象发出的封锁请求。

T2的封锁请求能否被满足用矩阵中的Y和N表示

Y表示事务T2的封锁要求与T1已持有的锁相容，封锁请求可以满足

N表示T2的封锁请求与T1已持有的锁冲突，T2的请求被拒绝

同一事务对同一个对象可以先加S锁，再加X锁

11.3 封锁协议

1.什么是封锁协议

在运用X锁和S锁对数据对象加锁时，需要约定一些规则，这些规则为封锁协议（Locking Protocol）：①何时申请X锁或S锁

②持锁时间

③何时释放

对封锁方式规定不同的规则，就形成了各种不同的封锁协议，它们分别在不同的程度上为并发操作的正确调度提供一定的保证

2.三级封锁协议

(1)一级封锁协议

事务T在修改数据R之前必须先对其加X锁，直到事务结束才释放。

正常结束（COMMIT）

非正常结束（ROLLBACK）

一级封锁协议可防止丢失修改，并保证事务T是可恢复的。

在一级封锁协议中，如果仅仅是读数据不对其进行修改，是不需要加锁的，所以它不能保证可重复读和不读“脏”数据

(2)二级封锁协议

二级封锁协议是一级封锁协议加上事务T在读取数据R之前必须先对其加S锁，读完后即可释放S锁

二级封锁协议可以防止丢失修改和读“脏”数据。在二级封锁协议中，由于读完数据后即可释放S锁，所以它不能保证可重复读

(3)三级封锁协议

一级封锁协议加上事务T在读取数据R之前必须先对其加S锁，直到事务结束才释放

三级封锁协议可防止丢失修改、读脏数据和不可重复读

3.三级协议的主要区别

什么操作需要申请封锁以及何时释放锁（即持锁时间）

4.不同的封锁协议使事务达到的一致性级别不同

封锁协议级别越高，一致性程度越高

说明：写数据必须加X锁，本书讲的是单处理机，一次只能一个事务写数据库，事务又是执行的基本单位。S锁只是在读数据时才加(一级封锁协议不加S锁)，二、三级封锁协议差别在释放S锁的时机不同

11.4 活锁和死锁

11.4.1 活锁

1.活锁现象

事务T1封锁了数据R

事务T2又请求封锁R，于是T2等待。

T3也请求封锁R，当T1释放了R上的封锁之后系统首先批准了T3的请求，T2仍然等待。

T4又请求封锁R，当T3释放了R上的封锁之后系统又批准了T4的请求……

T2有可能永远等待，这就是活锁的情形

2.避免活锁：采用先来先服务的策略(或者优先级)

当多个事务请求封锁同一数据对象时

按请求封锁的先后次序对这些事务排队

该数据对象上的锁一旦释放，首先批准申请队列中第一个事务获得锁

11.4.2 死锁

1.死锁现象

事务T1封锁了数据R1

T2封锁了数据R2

T1又请求封锁R2，因T2已封锁了R2，于是T1等待T2释放R2上的锁

接着T2又申请封锁R1，因T1已封锁了R1，T2也只能等待T1释放R1上的锁

这样T1在等待T2，而T2又在等待T1，T1和T2两个事务永远不能结束，形成死锁

2.两类方法

(1)死锁的预防

产生死锁的原因是两个或多个事务都已封锁了一些数据对象，然后又都请求对已为其他事务封锁的数据对象加锁，从而出现死等待。预防死锁的发生就是要破坏产生死锁的条件

①一次封锁法

要求每个事务必须一次将所有要使用的数据全部加锁，否则就不能执行

存在的问题：降低系统并发度

②顺序封锁法

顺序封锁法时预先对数据对象规定一个封锁顺序，所有事务都按这个顺序实行封锁

顺序封锁法存在的问题：

Ⅰ维护成本：数据库系统中封锁的数据对象极多，并且随数据的插入、删除等操作而不断地变化，要维护这样的资源的封锁顺序非常困难，成本很高

Ⅱ难以实现：事务的封锁请求可以随着事务的执行而动态地决定，很难事先确定每一个事务要封锁哪些对象，因此也就很难按规定的顺序去施加封锁

在操作系统中广为采用的预防死锁的策略并不太适合数据库的特点。数据库管理系统在解决死锁的问题上更普遍采用的是诊断并解除死锁的方法

(2)死锁的诊断与解除

①超时法

如果一个事务的等待时间超过了规定的时限，就认为发生了死锁

优点：实现简单

缺点：有可能误判死锁

时限若设置得太长，死锁发生后不能及时发现

②等待图法用

事务等待图动态反映所有事务的等待情况

事务等待图是一个有向图G=(T，U)

T为结点的集合，每个结点表示正运行的事务

U为边的集合，每条边表示事务等待的情况

若T1等待T2，则T1，T2之间划一条有向边，从T1指向T2

并发控制子系统周期性地（比如每隔数秒）生成事务等待图，检测事务。如果发现图中存在回路，则表示系统中出现了死锁

解除死锁：选择一个处理死锁代价最小的事务，将其撤消。释放此事务持有的所有的锁，使其它事务能继续运行下去

11.5 并发调度的可串行性

数据库管理系统对并发事务不同的调度可能会产生不同的结果。串行调度是正确的。执行结果等价于串行调度的调度也是正确的，称为可串行化调度

11.5.1 可串行化调度

可串行化(Serializable)调度：多个事务的并发执行是正确的，当且仅当其结果与按某一次序串行地执行这些事务时的结果相同

可串行性(Serializability)：是并发事务正确调度的准则。一个给定的并发调度，当且仅当它是可串行化的，才认为是正确调度

11.5.2 冲突可串行化调度

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

$R_{i}(X)$ 与 $W_{j}(X)$ /*事务 $T_{i}$ 读 $x$ ， $T_{j}$ 写 $x$ ，其中 $i\neq j$ */

$W_{i}(X)$ 与 $W_{j}(X)$ /*事务 $T_{i}$ 写 $x$ ， $T_{j}$ 写 $x$ ，其中 $i\neq j$ */

说明：①除读写操作和写写操作外，其他操作是不冲突操作
②不同事务的冲突操作和同一事物的两个操作是不可互换的

$R_{i}(X)$ 与 $W_{j}(X)$ 改变执行顺序，则 $T_{i}$ 读 $x$ 的数据将发生变化

$W_{i}(X)$ 与 $W_{j}(X)$ 改变执行顺序，则 $x$ 的数据由等于 $T_{j}$ 的结果，变为 $T_{i}$ 的结果

2.不能交换（Swap）的动作:

①同一事务的两个操作

②不同事务的冲突操作

3.冲突可串行化

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

若一个调度是冲突可串行化，则一定是可串行化的调度。冲突可串行化是可串行化调度的充分条件，不是必要条件。还有不满足冲突可串行化条件的可串行化调度

11.6 两段锁协议

数据库管理系统普遍采用两段锁协议的方法实现并发调度的可串行性，从而保证调度的正确性

1.两段锁协议：指所有事务必须分两个阶段对数据项加锁和解锁；在对任何数据进行读、写操作之前，事务首先要获得对该数据的封锁；在释放一个封锁之后，事务不再申请和获得任何其他封锁

2.“两段”锁的含义：

事务分为两个阶段：

第一阶段是获得封锁，也称为扩展阶段

事务可以申请获得任何数据项上的任何类型的锁，但是不能释放任何锁

第二阶段是释放封锁，也称为收缩阶段

事务可以释放任何数据项上的任何类型的锁，但是不能再申请任何锁

扩展阶段只可以加锁不能释放锁，收缩阶段只可以释放锁不可以加锁，中间阶段不可以上锁也不可以释放锁

说明：①事务遵守两段锁协议是可串行化调度的充分条件，而不是必要条件。

②若并发事务都遵守两段锁协议，则对这些事务的任何并发调度策略都是可串行化的

③若并发事务的一个调度是可串行化的，不一定所有事务都符合两段锁协议

3.两段锁协议与防止死锁的一次封锁法

(1)一次封锁法要求每个事务必须一次将所有要使用的数据全部加锁，否则就不能继续执行，因此一次封锁法遵守两段锁协议

(2)但是两段锁协议并不要求事务必须一次将所有要使用的数据全部加锁，因此遵守两段锁协议的事务可能发生死锁

11.7 封锁的粒度

1.封锁对象的大小称为封锁粒度(Granularity)

封锁的对象：逻辑单元和物理单元

例：在关系数据库中，封锁对象：

逻辑单元: 属性值、属性值的集合、元组、关系、索引项、整个索引、整个数据库等

物理单元：页（数据页或索引页）、物理记录等

2.选择封锁粒度原则

封锁粒度与系统的并发度和并发控制的开销密切相关

封锁的粒度越大，数据库所能够封锁的数据单元就越少，并发度就越小，系统开销也越小；

封锁的粒度越小，并发度较高，但系统开销也就越大

11.7.1 多粒度封锁

1.多粒度封锁(Multiple Granularity Locking)：在一个系统中同时支持多种封锁粒度供不同的事务选择

2.选择封锁粒度

同时考虑封锁开销和并发度两个因素, 适当选择封锁粒度，具体为：

(1)需要处理多个关系的大量元组的事务：以数据库为封锁单位

(2)需要处理某个关系的大量元组的事务：以关系为封锁单元

(3)只处理少量元组的事务：以元组为封锁单位

3.多粒度树

以树形结构来表示多级封锁粒度,根结点是整个数据库，表示最大的数据粒度。叶结点表示最小的数据粒度

4.多粒度封锁协议

允许多粒度树中的每个结点被独立地加锁，对一个结点加锁意味着这个结点的所有后裔结点也被加以同样类型的锁。在多粒度封锁中一个数据对象可能以两种方式封锁：显式封锁和隐式封锁

(1)显式封锁: 直接加到数据对象上的封锁

(2)隐式封锁:是该数据对象没有独立加锁，是由于其上级结点加锁而使该数据对象加上了锁

显式封锁和隐式封锁的效果是一样的。系统检查封锁冲突时，要检查显式封锁，还要检查隐式封锁

5.对某个数据对象加锁，系统要检查

该数据对象：有无显式封锁与之冲突

所有上级结点：检查本事务的显式封锁是否与所有上级节点数据对象上的隐式封锁冲突：(由上级结点已加的封锁造成的）

所有下级结点：看所有下级节点上面的显式封锁是否与本事务的隐式封锁（将加到下级结点的封锁）冲突

11.7.2 意向锁

1.引进意向锁（intention lock）目的：提高对某个数据对象加锁时系统的检查效率

如果对一个结点加意向锁，则说明该结点的下层结点正在被加锁。对任一结点加基本锁，必须先对它的上层结点加意向锁

例如，对任一元组加锁时，必须先对它所在的数据库和关系加意向锁

2.常用意向锁

(1)意向共享锁(Intent Share Lock，简称IS锁)

IS锁:如果对一个数据对象加IS锁，表示它的后裔结点拟（意向）加S锁

例如：事务T1要对R1中某个元组加S锁，则要首先对关系R1和数据库加IS锁

(2)意向排它锁(Intent Exclusive Lock，简称IX锁)

IX锁：如果对一个数据对象加IX锁，表示它的后裔结点拟（意向）加X锁

例如：事务T1要对R1中某个元组加X锁，则要首先对关系R1和数据库加IX锁

(3)共享意向排它锁(Share Intent Exclusive Lock，简称SIX锁)

SIX锁：如果对一个数据对象加SIX锁，表示对它加S锁，再加IX锁，即SIX = S + IX

例：对某个表加SIX锁，则表示该事务要读整个表（所以要对该表加S锁），同时会更新个别元组（所以要对该表加IX锁）

3.意向锁的相容阵列

4.锁的强度

锁的强度是指它对其他锁的排斥程度。一个事务在申请封锁时以强锁代替弱锁是安全的，反之则不然。下图X锁最强，IS锁最弱

5.具有意向锁的多粒度封锁方法

申请封锁时应该按自上而下的顺序进行。释放封锁时应该按自下而上的顺序进行

11.8 其他并发控制机制

1.时间戳方法

给每一个事务盖上一个时标，即事务开始执行的时间

控制机制：(1)每一个事务都有唯一时间戳，并按照这个时间戳来解决事务的冲突操作

(2)如冲突则回滚具有较早时间戳的事务

(3)回滚的事务赋予新的时间戳并从头开始执行

2.乐观控制法

又称验证法，乐观控制法认为事务的执行时间很少发生冲突，因此不对事务进行特殊管制

控制机制：(1)不对事务进行特殊管制，让它自由执行，事务提交前在进行准确性检查

(2)如果检查后发现该事务的执行出现了冲突并影响了可串行性，，则拒绝提交并回滚该事务

11.8.1 多版本并发控制

1.多版本并发控制(MVCC)

是指在数据库中通过维护数据库对象的多个版本信息来有效并发控制的一种策略

2.版本的定义

是指数据库中对象的一个快照，记录了数据库对象某个时刻的状态。随着计算机系统存储设备价格的不断降低，可以考虑为数据库系统的数据对象保留多个版本，以提高事务的并发操作程度

3.多版本控制并发协议

(1)每一个 $write(Q)$ 操作都会创建 $Q$ 一个新版本，形成一个版本序列 $Q_{1},Q_{2},Q_{3},\cdots ,Q_{m}$

每个版本 $Q_{k}$ 都包含三个数据：

①版本值

②创建 $Q_{k}$ 事务的时间戳 $W-timestamp(Q_{k})$ （表示在数据项 $Q$ 上成功执行 $write(Q)$ 操作的所有事务的最大时间戳）

③成功读取 $Q_{k}$ 事务的最大时间戳 $R-timestamp(Q_{k})$ （表示在数据项 $Q$ 上成功执行 $read(Q)$ 操作的所有事务中的最大时间戳）

(2)用 $TS(T)$ 表示事务 $T$ 的时间戳， $TS(T_{i})< TS(T_{j})$ 表示事务 $T_{i}$ 在事务 $T_{j}$ 之前开始执行

(3)协议描述如下：

假设版本 $Q_{k}$ 具有小于或等于 $TS(T)$ 的最大时间戳

若事务 $T$ 发出 $read(Q)$ ，则返回版本 $Q_{k}$ 的内容

若事务 $T$ 发出 $write(Q)$ ，则：①当 $TS(T)< R-timestamp(Q_{k})$ 时，回滚 $T$ (在读之前不能写，否则覆盖)

②当 $TS(T)= W-timestamp(Q_{k})$ 时，覆盖 $Q_{k}$ 的内容

否则创建 $Q$ 的新版本

(4)若一个数据对象的两个版本 $Q_{k}$ 和 $Q_{l}$ ,其 $W-timestamp$ 都小于系统中最老的事务的时间戳，那么这两个版本中较旧的那个版本将不再被用到，因而可以从系统中删除

多版本并发控制和封锁机制相比，主要的好处是消除了数据库中数据对象读和写操作的冲突，有效地提高了系统的性能。多版本并发控制方法有利于提高事务的并发度，但也会产生大量的无效版本，而且在结束事务时刻，其所影响的元组的有效性不能马上确定，这就为保存事务执行过程中的状态提出了难题

11.8.2 改进的多版本并发控制

1.改进多版本并发控制

将事务分为只读事务和更新事务

2.MV2PL协议

只读事务发生冲突的可能性小，采用多版本时间戳；更新事务采用较保守的两阶段封锁(2PL)协议，这样的混合协议称为MV2PL

3.验证锁(C锁)

除了传统的读锁(共享锁)和写锁(排他锁)，引入验证锁(C锁)，封锁的相容矩阵如下：

	R-LOCK	W-LOCK	C-LOCK
R-LOCK	Y	Y	N
W-LOCK	Y	N	N
C-LOCK	N	N	N
注：Y=YES表示相容的请求；N=NO，表示不相容的请求；

注意：在这个相容矩阵中，读锁和写锁变得是相容的了。这样当某个事务写数据对象时。允许其他事务读数据（当然，写操作将生成一个新的版本，而读操作就是在旧的版本上读）。一旦写事务要提交的时候，必须首先获得在那些加了写锁的数据对象上的验证锁。由于验证锁和读锁是不相容的，所以为了得到验证锁，写事务不得不延迟它的提交，直到所有被它加上写锁的数据对象都被所有那些正在读它们的事务释放。一旦写事务获得验证锁，系统就可以丢弃数据对象的旧值，代之于新版本，然后释放验证锁，提交事务

在这里，系统最多只要维护数据对象的两个版本。多个读操作可以和写操作并发地执行

泛华赏

关注

8
点赞
踩
15

收藏

觉得还不错? 一键收藏
1
评论
数据库系统概论（第五版）王珊学习笔记

关系数据结构的形式化定义1.域(domain)域是一组具有相同数据类型的值的集合，例如自然数、整数、{男，女}2.笛卡尔积笛卡尔积是域上的一种集合运算，定义：给定一组域,• • •,,允许其中某些域是相同的，,• • •,的笛卡尔积为××•••×={(,• • •,)|∈= 1,2,• • •,n}其中每一个元素(,• • •,)叫作一个n元组(n-tuple),或简称元组(tuple),元素中的每一个值叫作一个分量一个域允许的不同取值个数称为这个域的基数若。
复制链接

扫一扫