Exsched Solving Constraint Satisfaction Problems with the Spreadsheet
Exsched Solving Constraint Satisfaction Problems with the Spreadsheet Paradigm
Motivation n Designing schedules is a problem that arises quite frequently: n n n n Class schedules Employee schedules Examination schedules These schedules have a tabular, 2 -D structure In general, many constraint satisfaction problems such as timetabling problems, scheduling problems, recreational puzzles can be modeled as tables of constraints Use of spreadsheet paradigm for this purpose Goal: Design an interface that facilitates the interactive development of such tabular schedules
CS Class Schedule
Spreadsheet Paradigm n n n n Our solution: Use the spreadsheet paradigm Spreadsheets: Popular for arithmetic computations Uses the paper and pencil approach Arithmetic expressions are interactively entered and evaluated until desired results are obtained Repetitive computations are performed by copying expressions from one cell to another, with appropriate transformation applied Regular spreadsheets can not handle constraints We generalize spreadsheets so that finite domain constraints can also be entered in the cells n Knowledgesheet n Exsched: plug-in for Microsoft Excel
Interface n n Interface similar to regular spreadsheet (extension of MS Excel) Each cell can be thought of as a variable or place holder A user can enter finite domain values in a cell. These finite domain values denote the finite domain of the variable corresponding to the cell. Example: [1. . 5] Constraints can also be entered in the cell. Constraints contain variable names (cell coordinates) and constants. Example: B 3 #= C 4 + 1
Interface (cont’d) n n n n Constants can also be entered in the cell: the variable corresponding to that cell is set to the constant entered Constraints/constants/finite domains can either be entered into the current cell or via dialog boxes Lots of built-ins are available as clickable buttons Once constraints/constants/finite domains are entered, the system automatically collects them, composes a clp(FD) program, solves it using clp(FD) engine running in the background and displays the solution The user must enter at least one Query Table and zero or more Auxiliary Tables Query Table is used to compose the query Auxiliary table turns into facts Computed results for the query are displayed in the query table
Example: Employee schedule n Scheduling managers at a store: n n n n Store opens 8 AM to 11 PM, 7 days/week Each manager must work 8. 5 hr / day (includes 0. 5 hrs for lunch) Each manager must work 5 days / week At least one manager must be present at any moment Someone with night shift should not get morning shift next day Schedule must be fair to all managers In most cases, this scheduling is done manually n Erroneous, leads to employee dissatisfaction
Solution: Employee Schedule n Assume that there are 5 managers n Each manager works 8. 5 hrs per day either in n The morning shift (8: 00 AM to 4: 30 PM), or The midday shift (10: 00 AM to 6: 30 PM), or The evening shift (2: 30 PM to 11: 00 PM)
Solution: Employee Schedule (cont’d) An Empty Table
Solution: Employee Schedule (cont’d) n n n n Morning, midday and evening shifts are denoted by 5, 2 and 4 respectively 0 will be used to indicate a manager’s day off Domain of each cell: [0, 2, 4, 5] User enters domain in one cell, copies it to rest For no morning after night restriction, we enter the constraint: C 2 != B 2 + 1 (copied everywhere) At least one manager is present at any time during the day: member(4, [D 2, D 3, D 4, D 5, D 6]), member(5, [D 2, D 3, D 4, D 5, D 6]) No manager works for more than 5 days a week: frequency(0, [B 2, C 2, D 2, E 2, F 2, G 2, H 2], 2) Every manager has more or less same proportion of morning, midday and evening shifts: sublist([2, 4, 5], [B 2, C 2, D 2, E 2, F 2, G 2, H 2])
Solution: Employee Schedule (cont’d) Table after adding further constraints
Solution: Employee Schedule (cont’d) [0, 2, 4, 5], C 2 != B 2 + 1 (Cell Constraints) count(0, [B 2, C 2, D 2, E 2, F 2, G 2, H 2], =, 2), member(4, [D 2, D 3, D 4, D 5, D 6]), member(5, [D 2, D 3, D 4, D 5, D 6]) sublist([2, 4, 5], [B 2, C 2, D 2, E 2, F 2, G 2, H 2]) (Column Constraints) (Row Constraints) Note: Cell constraints are replicated in all 35 cells, column constraints in B 7 through H 7 and row constraints in I 2 through I 6.
Solution: Employee Schedule (cont’d) Displaying a solution
Solution: Employee Schedule (cont’d) Displaying a solution
Example: The 3 x 3 Grid Puzzle Cell constraints: n B 3: B 3+C 3+D 3 #= 15, B 3+B 4+B 5 #= 15 n C 3: C 3+C 4+C 5 #= 15 n D 3: D 3+D 4+D 5 #= 15 n B 4: B 4+C 4+D 4 #= 15 n B 5: B 5+C 5+D 5 #= 15, B 5+C 4+D 3 #= 15 n D 5: B 3+C 4+D 5 #= 15, alldiff([B 3, B 4, B 5, C 3, C 4, C 5, D 3, D 4, D 5])
Solution: The 3 x 3 Grid Puzzle
Example: Cryptarithmetic Puzzles Most puzzles have such a graphical structure; for example, Zebra puzzle
Conclusion n Advantages of the Knowlodgesheet Approach: n n n n Flexibility Interactivity Non-experts can use it Domain specific knowledge can be incorporated User and clp(FD) system cooperate to produce solutions User can give partial solutions, compute rest with Knowledgesheet Disadvantages: n n Works only for tabular clp(FD) programs No help if the system is overconstrained
- Slides: 18