自动排课系统——数据库大作业说课材料.doc

自动排课系统——数据库大作业

精品文档
自动排课系统
一． 系统需求
1 该“自动排课系统”只是对数据库应用技术的一个样本数据库的实例，重在对数据库一些方法的熟悉与掌握，其中包括从数据库的概念模型到ER图的绘制，再到数据库模式的建立（即为数据库及其基本表的建立），而后数据的录入（在此从略），最后完成一些简单的关系运算表达式的表达和相关的Select 查询语句的使用。
2 系统结构简述：
2.1 本系统包括六个实体：课程，学生，教室，教师，学院，班级
2.2 实体之间的多对多联系：教学计划，选课
2.2.1 一个学生可选多门课，一门课可被多个学生选修。
2.2.2 一门课可使用多个教室，一个教室可被多门课占用。
2.3 实体之间的一对多联系：教学，隶属，从属，属于，开设
2.3.1 一个学院有多个教师，一个教师只隶属于一个学院。
2.3.2 一个学院有多个班级，一个班级只隶属于一个学院。
2.3.3 一个学院可开设多门课程，一门课程只能被一个学院开设。
2.3.4 一个班级有多名学生，一个学生只隶属于一个班级。
2.3.5 一个老师可教多门课程，一门课程只能被一个老师教。
实体之间的关系转换为其他三个表：教学（一对多），教学计划（多对多），选课（多对多）。
注：各个基本表中关键字已在关系模式中说明。
3 经过分析，得到自动排课系统的如下功能：
3.1 部分属性插入时添加默认值。
3.1.1 为选课表中属性是否重修创建并绑定默认值’NO’。
3.1.2 为教师表中属性是否可用创建并绑定默认值’YES’。
3.2 对教学计划表插入数据时检查数据是否合理。
3.2.1 当插入课程人数多于教室容量的数据时，无法插入数据。
3.2.2 当插入课程占用不可被占用的教室的数据时，无法插入数据。
3.3 部分表添加唯一约束，使插入数据的数据值受到限制。
3.3.1 教学计划表中，每一门课程在一天只能安排一次。
3.3.2 教学表中，一门课程只能被一个老师教。
3.4 数据检索
3.4.1 学生查询所选某门课程的成绩。
输入：学生学号，课程编号
输出：课程名称，成绩
3.4.2 教师查询选修所教课程的学生的信息。
输入：教师编号
输出：学生姓名，学生学号，学生性别，籍贯
3.4.3 学生查询选修课程授课教师的信息。
输入：学生学号
输出：课程编号，教师姓名，教师编号，授课效果，从教时间
3.4.4 学生查询所有选修课程的平均分。
输入：学生学号
输出：选课科目数，平均分
3.4.5 教室查询占用教室课程的信息。
输入：教室编号
输出：教室编号，课程名称，课程编号，教师名称，教师编号，
3.5 数据插入
3.5.1 教室数据插入。
3.5.2 学院数据插入。
3.5.3 教学数据插入。
3.5.4 学生数据插入。
3.6 数据修改
3.6.1 教学计划数据修改：修改教学计划时，输入课程编号及所需修改的属性值，完成对教学计划表的修改。
3.6.2 教师数据修改：修改教师数据时，输入教师编号及所需修改的属性值，完成对教师表的修改。
3.6.3 班级数据修改：修改班级数据时，输入班级编号及所需修改的属性值，完成对班级表的修改。
3.7 数据统计与查询
3.7.1 学生及其选课的所有信息
3.7.2 教师教授课程情况的所有信息
3.7.3 教室安排情况的所有信息
3.7.4 学院_班级_学生间联系的所有信息
二． 概念设计
经需求分析，抽象出以下E-R模型。
图1 课程实体
课程
课程名称
课时数
学分
课程编号
课程容量
课程类型
1. 课程实体（图1）

2. 学生实体（图2）
学生
出生日期
籍贯
学生性别
学生年龄
学生学号
学生姓名

图2 学生实体

3. 教师实体（图3）
教师
教师年龄
教师性别
教师编号
教师姓名

