数据库原理 数据的完整性 实验报告.doc

*大学计算机科学与信息学院软件工程系上机实验报告数据库原理实验报告题目实验8 数据的完整性姓名*班级*日期*实验环境：SQL Server 2000一、实验目的熟悉通过SQL对数据进行完整性控制，通过实验加深对数据完整性的理解。n 相关知识触发器是实施复杂完整性的一种机制。触发器不需要专门的语句调用，当对它所保护数据进行修改时由DBMS自动激活，以防止对数据进行不正确，未授权或不一致的修改。创建触发器的语法为：CREATE TRIGGER ON WITH ENCRYPTIONFOR DELETE，INSERT，UPDATEWITH APPENDNOT FOR REPLICATIONAS 其中：1) WITH ENCRYPTION 为加密选项。2) DELETE 选项为创建DELETE 触发器。DELETE触发器的作用是当对表执行DELETE操作时触发器被激活，并从指定表中删除元组，同时将删除的元组放入一个特殊的逻辑表（delete表）中。触发器的动作可以检查delete表中的数据，以确定下一步该如何处理。3) INSERT选项为创建INSERT触发器。INSERT触发器在对指定表中执行插入数据操作时激活，激活后将插入表中的数据拷贝并送入一个特殊的逻辑表（inserted 表）中，触发器会根据INSERT表中的值决定如何处理。4) UPDATE选项为创建UPDATE触发器。UPDATE触发器仅在对指定表中进行更新数据操作时激活。UPDATE触发器激活后把将要被更新的原数据移入delete表中,再将要被更新后的新数据的备份送入inserted表中，UPDATE触发器对delete和inserted表进行检查，并决定如何处理。5) NOT FOR REPLICATION 选项说明当一个复制过程在修改一个触发器表时，与该表相关联的触发器不能被执行。一个触发器只适用于一个表，每个表最多只能有三个触发器，它们分别是INSERT、UPDATE和DELETE触发器。触发器仅在实施数据完整性和处理业务规则时使用。二、实验内容本实验的内容为使用SQL对数据进行完整性控制（关系数据库的三类完整性、CHECK短语、CONSTRAINT子句、触发器）。用实验证实，当操作违反了完整性约束条件时，系统如何处理的。认真完成以下题目，按要求填写实验报告，记录所有实验用例。（1）打开SQL Server 2000的查询分析器，用DDL语句建立如下三个表。表student1：字段名类型长度含义约束SnoChar10学号主码(约束名：STU_PK)SnameChar20姓名非空(约束名：STU_U1)SsexChar2性别男或女 (约束名：STU_C1)SageInt年龄18 到 30(约束名：STU_C2)SdeptChar10所在系表course1：字段名类型长度含义约束CnoChar10课程号主码(约束名：COU_PK)CnameChar20课程名唯一(约束名：COU_C1)CpnoChar2先行课CcreditInt学分表SC1：字段名类型长度含义约束SnoChar10学号主码(约束名：SC_PK)外码(参照student1的Sno，约束名：SC_FK1)CnoChar20课程号主码(约束名：SC_PK)外码(参照course1的Cno，约束名：SC_FK2)GradeChar2成绩create table student1(Sno char(10),Sname char(20)constraint STU_U1 NOT NULL,Ssex char(2)constraint STU_C1 CHECK(Ssex in(男,女),Sage intconstraint STU_C2 check(Sage between 18 and 30),Sdept char(10)constraint STU_PK PRIMARY KEY(Sno);create table course1(Cno char(10),Cname char(20)constraint COU_C1 UNIQUE,Cpno char(2),Ccredit intconstraint COU_PK PRIMARY KEY(Cno);create table SC1(Sno char(10),Cno char(10),Grade CHAR(2),constraint SC_PK PRIMARY KEY(Sno,Cno),constraint SC_FK1 FOREIGN KEY(Sno) REFERENCES student1(Sno),constraint SC_FK2 FOREIGN KEY(Cno) REFERENCES course1(Cno),);（2）插入如下数据，什么原因导致某些数据不能正确插入？SnoSnameSsexSageSdept95001黎小明男16CS95002刘小华男45CS95003王小菲女26MA95004王小凌女20IS95004刘小烨男27ISinsertinto student1(Sno,Sname,Ssex,Sage,Sdept)VALUES(95001,黎小明,男,16,CS);insertinto student1(Sno,Sname,Ssex,Sage,Sdept)VALUES(95002,刘小华,男,45,CS);insertinto student1(Sno,Sname,Ssex,Sage,Sdept)VALUES(95003,王小菲,女,26,MA);insertinto student1(Sno,Sname,Ssex,Sage,Sdept)VALUES(95003,王小凌,女,20,IS);insertinto student1(Sno,Sname,Ssex,Sage,Sdept)VALUES(95003,刘小烨,男,27,IS);原因：年龄不在约束条件内，Sno不能重复插入。学号重复。CnoCnameCcredit1高等数学42大学英语43计算机导论34计算机网络35计算机网络3insertinto course1(Cno,Cname,Ccredit)VALUES(1,高等数学,4);insertinto course1(Cno,Cname,Ccredit)VALUES(2,大学英语,4);insertinto course1(Cno,Cname,Ccredit)VALUES(3,计算机导论,3);insertinto course1(Cno,Cname,Ccredit)VALUES(4,计算机网络,3);insertinto course1(Cno,Cname,Ccredit)VALUES(5,计算机网络,3);原因：Cname唯一，不能重复插入。SnoCnoGrade9500118095001290950031959500417595004295insertinto SC1(Sno,Cno,Grade)VALUES(95001,1,80);insertinto SC1(Sno,Cno,Grade)VALUES(95001,2,90);insertinto SC1(Sno,Cno,Grade)VALUES(95003,1,95);insertinto SC1(Sno,Cno,Grade)VALUES(95004,1,75);insertinto SC1(Sno,Cno,Grade)VALUES(95004,2,95);（3） 给SC1表添加一个约束，约束名为SC1_C1，该约束限制Grade的取值范围是0到100。alter table SC1add constraint SC1_C1 check(Grade between 0 and 100);（4） 更改约束STU_C2，使得年龄的范围是15到50。alter table student1drop constraint STU_C2 ;ALTER TABLE student1add constraint STU_C2 CHECK(Sage between 0 and 100);（5） 对SC1表设计一个触发器，该触发器保证每个学生所选课程的总学分不超过12分。create trigger Insert_SC1 on dbo.SC1for insert,updateas if(12=(select sum(Ccredit)from SC1,Course1,Inserted where SC1.Cno=Course1.Cno and SC1.Sno=(select Sno from inserted)beginraiserror(不能选！,0,0)rollback transactionend return;（6）验证触发器。insertinto SC1values(200215121,1,92)insertinto SC1values(200215121,2,92)insertinto SC1values(200215121,3,92)三、实验步骤1.在查询分析器中完成（1）到（4）小题。2.在企业管理器中创建触发器：（1）在企业管理器中，由服务器开始逐步扩展到触发器所属表的数据库，打开表文件夹，在表窗口中用鼠标右击触发器所属的表（本例为选课表即SC1）。（2）在弹出菜单上选择“所有任务”“管理触发器”项，则弹出如图7-1所示的触发器属性对话框。 图7-1 触发器属性对话框如果要新建触发器，在文本T的文本框中输入创建触发器的SQL语句（见实验内容），单击“检查语法”按钮进行语法的检查，检查无误后，单击确定按钮。如图7-2所示，于是执行对基本表SC1的插入和更新操作的时候，都会自动显示表SC1中的内容。请在查询分析器中进行验证该触发器。图7-2 触发器语法检查对话框（3）如果要修改触发器，则在“名称”弹出项中选择要修改的触发器名（本例为SC_inserted）,然后在文本T的文本框中对已有的内容进行修改，检查无误后单击“确定”按钮。如图7-3所示，将该触发器代码修改为检查插入、更新、删除操作时触发。请在查询分析器中进行验证该触发器。图7-3 修改触发器语法检查对话框（4）如果要删除触发器，则在“名称”弹出项中选择要删除的触发器名（本例为SC