Prototype Classroom Scheduling Application

By Tony Marston

14th July 2006

Introduction
Database Schema - Classroom Scheduling
Subjects and Lessons
Teachers
Rooms
Classes and Students
Schedules and Timetables

Introduction

The download of the Radicore framework includes several prototype applications which demonstrate the kind of application which the framework is designed to support. One of these is the CLASSROOM SCHEDULING application, and the purpose of this article is to provide a description of that application so that you can see what it does as well as how it does it. This may prove useful in the event that you should ever want to build your own version of the prototype.

Database Schema - Classroom Scheduling

This is the database schema used by this application showing all the entities (tables) and the relationships between them.

Figure 1 - Classroom Scheduling Schema

class table class_lesson table conflict table lesson table room table schedule table schedule_hdr table student table student_lesson table subject table teacher table classroom-prototype-001 (1K)

Subjects and Lessons

A subject is a very broad category and may be broken down into a large number of different lessons. A lesson is the only unit which can be scheduled, so "Mathematics, Class 1" is a different lesson to "Mathematics, Class 2". Each lesson is given by a single teacher, so "Mathematics by teacher A" is a different lesson to "Mathematics by Teacher B".

SUBJECT table
subject_id This is the unique identifier for this entry.
subject_name This is the description for this entry.

Figure 2 - Subjects

classroom-prototype-002 (8K)
LESSON table
lesson_id This is a unique identifier which is generated by the system.
lesson_name This is the description for this entry.
lesson_short_name This is a short description which will appear in all schedule screens.
year This is used to restrict the lesson to students of the same year. For example "Maths for year 1" cannot be taken by a student in year 3.
teacher_id This is a foreign key to the TEACHER table.
lesson_notes Some free-format notes about the lesson.
subject_id This is a foreign key to the SUBJECT table.

Figure 3 - Lessons

classroom-prototype-003 (13K)

Teachers

A teacher is the person who gives a lesson. A teacher can give many lessons, but each lesson has only one teacher.

TEACHER table
teacher_id This is a unique identifier which is generated by the system.
first_name This is the teacher's first name.
last_name This is the teacher's last name
initials These are the teacher's initials.
title This is the teacher's title.
teacher_notes These are free-format notes about the teacher.

Figure 4 - Teachers

classroom-prototype-004 (11K)

Rooms

A room is the place where a lesson takes place. A room may be allocated to a class so that the class stays in that room for all its lessons, while other rooms may be purpose-built for particular subjects, such as a chemistry laboratory or gymnasium.

A room can only be used for one lesson at a time, so the scheduling system must disallow double booking. A room may also have a maximum capacity, so the scheduling system must prevent this capacity from being exceeded.

ROOM table
room_id This is the unique identifier for this entry.
room_desc This is the description for this entry.
room_capacity This is the maximum number of students who can occupy this room.
room_notes These are free-format notes about the room.

Figure 5 - Rooms

classroom-prototype-005 (10K)

Classes and Students

A student is an individual while a class is a group of students who share the same schedule. Junior students (i.e. those in the 1st to 5th years) may always belong in a class while senior students (i.e. those in the 6th year) who have fewer lessons in more specialised subjects may have their lessons scheduled individually.

CLASS table
class_id This is the unique identifier for this entry.
class_name This is the description for this entry.
year This gives an indication of the experience and abilities of the class. Juniors start at year 1 and become more senior with each passing year.

When choosing a lesson which is to be taken by the class, only those lessons which are for the same year as the class will be made available in the popup screen.

class_notes These are free-format notes about the class.

Figure 6 - Classes

classroom-prototype-006 (10K)
CLASS_LESSON table
class_id This is a foreign key to the CLASS table.
lesson_id This is a foreign key to the LESSON table.

This table links a class to a lesson. All the students within that class are therefore linked to that lesson.

Figure 7 - Class-Lesson

classroom-prototype-007 (12K)
STUDENT table
student_id This is a unique identifier which is generated by the system.
first_name This is the student's first name.
last_name This is the student's last name
initials These are the student's initials.
year This is only used if CLASS_ID is not specified. It gives an indication of the experience and abilities of the student. Juniors start at year 1 and become more senior with each passing year.

When choosing a lesson which is to be taken by the student, only those lessons which are for the same year as the student will be made available in the popup screen. A student who is assigned to a class cannot be assigned to lessons as an individual.

class_id This is only used if YEAR is not specified. This is a foreign key to the CLASS table.
student_notes These are free-format notes about the student.

Figure 8 - Students

classroom-prototype-008 (11K)
STUDENT_LESSON table
student_id This is a foreign key to the STUDENT table.
lesson_id This is a foreign key to the LESSON table.

This table links an individual student to a lesson. Notice that it is possible to link a student to a lesson which is also taken by a class.

Figure 9 - Student-Lesson

classroom-prototype-009 (8K)

