实验九 数据库完整性实验（V2.0）

实验九   数据库完整性

 

一、实验目的：

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，对修改或删除的学生元组对应的宿舍人数进行修改。