数据库笔记

本文参考学习自《数据库系统概论第五版》

第一章绪论

1.1数据库系统概述

数据库的四个基本概念

数据

​ 数据库中存储的基本对象，描述事物的符号记录，是现象，而信息更反映实质

数据库(DB, Database)

​ 长期存储在计算机内，有组织，可共享的大量数据的集合

数据库系统DBS

​ DBS是实现有组织的、动态的存储大量关联数据、方便多用户访问的计算机硬件、软件和数据资源组成的系统，即采用数据库技术的计算机系统

​ 根本目标是解决数据的共享问题

数据库系统的构成

​ 1.数据库(DB)

​ 2.数据库管理系统(DBMS)

​ 3.应用程序

​ 4.数据库管理员(DBA)：从事管理和维护数据库管理系统

数据库管理系统(DBMS)

​ 是位于用户和操作系统之间的一层管理软件，专门管理数据库的计算机系统软件，作为一个门，通过它才能访问DBS

​ 常见的DBMS：

​ MySQL：适用于所有平台，便宜

​ Oracle：适用于所有平台，非常昂贵，安全性高，性能最好

​ SQL Server : 适用于windows，昂贵

主要功能

​ 1.数据库定义：定义数据库中Table的名称，标题等

​ 2.数据库操纵：对数据进行增删查改

​ 3.数据库控制：控制数据的使用权

数据库语言(标准的：SQL语言)

​ 使用者通过数据库语言利用DBMS操作数据库

​ DDL：数据定义语言，用于改变数据库结构，包括创建、更改和删除数据库对象

​ DML：数据操纵语言，增删查改

​ DCL：数据控制语言，对数据访问权进行控制

数据管理系统的产生和发展

数据管理的发展：人工管理阶段，文件系统阶段，数据库系统阶段

好坏标准：1)数据冗余，2)数据共享，3)数据独立性

数据库系统特点

1.数据结构化

​ 整体数据的结构化，数据最小存取单位是数据项，数据的结构用数据模型描述

2.数据的共享性高，冗余度低且易扩充

3.数据独立性

​ 由数据管理系统的二级映像功能来保证

​ 物理/逻辑独立性：数据的物理存储/逻辑结构改变，应用程序不用变

4.数据由数据管理系统统一管理和控制

​ 1.数据的安全性保护：主动安全(安全机制)和被动安全(备份恢复)

​ 2.数据完整性检查：保证数据的正确性，有效性和相容性

​ 3.并发控制

1.2数据模型

​ 数据模型是对现实世界中数据特征的抽象，是数据库系统的核心和基础

​ 数据库中的数据是按一定的方式存储在一起的，其组织结构又称数据模型，它决定了数据库中数据之间联系的表达方式。分为两种：

​ 1.概念模型(信息模型)

​ 2.数据模型(逻辑模型和物理模型)

数据模型的三要素：数据结构，数据操作，数据的完整性约束

两类数据模型

1.概念模型(信息模型)

​ 反映关系，常用实体-联系方法

​ ER图，长方形-实体，椭圆-属性，棱形-联系，线上得标出1对1，1对n和n对m；属性可以添加在实体和联系上

2.数据模型(逻辑模型和物理模型)

现实世界=>信息世界(概念模型)=>机器世界(逻辑模型=>物理模型)

前两个转换由数据库设计人员完成，后面由DBMS完成

三大经典模型

1.层次模型(树形结构)

​ 1.结点双亲唯一

​ 2.只能处理1对n

​ 3.无独立个体

2.网状模型(有向图)

​ 1.允许多个结点无双亲

​ 2.允许一个结点又多个双亲

​ 3.允许两结点间多种联系

3.关系模型(主流)

​ 存取路径隐蔽，用户只要说明做什么。

​ 关系模型的数据结构

• 关系：一个关系对应一张表
• 元组：表中一行为一个元组
• 属性：表中一列为一个属性
• 主码：也称码键，某个属性组，可以唯一确定一个元组
• 分量：元组中的一个属性值，必须是不可再分的

​ 完整性约束条件

• 实体完整性

  • 简单来说就是关系中主属性值不允许为空

• 参照完整性

  • 要求关系中不允许引用不存在的实体

• 用户定义的完整性

  在关系模式中，任何关系都必须满足实体完整性和参照完整性两个条件

1.3数据库系统的结构

基本概念

• 型：对某一类数据的结构和属性的说明
• 值：是型的一个具体赋值
• 模型：是数据管理基本特征的抽象，是数据库的核心和基础
• 模式：是对给定模型的具体数据的描述，是全体数据的逻辑结构和特征的描述

1.3.2数据库系统的三级模式

外模式(一对多用户) -> 模式(一对多外模式) -> 内模式

1. 外模式(用户模式/子模式)
   • 数据库用户使用的局部数据的逻辑结构和特征的描述
   • 保证数据库安全性的有利措施(封装)
   • 独立于存储模式和存储设备
2. 模式(逻辑模式)
   • 全体数据的逻辑结构和特征的描述
   • 一个数据库只有一个模式，是数据库系统模式结构的中间层
3. 内模式(存储模式)
   • 数据物理结构和存储方式的描述
   • 一个数据库只有一个内模式，是数据在数据库内部的表示方式
   • 依赖于它的全局逻辑结构，独立于具体的存储设备

1.3.3数据库的二级映像和数据独立性

​ 从以程序为中心发展到以数据为中心

​ 外模式：描述数据的局部逻辑结构

​ 模式：描述数据的全局逻辑结构

​ 1.外模式/模式映像：保证数据与程序的逻辑独立性，可多个

​ 当模式改变时，DBA对该映像作出相应改变，使外模式不变，应用程序是依据数据的外模式编写的，从而应用程序不必修改

​ 2.模式/内模式映像：保证数据的物理独立性，只唯一

​ 当数据库的存储结构改变时，DBA对该映像作出相应改变，使模式不变，应用程序也不受影响

数据库系统怎样实现数据物理独立性和逻辑独立性？

数据库管理系统在三级模式之间提供的两层映像保证了数据库系统中的数据能够具有较高的逻辑独立性和物理独立性

1.4数据库系统的组成

---

第二章 关系数据库

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

单一的数据结构–关系，数据的逻辑结构–二维表

1. 域：一组具有相同数据类型的值的集合，二维表中的一列

2. 笛卡尔积：D1 x D2 x ……Dn={(d1,d2,……dn)|di属于Di ,i=1,2,……n}

   笛卡尔积基数(元组数)=各集合元素数相乘。

   笛卡儿积中许多元组是无视意义的

元组的每个分量di是按序排列的,如(1,3,2)!=(1,2,3)

关系中的元素是没有排序次序的

3. 关系：笛卡尔积的子集，表示为R(D1,D2……Dn)

   • 属性：关系中不同列字段可以对应相同的域

   • 候选码：若关系中的某一属性组能唯一地标识一个元组，而其子集不能，则称该属性组为候选码

   • 主码：若一个关系有多个候选码，则选定其中一个为主码，

     一个关系表中的主码只能有一个

     主码可以是单个属性，也可以是属性组

   • 候选码的诸属性称为主属性，不包含在任何候选码中的属性称为非主属性或非码属性

   • 全码：一个关系模式中所有属性构成码