Schedules and Timetables

Some systems attempt to create schedules "automagically", but as this would be complicated and CPU-intensive operation which could never be guaranteed to produce perfect results first time every time this prototype uses a manual approach. This involves choosing a room, then allocating lessons one at a time to a particular day of week and to a particular start and end time. The system will check that the latest addition does not conflict with any previous scheduled events and will set the IS_VALID flag accordingly. Only valid scheduled events, those without any conflicts, will appear in any schedule screens. Invalid events will have the details of all conflicts recorded in the SCHEDULE_ERRORS field.

To speed up the process of checking for conflicts this prototype takes all the raw data and builds a database table containing all the possible conflicts. The validation of a scheduled event can therefore perform a few simple lookups on this table instead of trawling through the raw data. The "Update" button on the List Conflicts screen is used to erase and rebuild the current list of conflicts. Note that this function must be used AFTER all rooms, teachers, lessons, classes and students have been input or amended and BEFORE any attempt is made to schedule any lessons. Also note that as this function is required to read and process virtually every record in the database it will not be quick.

Each record in the CONFLICT table identifies two LESSONS which cannot be run simultaneously for one of the following reasons:

CONFLICT table
conflict_id This is a unique identifier which is generated by the system.
conflict_type
  • Class
  • Student
  • Teacher
teacher_id This is a foreign key to the TEACHER table.
student_id This is a foreign key to the STUDENT table.
class_id This is a foreign key to the CLASS table.
lesson_id1 This is a foreign key to the LESSON table.
lesson_id2 This is a foreign key to the LESSON table.

Figure 10 - Scheduling Conflicts

classroom-prototype-010 (13K)

It is more than likely that while one set of schedules is in general use the administrator will want to create a new set of schedules for the following year. This is achieved by having multiple schedule header records (each with its own schedule_id) and having one of them marked as "active" for general use. By default all users will be restricted to those schedules which were created under this schedule_id, but a user who has access to the Choose Schedule Id screen can select a different schedule_id for use during the current session. In this way an administrator can work on a new set of schedules without affecting any of the existing schedules.

SCHEDULE_HDR table
schedule_id This is a unique identifier which is generated by the system.
schedule_desc This is a description for this entry.
schedule_notes These are free-format notes about the schedule header.
is_active This indicates if this schedule is currently active or not.
start_time This is the start time of each day's schedule. This must specify minutes of '00', '15', '30' or '45'.
end_time This is the end time of each day's schedule. This must specify minutes of '00', '15', '30' or '45'.
last_day_no Each week starts on a Monday (day 1) and ends on another day, such as Friday (day 5), Saturday (day 6) or Sunday (day 7). The required ending day is set here.

Figure 11 - Schedule Header

classroom-prototype-011 (7K)

Figure 12 - Choose Schedule Id

classroom-prototype-012 (6K)

This is the table which holds all the scheduled events - an occurrence of LESSON which is linked to a ROOM on a certain day between certain times. As each event is entered it is checked for validity. If it is valid it will appear on the various schedule screens. If it is not valid a list of errors will appear in the schedule_errors column.

SCHEDULE table
schedule_id This is a foreign key to the SCHEDULE_HDR table.
seq_no This is a unique identity which is generated by the system.
room_id This is a foreign key to the ROOM table.
day_no
  • 1 = Monday
  • 2 = Tuesday
  • 3 = Wednesday
  • 4 = Thursday
  • 5 = Friday
  • 6 = Saturday
  • 7 = Sunday
start_time The start time for this scheduled event.
end_time The end time for this scheduled event.
lesson_id This is a foreign key to the LESSON table..
is_valid If there are any conflicts this is set to 'NO'.
schedule_errors This contains a list of all conflicts with other scheduled events.

To create schedule events the user starts by selecting a ROOM on the List Room screen, then presses the "Maintain Schedule" button to bring up the Maintain Schedule Events screen. The user then selects the relevant day using the scrolling links to show what lessons have been scheduled on that day. New events can be added by pressing the "New" button which activates a variation of the Update Schedule Event screen which allows the user to select a LESSON and a start and end time. If the event fails its validation it will still be added to the database, but will be marked as invalid so that it will not appear in any schedule screens. There may be numerous reasons why the validation failed, s all of these reasons will be shown in the schedule_errors column.

The following errors will prevent a record from being inserted or updated:

The following errors will allow a record to be inserted or updated, but will set IS_VALID to 'NO':

Figure 13 - Maintain Schedule Events

classroom-prototype-013 (11K)

Figure 14 - Update Schedule Event

classroom-prototype-014 (12K)

Figure 15 - View Schedule/Timetable

classroom-prototype-015 (13K)

Several variations of this screen are available by selecting different parent entities:


© Tony Marston
14th July 2006

http://www.tonymarston.net
http://www.radicore.org

counter