数据库完整性代码

1、4 实验报告数据库原理实验报告实验题目:数据完整性姓名吴位吕日期2012-5-22实验环境：XP Windows系统环境Microsoft SQL Server 2005 中  SQL Server Management Studio实验内容与完成情况：一、三类完整性的实现对系表Dept、学生表Student、教师表Teacher、课程表Course、SC表，教师授课表TC，实现规定的完整性，并验证，当操作违反了完整性约束条件时，DBMS如何处理。-1、Dept表-?DEPTNO CHAR（）-?E VARCHAR（）DNAM-?实体完整性：DEPTNO为主码；-?用户定义完整性-D

2、EPTNO的取值为至；-DNAME取值非空且唯一；CREATE TABLE DEPT(DEPTNO CHAR(2)PRIMARY KEYconstraint c1 check(DEPTNO between 01 and 99),DNAME VARCHAR(20) constraint c2 NOT NULL unique,);-2、Student表-l SNO CHAR（）-l SNAME VARCHAR（）-l SEX CHAR（）-l DEPTNO CHAR（）-² 实体完整性：SNO为主码；-² 参照完整性：DEPTNO为外码，并实现级联更新和级联删除操作；-

3、8; 用户定义完整性-SNO取值为级的学号取值范围，如等。-SNAME 取值非空；-SEX的取值只能为男或女；CREATE TABLE Student(SNO CHAR(10)PRIMARY KEYconstraint b1 check(SNO between 2010080000 and 2010089999),Sname varCHAR(8) NOT NULL unique,Ssex CHAR(2) constraint b2 check(Ssex in('男','女'),DEPTNO CHAR(2),FOREIGN KEY (DEPTNO)REFERENC

4、ES DEPT(DEPTNO)on delete cascadeon update cascade);drop table Student-3、Teacher表-l TNO CHAR（）-l TNAME VARCHAR（）-l DEPTNO CHAR（）-² 实体完整性：TNO为主码；-² 参照完整性：DEPTNO为外码，并实现级联更新和级联删除操作；-² 用户定义完整性-TNO取值为开头的四位数字字符串；-TNAME取值非空；CREATE TABLE Teacher(TNO CHAR(3)PRIMARY KEYconstraint a1 check(TNO be

5、tween 0800 and 0899),TNAME varCHAR(8) NOT NULL ,DEPTNO CHAR(2),FOREIGN KEY (DEPTNO)REFERENCES DEPT(DEPTNO)on delete cascadeon update cascade);drop table Teacher-4、Course表-l CNO CHAR（）-l TNO CHAR（）-l CNAME VARCHAR（）-l CREDIT SNALLINT-² 实体完整性：CNO；-² 用户定义完整性-CNAME取值非空；-CREDIT取值为至以内的数字；-²

6、 参照完整性：TNO为外码，并实现级联更新和级联删除操作；CREATE TABLE Course(CNO CHAR(4),TNO CHAR(3),CNAME CHAR(20) NOT NULL,CREDIT INT check(CREDIT>=1 and CREDIT<=10),PRIMARY KEY(CNO),FOREIGN KEY (TNO)REFERENCES Teacher(TNO)on delete cascadeon update cascade,);drop table Course-5、SC表-l SNO CHAR（）-l CNO CHAR（）-l GRADE SM

7、ALLINT-² 实体完整性：(SNO，CNO)为主码；-² 参照完整性：SNO外码、CNO外码，都实现级联删除和级联更新操作；-² 用户定义完整性- GRADE取值在到之间。CREATE TABLE SC(SNO CHAR(10),CNO CHAR(4),GRADE SMALLINT check(GRADE>=0 and GRADE<=100),PRIMARY KEY(SNO,CNO),FOREIGN KEY (SNO)REFERENCES Student(SNO)on delete cascadeon update cascade,FOREIGN

8、KEY (CNO)REFERENCES Course(CNO)on delete no actionon update no action,);-二、触发器的实现-1、修改Student表，增加属性CNT，SMALLINT型，表示学生的选课门数，设置CNT的默认值为。-增加属性SZK，SMALLINT型，表示素质选修课学分。ALTER TABLE Student ADD CNT SMALLINT;ALTER TABLE Student ADD SZK SMALLINT;-在Student表上创建以下触发器。-（）创建触发器Trig1，实现在SC表里添加一条数据时，Student表里对应的学生的

9、CNT值自动增加。CREATE TRIGGER TRIG1 ON SCFOR INSERT ASUPDATE STUDENT SET CNT=CNT+1WHERE STUDENT.SNO IN(SELECT SNO FROM INSERTED )-（）创建触发器Trig2，实现在SC表里删除一条数据时，Student表里对应的学生的CNT值自动减。CREATE TRIGGER TRIG2 ON SCFOR DELETEASupdate student SET CNT=CNT-1WHERE STUDENT.SNO IN(SELECT DELETED.SNO FROM DELETED )-（）创建

10、触发器Trig3，实现不允许修改SC表的Sno和Cno。create trigger tirg3on scfor updateas if update(sno) or update(cno) rollback transaction-（）SZK取值不能大于，如果大于，自动修改为create trigger tirg4on studentfor insert,updateas declare i char(8)select i=szk from insertedif ( i>8) update student set szk=8 where sno in(select sno from i

11、nserted)drop trigger id-2、修改Teacher表，增加属性ZC，char(5)，表示教师的职称，增加属性GZ，money，表示教师的工资。-在Teacher表上创建触发器，实现当职称为副教授时，GZ如果低于，则自动修改为。ALTER TABLE Teacher ADD ZC char(5);ALTER TABLE Teacher ADD GZ money;create trigger tirg5on Teacherfor insert,updateas declare j char(5)declare k moneyselect j=ZC from insertedselect k=GZ from insertedif ( j='副教授' and k<3000)begin update Teacher set GZ=4000 where ZC='副教授' and GZ<3000end出现的问题：在开始的时候，用户定义完整性和参照完整性：SNO外码、CNO外码中，要都实