6.1 数据完整性、安全性、事务
6.1.1 事务及完整性约束
事务定义：
事务的性质：
事务的结束：
示例：
UPDATE account SET balance = balance - 100
WHERE account_number = 'A-101';
UPDATE account SET balance = balance + 100
WHERE account_number = 'A-201';
COMMIT WORK;完整性约束：
① 单个关系上的约束
例1
create table instructor2
(
ID char(5) primary key,
name varchar(20) not null, --姓名不能为空
dept_name varchar(20),
salary numeric(8,2) not null, --薪水不能为空
check (salary>=0); --薪水不能为负值② 域约束
例1，
/*定义域约束*/
create domain Dollars as numeric(12,2) not null
create domain Pounds as numeric(12,2);
create table instructor
(
ID char(5) primary key,
name varchar(20),
dept_name varchar(20),
salary Dollars, --域约束指定薪水不能为空
comm Pounds
);例2，使用check子句保证教师工资域中只允许出现大于给定值的值
/*check子句也可应用于域上*/
create domain YearlySalary numeric(8,2) --保证年薪大于或等于29000.0
constraint salary_value_test check(value>29000.0); --可选子句，系统用sal...test指定更新违反了哪个操作例3，使用in子句限定一个域只包含指定的一组值
create domain degree_level varchar(10)
constraint degree_level_test
check (value in('Bachelors','Masters','Doctorate')); --约束学历水平只能是‘学士’，‘硕士’，‘博士’中的一个。参照完整性：
假设有如下关系：
则有：
主码、外码、候选码的指明：
例，
/*三种声明外码的方法*/
foreign key (dept_name) references department --外码：dept_name,被参照关系：department
dept_name varchar(20) references department
foreign key (dept_name) references department (dept_name)SQL中的级联动作：
create table course(
...
foreign key(dept_name) references department
[on delete cascade] --此可选子句表示：如果删除元组导致参照完整性被违反，则进行级联删除
[on update cascade] --更新同上6.1.2 断言及触发器
断言（assertion）:
例1，对于student关系中的每个元组，它在属性tot_cred上的取值必须等于其所成功修完课程的学分总和
create assertion credits_earned_constraint check --学分挣得约束
(not exists --集合不为空，检测结果为false
(select ID
from student
where tot_cred <>( --总学分不等于学分总和，则向集合中添加元素
select sum(credits) --计算学分总和
from takes natural join course --自然内连接，同名字段相等
where grade is not null --此门课程学分不为空
and grade <>'F'))); --此门课程未不及格例2，每位教师不能在同一个学期的同一个时间段在两个不同的教室授课
/*将一个老师同一学期同一时间段进行group by分组求和，
*将和大于1的元素挑选出来放入集合，利用not exists进行集合是否非空的判断
*/
create assertion ins_teaches_constraint check
not exists(
select ID,name,section_id,semester,years,tim_slot_id,count(distinct building,room_number)
from instructor natural join teaches natural jion section
group by (ID,name,section_id,semester,years,time_slot_id)
having count(building,room_number)>1);触发器（trigger）:
触发事件包括：
例1，使用触发器来确保参照关系section中属性time_slot_id的参照完整性，即在被参照关系time_slot中存在
create trigger timeslot_check1 after insert on section --创建一个名为timeslot_check1的触发器，触发条件是向section中插入语句
referencing new row as nrow --引用插入的新行，命别名为nrow
for each row
when(
nrow.time_slot_id not in( --当新行的time_slot_id不在集合中
select time_slot_id
from time_slot)) --将time_slot中的所有time_slot_id取出构成一个集合
begin --执行回滚操作
rollback
end;例2，使用触发器来确保在删除被参照关系中的元组时，time_slot_id的参照完整性，即在参照关系section中未被引用
create trigger timeslot_check2 after delete on time_slot --对表time_slot执行删除时触发
referencing old row as orow --引用“旧”行，即被删除的行，命别名为orow
for each row
when(
orow.time_slot_id not in --再确认一遍已删除行的time_slot_id已经不存在
(select time_slot_id
from time_slot)
and orow.time_slot_id in --且在section中仍有time_slot_id的引用
(select time_slot_id
from section))
begin
rollback
end;
例3，针对update的触发器可以指定具体修改的属性
/*针对grade的update触发器*/
create trigger takes_trigger after update of takes on grade
/*引用修改前的元组*/
refercing old row as orow
/*引用修改后的元组*/
refercing new row as nrow语句级触发器：
外部动作：
例1，假设仓库存在如下关系
create trigger reorder_trigger after update of level on inventory --针对表inventory中level的更新触发器
referencing old row as orow,new row as nrow --更新前的库存量和更新后的库存量
for each row
when nrow.level <= --当更新后的库存量不大于报警线
(select level
from minlevel
where minlevel.item=nrow.item)
and orow.level > --且更新前的库存量未达到报警线
(select level
from minlevel
where minlevel.item=orow.item)
begin
insert into orders --则执行添加订单操作
(select item,amount
from reorder
where reorder.item=orow.item)
end其他：
6.1.3 数据安全性
安全性机制：
对数据的授权：
对表的授权：
权限的授予：
要求：
授权语法：
grant <权限列表>
on <关系名或视图名> to <用户列表> --<用户列表>：用户ID、public(所有合法用户)、角色
/*应注意：
1. 授予对视图的权限并不意味着授予对定义该视图的基础关系的权限
2. 权限的授予者本身必须拥有相应的权限
*/例1，授予用户U、U、U对instructor关系的select权限
grant select on instructor to U1,U2,U3
/*
类似的：
insert:允许插入元组
update:允许修改元组
delete:允许删除元组
references:创建关系时允许声明外键
all privileges:所有权限
*/例2，授予U对instruct的select权限并允许U将此权限授予其他用户
/*with grant option:允许用户把被授予的权限再转授给其他用户*/
grant select on instructor to U1 with grant option角色：
例1
create role instructor; --创建角色命名为instructor
grant select on takes to instructor --授予角色instructor对表takes的select权限
grant dean to Amit; --把角色dean赋予给特定用户Amit
create role dean; --创建角色命名为dean
grant instructor to dean; --将角色instructor赋予给角色dean
grant dean to Satoshi; --将角色dean赋予给用户Satoshi权限的回收：
revoke<权限列表> on <关系名或视图名> --权限列表如果是all的话表示所有权限
from <用户列表> [restrict|cascade] --restrict表示不进行级联回收，cascade表示进行级联回收例，
revoke select on instructor from U1,U2,U3 cascade --收回U1,U2,U3对表instructor的select权限并收回它们授予出去的select权限,即级联收回
授予的局限性：
审计跟踪（autid trail）：
Oracle中的审计：
语句审计：
audit <语句类型> [by <用户>] --语句类型：table、view、role...，用户缺省表示对所有用户审计
[by session | access] --by session:相同类型的语句仅记录一次，access:记录所有操作
[whenever successful | whenever not successful] --只有当成功或者不成功时才会被记录进日志例，
/*审计用户scott每次成功执行的有关table的语句*/
audit table by scott --对用户scott进行table类型操作的审计
by access --记录所有语句的操作
whenever successful --仅当成功时记录
/*取消审计*/
noaudit ... 对象（实体）审计：
audit <对象类型> on <指定的对象表、视图名> | default --对象类型：insert、delete、update...
[by session | by access]
[whenever successful|whenever not successful]例，
/*审计所有用户对student表的delete和update操作*/
audit delete,update on student;
/*取消审计*/
noaudit(完)