第1页 / 共16页
第2页 / 共16页
第3页 / 共16页
第4页 / 共16页
第5页 / 共16页
第6页 / 共16页
第7页 / 共16页
第8页 / 共16页
北邮 数据库系统原理(SQL Server版) 实验五.pdf
实验名称 数据库完整性与安全性实验
实验五 数据库完整性与安全性实验
实验目的
实验内容与要求
实验步骤及结果：
实验目的
实验五 数据库完整性与安全性实验
实验目的
1. 通过对完整性规则的定义实现，熟悉了解 SQL SERVER 中完整性保证的规则和实现方
法，加深对数据完整性的理解。
2. 通过对安全性相关内容的定义，熟悉了解 SQL SERVER 中安全性的内容和实现方法，
加深对数据库安全性的理解
实验内容与要求
完整性实验与要求：
1. 分别定义数据库中各基表的主键、外键，实现实体完整性约束和参照完整性约束；
2. 向学生表插入具有相同学号的数据，验证其实体完整性约束；
3. 向学生表中插入一条数据，班级号是学生表的外键，验证参照完整性约束；
4. 删除教师表中的所有数据，验证参照完整性约束；
5. 定义存储过程，完成查询学生表中学号=g9940206 的行，并执行。
6. 定义触发器，当向学生表插入一条记录时，将所有学生的学号值加 1；并对其进行测试
分别用 Enterprise Manager 和 T_SQL 完成以上操作。
安全性实验与要求：
1. 定义一新的登陆帐号、数据库用户，并授予其访问学生选课数据库的读权限；
2. 分别采用 Windows 2007 验证方式和 Windows 2007 及 SQL Server 混合验证方式用不同
的用户进行登录连接；
3. 分别用 sa 用户和新定义的用户访问学生选课数据库，并对其中的学生表数据进行修改；
4. 创建数据库角色，并授予访问学生选课数据库的读、写权限；
5. 将角色赋予（1）中定义的用户，建立用户和角色联系；
6. 再次用此用户访问学生选课数据库，并对其中的学生表数据进行修改。
分别用 Enterprise Manager 和 Transact_SQL 语句分别完成以上内容。
实验步骤及结果：
完整性实验：
1. 分别定义数据库中各基表的主键、外键，实现实体完整性约束和参照完整性约束；
由于在实验 2 建立各个表的时候我已经设置了各个表的主键和外键，因此我先删除了所
有的逐渐和外键，然后开始这次实验。
T_SQL 语句：
--book
ALTER TABLE book ADD CONSTRAINT PK_BOOK_BOOK_ID PRIMARY KEY CLUSTERED (book_id);
--course
ALTER TABLE course ADD CONSTRAINT PK_COURSE_COURSES_ID PRIMARY KEY CLUSTERED (course_id);
ALTER TABLE course ADD CONSTRAINT FK_COURSE_BOOK_ID FOREIGN KEY (book_id) REFERENCES book
(book_id)
--ON DELETE CASCADE
--ON UPDATE CASCADE
;
--department
ALTER TABLE department ADD CONSTRAINT PK_DEPARTMENT_DEPARTMENT_ID PRIMARY KEY CLUSTERED
(department_id);
--class
ALTER TABLE class ADD CONSTRAINT PK_CLASS_CLASS_ID PRIMARY KEY CLUSTERED (class_id);
ALTER TABLE class ADD CONSTRAINT FK_CLASS_DEPARTMENT_ID FOREIGN KEY (department_id)
REFERENCES department (department_id)
--ON DELETE CASCADE
--ON UPDATE CASCADE
;
--class_course
ALTER TABLE class_course ADD CONSTRAINT PK_CLASS_COURSE_CLASSID_COURSEID PRIMARY KEY
CLUSTERED (class_id, course_id);
ALTER TABLE class_course ADD CONSTRAINT FK_CLASS_COURSE_CLASS_ID FOREIGN KEY (class_id)
REFERENCES class (class_id)
--ON DELETE CASCADE
--ON UPDATE CASCADE
;
ALTER TABLE class_course ADD CONSTRAINT FK_CLASS_COURSE_COURSE_ID FOREIGN KEY (course_id)
REFERENCES course (course_id)
--ON DELETE CASCADE
--ON UPDATE CASCADE
;
--student
ALTER TABLE student ADD CONSTRAINT PK_STUDENT_STUDENT_ID PRIMARY KEY CLUSTERED
(student_id);
ALTER TABLE student ADD CONSTRAINT FK_STUDENT_CLASS_ID FOREIGN KEY (class_id) REFERENCES
class (class_id)
--ON DELETE CASCADE
--ON UPDATE CASCADE
;
--student_course
ALTER TABLE student_course ADD CONSTRAINT PK_STUDENT_COURSE_COURSEID_STUDENTID PRIMARY KEY
CLUSTERED (course_id, student_id);
ALTER TABLE student_course ADD CONSTRAINT FK_STUDENT_COURSE_COURSE_ID FOREIGN KEY
(course_id) REFERENCES course (course_id)
--ON DELETE CASCADE
--ON UPDATE CASCADE
;
ALTER TABLE student_course ADD CONSTRAINT FK_STUDENT_COURSE_STUDENT_ID FOREIGN KEY
(student_id) REFERENCES student (student_id)
--ON DELETE CASCADE
--ON UPDATE CASCADE
;
--teacher
ALTER TABLE teacher ADD CONSTRAINT PK_TEACHER_TEACHER_ID PRIMARY KEY CLUSTERED
(teacher_id);
ALTER TABLE teacher ADD CONSTRAINT FK_TEACHER_DEPARTMENT_ID FOREIGN KEY (department_id)
REFERENCES department (department_id)
--ON DELETE CASCADE
--ON UPDATE CASCADE
;
--teacher_course_class
ALTER TABLE teacher_course_class ADD CONSTRAINT PK_TEACHER_COURSE_CLASS PRIMARY KEY
CLUSTERED (teacher_id,course_id, class_id);
ALTER TABLE teacher_course_class ADD CONSTRAINT FK_TCC_TEACHER_ID FOREIGN KEY (teacher_id)
REFERENCES teacher (teacher_id)
--ON DELETE CASCADE
--ON UPDATE CASCADE
;
ALTER TABLE teacher_course_class ADD CONSTRAINT FK_TCC_COURSE_ID FOREIGN KEY (course_id)
REFERENCES course (course_id)
--ON DELETE CASCADE
--ON UPDATE CASCADE
;
ALTER TABLE teacher_course_class ADD CONSTRAINT FK_TCC_CLASS_ID FOREIGN KEY (class_id)
REFERENCES class (class_id)
--ON DELETE CASCADE
--ON UPDATE CASCADE
;
ALTER TABLE teacher_course_class ADD CONSTRAINT FK_TCC_BOOK_ID FOREIGN KEY (book_id)
REFERENCES book (book_id)
--ON DELETE CASCADE
--ON UPDATE CASCADE
;
执行结果：
查看各个表的主键及外键：
2. 向学生表插入具有相同学号的数据，验证其实体完整性约束；
以下是学生表中的部分信息：
现执行插入语句（插入的学号和表中的第一条记录相同）：
insert into student values(
'g9940201', '李虹', '女', '1998-09-24', 'g99402', '2015-09-11', '北京市海淀区西
土城路10号');
由于插入的数据的student_id值在student表中已存在，报错：
3. 向学生表中插入一条数据，班级号是学生表的外键，验证参照完整性约束；
以下是 class 表的内容：
现向 student 表中插入一条 class_id 不在上表中的数据（class_id = ‘g11111’）：
insert into student values(
'g9941111', '李虹', '女', '1998-09-24', 'g11111', '2015-09-11', '北京市海淀区西
土城路 10 号');
由于插入的数据中的 class_id 值在 class 表中不存在，报错
4. 删除教师表中的所有数据，验证参照完整性约束；
由于违反了参照完整性约束，报错：
5. 定义存储过程，完成查询学生表中学号=g9940206 的行，并执行。
创建存储过程：
create procedure queryStudentID
@student_id varchar(10)
as
select * from student where student_id = @student_id
结果：
执行存储过程：
execute dbo.queryStudentID N'g9940206'
查询结果：
6. 定义触发器，当向学生表插入一条记录时，将所有学生的学号值加 1；并对其进行测试
SQL 代码：
create trigger increaseStudentID
on student
for insert as
begin
update student
set student_id = 'g' + cast(cast(substring(student_id, 2, 7) as integer) + 1 as
char)
end
student 表原始数据：
执行插入语句：
insert into student values(
'g9940305', '李虹', '女', '1998-09-24', 'g99403', '2015-09-11', '北京市海淀区西
土城路10号');
由于违反了参照完整性约束，结果会报错：
相关推荐
2023年江西萍乡中考道德与法治真题及答案.doc
2012年重庆南川中考生物真题及答案.doc
2013年江西师范大学地理学综合及文艺理论基础考研真题.doc
2020年四川甘孜小升初语文真题及答案I卷.doc
2020年注册岩土工程师专业基础考试真题及答案.doc
2023-2024学年福建省厦门市九年级上学期数学月考试题及答案.doc
2021-2022学年辽宁省沈阳市大东区九年级上学期语文期末试题及答案.doc
2022-2023学年北京东城区初三第一学期物理期末试卷及答案.doc
2018上半年江西教师资格初中地理学科知识与教学能力真题及答案.doc
2012年河北国家公务员申论考试真题及答案-省级.doc
2020-2021学年江苏省扬州市江都区邵樊片九年级上学期数学第一次质量检测试题及答案.doc
2022下半年黑龙江教师资格证中学综合素质真题及答案.doc
