《数据库原理》课程设计报告 - 21 -
select Sname, Cource.Courceno,Grade from Student, Cource, Elective where Student.Sno= Elective.Sno and Cource.Courceno= Elective.Courceno with check option
4.用于显示当前所选课程基本信息的视图定义如下：
create view ElectiveView as
select Sname,Courcename,Courcedirector from Student, Cource,Elective where Student.Sno= Elective.Sno and Cource.Courceno= Elective.Courceno with check option
5.用于显示所开设课程基本信息的视图定义如下：
create view CourceView as
select Courcename,Courcedirector from Cource
6.用于查询学生平均成绩的视图定义如下：
create view AverageView(Sno,AVG_Grade) AS
SELECT Sno,AVG (Grade) FROM Elective group by Sno
3.5.2建立存储过程
1.为教师表建立一个存储过程
go
create procedure Update_Tsal @tno char(10) as begin
update Teacher set
Tsal = '8000' where @tno=Tno end
declare
@tno1 char(10) set @tno1='011343' exec Update_Tsal @tno1
2.为系表建立一个存储过程
Create procedure Department_insert
21
《数据库原理》课程设计报告 - 22 -
@Xno char(10), @Xname char(10), @Xdiredtor char(10) As
Insert into Department values(@Xno,@Xname,@Xdiredtor); select *
from Department
exec Department_insert '07','市政学院','085426' select *
from Department
3.5.4建立触发器
1.教师表
create trigger T1 On Teacher for update as
if UPDATE(Tname) begin
print'拒绝修改' rollback end
update Teacher set Tname='吴迪' where Tno='022124'
2. 学生表
create trigger student_insert on Student for insert as
print'有新同学加入'
insert into Student values('0113002','周新','女','平顶山市新华',20,'软件工程' ,'0113')
3.选课表
create trigger 删除选课 on Elective for DELETE AS
if((select Courceno from deleted) in (select Courceno from Elective)) begin
print'删除选课成功'
22
《数据库原理》课程设计报告 - 23 -
rollback end
delete
from Elective
where Sno='0513001' and Courceno='05001'
4.院系
create trigger 添加院系 on Department for insert as
print '添加新系'
insert into Department values('0844131','考古系','周新新')
5 班级信息的更新
create trigger 班级信息 on Class for update as
if update(Cdirector) begin
select Inserted.Cno,Inserted.Cname,Inserted.Xno,deleted.Cdirector as 原班主任,Inserted.Cdirector as 新班主任 from Deleted,Inserted
where Deleted.Cno=Inserted.Cno end
update Class
set Cdirector='023476' where Cno='0213'
6.课程
create trigger 添加课程 on Cource for insert as
print '添加课程'
insert into Cource values('00001','大学英语',4,'033226')
7.教室信息
create trigger 添加教室 on Classroom for insert as
print'添加新的教室'
23
《数据库原理》课程设计报告 - 24 -
insert into Classroom values('0834131','C-03','04004')
8.占用信息
create trigger 占用信息更改 on Occupy for update as
if UPDATE(Usetime) begin
select inserted.Cno,inserted.Rno,inserted.Stime,deleted.Usetime as 原课时, inserted.Usetime as 新课时 from deleted,inserted
where deleted.Cno=inserted.Cno,deleted.Rno=inserted.Rno, deleted.Stime=inserted.Stime end
update Occupy set Usetime=30
where Cno='0113',Rno='113',Stime='2015-04-26'
9.授课信息
create trigger 授课信息 on Teaching for insert as
print'无法插入该信息'
insert into Teaching values('0111111','022','02','2015-4-2')
3.6 数据库实施
3.6.1 数据表截图
1.Deartment表
24
《数据库原理》课程设计报告 - 25 -
图3-1
2.Teacher 表
图 3-2
3.Class 表
图 3-3
25