华北科技学院计算机系综合性实验
实 验 报 告
课程名称 《数据库原理及应用教程》 实验学期 2011 至 2012 学年 第 2 学期 学生所在系部 计算机学院 年级 专业班级 学生姓名 学号 任课教师 郭红 实验成绩
计算机系制
《数据库原理及应用教程》课程综合性实验报告
2
3
4
5
6
7
8
9
--一、创建表 -- 建立系别表 CREATE TABLE sdept
(Dno CHAR (10) PRIMARY KEY , Dname CHAR (20) UNIQUE , Dphone CHAR (20), );
-- 建立教师表 create table teacher (Tno CHAR (10) PRIMARY KEY , Tname CHAR (20) not null,
Tsex CHAR (2) CHECK (Tsex IN (' 男' , ' 女' )), Prof CHAR (4), Tage SMALLINT , Dno CHAR (10) ,
FOREIGN KEY (Dno ) REFERENCES Sdept (Dno ) );
-- 建立学生表 CREATE TABLE student (Sno CHAR (10) PRIMARY KEY , Sname CHAR (20) NOT NULL,
Ssex CHAR (2) CHECK (Ssex IN (' 男' , ' 女' )), Sage SMALLINT ,
Dno CHAR (10), FOREIGN KEY (Dno ) REFERENCES Sdept (Dno ) );
-- 建立课程表 CREATE TABLE course
(Cno CHAR (10) PRIMARY KEY , Cname CHAR (20) UNIQUE , Credit SMALLINT , );
--建立授课表
CREATE TABLE TC (Cno CHAR (10), Tno CHAR (10), );
-- 建立选课表 CREATE TABLE SC (Sno CHAR (10) , Cno CHAR (10),
Grade SMALLINT CHECK (Grade >=0 AND Grade
10
PRIMARY KEY (Sno , Cno ),
);
----------------------------------------------------------
--学生的学号、成绩等信息查询的视图
create view V_student
as
select student . Sno , Sname , cname , Credit , grade
from student , SC , course
where student . Sno =SC . Sno and
course . Cno =SC . Cno
--教师基本信息查询的视图
create view V_teacher
as
select Tname , Prof , Dname
from teacher , sdept
where teacher . Dno =sdept . Dno
----------------------------------------------
--在教师表中系代号列上建立索引
create unique index teadno
on teacher (Tno , Dno );
--在学生表姓名列上建立索引
create unique index stusname
on student (Sno , sname );
-----------------------------------------------
--创建学生用户
create login u1
with password ='111'
use GRADE
create user 学生
for login u1
--对学生用户进行授权
grant select
on student
to 学生
grant select
on V_student
to 学生
grant select
on SC
to 学生
grant select
on TC
to 学生
grant select
on V_teacher
to 学生
--创建教师用户
create login u2
with password ='111'
use GRADE
create user 教师
for login u2
--对教师用户进行授权
grant select
on student
to 教师
grant select
on TC
to 教师
grant select , update
on teacher
to 教师
grant select , insert , update (grade )
on SC
to 教师
--创建教务管理员用户
create login u3
with password ='111'
use GRADE
create user 教务处
for login u3
--对教务管理员用户进行授权
grant select , update , insert , delete
on teacher
to 教务处
grant select , update , insert , delete
on student
to 教务处
grant select , update , insert , delete
on sdept
to 教务处
grant select , update , insert , delete
on course
to 教务处
grant select , update , insert , delete
on sdept
to 教务处
grant select , update , insert , delete
on course
to 教务处
grant select , update , insert , delete
on TC
to 教务处
grant select , update , insert , delete
on SC
to 教务处
------------------------------------------
--删除教师信息触发器
create trigger 删除教师信息
on teacher
for delete
as
if exists(select * from tc where tno =(select tno from deleted ))
begin
print 'course 表里有该教师的课程，请先删除course 表里的记录！'
rollback
end
--演示删除教师信息触发器的操作
delete
from teacher
where tno ='t1'
--删除学生信息触发器
create trigger 删除学生信息
on student
for delete
as
if exists(select * from sc where sno =(select sno from deleted ))
begin
print 'SC 表里有该学生的选课课程，请先删除SC 表里的记录！'
rollback
end
--演示删除学生信息触发器的操作
delete
from student
where sno ='s1'
--选课触发器
create trigger 选课
on SC
for insert
as
if exists(select Cno from course where cno =(select cno from inserted )) begin
print ' 选课成功'
end
--演示执行“选课”触发器
insert
into SC (sno , cno )
values ('s1' , 'c3' )
--查询选课结果
select *
from SC
--删除选课触发器
create trigger 删除选课
on SC
for DELETE
AS
if exists(select Cno from sc where cno =(select cno from deleted )) begin
print ' 删除选课成功'
end
--演示执行“删除选课”触发器
delete
from sc
where sno ='s1' and cno ='c3'
--------------------------------------------------- --查询成绩的存储过程
CREATE PROCEDURE 查询成绩 @sn char (10)
as
select student . sno , sname , cname , Credit , grade
from student , course , SC
where student . sno =sc . sno and
course . cno =sc . cno and
student . sno =@sn
--查询学号为“s1”的学生成绩
exec 查询成绩 's1'
华北科技学院计算机系综合性实验
实 验 报 告
课程名称 《数据库原理及应用教程》 实验学期 2011 至 2012 学年 第 2 学期 学生所在系部 计算机学院 年级 专业班级 学生姓名 学号 任课教师 郭红 实验成绩
计算机系制
《数据库原理及应用教程》课程综合性实验报告
2
3
4
5
6
7
8
9
--一、创建表 -- 建立系别表 CREATE TABLE sdept
(Dno CHAR (10) PRIMARY KEY , Dname CHAR (20) UNIQUE , Dphone CHAR (20), );
-- 建立教师表 create table teacher (Tno CHAR (10) PRIMARY KEY , Tname CHAR (20) not null,
Tsex CHAR (2) CHECK (Tsex IN (' 男' , ' 女' )), Prof CHAR (4), Tage SMALLINT , Dno CHAR (10) ,
FOREIGN KEY (Dno ) REFERENCES Sdept (Dno ) );
-- 建立学生表 CREATE TABLE student (Sno CHAR (10) PRIMARY KEY , Sname CHAR (20) NOT NULL,
Ssex CHAR (2) CHECK (Ssex IN (' 男' , ' 女' )), Sage SMALLINT ,
Dno CHAR (10), FOREIGN KEY (Dno ) REFERENCES Sdept (Dno ) );
-- 建立课程表 CREATE TABLE course
(Cno CHAR (10) PRIMARY KEY , Cname CHAR (20) UNIQUE , Credit SMALLINT , );
--建立授课表
CREATE TABLE TC (Cno CHAR (10), Tno CHAR (10), );
-- 建立选课表 CREATE TABLE SC (Sno CHAR (10) , Cno CHAR (10),
Grade SMALLINT CHECK (Grade >=0 AND Grade
10
PRIMARY KEY (Sno , Cno ),
);
----------------------------------------------------------
--学生的学号、成绩等信息查询的视图
create view V_student
as
select student . Sno , Sname , cname , Credit , grade
from student , SC , course
where student . Sno =SC . Sno and
course . Cno =SC . Cno
--教师基本信息查询的视图
create view V_teacher
as
select Tname , Prof , Dname
from teacher , sdept
where teacher . Dno =sdept . Dno
----------------------------------------------
--在教师表中系代号列上建立索引
create unique index teadno
on teacher (Tno , Dno );
--在学生表姓名列上建立索引
create unique index stusname
on student (Sno , sname );
-----------------------------------------------
--创建学生用户
create login u1
with password ='111'
use GRADE
create user 学生
for login u1
--对学生用户进行授权
grant select
on student
to 学生
grant select
on V_student
to 学生
grant select
on SC
to 学生
grant select
on TC
to 学生
grant select
on V_teacher
to 学生
--创建教师用户
create login u2
with password ='111'
use GRADE
create user 教师
for login u2
--对教师用户进行授权
grant select
on student
to 教师
grant select
on TC
to 教师
grant select , update
on teacher
to 教师
grant select , insert , update (grade )
on SC
to 教师
--创建教务管理员用户
create login u3
with password ='111'
use GRADE
create user 教务处
for login u3
--对教务管理员用户进行授权
grant select , update , insert , delete
on teacher
to 教务处
grant select , update , insert , delete
on student
to 教务处
grant select , update , insert , delete
on sdept
to 教务处
grant select , update , insert , delete
on course
to 教务处
grant select , update , insert , delete
on sdept
to 教务处
grant select , update , insert , delete
on course
to 教务处
grant select , update , insert , delete
on TC
to 教务处
grant select , update , insert , delete
on SC
to 教务处
------------------------------------------
--删除教师信息触发器
create trigger 删除教师信息
on teacher
for delete
as
if exists(select * from tc where tno =(select tno from deleted ))
begin
print 'course 表里有该教师的课程，请先删除course 表里的记录！'
rollback
end
--演示删除教师信息触发器的操作
delete
from teacher
where tno ='t1'
--删除学生信息触发器
create trigger 删除学生信息
on student
for delete
as
if exists(select * from sc where sno =(select sno from deleted ))
begin
print 'SC 表里有该学生的选课课程，请先删除SC 表里的记录！'
rollback
end
--演示删除学生信息触发器的操作
delete
from student
where sno ='s1'
--选课触发器
create trigger 选课
on SC
for insert
as
if exists(select Cno from course where cno =(select cno from inserted )) begin
print ' 选课成功'
end
--演示执行“选课”触发器
insert
into SC (sno , cno )
values ('s1' , 'c3' )
--查询选课结果
select *
from SC
--删除选课触发器
create trigger 删除选课
on SC
for DELETE
AS
if exists(select Cno from sc where cno =(select cno from deleted )) begin
print ' 删除选课成功'
end
--演示执行“删除选课”触发器
delete
from sc
where sno ='s1' and cno ='c3'
--------------------------------------------------- --查询成绩的存储过程
CREATE PROCEDURE 查询成绩 @sn char (10)
as
select student . sno , sname , cname , Credit , grade
from student , course , SC
where student . sno =sc . sno and
course . cno =sc . cno and
student . sno =@sn
--查询学号为“s1”的学生成绩
exec 查询成绩 's1'