教室管理系统数据库文件.zip

/*create database ClassroomManagers on( name=ClassroomManager_data, filename='C:\aa\ClassroomManager_data.mdf', size=20MB, maxsize=100MB, filegrowth=10% ) log on( name=ClassroomManager_log, filename='C:\aa\ClassroomManager_log.ldf', size=2MB, maxsize=10MB, filegrowth=10% )*/ /*use ClassroomManagers go --教室信息表 create table classroom( Js_no int not null primary key, --教室编号 Js_ty char(20) null, --院办教室/教学教室 Js_ca char(10) null, --容纳学生数 Js_na char(10) null, --教室简称 ); insert into classroom(Js_no,Js_ty,Js_ca,Js_na)values('1102','教学教室' ,'<126','南_1102') insert into classroom(Js_no,Js_ty,Js_ca,Js_na)values('501','院办教室' ,'<56','材_501') insert into classroom(Js_no,Js_ty,Js_ca,Js_na)values('508','院办教室' ,'<56','材_508') insert into classroom(Js_no,Js_ty,Js_ca,Js_na)values('102','院办教室' ,'<56','物_102') insert into classroom(Js_no,Js_ty,Js_ca,Js_na)values('204','院办教室' ,'<63','物_204') insert into classroom(Js_no,Js_ty,Js_ca,Js_na)values('206','院办教室' ,'<63','物_206') insert into classroom(Js_no,Js_ty,Js_ca,Js_na)values('302','院办教室' ,'<63','物_302') insert into classroom(Js_no,Js_ty,Js_ca,Js_na)values('402','院办教室' ,'<63','物_402') insert into classroom(Js_no,Js_ty,Js_ca,Js_na)values('404','院办教室' ,'<63','物_404') insert into classroom(Js_no,Js_ty,Js_ca,Js_na)values('502','院办教室' ,'<63','环_502') insert into classroom(Js_no,Js_ty,Js_ca,Js_na)values('1203','教学教室' ,'<126','南_1203') insert into classroom(Js_no,Js_ty,Js_ca,Js_na)values('1204','教学教室' ,'<126','南_1204') insert into classroom(Js_no,Js_ty,Js_ca,Js_na)values('3104','教学教室' ,'<126','北_3104') insert into classroom(Js_no,Js_ty,Js_ca,Js_na)values('3303','教学教室' ,'<126','北_3303') insert into classroom(Js_no,Js_ty,Js_ca,Js_na)values('2304','教学教室' ,'<126','北_2304') insert into classroom(Js_no,Js_ty,Js_ca,Js_na)values('403','院办教室' ,'<58','环_403') --院系信息表 create table department( Yx_no int not null primary key, Yx_na char(20) null ); insert into department(Yx_no,Yx_na)values('036','化工与能源学院') insert into department(Yx_no,Yx_na)values('024','信息工程学院') insert into department(Yx_no,Yx_na)values('077','软件学院') insert into department(Yx_no,Yx_na)values('023','外语学院') insert into department(Yx_no,Yx_na)values('034','数学与统计学院') insert into department(Yx_no,Yx_na)values('026','物理工程学院') insert into department(Yx_no,Yx_na)values('054','机械工程学院') insert into department(Yx_no,Yx_na)values('033','土木工程学院') --管理员表 use ClassroomManagers go create table login( name char(8) not null primary key, pwd char(8) null ); insert into login(name,pwd)values('qwertyui','12345678') insert into login(name,pwd)values('asdfghjk','12345678') insert into login(name,pwd)values('zxcvbnm,','12345678') insert into login(name,pwd)values('123','123')*/ --院系教师学生表 create table numts( Te_nu char(20) not null, St_nu char(20) not null, Yx_no int not null primary key, foreign key (Yx_no) references department(Yx_no) ); insert into numts(Yx_no,Te_nu,St_nu)values('036','1','2') insert into numts(Yx_no,Te_nu,St_nu)values('024','2','3') insert into numts(Yx_no,Te_nu,St_nu)values('077','1','4') insert into numts(Yx_no,Te_nu,St_nu)values('023','2','4') insert into numts(Yx_no,Te_nu,St_nu)values('034','1','1') insert into numts(Yx_no,Te_nu,St_nu)values('026','2','1') insert into numts(Yx_no,Te_nu,St_nu)values('054','1','2') insert into numts(Yx_no,Te_nu,St_nu)values('033','2','1') --教师信息表 create table teacher( Te_no int not null primary key, Te_na char(20) null, Te_se char(10) null check(Te_se='男'or Te_se='女'), Yx_no int not null, foreign key (Yx_no) references department(Yx_no) ); insert into teacher(Te_no,Te_na,Te_se,Yx_no)values('23444','张三','男','023') insert into teacher(Te_no,Te_na,Te_se,Yx_no)values('23445','李四','男','024') insert into teacher(Te_no,Te_na,Te_se,Yx_no)values('23446','王五','女','026') insert into teacher(Te_no,Te_na,Te_se,Yx_no)values('23447','陈麻','男','033') insert into teacher(Te_no,Te_na,Te_se,Yx_no)values('23448','赵甲','男','034') insert into teacher(Te_no,Te_na,Te_se,Yx_no)values('23449','钱乙','男','036') insert into teacher(Te_no,Te_na,Te_se,Yx_no)values('23450','孙丙','女','054') insert into teacher(Te_no,Te_na,Te_se,Yx_no)values('23451','李丁','女','077') insert into teacher(Te_no,Te_na,Te_se,Yx_no)values('23452','周辰','男','023') insert into teacher(Te_no,Te_na,Te_se,Yx_no)values('23453','吴巳','女','024') insert into teacher(Te_no,Te_na,Te_se,Yx_no)values('23454','郑午','男','026') insert into teacher(Te_no,Te_na,Te_se,Yx_no)values('23455','王未','女','033') --学生信息表 create table student( St_no int not null primary key, St_na char(20) null, St_se char(10) null check(St_se='男'or St_se='女'), Yx_no int not null, foreign key (Yx_no) references department(Yx_no) ); insert into student(St_no,St_na,St_se,Yx_no)values('15234','甲子','男','023') insert into student(St_no,St_na,St_se,Yx_no)values('15235','乙丑','男','026') insert into student(St_no,St_na,St_se,Yx_no)values('15236','丙寅','男','024') insert into student(St_no,St_na,St_se,Yx_no)values('15237','丁卯','男','033') insert into student(St_no,St_na,St_se,Yx_no)values('15238','戊辰','女','054') insert into student(St_no,St_na,St_se,Yx_no)values('15239','己巳','女','023') insert into student(St_no,St_na,St_se,Yx_no)values('15240','庚午','女','077') insert into student(St_no,St_na,St_se,Yx_no)values('15241','辛未','男','034') insert into student(St_no,St_na,St_se,Yx_no)values('15242','壬申','女','024') insert into student(St_no,St_na,St_se,Yx_no)values('15243','癸酉','女','077') insert into student(St_no,St_na,St_se,Yx_no)values('15244','甲戌','男','023') insert into student(St_no,St_na,St_se,Yx_no)values('15245','乙亥','女','054') insert into student(St_no,St_na,St_se,Yx_no)values('15246','丙子','女','077') insert into student(St_no,St_na,St_se,Yx_no)values('15247','丁丑','男','023') insert into student(St_no,St_na,St_se,Yx_no)values('15248','戊寅','女','077') insert into student(St_no,St_na,St_se,Yx_no)values('15249','己卯','女','036') insert into student(St_no,St_na,St_se,Yx_no)values('15250','庚辰','男','024') insert into student(St_no,St_na,St_se,Yx_no)values('15251','辛巳','女','036') --课程信息表 create table course( Co_no int not null primary key, Co_na char(20) null, Co_ty char(10) null, Yx_no int not null, foreign key (Yx_no) references department(Yx_no) ); insert into course(Co_no,Co_na,Co_ty,Yx_no)values('3241','AAAA','选修','023') insert into course(Co_no,Co_na,Co_ty,Yx_no)values('3242','BBBB','必修','036') insert into course(Co_no,Co_na,Co_ty,Yx_no)values('3243','CCCC','必修','024') insert into course(Co_no,Co_na,Co_ty,Yx_no)values('3244','DDDD','选修','054') insert into course(Co_no,Co_na,Co_ty,Yx_no)values('3245','EEEE','必修','077') insert into course(Co_no,Co_na,Co_ty,Yx_no)values('3246','FFFF','选修','033') insert into course(Co_no,Co_na,Co_ty,Yx_no)values('3247','GGGG','必修','026') insert into course(Co_no,Co_na,Co_ty,Yx_no)values('3248','HHHH','选修','034') --排课信息表 create table class( Yx_no int not null, --院系编号 Js_no int not null, --教室编号 Te_no int not null, --教师编号 Co_no int not null, --课程编号 Sk_ti char(10) not null, --上课时间 Sk_da char(10) not null, --上课星期 primary key(Js_no,Sk_ti,Sk_da), foreign key (Yx_no) references department(Yx_no), foreign key (Js_no) references classroom(Js_no), foreign key (Te_no) references teacher(Te_no), foreign key (Co_no) references course(Co_no) ); insert into class(Yx_no,Js_no,Te_no,Co_no,Sk_ti,Sk_da)values('036','1102','23449' ,'3242','1-2节','周一') insert into class(Yx_no,Js