COSI127B - Programming Assignment 1 - Timetables-R-Us -  Database Management Systems  - Solved

1    Background
Your school needs a database system to help with the construction of their class schedules (or timetable). A timetable is a dayto-day schedule describing what classes are taught when and to whom. By using a database to manage timetables, the constructor of a school’s timetable (the timetable) can define queries that determine individual teacher schedules and individual student schedules. As well, queries can reveal conflicts in the timetable, such as teacher conflicts which occur when a teacher has been scheduled to teach two classes at the same time, and homeroom conflicts which occur when a student has been scheduled to take two classes at the same time.

 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) 
Grades 
= 
(grade,hr)                                            Assigned 
= 
(cno,day,per) 
Periods 
= 
(day,per)                                  Assigned Backup 

Table 2: Database Structure 
= 
(cno,day,per) 
 

Table Courses describes all courses that are taught in the school. As we saw with the timetable of Figure 1, a course can have a single teacher and be taught to a single homeroom (e.g., English, taught to homeroom 11A by Cole), or can have multiple teachers and be taught to multiple homerooms (e.g., Math, taught to homerooms 10A and 10B by Corea, Burton and Metheny). Each course has a course number (cno), a subject (subj) and a number indicating the number of periods it must be assigned within the timetable (nper).

 

Table Teachers names every teacher in the school (tname) and indicates how many periods a week they have to teach (tload). Table Grades lists every homeroom (hr) for every grade (grade). Table Periods lists all of the periods that appear on a schedule. (day and per). Table taught by shows every teacher (tname) who has been assigned to teach each course (cno). Table Taught to shows every homeroom (grade, hr) to whom each course (cno) is taught. (Courses such as Math that are taught to all homerooms in a particular grade are assigned hr = ‘Z’). Finally, table Assigned shows which periods (day, per) have been assigned to what courses (cno).2 

 

You will be posing numerous SQL queries over your timetable database in order to complete a set of tasks. A given task may require one or more queries. You may use temporary tables only from queries 19 to 24. 

 

 

2The table Assigned Backup is identical to Assigned and can be used as a backup table if you decide you have made mistakes in updating table Assigned. Note: DO NOT UPDATE Assigned Backup. Instead, if you want to start the assignment from scratch, delete all entries in table Assigned using the SQL command 

delete from Assigned, 

and then copy all rows of Assigned Backup into Assigned using the SQL command, 

insert into Assigned select * from Assigned Backup. 

 

1.       Write a query over table Assigned to populate the table, Periods. The result of your query should generate the table containing all periods that appear in the table sorted by day and by period. Therefore, your query should generate a table whose first 9 rows (if ordered by day, per) are shown below:

day 
per 
A 
1 
A 
2 
A 
3 
A 
4 
B 
1 
B 
2 
B 
3 
B 
4 
C 
1 
                                                                                                ‘...           ... 

To populate the table Assigned with the result of your query, use the SQL insert command which uses the syntax below:

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 
… 
… 
 

3.       Write a query to generate Marsalis’ teaching schedule. The result of this query should be a table showing the day, period and course number of every course taught by Marsalis that has been assigned a period thus far. The result should be sorted by day and period (i.e., in the order: A1, A2, A3, A4, B1, B2, …).

 

4.       Marsalis has been scheduled to teach two courses on period B2. Fix Marsalis’ schedule by changing the period assigned to course #10 from B2 to B4. Report the SQL query used to achieve this.

 

5.       Write a query to generate 11B’s homeroom schedule. The result of this query should be a table showing the day, period and course number of every course taught to 11B that has been assigned a period thus far. (Don’t forget that courses taught to homeroom 11Z are taught to both homerooms in Grade 11 and should therefore be included.) The result should be sorted by day and period.

 

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.  

 

11.    Write a query that reports which courses have been assigned two periods in the same day and which periods these are. Your query should report one course that has been scheduled twice in the same day in the current schedule.

 

 

12.    Burton has been scheduled to teach two courses on period A4. Fix Burton’s schedule by changing the period assigned to course #14 from A4 to A2. Report the SQL query used to achieve this.

 

13.    Mays has been scheduled to teach two courses on period B4. Fix Mays’ schedule by changing the period assigned to course #3 from B2 to D2. Report the SQL query used to achieve this.

 

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

assigned to course #19 from B2 to B4. Report the SQL query used to achieve this.

 

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

assigned to course #22 from A2 to A4. Report the SQL query used to achieve this.

 

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

changing the period assigned to it from C2 to D1. Report the SQL query used to achieve this.

 

17.    Write a query that finds for each course how many periods have not yet been assigned in the timetable. (courses.nper is the number of times a course should be taught.) Do not list the courses that are already fully assigned.

 

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.)

 

22.    Write a query to find, for each course, all periods that cannot be assigned to that course because one of the teachers who teach that course has already been scheduled to teach that period. For example, course #1 is taught by Burton, Corea and Metheny. If Burton has been assigned to teach on periods A1 and C2, Corea has been assigned to teach periods A1 and B1, and Metheny has been assigned to teach periods A1 and D2, then your query should associate course #1 with periods A1, B1, C2 and D2.

 

23.    Write a query to find, for each course, all periods that cannot be assigned to that course because one of the homerooms that the course is taught to has already been scheduled to take another course that period. For example, course #7 is taught to homeroom 10A. If Grade 10 Math has been scheduled for periods A1 and C2, and Eco 10A has been scheduled for periods D1 and E4, then course #7 should be associated with periods A1, C2, D1 and E4.