create database ClassroomManage
on(
name=ClassroomManage_data,
filename='D:\sqldata\ClassroomManage_data.mdf',
size=20MB,
maxsize=100MB,
filegrowth=10%
)
log on(
name=ClassroomManage_log,
filename='D:\sqldata\ClassroomManage_log.ldf',
size=2MB,
maxsize=10MB,
filegrowth=10%
)
use ClassroomManage
go
--教室信息
create table classroom (
clno char(20) not null primary key ,--教室编号
ctype char(20) null,--教室类型（阶梯教室、普通教室）
capacity char(10) null,--教室容量
medium char(10) null--教室多媒体
);
insert into classroom(clno,ctype,capacity,medium )values('J1-111','阶梯教室','<300,>150','有多媒体')
insert into classroom(clno,ctype,capacity,medium )values('J1-202','普通教室','<150','无多媒体')
insert into classroom(clno,ctype,capacity,medium )values('J2-204','阶梯教室','<300,>150','有多媒体')
insert into classroom(clno,ctype,capacity,medium )values('J2-411','普通教室','<150','无多媒体')
insert into classroom(clno,ctype,capacity,medium )values('J3-101','普通教室','<150','有多媒体')
insert into classroom(clno,ctype,capacity,medium )values('J3-502','阶梯教室','<300,>150','有多媒体')
--院系信息
create table department (
depno char(20) not null primary key ,
depname char(20) null
);
insert into department(depno,depname)values('101010','计算机科学与技术学院')
insert into department(depno,depname)values('101011','公共管理学院')
--课程信息
create table course(cno char(20) not null primary key ,
cname char(20) null,
credit char(1) null,
category char(20) null,--课程类型号
depno char(20) not null,--开课院系号
foreign key (depno) references department(depno)
);
insert into course(cno,cname,credit,category,depno)values('1010101','C语言程序设计','2','必修课','101010')
insert into course(cno,cname,credit,category,depno)values('1010102','汇编语言','2','必修课','101010')
insert into course(cno,cname,credit,category,depno)values('1010103','JAVA程序设计','2','必修课','101010')
insert into course(cno,cname,credit,category,depno)values('1010104','数据库原理','2','必修课','101010')
insert into course(cno,cname,credit,category,depno)values('1010105','多媒体技术','2','必修课','101010')
insert into course(cno,cname,credit,category,depno)values('1010110','马克思主义基本原理','2','公共课','101011')
insert into course(cno,cname,credit,category,depno)values('1010111','中国近现代史','2','公共课','101011')
--教师信息
create table teacher(tno char(20) not null primary key ,
tname char(20) null,
sex char(2) null check(sex='男'or sex=
'女'),
depno char(20) not null,
title char(10) null,--职称(教授、讲师...)
tid char(20) null,--身份证号
foreign key (depno) references department(depno)
);
insert into teacher(tno,tname,sex,depno,title,tid)values('10101080','张华','男','101010','教授','320481************')
insert into teacher(tno,tname,sex,depno,title,tid)values('10101081','李华','男','101010','教授','320481************')
insert into teacher(tno,tname,sex,depno,title,tid)values('10101082','张兴华','男','101010','教授','320581************')
insert into teacher(tno,tname,sex,depno,title,tid)values('10101170','李丽华','女','101011','讲师','321581************')
insert into teacher(tno,tname,sex,depno,title,tid)values('10101171','赵兴磊','男','101011','副教授','320481************')
--学生信息
create table student(sno char(20) not null primary key ,
sname char(20) null,
depno char(20)not null,
sid char(20)null,--身份证号
foreign key (depno) references department(depno)
);
insert into student(sno,sname,depno,sid)values('10101061','赵磊','101010','320481************')
insert into student(sno,sname,depno,sid)values('10101062','李星','101010','321481************')
insert into student(sno,sname,depno,sid)values('10101063','张雨欣','101010','323481************')
insert into student(sno,sname,depno,sid)values('10101064','叶欣','101010','32448199105025689')
insert into student(sno,sname,depno,sid)values('10101065','赵磊','101010','325481************')
--教师开课信息
create table teachclass (tno char(20) not null,--教师号
clno char(20) not null,--教室编号
cno char(20) not null,--课程号
number char(20)null,--选该教师的学生人数（确定教室的容量）
depno char(20)not null,
weekday char(8)not null,--星期
period char(20)not null,--上课时间段
primary key(clno,weekday,period),
foreign key (depno) references department(depno),
foreign key (tno) references teacher(tno),
foreign key (clno) references classroom(clno),
foreign key (cno) references course(cno)
);
insert into teachclass(tno,clno,cno,number,depno,weekday,period)values('10101080','J1-111','1010101','150','101010','星期一','1-3节')
insert into teachclass(tno,clno,cno,number,depno,weekday,period)values('10101080','J1-202','1010102','80','101010','星期二','4-5节')
insert into teachclass(tno,clno,cno,number,depno,weekday,period)values('10101081','J2-204','1010103','200','101010','星期三','6-7节')
insert into teachclass(tno,clno,cno,number,depno,weekday,period)values('1
0101082','J2-411','1010104','80','101010','星期四','8-9节')
insert into teachclass(tno,clno,cno,number,depno,weekday,period)values('10101170','J3-101','1010110','100','101011','星期五','4-5节')
insert into teachclass(tno,clno,cno,number,depno,weekday,period)values('10101171','J3-502','1010111','100','101011','星期一','6-7节')
--借教室
create table cborrow (clno char(20) not null,
sno char(20)not null,--借用教室人员编号
usedate char(10)not null,--使用教室的日期
weekday char(8) null,--使用教室的星期
period char(20) not null,--使用教室的时间段
uses char(100)null,--教室的用途
usestatus char(10)null,--审批状态
primary key(clno,sno,usedate,period),
foreign key (clno) references classroom(clno),
foreign key (sno) references student(sno),
);
insert into cborrow(clno,sno,usedate,weekday,period,uses, usestatus)values('J1-202','10101062','2011-3-1','星期二','4-5节','上课','审批中')
insert into cborrow(clno,sno,usedate,weekday,period,uses, usestatus)values('J2-204','10101063','2011-3-2','星期三','6-7节','上课','审批中')
insert into cborrow(clno,sno,usedate,weekday,period,uses, usestatus)values('J1-111','10101064','2011-2-28','星期二','1-3节','上课','审批通过')
insert into cborrow(clno,sno,usedate,weekday,period,uses, usestatus)values('J3-101','10101065','2011-3-4','星期五','4-5节','上课','审批通过')
insert into cborrow(clno,sno,usedate,weekday,period,uses, usestatus)values('J3-502','10101061','2011-3-7','星期一','6-7节','上课','审批中')
--创建教师开课信息查询视图
use ClassroomManage
go
create view teachinfo
as
select dbo.teacher.tno as '教师编号',dbo.teacher.tname as '教师姓名',dbo.teacher.sex as '性别',
dbo.department.depname as '所属院系',dbo.teacher.title as '职称',https://www.doczj.com/doc/e416972671.html,ame as '课程名称',
dbo.teachclass.clno as '开课教室',dbo.teachclass.weekday as '开课星期',dbo.teachclass.period as '上课节次'
from dbo.teacher inner join dbo.teachclass on dbo.teacher.tno=dbo.teachclass.tno
inner join dbo.course on https://www.doczj.com/doc/e416972671.html,o=https://www.doczj.com/doc/e416972671.html,o
inner join dbo.department on dbo.department.depno=dbo.teacher.depno
go
select * from teachinfo
--创建教室信息查询视图
use ClassroomManage
go
create view classroominfo
as
select dbo.classroom.clno as '教室编号',dbo.classroom.ctype as '教室类型',dbo.classroom.capacity as '教室容量',
dbo.classroom.medium as '有无多媒体情况'
from dbo.classroom
go
select * from classroominfo
--创建课程信息查询视图
use ClassroomManage
go
create view courseinfo
as
select https://www.doczj.com/doc/e416972671.html,o as '课程编号',https://www.doczj.com/doc/e416972671.html,ame as '课程名称',dbo.course.credit as
'课程学分',
dbo.course.category as '课程类别',dbo.department.depname as '课程所属院系'
from dbo.course inner join dbo.department on dbo.course.depno=dbo.department.depno
go
select * from courseinfo
--创建需要大教室的教室信息查询视图
use ClassroomManage
go
create view classroomneedbig
as
select dbo.classroom.clno as '教室编号',dbo.classroom.ctype as '教室类型',dbo.classroom.capacity as '教室容量',
dbo.classroom.medium as '有无多媒体情况'
from dbo.classroom where dbo.classroom.ctype='阶梯教室'and dbo.classroom.capacity='<300,>150'and dbo.classroom.medium='无多媒体'
go
select * from classroomneedbig
--创建需要多媒体普通教室的教室信息查询视图
use ClassroomManage
go
create view classroomneedmedium
as
select dbo.classroom.clno as '教室编号',dbo.classroom.ctype as '教室类型',dbo.classroom.capacity as '教室容量',
dbo.classroom.medium as '有无多媒体情况'
from dbo.classroom where dbo.classroom.ctype='普通教室'and dbo.classroom.capacity='<150'and dbo.classroom.medium='有多媒体'
go
select * from classroomneedmedium
--创建需要多媒体阶梯教室的教室信息查询视图
use ClassroomManage
go
create view classroomneedbigmedium
as
select dbo.classroom.clno as '教室编号',dbo.classroom.ctype as '教室类型',dbo.classroom.capacity as '教室容量',
dbo.classroom.medium as '有无多媒体情况'
from dbo.classroom where dbo.classroom.ctype='阶梯教室'and dbo.classroom.capacity='<300,>150'and dbo.classroom.medium='有多媒体'
go
select * from classroomneedbigmedium
--创建不需要多媒体普通教室的教室信息查询视图
use ClassroomManage
go
create view classroomneedsmall
as
select dbo.classroom.clno as '教室编号',dbo.classroom.ctype as '教室类型',dbo.classroom.capacity as '教室容量',
dbo.classroom.medium as '有无多媒体情况'
from dbo.classroom where dbo.classroom.ctype='普通教室'and dbo.classroom.capacity='<150'and dbo.classroom.medium='无多媒体'
go
select * from classroomneedsmall
--创建当申请者按条件借教室时的关于申请者的部分信息
use ClassroomManage
go
create view candidates
as
select dbo.cborrow.sno as '申请人编号',https://www.doczj.com/doc/e416972671.html,edate as '借用日期',dbo.cborrow.weekday as '借用星期',
dbo.cborrow.period as '借用阶段',https://www.doczj.com/doc/e416972671.html,es as '借用用途',https://www.doczj.com/doc/e416972671.html,estatus as '审批状态'
from dbo.cborrow
go
select * from candidates
--创建当申请者的申请条件记录表信息
use ClassroomManage
go
create view candidatescondition
as
select dbo.classroom.capacity as '教室容量',dbo.classroom.medium as '多媒体情况',dbo.classroom.ctype as '教室类型'
from dbo.classroom
go
select * from candidatescondition