数据库系统概论

数据库系统概论

数据库系统的作用

数据与数据管理

　　1）数据。描述事物的符号记录称为数据。数据是数据库中存储的对象，也是数据库管理系统处理的对象。数据和关于数据的解释是不可分的。

　　2）数据管理。数据处理是指对各种数据进行采集、存储、检索、加工、传播和应用等一系列活动的总和。数据管理是对数据进行有效的分类、组织、编码、存储、检索、维护和应用，它是数据处理的中心问题。

数据管理技术的产生与发展

　　1）人工管理阶段。面向应用程序，一个数据集只能对应一个程序，没有相应的软件系统专门负责数据的管理工作。当多个应用程序涉及某些相同的数据时，必须由各自的应用程序分别定义和管理这些数据，无法共享利用，存在大量冗余。

　　2）文件系统阶段。利用文件系统管理数据就是由专门的软件对数据进行统一管理。对于一个特定的应用，数据被集中组织存放在多个数据文件组中，并针对该文件组来开发特定的应用程序。文件系统利用“按文件名访问，按记录进行存取”的管理技术，可以对文件进行修改、插入和删除。

　　文件系统的弊端：数据共享性差，数据冗余和不一致。数据独立性差。数据孤立。数据获取困难。完整性问题（也称为一致性约束）。原子性问题。并发访问异常。安全性问题。

　　3）数据库管理系统阶段。数据库管理系统是由一个相互关联的数据的集合和一组用以访问、管理和控制这些数据的程序组成。这个数据集合通常称为数据库。

　　数据库管理系统的优点：

　　a. 数据整体结构化。

　　b.数据的共享度高，冗余度低，易扩充。数据独立性高。

　　c.数据独立性是用来描述数据与应用程序之间的依赖程度，包括数据的物理独立性和数据的逻辑独立性。

　　物理独立性是指用户的应用程序与存储在磁盘上数据库中的数据是相互独立的。

　　逻辑独立性是指用户的应用程序与数据的逻辑结构是相互独立的。

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

　　DBMS 必须提供：数据的安全性保护。数据的完整性检查。并发控制。数据库恢复。

数据模型

　　数据模型是一个描述数据语义、数据与数据之间联系、数据操作，以及一致性（完整性）约束的概念工具的集合。通过数据模型可以对现实世界的数据特征进行抽象。

数据模型的分类

　　数据模型应满足三方面的要求：一是能比较真实地模拟现实世界；二是容易被人所理解；三是便于在计算机上实现。

　　1）概念模型。又称信息模型，它按用户的观点或认识对现实世界的数据和信息进行建模，主要用于数据库设计。常用的概念模型有 E-R 模型，OO 模型。

　　2）逻辑模型。逻辑层是数据抽象的中间层，用于描述数据库数据的整体逻辑结构。该层的数据抽象称为逻辑数据模型。它是用户通过数据库管理系统看到的现实世界，是按计算机系统的观点对数据建模，即数据的计算机实现形式，主要用于 DBMS 的实现。

　　3）物理模型。物理层是数据抽象的最低层，用来描述数据的物理存储结构和存取方法。它不但由 DBMS 的设计决定，而且与操作系统、计算机硬件密切相关。

数据模型的组成要素

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

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

　　3）数据的完整性约束条件。完整性规则是给定数据模型中数据及其联系所具有的制约和依存规则，用以限定复合数据模型的数据库状态以及状态的变化，以保证数据的正确、有效和相容。

　　数据模型有：层次模型、网状模型、关系模型、面向对象模型、XML模型。

数据抽象与数据库三级模式

数据抽象

　　1）物理层抽象。最低层次的抽象，描述数据实际上是怎样存储的。

　　2）逻辑层抽象。描述数据库中存储什么数据以及这些数据之间存在什么关系。

　　3）视图层抽象。最高层次的抽象，只描述整个数据库的某个部分。

数据库的三级模式

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

　　数据库的三级模式结构是指数据库管理系统提供的外模式、模式和内模式 3 个不同的抽象级别观察数据库中数据的角度。

　　1）模式。也称为逻辑层数据抽象。是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图。DBMS 提供 数据定义语言（DDL）来严格定义模式。模式对应于表。

　　2）外模式。对应于视图层数据抽象，它是数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述。

　　3）内模式。也称存储模式，对应于物理层数据抽象，它是数据的物理结构和存储方式的描述，是数据在数据库内部的表示方式。

数据库的两层映像功能与数据独立性

　　1）外模式/模式映像。定义了外模式与模式之间的对应关系。当模式改变时，由数据库管理员对各个外模式/模式的映像作相应的改变，可以保持外模式不变。保证了数据与程序的逻辑独立性，简称为数据的逻辑独立性。

　　2）模式/内模式映像。当数据库的存储结构改变了，由数据库管理员对模式。内模式映像作相应的改变，可以使模式保持不变。保证了数据与程序的物理独立性，简称为数据的物理独立性。

数据库系统

数据库系统组成

　　从DBMS角度来看：数据库系统结构是外模式/模式/内模式的三级模式；从用户角度看：数据库系统分为单用户结构、主从式结构、分布式结构、客户/服务器、浏览器/应用服务器/数据库服务器等结构。

　　数据库中包含 4 类数据：用户数据、元数据、索引和应用元数据。用户数据就是通过结构化的关系（二维表）组织的所有业务数据的集合；元数据是对关系数据库结构的描述数据和数据库的有关统计数据，也成为数据字典；索引是为了改进数据库的性能和可访问性而建立的附件数据；应用元数据是用户窗体、报表、查询和其他形式的应用组件。

数据库管理系统

DBMS 的功能

　　1）数据定义。DBMS 提供数据定义语言（Data Definition Language，DDL），用户通过它可以方便的对数据库中的数据对象进行定义。

　　2）数据组织、存储和管理。DBMS 分类组织、存储和管理各种数据，包括数据字典、用户数据、数据的存取路径等。

　　3）数据操纵。DBMS 还提供数据操纵语言（Data Manipulation Language，DML）用户通过它实现增删改查。

　　4）数据库的事务管理和运行。数据库在建立、运行和维护时由 DBMS 统一管理、统一控制，以保证数据的安全性、完整性、多用户对数据的并发操作以及发生故障后的系统恢复。

　　5）数据库的建立和维护。数据库初始数据的输入、转换功能，数据库的转储、恢复功能，数据库的重组织功能和性能监视、分析功能等。

DBMS 的组成

　　1）模式更新。对数据库中的逻辑结构进行修改。

　　2）查询。

　　3）更新。

　　4）查询处理器。对用户请求的 SQL 操作进行查询优化。

　　5）存储管理器。根据执行策略，从数据库中取数据或更新数据。

　　6）事务管理器。负责保证系统的完整性，保证多个同时运行的事务不发生冲突。

关系模型与关系代数

关系模型

关系

　　1）关系模型的数据结构为二维表，亦称关系，每个表（关系）有唯一的名字。

　　2）关系数据库是表的集合，即关系的集合。

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

　　1）域。域是一组具有相同数据类型的值的集合。如{‘男’，‘女’}。

　　2）笛卡儿积。

　3）码。

　　　　超码：属性集 A 可以唯一标识关系 r 总中的一个元组，则称属性集 A 为关系 r 的一个超码。

　　　　候选码：对于关系 r 的一个或多个属性的集合 A ，如果属性集 A 的任何真子集都不能成为关系的超码，则称属性集 A 为候选码。

　　　　主码：若一个关系有多个候选码，可以选定其中一个候选码作为该关系的主码。

　　　　总结：主码 属于 候选码 属于 超码。

关系模式

　　关系完整性的约束条件：

　　1）实体完整性。主码不能为空。

　　2）参照完整性。外码要么为空要么能等于被参照关系的某个元组的主码。

　　3）用户自定义完整性。限制关系中某些属性的值符合业务语义要求。如限制性别为男或女。

　　

关系操作

　　关系操作有查询操作和更新操作两大类。查询操作又可分为选择、投影、连接、除、并、交、差、笛卡儿积等。其中选择、投影、集合并、集合差和笛卡儿积是 5 种基本关系操作。

​ 关系代数

　在连接中把不能连接的元组丢弃称为自然连接，把左关系中不能连接的元组保留到结果关系称为左外连接，把右关系中不能连接的元组保留到结果关系中称为右外连接，把左右关系中不能连接的元组都保留到结果关系中称为全外连接。

　　关系代数例题

　　1）查找选修了 08-09 学年第一学期（08091）开出的全部课程的学生学号和姓名。

　　解析：先找出所有08-09 学年第一学期的课程号，然后用选课关系表除这些课程号得到选修了08-09 学年第一学期全部课程学生的学号，再和学生表连接然后投影出学号和姓名。

