第六章 数据库完整性和触发器

数据库完整性

数据库完整性概述

数据完整性是DBMS应保证的DB的一种特性

在任何情况下的正确性, 有效性和一致性

广义完整性

• 语义完整性狭义完整性
• 并发控制
• 安全控制
• DB故障恢复

狭义完整性

• 实体完整性
  主键不能取NULL
• 参照完整性
  外键只能取NULL或对应表的主键中的值
• 用户自定义完整性

为什么会引发数据库完整性的问题

不正当的数据库操作, 如:

1. 输入错误
2. 操作失误
3. 程序处理失误

数据库完整性管理的作用

1. 防止和避免数据库中不合理数据的出现
2. DBMS应尽可能自动防止DB中语义不合理现象

数据库完整性的类型

按约束对象分类

• 域完整性约束条件
  施加于某一列, 例如Sage > 15 and Sage < 20
• 关系完整性约束条件
  施加于关系(表)上, 涉及多列, 且可以跨表 例如 Chours/Credit < 7

按约束来源分类

• 结构约束

  来自于模型的约束, 只关系数值是否相等, 是否允许为空等, 而不关注具体的数值. 例如:

  • 函数依赖约束
  • 主键约束(实体完整性约束)
  • 外键约束(参照完整性约束)

• 内容约束

  来自于用户的约束, 对元组或属性的取值范围进行约束. 例如:

  Sage > 14 and Sage < 20

按约束状态分类

• 静态约束
  要求DB在任意时候均满足的约束. 例如: Sage > 0 and Sage < 150
• 动态约束
  要求DB从某一状态变为另一状态时应满足的约束. 例如: 工资只能升, 不能降

完整性约束条件的一般形式

$$\begin{gathered} IntegrityConstraint::=(O,P,A,R) \\ O:数据集合,约束的对象(列,多列元组,元组集合) \\ P:谓词条件,什么样的约束 \\ A:触发条件,什么时候检查 \\ R:响应动作,不满足时怎么办 \end{gathered}$$

含义:对于对象O, 当A发生的时候, 去检查是否满足P, 当不满足P条件时, 去执行R操作

SQL语言实现数据库的静态完整性

列约束

列约束紧跟在定义的变量后面

#not null				非空约束
#unique 				唯一性约束
#foreign key reference	外键约束
#primary key 			主键约束
#check					检查约束
#default				默认值约束
#删除约束实质是删除索引,因此使用drop index 约束名/索引名
#主键名总是为PRIMARY,自定义约束名没有用

create table student(
    #not null只能作为列约束
    #unique 唯一性约束,可以有多个null值,和主键约束还是有些区别 not null unique可以看作是主键约束
	Sno varchar(8) not null unique,
    Sname varchar(10),
    
    #定义约束名为ctssex
    Ssex varchar(2) constraint ctssex check(Ssex = '男' or Ssex = '女'), 
    
    #check自定义检查约束
    Sage integer check(Sage >= 1 and Sage < 150), 
    
    #student表中的Dno属性是外键, 对应Dept表中的Did主键, 且设置关联删除
    Dno varchar(2) references Dept(Did) on delete cascade, 
    
    #约束等级:cascade,set null,no action(默认情况)
    #on delete cascade  当Dept表中有元组被删除时, student表中也会删除相应的元组
    #on delete set null 关联置null
    #on update cascase  也可以是on update时执行操作
    #约束等级为no action时, 不能够直接删除父表的数据,需要先删除子表的数据
    Sclass varchar(6)
)

表约束

表约束统一写在表的末尾

create table student(
	Sno varchar(8),
    Sname varchar(10),
    Ssex varchar(2),
    Sage integer, 
    Dno varchar(2),
    Sclass varchar(6),
    #定义(Sno,Sname)为主键约束, 主键应该是极小的超键, 这里假设Sno不唯一, 而(Sno,Sname)唯一
    #Sno,Sname都不能是NULL
    primary key(Sno, Sname), 
    #定义(Dno, Sclass)为外键约束
    foreign key(Dno,Sclass) references Dept(Did, Sclass) on delete cascade,
    #定义约束名为ctssex,且设置自定义约束
    constraint cts check((Ssex = '男' or Ssex = '女') and (Sage >= 1 and Sage < 150));
)

断言

语法: create assertion <断言名> check(<where子句>)

当一个断言创建后, 系统将检测器有效性, 并在每一次更新中测试更新是否违反该断言.

由于断言的消耗大, 所以不推荐使用

SQL语言实现数据库的动态完整性——触发器

定义触发器

语法: create trigger <触发器名> <事件> on <表名> [定义变量] [触发器的检查范围] 检查条件 [执行程序]

1. <事件>: <参数一> <参数二> [参数三]

   • 参数一: before | after

   • 参数二: insert | delete | update

   • 参数三: [of <列名>]

