西北工业大学数据库实验报告5

《数据库原理》实验报告 题目：实验一 数据库和表的创建与管理 学号 姓名 班级 日期 一. 实验内容、步骤以及结果

1. 使用系统存储过程(sp_rename)将视图“V_SPJ”更名为“V_SPJ_三建”。（5分） exec sp_rename v_spj,v_spj_三建

2. 针对SPJ数据库，创建并执行如下的存储过程：（共计35分）

(1) 创建一个带参数的存储过程—jsearch。该存储过程的作用是：当任意输入一个工

程代号时，将返回供应该工程零件的供应商的名称(SNAME)和零件的名称(PNAME)以及工程的名称(JNAME)。执行jsearch存储过程，查询“J1”对应的信息。（10分） 创建：

create procedure jsearch(@search_jno nchar(20) ) as begin

select j.jname,s.sname,p.pname from s,p,j,spj where

spj.jno=@search_jno and spj.jno=j.jno and spj.sno=s.sno and spj.pno=p.pno end

执行：

EXEC jsearch @search_jno='J1'

(2) 使用S表，为其创建一个加密的存储过程—jmsearch。该存储过程的作用是：当执

行该存储过程时，将返回北京供应商的所有信息。（10分） 创建：

create procedure jmsearch with encryption as begin

select * from S where city='北京' end

使用系统存储过程sp_helptext查看jsearch, jmsearch的文本信息。（exec sp_helptext 'jsearch'

exec sp_helptext 'jmsearch'

(3) 执行jmsearch存储过程，查看北京供应商的情况。（5分） 执行：

exec jmsearch

5分）

(4) 删除jmsearch存储过程。（5分）

drop procedure jmsearch

3. 针对Student数据库，创建和执行如下的触发器：（共计40分）

(1) 删除SC表上的外键约束，针对SC表创建一个名为insert_s的INSERT触发器。该

触发器的功能：当用户向SC表中插入记录时，如果插入的cno值不是C表中Cno的已有值，则提示用户“不能插入记录这样的纪录”，否则提示“记录插入成功”。触发器创建成功之后，向SC表插入记录，验证触发器是否正常工作。(5分) create trigger insert_s on SC

instead of insert as

if (exists(select *from inserted where cno not in (select cno from C))) begin

print '不能插入这样的记录！！' rollback transaction end else

print '记录插入成功！！'

验证：

insert into sc values (95009,8,98)

更改数据：insert into sc values (95009,5,98)

(2) 为S表创建一个名为dele_s1的DELETE触发器，该触发器的作用是禁止删除S表

中的记录。触发器创建成功之后，删除S表中的记录，验证触发器是否正常工作。(5分) 创建：

create trigger dele_s1 on s

instead of delete as

rollback transaction print '禁止删除' 验证：

delete from s where sno='95001'

(3) 为S表创建一个名为dele_s2的DELETE触发器，该触发器的作用是删除S表中的

记录时删除SC表中该学生的选课纪录。触发器创建成功之后，删除S表中的记录删除S表中的记录，验证触发器是否正常工作(SC表中的数据被正常删除)。(5分) 创建：

create trigger dele_s2 on s

after delete as

delete from sc

where sno in (select sno from deleted) 验证：

delete from s where sno='95001' S表中：

Sc表中：

(4) 为S表创建一个名为update_s的UPDATE触发器，该触发器的作用是禁止更新S

表中“sdept”字段的内容。触发器创建成功之后，更新S表中“sdept”字段的内容，验证触发器是否正常工作。(5分) 创建:

create trigger update_s on s

instead of update as

if update(sdept) begin

raiserror('sdept 不能被修改',10,1) end 验证：

(5) 禁用update_s触发器。禁用之后，更新S表中的“sdept”字段的内容，验证触发

器是否还继续正常工作。(5分) 禁用触发器：

disable trigger update_s on s 验证：

update s

set sdept='CS1' where sno ='95002'

(6) 删除update_s触发器。(5分) drop trigger update_s

(7) 创建一个新的课程成绩统计表 CAvgGrade(Cno, Snum, examSNum, avgGrade)，分

别表示课号，选该课程的学生人数，参加考试人数，该门课程的平均成绩。利用触发器实现如下的功能：当SC表中有记录插入、删除或者某个人的成绩更新时，自动更新表CAvgGrade。注意SC表中的grade为NULL时表明该学生还未参加考试，计算平均成绩时不需要计算该成绩，但是grade为0即考试成绩为0时，需要计算该成绩。(10分) 创建表：