　　2）查找至少选修了一门其直接先修课编号为 CS012 的课程的学生学号和姓名。

　　解析：先找出先修课编号为 CS012 的课程然后依次连接选修关系和学生关系，最后投影出学生学号（没选过先修课编号为 CS012 的课程的同学会连接失败，如果选了多门先修课编号为 CS012 的课程的同学，投影会将学生学号和姓名去重）。

　　3）查找至少选修了学号为0703010 的学生所选课程的学号和姓名。

　　解析：先找出学号为0703010的学生所选的课程号，然后用选修关系除这些课程号，得到至少选修了学号为0703010 的学生所选课程的学号，再和学生表连接，投影出学号和姓名。

SQL语言

SQL 概述

　　SQL 语言由 4 部分组成，包括数据定义语言 DDL、数据操纵语言 DML、数据控制语言和其他。

　　1）数据定义语言（Data Definition Language，DDL）：主要用于定义数据库的逻辑结构，包括数据库，基本表，视图和索引等，扩展 DDL 还支持存储过程、函数、对象、触发器等的定义。DDL 包括 3 类语言，即定义、修改和删除。

　　2）数据操纵语言（Data Manipulation Language，DML）：主要用于对数据库的数据进行检索和更新，其中更新操作包括插入、删除和修改数据。

　　3）数据控制语言（Data Control Language，DCL）：主要用于对数据库的对象进行授权、用户维护（包括创建、修改和删除）、完整性规则定义和事务定义等。

　　4）其他：主要是嵌入式 SQL 语言和动态 SQL 语言的定义。

　　

　　SQL特点：风格统一。高度非过程化。面向集合的操作方式。同一种语法结构提供两种使用方式（独立使用SQL对数据库进行操作，嵌入到高级语言中）。语言简洁，易学易用。

　　SQL主要动词：

　　1）数据查询：select；

　　2）数据定义：create、alter、drop；

　　3）数据操纵：insert、update、delete；

　　4）数据控制：grant、revoke；

简单查询

　　where 子句可以实现关系代数中的选择运算，用于查询满足选择条件的元组。where 子句中常用的查询条件如下所示：

　% 表示任意长度的字符串，_表示任意一个字符， escape ‘ \ ‘ 表示 \ 后的符号不是通配符。

连接查询

　　外连接：

　　1）左外连接

　　　　select xxx

　　　　from 表a left outer join 表b on a.xx=b.xx

　　2）右外连接

　　　　select xxx

　　　　from 表a right outer join 表b on a.xx=b.xx

　3）全外连接

　　　　select xxx

　　　　from 表a full outer join 表b on a.xx=b.xx

嵌套子查询

使用 in 的子查询

使用比较运算符的子查询

使用存在量词 exists 的子查询

　　SQL 查询提供量词运算。量词有两种：一是存在量词，二是全称量词。全程量词可以用存在量词替代，故 SQL 语句仅提供存在量词的运算，使用谓词 exists 表示，全称量词转化通过 not exists 谓词来实现。

聚合查询

聚合函数

　如果指定 distinct 谓词，表示在计算时首先消除<列名>取重复值的元组，然后再进行统计。

分组聚合

　　在 SQL 查询中，往往需要对数据进行分组运算，分组运算的目的是为了细化聚合函数的作用对象。如果不对查询结果进行分组，则聚合函数作用于整个查询结果；如果对查询结果进行分组，则聚合函数分别作用于每个组，查询结果是按组聚合输出。SQL 语句中通过 group by 和having 子句来实现分组运算，其中：

　　group by 子句对查询结果按某一列或某几列进行分组，值相等的分为一组；

　　having 子句对分组的结果进行选择，仅输出满足条件的组。该子句必须与group by 子句配合使用。

　聚合函数可以直接使用在 having 子句中，也可以用于子查询中，但在 where 子句中不可以直接使用聚合函数。

复杂查询

或者

　　select studentNo, avg(score) as avgScore

　　from score

　　group by studentNo

　　having count(*) >5

　　order by avgScore

　　limit 0,1

集合运算

　　SQL 支持集合运算。select 语句查询的结果是集合，多个 select 语句的结果可以进行集合操作，传统的集合操作主要包括并 union、交 intersect、差 except 运算，在执行集合运算时要求参与运算的查询结果的列数一样，其对应列的数据类型必须一致。

SQL 查询一般格式

SQL 数据定义语言

　　数据库中的关系集合必须由数据定义语言 DDL 来定义，包括：数据库模式、关系模式、每个属性的值域、完整性约束、每个关系的索引集合和关系的物理存储结构等。

数据库的定义

数据库的创建

数据库的删除

基本表的定义

创建基本表

基本表的修改

　　 基本表在修改过程中，不可以删除列，一次仅执行一种操作。

基本表的删除

　　

　若选择 restrict ，则该表的删除有限制条件，即该表不能有视图，触发器以及被他表所引用，该项为默认项。

　　若选择 cascade， 则该表的删除没有限制条件，在删除基本表的同时，也删除建立在该表上的所有索引，完整性规则，触发器和视图。

索引的定义

　　如果数据有序，则检索速度是非常快的，对表中的记录进行排序有两种方案：一是对记录进行物理上的重新组织。二是不改变物理顺序，通过建立索引来实现数据记录的重新排列，称为逻辑排序。

　　一张表可以建立多个索引，可以从不同的角度加快查询速度，如果索引建立的比较多，会给数据维护带来较大的系统开销。

　　索引通常是由指针构成的记录，指针逻辑上按照索引关键字进行排序，但不改变表中记录的物理顺序。索引和基本表分别存储。

　　如果索引文件中的记录按照某个搜索码值指定的顺序物理存储，那么该搜索码对应的索引就称为主索引，也叫聚集索引。搜索码值顺序与索引文件中记录的物理顺序不同的那些索引称为辅助索引或非聚集索引。

索引的建立

索引的删除

　　索引一旦建立，用户就不需要管理它，由系统自动维护。如果某个关系经常要执行插入、删除和修改操作，系统会花费很多时间来维护索引，从而降低基本表的更新速度，因此可删除那些不经常使用的索引。删除索引的语法为：

SQL 数据更新语言

　　SQL 数据更新语句包括 3 条：插入 insert、删除 delete、修改 update。

　　插入数据

插入一条元组

插入多条元组

　删除数据

　　

　　修改数据

视图

　　视图是虚表，是从一个或几个基本表（或视图）中导出的表，在系统的数据字典中仅存放了视图的定义，不存放视图对应的数据。

定义视图

　with check option ：当对视图进行插入、删除和更新操作时必须满足视图定义的谓词条件。

查询视图

　　查询是对视图进行的最主要的操作。从用户的角度来看，查询视图与查询基本表的方式是完全一样的。

更新视图

　　更新视图指通过视图来插入、删除和修改基本表中的数据。由于视图是一个虚表，不实际存放数据，对视图的更新最终要转换为对基本表的更新，因此，如果视图的定义中包含了表达式，或聚合运算，或消除重复值运算，则不能对视图进行更新操作。

删除视图

习题

　1）查询在2005-2008 年之间没有归还图书的读者编号、读者姓名、读者工作单位、图书编号、图书名称和借书日期。

　　分析：将读者、借阅、书籍三个关系连接起来然后判断书籍是否应该在 2005~2008年之间归还且书籍归还日期为空。

　　select rd.readerNo, rd.readerName, rd.workUnit, bk.bookNo, bk.bookName, br.borrowDate

　　from Reader rd, Borrow br, Book bk

　　where rd.readerNo=br.readerNo and br.bookNo=bk.bookNo and year(shouldDate) between 2005 and 2008 and br.returnDate is null

　　2)查询没有借书的读者姓名（分别用 in 子查询和存在量词子查询表达）。

　　分析：首先在借阅表中找出所有借过书还没有归还的读者号，然后判断每一个读者的读者号是否在前面找出的读者号里面，不在则代表没有借书。

　　select readerName

　　from Reader

　　where readerNo not in(

　　select distinct readerNo

　　from Borrow

　　where returnDate is null );

　　select readerName

　　from Reader

　　where not exists (

　　select *

　　from Borrow

　　where Reader.readerNo=Borrow.readerNo and returnDate is null );

　　3)查询既借阅了“离散数学”又借阅了“数据库系统概念”两本书的读者编号、读者姓名、借书日期和图书名称。

　　分析：先找出借阅了离散数学的读者编号，再去看该读者是否借阅了数据库系统概念。

　　select rd.readerNo, rd.readerName, br.borrowDate, bk.bookName

　　from Reader rd, Borrow br, Book bk

　　where rd.readerNo=br.readerNo and br.bookNo=bk.bookNo and bookName=’离散数学’

