用SQL实现的一个自动排课机制

        同学毕业设计搞的是一个排课系统，具体功能就给课程安排教室和时间。排课算法是有一定难度的，很多老师说过，至今也没有完美的排课算法，的确，排课，是一个五维交叉的复杂体系：时间、课程、教室、班级、学生。一个排好的课表至少要保证以下几点原则：

  1. 一个教师同一时间只上一门他要教的课
  2. 一个班级同一时间只上一门他要上的课
  3. 一个教室同一时间只上一门课

        拿到这个问题，刚开始觉得简单，后来仔细分析后，发现难度相当大。关键是其复杂度太高，对待这种问题，我选择排除法。也就是先列出可能的各种组合，再依据约束条件进行排除，最后留下的结果即是所求。
        利用Power Designer来数据建模。
        首先是建立教师、班级、课程表，因为他们是主体。并建立相关的关系映射表：
        这五张表都是要作为输入数据提交给自动排课系统的。


        分配的主体还包括时间和地点，也就是上课时段和教室，由于系统要满足三点原则，所以，需要将五维关系进行关联，也就是教室和时间、教师和时间、班级和时间进行关系映射：


        上图中，教室表、教师表、班级表、上课时段表都是系统的输入数据。而他们之间的关系映射表：Room_Time_Map、Class_Time_Map、Teacher_Time_Map则是系统在运行中使用的表，系统就是靠他们完成前面提到的三个排除原则的，在系统运行之前，它们是空的。运行开始之后，每当系统要为一门课程分配教室时，都要检查这三个映射表，看是否已经有相关的记录。举个例子：当系统要为语文课分配某个时间和某个教室时它首先要看Class_Time_Map，看看上语文课的这个班级是否在同一时间已经有课，如果有则不分配这个时间。同理对教室，和教师。
        筛选条件准备好之后，开始构造课程表，准确说是预排课程表，这个课程表包括各种可能：


        它是教室表、上课时段、班级课程对应表、教师课程对应表的连接组合，只进行了一个初步筛选，就是每条记录中，的班级上的课程与教师上的课程必须是相同的。SQL如下：

 1 select   
 2     cc.CourseID,
 3     r.RoomID,
 4     cc.ClassID,
 5     tc.TeacherID,
 6     t.TimeID
 7 into
 8     TempPlans
 9 from
10     dbo.Class_Course_Map as cc,
11     dbo.Teacher_Course_Map as tc,
12     dbo.Times as t,
13     dbo.Rooms as r
14 where    
15     cc.CourseID = tc.CourseID

        在输入10个班级、6位教师、5个教室、5门课程、15个上课时段（5 天 × 3 时段/每天）的情况下共有3450条记录产生。

        在构造一个最终课程表，结构与预排课程表相同，用来放最终结果。所有预排课程表中的记录只要满足筛选条件都要插入到此表。
        下面就要对3450条记录进行筛选了，筛选之前还有一个问题必须考虑，那就是这3450条记录中有很多记录可能出现如下情况：一个班级在不同时间内上同一门课程若干次，而不是一次。系统是假定每门课程每个班级只上一次的（这可能有悖常理，稍后讨论）。所以有必要再构造一个表进行条件筛选，此表称之为班级课程记录表：



        每当系统为一个班级分配好一门课程时，都会将班级、课程号记录再此表内，将来再插入记录时便可以检查改表，看是否以为该班分配了该课，避免重复。

        下面就可以进行最后的运行了，系统将对预排课程表逐条检查，看其是否满足删除条件：也就是看Room_Time_Map、Class_Time_Map、Teacher_Time_Map、Class_COurse_Rec_Map中是否已经有此记录，如果有，略过；如果没有证明可以排课，则将其添加到最终的课程表；最后将这条记录从预排表中删除。
SQL如下：

 1 /* check the data one by one*/
 2 
 3 
 4 while (select count(*) from dbo.TempPlans) > 0
 5 begin
 6 
 7     declare @course int,@room int,@class int,@teacher int,@time int
 8     select top 1 @course=CourseID, @room=RoomID, @class=ClassID, @teacher=TeacherID, @time=TimeID
 9         from dbo.TempPlans
10 
11     declare @classok int,@teacherok int,@roomok int,@classrecok int
12     select @classok=count(*) from dbo.Class_Time_Map where ClassID=@class and TimeID=@time
13 
14     select @classrecok=count(*) from dbo.Class_Course_Rec_Map where ClassID=@class and CourseID=@course
15     
16     select @teacherok=count(*) from dbo.Teacher_Time_Map where TeacherID=@teacher and TimeID=@time
17     
18     select @roomok=count(*) from dbo.Room_Time_Map where RoomID=@room and TimeID=@time
19 
20     if @classok + @teacherok + @roomok + @classrecok = 0
21     begin
22         insert into dbo.Class_Time_Map values(@time,@class)
23         insert into dbo.Class_Course_Rec_Map values(@class,@course)
24         insert into dbo.Teacher_Time_Map values(@teacher,@time)
25         insert into dbo.Room_Time_Map values(@room,@time)
26         insert into dbo.Plans values(@course,@room,@class,@teacher,@time)
27     end
28     
29     delete from dbo.TempPlans 
30         where CourseID=@course and RoomID=@room and ClassID=@class and TeacherID=@teacher and TimeID=@time
31 
32 
33 end

 

 

 

        最终的结果条数应该与Class_Course_Map中的条数是一致的，因为我们假设每个课程每个班只上一次。经检测，实现了预想的效果，只是系统会尽少地完成安排，也就是说，系统会使用尽量少的教室安排课程，5个教室，只使用了3个。当然这些可以通过增加筛选条件加以避免。总的排课策略是对的。
        文章最后解决前面提到的一个问题：系统假设每门课程，每个班只上一次，这完全不满足现实需要。例如，大学的英语，每周要上三次之多。其实这个很好解决，可以把英语看作三个不同的课程，命名为英语1、英语2、英语3，这样对于上课多于一次的课程只要修改输入表：课程、班级课程对应表、教室课程对应表就可以了，并不需要修改系统。

posted @ 2013-06-04 08:55  最后的牛仔  阅读(2046)  评论(0)  编辑  收藏  举报