某高校选课系统数据库课程设计代码及实现图

use 数据库名、查看数据库中所有的触发器 use 数据库名 go

select * from sysobjects where xtype='TR'

go

select * from sysobjects where xtype='P'

1、基本表

建管理员表：

create table Manager(Mid char(20) primary key, Mname varchar(30) not null,

Msex char(2) check (Msex in('男','女'))not null , Mbirth datetime not null, Mpassword

char(20)

null);

建院系表：

create table Department(Did char(20) primary key, Dname varchar(30) not null,Head varchar(20) not null, Dcall varchar(20) not null);

建专业表：

create table J(Jid char(20) primary key, Jname varchar(30) not null, Did char(20) not null,

not

foreign key(Did) references Department(Did));

建教师表：

create table Teacher(Tid char(20) primary key, Tname varchar(30) not null,

Tsex char(2) check (Tsex in('男','女'))not null , Tbirth datetime not null, Tpassword char(20) not null,

Did char(20) not null,limits char(20) not null, foreign key(Did) references Department(Did));

建学生表：

create table Student(Sid char(20) primary key, Sname varchar(30) not null,

Ssex char(2) check (Ssex in('男','女'))not null , Sbirth date not null, Spassword char(20) not null, Jid char(20) not null,

foreign key(Jid) references J(Jid));

建课程表：

create table Course(Cid char(20) primary key, Cname varchar(30) not null,Tid char(20) not null, Period varchar(50) not null,Credits varchar(20) not null, Cstar varchar(10) not null,Cend varchar(10) not null, foreign key(Tid) references Teacher(Tid));

建学生选课信息表：

create table SC(Sid char(20) NOT NULL, Cid char(20) NOT NULL, Grade smallint null,

foreign key(Cid) references Course(Cid), foreign key(Sid) references Student(Sid), PRIMARY KEY(Cid,Sid));

建学生选课时间控制表：

create table select_T(S_control char(2) NOT NULL check (S_control in('0','1')))

2、视图：

建某学生已选课程视图：

create view S_selected1

as select Sid,Cname,Period,Credits, Cstar,Cend,Tname from SC,Course,Teacher

where SC.Cid=Course.Cid and Course.Tid= Teacher.Tid

建某老师的某一课程被选视图：

create view T_selected3

as select Tid,Cname,SC.Sid,Sname,Ssex,Jname,Grade from SC,Student,J,Course

where SC.Sid=Student.Sid and Student.Jid=J.Jid AND Course.Cid=SC.Cid

建选课情况视图：

create view C_selected (Cname,Tname,S_sum) as select Cname,Tname,COUNT(Sid) from SC,Course,Teacher

where SC.Cid=Course.Cid AND Course.Tid=Teacher.Tid group by Cname,Tname

create view NOT_SELECT as select Cname from Course

where Cname not in(select Cname from Course,SC where Course.Cid=SC.Cid)

3、触发器

添加学生信息触发器：

create trigger tri_addStudent on Student for insert,update

as if(select COUNT(*) from J,inserted where J.Jid=inserted.Jid)=0 begin

print'未找到该学生的专业信息，请重新填写！' rollback End

create trigger tri_addTeacher on Teacher for insert,update

as if(select COUNT(*) from Department,inserted where Department.Did=inserted.Did)=0 begin

print'未找到该教师的专业信息，请重新填写！' rollback end