黄博 《面向教室信息管理数据库系统的设计与实现》 第20页共29页
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','阶梯教室
黄博 《面向教室信息管理数据库系统的设计与实现》 第21页共29页
','<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')
黄博 《面向教室信息管理数据库系统的设计与实现》 第22页共29页
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','教授','320481196505026829')
insert into teacher(tno,tname,sex,depno,title,tid)values('10101081','李华','男','101010','教授','320481196607026819')
insert into teacher(tno,tname,sex,depno,title,tid)values('10101082','张兴华','男','101010','教授','320581196506027829')
insert into teacher(tno,tname,sex,depno,title,tid)values('10101170','李丽华','女','101011','讲师','321581196505225829')
insert into teacher(tno,tname,sex,depno,title,tid)values('10101171','赵兴磊','男','101011','副教授','320481196505026829') --学生信息
create table student(sno char(20) not null primary key ,
黄博 《面向教室信息管理数据库系统的设计与实现》 第23页共29页
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','320481199105025689') insert
into
student(sno,sname,depno,sid)values('10101062','
李
星
','101010','321481199005026689') insert
into
student(sno,sname,depno,sid)values('10101063','
张
雨
欣
','101010','323481199112029689') insert
into
student(sno,sname,depno,sid)values('10101064','
叶
欣
','101010','32448199105025689') insert
into
student(sno,sname,depno,sid)values('10101065','
赵
磊
','101010','325481199111025689') --教师开课信息
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) );
黄博 《面向教室信息管理数据库系统的设计与实现》 第24页共29页
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('10101082','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),