D64 Study Questions

I've listed some questions that you might find useful for studying for the final. These are mostly taken from old homework which means that they were based on a different book. Bear this in mind if you run across something that looks unfamiliar.

  1. All of the parts of this question are about the following SQL query:
     
    SELECT *
    FROM Student,Enrollment,Course
    WHERE class = 1
      AND grade = 'B'
      AND Student.id = Enrollment.id
      AND Enrollment.courseNum = Course.number
    The schema of the three relations is:
  2. Consider the following execution:
    1. T1 inserts into P1 (page 1)
    2. T1 updates on P3
    3. T2 updates on P1
    4. T1 commit
    5. T2 updates on P2
    6. T2 deletes from P1
    7. T3 deletes from P3
    8. T1 end
    9. T2 abort
    10. T3 updates P1
    11. T2 end
       
    Answer the following questions.  For all of them, assume that separate buffer pages are available to store the log tail.  Further, assume that the buffer manager uses LRU replacement. This question is based on the recovery scheme passed out in the extra chapter.
    1. For each point in the execution listed below, show the log, transaction table, dirty page table, LSN on each page in both memory and on the disk, and the minimum log record that must have been flushed to disk.
      1. After step 5 assuming that there are 3 buffer pages available.
      2. After step 8 assuming that there are 3 buffer pages available.
      3. After step 11 assuming that there are 3 buffer pages available.
      4. After step 8 assuming that there are 2 buffer pages available.
    2. Assume that there are 3 buffer pages available and that the DBMS crashed after step 8.  Show all of the information requested in the last part after the analysis phase, the redo phase, and the undo phase.
    3. Assume that there are 3 buffer pages available and that the DBMS crashed after step 8.  It also crashes during the undo phase of recovery after writing two new log records.  Repeat the previous question for the DBMS when it tries to recover for the second time.

      NOTE: This problem was from a different book so ignore the details we didn't cover.

  3. Consider the following classes of schedules: serializable, conflict serializable, recoverable, avoids-cascading abort, and strict.  For each of the following schedules, state which of the above classes it belongs to.  If you cannot decide whether a schedule belongs to a certain class based on the listed actions, explain briefly.

    The actions are listed in the order that they are scheduled.  if a commit or abort is not shown, the schedule is incomplete.
     

    1. T1:R(X), T2:W(X), T1:W(X), T2: Abort, T1:Commit
    2. T1:W(X), T2:R(X), T1:W(X), T2: Commit
     
  4. Consider the following two transactions:
     
    T1: read(A);
        read(B);
        if (A == 0) then B=B+1;
        write(B);
    T2: read(B);
        read(A);
        if (B == 0) then A=A+1;
        write(A);
    Let the consistency requirements be that A=0 or B=0, and the initial conditions be that A = B= 0.
    1. Show that every serial execution of the two transactions preserves the consistency of the database.
    2. Show a concurrent execution of T1 and T2 that produces a nonserializable schedule.
    3. Is there a concurrent execution of T1 and T2 that produces a serializable schedule?  Note that the problem asks about a serializable schedule and not a conflict serializable schedule.
     
  5. Give an example a of conflict serializable schedule that could not have been produced by a two phase locking scheduler.  Assume that the system only supports exclusive locks which must be granted before a read or a write.  Very simple solutions to this problem exist so don't go overboard on your schedules.