实验五 数据库完整性

 

【实验目的】

    1、掌握实体完整性约束、参照完整性约束

    2、灵活应用自定义完整性约束

    3、了解触发器在自定义完整性中的应用

 

【实验内容】

一、定义一个教师表Teacher（Tno,TName,TSex,TAge,TSal,TJob,TDept）
要求：
    1、性别只能是‘男’或‘女’
    2、Tno为主码，名字不能为空，且不能重复。
    3、年龄在18在60岁之间
    4、教授工资最低为5000（触发器完成）
    5、所在系Tdept，为外码，参照于DEPT表（注：DEPT表需要预先定义，当DEPT表删除元组时，相对应Teacher元组拒绝删除；修改时,相对应Teacher元组同时修改）。

二、定义记录表Record（RNo,TNo,ROldSal,RNewSal,RUser,RTime）
要求：
    1、RNo从1开始，每次加1为。主码
    2、TNo为外码，参照Teacher表。

     第一题和第二题参考：

    

view plain print ?

1. use sc     --打开sc数据库  
2. go  
3.   
4. /*-------------创建 系表-----------------------------*/  
5. create table Dept  
6. (TDept char(10) primary key);  
7. go  
8.   
9. /*-------------创建 教师表---------------------------*/  
10. create table Teacher  
11. (TNo char(10) primary key,  
12. TName char(10) not null,  
13. TSex char(2)check(TSex in('男','女')),  
14. TAge smallint check(TAge>=18 and TAge<=60),  
15. TSal numeric(7,2),  
16. TJob varchar(10),  
17. TDept char(10),  
18. foreign key (TDept) references Dept(TDept)on delete no action   on update cascade);    
19. go     
20.   
21. /*-------------创建 记录表---------------------------*/  
22. create table Record  
23. (RNo  int  identity (1,1) primary key,  
24. TNo char(10) references Teacher(TNo),  
25. ROldSal numeric(7,2),  
26. RNewSal numeric(7,2),RUser char(10), RTime Datetime);  
27. go   
28.   
29.   
30. /*-----定义触发器 教授工资不低于5000-------------------*/   
31. create trigger Teacher_JiaoShou_MINSal   
32. on  Teacher   
33.   
34. for Insert,Update as   
35.   
36. set Nocount off  
37. /*---------定义变量----------------------------------*/  
38. declare   
39.  @TNo char(10),  
40.  @NewSal numeric(7,2),  
41.  @Tjob char(10)  
42. begin  
43.  /*---------获得变量值--------------------------------*/  
44.  select @TNo=TNo,  
45.  @NewSal=TSal,  
46.  @Tjob=Tjob   
47.  from inserted   
48.   
49.  /*---------判断是否是教授，且工资小于5000---------------*/  
50.  if (((@NewSal-5000)<0) and (@Tjob='教授'))  
51.   begin  
52.    /*---------修改该员工（教授Tno）表-------------------*/  
53.    update Teacher   
54.    set Tsal=5000  
55.    where Tno=@Tno  
56.   end  
57. end  
58. go  

 

三、定义触发器与触发器的执行
    1、定义触发器Insert_Teacher,当插入Teacher一行元组时，会将该元组信息如：教师编号TNo，工资等信息，自动插入到表Record中（ROldSal为0）。

    参考代码：

   

view plain print ?

1. /*-----插入Teacher触发器-----------------*/   
2. create trigger Insert_Teacher   
3. on  Teacher   
4.   
5. for Insert as   
6.   
7. set Nocount off  
8. /*---------定义变量----------------------*/  
9. declare   
10.  @TNo char(10),  
11.  @RNewSal numeric(7,2)  
12. begin  
13.  /*---------获得变量值----------------------*/  
14.  select @TNoTNo=TNo,  
15.  @RNewSal=TSal   
16.  from inserted   
17.  /*---------插入要记录的数据到Record表----------------------*/  
18.  insert into Record(TNo,ROldSal,RNewSal,RUser,RTime) values(@TNo,0,@RNewSal,current_user,current_timestamp)  
19. end  

 

    测试数据

   

view plain print ?

1. insert into dept  
2. values('信息工程系')  
3. insert into teacher  
4. values ('2008001','wang','男',34,2000,'讲师','信息工程系')  
5. select * from teacher  

 

    2、定义触发器Update_Teacher,每当修改Teacher数据，且工资发生变动时，会自动记录工资变动情况到Record表。

    参考代码：

   

view plain print ?

1. /*-----更新Teacher触发器-----------------*/   
2. create trigger Update_Teacher   
3. on  Teacher   
4.   
5. for update as   
6.   
7. set Nocount off  
8. /*---------定义变量----------------------*/  
9. declare   
10.  @TNo char(10),  
11.  @ROldSal numeric(7,2),  
12.  @RNewSal numeric(7,2)  
13.   
14. begin  
15.  /*---------获得变量值----------------------*/  
16.  select @TNoTNo=TNo,  
17.  @ROldSal=TSal  
18.  from deleted   
19.   
20.  select @TNoTNo=TNo,  
21.  @RNewSal=TSal   
22.  from inserted   
23.  /*---------插入要记录的数据到Record表----------------------*/  
24.  insert into Record(TNo,ROldSal,RNewSal,RUser,RTime) values(@TNo,@ROldSal,@RNewSal,current_user,current_timestamp)  
25. end  

 

    测试代码：

   

view plain print ?

1. update teacher  
2. set tsal=3000  
3. where tno='2008001'  
4.   
5. select * from record  

 

    3、定义触发器Delete_Teacher,当删除Teacher数据时，会自动记录删除数据情况到Delete_teacher表。即记录教师所有信息和用户及删除时间。（自己完成）

 

    4、完成以上触发器的测试。

    说明：

    1）能够触发Insert_Teacher触发器的，有对Teacher表进行Insert操作和Update操作。

    2）能够触发Delete_Teacher触发器的，有对Teacher表进行Delete操作和Update操作。

    3）能够触发Update_Teacher触发器的，只有有对Teacher表进行Update操作。

 

【课外实验】

    一、建立如下关系表

         工程表（工程号，工程名，开工日期，竣工日期，状态，城市，项目经理号）

         职工表（职工号，姓名，年龄，籍贯，职称，基本工资）

         岗位表（岗位号，岗位名）

         工资表（序号，职工号，月份，绩效工资，奖金）

    要求：1、籍贯只记城市。

            2、正确确定四个表的主码

            3、通过外码，正确把握关系间的联系

            4、工程与职工具有多对多的联系，建立一个工程_职工表（工程号，职工号，岗位号，入职时间，状态） 

            5、工程表的状态有两种状态（0：未完工，1：已完工）

                工程_职工表的状态有两种（0：在职，2：已离职） 

            6、工资表的月份只能取1-12，奖金只能取100的倍数 

            7、职工表的年龄取值范围是18-60；职称取值为：初级、中级、高级；要求所有人员基本工资不低于800，高级人员的基本工资不低于4500.

           

    二、创建视图工资视图（序号，职工号，月份，基本工资，绩效工资，奖金，五险一金，应发，个人所得税，实发）

     三、是否能删除工程表的工程