图3教师实体

4. 教室实体（图4）
教室
教室编号
是否可用
教室容量

教室
教室编号
是否可用
教室容量
图4 教室实体

5. 学院实体（图5）

图5 学院实体
6. 班级实体（图6）

图6 班级实体
7. 全局E-R图（图7）

图7全局E-R图

三． 逻辑设计
系统中的实体有：课程、教师、教室、学生、学院、班级
将实体类型转换为关系模式如下：
课程（课程名称，课程编号，课程类型，课程容量，学分，课时数，所属学院编号）
教师（教师姓名，教师编号，教师年龄，教师性别，隶属学院编号）
教室（教室编号，教室容量，是否可用）
学生（学生姓名，学生学号，学生年龄，出生日期，学生性别，籍贯，所在班级编号）
学院（学院名称，学院编号，联系电话，通讯地址）
班级（班级编号，人数，所属年级，班导师，所在学院编号）

系统中的联系有：教学计划（多对多）、教授（一对多）、选修（多对多）
将联系类型转换为关系模式如下：
教学计划（课程编号，教室编号，起始周数，周数，起始节数，节数，星期），主键为（课程编号，教室编号）
教学（课程编号，教师编号，从教时间，授课效果），主键为（课程编号，教师编号）
选课（课程编号，学生学号，是否重修，成绩），主键为（课程编号，学生学号）
注：有下划线的是主键，有波浪线的是外键。
四． 物理设计
根据以上关系模式构建数据表如下：

表1 课程表（course）结构

表2 教师表（teacher）结构

表3 教室表（room）结构

表4 学生表（student）结构

表5 学院表（school）结构

表6 班级表（class）结构

表7 教学计划表（eduplan）结构

表8 教学表（education）结构

表9 选课表（sc）结构

五． 系统实现
自动排课系统的系统实现Transact-SQL语句如下：
1.建立数据库
create database Auto_Arrangement
on primary
(name=Auto_Arrangement_data,filename='e:\\Auto_Arrangement\\Auto_Arrangement_data.mdf',size=10MB,filegrowth=2MB,maxsize=500MB),
(name=Auto_Arrangement_data1,filename='e:\\Auto_Arrangement\\Auto_Arrangement_data1.ndf',size=5MB,filegrowth=1MB,maxsize=500MB)
log on
(name=Auto_Arrangement_log,filename='e:\\Auto_Arrangement\\Auto_Arrangement_log.ldf',size=1MB,filegrowth=10%)

2.基本表的创建
use Auto_Arrangement

2.1.教室表的创建
create table room(
RoomNumber char(10) primary key,
RoomVolume smallint not null,
Available char(4),
check(RoomVolume>=0),
check(Available='YES'or Available='NO'))

2.2.学院表的创建
create table school(
SchoolName char(30) not null,
SchoolNumber char(10) primary key,
Address char(50),
Tel char(20))

2.3.课程表的创建
create table course(
CourseName char(10) not null,
CourseNumber char(10) primary key,
CourseType char(10),
CourseVolume smallint not null,
Credit smallint not null,
Period smallint not null,
SchoolNumber char(10),
foreign key (SchoolNumber) references school(SchoolNumber))

2.4.教师表的创建
create table teacher(
TeacherName char(10),
TeacherNumber char(10) primary key,
TeacherAge char(4),
TeacherSex char(6),
SchoolNumber char(10),
check(TeacherAge>=0),
check(TeacherSex='Male'or TeacherSex='Female'),
foreign key (SchoolNumber) references school(SchoolNumber))

2.5.课程表的创建
create table class(
ClassNumber char(10) primary key,
ClassVolume smallint,
CGrade smallint not null,
Tutor char(10),
SchoolNumber char(10),
check(ClassVolume>=0),
foreign key (SchoolNumber) references school(SchoolNumber))

2.6.学生表的创建
create table student(
StudentName char(10) not null,
StudentNumber char(10) primary key,
StudentSex char(6),
StudentAge smallint,
Origo char(10),
Birthday datetime,
ClassNumber char(10),
check(StudentSex='Male'or StudentSex='Female'),
check(StudentAge>=0),
foreign key (ClassNumber) references class(ClassNumber))