　　and exists (

　　select *

　　from Borrow, Book

　　where Borrow.bookNo=Book.bookNo and rd.readerNo=Borrow.readerNo and Book.bookName=’数据库系统概念’ );

　　4）查询没有借阅’“经济管理”类图书的读者编号、读者姓名和出生日期（分别用 in 子查询和存在量词子查询表达）。

　　分析：先找出借阅了经济管理类书籍的读者号，然后判断每一个读者编号是否在前面找到的读者号中。

　　select readerNo, readerName, substring(identitycard,7,8) as birthday

　　from Reader

　　where readerNo not in (

　　select readerNo

　　from Borrow

　　where bookNo in(

　　select bookNo

　　from Book

　　where classNo in(

　　select classNo

　　from BookClass

　　where className=’经济管理’ )));

　　

　　

　　select readerNo, readerName, reader, substring(identitycard,7,8) as birthday

　　from Reader

　　where not exists(

　　select *

　　from Borrow br, Book bk, BookClass bc

　　where br.bookNo=bk.bookNo and bk.classNo=bc.classNo and bc,className=’经济管理’ and br.readerNo = Reader.readerNo);

　　

　　5)查询至少借阅了“马永强”所借的所有图书的读者编号、读者姓名、和工作单位。

　　分析：马永强所借的书他都借过 即 没有马永强借的书是他没有借的

　　select readerNo, readerName, workUnit

　　from Reader r1 //遍历每一位读者

　　where r1.readerNo not in(

　　select readerNo

　　from Borrow b1 //读者借书

　　where not exists(

　　select *

　　from Reader r2

　　where r2.readerName=’马永强’ and not exists(

　　select *

　　from Borrow b2 //马永强借书

　　where r2.readerNo=b2.readerNo and b1.bookNo=b2.bookNo and b2.returnDate is null)));

关系数据理论

数据冗余导致的问题

　　1）冗余存储：信息被重复存储，导致浪费大量存储空间。

　　2）更新异常：当重复的信息的一个副本被修改，所有副本都必须进行同样的修改。因此当更新数据时，系统要付出很大的代价来维护数据库的完整性，否则会面临数据不一致的风险。

　　3）插入异常：只有当一些信息事先已经存放在数据库中时，另外一些信息才能存入数据库中。

　　4）删除异常：删除某些信息时可能丢失其他信息。

函数依赖定义

函数依赖

　　在关系R中，若属性或者属性集 A 中 两个元组的值相等，如果这两个元祖中对应的属性或者属性集B中的值也相同，则记作A—>B。 A函数决定B； 或者 B函数依赖于A。

平凡与非平凡函数依赖

　　对于任一关系模式，平凡函数依赖都是必然成立的，它不反映新的语义。若不特别声明，总是讨论非平凡函数依赖。

完全函数依赖和部分函数依赖

　　完全函数依赖：（学号,课号）——>成绩; 单独一个学号，不能决定成绩，单独一个课程，也不能决定成绩；只有二者同时，才能决定；

　　部分函数依赖：（学号,课号）——>姓名；学号和课号能决定姓名, 单独一个 学号 也能决定 姓名；

传递函数依赖

　　学号—>系号，系号—>系主任; 系主任 传递依赖于 学号

函数依赖理论

码、超码、候选码和主码

　　码是一个或多个属性的集合。

　　超码是一个或多个属性的集合，超码中的这些属性可以让我们在一个实体集中唯一地标识一个实体。

　　候选码是极小的超码集，也就是它的任意真子集都不是超码，而他本身是超码。

　　主码是被选中用来在一个关系中区分不同元组的候选码。

　　候选码的确定：

　　设关系模式R中U=ABC…….等N个属性，U中的属性在FD中有四种范围：

　　(1)左右出现;
　　(2)只在左部出现;
　　(3)只在右部出现;
　　(4)不在左右出现;

　　算法：按以下步骤求候选键：
　　1.只在FD右部出现的属性，不属于候选码;
　　2.只在FD左部出现的属性，一定存在于某候选码当中;
　　3.外部属性一定存在于任何候选码当中; (左右都不出现)

　　4.其他属性逐个与2,3的属性组合，求属性闭包，直至X的闭包等于U,若等于U,则X为候选码。

　　例1：R<U,F>,U=(A,B,C,D,E,G),F={AB–>C,CD–>E,E–>A.A–>G},求候选码以及主属性。

　　因为：G只在右边出现，所以候选码肯定不包含G，BD只出现在左边，所以，候选码中肯定有BD，而BD的闭包还是BD，则对BD进行组合,除了G以外,BD可以跟A,C,E进行组合。

　　先看ABD
　　ABD本身自包ABD,而AB–>C,CD–>E,A–>G,所以ABD的闭包为ABDCEG=U
　 　再看BDC
　　 CD–>E,E–>A,A–>G,BDC本身自包,所以BDC的闭包为BDCEAG=U
　　 最后看BDE
　　 E–>A,A–>G,AB–>C,BDE本身自包,所以BDE的闭包为BDEAGC=U

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

　　候选码：ABD，BCD，BDE；

　　主属性（主要的属性，能决定其他属性的)：ABCDE;

　　非主属性：G； 　　

Armstrong 公理系统

　　设关系模式R<U,F>，其中U为属性集，F是U上的一组函数依赖，那么有如下推理规则

　　① A1自反律：若Y⊆X⊆U，则X→Y为F所蕴含； 即：ABC→AB； AB——>A (平凡依赖函数)；
　　② A2增广律：若X→Y为F所蕴含，且Z⊆U，则XZ→YZ为F所蕴含；
　　③ A3传递律：若X→Y，Y→Z为F所蕴含，则X→Z为F所蕴含。
　　根据上面三条推理规则，又可推出下面三条推理规则：
　　④ 合并规则：若X→Y，X→Z，则X→YZ为F所蕴含；
　　⑤ 伪传递规则：若X→Y，WY→Z，则XW→Z为F所蕴含； 即：A→B，AC→BC；BC→D ；得出AC→D；
　　⑥ 分解规则：若X→Y，Z⊆Y，则X→Z为F所蕴含。 即：A→BC; 能得出： A→B，A→C；

属性集闭包

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

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

　　已知关系R(A1，A2，A3，A4，A5，A6)，函数依赖集F为{ (A2，A3)——>A4，A3——>A6，(A2，A5)——>A1 }， 问(A2，A3)关于F的属性闭包为：{A2，A3,A4,A6}； 因为：A2，A3能带到A4，A3能得到A6；

　　已知关系R(A，B，C，D，E，F，G)，函数依赖集F为{ A ——>B，B——>D，AD——>EF，AG——>C}， 问：A关于F的属性闭包为:{A,B,D,E,F}； 因为：A能得到B，B能得到D，AD能得到EF;

最小函数依赖集（正则覆盖）

定义

　　如果函数依赖集F满足以下条件，则称F为一个极小函数依赖集。也称为最小依赖集或最小覆盖。

　　(1)F中任一函数依赖的右部仅含有一个属性。

　　(2)F中不存在这样的函数依赖X→A，使得F与F-{X→A}等价。

　　(3)F中不存在这样的函数依赖X→A，X有真子集Z使得F-{X→A}U{Z→A}与F等价。

最小依赖集通用算法

　　① 用分解的法则，使F中的任何一个函数依赖的右部仅含有一个属性；

　　② 去掉多余的函数依赖：从第一个函数依赖X→Y开始将其从F中去掉，然后在剩下的函数依赖中求X的闭包X+，看X+是否包含Y，若是，则去掉X→Y；否则不能去掉，依次做下去。直到找不到冗余的函数依赖；

　　③ 去掉各依赖左部多余的属性。一个一个地检查函数依赖左部非单个属性的依赖。例如XY→A，若要判 X 为多余的，则以X→A代替XY→A，若 A 属于(Y)+，则 X 是多余属性（A 不通过 XY→A，通过 Y 就可以得到 A ，证明 X 是冗余的。），若 X 为多余的则用 Y→A 替代 XY→A。

最小依赖集案例

　　例1：关系模式R(U，F)中，U=ABCDEG，F={B->D，DG->C,BD->E,AG->B,ADG->BC}；求F的最小函数依赖集

　　步骤：

　　（1）用分解的法则，使F中的任何一个函数依赖的右部仅含有一个属性；得到：F={B->D，DG->C,BD->E,AG->B,ADG->B，ADG->C}；

　　（2）去掉多余的函数依赖：从第一个函数依赖X→Y开始将其从F中去掉，然后在剩下的函数依赖中求X的闭包X+，依次做下去。直到找不到冗余的函数依赖；

