Final Presentation Project Sponsor: Mr. Tim Maloney James DeBolt - - PowerPoint PPT Presentation

Trinity School Project Final Presentation

Project Sponsor: Mr. Tim Maloney

James DeBolt Valiant Tsang SYST 699 Spring 2017

2

• Background
• Problem
• Project: Objective/Scope/Assumptions
• Technical Approach
• Trinity School Schedule – Data Entry/Output
• Sensitivity Analysis
• Value Added
• Future Work
• Acknowledgement

Agenda

Background

• Trinity School at Meadow View is small, private

secondary school in Falls Church, VA

• Has approximately 150 students in grades 7

through 12

• School enrolls both boys and girls, but classes

are mostly separated by gender

• All students in a particular grade follow the

same curriculum with minor deviations

3

Problem

• Creating the master semester schedule by allocating teachers, courses, classrooms,

and periods of the day is a cumbersome and time-consuming paper and pencil process

4

Project

• Objective:
• Build upon a previously developed proof-of-concept

to replace current paper and pencil process

• Scope:
• Improve the user interface and workflow
• Incorporate additional curriculum-related

constraints while not violating established constraints

• Deliver tool suite and user manual
• Assumptions:
• Actual assignments of teachers to courses is

predetermined

• No changes to school facility/classroom

5

Technical Approach

6

• Determined based upon project sponsor feedback and previously developed proof-of-

concept

• Project Decomposition
• Part A: Tool Enhancement/Software Development
• Improve user interface (UI) for site usability and workflow improvement
• Ensure robustness of functionality of tool features
• Develop capability to generate multiple feasible schedules
• Part B: Timetabling/course scheduling
• Incorporate additional scheduling constraints

Technical Approach: Part A

7

Requirements Analysis Coding Unit Testing Verification and Validation System Design

Integration and System Testing

• Hybrid approach: Following old-

school V-model process within Agile/SCRUM sprinting model.

• Within each sprint, we

followed the V-model

• Incrementally incorporated

new functionality

• Goal of having a tested and

integrated set of functionality developed every sprint

8

LPSolve IDE

User converts *.lp file to *.mps file User uploads *.mps file to Gurobi

(5)

Performs the solving computations Inconsistent fields in tables with no permanent relationships, forms, macros and modules with significant amount of hardcoded VBA code Results are sent to user-provided email

NEOS Gurobi Solver

User takes contents of email and saves to *.txt file Use macro-enabled excel file to produce the

• utput/schedule

(1) (2) (3) (4)

Produces *.lp file User must manually repeat this entire process for each new schedule

Trinity School Scheduler 1.0 User Process

9

Use macro- enabled excel file to produce the

• utput/schedule

(1) (2)

Java code uses lp_solve 5.5.2.5

• n the user’s machine to

automatically generate numerous feasible schedules Tables with established relationships, improved forms, new reports, and modified macros and modules that sets up linear equations and writes *.lp file, launches Java executable

Trinity School Scheduler 2.0 User Process

10

MS Access Table Relationships

11

Testing

• Developed 8 test cases based on new/improved functions associated with the MS

Access UI

Scenario ID Test Case ID Test Case Test Case Description Test Step Test Step Description Expected Result Actual Result Status Comment

TS001 TC002 Adding a teacher Tests the ability to add a new teacher Pre- Condition TSS 2.0 is opened to Homepage Step 1 Select "Teacher Roster Availability Course Pairing" btn Opens frm_Teacher_Semester Step 2 Select "Add New Teacher" btn Opens frm_New_Teacher Step 3 Enter first and last name of teacher in respective text boxes Letters of name of teacher appearing in text boxes Step 4 Select "Save" btn Creates new record in tbl_Teacher, writes to fields FirstName and LastName Step 5 Select "Close" btn Closes frm_New_Teacher, returns to frm_Teacher_Semester Step 6 Scroll to bottom of frm_Teacher_Semester Name of new teacher appears at bottom of form

Example Scenario/Case

Timetabling

12

• Timetabling/course scheduling
• Well-researched problem that can be solved by applying various computational

methods such as integer linear programming (ILP), constraint programming, greedy algorithms, etc.

• Uniqueness of constraints vary between academic institutions
• Continue use of ILP
• Objective function: find as many randomly-specified decision variables, Xijk

where i = section/teacher pairing, j = classroom, k = period subject to 16 constraints (previously defined)

13

Trinity School Scheduler – Data Entry

14

Trinity School Scheduler – Data Entry

15

Trinity School Scheduler – Data Entry

16

Trinity School Scheduler – Output

17

Trinity School Scheduler – Output

18

Sensitivity Analysis

Feasible Schedule Probability (Baseline 60%) Varied Parameters Low High Teacher Availability 13% 95% Sections Taught 45% 65% Room Capacity 52% 62% Section/Room Restriction 57% 61% Parameter Value Range Varied Parameters Low Medium High Teacher Availability 3 5 6 Sections Taught 1.5 3 4 Room Capacity 10 17 25 Section/Room Restriction 12 48 72

19

Value Added

• Documentation and thorough analysis of the course constraints
• Enhancements to the tool
• Greatly simplified workflow / improving ease of use
• Improved the user interface
• Capitalized on capabilities of MS Access to ensure functionality of features
• Established the relationships for the tables
• Added/corrected data entry functionality
• Provided capability to produce status reports for data entry
• User guide includes instructions to modify the design of the MS Access portion of the

tool and the ability to toggle on/off constraints in the code

20

Future Work

• Investigate why not all previously developed constraints allow for generation of a

feasible schedule

• Incorporation of additional constraints
• Develop coding for exception to constraints 9, 11, 12
• Allow assignment of 2 teachers to single course that require 2 teachers
• Adding specific workflow feedback when there is an infeasible solution
• Improve flexibility with variables in coding
• Utilize a different mathematical method for computation

21

Acknowledgement

• Mr. Tim Maloney, Project Sponsor
• Dr. Kathryn Laskey
• Spring 2017 SYST 699 Class

22

Questions?

23

BACKUP

24

Constraints –previously implemented

1. Teacher availability constraint 2. Full time teachers will teach no more than 4 sections, part-time teachers no more than 2 sections 3. Prohibits teachers from being scheduled for more than 3 consecutive periods 4. Prohibits teacher’s sections to scheduled during the same period 5. Room capacity constraint 6. Limits certain courses to be taught in certain rooms 7. Room availability constraint 8. Limits section to be taught only once over all classrooms and periods 9. Limits one section to be scheduled for a grade/gender section during a single period

• 10. Limits one section to be scheduled to a classroom for each period
• 11. Limits 12 sections be scheduled during 1 period
• 12. Limits 72 sections be scheduled over 6 periods
• 13. Humane Letters be scheduled for 2 consecutive hours
• 14. 9th and 10th grade (by gender) Music be scheduled simultaneously
• 15. 11th grade boys and girls language be scheduled simultaneously
• 16. 12th grade boys and girls Art/Drama be scheduled simultaneously and for 2 consecutive hours

25

Project Schedule

26

Earned Value Management

27

Team Roles and Responsibilities

Team Member Roles/Responsibilities James DeBolt Valiant Tsang Requirements Analysis X X UI Modification X VBA Coding X X Java Coding X Testing X X Documentation Development X X Project Management X X