2.7.教学计划表的创建
create table eduplan(
CourseNumber char(10),
RoomNumber char(10),
StartWeek smallint,
Weeks smallint,
StartLesson smallint,
Lessons smallint,
EduDay smallint,
check(StartWeek between 1 and 20),
check(Weeks>=0),
check(StartLesson between 1 and 11),
check(Lessons>=0),
check(EduDay between 1 and 7),
primary key (CourseNumber,RoomNumber),
foreign key (CourseNumber) references course(CourseNumber),
foreign key (RoomNumber) references room(RoomNumber))

2.8.教学表的创建
create table education(
CourseNumber char(10),
TeacherNumber char(10),
TeachTime smallint,
Effect smallint,
check(TeachTime between 1 and 100),
check(Effect between 1 and 100),
primary key (CourseNumber,TeacherNumber),
foreign key (CourseNumber) references course(CourseNumber),
foreign key (TeacherNumber) references teacher(TeacherNumber))

2.9.选课表的创建
create table sc(
CourseNumber char(10),
StudentNumber char(10),
Revamp char(4),
Grade smallint,
check(Revamp='YES'or Revamp='NO'),
check(Grade between 1 and 100),
primary key (CourseNumber,StudentNumber),
foreign key (CourseNumber) references course(CourseNumber),
foreign key (StudentNumber) references student(StudentNumber))

3.默认值的创建
3.1.创建默认值’NO’，并为选课表中是否重修绑定
create default de_Revamp as'NO'
exec sp_bindefault 'de_Revamp','sc.Revamp'

3.2.创建默认值’YES’，并为教室表中是否可用绑定
create default de_Available as'YES'
exec sp_bindefault 'de_Available','room.Available'

4.触发器的创建
4.1.对教学计划表创建触发器，当插入课程人数多于教室容量的数据时，回滚操作
create trigger TR_eduplan1 on eduplan
for insert
as
if(
select count(*)
from course c inner join inserted i on c.CourseNumber=i.CourseNumber
inner join room r on i.RoomNumber=r.RoomNumber
where datediff(d,CourseVolume,RoomVolume)<0
)<>0
begin
print'课程人数大于教室容量，无法安排课程，操作无法完成'
rollback
end

4.2.对教学计划表创建触发器，当插入课程占用不可被占用的教室的数据时，回滚操作
create trigger TR_eduplan2 on eduplan
for insert
as
if(
select count(*)
from inserted i inner join room r on i.RoomNumber=r.RoomNumber
where available='NO'
)<>0
begin
print'插入课程占用不可被占用的教室，无法安排课程，操作无法完成'
rollback
end

