华南理工大学-计算机学院-数据库-实验二-报告

1、精选优质文档-倾情为你奉上精选优质文档-倾情为你奉上专心-专注-专业专心-专注-专业精选优质文档-倾情为你奉上专心-专注-专业华南理工大学数据库课程实验报告实验题目： 实验二：数据库的安全和完整性约束 姓名： 学号： 班级： 17计科一班 组别： 无 合作者： 无 指导教师： 董守玲 实验概述【实验目的及要求】实验目的： 通过创建视图、触发器，设计安全机制等方式掌握数据库的安全和完整性的设计。实验要求：1采用实验一的建库脚本和数据插入脚本创建Student数据库,并完成以下操作：1）新增表Credits(SNO,SumCredit,NoPass)，表示每学生已通过选修课程的合计学分数，以及不及

2、格的课程数。2）创建视图Student_Grade(Sname,Cname,Grade)，表示学生选修课程及成绩的详细信息。2.在数据库中创建以下触发器：1）Upd_Credit要求：当在SC表中插入一条选课成绩，自动触发Upd_Credit，完成在Credits表中修改该学生的合计学分数和不及格的课程数。2）Upd_StuView（Instead of触发器）要求：当对视图Student_Grade作插入数据项操作时，自动触发Upd_StuView，完成对SC表的插入操作。如：当执行Insert into Student_Grade values(王刚，数据库，54)则触发器完成另一插入操作

3、：Insert into SC values(，CS-110，54)另外，需要检查当前插入的学生和课程是否已在Students，和Courses表中存在，如不存在，不执行任何操作，并提示用户错误信息。3）PK_SC,FK_SC_SNO,FK_SC_CNO)（选做）要求：首先删除SC中所有主键和外键定义，用触发器实现表SC上的主键（SNO，CNO）和外键SNO，CNO的约束定义。3为Student数据库设计安全机制。要求：在该数据库系统中，有三类用户：1)学生，权限包括：查询所有的课程信息，根据学号和课程号来查询成绩。但不允许修改任何数据。(必做)只能查询自己的成绩，不能查询别人的成绩。（选做）

4、2）老师：权限包括：查询有关学生及成绩的所有信息，有关课程的所有信息，但不允许修改任何数据。3）教务员：权限包括：查询和修改任何有关学生和课程的信息，但不允许查询和修改数据库中其它任何表，视图等数据库对象。要求：安全控制必须仅由数据库一端来实现，不考虑由应用程序来控制。为此，需要创建三个用户，登录时密码验证；分别授予各类权限，并测试权限的控制是否有效。【实验环境】PC机，WINDOWS操作系统，Oracle 或Microsoft SQL Server 数据库实验内容【实验过程】实验步骤：1.登录数据库，使用实验相关数据库2.完成触发器部分： （1）新增数据库并计算插入相关数据（需要辅助表） （

