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.
- All of the parts of this question are about the following SQL query:
WHERE class = 1
AND grade = 'B'
AND Student.id = Enrollment.id
AND Enrollment.courseNum = Course.number
The schema of the three relations is:
- Consider the following execution:
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.
- T1 inserts into P1 (page 1)
- T1 updates on P3
- T2 updates on P1
- T1 commit
- T2 updates on P2
- T2 deletes from P1
- T3 deletes from P3
- T1 end
- T2 abort
- T3 updates P1
- T2 end
- 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.
- After step 5 assuming that there are 3 buffer pages available.
- After step 8 assuming that there are 3 buffer pages available.
- After step 11 assuming that there are 3 buffer pages available.
- After step 8 assuming that there are 2 buffer pages available.
- 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.
- 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.
- 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
- T1:R(X), T2:W(X), T1:W(X), T2: Abort, T1:Commit
- T1:W(X), T2:R(X), T1:W(X), T2: Commit
- Consider the following two transactions:
if (A == 0) then B=B+1;
if (B == 0) then A=A+1;
Let the consistency requirements be that A=0 or B=0, and the initial conditions be that A = B= 0.
- Show that every serial execution of the two transactions preserves the consistency of the database.
- Show a concurrent execution of T1 and T2 that produces a nonserializable schedule.
- 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.
- 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.