逻辑模型

特征：数据结构，操作，约束条件

候选码：可唯一确定某一行，不一定只有一种

关系模式

关系模式：对关系的描述，静态稳定，可形式化地表示为R(U,D,DOM,F)，通常可以简记为：

R(U) 或R(A1,A2……An)

A1,A2……An:属性名

例：一个表示学生的关系可简记为：

学生（学号，姓名，性别，年龄，班级，系别）

关系数据库：所有实体及实体之间联系的关系的集合构成

2.2关系操作

对关系可以执行的操作包括：查询，插入，删除和更新

关系数据库语音分类

关系代数语言，关系演算语言，及介于两者的SQL

2.3关系完整性

1. 实体完整性规则

   若属性A是基本关系R的主属性，则A不能取空值(即是不知道，不存在或无意义的值)

2. 参照完整性规则

   设有R(…F…) , S(F…)两个基本关系，F是S的主码，则称F是R的外码，R是参照关系，S是被参照关系

   对于R中每个元组在F上的值要么为空值，要么等于S中某个元组的主码

   

3. 用户定义完整性

   针对某一具体关系数据库的约束条件

2.4关系代数

关系代数的运算

传统集合运算

• 并，交，差

  • 关系R,S进行上述运算前提

    • 两个关系的属性数目必须相同，即列相同
    • 对于每个i，R的第i个属性的域必须和S的第i个属性的域相同

    

  

  R∩S = R – (R – S) = S – (S – R)

• 广义笛卡尔积R*S

  

  属性个数=a+b，元组个数=a*b

  

若有重名的，需重新命名，如R.A，S.A

专门的关系运算

1. 选择σ：选择给定条件的元组—-行，对应Where

2. 投影π：关系R上的投影是从R中选中若干属性列—-列，避免重复行，对应SELECT

3. 连接 ：从两个关系的笛卡尔积中选择属性间满足一定条件的元组。

   1. 自身连接当与自身连接时，采用更名操作

      

   2. 等值连接

      

   3. 自然连接：特殊的等值连接，相等的分量(重复)为公共属性，将重复的属性列其去掉

      

   4. 外连接，在R,S自然连接时，对不匹配的元组用空置来匹配，有左外连接，右外连接和全外连接

      

      

   注意有关联左右的放中间

   

4. 除运算

​

最基本五种运算

并(∪)、差(－)、投影(π)、选择(σ)和笛卡儿积(×)

其他3种运算均可以用5种基本运算来表达

多个字符串用+连接起来

---

第三章 SQL

SQL概述

结构化查询语言，是关系数据库的标准语言

特点

1. 综合统一

   集数据定义语言(DDL),数据操纵语言(DML)，数据控制语言(DCL)功能于一体

2. 高度非过程化

   SQL只要提出做什么，无需了解存取路径

3. 面向集合的操作方式

4. 以同一种语法结构提供多种使用方式

5. 语言简洁易学易用

SQL支持关系数据库三级模式结构

​ SQL
​ /
外模式 视图 视图 （虚表）
​ | |
模式 基本表 基本表 基本表
​ | / |
内模式 存储文件 存储文件

SQL数据定义语句

操作方式：创建CREATE 删除DROP 修改ALTER

操作对象：模式 SCHEMA 表 TABLE 视图 VIEW 索引 INDEX

除了视图和模式没有修改语句外，其他操作方式都可对应四种对象

实例=>数据库=>模式=>表，视图和索引 [箭头皆为可一对多的关系，由一个建立多个]

创建数据库

CREATE DATABASE 数据库名称
ON PRIMARY
{数据文件}
LOG ON
{日志文件}

3.3数据定义

模式

定义模式(命名空间)

CREATE SCHEMA 模式名 AUTHORIZATION (用户名) //AUTHORIZATION授权

创建模式的用户必须拥有DBA权限，或获得DBA授予的创建模式的权限

删除模式

DROP SCHEMA 模式名 CASCADE|RESTRICT

• CASCADE 级联

  删除模式同时把该模式中的所有数据库对象全部删除

• RESTRICT 限制

  如果该模式定义了下属数据库对象，则拒绝该删除语句的执行(保护下属)

• NULLFIES 置空值

基本表

数据类型表

定义基本表

CREATE TABLE 表名
( 	列名 数据类型  列级完整性约束条件，
      。。。。。。
	表级完整性约束条件
)

表级完整性约束条件

• UNIQUE 唯一性约束