　　　　① 去掉B->D，此时F={DG->C,BD->E,AG->B,ADG->B，ADG->C}，此条件下得出B的闭包 B+ = B；B+不包含D，所以B->D保留。

　　　　②去掉DG->C，此时F={B->D,BD->E,AG->B,ADG->B，ADG->C}，此时DG闭包DG+ = DG，不包含C,所以不能去掉DG->C.

　　　　③ 去掉BD->E，此时F={B->D，DG->C,AG->B,ADG->B，ADG->C}，此时闭包BD+ = BD,不包含E，所以不能去掉BD->E，继续保留。

　　　　④去掉AG->B，此时F={B->D，DG->C,BD->E,ADG->B，ADG->C}；此时AG+ = AG，不包含B，所以不能去掉AG->B，继续保留。

　　　　⑤去掉ADG->B，此时F={B->D，DG->C,BD->E,AG->B,ADG->C}，此时ADG+ = ADGCBE,包含了B，所以删除ADG->B，不保留。

　　　　⑥去掉ADG->C，此时F={B->D，DG->C,BD->E,AG->B}，此时ADG+ = ADGCBD,包含了C，所以删除ADG->C，不保留。

　　　　综上所得，此时得到F={B->D，DG->C,BD->E,AG->B}；

　　（3）去掉各依赖左部多余的属性。一个一个地检查函数依赖左部非单个属性的依赖。

　　此时函数依赖左边非单个属性有：DG->C,BD->E,AG->B；所以做如下操作：

　　　　①先来看DG->C，判断 D 是否多余，用 D->C 代替 DG->C 并求 DG - D = G 的闭包，此时G的闭包G+ = G，不包含C，保留D。判断 G 是否多余，求 DG - G = D 的闭包，此时D+ = D，不包含C，所以G也不能去掉；

　　　　②再来看BD->E，判断 B 是否多余，求 BD - B = D 的闭包，此时D的闭包D+ = D，不含E，保留B。判断 D 是否多余，求 BD - D = B 的闭包，此时B+ = BDE，包含了E，所以去掉D。

　　　　③最后再来看 AG->B，判断 A 是否多余，求 AG - A = G 的闭包，G+ = G，不包含B，不能去掉A。判断 G 是否多余，求 AG - G = A 的闭包，A的闭包A+ =A，不含B，不能去掉G，还是AG->B ；

　　所以最后得出：F的最小函数依赖集是：F={B->D，DG->C,B->E,AG->B}；

无损连接分解判定

判断表法

无损连接定理

　　

　　案例（1）：关系模式R（SAIP），F={S—>A,SI—>P}; ρ={R1(SA),R2(SIP)}检测分解是否为无损连接？

　　因为：R1∩R2 = S ；R1—R2 = A； R2—R1 = IP；所以得出：S —>A；或者S —>IP； 而 S —>A 在F={S—>A,SI—>P}中，所以此分解是无损连接。

　　举例（2）：已知R<U,F>，U={A,B,C}，F={A→B}，如下的两个分解：
　　① ρ1={AB,BC}；

　　② ρ2={AB,AC}；

　　因为：AB∩BC = B；AB—BC = A；BC—AB = C；得出；B→A，或者 B→A，两个都不包含在F={A→B}中，所以 ρ1 分解是有损的。

　　因为：AB∩AC = A；AB—AC = B；AC—AB = C；得出：A→B，或者A→C，而A→B包含在F={A→B}中，所以 ρ2 分解是无损的。

保持依赖分解判定

　　

