CS 4250: Database Management Systems
Fall 2025 - Homework 4
Concurrency Control and Recovery Homework
Due Wednesday, 10 December 2025, at midnight
Submit to Canvas (by midnight), or turn in a typed
hardcopy during class time on the due date.
(Strongly prefer plain text.
Will accept PDF or MSWord documents, if typed.)
Handwritten answers, on paper or as photographs, strongly discouraged.
This is an individual assignment. All work must be entirely your own.
You should not look at any other student's work (in whole or in part, on paper
or on screen), nor allow anyone else to look at yours, nor discuss the assignment
with other students, during the course of this assignment. Nor may you submit
the work of software programs as your own.
Put your own name at the top of your own homework answers.
Note: T<number> identifies a transaction numbered number.
R(<letter>) identifies a read operation on database object letter.
W(<letter>) identifies a write operation on database object letter.
Questions
- Consider the following schedule:
T2: R(B), T2: R(A), T1: R(C), T3: R(A), T1: W(C), T2: W(C), T2: R(A), T1: R(B) , T3: R(A), T2: W(A), T3: R(D), T2: W(D), T1: W(B), T1: W(B), T3: R(B)
Is the schedule serializable? If so, show an equivalent serial transaction
order. If not, precisely describe why not.
If relevant, fill in this table with the equivalent serial transaction
order. Time proceeds from left to right, with only one action possible in each
time slot.
| Schedule |
Time 1 | Time 2 | Time 3 |
Time 4 | Time 5 | Time 6 |
Time 7 | Time 8 | Time 9 |
Time 10 | Time 11 | Time 12 |
Time 13 | Time 14 | Time 15 |
| T1 |
| | | |
| | | |
| | | |
| | |
| T2 |
| | |
| | |
| | |
| | |
| | |
| T3 |
| | |
| | |
| | |
| | |
| | |
- Consider the following schedule:
T2: R(A), T1: R(B), T3: W(B), T2: R(C), T1: W(D), T2: W(C), T1: R(A), T1: W(A), T2: R(D), T2: W(D), T2: W(A), T3: R(D), T3: W(D)
Is the schedule serializable? If so, show an equivalent serial transaction
order. If not, precisely describe why not.
If relevant, fill in this table with the equivalent serial transaction
order. Time proceeds from left to right, with only one action possible in each
time slot.
| Schedule |
Time 1 | Time 2 | Time 3 |
Time 4 | Time 5 | Time 6 |
Time 7 | Time 8 | Time 9 |
Time 10 | Time 11 | Time 12 |
Time 13 | Time 14 | Time 15 |
| T1 |
| | | |
| | | |
| | | |
| | |
| T2 |
| | | |
| | | |
| | | |
| | |
| T3 |
| | | |
| | | |
| | | |
| | |
For questions 3-5,
consider the execution of the ARIES recovery algorithm given the following log:
| LSN | Log Record |
| 00 | begin_checkpoint |
| 05 | end_checkpoint |
| 10 | Update: T1 writes P2 |
| 20 | Update: T1 writes P4 |
| 30 | Update: T3 writes P1 |
| 40 | T1 abort |
| 50 | Update: T2 writes P3 |
| 60 | CLR: Undo T1 LSN 10 |
| 70 | Update: T4 writes P2 |
| 80 | CLR: Undo T1 LSN 20 |
| 90 | Update: T3 writes P3 |
| 100 | T4 commit |
| 110 | Update: T2 writes P2 |
| 120 | T3 abort |
| 130 | T1 end |
|
|
X - crash, restart |
For the questions below, when you are asked which log records are read,
you are to supply the exact list of LSNs from log above. When data pages
are asked for, you are to supply the exact list of page identifiers from the
log above. And so on. Be specific and concrete in your answers, answering
specifically for the provided log.
Operations can be identified using the LSN for the log record recording that operation.
(So, of course, can the log record itself.)
Generic statements or quotations from the
textbook (or slides) will earn 0 points. Example: "all the pages" == 0 points.
"P1, P2 and P3" == more than 0 points, assuming those are the pages read. (Don't use
English. List the log record numbers, list the page numbers, etc.)
For questions 3-5, parts a-c are separate questions. Answer a through c separately.
Label your answers so that it is clear what is your answer to '3.a' and what
is your answer to '4.c', and so on.
- During Analysis:
- a) What log records are read?
- b) What are the contents of the Dirty Page Table (DPT) and
the transaction table at the end of the analysis stage?
- During Redo:
- a) What log records are read?
- b) What data pages are read?
- c) What operations are redone?
(Assume no updates made it out to stable storage, like a hard disk, before the crash,
except updates written to stable storage as part of a transaction commit.)
- During Undo:
- a) What log records are read?
- b) What operations are undone?
Item below removed from Fall 2025 Homework 4.
- Consider these tables (from Homework 2).
Shuttle(shuttleID: integer, shuttlename: string, launchdate: date, enddate: date, currentlocation: string, massKg: integer)
Flight(flightID: integer, shuttleID: integer, plannedlaunchdate: date, actuallaunchdate: date, returndate: date, landingsite: string, commanderID: integer)
FlewOn(flightID: integer, astroID: integer, role: string);
Assume the Shuttle table has 100 tuples in it, the Flights table has 1,000 tuples in it, and the FlewOn table has 8,000 tuples in it.
Also consider this question:
Find the shuttle identifier, shuttle name, flight identifier and planned launch date for each shuttle flight that has some astronaut flying on the flight in the role of "Payload Specialist 1".
One possible solution to the question is:
SELECT S.shuttleID, S.shuttlename, F.flightID, F.plannedLaunchDate
FROM Shuttle S, Flight F, FlewOn FO
WHERE S.shuttleID = F.shuttleID AND F.flightID = FO.flightID AND FO.role = "Payload Specialist 1";
a) Translate this SQL query into a corresponding relational algebra expression (and write down the expression).
b) Then draw a System-R style query plan for your relational algebra expression.
N.B. There were 135 real space shuttle missions and 6 space shuttles.
The numbers above are entirely fictional.