华南理工大学-计算机学院-数据库-实验二-报告(共19页).doc

《华南理工大学-计算机学院-数据库-实验二-报告(共19页).doc》由会员分享，可在线阅读，更多相关《华南理工大学-计算机学院-数据库-实验二-报告(共19页).doc（19页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

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

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

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

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

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

6、s(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 向辅助表插入数据 tmpSumCredit tmpNoPass代码：insert int

7、o 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 向Credit插入数据（通过辅助表） 代码：insert into Credits(SNO,S

8、umCredit,NoPass)select tmpSumCredit.SNO, tmpSumCredit.SumCredit, tmpNoPass.NoPassfrom tmpSumCredit, tmpNoPasswhere tmpSumCredit.SNO=tmpNoPass.SNO; 2） 创建视图Student_Grade(Sname,Cname,Grade)，表示学生选修课程及成绩的详细信息。 创建视图 结果 代码：create view Student_Grade(Sname, Cname, Grade)asselect SC.SNO, SNAME, GRADEfrom Stud

9、ents, 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 rowbeginselect CREDIT into Credit from Course

10、s 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_Grade作插入数据项操作时，自动触发Upd_StuView，完成对SC表的插入操作

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

12、mallint)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 tmpCNO from Courses where newCNAME=CNAME;

13、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的约束定义。在存储过程内，使用约束性命名子句进行实体完整性和参照完整性约束代码：delimiter /cr

14、eate 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 /3 为Student数据库设计安全机制。要求：安全控制必须仅由数据库一端来实现，不考虑由应用程序来控制。为此，需要创建三个用

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

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

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

18、date on lab.Courses to dean;grant update on lab.Students to dean;Step2:教务员拥有权限（1.用户登录2.查询相关表 3.更新相关表 4.无法查询其他表）(1).(2).(3).(4).小结通过这次实验，我学习到了很多知识及操作技巧。通过在实践中不断反复练习尝试，我非常欣喜能够将课堂上讲授的理论知识在一步步完成，实现，尤其是在看到查询结果或者相关操作实现之后，那种喜悦一点点激发着对于数据库课程学习的兴趣。在这次实验过程中，我首先是巩固了之前实验的操作;其次，我学习到了建立触发器的操作;最后，我得到的安全性工作的相关技巧。在过程

19、中，还是遇到了一定问题。首先是如何通过已有的表建立一个有数据的表，这个问题通过提问解决（感谢助教师兄师姐给予的帮助）。然后，花费了大量的时间在创建触发器上，原因是相关的语法知识的欠缺。关于这个问题，是通过查阅大量相关资料，并收集总结后解决的。虽然是花费了一定的时间，但是中间获得的知识还是值得的，尤其是解决问题的能力。现将收获的技能技巧总结如下：（1） 通过已经建立的表插入数据: 首先建立该表。 通过建立辅助表，以获得每个属性应有的数据。 在通过有子查询过程的insert语句插入相关数据。（2） mysql的delimiter语法： 在存储过程或者触发器的begin和and中，可能拥有多条语句，

20、需要“;”但是又不能让中间的过程预先执行，则使用delimiter来表明中间过程中的“;”先不执行。（3） mysql 创建触发器语法： 语法有三个关键点： 1）在触发器定义时需有语句： create trigger 触发器名 before/after insert/delete/update on 表名 for each row 2）更加具体的内容应该写在：begin end 之中 3）获取新插入或者旧删除的数据应该在原本属性名之前加上new/old来获得（4） mysql declare语法： 不能有，不能并列定义（5） mysql 存储过程语法： create procedure 过程名 begin and（6） mysql 安全性授予权限（插入删除更新权限）：grant 权限 on 表名 to 用户（7） mysql 创建用户过程: create user 用户名% identified by 密码;（8） 巩固创建角色role的过程: create role 之后再像正常使用权限一样使用。（9） 巩固表及视图的定义及更新操作（10） 巩固数据库的登录及使用操作 最后，实验的过程使我们在课堂上学习的理论知识得以实际实现，使我获得很多相关的知识和相关技巧，感谢实验过程带给我们的收获。指导教师评语及成绩评语： 成绩： 指导教师签名： 批阅日期：专心-专注-专业