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

***大学计算机科学与信息学院软件工程系上机实验报告

《数据库原理》实验报告

题目实验8 数据的

完整性姓名***班级***日期***

实验环境：SQL Server 2000

一、实验目的

熟悉通过SQL对数据进行完整性控制，通过实验加深对数据完整性的理解。

相关知识

触发器是实施复杂完整性的一种机制。触发器不需要专门的语句调用，当对它所保护数据进行修改时由DBMS自动激活，以防止对数据进行不正确，未授权或不一致的修改。

创建触发器的语法为：

CREATE TRIGGER < 触发器> ON <表名>

[WITH ENCRYPTION]

FOR {[DELETE][，][INSERT][，][UPDATE]}

[WITH APPEND]

[NOT FOR REPLICATION]

AS

其中：

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：

字段名类型长度含义约束

Sno Char 10 学号主码(约束名：STU_PK)

Sname Char 20 姓名非空(约束名：STU_U1)

Ssex Char 2 性别‘男’或’女’ (约束名：STU_C1)

Sage Int 年龄18 到 30(约束名：STU_C2)

Sdept Char 10 所在系

表course1：

字段名类型长度含义约束

Cno Char 10 课程号主码(约束名：COU_PK)

Cname Char 20 课程名唯一(约束名：COU_C1)

Cpno Char 2 先行课

Ccredit Int 学分

表SC1：

字段名类型长度含义约束

Sno Char 10 学号主码(约束名：SC_PK)

外码(参照student1的Sno，约束名：

SC_FK1)

Cno Char 20 课程号主码(约束名：SC_PK)

外码(参照course1的Cno，约束名：SC_FK2)

Grade Char 2 成绩

create table student1(

Sno char(10),

Sname char(20)

constraint STU_U1 NOT NULL,

Ssex char(2)

constraint STU_C1 CHECK(Ssex in('男','女')), Sage int

constraint 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 int

constraint 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_FK1FOREIGN KEY(Sno)REFERENCES student1(Sno),

constraint SC_FK2FOREIGN KEY(Cno)REFERENCES course1(Cno),

);

（2）插入如下数据，什么原因导致某些数据不能正确插入？

Sno Sname Ssex Sage Sdept

95001 黎小明男16 CS 95002 刘小华男45 CS 95003 王小菲女26 MA 95004 王小凌女20 IS 95004 刘小烨男27 IS insert

into student1(Sno,Sname,Ssex,Sage,Sdept)

VALUES('95001','黎小明','男',16,'CS');

insert

into student1(Sno,Sname,Ssex,Sage,Sdept)

VALUES('95002','刘小华','男',45,'CS');

insert

into student1(Sno,Sname,Ssex,Sage,Sdept)

VALUES('95003','王小菲','女',26,'MA');

insert

into student1(Sno,Sname,Ssex,Sage,Sdept)

VALUES('95003','王小凌','女',20,'IS');

insert

into student1(Sno,Sname,Ssex,Sage,Sdept)

VALUES('95003','刘小烨','男',27,'IS');

原因：年龄不在约束条件内，Sno不能重复插入。学号重复。

Cno Cname Ccredit

1 高等数学 4

2 大学英语 4

3 计算机导论 3

4 计算机网络 3

5 计算机网络 3

insert

into course1(Cno,Cname,Ccredit)

VALUES('1','高等数学','4');

insert

into course1(Cno,Cname,Ccredit)

VALUES('2','大学英语','4');

insert

into course1(Cno,Cname,Ccredit)

VALUES('3','计算机导论','3');

insert

into course1(Cno,Cname,Ccredit)

VALUES('4','计算机网络','3');

insert

into course1(Cno,Cname,Ccredit)

VALUES('5','计算机网络','3');

原因：Cname唯一，不能重复插入。

Sno Cno Grade 95001 1 80

95001 2 90

95003 1 95

95004 1 75 95004 2 95 insert

into SC1(Sno,Cno,Grade)

VALUES('95001','1',80);

insert

into SC1(Sno,Cno,Grade)

VALUES('95001','2',90);

insert

into SC1(Sno,Cno,Grade)

VALUES('95003','1',95);

insert

into SC1(Sno,Cno,Grade)

VALUES('95004','1',75);

insert

into SC1(Sno,Cno,Grade)

VALUES('95004','2',95);

（3）给SC1表添加一个约束，约束名为SC1_C1，该约束限制Grade的取值范围是0到100。alter table SC1

add constraint SC1_C1check(Grade between 0 and 100);

（4）更改约束STU_C2，使得年龄的范围是15到50。

alter table student1

drop constraint STU_C2;

ALTER TABLE student1

add constraint STU_C2CHECK(Sage between 0 and 100);

（5）对SC1表设计一个触发器，该触发器保证每个学生所选课程的总学分不超过12分。create trigger Insert_SC1on dbo.SC1

for insert,update

as if(12<=(select sum(Ccredit)

from SC1,Course1,Inserted

where https://www.360docs.net/doc/9a29016.html,o=https://www.360docs.net/doc/9a29016.html,o and SC1.Sno=(select Sno from inserted))) begin

raiserror('不能选！',0,0)

rollback transaction

end return;

（6）验证触发器。

insert

into SC1

values('200215121','1',92)

insert

into SC1

values('200215121','2',92)

insert

into SC1

values('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_inserted），并检查文本[T]的文本框中的内容，看是否选得正确，最后单击“删除”按钮。如图7-4所示。

图7-4 删除触发器对话框

3.在查询分析器中插入某个学生的多个选课记录，设计和验证触发器是否能够保证该学生的所有课程的总学分不能超过12分。

三、出现的问题及其解决方案(列出遇到的问题和解决办法，列出没有解决的问题)

问题1：在链接关系表时，提示提示出错。

分析原因：输入的数据违反了用户定义的数据的完整性。

解决办法：将数据改为定义范围内的数据。