2. [定义变量]: [referencing corr_name_def, …]
   定义检查条件和执行程序中的变量

3. [检查范围]: [for each row | for each statement]

5. [执行程序]: begin atomic 语句1; 语句2; … ; end

before和after的含义

• before和after都是指事务提交后的一段时间

• before是还未写入磁盘, 而after是写入磁盘之后

触发器设计示例

1. 设计一个触发器, 当进行teacher表更新元组时, 使其工资只能升不能降

   DELIMITER $
   create trigger teacher_chgsal 
       #更新操作update:相当于先删delete,后插入insert
       before update of salary on teacher
       for each row
       
       begin	
           #new表示新添加的那条数据,old表示即将删除的那条数据
   
           #定义一个DOUBLE型的变量mgr_sal
           #DECLARE mgr_sal DOUBLE;
   
           #如果更新后的工资x.salary < 更新前的工资y.salary,报错
           IF new.salary < old.salary 
               #给出自定义的错误信息
               THEN SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '工资不能降低';
           END IF;
       end $
   DELIMITER ;
   

2. 假设student(Sno, Sname, SumCourse), 其中SumCourse为该同学已经学习的课程数, 初始值为0, 以后每选修一门课程都要对其增加1. 设计一个触发器自动完成上述功能

   create trigger sumc 		#触发器名称为sumc
   	after insert			#插入事务写入数据库之后
   	on sc					#针对sc选课表
   	referencing new newi	#只考虑新数据new
   	for each row			#对每一行
   	
   	begin					#when条件省略, 即恒成立
   		update student		#student表中的更新操作
   		set SumCourse = SumCourse + 1 #更新操作的具体细节
   			where Sno =: newi.Sno;
   	end;
   

3. 假设student(Sno, Sname, Sage, Ssex, Sclass)中某一学生要变更其主码Sno的值, 如从原来的98030101变更为99030131. 此时sc选课表中该同学已选课记录的Sno也需要自动随其改变. 设计一个触发器完成该功能

   create trigger updSno
   	after update of Sno
   	on student
   	referencing old oldi, new newi
   	for each row
   	begin
   		update sc 				#sc表中的更新操作
   		set Sno = newi.Sno		#更新操作的具体细节
   		where Sno = oldi.Sno;
   	end;
   

4. 假设student(Sno, Sname, SumCourse), 当删除某一同学Sno时, 该同学的所有选课也都要删除. 设计一个触发器完成上述功能

   create trigger delSno
   	after delete
   	on student
   	referencing old oldi
   	for each row
   	begin
       	delete sc				#sc表中的删除操作
       	where Sno = oldi.Sno;
   	end;
   

5. 假设student(Sno, Sname, SumCourse), 当删除某一同学Sno时, 该同学的所有选课记录都要置为NULL. 设计一个触发器完成上述功能

   create trigger updSno
   	after delete
   	on student
   	referencing old oldi
   	for each row
   	begin
   		update sc
   		set Sno = NULL
   		where Sno = oldi.Sno;
   	end;
   

6. 假设Dept(Dno, Dname, Dean), 而Dean一定是该系的教师Teacher(Tno, Tname, Dno, Salary)中工资最高的教师. 设计一个触发器完成上述功能(更新Dean时触发)

   # todo
   

数据库安全性

DBMS的安全机制

1. 自主安全性机制: 存取控制
   用户之间可以传递权限

2. 强制安全性机制

   • 数据强制分类
   • 用户强制分类
   • 不同类别的用户只能访问相应权限的数据

3. 推断控制机制

4. 数据加密存储机制

自主安全性机制

自主安全性访问规则

AccessRule ::= (S, O, t, P)

S: 请求主体(用户)

O: 访问对象

t: 访问权限(创建,增,删,改,查)

P: 谓词

含义: S这个用户, 对O这个对象, 在满足P的条件下, 拥有t这种权限

自主安全性的实现方式

存储矩阵: SOt

自主安全性控制示例

1. 对于Employee(Pno, Pname, Page, Psex, Psalary, Dno, Head)有如下的安全性访问要求:

   • 员工管理人员: 能够访问该数据库的所有内容, 便于维护员工信息(读, 增删改)
   • 收发人员: 访问该数据库以确认某员工是哪一个部门, 只能访问基本信息, 不允许访问其他信息
   • 每个员工: 允许访问关于自己的记录, 但不能修改
   • 部门领导: 能够查询其所领导的部门下的人员的所有情况
   • 高层领导: 能够访问该数据库的所有内容, 但只能读

   S	O	t	P
   员工管理人员	Employee	读, 增, 删, 改
   收发人员	Employee(Pname, Dno)	读
   每个员工	Employee	读	Pno = UserId
   部门领导	Employee	读
   高层领导	Employee	读	Head = UserId