第五章数据库完整性例题

数据库的完整性

1. 数据的正确性：数据是符合现实世界语义，反映了当前实际状况的
2. 数据的相容性：同一对象在不同关系表中的数据是符合逻辑的

数据的完整性
防范对象：不合语义的、不正确的数据
数据的安全性
防范对象：非法用户和非法操作

一、实体完整性

1 实体完整性定义

[例5.1] 将Student表中的Sno属性定义为码

--方式一：在列级定义主码
create table Student
(Sno char(20) primary key, --在列级定义主码
 Sname char(10) not null,
 Ssex char(2),
 Sage int,
 Sdept char(5)
);
-- 方式二：在表级定义主码
create table Student
(Sno char(20),
 Sname char(10) not null,
 Ssex char(2),
 Sage int,
 Sdept char(5),
 primary key(Sno)
);

例2：将SC表中的Sno、Cno属性定义为主码

create table SC
(Sno char(20),
 Cno char(4),
 Grade int,
 primary key(Sno,Cno), -- 在表级定义主码，主码中包含多个属性
);

2.实体完整性检查和违约处理

插入或更新操作时，DBMS按照实体完整性规则自动进行检查。

1. 检查主码值是否唯一，如果不唯一则拒绝插入或修改。
2. 检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改
3. 借用之前的实验数据，填充Student 表
   
   学号是主键，重复了。出入被禁止

insert into Student values('201215121','李强','男',20,'CS');

填充SC表

当主码中的属性是重复的时候也不被允许，违反了主键约束

insert into SC values ('201215121','2',95)

二、参照完整性

1.定义参照完整性

关系模型的参照完整性定义

1. 在CREATE TABLE中用FOREIGN KEY短语定义哪些列为外码
2. 用REFERENCES短语指明这些外码参照哪些表的主码

[例5.3]定义SC中的参照完整性

create table SC
(Sno char(20),
 Cno char(4),
 Grade int,
 primary key(Sno,Cno), -- 在表级定义实体完整性
 foreign key(Sno) references Student(Sno),-- 在表级定义参照完整性
 foreign key(Cno) references Course(Cno) -- 在表级定义参照完整性
);

可能破坏参照完整性的情况及违约处理
[例5.4] 显式说明参照完整性的违约处理示例

create table SC
(Sno char(20),
 Cno char(4),
 Grade int,
 primary key(Sno,Cno),
 foreign key(Sno) references Student(Sno)
 on delete cascade -- 级联删除SC表中相应的元组
 on update cascade, -- 级联更新SC表中相应的元组
 foreign key(Cno) references Course(Cno)
 on delete no action -- 当删除Course表中的元组造成与SC表不一致时，拒绝删除
 on update cascade -- 当更新Course表中的Cno时，级联更新SC表中相应的元组
 );

三、用户自定义的完整性

用户定义的完整性是：针对某一具体应用的数据必须满足的语义要求

属性上的约束条件

CREATE TABLE时定义属性上的约束条件

1. 列值非空（NOT NULL）
2. 列值唯一（UNIQUE）
3. 检查列值是否满足一个条件表达式（CHECK）

[例5.5] 在定义SC表时，说明Sno、Cno、Grade属性不允许取空值。

create table SC
(Sno char(20),
 Cno char(4),
 Grade int ,
 primary key(Sno,Cno), -- 在表级定义实体完整性，隐含了Sno，Cno不允许取空值，在列级不允许取空值的定义可不写
);

[例5.6]建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码

create table DEPT
(
Deptno numeric(2),
Dname char(9) unique not null,
Location char(10),
primary key(Deptno)
);

[例5.7] Student表的Ssex只允许取“男”或“女”。

create table Student
(
Sno char(20) primary key,
Sname char(10) not null,
Ssex char(2) check (Ssex in('男','女')), --性别属性只能取男或女
Sage int,
Sdept char(5)
);

[例5.8] SC表的Grade的值应该在0和100之间。

create table SC
(
Sno char(20),
Cno char(4),
Grade int check(Grade>=0 and Grade<=100),
primary key(Sno,Cno),
foreign key(Sno)references Student(Sno),
foreign key(Cno)references Course(Cno)
);

属性上的约束条件检查和违约处理