• PRIMARY KEY 定义主码，保证唯一性和非空性

  用法：PRIMARY KEY (列名,列名。。。）

  • 对于单属性构成码，既可以列级，也可以表级

  • 对于多个属性构成的码只有表级

• FOREIGN KEY 用于定义参照完整

  用法：FOREIGN KEY (外码列名) REFERENCES 被参照表名(被参照列名)

模式与表，一个模式包含多个基本表

定义基本表所属模式

1. 在表名明显给出模式名

   Create table “模式名”. 表名（）

2. 在创建模式语句中同时创建表

3. 设置所属的模式

   SET search_path TO "模式名"，PUBLIC；
   Create table 表名 () 
   

修改基本表

• ADD 用于增加新列或约束条件
• DROP COLUMN 用于删除表中的列
• DROP CONSTRAINT 用于删除指定的完整性约束条件
• ALTER COLUMN 用于修改列定义(列名和数据类型)

索引

一个指向表中数据的指针

建立目的：加快查询速度

​ DBA或建立表的人可以建立索引

​ DBMS会自动建立PRIMARY KEY和UNIQUE上的索引，会自动维护索引，自动选择是否使用及使用哪些索引

分类

• 按存储方式分：聚集索引和非聚集索引
• 按维护与管理索引分：唯一索引和组合索引等

建立索引

CREATE (UNIQUE) (CLUSTER) INDEX 索引名
ON 表名 (列名 次序，列名 次序。。。)

• 次序：指定索引值的排列次序
  • 升序：ASC 降序：DESC 缺省值：ASC
• CLUSTER:表示要建立的索引是聚簇索引

修改索引

ALTER INDEX 旧索引名 RENAME TO 新索引名

删除索引

DROP INDEX 索引名

3.4数据查询

SELECT [ALL|DISTINCT] 目标列表达式[AS]别名,...  //DISTINCT：去重 ALL：不去重，默认
FROM 表名或视图名,...
[WHERE 条件表达式]	//WHERE分组前进行先筛选,HAVING在分组后对生成的组进行筛选
[GROUP BY 列名 [ HAVING 条件表达式] ]
[ORDER BY 列名 [ ASC | DESC] ]
[COMPUTE 聚集函数]	//GROUP只能显示统计结果，COMPUTE可以显示详细数据和统计结果

投影运算(选择列)：SELECT 投影(π)

选择运算(选择行)：WHERE 选择(σ)

分类汇总：GROUP BY

对分类汇总进行筛选：HAVING

排序：ORDER BY

单表查询

选择若干列

全部列

• SELECT 所有列名
• SELECT *

说明文字串用单引号括起来\’ \’

补：TOP: 返回表中前面一定数量的数据

SELECT [TOP 行数|TOP 百分比 PERCENT]
FROM table_name

选择若干元组

<> 等价于!= ,一般用前者

IN <值表>，NOT IN <值表>  //确定集合

当查询的字符串本身就含有%或_时，用ESCAPE \’换码字符\’进行转义

WHERE name LIKE \'[^李]%\';				  //表示不姓李的
WHERE name LIKE \'[李，张]%\';				//姓李和姓张的
=name LIKE\'李%\'OR name LIKE\'张%\';
=name IN (\'李%\',\'张%\');
WHERE Cname LIKE \'DB\_design\' ESCAPE \'\\';  //表示查询课程名为DB_design

空值查询：IS NULL或IS NOT NULL，IS不能用=代替

排序ORDER BY

只影响表的逻辑顺序，不影响真正的物理顺序

升序排序：ASC 降序排序：DESC ，默认升序排序

多个排序列，先按第一列排序，遇到相同按第二列排序

主要聚集函数

COUNT ([DISTINCT|ALL] *)		//1.计数 除了COUNT(*),其它聚集函数忽略空值
COUNT ([DISTINCT|ALL] <列名>)
SUM ([DISTINCT|ALL] <列名>)	   //2.总和
AVG ([DISTINCT|ALL] <列名>)	   //3.平均值
MAX ([DISTINCT|ALL] <列名>)	   //4.最大值
MIN ([DISTINCT|ALL] <列名>)	   //5.最小值
//WHERE子句不能出现聚集函数作为条件，这时应用HAVING子句

GROUP BY

使用GROUP BY 进行分组，SELECT子句只可能：

• 列名应用了聚集函数
• 未应用聚集函数的列包含于GROUP BY子句中

连接查询

一般格式：

[<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>

[<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>

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

连接运算符：=

• 嵌套循环法
• 排序合并法
• 索引连接法

当单个查询引用多个表时，里面同名的列需加上表名修饰：表名. 列名

2.自身连接

一个表与自己进行连接，需起别名以区别，一旦使用别名代替原名，则不能使用原名

SELECT FIRST.Cno,SECOND.Cpno
FROM Course FIRST,Course SECOND
WHERE FIRST.Cpno=SECOND.Cno

3.外连接

​ 普通连接只输出满足连接条件的元组，外连接以指定表为主体表，其不满足连接条件的元组一并输出，分为

• 左外连接

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

  连接运算符左边的Student在SC里找不到满足的全部设为NULL一并输出，左边为主表

• 右外连接

• 完全外连接

4.多表连接

嵌套查询

​ 一个SELECT-FROM-WHERE语句称为一个查询块，将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件的查询称为嵌套查询

1.带有IN谓词的子查询

构造嵌套查询：1.先构造子查询(父查询条件) 2.再构造父查询

不相关子查询：子查询查询条件不依赖于父查询，由内向外逐层处理

子查询结果是集合，可嵌套于：WHERE,HAVING,FROM ，不能使用ORDER BY子句

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

当能确切知道内层查询返回单值时，可用比较运算符(>,<,=,>=,<=,!=或<>)

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

必须同时使用比较运算符

4.带有EXISTS谓词的子查询

EXISTS：子查询若非空，返回真；若为空，返回假。

NOT EXISTS ：子查询若非空，返回假；若为空，返回真 (用法：双重否定)

所有带IN，比较运算符，ANY和ALL的子查询都能用带EXISTS的子查询等价替换

集合查询

• 并操作 UNION(自动去重)，类似 OR 保留重复的：UNION ALL

• 交操作 INTERSECT 类似 AND

• 差操作 EXCEPT

参与集合操作的各查询结果的列数必须相同，对应项的数据类型也必须相同

基于派生表的查询

​ 子查询不仅可以出现在WHERE子句中，还可以出现在FROM子句，此时子查询生成的临时派生表成为主查询的查询对象

补充：

year(时间) : 获取指定日期年份

year(getdate()-year(出生年月))  //求年龄

3.5数据更新

DBAS在执行插入,修改，删除语句时，会检测所插元组是否破坏表上已定义的完整性规则

插入数据

• 插入元组

  INSERT
  INTO <表名> [(<属性列1>...)]
  VALUES (<常量1>...)
  

  • 属性列的顺序可与表定义的顺序不一致
  • 没有指定属性列说明插入的是完整的，且属性列属性与表定义中的顺序一致的元组
  • 指定部分属性列，其余属性列取空值
  • 常量个数和类型必须与属性列匹配

• 插入子查询结果(即多个元组)

  INSERT
  INTO <表名> [(<属性列1>...)]
  子查询
  

  常量个数和类型必须与属性列匹配

修改数据

UPDATE <表名>
SET <列名>=<表达式>[,<列名>=<表达式>]...
[WHERE <条件>]

如果省略WHERE子句，表示要修改表中的所有元组

删除数据

DELETE
FROM <表名>
[WHERE<条件>]

如果省略WHERE子句，表示要删除表中的所有元组，表的定义仍在字典中

空值处理

空值判断

IS NULL或 IS NOT NULL

空值约束条件

• 有NOT NULL约束条件的不可取空
• 加了UNIQUE限制的不可取空
• 码属性不可取空

空值运算

• 空值与另一个值的算术运算结果为空值
• 空值与另一个值的比较运算结果为UNKNOWN

3.6视图

虚表，只存放视图的定义，不存放视图对应的数据

视图主要为了简化用户操作，提供安全服务

建立视图

CREATE VIEW <视图名> [(<列名>[，<列名>])]  //将视图定义放入数据字典
AS<子查询>
[WITH CHECK OPTION]  //对视图进行更新，插入和删除时需进行谓词条件检测

属性列名只能全部省略或全部指定

由单个基本表导出，且只去掉了基本表中某些行和某些列，保留了主码。称这类视图为行列子集视图

删除视图

DROP VIEW <视图名>[CASCADE]

• 从数据字典中删除指定的视图定义，撤销一个视图，但不消除数据，在基础表中的数据仍然保留
• 如果该视图导出其他视图，需用CASCADE级联删除语句一同删除
• 删除基表时，由其导出的所有视图定义需显示地用DROP VIEW语句删除

查询视图

用户角度：查询视图和查询基本表相同

DBMS实现视图查询方法

视图消解法

• 进行有效性检查
• 转换为等价的对基本表的查询
• 执行修正后的查询

更新视图

有些视图不允许被更新，更新视图一般对行列子集视图

对视图进行更新操作，其限制条件比较多，尽量不要进行更新操作

视图作用

• 简化用户操作

• 对机密数据提供安全保护

  为不同用户定义不同视图，使其只能看到权限内的数据

• 使用户以多种角度看待同一数据

• 对重构数据库提供了一定程度的逻辑独立性

GRANT INSERT
ON TABLE SC 
TO U5
WITH G

CREATE USER

有三种权限：CONNECT RESOURCE DBA

角色是权限的集合

CREATE ROLE<角色名>

第四章 数据库安全性

概述

数据库的数据保护

• 数据库安全性
  • 保护数据库防止恶意破坏和非法存取
  • 防范对象：非法用户和非法操作
• 数据库完整性
  • 防止错误信息的输入和输出所造成的无效操作和错误结果
  • 防范对象：不合语义的数据

4.1计算机安全性概述

数据库安全性

指保护数据库，防止用户非法使用数据库造成数据泄露，更改和破坏

数据库系统安全保护措施是否有效是数据库系统主要的性能指标之一

数据库不安全因素

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

   提供的安全措施主要包括：用户身份鉴别，存取控制和视图等技术

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

   提供的主要技术有：强制存取控制，数据加密存储和加密传输等

3. 安全环境的脆弱性

4.2安全性控制

存取控制机制的功能

• 定义用户权限
• 合法权限检查

常用存取控制方法

• 自主存取控制DAC
• 强制存取控制MAC

自主存取控制DAC

通过SQL的GRANT和REVOKE语句实现，定义用户存取权限(授权)

用户权限组成：数据对象，操作类型

SQL Server的安全机制

建立在身份验证和访问许可两种安全机制

主要包括三个等级

• 服务器级别的安全性
• 数据库级别的安全性
• 数据对象级别的安全性

两种身份验证模式

• Windows身份验证

• 混合验证

  windows+SQL Server

添加一个Windows NT用户或用户组

CREATE LOGIN [WTQ-PC\Test]  //用户：WTQ-PC\Test
FROM WINDOWS
WITH DEFAULT_DATABASE=DBS	//数据库：DBS

添加一个SQL Server用户

CREATE LOGIN sql			//用户名：sql
WITH PASSWORD = \'pwd\'		//密码：pwd
DEFAULT_DATABASE=DBS		//数据库：DBS

删除登录账号

DROP LOGIN [WTQ-PC\Test]
DROP LOGIN sql

授权：授予与回收

授予

GRANT <权限>[,<权限>]...
ON <对象类型> <对象名>[,<对象类型> <对象名>]...//如table
TO <用户>[,<用户>]...
[WITH GRANT OPTION];	//指定：可再授予  未指定：不能传播  不允许循环授权

发出者：数据库管理员，数据库对象创建者，拥有该权限的用户

接收者：一个或多个具体用户，PUBLIC(全体用户)

GRANT ALL PRIVILEGES //授予全部权限
ON XXX XX
TO PUBLIC;	//授予所有用户


GRANT UPDATE(Sno)  //对属性列的授权时必须明确指出相应属性列名

回收

由数据库管理员或其他授权者用REVOKE收回

REVOKE <权限>[,<权限>]...
ON <对象类型> <对象名>[,<对象类型> <对象名>]...
FROM <用户>[,<用户>]...[CASCADE|RESTRICT]  //系统只收回直接或间接从U5获得的权限

例子

GRANT INSERT
ON TABLE SC
TO U5
WITH GRANT OPTION

• 此时若回收U5的INSERT权限时应该使用CASCADE来同时回收U6,U7的INSERT权限，否则拒绝执行该语句
• 若U5,U6还从其他用户处获得对SC表的任何权限，则他们仍然具有该权限。

SQL具有灵活的授权机制

• 数据库管理员
  • 拥有所有对象的所有权限
  • 可将不同权限授予不同用户
• 用户
  • 拥有自己建立的对象的全部的操作权限
  • 可以使用GRANT将权限授予其他用户
• 被授权用户
  • 拥有被授权的特定对象的特定权限
  • 如果具有再授权许可，可再授权

数据库管理员创建用户时

CREATE USER <username>
[WITH][DBA|RESOURCE|CONNECT]

权限名	创建新用户	创建基本表和视图	创建新模式	登录数据库，执行数据查询和操纵
CONNECT(默认)	否	否	否	是，但需拥有相应权限
RESOURCE	否	是	否	是
DBA(超级用户)	是	是	是	是

数据库角色

角色是权限的集合，可为一组具有相同权限的用户创建一个角色

CREATE ROLE <角色名>	   //创建角色
GRANT <角色1>[,<角色2>]...//将角色授予其他角色或用户
TO <角色3>[,<用户1>]...
[WITH ADMIN OPTION]		//=全部权限+可再授予权限

强制存取控制MAC

自主存取控制

可能存在数据”无意泄漏”,数据本身无安全性标记

保证更高的安全性，适用于对数据有严格而固定密级分类的部门

管理的全部实体分为

• 主体是系统中活动实体
  • 实际用户
  • 代表用户的各进程
• 客体是系统中被动实体，受主体操纵
  • 文件，基本表，索引，视图

敏感度标记(Label)

• 绝密 (Top Secret, TS)
• 机密 (Secret, S)
• 可信 (Confidential，C)
• 公开 (Public, P)
• TS>=S>=C>=P

主体敏感度标记称为许可证级别；客体称为密级

MAC规则：当主体许可证级别>=客体密级，可读；当主体许可证级别<=客体密级，可写

4.3视图机制

4.4审计Audit

启用一个专用的审计日志，将用户对数据库的所有操作记录在上面

作用：审计员利用审计日志，找出非法存取数据的人，时间和内容

可选性：由于审计费时间空间，所以主要用于安全性较高要求的部门，且可灵活打开或关闭审计功能

审计事件

• 服务器事件
• 系统权限
• 语句事件
• 模式对象事件

审计功能

• 基本功能：提供多种审计查阅方式
• 提供审计分析和报表功能
• 审计日志管理功能
• 提供审计设置及记录信息的专门视图

AUDIT ALTER
ON SC;
NOAUDIT ALTER
ON SC;

4.5数据加密

作用：防止数据库中数据在存储和传输中失密的有效手段

基本思想：原始数据明文通过算法变换位不可直接识别的格式-密文

加密方式

• 存储加密
  • 透明存储加密
    • 内核级加密方法：性能好，安全完备性高
  • 非透明存储加密
• 传输加密
  • 链路加密：链路层加密，报文报头均加密
  • 端到端加密：只加密报文，不加密报头

4.6其他安全性保护

• 推理控制
  • 避免用户利用能访问的数据推知更高密级的数据
  • 常用方法：基于函数依赖或敏感关联
• 隐蔽信道
• 数据隐私保护

第五章数据库完整性

• 数据的完整性

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

• 数据的安全性

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

数据库完整性

1. 完整性约束条件定义机制

   实体完整性，参照完整性，用户定义完整性

2. 完整性检查机制

   一般在插入，更新，删除语句执行后开始检查

3. 违约反应

   • 拒绝(NO ACTION)执行该操作
   • 级联(CASCADE)执行其他操作

实体完整性违约处理

进行插入或更新操作时自动进行完整性检查

• 检查主码是否唯一，不唯一则拒绝插入或修改

  检查方法

  • 全表扫描：费时
  • B+树索引

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

参照完整性违约处理

1. 拒绝(NO ACTION)执行

2. 级联(CASCADE)操作

   当删除或修改被参照表的一个元组时造成了不一致，则将参照表中所有不一致元组删除或修改

3. 设置为空值(SET-NULL)

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

FOREIGN KEY (外码列名) REFERENCES 被参照表名(被参照列名)
 ON UPDATE/DELETE CASCADE，  //级联更新/删除。。。
 ON DELETE NO ACTION //拒绝删除

用户定义完整性

针对某一具体应用的数据必须满足的语义要求

1. 列值约束条件

• 列值非空(NOT NULL)

• 列值唯一(UNIQUE):可为空值

• 检查列值是否满足某个条件表达式(CHECK)

  CHECK 条件表达式

• 列值缺省值(DEFAULT)

  DEFAULT 缺省值

2. 元组约束条件

   用CHECK短语可以设置不同属性之间取值的相互约束条件

完整性约束命名子句

CONSTRAINT <完整性约束条件名><完整性约束条件>
DROP CONSTRAINT xx

断言

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

断言创建以后,任何涉及断言的相关操作都会进行检查,任何使断言不为真值的操作都会被拒绝执行

 CREATE ASSERTION<断言名><CHECK子句>	//创建
 DROP ASSERTION<断言名>;			 //删除

触发器Trigger

用户定义在关系表上的一类由事件驱动的特殊过程

触发器是建立在某个表上的一系列能有系统自动执行对数据库修改的SQL语句的集合即程序,经过预编译后存储于数据库中

也称为主动规则或事件-条件-动作规则

CREATE TRIGGER <触发器名>
{BEFORE|AFTER}<触发事件>(OF <触发列...>) ON <表名>  //事件:插入,删除,修改等操作
REFERENCING NEW|OLD ROW AS<变量>
FOR EACH {ROW|STATEMENT}
{WHEN <触发条件>} <触发动作体>

触发器只能定义在基本表上,不能定义在视图上.表的拥有者才可以在表上创建触发器

触发器类型

• 行级触发器

  该表有多少行,触发动作执行多少次

• 语句级触发器

  执行某语句后触发动作只执行一次

p72

第六章关系数据理论

关系模式由五部分组成，是一个五元组

R(U,D,DOM,F)

• 关系名R是符号化的元组语义
• U为一组属性
• D为U中属性所来自的域
• DOM为属性到域的映射
• F为U上的一组数据依赖

由于D,DOM与模式设计关系不大，因此本章将关系模式看作三元组:R<U,F>

6.1 数据依赖

• 关系内部属性与属性之间的一种约束关系
  • 通过属性间值的相等与否体现出来的数据间相互联系
• 是现实世界属性间相互联系的抽象
• 是语义的体现

主要类型

• 函数依赖 FD
• 多值依赖 MVD

例：Sname=f(Sno),Sdept=f(Sno)

• Sno函数决定Sname，Sno
• 记作：Sno->Sname, Sno->Sdept

不合适的函数依赖会导致存在

1. 数据冗余 后者出现重复性
2. 更新异常 修改后者可能需要修改每个相关的前者
3. 插入异常 没有前者就无法存入后者
4. 删除异常 前者删除，后者消息丢失

解决方法：用规范化理论改造关系模式来消除其中不合适的数据依赖

6.2 规范化

函数依赖

A->B

简单来说就是一对一，不会出现一对多或多对一

平凡函数依赖与非平凡函数依赖

• X->Y, 但Y不属于X，则称X->Y是非平凡函数依赖
• X->Y, 但Y属于X，则称X->Y是平凡函数依赖

综上所述，函数依赖分为：完全函数依赖，部分函数依赖，传递函数依赖

码与候选码

候选码的求解算法！！！

求解步骤

推论2很好用！！！

主属性与非主属性

范式

符合某一级别的关系模式的集合

• 第一范式 1NF
• 第二范式 2NF
• 第三范式 3NF
• BC范式 BCNF
• 第四范式 4NF
• 第五范式 5NF

大小按从上往下排，上包含下，一个低一级的范式通过模式分解可以转换为若干个高一级范式的关系模式的集合，这个过程就叫规范化

1NF：关系模式R的所有属性值域不可再分

2NF：R属于1NF，且每个非主属性都完全函数依赖于任何一个候选码，则R属于2NF

3NF：R属于3NF，则R的每一个非主属性既不部分函数依赖于候选码，也不传递函数依赖于候选码

BCNF：模式的彻底分解，达到了最高的规范化程度

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

多值依赖:X,Y,Z为R(U)子集，且Z=U-X-Y. X->->Y，每个X对应一组Y值，而不论Z取何值

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

• 若X->->Y，而Z为空集，则称X->->Y为平凡的多值依赖
• 否则称X->->Y为非平凡的多值依赖

4NF：若R<U,F>属于1NF，如果对于R的每个非平凡多值依赖

X->->Y(Y不属于X)，X都含有码，则R<U,F>属于4NF

小结

在关系数据库中，基本要求是满足第一范式

规范化的基本思想：

• 逐步消除数据依赖中不合适的部分，使各关系模式达到某种程度的”分离”
• 即采用”一事一地”的模式设计原则
  • 让一个关系描述一个概念，一个实体或者实体间的一种联系
  • 若多于一个概念则分离
• 规范化实质上是概念的单一化

并非规范化程度越高的关系模式越好

6.3 Armstrong公理系统

设U为属性集总体，F是U上的一组函数依赖，于是有关系模式R<U,F>，对R<U,F>来说有以下的推理规则：

• 自反律

• 增广律

• 传递律

  

• 合并规则：由X→Y,X→Z，得X→YZ

• 伪传递规则：由X→Y，WY→Z，有XW→Z

• 分解规则：由X→Y，Z属于Y，有X→Z

闭包！！

在关系模式R<U,F>中为F所逻辑蕴涵的函数依赖的全体叫做F的闭包，记作F+

即：F+是所有能从F中推导出来的函数依赖的集合

闭包求法：

最小依赖集

每个函数依赖集F均等价于一个极小函数依赖集Fm，此Fm称为F的最小依赖集

最小依赖集求解步骤！！！

1. 将所有函数依赖的右边化为单一属性

2. 去掉所有冗余的函数依赖

3. 去掉所有函数依赖左边的冗余属性

   找左边多于1个属性，分别检查是否多余

G为去掉考察的剩余关系集

如：当检查AB→C，G为F-{AB→C}={AB→D,A→D}

属性在G的闭包至少等于它本身

判断是否冗余，需要删除的依据是，看这个检查的推出的是否属于它的闭包，如：AB→C, C不属于(AB)G+=ABD，则AB→C不冗余，不可删

6.4模式分解

• 关系模式的规范化过程是通过对关系模式的分解来实现的

三种模式分解的等价定义

1. 分解具有无损连接性
2. 分解要保持函数依赖
3. 分解既要保持函数依赖，又要具有无损连接性

无损连接分解判断！！！

算法

① 建立一张n列k行的表，每一列对应一个属性，每一行对应分解中的一个关系模式。若属性Aj Ui，则在j列i行上真上aj，否则填上bij；

② 对于每一个FDi做如下操作：找到Xi所对应的列中具有相同符号的那些行。考察这些行中li列的元素，若其中有aj，则全部改为aj，否则全部改为bmli，m是这些行的行号最小值。

如果在某次更改后，有一行成为：a1,a2,…,an，则算法终止。且分解ρ具有无损连接性，否则不具有无损连接性。

对F中p个FD逐一进行一次这样的处理，称为对F的一次扫描。

③ 比较扫描前后，表有无变化，如有变化，则返回第② 步，否则算法终止。如果发生循环，那么前次扫描至少应使该表减少一个符号，表中符号有限，因此，循环必然终止。

保持函数依赖分解！！！

1. 先逐一对分解后的集合进行判断包含原集合的哪些函数依赖

   如上面那道题：

   ​ 对于R1(AD)，F1=空集，对于R4(CDE),F4={C->D,DE->C}；

   ​ 函数依赖的左右两边都得包含于分解的集合中才算是它保持了这个函数依赖

2. 把各个集合包含的函数依赖并起来，看是否等于原集合的全部函数依赖

3. 若是，则 保持了函数依赖

练习题：https://blog.csdn.net/qq_37915248/article/details/104765697

最佳分解算法！！！

有R<U,F>，将关系模式R保持依赖地且无损地分解成3NF

1. 求得候选码X

2. 算法6.3保持依赖地分解成3NF

   1. 计算F的最小函数依赖集
   2. 找出不在F中的属性，将它们单独构成一个关系模式，并从R的U中消去这些属性
   3. 对F按具有相同左部原则分组

3. 判断候选码X是否在分解后的子关系模式的属性集中出现

   1. 如果出现，算法已完成
   2. 如果没有出现，就把X作为子关系模式加入属性集

   

习题分析：

1. L：CE，N：ABD，(CE)G+=ABCDE，所以CE为唯一候选码
2. 求最小函数依赖集F1={A→D,A→B,E→D,D→BA,BC→D},删除了DC→A
3. 没有不在F中的属性
4. 按左部分组：ABD，ED，BCD
5. 加入CE得p

重要事实

• 若要求分解保持函数依赖，则分解可以达到3NF，但不一定能达到BCNF
• 若要求分解既保持函数依赖，又具有无损连接，则可达到3NF，不一定到达BCNF
• 若只要求分解无损连接性，那一定可以达到4NF

第七章数据库设计

概述

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

设计方法

• 手工试凑法
• 规范设计方法
  • 基本思想：过程迭代和逐步求精
  • 典型方法：
    • 新奥尔良方法
    • 基于ER模型的数据库设计方法
    • 3NF设计方法
    • UML方法

数据库设计基本步骤

数据库设计分为五个阶段：

1. 需求分析：主要是准确收集用户信息需求和处理需求，并对收集的结果进行整理和分析，形成需求说明。
2. 概念结构设计：对用户需求进行综合、归纳、抽象，形成一个与与具体的DBMS无关概念模型（一般为ER模型）。
3. 逻辑结构设计：将概念结构设计的概念模型转化为某个特定的DBMS所支持的数据模型，建立数据库逻辑模式，并对其进行优化，同时为各种用户和应用设计外模式。
4. 物理结构设计：为设计好的逻辑模型选择物理结构，包括存储结构和存取方法等，建立数据库物理模式。
5. 实施和维护：实施就是使用DLL语言建立数据库模式，将实际数据载入数据库，建立真正的数据库。维护阶段是对运行中的数据库进行评价、调整和修改

概念结构设计

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

实体用矩形，属性用椭圆，关系用棱形

E-R图之间的冲突

• 属性冲突

  • 属性域冲突，即是类型，取值范围，取值集合
  • 取值单位

• 命名冲突

  • 同名异义
  • 异名同义

• 命名冲突

• 结构冲突

  • 同一对象在不同应用中具有不同抽象(实体/属性)
  • 统一实体在不同子系统E-R图中所包含的属性个数和属性排列次序不完全相同

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

查询步骤

1. 查询分析

   • 词法分析：识别正确的语音符号
   • 语法分析：进行语法检查

2. 查询检查

   • 合法权检查
   • 视图转换
   • 安全性检查
   • 完整性初步检查

3. 查询优化

   分类

   • 代数优化/逻辑优化：关系代数表达式的优化
   • 物理优化：存取路径和底层操作算法的选择

   选择依据

   • 基于规则
   • 基于代价
   • 基于语义

4. 查询执行

   • 自顶向下
   • 自底向上

实现查询操作的算法

• 选择操作的实现

  • 全表扫描方法：适合小表

  • 索引扫描方法：适合选择条件中属性上有索引(如B+树索引或Hash索引)

    同过索引先找到满足条件的元组主码或指针，再通过元组指针直接在查询的基本表中找到元组

• 连接操作的实现（最费时）

  • 嵌套循环算法
  • 排序合并算法
    • 先对两个表进行排序
    • 依次取A表的每个连接属性，然后对应扫描B表具有相同连接属性元组，A(1一个)对应B（多个）连接
    • 循环执行
  • 索引连接算法
  • Hash Join算法
    • 包含较少元组的表先进行散列到Hash
    • 再将剩下的表按相同hash函数散列
    • 将与之匹配的进行连接

查询优化

影响着关系数据库管理系统性能的关键因素也是关系系统的优点，有着非常重要的地位

用户不必考虑最好地表达查询以获得好效率，系统可以优化得更好

• 集中式数据库
  • 执行开销主要包括
    • 磁盘存取块数（I/O代价）
    • 处理机时间（CPU代价）
    • 查询的内存开销
• 分布式数据库
  • 总代价=I/O代价+CPU代价+内存代价+通信代价

代数优化

关系代数表达式等价变换规则

• 规则1：连接，笛卡尔积的交换律

  

• 规则2：连接，笛卡尔积的结合律

  

• 规则3：投影的串接定律

  

• 规则4：选择的串接定律

  

• 规则5：选择和投影的交换律

  

  若与B1，B2也有关，需补上

  

• 规则6：选择与笛卡儿积的交换律

  哪一部分涉及哪些属性，拆分出来时就对应选择哪些属性

• 规则7，8，9：选择 和 并/差/自然连接的分配律：都一样

  

• 规则10：投影与笛卡尔积/并的分配律，同上6

查询树的启发式优化！！！

典型的启发式规则

SELECT 投影(π)

WHERE 选择(σ)

1. 选择运算尽可能先做：减少中间运算结果

2. 投影运算和选择运算同时进行：避免重复扫描关系

3. 合并连接的选择与投影操作，减少扫描次数

4. 把某些选择同它前面要执行的笛卡尔积结合成连接运算：等值连接运算优于笛卡尔积

   

5. 找出公共子表达式，先计算一次并写入中间文件，减少计算量

物理优化

• 基于规则的启发式优化

  大多数情况适用的规则

• 基于代价估算的优化

• 两者结合的优化方法

  • 通常先用启发式规则缩小范围，再用代价估算选出最优

基于启发式规则的存取路径选择优化

1. 选择操作的启发式规则
   • 对于小关系，使用全表顺序扫描
   • 对于大关系，启发式规则
     • 对于选择条件是主码=值的查询
       • 查询结果最多是一个元组，可用选择主码索引
     • 对于选择条件是非主属性=值的查询，或者是属性上的非等值查询或者范围查询，并且选择列上有索引
       • 要估算查询结果的元组数目，比例<10%，可使用索引扫描法
       • 否则采用全表顺序扫描
     • 对于用AND连接的合取选择条件
     • P125

笛卡尔积的选择最好选有公共属性的放在一起下移

第十章 数据库恢复技术

事务

基本解释

事务是用户定义的一个数据库操作序列，要么全做，要么全不做，是不可分割的工作单位

事务与程序

• 一个事务可以是一条SQL语句，一组SQL语句或整个程序
• 一个程序通常包含多个事务

事务是恢复和并发控制的基本单位

事务结束

• COMMIT
  • 事务正常结束
  • 提交事务所有操作(读+更新)
  • 更新写回磁盘上的物理数据库中
• ROLLBACK
  • 事务异常终止
  • 事务运行过程发生故障，不能继续执行
  • 将所有已完成操作全部撤销，事务回滚到开始状态

事务的特性(ACID特性)-事务处理的任务

• 原子性
  • 事务是数据库的逻辑工作单位
• 一致性
  • 事务执行的结果必须使数据库从一个一致性状态变成另一个一致性状态
  • 一致性状态(COMMIT)：数据库中只包含成功事务提交的结果
• 隔离性
  • 事务执行不能受其他事务干扰
• 持续性
  • 一个事务一旦提交，接下来的其他操作或故障都不应对其结果有影响

故障恢复涉及如何保证原子性和持久性

数据库恢复

指将数据库从错误状态恢复到某一已知的正确状态的功能

恢复技术是衡量系统优劣的重要指标

故障种类

1. 事务内部的故障

   事务故障：仅指非预期的故障

   • 某个程序(事务)自身运行错误所引起的故障
   • 影响该程序本身

   事务故障的恢复：事务撤销UNDO

   • 强行回滚该事务
   • 撤销该事务已作出的任何对数据库的修改

2. 系统故障

   称为软故障，指造成系统停止运转的任何事件，使得系统要重启

   原因

   • 硬件错误，操作系统故障，数据库管理系统代码错误，系统断电

   系统故障的恢复

   让所有非正常终止的事务回滚，强行撤销(UNDO)所有未完成事务，重做(REDO)所有已经提交的事务

3. 介质故障（影响最严重）

   称为硬故障，指外存故障，影响正在存取这部分数据的所有事务

4. 计算机病毒

   称为人为故障

各类故障对数据库影响有两种

• 数据库本身被破坏
• 数据库没被破坏，但数据不正确

恢复

恢复操作基本原理：冗余

恢复的基本原理：利用存储在系统别处（后备副本，日志文件，数据库镜像）的冗余数据来重建数据库中不正确的数据

恢复的实现技术：复杂

恢复技术能保证事务的原子性，一致性和持续性

恢复的实现技术

关键问题

1. 如何建立冗余数据
   1. 数据转储
   2. 登记日志文件
2. 如何利用这些数据来恢复

数据转储

指数据库管理员定期地将整个数据库复制到存储介质上保存起来的过程

备用数据文本称为后备副本或后援副本

转储办法

• 静态转储
  • 系统中无运行事务
  • 转储期间不允许对数据库的任何存取修改
• 动态转储
  • 转储操作和用户事务并发
  • 转储期间允许对数据库存取或修改
• 海量转储
  • 每次转储全部数据库
• 增量转储
  • 只转储上次转储后更新过的数据

日志文件

格式和内容

含义：用于记录事务对数据库的更新操作的文件

格式：

• 以记录为单位
  • 各个事务的开始标记BEGIN TRANSACTION
  • 结束标记COMMIT ROLLBACK
  • 所有更新操作
  • 每条记录内容
    • 事务标识
    • 操作类型
    • 操作对象
    • 更新前数据值，更新后数据值
• 以数据块为单位
  • 每条日志记录的内容
    • 事务标识
    • 被更新的数据块

登记日志原则

• 登记次序严格按并发事务执行的时间次序
• 必须先写日志文件，后写数据库
  • 先写日志文件为保证数据库的可恢复性
  • 如果先写了数据库修改，而在运行记录中没有登记下这个修改，则以后就无法恢复这个修改了。
  • 如果先写日志，但没有修改数据库，按日志文件恢复时只不过是多执行一次不必要的UNDO操作，并不会影响数据库的正确性。

恢复策略

事务故障恢复（系统自动完成）

由恢复子系统利用日志文件撤销(UNDO)此时五已对数据库进行的修改

恢复步骤

1. 反向扫描文件日志
2. 对该事务的更新操作执行逆操作
3. 继续反复上述操作
4. 直到读到此事务的开始标记

系统故障恢复（系统自动完成）

造成不一致状态的原因

• 未完成事务对数据库的更新可能已写入数据库
• 已提交事务对数据库的更新可能还留在缓冲区没来得及写入数据库

恢复方法

1. Undo故障发生时未完成的事务
2. Redo已完成的事务

恢复步骤

1. 正向扫描日志文件
   1. 重做REDO队列：故障发生前已经提交的事务
   2. 撤销UNDO队列：故障发生时尚未完成的事务
2. 对撤销UNDO队列事务进行撤销处理
   1. 反向扫描日志文件，对每个撤销事务的更新操作执行逆操作
   2. 即是将更新前的值写入数据库
3. 对重做REDO队列事务进行重做处理
   1. 正向扫描日志文件，对每个重做事务重新执行登记的操作
   2. 即是将更新后的值写入数据库

介质故障恢复（管理员介入）

恢复步骤

1. 重装数据库：装入最小的后备数据库副本，使数据库恢复到最近一次转储时的一致性状态
2. 重做已完成事务
   1. 扫描日志文件，将已提交事务记入重做队列
   2. 正向扫描日志文件，对重做队列所有事务进行重做

具有检查点的恢复技术

• 在日志文件增加检查点记录
• 增加重新开始文件
• 恢复子系统在登录日志文件期间动态地维护日志

检查点记录的内容

• 建立检查点时刻所有正在执行的事务清单
• 这些事务最近一个日志记录的地址

重新开始文件的内容

• 记录各个检查点记录在日志文件中的地址

数据库镜像

提高数据库可用性的解决方案：数据库镜像

第十一章并发控制

多事务执行方式

• 事务串行执行
  • 每个时刻只有一个事务允许，其他食物必须等到该事务结束后方能允许
• 交叉并发方式
  • 单处理机系统中，事务的并行执行是指这些并行事务的并行操作轮流交叉运行
  • 并非真正并行运行，但能减少处理机的空闲时间
• 同时并发方式
  • 多处理机中，每个处理机可运行一个事务

事务并发执行带来的问题=>并发控制机制

• 多个事务同时存取同一数据的情况
• 可能会存取不正确的数据，破坏事务隔离性和一致性

并发控制机制的任务

• 对并发操作进行正确调度
• 保证事务隔离性和数据库一致性

并发操作带来的数据不一致性

• 丢失修改

• 不可重复读

• T1读取某数据，T2对其更新(修改，删除，插入)；T1再读取该数据时该数据已经变化

  • 重新读取了前一事务已提交的数据

• 读脏数据

  • T1修改某数据R1，T2读取R1后T1又撤销原来操作，数据库中的的R1恢复原来T1未修改前的值，此时T2读取到的R1是修改后的，和数据库中的不一致
  • 读取了前一事务未提交的脏数据

并发控制的主要技术

• 封锁
• 时间戳
• 乐观控制法
• 多版本并发控制

封锁

封锁就是事务T对某数据对象操作前，先向系统发出请求，对其加锁，加锁后T对其有一定控制，在T释放该对象的锁前，其他事务不能更新该数据对象

基本封锁类型

• 排它锁-X锁-写锁

  当T对某数据对象A加上X锁后，只有T可以进行读取和修改，其他任何事务都不能对A加锁和操作

• 共享锁-S锁-读锁

  当T对某数据对象A加上S锁后，T可以进行读取，其他任何事务只可以对A加S锁和读取

封锁协议

在运用X锁和S锁对数据对象加锁时，需要约定一些规则，称为封锁协议，形成了三级封锁协议，在不同程度上保证了数据一致性

• 何时申请X锁或S锁
• 持锁时间
• 何时释放

一级封锁协议

• 事务T在修改数据R之前必须先对其加X锁直到事务结束才释放
  • 正常结束COMMIT
  • 非正常结束ROLLBACK
• 一级封锁协议可防止丢失修改，保证事务T是可恢复的
• 若仅读数据，不需要加锁，所以无法保证重复读和读脏数据

二级封锁协议

• 事务T在读取数据R之前必须先对其加S锁，读完即可释放
• 可以防止丢失修改和读脏数据
• 读完即释放，所以无法保证可重复读

三级封锁协议

• 事务T在读取数据R之前必须先对其加S锁，直到事务结束才可释放
• 可以防止丢失修改和读脏数据，不可重复读

活锁和死锁

活锁

• 由于后来的事务先给予了请求批准，导致它前面的某个事务永远处于等待状态，得不到封锁的机会
• 避免活锁的方法：采用先来先服务的策略

死锁

• 事务T1封锁了数据R1，事务T2封锁了数据R2
• 事务T1请求封锁数据R2，事务T2请求封锁数据R1
• 都在等待，形成死锁
• 死锁的预防：破坏死锁的产生条件
  • 一次封锁法
    • 要求每个事务必须一次性将所需数据全部加锁，否则就不能继续执行
    • 难于事先精确确定封锁对象
  • 顺序封锁法
    • 预先对数据对象规定一个封锁顺序，所有事务按照该顺序封锁
    • 成本高，难以实现
• 死锁的诊断：DBMS普遍采用
  • 超时法
    • 一个事务等待时间超过规定时限，认为发生死锁
  • 等待图法
    • 用事务等待图动态反映所有事务等待情况，若发生回路，则表示出现死锁
      • 有向图G=(T,U)
      • T为结点集合，结点表示正运行的事务
      • U为边集合，边表示事务等待情况
      • T1等待T2，则从T1画一条有向边指向T2
• 死锁解除：选择代价最小的事务撤销，释放它所持有的锁

并发调度的可串行性

可串行化调度

• 多个事务的并发执行是正确的，当且仅当其结果与按某一次序串行执行后的结果相同

可串行性

• 并发事务正确调度准则/正确性的准则
• 一个给定的并发调度，当且仅当它是可串行化的，才认为是正确调度

冲突可串行化调度

• 冲突操作，指不同事务对同一数据的读写操作和写写操作
• 一个调度S在保证冲突操作次序不变的情况下，通过交换两个事务不冲突操作的次序得到另一个调度S\’，若S\’为串行的，则S为冲突可串行化调度
• 一个调度是冲突可串行化，则一定是可串行化的调度(充分条件)

两段锁协议

• DBMS普遍采用两段锁协议的方法来实现并发调度的可串行性，保证并发调度的正确性
• 若并发事务都遵守两段锁协议，则对这些事务的任何并发调度策略都是可串行化的
• 两段锁协议指：所有事务必须分为两个阶段对数据加锁和解锁
  • 获得封锁，又称扩展阶段
    • 事务可以申请获得任何数据项上任何类型的锁，但不能释放任何锁
  • 释放封锁，又称收缩阶段
    • 事务可以释放任何数据项上任何类型的锁，但不能再申请任何锁

遵守第三级封锁协议必然遵守两端协议

封锁粒度

• 封锁对象的大小称为封锁粒度
• 封锁对象
  • 逻辑单元：属性值，元组，关系，索引项
  • 物理单元：页（数据页/索引页），物理记录
• 例：
  • 若封锁粒度为数据页，事务T修改元组L，需要等待
  • 若封锁粒度为元组，事务T修改元组L，不需要等待

选择封锁粒度的原则

• 考虑封锁对象和并发度两个因素
• 多粒度封锁：同一系统同时支持多种封锁粒度供选择
  • 多粒度树
    • 根节点是整个数据库，表示最大的数据粒度
    • 叶节点表示最小的数据粒度
• 多粒度封锁协议
  • 允许多粒度树中每个结点单独被加锁
  • 对一个结点加锁意味着它后裔结点加同一锁
  • 一个数据对象可有两种封锁方式
    • 显式封锁：直接加到数据对象上的封锁
    • 隐式封锁：该对象没有独立加锁，是由于上级加锁导致加锁
  • 系统检查封锁冲突时
    • 该数据对象，检查显式封锁
    • 搜索下级结点，
      • 本事务的隐式封锁与下级结点的显式封锁是否发生冲突
    • 搜索上级结点
      • 本事务的显式封锁是否与该数据对象上的隐式封锁冲突
• 意向锁
  • 提高对某数据对象加锁时系统的检查效率
  • 一个结点加意向锁，说明该结点下层结点被加锁
  • 以强锁代替弱锁是安全的，反之不然
  • 分类
    • 意向共享锁IS
      • 对一个数据对象加IS锁，则后裔加S锁，反之
    • 意向排它锁IX
      • 对一个数据对象加IX锁，则后裔加X锁，反之
    • 共享意向排它锁SIX
      • 对一个数据对象加SIX锁，则表示对它加S锁，再加IX锁