Description

1 Background
A simple timetable is illustrated in Table 1. This timetable has columns for every “homeroom” (10A, 10B, 11A and 11B) and rows for every period (1-4). A given entry in this timetable illustrates which course has been scheduled to be taught at a particular time. For example, Grade 11 English (which is taught by Cole to Homeroom 11A) has been scheduled for period 1, while Grade 10 French (taught to homerooms 10A and 10B by Cole, Glass and Marsalis) has been scheduled for period 3.’

Day Per 10A 10B 11A 11B
A 1 Math
Burton/Corea/Metheny English Cole CS Mays
A 2 Biology Corea English Cole Math
Burton/Metheny/Mays
A 3 French
Cole/Glass/Marsalis CS Mays English Burton
A 4 English Metheny Biology Corea French
Glass/Marsalis/Jarrett

Table 1: A Simple Timetable

‘The purpose of having multiple teachers and homerooms for a given course (as is done in this example for French and Math) is to permit “streaming”; the grouping of students in classes according to their abilities. Because Math and French are taught to all students in a given grade at the same time, good students from both homerooms in that grade can be taught together as can average and weak students.

2 Assignment Overview
For this assignment, you will define a set of queries over a timetabling database that will be provided for you. These queries will allow the timetable to recognize conflicts in a given timetable (e.g., a teacher who has been scheduled to teach two classes at one time, or a homeroom that has been scheduled to take two classes at one time), as well as view teacher and homeroom schedules, find when a teacher has been overscheduled on any given day etc.
You will define SQL queries over a timetable database. The purpose of this assignment is to help you become familiar with Postgres and to give you practice in writing sophisticated SQL queries.
3 Assignment Particulars
What you will be given: You will be provided with a Postgres database with the same name as your login name, and to which you have exclusive read and write access. This database includes the tables shown below with their respective schemas:
Courses = (cno,subj,nper) Taught by = (cno,tname)
Teachers = (tname,tload) Taught to = (cno,grade,hr)
Table 2: Database Structure = (cno,day,per)

day per
A 1
A 2
A 3
A 4
B 1
B 2
B 3
B 4
C 1
‘… …
INSERT INTO (table name) (query).
2. Write a query over tables Courses and Taught By to populate the table, Teachers. The result should have a row for every teacher with that teacher’s teaching load calculated by the query. As well, the resulting table should be sorted by teacher name. Thus, the first 8 rows of the table produced by your query should be:

tname tload
Burton 14
Clarke 12
Cole 10
Corea 14
Gismonti 10
Glass 10
Jarrett 9
Kroll 8
… …

6. 11B has been scheduled to take two courses on period B4. Fix 11B’s schedule by changing the Grade 11’s math assignment from B4 to B2. Report the SQL query used to achieve this.

7. Write a query to generate every teacher’s teaching schedule. The result of this query should be a table listing every teacher, and every day and period that teacher is scheduled to teach along with the subject grade and homeroom that is taught during that period. The result should be sorted by teacher, day and period. Thus, your query should generate a table whose first 11 rows are as shown below:

tname day per subj grade hr
Burton A 1 Mat 10 Z
Burton A 4 Mat 11 Z
Burton A 4 Bio 10 B
Burton B 2 Mat 11 Z
Burton B 3 Mat 10 Z
Burton C 2 Mat 10 Z
Burton E 1 Mat 10 Z
Burton E 2 Mat 11 Z
Burton E 4 Bio 10 B
Clarke A 1 Fre 11 Z
Clarke A 4 Eco 12 A
… … … … … …

8. Write a query to generate every homeroom’s class schedule. The result of this query should be a table listing every grade and homeroom, and every day and period that homeroom has been scheduled to take a class along with the subject that is taught to them during that period. The result should be sorted by grade, homeroom, day and period. Thus, your query should generate a table whose first 12 rows are as shown below:

Grade HR Day Per Subj
10 A A 1 Mat
10 A A 3 Bio
10 A A 4 Eng
10 A B 2 CS
10 A B 3 Mat
10 A B 4 Fre
10 A C 1 Bio
10 A C 2 Fre
10 A C 2 Mat
10 A C 3 PE
10 A C 4 Fre
10 A D 2 Eng
… … … … …

9. Write a query that reports which teachers have schedules with teacher conflicts and on what periods these conflicts occur. Your query should report four teacher conflicts in the current schedule.

10. Write a query that reports which homerooms have schedules with homeroom conflicts and on what periods these conflicts occur. Your query should report four homeroom conflicts in the current schedule.

14. 11A has been scheduled to take two courses on period B2. Fix 11A’s schedule by changing the period

15. 11B has been scheduled to take two courses on period A2. Fix 12B’s schedule by changing the period

16. Course #4 has been scheduled twice in the same day in the current schedule. Fix course #4’s schedule by

18. Write a query that finds all teachers who only teach one subject. Your query should report the teacher
name(s) and the subject(s) he/she teaches. For full credit you should not use any aggregate.

19. Write a query that reports for each teacher who has windows in their schedule, the days on which those windows occur, and the number of windows on each of those days. A window is a period where that teacher is not scheduled to teach, but that is both preceded and followed by periods on the same day where that teacher is scheduled to teach. For example, if a teacher is scheduled to teach periods 1 and 3 (but not 2) on day A, then that teacher has one window on day A (from the empty period 2). If a teacher is scheduled to teach periods 1 and 4 on day A (but not 2 and 3), then that teacher has two windows in his/her schedule as a result. On the other hand, if a teacher is scheduled to teach periods 2, 3 and 4 but not 1 on a given day, then that day contains no windows.

20. Write a query that finds the total number of windows in each teacher’s schedule. Note that this should
include rows for teachers who have no windows in their schedule!

21. Write a query that finds the total number of windows overall in the entire timetable. (This is called the
“goodness” measure of the timetable.)

24. Write a query to find, for each teacher, the maximum number of periods taught by that teacher on any
given day, and the day(s) when the teacher teaches that number of periods.