1. 插入元组或修改属性的值时，关系数据库管理系统检查属性上的约束条件是否被满足
2. 如果不满足则操作被拒绝执行

2 元组上的约束条件

在CREATE TABLE时可以用CHECK短语定义元组上的约束条件，即元组级的限制

[例5.9]当学生的性别是男时，其名字不能以Ms.打头。

create table Student
	(
	Sno    char(9), 
	Sname  char(8) not null,
	Ssex   char(2),
	Sage   smallint,
	Sdept  char(20),
	primary key (Sno),
	check (Ssex='女' or Sname not like 'Ms.%')--定义了元组中Sname和 Ssex两个属性值之间的约束条件
	);

元组上的约束条件检查和违约处理和属性上的类似

四、 完整性约束命名子句

优点是方便在建立表之后的对约束条件进行增删改

1.完整性约束命名子句

CONSTRAINT <完整性约束条件名><完整性约束条件>

<完整性约束条件>包括NOT NULL、UNIQUE、PRIMARY KEY短语、FOREIGN KEY短语、CHECK短语等

[例5.10]建立学生登记表Student，要求学号在90000~99999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”。

create table Student
	(
	Sno  numeric(6)
	constraint C1 check (Sno between 90000 and 99999),
	Sname  char(20)  
	constraint C2 NOT NULL,
	Sage  numeric(3)
	constraint C3 check (Sage < 30),
	Ssex  char(2)
	constraint C4 check(Ssex in( '男','女')),
	constraint StudentKey primary key(Sno)
	);

[例5.11]建立教师表TEACHER，要求每个教师的应发工资不低于3000元。应发工资是工资列Sal与扣除项Deduct之和。

create table TEACHER
	(
	Eno    numeric(4)  primary key,   /*在列级定义主码*/
	Ename  char(10),
	Job    char(8),
	Sal    numeric(7,2),
	Deduct numeric(7,2),
	Deptno numeric(2),
	constraint TEACHERFKry foreign key (Deptno) 	references DEPT(Deptno),
	constraint C1 check(Sal + Deduct >= 3000) --应发工资是工资列Sal与扣除项Deduct之和。
	);

2. 修改表中的完整性限制

[例5.12]去掉例5.10 Student表中对性别的限制。

alter table Student 
  drop constraint C4;

[例5.13] 修改表Student中的约束条件，要求学号改为在900000~999999之间，年龄由小于30改为小于40

alter table Student
drop constraint C1;
alter table Student
add constraint C1 check(Sno between 900000 and 999999);

alter table Student
drop constraint C3;
alter table Student
add constraint C3 check(Sage<40);
--这里都是先删除再增加以达到改变的目的

五、 断言

SQL中，可以使用 CREATE ASSERTION语句，通过声明性断言来指定更具一般性的约束。

1. 可以定义比较复杂的完整性约束。
2. 断言创建以后，任何对断言中所涉及的关系的操作都会触发DBMS对断言的检查，任何使断言不为真值的操作都会被拒绝执行

创建断言的语句格式

CREATE ASSERTION<断言名><CHECK 子句>

[例5.18] 限制数据库课程最多60名学生选修

create assertion ASSE_SC_DB_NUM
	check (60 >= (select count(*)
                 from Course,SC
    		     where SC.Cno=Course.Cno and 					
				 Course.Cname ='数据库')
			   	);

[例5.19]限制每一门课程最多60名学生选修

create assertion ASSE_SC_CNUM1
check(60 >= all 
       (select count(*) 		   				         
		from SC 
	    group by cno)
        );

2.删除断言的语句格式

drop assertion<断言名>

六、触发器

1、定义触发器

CREATE TRIGGER语法格式
CREATE TRIGGER <触发器名>  
{BEFORE | AFTER} <触发事件> ON <表名>
REFERENCING NEW|OLD ROW AS<变量>
FOR EACH  {ROW | STATEMENT}
[WHEN <触发条件>]<触发动作体>

行级触发器（FOR EACH ROW）
语句级触发器（FOR EACH STATEMENT）

2.触发事件

[例5.21]当对表SC的Grade属性进行修改时，若分数增加了10%则将此次操作记录到下面表中：

标准SQL

create trigger SC_T
after update of Grade on SC
referencing 
 old row as OldTuple,
 new row as NewTuple
