自动排课系统——数据库大作业说课材料.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_Arrangementon primary(name=Auto_Arrangement_data,filename=e:Auto_ArrangementAuto_Arrangement_data.mdf,size=10MB,filegrowth=2MB,maxsize=500MB),(name=Auto_Arrangement_data1,filename=e:Auto_ArrangementAuto_Arrangement_data1.ndf,size=5MB,filegrowth=1MB,maxsize=500MB)log on(name=Auto_Arrangement_log,filename=e:Auto_ArrangementAuto_Arrangement_log.ldf,size=1MB,filegrowth=10%)2.基本表的创建use Auto_Arrangement2.1.教室表的创建create table room(RoomNumber char(10) primary key,RoomVolume smallint not null,Available char(4),check(RoomVolume=0),check(Available=YESor 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=Maleor 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=Maleor 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=YESor 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 asNOexec sp_bindefault de_Revamp,sc.Revamp 3.2.创建默认值YES，并为教室表中是否可用绑定create default de_Available asYESexec sp_bindefault de_Available,room.Available4.触发器的创建4.1.对教学计划表创建触发器，当插入课程人数多于教室容量的数据时，回滚操作create trigger TR_eduplan1 on eduplanfor insertasif(select count(*)from course c inner join inserted i on c.CourseNumber=i.CourseNumber inner join room r on i.RoomNumber=r.RoomNumberwhere datediff(d,CourseVolume,RoomVolume)0)0beginprint课程人数大于教室容量，无法安排课程，操作无法完成rollbackend 4.2.对教学计划表创建触发器，当插入课程占用不可被占用的教室的数据时，回滚操作create trigger TR_eduplan2 on eduplanfor insertasif(select count(*)from inserted i inner join room r on i.RoomNumber=r.RoomNumberwhere available=NO)0beginprint插入课程占用不可被占用的教室，无法安排课程，操作无法完成rollbackend5.数据的输入 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_electiveasselect s.StudentNumber,s.StudentName,c.CourseNumber,c.CourseName,t.TeacherNumber,t.TeacherName,sc.Revamp,sc.Gradefrom 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_teachasselect t.TeacherName,c.CourseName,eduplan.RoomNumberfrom 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_