CSC 170
Homework 5

Assigned 26 Nov, due 12 Dec

The task we're trying to accomplish

Every semester, the Adelphi registrar has to figure out which courses will be in which classrooms at which times. This is a tricky problem, requiring some sophisticated database operations. We'll do a simplified version of it.

Develop an Access database to help with the problem of assigning Adelphi courses to classrooms and time slots. (When I originally assigned this, I added "and instructors", but I think that just makes it more complicated without making it more interesting, so we can skip the instructors.)

Certain information is known before the process starts: the type and capacity of each classroom, the maximum number of students in each course, the type of classroom each course should be in (for example, CSC 170 should be in a computer lab, and MTH 140 should be in a lecture room), etc. You'll probably need several tables -- say, a table of classrooms, a table of courses, etc. -- to keep track of this information. Fill in these tables with a reasonable variety of courses, classrooms, etc. so you can test whether your queries work. For example, make sure there are classrooms of different types and different sizes, so you can test whether your queries to check those things work correctly.

The task next requires somebody to choose a classroom and a time slot for each course. You may view this as filling in a separate table, or as filling in additional fields in the table of courses (as far as I know, either way should work).

Simplifying assumptions

To save your sanity and mine, please make the following simplifying assumptions:

• Each course meets in a single standard time slot (e.g. MWF 9:00-9:50, MW 2:25-3:40, TR 8:00-9:15, etc.; we're not worrying about 1-credit courses, or 4-credit courses, or courses that meet for 2-1/2 hours once a week, or any of that stuff)
• There's only one section of each course (i.e. you don't need to worry about CSC 170-001, CSC 170-002, CSC 170-003, etc.)
• There are three types of classrooms: CLC for "computer lab classroom", LEC for "lecture hall", and SCL for "science lab" (there are really more types than that, but that'll do for this assignment).
• There are no cross-listed courses (which show up under two different course numbers but are taught in the same room at the same time).

Constraints and queries

In order for the assignments to make sense, certain things have to be true:

• Each course must have exactly one classroom and exactly one time slot.
• There must not be two different courses in the same classroom at the same time.
• Each course must be in a classroom of the right type.
• Each course must be in a classroom with at least as many seats as the maximum number of students in that course.

But for most of these rules, probably the best thing to do is define a query that finds violations of the rule. So you would want to define the following queries:

• List all courses that don't have a classroom and a time slot assigned, showing which of these things if any have been assigned.
• List all courses that are assigned to the wrong type of classroom, with what classroom they're assigned to, what type it is, and what type they're supposed to be.
• List all courses that are assigned to a classroom that's too small, with what classroom they're assigned to, how many seats it has, and how many students might be in the course.
• List all classrooms, times, and courses that represent classroom conflicts (i.e. two or more courses are scheduled in the same room at the same time).
  Note: I haven't figured out how to do this yet, so it may be removed from the assignment -- or if it turns out to be fairly easy, it'll stay in the assignment.

In addition, in the process of making assignments, it'll be helpful to have queries like

• List all classrooms of a particular type with at least a specified number of seats. (For extra credit, read about "parameter queries" and have the classroom type and minimum number of seats be parameters entered by the user. If you're not trying for extra credit, just do a specific example like all the CLC classrooms with at least 20 seats.)
• List all the courses scheduled in a particular classroom, with time slots. (See above about parameter queries.)
• List all the classrooms of a particular type with at least a certain number of seats that are available in a specified time slot. (Ditto.)
  Note: I haven't figured out how to do this yet, so it may be removed from the assignment -- or if it turns out to be fairly easy, it'll stay in the assignment.

What to turn in

Turn in (by attaching it to an e-mail) an Access database file containing whatever tables you need, and the definitions of the queries I've asked for above. The tables should be filled in with a good selection of classrooms, courses, etc. -- let's say, at least ten classrooms, thirty courses, and five time slots.

Also fill in a "bad" assignment of courses to classrooms and time slots. By a "bad" assignment I mean one that violates the rules: there should be at least one example of a course that doesn't have a classroom or a time slot assigned, at least one example of a course assigned to the wrong type of classroom, at least one example of a course assigned to the right type of classroom but too small, at least one example of two courses assigned to the same classroom at the same time. This "bad" assignment is necessary so we can tell whether your queries work correctly to detect violations of the rules.