数据库-教务管理系统-综合实验报告

华北科技学院计算机系综合性实验

实验报告

课程名称《数据库原理及应用教程》

实验学期2011 至2012 学年第 2 学期学生所在系部计算机学院

年级专业班级

学生姓名学号

任课教师郭红

实验成绩

计算机系制

《数据库原理及应用教程》课程综合性实验报告

--一、创建表

-- 建立系别表

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<=100),

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

https://www.360docs.net/doc/2d2455956.html,o=https://www.360docs.net/doc/2d2455956.html,o

--教师基本信息查询的视图

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

https://www.360docs.net/doc/2d2455956.html,o=https://www.360docs.net/doc/2d2455956.html,o and

student.sno=@sn

--查询学号为“s1”的学生成绩

exec查询成绩's1'