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)

JDBC code

Extracts from three JDBC programs are given below. Each program can be run as a transaction, and each program/transaction contains several steps.

• Program 1 has steps: <Q1.1> <Q1.2> <Q1.3> <I1.4>
• Program 2 has steps: <Q2.1> <U2.2>
• Program 3 has steps: <Q3.1> <Q3.2> <Q3.3> <D3.4> <I3.5>

Later we shall consider what can happen when these programs are run concurrently with each other, and with other instances of themselves.

(Note: only extracts from these programs are shown here. Symbols /* and */ are placeholders for some Java code, and a lot of boilerplate code, e.g. for connecting to the database, has been left out.)

First read the programs and make sure you understand what they do. In particular, determine what each of the database steps does.

---

Program 1 ("booking classes"):

/* get values for the variables course and day from the user */
ResultSet rs = stmt.executeQuery( // Q1.1
    "SELECT room FROM Courses C, Teachers T WHERE teacher = T.name AND C.name ='" 
    + course + "'");
rs.next();
String room = rs.getString(1);
String getTimes = "SELECT * FROM ClassTimes WHERE time NOT IN ("
    + "SELECT hour FROM Classes A, Courses C, Teachers T WHERE "
    + "course = C.name AND teacher = T.name AND "
    + "room = '" + room + "' AND day = '" + day + "')";
rs = stmt.executeQuery(getTimes); //Q1.2
if ( !rs.next() ) {
    rs = stmt.executeQuery("SELECT * FROM ClassTimes"); //Q1.3
    day = "Someday";
    rs.next();
}

do {
    /* print time */
} while ( rs.next() );

/* get chosen time into variable hour */
stmt.executeUpdate( //I1.4
    "INSERT INTO Classes VALUES ('" + course + "','" + day + "'," + hour + ")")

---

Program 2 ("adapt room sizes")

String getRooms = "SELECT R.name, nrStudents "
    + "FROM Courses C, Teachers T, Rooms R " 
    + "WHERE teacher = T.name AND room = R.name " 
    + "AND nrStudents > nrSeats";
ResultSet rs = stmt.executeQuery(getRooms); //Q2.1
while ( rs.next() ) {
    stmt.executeUpdate("UPDATE Rooms SET nrSeats = " + rs.getInt(2)
        + " WHERE name = '" + rs.getString(1) + "'"); //U2.2
}

---

Program 3 ("Reschedule classes")

String[] days = new String[6];

ResultSet rs = stmt.executeQuery( //Q3.1
    "SELECT * FROM ClassDays WHERE day != 'Someday'");
int i = 0;
while ( rs.next() ) {
    days[i++] = rs.getString(1);
}

while (true) {
    /* Sleep for a random time */
    String getClasses = "SELECT C.name, room, hour " 
        + "FROM Classes A, Courses C, Teachers T WHERE day='Someday'"
        + " AND course=C.name AND teacher=T.name";
    ResultSet rs = stmt.executeQuery(getClasses); //Q3.2
    while ( rs.next() ) {
        String course = rs.getString(1);
        String room = rs.getString(2);
        int hour = rs.getInt(3);
        /* get a random number between 1 and 6 into variable i */
        day = days[i];
        String clashes = "SELECT * FROM Classes A, Courses C, Teachers T "
            + "WHERE course = C.name AND teacher = T.name "
            + "AND day = '" + day + "' AND hour = " + hour " AND room=" + room;
        ResultSet rs2 = stmt2.executeQuery(clashes); //Q3.3
        if ( !rs2.next() ) {
            stmt2.executeUpdate("DELETE FROM Classes WHERE day = 'Someday' "
                + " AND hour = " + hour
                + " AND course = '" + course + "'"); //D3.4
            stmt2.executeUpdate("INSERT INTO Classes VALUES ('"
                + course + "','" + day + "'," + hour + ")"); //I3.5
        }
    }
}

---

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

Consider what happens when they are run in each of the four levels of isolation, i.e.

• serializable
• repeatable read
• read committed
• read uncommitted

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.

Book exercises

The following exercises from "Database Systems: The Complete Book, 2/E" are relevant:

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