黄博 《面向教室信息管理数据库系统的设计与实现》 第25页共29页
); 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 '职称',dbo.course.cname 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 dbo.course.cno=dbo.teachclass.cno inner
join
dbo.department
on
dbo.department.depno=dbo.teacher.depno go
select * from teachinfo
黄博 《面向教室信息管理数据库系统的设计与实现》 第26页共29页
--创建教室信息查询视图 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 dbo.course.cno as '课程编号',dbo.course.cname 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
黄博 《面向教室信息管理数据库系统的设计与实现》 第27页共29页
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 '教室容量',
黄博 《面向教室信息管理数据库系统的设计与实现》 第28页共29页
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 '申请人编号',dbo.cborrow.usedate as '借用日期',dbo.cborrow.weekday as '借用星期',
dbo.cborrow.period as '借用阶段',dbo.cborrow.uses as '借用用途',dbo.cborrow.usestatus as '审批状态' from dbo.cborrow
黄博 《面向教室信息管理数据库系统的设计与实现》 第29页共29页
go
select * from candidates
--创建当申请者的申请条件记录表信息 useClassroomManage go
create view candidatescondition as
select dbo.classroom.capacity as '教室容量',dbo.classroom.medium as '多媒体情况',dbo.classroom.ctype as '教室类型' fromdbo.classroom go
select * from candidatescondition