华工数据库实验题 (2)

IF(EXISTS(SELECT * FROM Students WHERE SNAME=@SNAME) AND EXISTS (SELECT * FROM Courses WHERE CNAME=@CNAME)) BEGIN

Insert into SC

select (SELECT TOP 1 SNO FROM Students WHERE SNAME=@SNAME), (SELECT TOP 1 CNO FROM Courses WHERE CNAME=@CNAME), @GRADE

END

3） PK_SC,FK_SC_SNO,FK_SC_CNO) （选做）

要求：首先删除SC中所有主键和外键定义，用触发器实现表SC上的主键（SNO，CNO）和外键SNO，CNO的约束定义。

3．为Student数据库设计安全机制。

要求：在该数据库系统中，有三类用户：

1) 学生，权限包括：查询所有的课程信息，根据学号和课程号来查询成绩。

但不允许修改任何数据。(必做)

只能查询自己的成绩，不能查询别人的成绩。（选做）

2）老师：权限包括：查询有关学生及成绩的所有信息，有关课程的所有信息，但不允许修改任何数据。

答：

CREATE TRIGGER secure_student BEFORE UPDATE OR DELETE ON database BEGIN

IF((select user from dual)='老师') THEN

RAISE_APPLICATION_ERROR(-20506,

'您没有权限对学生表进行修改.')

3）教务员：权限包括：查询和修改任何有关学生和课程的信息，但不允许查询和修改数据库中其它任何表，视图等数据库对象。 答：CREATE TRIGGER secure_student

BEFORE UPDATE OR DELETE ON database EXCEPT Students BEGIN

IF((select user from dual)='教务员') THEN

RAISE_APPLICATION_ERROR(-20506,

'您没有权限进行修改.')

要求：安全控制必须仅由数据库一端来实现，不考虑由应用程序来控制。 为此，需要创建三个用户，登录时密码验证；分别授予各类权限，并测试权限的控制是否有效。

实验三：SQL编程 实验要求：

1．采用实验一的建库脚本和数据插入脚本创建Student数据库。 2．在数据库中创建以下存储过程：

1) Add_Student (SNO,SNAME,SEX,BIRTHDAY,HEIGHT,DEPT) 要求：根据输入参数，插入一条学生记录。

答：create procedure Add_Student @SNO varchar(100), @SNAME varchar(100), @SEX varchar(10), @BIRTHDAY datetime, @HEIGHT decimal, @DEPT varchar(100) as

insert into Students values( @SNO, SNO, @SNAME, @SEX, @BIRTHDAY, @HEIGHT, @DEPT

)

2) Upd_Grade (SNO, CNO, GRADE)

要求：根据输入参数，修改某学生选课的成绩。 答：create procedure Upd_Grade

@SNO varchar(100), @CNO varchar(100), @GRADE INT as

UPDATE SC set SNO=@SNO, CNO=@CNO, GRADE=@GRADE

where SNO=@SNO and CNO=@CNO

3) Disp_Student (SNO，SUM_CREDIT output，AVG_GRADE output) 要求：根据SNO参数显示该学生的有关信息，包括：

a)学号，姓名，性别，年龄，身高，系别，所有选修的课程及成绩； b)显示输出参数SUM_CREDIT(表示选修课程的总学分)及AVG_GRADE (表示3学分以上的课程的平均成绩)。

答：create procedure Upd_Grade

@SNO varchar(100), @SUM_CREDIT INT output, @AVG_GRADE int output

as

select * from Students s inner join SC on sc.SNO=s.SNO

inner join Courses c on sc.CNO=c.CNO where s.SNO=@SNO

select @SUM_CREDIT=SumCredit from Credits where SNO=@SNO

select @AVG_GRADE=avg(SC.GRADE) from Students s inner join SC on sc.SNO=s.SNO where s.SNO=@SNO and SC.GRADE>3 group by s.SNO

4) CAL_GPA (SNO，GPA output)

要求：根据SNO参数, 输出并显示该学生的GPA值。计算方法如下： GRADE(G) GRADEPOINT(GP) G>=85 4 85>G>=75 3 75>G>=60 2 60>G 1

GPA= (∑GP*CREDIT)/ ∑CREDIT) 答：create procedure CAL_GPA

@SNO varchar(100), @GPA decimal output as

declare @SUM_CREDIT int,@AVG_GRADE int,@All_CREDIT int select @SUM_CREDIT=SumCredit from Credits where SNO=@SNO

select @AVG_GRADE=avg(case when SC.GRADE>=85 then 4 when 85>SC.GRADE and SC.GRADE>=75 then 3 when 75>SC.GRADE and SC.GRADE>=60 then 2 when 60>SC.GRADE then 1 end) from Students s inner join SC on sc.SNO=s.SNO where s.SNO=@SNO and SC.GRADE>3 group by s.SNO

select @GPA=(@AVG_GRADE*@SUM_CREDIT)/@All_CREDIT

3．选做题：

使用其它程序设计语言编程，连接数据库并提交SQL语句，显示查询结果。 要求：实现上题中的第3)小题要求，设计一个图形界面来输入查询的参数SNO，及显示查询的结果。(如采用VC++，VB等)

实验四：事务的管理（选作） 实验要求：

1． 采用实验一的建库脚本和数据插入脚本创建Student数据库。 2． 测试事务隔离级别，要求：

分别设置不同的隔离级别,包括：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET TRANSACTION ISOLATION LEVEL READ ONLY;

两个并发事务交错执行的程序，能分别显示每种隔离级别下，是否出现丢失更新，脏读，读值不可复现以及幻象四种情况。 3．备份与恢复

? 备份数据库 答：

BACKUP DATABASE test TO disk = 'c:\\test'

? 删除sc表

答：DROP TABLE SC

? 恢复到删除之前 答:

USE master GO

RESTORE DATABASE test_wt FROM disk = 'c:\\test_wt'

GO

搜索“diyifanwen.net”或“第一范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，第一范文网，提供最新幼儿教育华工数据库实验题 (2)全文阅读和word下载服务。