5.数据的输入
5.1.教室表数据
insert into room (RoomNumber,RoomVolume) values('23102',280)
insert into room (RoomNumber,RoomVolume) values('23104',280)
insert into room (RoomNumber,RoomVolume) values('23106',280)
insert into room values('23108',280,'NO')
insert into room (RoomNumber,RoomVolume) values('23110',100)
insert into room (RoomNumber,RoomVolume) values('23112',100)
insert into room (RoomNumber,RoomVolume) values('23114',280)
insert into room (RoomNumber,RoomVolume) values('23202',280)
insert into room (RoomNumber,RoomVolume) values('23204',280)
insert into room (RoomNumber,RoomVolume) values('23206',280)
insert into room values('23208',280,'NO')
insert into room (RoomNumber,RoomVolume) values('23210',280)
insert into room (RoomNumber,RoomVolume) values('23212',280)
insert into room (RoomNumber,RoomVolume) values('23214',280)
5.2.学院表数据
insert into school values('自动化','001','天津市南开区号','22345612')
insert into school (SchoolName,SchoolNumber) values('精密仪器','002')
insert into school (SchoolName,SchoolNumber) values('机械','003')
insert into school (SchoolName,SchoolNumber) values('建筑','004')
insert into school (SchoolName,SchoolNumber) values('建工','005')
insert into school (SchoolName,SchoolNumber) values('信息','006')
insert into school (SchoolName,SchoolNumber) values('化工','007')
insert into school (SchoolName,SchoolNumber) values('计算机','008')
insert into school (SchoolName,SchoolNumber) values('理学院','009')
insert into school (SchoolName,SchoolNumber) values('文法','010')
5.3.课程表数据
insert into course values('复变','0001','必修',280,2,32,'009')
insert into course values('模电','0002','必修',100,4,64,'001')
insert into course values('信号','0003','必修',100,3,48,'001')
insert into course values('电拖','0004','必修',100,3,48,'001')
insert into course values('概率','0005','选修',280,3,32,'009')
insert into course values('英语','0006','必修',80,1,16,'010')
insert into course values('口语','0007','必修',40,1,16,'010')
insert into course values('数据库','0008','选修',280,3,32,'008')
insert into course values('电路','0009','必修',100,4,48,'001')
insert into course values('大物','0010','必修',100,4,64,'009')
insert into course values('马原','0011','必修',280,4,64,'010')
5.4.班级表数据
insert into class values('zdh01',30,2012,'许','001')
insert into class (ClassNumber,ClassVolume,CGrade,SchoolNumber) values('zdh02',30,2012,'001')
insert into class (ClassNumber,ClassVolume,CGrade,SchoolNumber) values('zdh03',30,2012,'001')
insert into class (ClassNumber,ClassVolume,CGrade,SchoolNumber) values('zdh04',30,2012,'001')
insert into class (ClassNumber,ClassVolume,CGrade,SchoolNumber) values('zdh05',30,2012,'001')
insert into class (ClassNumber,ClassVolume,CGrade,SchoolNumber) values('zdh06',30,2012,'001')
insert into class (ClassNumber,ClassVolume,CGrade,SchoolNumber) values('jsj01',30,2012,'008')
insert into class (ClassNumber,ClassVolume,CGrade,SchoolNumber) values('jsj02',30,2012,'008')
insert into class (ClassNumber,ClassVolume,CGrade,SchoolNumber) values('jsj03',30,2012,'008')
insert into class (ClassNumber,ClassVolume,CGrade,SchoolNumber) values('jsj04',30,2012,'008')
5.5.教师表数据
insert into teacher values('韩','001','35','Male','009')
insert into teacher values('周','002','40','Female','001')
insert into teacher values('石','003','45','Male','001')
insert into teacher values('费','004','43','Male','001')
insert into teacher values('胡','005','37','Male','009')
insert into teacher values('田','006','43','Female','010')
insert into teacher values('姚','007','45','Male','010')
insert into teacher values('喻','008','36','Female','008')
insert into teacher values('刘','009','30','Female','001')
insert into teacher values('李','010','31','Male','009')
5.6.学生表数据
insert into student values('白某','1201','Male','20','河北','1993.01.16','zdh01')
insert into student values('贾某','1202','Male','21','天津','1992.02.16','zdh01')
insert into student values('王某','1203','Male','20','天津','1993.08.06','zdh02')
insert into student values('龚某','1204','Female','18','云南','1995.11.11','zdh02')
insert into student values('邢某','1205','Male','20','吉林','1993.09.20','zdh03')
insert into student values('戴某','1206','Male','20','辽宁','1993.12.16','zdh03')
insert into student values('刘某','1207','Female','20','天津','1993.01.16','zdh04')
insert into student values('陈某','1208','Female','20','辽宁','1993.08.17','zdh04')
insert into student values('韩某','1209','Male','20','安徽','1993.10.19','zdh05')
insert into student values('袁某','1210','Female','20','浙江','1993.11.23','zdh05')
5.7.教学计划表数据
insert into eduplan values('0001','23102',4,16,1,2,1)
insert into eduplan values('0002','23104',4,18,3,2,1)
insert into eduplan values('0002','23206',4,18,3,2,3)
insert into eduplan values('0003','23112',4,16,3,2,2)
insert into eduplan values('0004','23214',4,18,1,2,2)
insert into eduplan values('0005','23212',4,16,9,3,3)
insert into eduplan values('0006','23106',4,16,3,2,3)
insert into eduplan values('0007','23110',4,16,5,2,2)
insert into eduplan values('0008','23202',4,14,9,3,2)
insert into eduplan values('0009','23204',4,12,7,2,3)
insert into eduplan values('0009','23206',4,12,3,2,5)
5.8.教学表数据
insert into education values('0001','001',4,80)
insert into education values('0002','002',20,95)
insert into education values('0003','003',10,70)
insert into education values('0004','004',18,75)
insert into education values('0005','005',22,83)
insert into education values('0006','006',8,78)
insert into education values('0007','007',18,90)
insert into education values('0008','008',6,88)
insert into education values('0009','009',14,90)
insert into education values('0010','010',6,82)
5.9.选课表数据
insert into sc values('0001','1201','YES',70)
insert into sc (CourseNumber,StudentNumber,Grade) values('0002','1201',93)
insert into sc (CourseNumber,StudentNumber,Grade) values('0003','1201',67)
insert into sc (CourseNumber,StudentNumber,Grade) values('0008','1202',90)
insert into sc values('0004','1202','YES',67)
insert into sc (CourseNumber,StudentNumber,Grade) values('0006','1202',73)
insert into sc (CourseNumber,StudentNumber,Grade) values('0003','1203',98)
insert into sc (CourseNumber,StudentNumber,Grade) values('0007','1203',84)
insert into sc (CourseNumber,StudentNumber,Grade) values('0005','1204',78)
insert into sc (CourseNumber,StudentNumber,Grade) values('0006','1204',62)
insert into sc (CourseNumber,StudentNumber,Grade) values('0010','1205',90)
insert into sc (CourseNumber,StudentNumber,Grade) values('0009','1206',92)
insert into sc (CourseNumber,StudentNumber,Grade) values('0007','1207',88)
insert into sc (CourseNumber,StudentNumber,Grade) values('0009','1208',79)
insert into sc (CourseNumber,StudentNumber,Grade) values('0005','1209',73)

