数据库系统原理——数据库的安全性与完整性（二）

一、 实验目的

1、 掌握自主存取控制权限的定义和维护方法；
2、 掌握实体完整性、参照完整性和用户自定义完整性的定义和维护方法；
3、 掌握数据库触发器的设计和使用方法。

二、 实验内容

3.2数据库完整性实验

打开ScoreDB数据库，完成以下操作：
（1）分别定义ScoreDB数据库中各基表的主键、外键，实现实体完整性约束和参照完整性约束；

ALTER TABLE Class ADD PRIMARY KEY(classNo) 

ALTER TABLE Course ADD PRIMARY KEY(courseNo) 

ALTER TABLE Student ADD PRIMARY KEY(studentNo) 

ALTER TABLE Student ADD FOREIGN KEY(classNo) REFERENCES Class

ALTER TABLE Score ADD FOREIGN KEY(studentNo) REFERENCES Student
ALTER TABLE Score ADD FOREIGN KEY(courseNo) REFERENCES Course

（2）往student表插入一条跟已有的学号相同的学生数据，验证实体完整性约束；

INSERT INTO Student
VALUES ('0700001','张小勇','男','1990/12/21 0:00:00','南昌','汉族','CS0701')
----显示不能插入重复键

（3）向score表中插入一条数据，其中数据中的课程编号是课程表中没有的，验证参照完整性约束；

INSERT INTO Score
VALUES('1700001','001','98')

（4）修改score表中一条数据的课程编号，该编号是课程表中没有的，验证参照完整性约束；

UPDATE Score
SET studentNo='1700001'
WHERE studentNo='0700001'

（5）删除student表中的一条数据（其中学号出现在score表中），验证参照完整性约束。

DELETE
FROM Student
WHERE studentNo='0700001'

（6）增加score表的约束条件，要求成绩在0-100之间。增加student表的约束条件，要求性别只允许取“男”或“女”。

ALTER TABLE Score
ADD CONSTRAINT C_Score CHECK (score>=0 AND score<=100)

（7）创建一个触发器student_update，实现功能：如果在学生表中修改了学号，则自动修改成绩表中的学号。

CREATE TRIGGER student_update
ON student
FOR UPDATE
AS
UPDATE score
SET studentNo =(SELECT studentNo FROM inserted)
WHERE studentNo =(SELECT studentNo FROM deleted)

（8）创建一个级联删除触发器student_delete。要求：当从学生表中删除某学生信息时，同时删除score表中此学生相关的选课记录。

create trigger student_ delete
on student
for delete
as
declare @sno_del char(7)
select @sno_del=studentNo from deleted
delete from score where studentNo=@sno_del

（9）创建一个触发器student_insert，实现功能：如果往学生表中插入数据，则自动修改班级表中的班级人数classNum。（如果改为删除数据，代码要怎么改？）

CREATE TRIGGER student_insert
on student
AFTER insert
AS
BEGIN
update class
set classNum=stuNumber
from class a,(select classNo,count(*) stuNumber from student group by clasNo) b
where a.classNo=b.classNo
END

（10）建立一个触发器course_check，每当删除课程表中记录时，先检查此课程是否已被选修，如选修则不允许删除，且给出提示信息“此课程已有学生选修，无法删除！”。

CREATE TRIGGER course_ check
ON course
INSTEAD OF DELETE
AS
IF EXISTS(SELECT courseNo FROM score WHERE courseNo=score.courseNo)
BEGIN
PRINT '此课程已有学生选修，无法删除!'
ROLL BACK TRANSACTION
END