Tutorial 5

We will keep working with the same domain, where we have the following tables:

• Teachers(name, room)
• TeacherTitles(teacher, title)
• Rooms(name, nrSeats)
• Courses(name, teacher, nrStudents)
• Classes(course, day, hour)

You can also assume these two helper tables, to specify what days and times are valid for classes:

• ClassDays(day)
• ClassTimes(time)

The contents of these two tables are (Monday-Saturday, Someday) for ClassDays, and (8, 10, 13, 15, 17, 23) for ClassTimes.

JDBC

Your task is to write a few programs that perform useful operations on the database. For each of these tasks, you don't need to write the full Java code for the program. It is enough to write pseudo-code for the parts that are purely Java, and concentrate on the parts where you communicate with the database.

1. Sketch a JDBC program for booking lectures. The program should take as input a course and a day of the week. It should then respond to the user with a list of times when the classroom that the course is held in is unoccupied. The user should pick one of these, and the system should insert a class for the course at that time.
   If there are no available times, the program should instead respond to the user with a list of all possible times (given by ClassTimes). The user should again pick one of these, and the system should insert a class at that time on Someday instead of the day the user provided.
   
   
2. Sketch a JDBC program that ensures that all students always get a seat on all classes. It should do this by looking up all courses, and ensure that the room for the course has more seats than there are number of students in the course. If this is not true, the program should extend the number of seats in that room so that all students fit in.
   
   
3. Sketch a JDBC program for maintaining Somedays. Every time it is run, it should get a list of all classes that are scheduled for Someday, and try to add them at random to one of the normal weekdays, not Sunday, at the same time of day that the class was to have been given originally. The program should run "forever", and try to schedule classes whenever it gets the chance. Between attempts it should sleep for a random period of time.
   
   

Also do the following exercises from "Database Systems: The Complete Book, 2/E":

• 9.3.1 a,d using JDBC (i.e. 9.6.1 a,d)

If you have the first edition of the textbook, the following exercises are similar:

• 8.1.1 b,c using JDBC (i.e. 8.5.1 b,c)

Transactions

For each of the programs above, consider what could happen if it is run

• on its own
• in parallel with another instance of the same program
• in parallel with one of the other two programs

when run in each of the four levels of isolation, i.e.

• serializable
• read committed
• read uncommitted
• repeatable read

For each program, suggest a proper level of isolation to get the desired behavior, or if that is impossible, suggest how to solve it instead.

Also do the following exercises from "Database Systems: The Complete Book, 2/E":

• 6.6.1 c,d
• 6.6.2 (for c,d)

If you have the first edition of the textbook, the following exercises are similar:

• 8.6.1 d,a
• 8.6.2 (for d,a)