for each row
when (NewTuple.Grade>=1.1*OldTuple.Grade)
insert into SC_U(Sno,Cno,OldGrade,NewGrade)
values(OldTuple.Sno,OldTuple.Cno,OldTuple.Grade,NewTuple.Grade);

TSQL
需要先声明变量，实现声明好需要插入的新变量，用到 @符号声明变量，用insert 和 deleted来区分更新前后的数据，还需要用到begin和end

create trigger SC_T
on SC
after update
as
begin 
	declare @old int,@new int,@sno char(15),@cno char(10)
	if(update(Grade))
	begin
	select @old=Grade from deleted
	select @new=Grade from inserted
	select @sno=Sno from inserted
	select @cno=Cno from inserted   
	if(@new>=1.1*@old)
		insert into SC_U(Sno,Cno,Old,New)  
		values(@sno,@cno,@old,@new)
	end
end;

检验测试样例

update SC
set Grade=50
where Sno='201215121' and Cno='2';
select  * from SC_U

[例5.22] 将每次对表Student的插入操作所增加的学生个数记录到表StudentInsertLog中。

标准SQL

create trigger Student_Count
after insert on Student
 new table as Delta
 for each statement
 insert into Studentinsertlog(Numbers)
 select count(*) from delta;

TSQL

create table StudentInsertLog(
Numbers int
);
--分开写
 
create trigger Student_Count
on Student
after insert
as insert into StudentInsertLog(Numbers)
select count(*) from Student;

[例5.23] 定义一个BEFORE行级触发器，为教师表Teacher定义完整性规则“教授的工资不得低于4000元，如果低于4000元，自动改为4000元”。

create trigger insert_or_Update_Sal
before insert or update on Teacher
for each row
begin 
if(new.Job='教授')and(new.sal<4000)
then new.Sal :=4000;
end if;
end;

4.激活触发器

触发器的执行，是由触发事件激活的，并由数据库服务器自动执行！

一个数据表上可能定义了多个触发器，遵循如下的执行顺序:

（1） 执行该表上的BEFORE触发器;

（2） 激活触发器的SQL语句;

（3） 执行该表上的AFTER触发器

5.删除触发器

DROP TRIGGER <触发器名> ON <表名>;

七、存储过程和函数

存储过程：由过程化SQL语句，经编译和优化后存储在数据库服务器中，可以被反复调用，运行速度较快。

优点：

（1）运行效率高

（2）降低了客户机和服务器之间的通信量

（3）方便实施企业规则

1、创建存储过程

CREATE OR REPLACE PROCEDURE 过程名([参数1,参数2,...]) AS <过程化SQL块>；

[例8.8] 利用存储过程来实现下面的应用：从账户1转指定数额的款项到账户2中。

create procedure Proc_TRANSFER (
--定义存储过程为 transfer
@inAccount int,
@outAccount int,
@amount float)
--形参
as
begin transaction trans
declare
@totalDepositOut float,--定义变量
@totalDepositin float,
@inAccountum int;
 
select @totalDepositOut=total from Account where accountnum = @outAccount;
if @totalDepositOut is null --1.如果转出账户不存在或账户中没有存款
begin
    print '转出账户不存在或者账户余额为0'
rollback transaction trans; --回滚事务
return
end ;
if @totalDepositOut < @amount --2.账户余额不足
begin
 print '账户余额不足'
rollback transaction trans;
return;
end 
select @inAccount =accountnum from Account
where accountnum = @inAccount;
if @inAccount is null
begin
print '转入账户不存在'
rollback transaction trans;
return ;
end ;
begin
update Account set total =total-@amount
where accoutnum =@outAccount;
update account set total = total+@amount
where accountnum =@inAccount;
print '转账成功'
commit transaction trans;
return ;
end;

2、执行存储过程

[例8.9] 从账户01003815868转10000元到01003813828账户中。

call procedure
transfer (01003813828,01003815868,10000);

TSQL

exec	Proc_TRANSFER
@inAccount = 01003813828,	--转入账户
@outAccount = 01003815868,	--转出账户
@amount = 10000     --转出金额

select * from Account;

3、修改存储过程

alter procedure 过程名1  rename to  过程名2;

4、删除存储过程

drop procedure 过程名()；