​ 案例（1）：关系模式R<U, F>，U=｛A, B, C, D, E｝，F＝{B→A，D→A，A→E，AC→B｝则分解ρ={R1（ABCE），R2（CD）｝是否满足保持函数依赖。

　　因为：B→A，A→E，AC→B在R1上成立，D→A在R1和R2上都不成立，因此需做进一步判断。

　　由于B→A，A→E，AC→B都是被保持的（因为它们的元素都在R1中），因此我们要进一步判断的是D→A是不是也被保持。

　　①先看R1：因为：result = D；result ∩R1 = ф (空集)；所以：t=ф，result=D；

　　②再看R2：因为：result = D；result ∩R2 = D；D+ = DA; D+ ∩ R2 = D; 所以：t=D，result=D；

　　一个循环后result未发生变化，因此最后result=D，并未包含A，所以D→A未被保持，该分解不是保持依赖的。

　　案例（2）：关系R<U,F>，U={A,B,C,D,E}，F={A→C,B→C,C→D,DE→C,CE→A}，R的一个分解为R1(AD)，R2(AB)，R3(BE)，R4(CDE)，R5(AE)，判断这个分解是否具有函数依赖性。

　　因为：,C→D,DE→C均在R4(CDE)中被保持，而A→C,B→C，CE→A，在R1….R5上都不成立，需要进一步判断。

　　（1）A→C；

　　①先看R1：因为：result = A；result ∩R1 = A ； A+ = ACD ； A+ ∩ R1 = AD；所以：t=AD，result=AD； 此时，result改变，则，进入R2；

　　②再看R2：因为：result = AD；result ∩R2 = ф，最后还是result = AD；

　　③再看R3：因为：result = AD；result ∩R3 = ф，最后还是result = AD；

　　④再看R4：因为：result = AD；result ∩R4 = D，D+ = D; D+ ∩ R4= D；最后还是result = AD；

　　⑤再看R5：因为：result = AD；result ∩R5 = A，最后还是result = AD；

　　最后result = AD 并未包含C；所以，所以D→A未被保持，该分解不是保持依赖的；

范式

　　（1）：1NF：每个分量都是不可再分的数据项（值、原子）。即：属性中，不能存在复合属性 或者 多值属性。

　　（2）：2NF：每一个非主属性 完全函数依赖 于 候选键（码）。注意：这里是码（不是主属性）；即：不能存在 非主属性部分函数依赖于码。

　　（3）：3NF：每一个非主属性 都不传递依赖于 码。 即：不能存在非主属性对于码的传递函数依赖。

　　（4）：BCNF：不存在 主属性 对于 码 的 部分函数依赖 与 传递函数依赖。判断方法：箭头左边的必须是候选码（不能只是一个属性，部分码）。

　　判断范式的方法：

　　

　　例1：R（A，B，C），F={A->B, B->A, A->C}

　　　　L ：No，R：C，LR：A，B

　　　　计算A+ = ABC ，A 是候选码

　　　　计算B+ = ABC，B 是候选码

　　　　主属性： A，B ；非主属性： C

　　　　1）看非主属性是否部分依赖于主属性，发现没有部分依赖。

　　　　2）看非主属性是否传递依赖于主属性，发现 B -> A -> C ，C 传递依赖于 B，但这个传递依赖成立的条件是 A -> B 不成立，否则A -> C 推不出来。故没有部分传递依赖。

　　　　3）看所有依赖左边是否全部为候选码，所有依赖左边依次是 A，B，A 全部为 候选码 故为 BCNF 范式。

　　　　例2：R（A，B，C，D），F={B->D, D->B, AB->C}

　　　　L：A， R：C，LR：B，D

　　　　L 一定为主属性，将 L 和 LR 组合为 AB，AD

　　　　主属性： A，B，D ；非主属性： C

　　　　AB+ = ABCD；AD+ = ABCD；故 AB，AD为候选码。

　　　　1）查看部分依赖。 C 完全依赖于 AB，没有部分依赖。

　　　　2）查看传递依赖。C直接完全依赖于候选码 AB，没有传递依赖。

　　　　3）查看是否全为候选码。所有依赖左边依次是 B，D，AB ，B，D不为 候选码 故为 3NF 范式。

模式分解

3NF 分解

　　1）计算最小函数依赖

　　2）将最小函数依赖依次分解，得到 3NF 保持函数依赖分解。

　　3）将保持依赖分解添加一个候选码到结果中，得到 3NF 无损连接分解。

BCNF 分解

　　R(A，B，C，D)，F={A->B，C->D}一直找不是候选码的函数依赖项 A->B，将依赖集分解为两部分：

　　1）AB

　　2）ACD (B 可由A 推出)

　　继续分解 ACD。

　　

　　例： R（A，B，C，D，E，F），F={AE->F，A->B, BC->D, CD->A, CE->F}

3NF 分解例子

　　1）计算最小函数依赖集，这里省略，可以看到 F 的最小函数依赖集就是它本身。

　　2）计算候选码

　　L：C，E

　　R：F

　　LR：A，B，D

　　L 一定为主属性，将 L 和 LR 组合为 ACE，BCE，CDE。

　　主属性： A，B，C，D ，E；非主属性： F

　　ACE+ = ABCDEF；BCE+ = ABCDEF；CDE+ = ABCDEF；故ACE，BCE，CDE为候选码。

　　3）分解

　　将上面的函数依赖依次分解得到：AEF，AB，BCD，CDA，CEF。

　　得到 3NF 保持函数依赖分解 : AEF，AB，BCD，CDA，CEF

　　任意添加一个候选码进去(这里选 ACE)。

　　得到 3NF 无损连接依赖分解 : AEF，AB，BCD，CDA，CEF，ACE

BCNF 分解例子

　　依次分解左边不是候选码的依赖项

　　AE->F，A->B, BC->D, CD->A, CE->F 左边全部都不是候选码，都需要分解。

　　第一次分解 AE->F：

　　AEF， 剩下 R=（ABCDE），F={A->B, BC->D, CD->A} （F可以被导出，若 F 在依赖的左边需要使用其他依赖代替）

　　第二次分解 A->B：

　　AB，剩下 R=（ACDE）， F={AC->D, CD->A} （B可以被导出，丢失BC-> D）

　　第三次分解 CD->A：

　　CDA，剩下 R=（CDE）， F={} （A可以被导出）

　　CDE为候选码分解停止。

　　故 BCNF 分解为 AEF，AB，CDA，CDE

参考：

　　https://blog.csdn.net/prdslf001001/article/details/80336835

　　https://www.bilibili.com/video/av73467859/

　　https://www.bilibili.com/video/BV1eE411a79r/

E - R模型

数据库设计过程

​ 1）需求分析：了解和分析系统将要提供的功能及未来数据库的用户需求。

​ 2）概念设计：根据需求分析中得到的信息，设计者此阶段须选择适当的数据模型将这些需求转化为数据库的概念模式。例如 E - R 模型 是概念设计。

​ 3）逻辑设计：将概念设计转化为所选择的数据库管理系统支持的逻辑数据模型，即数据库模式。逻辑数据库设计的任务是将 E - R 模型转化为关系数据库模式。

​ 4）模式求精：对已得到的关系数据库模式进行分析找出潜在的问题并加以改进和优化。

​ 5）物理设计：为逻辑数据库选取一个最适合现实应用的物理结构。

​ 6）应用与安全设计：数据库系统必须指出哪些用户可以访问数据库以及他们通过哪些存储过程访问数据库。

E - R 模型基本概念及表示

实体与实体集

​ 实体是客观世界中可区别于其他事物的“事物”或“对象”。

​ 实体的两个特征：独立存在（一个实体的存在不依赖于其他实体）、可区别于其他实体。

​ 实体集是指具有相同类型及相同性质（或属性）的实体集合。

属性

​ 实体是通过一组属性来描述的，属性是实体集中每个实体都具有的描述性性质。在已实体集中，所有实体都具有相同的属性。

​ 每个属性所允许的取值范围或集合称为该属性的域。

​ E - R 模型中的属性可按如下类型划分：

​ 1）简单属性和复合属性。简单属性是指不能再分为更小部分的属性。复合属性指可以进一步划分为更小部分的属性。

​ 2）单值属性和多值属性。如果某属性对一个特定实体任何时候都只能有单独的一个值，则称该属性为单值属性，否则为多值属性。例如一个studentNo 属性只对应一个学号，为单值属性。一个phoneNumber属性可能有不同数目的值，为多值属性。

​ 3）空值（NULL）属性。当某个属性上没有值时可以使用 NULL 值。

​ 4）派生属性，这类属性的值可以从其他属性的值派生出来。例如实体集 Student 的 age 属性表示学生的年龄，它可以由当前日期和生日属性的值计算得到。

​ 在 E - R 图中，实体集用矩形表示，属性用椭圆表示，多值属性用双椭圆表示，派生属性用虚线椭圆表示，属性与实体之间用连线表示。

联系与联系集

​ 联系集是 n （n >= 2）个实体集上的数学关系，这些实体集不必互异。

​ 参与联系的实体集的数目称为联系集的度。

​ 下图中，Student 与 Course 之间有 Enroll 联系集，选课联系集上有 Score 属性。课程里面的 PriorCourse 属性参照 Course 关系。

约束

映射约束

​ 映射基数指一实体集中的一个实体通过一个联系集能同时与另一个实体集相联系的实体数目。在二元联系中，共有 4 种映射基数：1：1（一对一）、1：m（一对多）、m：1（多对一）、m：n（多对多）。

​ 在 E - R 图中，“—>”指向参与联系集中“一”方实体集，线段“—”表示参与联系集中的“多”方实体集。

码约束

实体集的码

​ 码是一个或多个属性的集合。

​ 超码是一个或多个属性的集合，超码中的这些属性可以让我们在一个实体集中唯一地标识一个实体。

​ 候选码是极小的超码集，也就是它的任意真子集都不是超码，而他本身是超码。

​ 主码是被选中用来在一个关系中区分不同元组的候选码。

联系集的码

​ 二元联系集的主码选择依赖于联系集的映射基数，具体如下。

​ 1）一对一：主码可以使用参与联系集中的任何一方实体集的主码；

​ 2）一对多和多对一：主码由“多的一方实体集的主码组成；

​ 3）多对多：主码由参与联系集中所有实体集的主码组成。

参与约束

​ 如果实体集 A 中的每个实体都参与到联系集 R 中至少一个联系中，则称实体集 A 全部参与联系集 R。

存在依赖与弱实体集

​ 存在一类实体集，其属性不足以形成主码，它们必须依赖于其他实体集的存在而存在，称这样的实体集为弱实体集。与此相对，其属性可以形成主码的实体集称为强实体集。弱实体集所依赖的强实体集称为标识实体集。弱实体集必须与一个标识实体集相关联才有意义，该实体集称为标识实体集。

​ 对于弱实体集，必须满足下列限制：

​ 1）标识实体集和弱实体集必须是一对多联系集。

​ 2）弱实体集在标识联系集中是全部参与。

​ E - R 图使用双矩形表示弱实体集，双菱形表示标识联系，用虚下划线表示弱实体集的部分码。下图描述了 CourseClass 及其标识实体集 Course 之间的标识联系集 Arrange 。注意标识联系集没有描述性属性，因为任何所需的属性都可和弱实体相关联。

E - R 模型转化为关系模型

E - R 模型转化方法

​ 1）强实体集转化方法：将实体集的每个属性对应为关系模式的属性，实体集的码作为关系模式的码。

​ 2）弱实体集转化方法：弱实体集对应的关系模式属性由弱实体集本身的描述属性加上所依赖的强实体集的主码属性组成。主码由所依赖的强实体集主码和弱实体集的部分码组成。

​ 3）联系集转化方法

​ 联系集一般转化方法：一个联系集转化为一个关系模式。联系集的主码设置见“联系集的主码“。

​ 一对多或多对一联系集的转化：在 ”多“ 方的实体集中添加 ”一“ 方的主码，使 ”一“ 方的主码成为 ”多“ 方 的外码。

​ 4）复合属性及多值属性转化方法：对于复合属性，应为每个子属性创建一个单独的属性，而不是为复合属性自身创建的一个单独的属性。

习题

​

数据库完整性与安全性

数据库安全性

SQL 存取控制机制

​ SQL 支持受控的存取保护，即在自主存取控制中，用户对于不同的数据对象有不同的存取权限，不同的用户对同一对象也有不同的权限，而且用户还可将其拥有的存取权限转授给其他用户。因此自主存取控制非常灵活。

​ 自主存取控制通过 SQL 的 grant 和 revoke 语句实现。

​ 用户权限是由两个要素组成的：数据对象和操作对象。

​ 用户的存取权限：该用户可以在哪些数据对象上进行哪些类型的操作。定义存取权限称为授权。

​ 自主存取控制能够通过授权机制有效地控制其他用户对敏感数据的存取。

创建用户

​ 创建用户语句 create user 的语法如下：

​ 只有系统的超级用户才有权创建一个新的数据库用户。新创建的数据库用户有 3 种权限 connect、resource 和 dba。默认为 connect 权限，拥有 connect 权限的用户不能创建新用户、模式和基本表，只能登录数据库。然后由 dba 或其他用户给他转授权限。拥有 resource 权限的用户可以创建基本表和视图，并称为所创建对象的属主，但不能创建模式和新用户。数据库对象的属主可以使用 grant 语句把该对象上的存取权限授予其他用户。拥有 dba 权限的用户是系统中的超级用户，可以创建新用户、模式、基本表和视图等；dba 拥有所有数据库对象的存取权限，还可以将这些权限授予给一般用户。

权限的授予与收回

​ grant 和 revoke 有两种权限：目标权限和命令权限。

命令权限的授予与收回

​ 命令级权限主要指 DDL 操作权限。命令权限的授予语句 grant 和 收回语句 revoke 的语法分别为：

​ 其中 < command_list > 可以是 create database、create default、create function、create procedure、create rule、create table、create view、create index、backup database 和 backup log 等。

​ 一次可以授予多种权限，授予多种权限时，权限之间用逗号分隔。

​ all：表示上述所有权限。

​ public：表示所有用户。

​ < username_list >：指定的用户名列表。如果将某组权限同时授予多个用户，则用户名之间用逗号分隔。

目标权限的授予与收回

​ 目标权限主要指对对象的 DML 操作权限。对象权限的授予语句 grant 和收回语句 revoke 的语法分别为：

​ 其中 < command_list > 可以是 update、select、insert、delete、execute 和 all 。execute 针对存储过程授予执行权限，update、select、insert、delete 针对基本表和视图授权， all 指全部的权限。

​ cascade ：级联收回。

​ restrict：默认值，若转赋了权限，则不能收回。

​ with grant option：将指定对象上的目标权限授予其他安全账户的能力，但是不允许循环授权。即不允许将其得到的权限授予其祖先。

数据库完整性

完整性约束条件

​ 完整性约束条件作用的对象可以是关系、元组、列 3 种。列约束主要是列的类型、取值范围、精度、是否允许空值等的约束条件。元组约束是元组中属性间的联系的约束。关系约束是若干元组间、关系集合上以及关系之间的约束。

​ 完整性约束条件涉及的这 3 类对象，其状态可以是静态的，也可以是动态的。

​ 静态约束是指数据库每一确定状态时的数据对象所应满足的约束条件，它反映数据库状态合理性的约束，这是最重要的一类完整性约束。

​ 静态约束主要表现在：

​ 1）静态列级约束：对一个列的取值域的说明。对数据类型（类型，长度、单位、精度等）、数据格式、对取值范围或取值集合的约束、对空值的约束和其他约束。

​ 2）静态元组约束：规定元组的各个列之间的约束关系。

​ 3）静态关系约束：在一个关系的各个元组之间或若干关系之间存在各种联系或约束。常见的静态关系约束有：实体完整性约束、参照完整性约束和函数依赖约束。

​

​ 动态约束是指数据库从一种状态转变为另一种状态时的新、旧值之间所应满足的约束条件，它是反映数据库状态变迁的约束。

​ 动态约束主要表现在：

​ 1）动态列级约束。修改列定义或列值时应满足的约束条件。包括修改列定义时的约束（将允许空值的列修改为不允许空值，记录中有一列为空值，拒绝修改）修改列值时的约束（修改列值有时需要参照其旧值）。

​ 2）动态元组约束：指需改元组的值时元组中各个字段间需要满足某种约束条件。

​ 3）动态关系约束：动态关系约束是加在关系变化前后状态上的限制条件。例如，事物一致性、原子性等约束条件。

​

​ 完整性约束又可以分为立即执行的约束和延迟执行的约束。

​ 立即执行约束：检查是否违背完整性约束的时机是在一条语句执行完后立即检查。

​ 延迟执行约束：需要延迟到整个事务执行结束后再进行检查。

​

实体完整性

​ 实体完整性要求基本表的主码值唯一且不允许为空值。primary key 指定

​ 实体完整性的检查和违约处理：

​ 1）检查主码是否唯一。如果不唯一则拒绝插入或修改。（索引或顺序查找）

​ 2）检查主码的各个属性是否为空，只要有一个为空则拒绝插入或修改。

参照完整性

​ 参照完整性为若干个表中的相应元组建立联系。参照完整性定义是使用 create table 语句中的 foreign key 和 references 短语来实现，或通过 alter table 语句中的 add foreign key 来实现。

​ 参照完整性的检查和违约处理：

​ 1）拒绝执行。如果发生了违约，阻止操作。

​ 2）级联操作。当删除或修改被参照关系的某个元组造成了与参照关系的不一致时，则删除或修改参照表中所有不一致的元组。级联操作必须在定义外码时给出定义（在外码定义最后追加 on delete/update cascade）。

​ 3）设置为空值。如果外码可以为空，发生了违约则将外码置空。

​ 4）置空值删除。删除被参照关系的元组，并将被参照关系中相应元组的外码置空值。

用户自定义完整性

属性上的约束

​ 包括：列值非空、列值唯一、设置默认值和满足 check 定义。如果不满足则拒绝相应的操作。

​ 以上约束分别通过 not null、unique、default+默认值、check 实现。

元组上的约束

​ 元组上的约束可以设置不同属性之间的取值相互约束条件，也是用 check 实现。插入元组或修改属性的值时，RDBMS 检查元组上的约束条件是否满足，否则拒绝操作。

​ 第一个 check 为属性上的约束，放在属性定义后，第二个 check 为元组上的约束。

完整性约束的修改

​ 要修改约束必须先删除约束，然后加入新的约束。

游标

​ 若要对 select 语句返回的结果值进行逐行处理，必须使用游标。可对游标的当前位置进行更新、查询和删除，使用游标必须经历 5 个步骤：

​ 1）定义游标：declare；

​ 2）打开游标：open；

​ 3）逐行提取游标集中的行：fetch；

​ 4）关闭游标：close；

​ 5）释放游标：deallocate；

游标的使用

定义游标

​ read only 表示当前游标集中的元组仅可以查询，不可以修改。update表示可以对当前游标集中的元组进行更新操作，如果有 of < columnName_list >，表示仅可以对游标集中指定的属性列进行更新操作。

打开游标

​ 系统按照游标的定义从数据库中将数据检索出来，放在内存的游标集中，并为游标集指定一个游标，该游标指向游标集中的第一个元组。

​ 打开游标的语法：open < cursorName >

获取当前游标值

​ 要对当前游标所指向的元组进行操作，必须获取当前游标所指向的元组，其语法是

​ fetch < cursorName > into < @variableName_list >

​ 获取当前游标的值，必须将当前游标所指向的元组的各个属性值分别用变量接收，其变量个数、数据类型必须与定义游标中的 select 子句所定义的属性（或表达式）个数数据类型相一致。

​ SQL Server 中，变量名前面必须使用 @ 符号，使用一个 @ 符号位局部变量，使用两个 @ 为全局变量。

​ 执行一次该语句，系统将当前游标所指向的元组属性放到变量中，然后游标自动下移一个元组。当游标移至尾部，则不可以再读取游标，必须关闭游标再重新打开游标。可以通过检查全局变量 @@FETCH_STATUS 来判断是否已经读完游标集中所有行。

​ @@FETCH_STATUS 的值有：

​ 0 ：fetch 语句成功，表示已经从游标集中获取了元组值。

​ 1：fetch 语句失败或此行不在结果集中。

​ 2：被提取的行不存在。

关闭游标

​ close < cursorName >

释放游标所占用的存储空间

deallocate < cursorName >

变量赋值

对当前游标集的修改

​ 可以对当前游标集中的元组执行删除和更新操作。

删除游标集中的当前行

​ delete from < tableName > where current of < cursorName >

更新游标集中的当前行

​ update < tableName >

​ set < columnName >=< expr >[,< columnName >=< expr >…]

​ where current of < cursorName >

存储过程

​ 存储过程是为了完成特定功能汇集而成的一组命名了的 SQL 语句集合，该集合编译后存放在数据库中，可按实际情况重新编译。

​ 使用存储过程的优点：将业务操作封装、便于事务管理、实现一定程度的安全性保护、特别适合统计和查询操作、减少网络通信量。

创建存储过程

​ output：输出参数，被调用者获取使用。

执行存储过程

​ 存储过程创建后存放在数据库中，当要使用存储过程时，必须执行命令 execute。

修改和删除存储过程

修改存储过程

删除存储过程

​ drop procedure < procedureName >

触发器

​ 触发器是用户定义在关系表上的一类由事件驱动的存储过程，由服务器自动激活。触发器可以进行更为复杂的检查和操作，具有更精细和强大的数据控制能力。

​ 有两个特殊的表用在触发器语句中，不同的数据库其名称不一样。以SQL Server 为例介绍触发器。

​ 1）deleted 表。存储 delete 和 update 语句执行时所影响的行的拷贝，在 delete 和 update 语句执行前被作用的行转移到 deleted 表中，即将被删除的元组或修改前的元组值存入该表中。

​ 2）inserted 表。存储 insert 和 update 语句执行时所映像的行的拷贝，在 insert 和 update 语句执行期间，新行被同时加到 inserted 表和触发器中，即将被插入的元组或修改后的元组存入该表中，同时也更新基本表。

创建触发器

​ <insert|update|delete> ：触发器事件。

修改和删除触发器

​ 修改触发器：

​ 删除触发器：

​ drop trigger < triggerName >

触发器的作用

​ 触发器常用于保证完整性，并在一定程度上实现安全性，如用触发器来进行审计。

事务管理与恢复

事务

事务概念

​ 对于用户而言，事务是具有完整逻辑意义的数据库操作序列的集合。对于数据库管理系统而言，事务则是一个读写操作序列。这些操作是一个不可分割的逻辑工作单元，要么都做，要么都不做。

​ 通常有有两种类型的事务结束语句：

​ 1）事务提交（commit）：将成功完成事务的执行结果（即更新）永久化，并释放事务占有的全部资源。

​ 2）事务回滚（rollback）：中止当前事务、撤销其对数据库所做的更新，并释放事务占有的全部资源。

​

SQL Server 数据库提供了 3 种类型的事务模式：显式事务、隐式事务及自定义事务。

​ 显式事务是指用户使用了 Transact-SQL 事务语句所定义的事务，其事务语句包括：

​ 事务开始：begin transaction

​ 事务提交：commit transaction，commit work

​ 事务回滚：rollback transaction，rollback work

​ 隐式事务是指事务提交或回滚后，SQL Server 自动开始新的事务。该类事务不需要采用 begin transaction 语句标识事务的开始。

​ 自动定义事务模式：当一个语句成功执行后，它被自动提交，而当执行过程中出错时，则被自动回滚。

事务特性

​ 1）原子性（Atomicity）。事务的所有操作要么全部被执行，要么都不执行。

​ 2）一致性（Consistency）。一个单独执行的事务应保证其执行结果的一致性，即总是将数据库从一个一致性状态转化到另一个一致性状态。

​ 3）隔离性（Isolation）。当多个事务并发执行时，一个事务的执行不能影响另一个事务，即并发执行的各个事务不能相互干扰。

​ 4）持久性（Durability）。一个事务提交成功后，它对数据库的改变必须是永久的，即使随后系统出现故障。

事务并发执行与调度

​ 数据库管理系统允许多个事务并发执行，其主要优点是增加系统吞吐量和减少平均响应时间。

​

​ 并发事务带来的问题：

​ 1）脏读（Dirty Read）：一个事务正在访问数据并对数据进行修改，修改还没有提交到数据库，这是另外一个事务访问了这个数据，然后使用了这个数据。这个数据更改之前的数据，另一个事务读到的数据是“脏数据”，依靠“脏数据”所做的操作是不正确的。

　2）丢失修改（Lost to modify）：一个事务读取一个数据时，另外一个事务也访问了该数据，在第一个事务中修改数据后，第二个事务也修改了这个数据。第一个事务内的修改结果丢失，因此称作丢失修改。例如事务1读取某表中的数据 A=20 ，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改丢失。

　3）不可重复读（Unrepeatable read）：在一个事务内多次读同一个数据在这个事务还没有结束时，另一个事务也访问该数据。在第一个事务的两次读之间，另一个事务可能已经修改了数据，导致两次读取的数据可能不太一样。

　4）幻读（Phanatom read）：幻读与不可重复读类似。发生在一个事务读了几行数据，接着另一个并发 事务插入了一些数据。在随后的查询中第一个事务就会发现多了一些原本不存在的记录，好像发生了幻觉。

事务调度及正确性准则

​ 事务并发执行顺序是随机的，将由多个事务操作组成的随机执行序列称为一个调度。对于一组事务操作组成的调度序列而言，应满足下列条件：

​ 1）该调度包括该组事务的全部操作；

​ 2）属于同一个事务的操作应保持在原事务中的执行顺序。

​ 串行调度：在调度 S 中，如果属于同一事务的操作都是相邻的，则称 S 是串行调度。

​ 冲突操作：在一个调度 S 中，如果 A 和 B 是不同事务在相同数据对象上的操作，并且其中至少有一个是写操作，则称 A 与 B 是冲突操作。

​ 冲突等价：如果一调度 S 可以经过交换一系列非冲突操作执行的顺序而得到一个新的调度 S‘ ，则称 S 与 S’ 是冲突等价的。

​ 冲突可串行化：如果一调度 S 与一串行调度是冲突等价 的，则称 S 是冲突可串行化的。

判断调度是否可串行化的方法

​ 图 10-2(a) 中，对于 A 的并发访问：R1(A), W1(A), R2(A), W2(A)，存在 W1(A)后执行R2(A)，W1(A)后执行W2(A)。故 T1 -> T2。优先图中无环可以串行化。

​ 图 10-2(b) 中，对于 A 的并发访问：R2(A), W2(A), R1(A), W1(A) ，存在 W2(A)后执行R1(A)，W2(A)后执行W1A)。故 T2 -> T1。优先图中无环可以串行化。

​ 图10-8 中，对于 A 的并发访问：R4(A), W4(A), R6(A), W6(A)，存在 W4(A)后执行R6(A)，W4(A)后执行W6(A)。故 T4-> T6。对于 B 的并发访问：R6(B), W6(B), R4(B), W4(B)，存在 W6(B)后执行R4(B)，W6(B)后执行W4(B)。故 T6-> T4。优先图中有环，不可串行化。

并发控制

基于封锁的协议

​ 并发控制机制大体上可分为悲观的和乐观的两种。悲观的并发控制方法认为数据库 的一致性经常会收到破坏，因此在事务访问数据对象前采取一定措施加以控制，只有得到访问许可时，才能访问数据对象，如基于封锁的并发控制方法。而乐观的并发控制方法则认为数据库的一致性通常不会得到破坏，故事务执行时可直接访问数据对象，只在事务结束时才验证数据库的一致性是否会遭到破坏，如基于有效性验证方法。

​ 基于封锁的并发控制方法的基本思想是：当事务 T 需访问数据对象 Q 时，先申请对 Q 的锁。如批准获得，则 T 继续执行，且此后不允许其他任何事物修改 Q，直到事务 T 释放 Q 上的锁为止。

​ 基本锁类型：

​ 1）共享锁（Shared Lock，记为 S ）：如果事务 T 获得的对象 Q 上的共享锁，则 T 可读 Q 但不能写 Q 。

​ 2）排他锁（eXclusive lock，记为 X ）：如果事务 T 获得的对象 Q 上的排他锁，则 T 可读 Q 又能写 Q 。

​ 一个数据对象 Q 上可能有多个（被不同事务拥有的）共享锁，但任何时候只能有一个排他锁。

​ 图10-13 中的调度存在以下问题：

​ 1）脏读。T2 步骤 11 读了 T1 修改后的数据，而T1 在步骤 12 回滚了。

​ 2）不可重复读。如 T3 两次读到 A 的值不同。

​ 3）不可串行化。

​ 出现上述问题的原因是事务过早地释放了锁，如果规定事务在结束后才释放其持有地锁则可以保证调度的可串行性。但这会导致系统性能下降。

两阶段封锁协议

​ 两阶段封锁协议要求每个事务分两个阶段提出申请锁和解锁申请。

​ 1）增长阶段：事务可以获得锁，但不能释放锁。

​ 2）缩减阶段：事务可以释放锁，但不能获得新锁。

​ 一开始，事务处于增长阶段，事务根据需要获得锁。一旦该事务释放了锁，它就进入了缩减阶段，不能再发出加锁请求。

​ 两阶段封锁协议能保证冲突可串行化。对于任何事务，调度中该事务获得其最后加锁的时刻（增长阶段结束点）称为事务的封锁点。多个事务可以根据它们的封锁点进行排序，而这个顺序就是并发事务的一个冲突可串行化顺序。

​ 图10-14 采用了两阶段封锁，允许 T4 在获得全部锁后（A 和 B 上的排他锁）提前释放部分锁（步骤 7 释放了 A 上的排他锁），T5得以提前执行，从而提高了 T4 和 T5 的并发度，该调度是可串行化 的。

​ 两阶段封锁保证了并发执行事务的正确性，但仍存在两个主要问题：

​ 1）可能导致死锁，即持有锁的事务出现相互等待都不能继续执行。解除死锁的一个简单方法是超时机制。如果一个事务为某个锁等待的时间过长，可以悲观得认为死锁已经发生，回滚该事务并重启。

​ 2）不能避免读脏数据。

​ 另一个两阶段封锁得变体是强两阶段封锁协议，它要求事务提交之前不得释放任何锁。事务可以按其提交得顺序串行化。

封锁协议总结

在运用 X 锁和 S 锁这两种基本封锁对数据对象加锁时，还要约定一些规则。例如，何时申请 X 锁和 S 锁、封锁时间、何时释放等。这些规则称为封锁协议。

对并发操作的不正确调度可能会带来脏读、丢失修改、不可重复读等不一致性问题。三级封锁协议分别在不同程度上解决了这些问题，为并发操作的正确调度提供一定的保证。

1. 一级封锁协议，事务 T 在修改数据 R 之前必须先对其加 X 锁，直到事务结束才释放。事务结束包括正常结束（COMMIT）和非正常结束（ROLLBACK）。
2. 二级封锁协议，在一级封锁协议基础上增加事务 T 在读取数据 R 之前必须先对其加 S 锁，读完后即可释放 S 锁。
3. 三级封锁协议，在一级封锁协议基础上增加事务 T 在读取数据 R 之前必须先对其加 S 锁，直到事务结束才释放。

总结：三个等级的封锁协议都是事务结束后释放 X 锁，不同的是一级封锁对于读取数据不加 S 锁，二级封锁加 S 锁，但在读取操作结束后就释放，三级封锁加 S 锁，在事务结束后释放。一级封锁保证不会丢失修改，二级封锁保证不会丢失修改和脏读，三级封锁保证不会丢失修改、脏读和可重复读。

活锁与死锁

活锁就是一个事务一直处于“饥饿”状态。死锁即为临界资源的循环占用。

避免出现“饥饿”现象的最简方法为采用 FCFS 策略。

避免死锁的方法有预防死锁和诊断并解除死锁两种。

预防死锁：

1. 一次封锁法，每个事务必须一次将所有使用的数据全部加锁。
2. 顺序封锁法，对数据对象规定一个封锁顺序，所有事务都按这个顺序实施封锁。

诊断和解除死锁

1. 超时法，如果一个事务的等待时间超过了规定的时限，就认为发生了死锁。
2. 等待图法，出现环则发现死锁。

封锁的粒度

封锁对象的大小称为封锁粒度。封锁对象可以是逻辑单元，也可以是物理单元。封锁粒度与系统的并发度和并发控制的开销密切相关，封锁的粒度越大，并发度越小，系统的开销也越小；封锁的粒度越小，并发度较高，系统开销也较大。

多粒度封锁

多粒度树的根结点是整个数据库，表示最大的数据粒度。叶节点表示最小的数据粒度。

多粒度封锁协议允许多粒度树中的每个结点被独立地加锁，对一个结点加锁意味着这个结点所有子节点也被加以同样类型的锁。在多粒度封锁中，一个数据对象可能以两种方式封锁：显式封锁和隐式封锁。显示封锁是应事务的要求直接加到数据对象上的锁；隐式封锁是该数据对象没有被独立加锁，由于其上级结点加锁而使该数据对象加上了锁。

意向锁

意向共享锁（Intent Share Lock，IS 锁）意向排他锁（Intent Exclusive Lock，IX 锁）；共享意向排他锁（Share Intent Exclusive Lock，SIX 锁）。

1. IS 锁，如果对一个数据对象加 IS 锁，表示它的子结点拟加 S 锁。
2. IX 锁，如果对一个数据对象加 IX 锁，表示它的子结点拟加 X 锁。
3. SIX 锁，如果对一个数据对象加 SIX 锁，表示对它加 S 锁，再加 IX 锁。

在具有意向锁的多粒度封锁方法中，任意事务 T 要对一个数据对象加锁，必须先对它的上层结点加意向锁。申请封锁时应该按自上而下的次序进行，释放封锁时则应该按自下而上的次序进行。

图 b 中，所谓锁的强度是指它对其他锁的排斥程度。一个事务在申请封锁时以强锁代替弱锁是安全的，反之则不然。

其他并发控制机制

并发控制的方法除了封锁技术外还有时间戳方法、乐观控制法和多版本并发控制等。

时间戳方法给每一个事务盖上一个时标，即事务开始执行的时间。每个事务具有唯一的时间戳，并按照这个时间戳来解决事务的冲突操作。

乐观控制阀认为书屋执行时很少发生冲突，不对事务进行特殊的管制，而是让它自由执行，事务提交前再进行正确性检查。如果发生冲突则回滚事务。

多版本并发控制是指数据库中通过维护数据对象的多个版本信息来实现高效并发控制的一种策略。

恢复与备份

故障分类及恢复策略

​ 1）事务故障。事务未运行至正常终止点就夭折了。

​ 2）系统故障。突发事件导致系统停止运行。

​ 3）介质故障。硬件损坏。

​ 4）其他故障。有人攻击。

事务访问数据方式

​ 对于一个事务而言，它是通过 3 个地址空间同数据库进行交互：

​ 1）保存数据库元素的磁盘块空间——物理数据库。

​ 2）缓冲区管理器所管理的内存地址空间——数据缓冲区。

​ 3）事务的局部地址空间——事务工作区。

​ 当事务要读取数据库元素时，首先必须将该元素从物理数据库读取到数据缓冲区中，除非它已经在缓冲区中，然后再将缓冲区中的内容读到事务工作区中。

基于日志的故障恢复策略

​ 日志是 DBMS 记录数据库全部更新操作的序列文件。通常一个数据库系统只有一个日志文件，为所有事务共享，其主要特点有：

​ 1）日志文件记录了数据库的全部更新顺序。

​ 2）每条日志都记录在日志的尾部，故日志文件是一个追加文件。

​ 3）DBMS 允许事务的并发执行导致日志文件是“交错的”。

​ 4）属于单个事务的日志顺序与该事务的更新操作顺序是一致的。

​ 5）日志记录通常是先写到日志缓冲区中，然后写到稳定存储器中。

​ 数据库中的日志记录有两种类型：

​ 1）记录数据更新操作的日志记录，包括 update，insert 和 delete 操作。

​ 2）记录事务操作的日志记录，包括start，commit 和 abort 操作。

​ 它们的具体记录格式如下：

​ < Ti, A, V1,V2 > 表示 Ti 对数据元素 A 执行了更新操作，V1为 A 更新前的值，V2表示 A 更新后的值。

​ < Ti, START > 表示事务 Ti 已经开始。此时 DBMS 完成对事务的初始化工作，如分配事务工作区等。

​ < Ti, COMMIT > 表示事务 Ti 已经提交。

​ < Ti, ABORT > 表示事务已经终止，即事务执行失败。

​ 为了保证数据库能运用日志进行恢复，要求日志文件必须放到稳定存储器上，并且要求每条日志记录必须在其所包含数据元素的更新值写到稳定存储器之前写到稳定存储器上，即先写日志规则。

UNDO 操作

​ 事务 T 执行过程中修改了数据库后，可能由于某种原因事务中止或系统崩溃，可使用 UNDO 恢复技术将 T 修改的全部数据对象值恢复到 T 开始前的状态。

​ 对于要 UNDO 的事务 T ，日志中记录有 <T, START> 以及 T 对数据库的所有更新操作的日志记录。UNDO 过程为：从 T 的最后一条更新日志开始，从日志尾向日志头（反向）依次将 T 更新的数据元素恢复为旧值（V1）。

​ 之所以需要 UNDO ，是因为故障发生时未提交事务的修改可能已写到磁盘上。

REDO 操作

​ REDO 操作时对已提交事务进行重做，将数据库状态恢复到事务结束后的状态。

​ 对于要 REDO 的事务 T，日志中已经记录了 <T, START> ，T 的所有更新操作日志以及 <T, COMMIT>。REDO 的过程为：从 T 的第一条更新日志记录来时，从日志头向日志尾（顺向）依次将 T 更新的数据元素值恢复为新值（V2）。

​ 需要 REDO 的原因是，故障发生时可能有些已提交事务的更新数据还未写到磁盘上。

并发执行事务的基本恢复过程

​ 1）分析阶段。从日志头开始顺向扫描日志，以确定重做事务集和撤销事务集。将既有 <T, START>又有 <T, COMMIT> 日志记录的事务 T 加入重做事务集。将只有 <T, START>没有 <T, COMMIT> 日志记录的事务 T 加入撤销事务集。

​ 2）撤销阶段。从日志尾反向扫描日志，对每一条属于撤销事务集中的事务更新操作日志依次执行 UNDO 操作。

​ 3）重做阶段。从日志头顺向扫描日志，对每一条属于重做事务集中的事务更新操作日志依次执行 REDO 操作。

检查点

​ 检查点是周期性地向日志中写一条检查点记录并记录所有当前活跃的事务，为恢复管理器提供信息，以决定从日志的何处开始恢复。在日志记录中使用 < Checkpoint L >来指定检查点 L 。

​ 图10-19 是系统崩溃时的不同事务状态类型，其中 Tc 为完成最近检查点时刻，Tf 为故障发生时刻。

备份与介质故障恢复

​ 动态备份是指备份操作与用户事务的执行并发进行，备份期间允许对数据库进行存取或修改。静态备份则要等待用户事务结束然后备份。

​ 具体进行数据备份时可以有两种方式，一种是全备份，一种是增量备份。

​ 全备份是指每次备份全部数据库，而增量备份只备份上次备份后更新过的数据。