实验九 数据库完整性
一、实验目的:
1.掌握实体完整性的定义
2.掌握参照完整性的定义,级联(修改、删除)、拒绝和设置为空等定义方法。
3.掌握不能为空、唯一性和check等方式的自定义完整性。
4.掌握触发器的应用。(*)
二、实验内容
1、定义两个表
学生表(学号,姓名,性别,年龄,宿舍号)
宿舍表(编号,标准人数,实际人数)
2、要求:
1)学号为主码;姓名唯一,且不能为空;性别为“男”或“女”,年龄为12-30的整数;宿舍号为外码,参照宿舍表的主码(如果修改时,级联修改;如果删除时,拒绝);
2)编号为主码;标准人数和实际人数都为整数,标准人数默认为6,实际人数默认为0,都不能为空。
3)未说明数据类型的都为字符型。
说明:
1. check( )的作用是,当进行元组的插入、修改时检查check括号内提供的表达式是否为‘真’。如果不为真,拒绝操作。
2. 在定义学生表内宿舍号外码时,不仅仅做foreign key (sdom) references dom(dno)定义,还附有 on update cascade on delete no action详细的设置说明,当进行修改时(修改dom表内dno值)做级联修改(级联修改的是student表中,对应的宿舍号),当删除时(删除宿舍表的元组),拒绝删除(原因是,学生表内有学生住在对应的宿舍)。
3、设计测试数据,并验证。
1)实体完整性。
2)参照完整性。被参照不存在的;被参照的修改时,对外码的影响。被参照的删除时,如果有引用,则拒绝删除。
3)自定义完整性。null,unique,check( )
说明:
insert into dom(dno)
values('202')
insert into student
values('2012001002','王同学','男',20,'202')
update dom
set dno = '201'
where dno = '202'
执行以上操作,查看结果。发现先学生表有自动修改。
4、触发器的应用。
1)设计触发器Insert_Trigger,当插入一个学生元组时,如果宿舍号不为空,则对应的宿舍数据实际人数+1,但是不能超过标准人数。
参考示例代码:
create trigger dom_num2_in_tri
-- 处发器作用对象表
on student
-- 触发器激活条件
for insert as
-- 定义变量
Declare
@dno char(4), -- 宿舍号
@sno char(12),-- 学生号
@dnum1 int, -- 标准人数
@dnum2 int -- 已住人数
-- 主程序
begin
-- 宿舍号
select @dno=sdom,@sno = sno
from inserted /*inserted为刚插入的学生元组*/
if(@dno is not null)
begin
-- 查询标准人数和已住人数
select @dnum1 = dnum1, @dnum2 = dnum2
from dom
where dno = @dno
if(@dnum1>@dnum2)
begin -- 可以安排
-- 修改宿舍已住人数
update dom
set dnum2 = dnum2+1
where dno = @dno
end
else
begin -- 人数已满,不可以再安排
delete
from student
where sno = @sno
end
end
end
2)设计一个修改或删除学生元组时触发的一个触发器Update_Delete_Trigger,对修改或删除的学生元组对应的宿舍人数进行修改。