The minimum requirement for active attendance: 3 tasks done
The tasks marked with '(**)' are counted as two tasks.
The background of these tasks is EN, Ch. 18 (query processing and
optimization) for task 1 and EN, Chs. 19, 21 for the other tasks.
1. In a nested-loop join it is advantageous to use the file with fewer blocks as the outer-loop file (EN, beginning of page 598). Is this rule always valid? Could you find counterexamples?
2. a) What kind of an operation sequence is included in a typical online banking terminal transaction (a cash withdrawal)? We assume that the card's verification code is checked locally (without a database) but the accounts are managed by a database. Which exceptions there can happen (concerning the transaction management)?
b) Suppose that a system makes reservations for several successive flights (from A to B with some transfers between) or for a holiday trip (with hotel, flights, and rental car). Are these situations somehow problematic from the transaction management point of view? (Consider the ACID properties, EN, p. 640, 1-4.)
3. Assume that the following records are found in the log file when the system crashes:
100: [start, T1] 101: [start, T2] 102: [write, T1, A, 10, 20] 103: [write, T2, B, 11, 22] 104: [commit, T1] 105: [start, T3] 106: [write, T3, A, 20, 30] 107: [abort, T2] 108: [checkpoint] 109: [start, T4] 110: [write, T3, B, 11, 33] 111: [write, T3, A, 30, 40] 112: [write, T4, C, 15, 25] 113: [commit, T3] 114: [write, T4, C, 25, 35]What recovery operations are needed? We assume that the PageLSN (page log sequence number) field of the page containing data item A is 106, the PageLSN for B is 110 and the PageLSN for C is 112.
4. a) What kind of a commit protocol should be used were it required that no redos be necessary in recovery? What drawbacks there are with this scheme?
b) Why are all undos performed in backward order, and all redos in forward order, from the log? Give a concrete example describing the significance of the backward/forward order. 5. According to the WAL (Write-Ahead Logging) the log is always written to the disk before the data. Does that mean that the log is more important than the database? (Give your arguments and explanation for the situation.)
6. (**) Let us assume that the log contains also the [read ...] records as in the following example (describing the log contents when the system crashes):
100: [start, T3] 101: [read, T3, C] 102: [write, T3, B, 15, 12] 103: [start, T2] 104: [read, T2, B] 105: [write, T2, B, 12, 18] 106: [start, T1] 107: [read, T1, A] 108: [read, T1, D] 109: [write, T1, D, 20, 25] 110: [read, T2, D] 111: [write, T2, D, 25, 26] 112: [read, T3, A]a) What the log tells about the initial values of the variables?
The Finnish lecture notes are available via the Finnish course page ('Luentokalvot') in pdf-format or in a course folder in room A412 (on paper).