数据库第六章

数据库系统原理1陈岭浙江大学计算机学院关系模型2事务完整性约束触发器数据安全性审计跟踪SQL语言（4）事务3事务（transaction）由查询和更新语句的序列组成。SQL标准规定当一条SQL语句被执行，就隐式地开始了一个事务。下列SQL语句之一会结束一个事务:Commitwork:提交当前事务，也就是将该事务所做的更新在数据库中持久保存。在事务被提交后，一个新的事务自动开始Rollbackwork：回滚当前事务，即撤销该事务中所有SQL语句对数据库的更新。这样，数据库就恢复到执行该事务第一条语句之前的状态事务4动机示例：updateaccountsetbalance=balance–100whereaccount_number=‘A-101’;updateaccountsetbalance=balance+100whereaccount_number=‘A-201’;COMMITWORK;如果其中一个更新成功，另一个更新失败，会数据库中导致数据不一致问题因此，这两个更新要么全部成功，要么全部失败原子性（atomic）一个事务或者在完成所有步骤后提交其行为，或者在不能成功完成其所有动作的情况下回滚其多有动作事务5事务的四个性质：原子性（atomic）一致性(consistency)隔离性(isolation)持久性(durability)在很多SQL实现中，默认方式下每个SQL语句自成一个事务，且一执行完就提交如果一个事务要执行多条SQL语句，就必须关闭单独SQL语句的自动提交，如何关闭自动提交也依赖于特定的SQL实现一个较好的选择是，作为SQL:1999标准的一部分，允许多条SQL语句包含在关键字beginatomic…end之间完整性约束6完整性约束保证授权用户对数据库所做的修改不会破坏数据的一致性完整性约束的例子有：教师姓名不能为null任意两位教师不能有相同的教师标识course关系中的每个系名必须在department关系中有一个对应的系名一个系的预算必须大于0.00美元域完整性、实体完整性（主键的约束）、参照完整性（外键的约束）和用户定义的完整性约束完整性约束是数据库实例(Instance)必须遵循的完整性约束由DBMS维护单个关系上的约束7单个关系上的约束notnulluniquecheck（谓词）例，CREATETABLEinstructor2(IDchar(5)primarykey,namevarchar(20)notnull,dept_namevarchar(20),salarynumeric(8,2)notnull,check(salary=0));域约束8域约束是完整性约束的最基本形式，可用于检测插入到数据库中的数据的合法性从现有数据类型可以创建新的域createdomainDollarsasnumeric(12,2)notnullcreatedomainPoundsasnumeric(12,2);createtableinstructor(IDchar(5)primarykey,namevarchar(20),dept_namevarchar(20),salaryDollars,commPounds);域约束9check子句也可以应用到域上例，check子句可以保证教师工资域中只允许出现大于给定值的值createdomainYearlySalarynumeric(8,2)constraintsalary_value_testcheck(value=29000.00);YearlySalary域有一个约束来保证年薪大于或等于29000.00美元constraintsalary_value_test子句是可选的，它用来将该约束命名为salary_value_test。系统用这个名字来指出一个更新违反了哪个约束作为另一个例子，使用in子句可以限定一个域只包含指定的一组值createdomaindegree_levelvarchar(10)constraintdegree_level_testcheck(valuein(’Bachelors’,’Masters’,or’Doctorate’));参照完整性10参照完整性约束定义：令关系r1和r2的属性集分别为R1和R2，主码分别为K1和K2如果要求对r2中任意元组t2，均存在r1中元组t1使得t1[K1]=t2[]，我们称R2的子集为参照关系r1中K1的外码（foreignkey）参照完整性约束也称为子集依赖，可写作：(r2)K1(r1)参照完整性11回顾第二讲中外键的内容假设存在关系r和s：r(A,B,C),s(B,D)，则在关系r上的属性B称作参照s的外码，r也称为外码依赖的参照关系，s叫做外码被参照关系例，学生(学号,姓名,性别,专业号,年龄)-参照关系专业(专业号,专业名称)-被参照关系(目标关系)其中属性专业号称为关系学生的外码instructor(ID,name,dept_name,salary)-参照关系department(dept_name,building,budget)-被参照关系选修(学号,课程号,成绩)课程(课程号,课程名,学分,先修课号)参照关系中外码的值必须在被参照关系中实际存在或为null数据库的修改12数据库的修改会导致参照完整性的破坏。这里列出对各种类型的数据库修改应做的测试，以保持如下的参照完整性约束：插入。如果向r2中插入元组t2，则系统必须保证r1中存在元组t1使得t1[K]=t2[]。即t2[]K(r1)删除。如果从r1中删除元组t1，则系统必须计算r2中参照t1的元组集合。即=t1[K](r2)―如果集合非空，要么删除命令报错并撤销，要么参照t1的元组本身必须被删除（可能导致级联删除）(r2)K1(r1)数据库的修改13更新。必须考虑两种更新：对参照关系r2做更新，以及对被参照关系r1做更新―如果关系r2中元组t2被更新，并且更新修改外码上的值，则进行类似插入情况的测试。令t2’表示元组t2的新值，则系统必须保证t2’[]K(r1)―如果关系r1中元组t1被更新，并且该更新修改主码K上的值，则进行类似删除情况的测试。系统必须用旧的t1值（更新前的值）计算=t1[K](r2)如果该集合非空，则更新失败，或者以类似删除的方式做级联更新数据库的修改14更新。必须考虑两种更新：对参照关系r2做更新，以及对被参照关系r1做更新―如果关系r2中元组t2被更新，并且更新修改外码上的值，则进行类似插入情况的测试。令t2’表示元组t2的新值，则系统必须保证t2’[]K(r1)―如果关系r1中元组t1被更新，并且该更新修改主码K上的值，则进行类似删除情况的测试。系统必须用旧的t1值（更新前的值）计算=t1[K](r2)如果该集合非空，则更新失败，或者以类似删除的方式做级联更新SQL中的参照完整性15主码、候选码和外码可在SQL的createtable语句中指明primarykey子句包含一组构成主码的属性unique子句包含一组构成候选码的属性foreignkey子句包含一组构成外码的属性以及被修改外码所参照的关系名SQL中的参照完整性16默认地，外码参照被参照关系中的主码foreignkey(dept_name)referencesdepartment可以使用如下的简写形式定义单个列为外码dept_namevarchar(20)referencesdepartment被参照关系中的属性可以被明确指定，但是必须被声明为主码或候选码foreignkey(dept_name)referencesdepartment(dept_name)名字可以不同SQL中的参照完整性17例，createtableclassroom(buildingvarchar(15),room_numbervarchar(7),capacitynumeric(4,0),primarykey(building,room_number))createtabledepartment(dept_namevarchar(20),buildingvarchar(15),budgetnumeric(12,2)check(budget0),primarykey(dept_name))SQL中的参照完整性18createtablecourse(course_idvarchar(8),titlevarchar(50),dept_namevarchar(20),creditsnumeric(2,0)check(credits0),primarykey(course_id),foreignkey(dept_name)referencesdepartment)createtableinstructor(IDvarchar(5),namevarchar(20),notnulldept_namevarchar(20),salarynumeric(8,2),check(salary29000),primarykey(ID),foreignkey(dept_name)referencesdepartment)SQL中的级联动作19由于有了与外码声明相关联的ondeletecascade子句，如果删除department中的元组导致了此参照完整性约束被违反，则删除并不被系统拒绝，而是对course关系作“级联”删除，即删除了被删除系的元组“级联”更新也类似createtablecourse(...foreignkey(dept_name)referencesdepartment[ondeletecascade][onupdatecascade]...);SQL中的级联动作20如果存在涉及多个关系的外码依赖链，则在链一端所做的删除或更新可能传至整个链但是，如果一个级联更新或删除导致的对约束的违反不能通过进一步的级联操作解决，则系统终止该事务即，该事务所做的所有改变及级联动作将被撤销SQL中的级联动作21参照完整性只在事务结束时检查中间步骤可以破坏参照完整性,只要后续步骤解消这种破坏即可否则不可能建立某些数据库状态,例如插入两条互相有外键引用的元组―例，关系marriedperson的spouse属性marriedperson(name,address,spouse)SQL中的级联动作22除级联操作之外的其他选择:ondeletesetnullondeletesetdefault外键属性上的空值使SQL的参照完整性语义变得复杂,最好用notnull来防止若某外键属性为null,则该元组按定义是满足参照完整性约束的断言23断言（assertion）是表达要求数据库永远满足的条件的谓词（复杂check条件）SQL中的断言形式如下：createassertionassertion-namecheckpredicate创建了某断言之后,系统将检查它的合法性,并对每一个可能破坏该断言的数据库更新进行检测这种检测会产生大量的开销，因此断言的使用应非常谨慎由于SQL不提供“forallX,P(X)”结构，我们可以通过迂回的方式表达：notexistsXsuchthatnotP(X)∵(x)P(x)=¬(x)¬P(x)断言24例1，对于student关系中的每个元组，它在属性tot_cred上的取值必须等于其所成功修完课程的学分总和createassertioncredits_earned_constraintcheck(notexists(selectIDfromstudentwheretot_cred(selectsum(credits)fromtakesnaturaljoincoursewherestudent.ID=takes.IDandgradeis