第八章数据库管理.ppt

数据库实用教程（第三版）,第八章 数据库管理,第八章 数据库管理 教学内容： 事务的定义，事务的ACID性质，事务的状态变迁图。, 恢复的定义、基本原则和实现方法,故障的类型,检查点技术。, 并发操作带来的三个问题，X锁、PX协议,活锁、死锁， 并发调度、串行调度、并发调度的可串行化，两段封锁法。, 完整性的定义，完整性子系统的功能，完整性规则的组成； SQL中的三大类完整性约束，SQL3中的触发器技术。, 安全性的定义、级别，权限，SQL中的安全性机制。, 检查点技术 并发操作、封锁带来的若干问题，并发调度的可串行化。 SQL中完整性约束的实现：断言、触发器技术。 安全性中的授权语句。,教学重点：,§1 事务的概念 一、事务的定义 形成一个逻辑工作单元的数据库操作的汇集, 称为事务(transaction)。,例：在关系数据库中，一个事务可以是一条SQL语句、 一组SQL语句 或整个程序。 事务和程序是两个概念。一般地说：一个程序中包含多个事务。 事务的开始和结束可以由用户显式控制。 如果用户没有显式地定义事务,则由DBMS按照缺省自动划分事务。,在SQL语言中，定义事务的语句由三条： 事务开始: 事务提交: 事务回滚:,BEGIN TRANSACTION,COMMIT,ROLLBACK,二、事务的ACID性质 为了保证数据完整性（数据是正确的），要求事务 具有下列四个性质：,原子性（Atomicity） 一致性（Consistency） 隔离性（Isolation） 持久性（Durability）。 上述四个性质称为事务的ACID性质。,三、事务的状态变迁,BEGIN TRANSATION,§2 数据库的恢复 DBMS的恢复管理子系统: 采取一系列措施保证在任何情况下保持事务的原子性和 持久性，确保数据不丢失、不破坏；当发生系统故障时，数 据库可恢复到正确状态。,一、故障分类 事务故障 系统故障 介质故障,二、数据库恢复技术 恢复机制涉及的两个关键问题： 如何建立冗余数据；如何利用这些冗余数据实施数据库的恢复。 建立冗余数据最常用的技术是数据库转储和登录日志文件。,1、数据转储 转储是指DBA将整个数据库复制到永久存储器的过程。 这些备用的数据文本称为后备副本或后援副本。 一旦系统发生介质故障，数据库遭到破坏，可以将副本 重新装入，把数据库恢复起来。,转储 运行事务,故障发生点,ta,tb,tf,正常运行,重装后备副本 重新运行事务,恢复,2.登记日志文件（Logging） 日志文件是记录事务对数据库的更新操作的文件。 日志文件的存储结构-日志记录的表示： 事务开始记录： 更新数据记录： 事务终止记录： 更新数据日志记录 与每一个数据库写操作 WRITE（Q）相对应其中： Ti :事务名；X:操作类型；A:数据项；V1:原始值；V2:新值,日志文件在数据库恢复过程中起着重要的作用。 为保证数据库的可恢复性， 登记日志文件必须遵循两条原则： 登记的次序必须严格按并行事务执行的时间次序。 必须先写日志文件，再写数据库。,转储 运行事务,故障发生点,ta,tb,tf,正常运行,重装后备副本 利用日志文件恢复事务 继续运行,介质故障恢复,登记日志文件,三、恢复策略 当系统运行过程中发生故障，利用数据库后备副本和日志文件将数据库恢复到故障前的某个一致性状态。不同故障其恢复技术不一样： 1事务故障的恢复 事务故障是指事务在运行至正常终止点前被中止，此时恢复子 系统应撤销（UNDO）此事务已对数据库进行的修改。,事务故障恢复的具体做法如下: 反向扫描日志文件（即从最后向前扫描日志文件）， 查找该事务的更新操作。 对该事务的更新操作执行逆操作。 即将日志记录中“更新前的值”写入数据库： 若记录中是插入操作，则相当于做删除操作; 若记录中是删除操作,则做插入操作； 若是修改操作，则用修改前值代替修改后值。,返回, 继续反向扫描日志文件，查找该事务的其他更新操 作，并做同样处理。 如此处理下去，直至读到此事务的开始标记，事务 故障恢复就完成了。 事务故障的恢复是由系统自动完成的,不需要用户干预。,2系统故障的恢复 系统故障造成数据库不一致状态的原因有两个： 未完成事务对数据库的更新已写数据库； 已提交事务对数据库的更新还留在缓冲区没来得及真正 写入数据库. 恢复操作：撤销故障发生时未完成的事务， 重做已完成的事务。,具体做法如下: 正向扫描日志文件(即从头开始扫描日志文件): 找出在故障发生前: 已提交事务(既有记录，也有记 录),将其事务标识记入重做队列。 尚未完成的事务(有记录,无记录), 将其事务标识记入撤销队列。, 对撤销队列中的各个事务进行撤销（UNDO）处理 进行撤销（UNDO）处理的方法是: 反向扫描日志文件， 对每个UNDO事务的更新操作执行逆操作. 即将日志记录中“更新前的值”写入数据库。, 对重做队列中的各个事务进行重做（REDO）处理 进行重做REDO处理的方法是： 正向扫描日志文件， 对每个REDO事务重新执行登记操作。 即将日志记录中“更新后的值”写入数据库。 系统故障的恢复也由系统自动完成的,不需要用户干预。,3介质故障的恢复 在发生介质故障和遭受病毒破坏时，磁盘上的物理数据库遭到毁灭性破坏。此时恢复的过程如下： 装入最新的后备副本到新的磁盘，使数据库恢复到最近一次转 储时的一致状态。 装入有关的日志文件副本，重做已提交的所有事务。 这样就可以将数据库恢复到故障前某一时刻的一致状态。,四、检测点机制 为提高系统效率，DBMS定时设置检查点。 在检查点时刻才真正做到把对DB的修改写到 磁盘，并在日志文件写入一条检查点记录(以便恢复时使用)。,1. 检查点方法 DBMS定时设置检查点，在检查点时，做下列事情： 第一步： 第二步： 第三步： 第四步：,将日志缓冲区中的日志记录写入磁盘。,将数据库缓冲区中修改过的缓冲块内容写入磁盘。,写一个检查点记录到磁盘，内容包括： 检查点时刻，所有活动事务； 每个事务最近日志记录地址。,把磁盘中日志检测点记录的地址写入“重新启动文件中”。,2.检查点恢复步骤 正向扫描日志文件，建立事务重做队列和事务撤消队列。 重做队列：将已完成的事务加入重做队列； 撤销队列：未完成的事务加入撤销队列。, 对撤销队列做UNDO处理的方法是： 反向扫描日志文件，根据撤销队列的记录对每一个撤销 事务的更新操作执行逆操作，使其恢复到原状态。, 对重做队列做REDO处理的方法是： 正向扫描日志文件，根据重做队列的记录对每一个重做事 务实施对数据库的更新操作。,五、运行记录（日志记录）优先原则 为了安全，定义“运行记录优先原则”包含以下两点： 至少要等相应运行记录（日志记录）已经写入运行日志文件 后，才能允许事务往数据库中写记录； 直至事务的所有运行记录（日志记录）都已经写入到运行日 志文件后，才能允许事务完成COMMIT处理。 这样，如果出现故障，则可能在运行日志中而不是在数据库中记录了一个修改。在重启动时，就有可能请求UNDOREDO处理原先根本没有对数据库做过的修改。,§3 数据库的并发控制,原子性（Atomicity） 一个事务对数据库的所有操作，是一个不可分割的工作单元。这些操作要么全部执行，要么什么也不做（就效果而言）。 保证原子性是数据库系统本身的职责， 由DBMS的事务管理子系统来实现。,返回,一致性（Consistency） 一个事务独立执行的结果，应保持数据库的一致性， 即数据不会应事务的执行而遭受破坏。 编写事务的应用程序员的职责:确保单个事务的一致性。 在系统运行时，由DBMS的完整性子系统执行测试任务。,返回,隔离性（Isolation） 在多个事务并发执行时,系统应保证与这些事务先后单独执行时的结果一样,此时称事务达到了隔离性的要求。 即：多个事务并发执行时，保证执行结果是正确的， 如同单用户环境一样。 隔离性是由DBMS的并发控制子系统实现的。,返回,持久性（Durability） 一个事务一旦完成全部操作后，它对数据库的所有更新应永久地反映在数据库中。即使以后系统发生故障，也应保留这个事务执行的痕迹。 持久性由DBMS的恢复管理子系统实现的。,返回,§3 数据库的并发控制 教学内容： 并发操作带来的三个问题，X锁、PX协议,活锁、 死锁，并发调度、串行调度、并发调度的可串行化， 两段封锁法。 教学重点： 并发操作、封锁带来的若干问题， 并发调度的可串行化。,为充分利用数据库资源，发挥数据库共享资源的特点,应该允许多个用户并行地存取数据库。 并发控制机制的好坏是衡量一个数据库管理系统性能的重要标志之一。,一、并发操作带来的三个问题 对并发操作如果不进行合适的控制，可能会导致数据库中数据的不一致性。 典型的并发操作的例子：火车订票系统中的订票操作。 在该系统中的一个活动序列： 甲售票员读出某列车的车票余数为A，设：A=18； 乙售票员读出同一列车的车票余数为A ，也是：A= 18；, 甲售票点卖出一张车票，修改车票余数A=A-1，所以A17， 把A写回数据库； 乙售票点卖出二张车票，修改车票余数A=A-2，所以A=16， 把A写回数据库。,事实上是卖出三张车票,而数据库中车票余额只减少2。,这种情况称为数据库的不一致性。这种不一致性是由甲乙两个 售票员并发操作引起的。（在一个CPU上，利用分时方法实行多个 事务同时执行）。 在并发操作情况下，对甲、乙两个事务的操作序列的调度是随机的。若按上面的调度序列执行，甲事务的修改就被丢失。这是由于 第4步中乙事务修改A并写回后覆盖了甲事务的修改。 并发操作带来的数据不一致性包括三类： 丢失修改； 不一致分析（不可重复读）； 读“脏”数据。,并发操作带来的数据不一致性包括三类： 丢失修改； 不一致分析（不可重复读）； 读“脏”数据。,1. 丢失更新（Lost update） 指事务Ti与事务Tj从数据库中读入同一数据并修改,事务2的提交结果破坏了事务1提交的结果,导致事务1的修改被丢失。,2.不一致分析（不可重复读nonrepeatable read ） 指事务Ti读取数据后，事务Tj执行更新操作，使事务Ti无法再读取前一次结果。,具体地讲，不一致分析（不可重复读）包括三种情况：, 事务Ti读取某一数据后，事务Tj对其做了修改，当事务Ti再 次读该数据时：得到与前一次不同的值。, 事务Ti按一定条件从数据库中读取某些数据记录后，事务Tj 删除了其中部分记录,当事务Ti再次按相同条件读取数据时： 发现某些记录消失了。, 事务Ti按一定条件从数据库中读取某些数据记录后，事务Tj 插入了一些记录，当事务Ti再次按相同条件读取数据时： 发现多了一些记录。, 两种不可重复读有时也称为幻行（phantom row）现象。,3.读“脏”数据（diriy read） 指:事务Ti修改某一数据，并将其写回磁盘，事务Tj读取同一数据后，事务Ti由于某种原因被撤销，这时事务Ti已修改过的数据恢复原值，事务Tj读到的数据就与数据库中的数据不一致，是不正确的数据，称为“脏”数据。,产生上述三类数据不一致性的主要原因是: 并发操作破坏了事务的隔离性。,并发控制就是要用正确的方式调度并发操作，使一个 用户事务的执行不受其他事务的干扰，从而避免造成数 据的不一致性。 DBMS的并发控制子系统的职责： 负责协调并发事务的执行， 保证数据库的完整性， 同时避免用户得到不正确的数据。,计算机系统对并行事务中并行操作的调度是随机的，而不同的调度可能会产生不同的结果，那么哪个结果是正确的，哪个是不正确的呢？,二、并发调度的可串行化 1、概念 事务的调度: 串行调度: 并发调度:,事务的执行次序称为“调度”。,如果多个事务依次执行，则称为事务的串行 调度（Serial Schedule）。,如果利用分时的方法，同时处理多个事 务，则称为事务的并发调度（Concurrent Schedule）。,在事务并发执行时,有可能破坏数据库的一致性, 或用户读了脏数据。,如果有n个事务串行调度，可有n!种不同的有效调度。 如果有n个事务并发调度,可能的并发调度数目远远大于n!。 DBMS的并发控制子系统实现: 如何产生正确的并发调度。 如何判断一个并发调度是正确的, 用并发调度的可串行化概念解决.,现在有两个事务，分别包含下列操作： 事务T1：读B；A=B十1；写回A； 事务T2：读A；B=A十1；写回B 假设A的初值为10，B的初值为2。,下图给出了对这两个事务的三种不同的调度策略。 （a）和（b）为两种不同的串行调度策略，虽然执行结果不同， 但它们都是正确的调度。 （c）中两个事务是交错执行的，由于其执行结果与（a）、 （b）的结果都不同，所以是错误的调度。 （d）中两个事务也是交错执行的，由于其执行结果与串行调 度1（图（a）的执行结果相同，所以是正确的调度。,（a）串行调度1(先T1后T2),（b）串行调度2 (先T2后T1),（c）不可串行化调度(交错执行),（d）可串行化调度 (结果同串行调度1),为了保证并行操作的正确性: DBMS的并行控制机制必须提供一定的手段来 保证调度是可串行化的。 从理论上讲，在某一事务执行时禁止其他事务执行的调度策略一定是可串行化的调度，这也是最简单的调度策略，但这种方法实际上是不可行的，因为它使用户不能充分共享数据库资源。,2可串行化调度定义: 每个事务中，语句的先后顺序在各种调度中始终保持一致。 在这个前提下: 如果一个并发调度的执行结果与某一串行调度的执行结果等价，那么这个并发调度称为“可串行化的调度”，否则是不可串行化的调度。 可串行性（serializable）是并发事务正确性的唯一准则。,为保证并行操作调度的可串行性 目前DBMS普遍采用封锁方法来保证调度的正确性， 另外还有其他一些方法：时标方法、乐观方法等。,三、封锁 封锁是实现并发控制的一个非常重要的技术。 封锁： 事务T在对某个数据对象（表或记录等）操作之前， 先向系统发出请求，对其加锁。 加锁后事务T对该数据对象有了一定的控制， 在事务T释放它的锁之前,其他的事务不能更新此数据对象。,1、封锁类型 基本的封锁类型有两种： 排它锁（exclusive lock,简记为X锁）; 共享锁（share lock, 简记为S锁）。, 排它锁(X锁): 如果事务T对某个数据实现X锁，那么其他事务T要 等T解除X锁以后，才能对这个数据进行封锁。也就是不 允许其他事务T再对该数据加任何类型的锁。,采用X锁的并发控制并发度低，只允许一个事务独占数据。而其他申请封锁的事务只能排队去等。 为此，降低要求，允许并发的读，就引入了共享型封锁（Shared Lock），这种锁简称为S锁，又称为读锁。,(2)共享锁(S锁): 如果事务T对某数据加上S锁后，仍允许其他事务再对该数据加S锁，但在对该数据的所有S锁都解除之前决不允许任何事务对该数据加X锁。 相容矩阵:,2、封锁粒度 X锁和S锁都是加在某一个数据对象上的。 封锁的对象可以是逻辑单元，也可以是物理单元。,在关系数据库中，封锁对象可以是: 属性值、属性值集合、元组、关系、索引项、整个索引、 整个数据库等逻辑单元；也可以是页（数据页或索引页）、 块等物理单元。 封锁对象可以很大，比如对整个数据库加锁，也可以很小， 比如只对某个属性值加锁。 封锁对象的大小称为封锁的粒度（granularity）。,封锁粒度与系统的并发度和并发控制的开销密切相关。 封锁的粒度越大，系统中能够被封锁的对象就越少， 并发度也就越小，但同时系统开销也越小； 相反，封锁的粒度越小，并发度越高，但系统开销也就越大。,因此，如果在一个系统中同时存在不同大小的封锁单元供不同的事务选择使用是比较、理想的。 选择封锁粒度时必须同时考虑封锁机构和并发度两个因素，对系统开销与并发度进行权衡，以求得最优的效果。 一般说来，需要处理大量元组的用户事务可以以关系为封锁单元；需要处理多个关系的大量元组的用户事务可以以数据库为封锁单位；而对于一个处理少量元组的用户事务，可以以元组为封锁单位以提高并发度。,3、封锁协议 封锁的目的是为了保证能够正确地调度并发操作。 在运用X锁和S锁这两种基本封锁，对一定粒度的数据对象加锁时，还需要约定一些规则，例如，应何时申请X锁或S锁、持锁时间、何时释放等。称这些规则为封锁协议（1ocking protocol）。 对封锁方式规定不同的规则，就形成了各种不同的封锁协议，它们分别在不同的程度上为并发操作的正确调度提供一定的保证。,（1）、保证数据一致性的封锁协议 PX协议和PXC协议使用X锁的规则。 PX协议： PXC协议：,任何事务T在更新记录R之前必须先执行“XFIND R”操 作，以获得对R的X锁，才能读或写记录R；如果未获准X锁，那么 这个事务进入等待状态。一直到获准X锁，事务才能继续做下 去。（如果过早地解锁，有可能使其他事务读了未提交数据（且 随后被回退），引起丢失其他事务的更新。,PX协议加上X锁的解除操作应该合并到事务的结 束（COMMIT或ROLLBACK）操作中。（可解决丢失更新）,等事务T1更新完成后再执行事务T2：（可解决丢失更新）, PS协议和PSC协议使用S锁的规则 PS协议： PSC协议：,任何要更新记录R的事务必须先执行“SFIND R”操作, 以获得对R的S锁.当事务获准对R的S锁后,若要更新记录R必须用 “UPDX R”操作，这个操作首先把S锁升级为X锁，若成功则更新 记录，否则这个事务进入等待队列。,注意：获准S锁的锁事务只能读数据，不能更新数据，若要更 新，则先要把S锁升级为X锁。,PS协议加上S锁的解除操作应该合并到事务的结束 （COMMIT或ROLLBACK）操作中。 （ PSC协议解决不一致分析和读“脏”数据问题）,解决不一致分析和读“脏”数据问题：,封锁技术可以有效地解决并行操作的一致性问题; 但也带来新的问题，即活锁和死锁的问题。 “活锁”:系统可能使某个事务永远处于等待状态，得不到封锁的 机会。 “死锁”:系统中有两个或两个以上的事务都处于等待状态，并 且每个事务都在等待其中另一个事务解除封锁，它才能 继续执行下去，结果造成任何一个事务都无法继续执行。,活 锁:,避免活锁的 简单方法是：采用先来先服务的策略。,“死 锁”: 系统中有两个或两个以上的事务都处于等待状态，并且每个事务都在等待其中另一个事务解除封锁，它才能继续执行下去，结果造成任何一个事务都无法继续执行。,用事务依赖图的形式测试系统中是否存在死锁。 事务依赖图中：每一个结点表示“事务”； 箭头表示事务间的依赖关系。,例:并发执行中两个事务的依赖关系如下图所示： 事务T1需要数据B，但B已被事务T2封锁， 那么从T1到T2画一个箭头； 事务T2需要数据A，但A已被事务T1封锁， 那么从T2到T1也应画一个箭头。 如果在事务依赖图中沿着箭头方向存在一个循环，那么死锁的条件就形成了，系统进入死锁状态。 事务依赖图:,DBMS中有一个死锁测试程序: 每隔一段时间检查并发的事务之间是否发生死锁。 如果发生死锁: 只能抽取某个事务作为牺牲品，把它撤消， 做回退操作，解除它的所有封锁，恢复到该事务的初始 状态。释放出来的资源就可以分配给其他事务，使其他 事务有可能继续运行下去，就有可能消除死锁现象。,理论上讲， 系统进入死锁状态时可能会有许多事务在相互等待， 但是System R的实验表明，实际上绝大部分的死锁只涉及到两个事务，也就是事务依赖图中的循环里只有两个事务。 死锁也被形象地称作“死死拥抱” 。,（2）、保证并行调度可串行性的封锁协议两段锁协议 可串行性是并行调度正确性的唯一准则,两段锁（two-phase 1ocking）协议是为了保证并行调度可串行性提供的封锁协议。 两段锁协议规定： 在对任何数据进行读写操作之前,事务必须获得对该数据的封锁 在释放一个封锁之后，事务不再获得任何其他封锁。 两段锁的实际含义是事务分为两个阶段： 第一阶段是获得封锁，也称为扩展阶段； 第二阶段是释放封锁，也称为收缩阶段。,例: 事务T1的封锁序列是： (遵守两段锁协议) SFIND ASFIND BXFIND C 解锁B 解锁A 解锁C; 事务T2的封锁序列是： (不遵守两段锁协议) SFINDA 解锁ASFIND B XFIND C 解锁C 解锁B;,可以证明，若并行执行的所有事务均遵守两段锁协议，则对这些事务的所有并行调度策略都是可串行化的。 结论： 所有遵守两段锁协议的事务，并行执行的结果 一定是正确的。 注意：事务遵守两段锁协议是可串行化调度的充分条件，而不是必要条件。即可串行化的调度中，不一定所有事务都必须符合两段锁协议。 两段锁协议仍有可能导致死锁，而且会增多，因为每个事务都不能及时解除被它封锁的数据,§4 数据库管理完整性 教学内容： 完整性的定义； 完整性子系统的功能； 完整性规则的组成； SQL中的三大类完整性约束； SQL3中的触发器技术。,重点： SQL中的三大类完整性约束； SQL3中的触发器技术。,数据库的完整性是指数据的正确性和相容性。,数据库的完整性机制: 检查数据库中数据是否满足规定的条件(完整性检查)。 完整性约束条件：数据库中数据应该满足的条件。 也称为完整性规则。 DBMS中执行完整性检查的子系统称为“完整性子系统”。,一、完整性子系统的主要功能： 监督事务的执行，并测试是否违反完整性规则。 如有违反现象，则采取恰当的操作。 如拒绝，报告违反情况，改正错误等方法来处理。,二、完整性规则的组成与分类 1、每个规则由三部分组成： 什么时候使用规则进行检查（规则的“触发条件”）； 要检查什么样的错误（ “ 约束条件”或“谓词”）； 若检查出错误，该怎样处理（ “ELSE子句”，即违反时 要做的动作）。,2、在关系数据库中，完整性规则可分为三类： 域完整性规则：定义属性的取值范围； 基本表约束； 断言。,三、SQL中的完整性约束 1. 域完整性规则：定义属性的取值范围-属性值约束。 包括：域约束子句、非空值约束、基于属性的检查子句。,用“CREATE DOMAIN”语句定义新的域,并可出现CHECK子句。 例: 定义一个新的域DEPT，可用下列语句实现： CREATE DOMAIN DEPT CHAR （20） DEFAULT 计算机软件 CONSTRAINT VALID_ DEPT /*域约束名字*/ CHECK（VALUE IN (计算机科学与技术,计算机软件)； 允许域约束上的CHECK子句中可以有任意复杂的条件表达式。,非空值约束（NOT NULL） 例： SNO char(4) NOT NULL,基于属性的检查子句(CHECK): 例：CHECK (GRADE IS NULL) OR (GRADE BRTWEEN 0 AND 100),2. 基本表约束： 主键约束：主键可用主键子句或主键短语定义; 外键约束：用外键子句定义外键： FOREIGN KEY（列名序列1）. REFERENCES （） ON DELETE ON UPDATE 其中：列名序列1是外键; 列名序列2是参照表中的主键或候选键。,参照动作可以有五种方式： NO ACTION（无影响）、 CASCADE（级联方式）、 RESTRICT（受限方式）、 SET NULL（置空值） SET DEFAULT（置缺省值）。 基于元组的检查子句全局约束 CHECK（条件表达式）,3. 断言： 如果完整性约束与多个关系有关，或者与聚合操作有关，SQL提供“断言”（Assertions）机制让用户书写完整性约束。 断言可以像关系一样，用CREATE语句定义。,定义断言： CREATE ASSERTION CHECK（） 与SELECT语句中WHERE子句中的条件表达式一样。 撤消断言： DROP ASSERTION ,例：设有三个关系模式： EMP（ENO，ENAME，AGE，SEX，ECITY） COMP（CNO，CNAME，CITY） WORKS（ENO，CNO，SALARY） 试用SQL的断言机制定义下列完整性约束：,每个职工至多可在3个公司兼职工作: CREATE ASSERTION ASSE1 CHECK （ 3 = ALL (SELECT COUNT(CNO) FROM WORK GROUP BY ENO ）；, 每门公司男职工的平均年龄不超过40岁: CREATE ASSERTION ASSE2 CHECK （40 = ALL（SELECT AVG(EMP.AGE） FROM EMP,WORK WHERE EMP.ENO=WORK.ENO AND SEX=男 GROUP BY CNO）；, 不允许女职工在建筑公司工作: CREATE ASSERTION ASSE3 CHECK （ NOT EXISTS （SELECT * FROM WORK WHERE CNO IN（SELECT CNO FROM COMP WHERE CNAME =建筑公司) AND ENO IN（SELECT ENO FROM EMP WHERE SEX=女);,断言也可以在关系定义中用检查子句形式定义，但是检查子句不一定能保证完整性约束彻底实现，而断言能保证不出差错。,四、SQL3的触发器主动规则 系统能自动根据条件转去执行各种操作， 甚至执行与原操作无关的一些操作。,1触发器结构 定义： 触发器（Trigger）是一个能由系统自动执行对数据库修改的语句。触发器有时也称为主动规则（Active Rule）或事件条件动作规则（EventConditionAction Rule，ECA规则）。,一个触发器由三部分组成： 事件: 条件: 动作:,如果触发器测试满足预定的条件，那么 就由DBMS执行相应的动作（即对数据库 的操作）。,指对数据库的插入、删除、修改等操作。触发器在这些事件发生时，将开始工作。,触发器将测试条件是否成立。如果条件 成立，就执行相应的动作，否则什么也 不做。, 触发器的命名 触发时间 触发事件 目标表名 旧值和新值的别名表(设置变量) 触发动作间隔时间 动作条件 动作体。,触发器结构的组成:,触发器结构的组成: (1)触发时间: BEFORE、 AFTER、 INSTEAD OF ; (2)触发事件:有三类- UPDATE、DELETE、INSERT (3)目标表名: 当目标表的数据被更新(插入、删除、修 改）时, 将激活触发器。 (4)旧值和新值的别名表: REFERENCES子句 (5)触发动作:,触发事件中的时间关键字有三种： BEFORE：在触发事件进行前，测试WHEN条件是否满足。若满足则先执行动作部分的操作，然后再执行触发事件的操作（此时可不管WHEN条件是否满足）。 AFTER：在触发事件完成以后，测试WHEN条件是否满足，若满足则执行动作部分的操作。 INSTEAD OF：在触发事件发生时。只要满足WHEN条件，就执行动作部分的操作，而触发事件的操作不再执行。,触发动作: 动作时间间隔: FOR EACH ROW 或 FOR EACH STATEMENT。 动作条件：动作条件用WHEN子句定义 动作体：当触发器被激活时DBMS要执行的SQL语句。 动作体若是一个SQL语句，直接写上即可； 若是一系列的SQL语句，则用分号定界，再使用BEGIN ATOMICEND限定。,如果触发事件是UPDATE： 应该用“OLD AS”和“NEW AS”子句 定义修改前后的元组变量； 如果是DELETE：只要用“OLD AS”子句定义元组变量； 如果是INSERT：只要用“NEW AS”子句定义元祖变量。,触发器有两类：元组级触发器和语句级触发器。 元组级触发器 带 “FOR EACH ROW” 子句， 而语句级触发器没有； 元组级触发器对每一个修改的元组都要检查一次， 而语句级触发器对SQL语句的执行结果去检查。 语句级触发器，不能直接引用修改前后的元组，但可以引用修改前后的元组集。旧的元组集由被删除的元组或被修改元组的旧值组成，而新的元组集由插入的元组或被修改元组的新值组成。,2SQL3的触发器实例 【实例1】 某单位修改工资原则:如果职工工资修改后仍低于900元，那么，在修改后的工资基础上再增加原工资的5%，但不得超过900元（元组级触发器 ）。,【实例2】 在学习关系SC表中修改课程号CNO,即学生的选课登记需作变化。在关系SC中的约束：要求保持每门课程选修人数不超过50。如果更改课程号后，违反这个约束，那么这个更改应该不做。（语句级触发器）,修改工资 触发器结构：,触发器命名:TRI_UPD_SALARY,设置元组变量: OLDTUPLE、NEWTUPLE,结束,CREAT TRIGGER TRI_UPD_SALARY /*触发器命名*/ AFTER UPDATE OF SALARY ON WORK /*触发时间、触发事件、目标表*/ REFERENCING /*设置必要的变量*/ OLD AS OLDTUPLE /*为元组级触发器设置变量*/ NEW AS NEWTUPLE FOR EACH ROW /*触发器的动作时间间隔*/ WHEN ( 900 NEWTUPLE.SALARY ) /*触发器的动作条件部分*/ BEGIN ATOMIC UPDATE WORK /*触发器的动作部分1*/ SET SALARY=NEWTUPLE.SALARY+OLDTUPLE.SALARY*0.5 WHERE ENO=NEWTUPLE.ENO AND(900 NEWTUPLE.SALARY + OLDTUPLE.SALARY*0.5); UPDATE WORK /*触发器的动作部分2*/ SET SALARY=900 WHERE ENO=NEWTUPLE.ENO AND(900 NEWTUPLE.SALARY+OLDTUPLE.SALARY*0.5); END;,CREAT TRIGGER TRI_UPD_SALARY /*触发器命名*/ AFTER UPDATE OF SALARY ON WORK /*触发时间,触发事件,目标表*/ REFERENCING /*设置必要的变量*/ OLD AS OLDTUPLE /*为元组级触发器设置变量*/ NEW AS NEWTUPLE FOR EACH ROW /*触发器的动作时间间隔*/ WHEN ( 900 NEWTUPLE.SALARY ) /*触发器的动作条件部分*/,BEGIN ATOMIC UPDATE WORK /*触发器的动作部分1*/ SET SALARY=NEWTUPLE.SALARY+OLDTUPLE.SALARY*0.5 WHERE ENO=NEWTUPLE.ENO AND (900NEWTUPLE.SALARY+OLDTUPLE.SALARY*0.5); UPDATE WORK /*触发器的动作部分2*/ SET SALARY=900 WHERE ENO=NEWTUPLE.ENO AND (900NEWTUPLE.SALARY+OLDTUPLE.SALARY*0.5); END;,【实例2】在学习关系SC表中修改课程号CNO,即学生的选课登记需作变化。在关系SC中的约束：要求保持每门课程选修人数不超过50。如果更改课程号后，违反这个约束，那么这个更改应该不做。（语句级触发器）,CREATE TRIGGER TRI_UPD_SC /*触发器的命名*/ INSTEAD OF UPDATE OF CNO ON SC /*时间、事件、目标*/ REFERENCING /*设置变量*/ OLD_TABLE AS OLDSTUFF /*为语句级触发器设置变量*/ NEW_TABLE AS NEWSTUFF WHEN （50 = ALL（SELECT COUNT(SNO) /*动作时间条件*/ FROM (SC EXCEPT OLDSTUFF) UNION NEWSTUFF) GROUP BY CNO） BEGIN ATOMIC /*动作体*/ DELETE FROM SC /*触发动作1*/ WHERE (SNO,CNO,GRADE) IN OLDSTUFF; INSERT INTO SC /*触发动作2*/ SELECT * FROM NEWSTUFF END;,CREATE TRIGGER TRI_UPD_SC /*触发器的命名*/ INSTEAD OF UPDATE OF CNO ON SC /*时间,事件,目标*/ REFERENCING /*设置变量*/ OLD_TABLE AS OLDSTUFF /*为语句级触发器设置变量*/ NEW_TABLE AS NEWSTUFF WHEN （50 = ALL（SELECT COUNT(SNO) /*动作时间条件*/ FROM (SC EXCEPT OLDSTUFF) UNION NEWSTUFF) GROUP BY CNO）,BEGIN ATOMIC /*动作体*/ DELETE FROM SC /*触发动作1*/ WHERE (SNO,CNO,GRADE) IN OLDSTUFF; INSERT INTO SC /*触发动作2*/ SELECT * FROM NEWSTUFF END;,触发器的动作时间:INSTEAD OF（第2行），任何企图修改关系SC中CNO值都被这个触发器截获，并且触发事件的操作（即修改CNO）不再进行，由触发器的条件真假值来判断是否执行动作部分的操作。 INSTEAD OF 表示：在触发事件发生时,只要满足WHEN条件，就执行动作部分的操作，而触发事件的操作不再执行。 动作部分的操作由两个SQL语句组成，前一个语句是从关系SC中删除修改前的元组，后一个语句是在关系SC中插入修改后的元组。用这样的方式完成触发事件的操作。 因为是语句级触发器，所以没有FOR EACH ROW，在这里FOR EACH STATEMENT也省略了。,五、SQL Server的数据库完整性及实现方法 SQL Server具有较健全的数据库完整性控制机制。 SQL Server使用约束、缺省，规则和触发器4种方法 定义和实施数据库完整性功能。,1、SQL Server的数据完整性的种类 SQL Server中的数据完整性包括 域完整性、实体完整性和 参照完整性3种。 （1）域完整性为列级和元组级完整性-为列或列组指定一个 有效的数据集，并确定该列是否允许为空。 （2）实体完整性为表级完整性-要求表中所有的元组都应该有 一个唯一的标识符(主码)。 （3）参照完整性是表级完整性-维护参照表中的外码与被参照 表中主码的相容关系。,2、SQL Server数据完整性的两种方式 SQL Server 使用声明数据完整性和过程数据完整性两种方式实现