Trinity School Project Final Presentation Project Sponsor: Mr. Tim Maloney James DeBolt Valiant Tsang SYST 699 Spring 2017
Agenda • Background • Problem • Project: Objective/Scope/Assumptions • Technical Approach • Trinity School Schedule – Data Entry/Output • Sensitivity Analysis • Value Added • Future Work • Acknowledgement 2
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 • Determined based upon project sponsor feedback and previously developed proof-of- concept • Project Decomposition Part A : Tool Enhancement/Software Development o Improve user interface (UI) for site usability and workflow improvement o Ensure robustness of functionality of tool features o Develop capability to generate multiple feasible schedules Part B : Timetabling/course scheduling o Incorporate additional scheduling constraints 6
Technical Approach: Part A • Hybrid approach: Following old- Requirements Verification Analysis and Validation school V-model process within Agile/SCRUM sprinting model. Integration and System Design Within each sprint, we System Testing followed the V-model Incrementally incorporated Coding Unit Testing new functionality Goal of having a tested and integrated set of functionality developed every sprint 7
Trinity School Scheduler 1.0 User Process (2) (3) (1) Performs the solving User converts *.lp computations file to *.mps file Produces *.lp file User uploads *.mps file to Gurobi NEOS LPSolve Inconsistent fields in tables with no Gurobi IDE permanent relationships, forms, macros Solver and modules with significant amount of hardcoded VBA code Results are sent to user-provided User must manually repeat Use macro-enabled email this entire process for each excel file to produce the new schedule output/schedule User takes contents of email and saves to *.txt file (4) (5) 8
Trinity School Scheduler 2.0 User Process (1) 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 Java code uses lp_solve 5.5.2.5 on the user’s machine to automatically generate numerous feasible schedules Use macro- enabled excel file to produce the output/schedule (2) 9
MS Access Table Relationships 10
Testing • Developed 8 test cases based on new/improved functions associated with the MS Access UI Example Scenario/Case Scenario Test Case ID 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 Pre- TSS 2.0 is opened to Homepage new teacher Condition Step 1 Select "Teacher Roster Availability Opens Course Pairing" btn frm_Teacher_Semester Step 2 Select "Add New Teacher" btn Opens frm_New_Teacher Step 3 Enter first and last name of Letters of name of teacher teacher in respective text boxes 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 Name of new teacher frm_Teacher_Semester appears at bottom of form 11
Timetabling • 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, X ijk where i = section/teacher pairing, j = classroom, k = period subject to 16 constraints (previously defined) 12
Trinity School Scheduler – Data Entry 13
Trinity School Scheduler – Data Entry 14
Trinity School Scheduler – Data Entry 15
Trinity School Scheduler – Output 16
Trinity School Scheduler – Output 17
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 18
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 o Established the relationships for the tables o Added/corrected data entry functionality o 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 19
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 20
Acknowledgement • Mr. Tim Maloney, Project Sponsor • Dr. Kathryn Laskey • Spring 2017 SYST 699 Class 21
Questions? 22
BACKUP 23
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. 9 th and 10 th grade (by gender) Music be scheduled simultaneously 15. 11 th grade boys and girls language be scheduled simultaneously 16. 12 th grade boys and girls Art/Drama be scheduled simultaneously and for 2 consecutive hours 24
Project Schedule 25
Earned Value Management 26
Team Roles and Responsibilities Team Member Roles/Responsibilities James DeBolt Valiant Tsang X X Requirements Analysis X UI Modification X X VBA Coding X Java Coding X X Testing X X Documentation Development X X Project Management 27
Recommend
More recommend