5、2）创建视图 （3）在表的插入过程中建立触发器 （4）在视图的插入过程中创建触发器完成安全性部分： （1）完成三个用户的创建，并测试初始权限 （2）授予学生权限，学生重新登录，测试相关权限有无 （3）授予老师权限，老师重新登录，测试相关权限有无 （4）教务员学生权限，教务员重新登录，测试相关权限有无实验数据： 实验一使用的数据库。三、实验主要过程：1采用实验一的建库脚本和数据插入脚本创建Student数据库,并完成以下操作：1）新增表Credits(SNO,SumCredit,NoPass)，表示每学生已通过选修课程的合计学分数，以及不及格的课程数。Step1创建表新增表Credits(SNO

6、,SumCredit,NoPass) 代码：create table Credits(SNO varchar(15),SumCredit decimal(5,1),NoPass smallint);Step2 创建辅助表tmpSumCredit( SNO varchar(15), SumCredit decimal(5,1) 两个 代码：create table tmpSumCredit(SNO varchar(15),SumCredit decimal(5,1);create table tmpNoPass(SNO varchar(15),NoPass smallint);Step3 向辅助

7、表插入数据 tmpSumCredit tmpNoPass代码：insert into tmpSumCredit(SNO,SumCredit)select SC.SNO, sum(CREDIT)from Courses,SCwhere SC.CNO=Courses.CNOgroup by SNO;insert into tmpNoPass(SNO,NoPass)select SC.SNO, sum(CASE WHEN GRADE60 THEN 1 ELSE 0 END)from Courses,SCwhere SC.CNO=Courses.CNOgroup by SNO;Step4 向Credi

8、t插入数据（通过辅助表） 代码：insert into Credits(SNO,SumCredit,NoPass)select tmpSumCredit.SNO, tmpSumCredit.SumCredit, tmpNoPass.NoPassfrom tmpSumCredit, tmpNoPasswhere tmpSumCredit.SNO=tmpNoPass.SNO; 创建视图Student_Grade(Sname,Cname,Grade)，表示学生选修课程及成绩的详细信息。 创建视图 结果 代码：create view Student_Grade(Sname, Cname, Grade)

9、asselect SC.SNO, SNAME, GRADEfrom Students, Courses, SCwhere Students.SNO=SC.SNO and Courses.CNO=SC.CNO; 2.在数据库中创建以下触发器：1）Upd_Credit要求：当在SC表中插入一条选课成绩，自动触发Upd_Credit，完成在Credits表中修改该学生的合计学分数和不及格的课程数。Step1:创建触发器,插入数据，并查看Credits表代码：delimiter /create trigger Upd_Credit after insert on SC for each rowbegi

10、nselect CREDIT into Credit from Courses where new.CNO=CNO;update Credits set SumCredit=SumCredit+Credit,NoPass=NoPass+(case when new.GRADE60 then 1 else 0 end) where SNO=new.SNO;end /Step2:结果（Credits前后对比，在插入8 EE1 59 后） 代码：insert into SCvalues(8,EE1,59);2）Upd_StuView（Instead of触发器）要求：当对视图Student_Grad

11、e作插入数据项操作时，自动触发Upd_StuView，完成对SC表的插入操作。如：当执行Insert into Student_Grade values(王刚，数据库，54)则触发器完成另一插入操作：Insert into SC values(，CS-110，54)另外，需要检查当前插入的学生和课程是否已在Students，和Courses表中存在，如不存在，不执行任何操作，并提示用户错误信息。Step1:触发器的创建得知mysql的DBMS 无法在视图上建立触发器，后改用存储过程。代码：delimiter /create procedure Upd_StuView(newSNAME varc

12、har(5),newCNAME varchar(10),newGRADE smallint)begindeclare tmpSNO varchar(15);declare tmpCNO varchar(10);case when exists(select* from Students where SNAME=newSNAME)and exists(select*from Courses where CNAME=newCNAME) then select SNO into tmpSNO from Students where newSNAME=SNAME;select CNO into tmp

13、CNO from Courses where newCNAME=CNAME;insert into SC values(tmpSNO, tmpCNO, newGRADE);end case;end /Step2:插入数据代码：call Upd_StuView(王刚,数据库,54);call Upd_StuView(张嘉奥,数字电路,59);Step3:查询结果（根据成绩反向查找，由上图的查询条件）3）PK_SC,FK_SC_SNO,FK_SC_CNO)（选做）要求：首先删除SC中所有主键和外键定义，用触发器实现表SC上的 主键（SNO，CNO）和外键SNO，CNO的约束定义。在存储过程内，使用

14、约束性命名子句进行实体完整性和参照完整性约束代码：delimiter /create procedure Cons_Def()beginalter table SC add constraint C1 primary key(SNO,CNO);alter table SC add constraint C2 foreign key(SNO) references Students(SNO);alter table SC add constraint C3 foreign key(CNO) references Courses(CNO);end /为Student数据库设计安全机制。要求：安全控

15、制必须仅由数据库一端来实现，不考虑由应用程序来控制。为此，需要创建三个用户，登录时密码验证；分别授予各类权限，并测试权限的控制是否有效。要求：在该数据库系统中，有三类用户：Step1:创建学生，老师，教务员三个用户代码：create userteacher% identified by;create userstudent% identified by;create userdean% identified by;Step2:在授权之前，相应的用户没有权限（举例访问Students数据库）(1)Student(第一行可看出的登录用户，最后报错可看出无访问权限)(2)Teacher(第一行可看出

16、的登录用户，最后报错可看出无访问权限)(3)Dean(第一行可看出的登录用户，最后报错可看出无访问权限)学生，权限包括：查询所有的课程信息，根据学号和课程号来查询成绩。但不允许修改任何数据。(必做)Step1:授予学生权限：代码：grant select on lab.Courses to student;grant select(GRADE) on lab.SC to student;Step2:学生拥有权限（第一行为用户登录，最后查询有结果，有权限）老师：权限包括：查询有关学生及成绩的所有信息，有关课程的所有信息，但不允许修改任何数据。Step1:授予老师权限代码：grant select

17、 on lab.Students to teacher;grant select on lab.Courses to teacher;grant select(GRADE) on lab.SC to teacher;Step2:老师拥有权限（1.重新登录，2.可以查询相关内容，3.但不能进行修改）(1).(2).(3).教务员：权限包括：查询和修改任何有关学生和课程的信息，但不允许查询和修改数据库中其它任何表，视图等数据库对象。Step1:授予教务员权限(分别读写两个表，四个权限)代码：grant select on lab.Courses to dean;grant select on la

18、b.Students to dean;grant update on lab.Courses to dean;grant update on lab.Students to dean;Step2:教务员拥有权限（1.用户登录2.查询相关表 3.更新相关表 4.无法查询其他表）(1).(2).(3).(4).小结通过这次实验，我学习到了很多知识及操作技巧。通过在实践中不断反复练习尝试，我非常欣喜能够将课堂上讲授的理论知识在一步步完成，实现，尤其是在看到查询结果或者相关操作实现之后，那种喜悦一点点激发着对于数据库课程学习的兴趣。在这次实验过程中，我首先是巩固了之前实验的操作;其次，我学习到了建立触发器的操作;最后，我得到的安全性工作的相关技巧。在过程中，还是遇到了一定问题。首先是如何通过已有的表建立一个有数据的表，这个问题通过提问解决（感谢助教师兄师姐给予的帮助）。然后，花费了大量的时间在创建触发器上，原因是相关的语法知识的欠缺。关于这个问题，是通过查阅大量相关资料，并收集总结后解决的。虽然是花费了一定的时间，但是中间获得的知识还是值得的，尤其是解决问题的能力。现将收获的技能技巧总结如下：通过已经建立的表插入数据: 首先建立该表。 通过建立辅助表，以获得每个属性应有的数据。