6.索引的创建
6.1.课程表（course）按课程编号（CourseNumber）升序排列
create index IN_course on course(CourseNumber)
6.2.学生表（student）按学生学号（StudentNumber）升序排列
create index IN_student on student(StudentNumber)
6.3.教师表（teacher）按教师编号（TeacherNumber）升序排列
create index IN_teacher on teacher(TeacherNumber)
6.4.教室表（room）按教室容量（RoomVolume）降序排列
create index IN_room on room(RoomVolume desc)
6.5.学院表（school）按学院编号（SchoolNumber）升序排列
create index IN_school on school(SchoolNumber)
6.6.班级表（class）按班级编号（ClassNumber）升序排列
create index IN_class on class(ClassNumber)
6.7.教学计划表（eduplan）按课程编号（CourseNumber），星期（EduDay）升序排列，组合索引,唯一索引
create unique index IN_eduplan on eduplan(CourseNumber,EduDay)
6.8.教学表（education）按课程编号（CourseNumber）升序排列，唯一索引
create unique index IN_education on education(CourseNumber)

7.视图的创建
7.1.学生选课视图
create view V_elective
as
select s.StudentNumber,s.StudentName,c.CourseNumber,c.CourseName,t.TeacherNumber,t.TeacherName,sc.Revamp,sc.Grade
from student s inner join sc on s.StudentNumber=sc.StudentNumber
inner join course c on sc.CourseNumber=c.CourseNumber
inner join education e on e.CourseNumber=c.CourseNumber
inner join teacher t on e.TeacherNumber=t.TeacherNumber

7.2.教师授课视图
create view V_teach
as
select t.TeacherName,c.CourseName,eduplan.RoomNumber
from teacher as t inner join education as e on t.TeacherNumber=e. TeacherNumber
inner join eduplan on e.CourseNumber=eduplan.CourseNumber
inner join course as c on eduplan.CourseNumber=c.CourseNumber

7.3.教室安排视图
create view V_