Download Database Management Systems Exam and more Cheat Sheet Computer science in PDF only on Docsity!
CS 245, Database Management Systems
Endsem Exam, Winter 2023-
Department of Computer Science and Engineering
IIT Guwahati
Time: Three hours
- Suppose that we have the following four tuples in a relation S(A, B, C) with three attributes:
Which of the following functional and multivalued dependencies can you infer does not hold over the relation S? No justification is needed. (10) (a) A โ B (b) A โโ B (c) BC โ A (d) BC โโ A (e) B โโ C
Solution: The dependency (c) does not hold. The given instance satisfies all the other dependencies.
- Consider the schema R = (A, B, C, D, E, G, H) and the set F of functional dependencies:
AB โ CD
D โ C
DE โ B
DEH โ AB
AC โ DC
(a) List all the candidate keys for R. You donโt have to show the computation. Just write the answer. (5)
Solution: The candidate keys are ABEGH, ACEGH and DEGH.
(b) Is the schema in 3NF? Justify your answer. (5)
Solution: The schema is in 3NF since the attributes on the rhs of every non- trivial dependency is part of a candidate key.
- Construct a B+-tree for the following set of key values.
Assume that the tree is initially empty and the keys are added in the above order. Take the number n of pointers that fit in one node to be four. It is enough if you show the final configuration of the tree. (10)
Solution:
19
You can check the answer by using the B+-tree visualization tool at https://www. cs.usfca.edu/~galles/visualization/BPlusTree.html.
- Answer the following questions for the schedule S involving the transactions T 1 , T 2 , T 3 and T 4 shown below.
S : r 1 (A); r 2 (A); r 1 (B); r 2 (B); r 3 (A); r 4 (B); w 1 (A); w 2 (B);
Here ri(X) and wi(X) denote a read and a write instruction respectively on data item X by transaction Ti. (a) What is the precedence graph for the schedule S? (5)
Solution: The precedence graph for S on the set of vertices V = {T 1 , T 2 , T 3 , T 4 } is shown below.
- The following is a schedule on two transactions T 1 and T 2 with one instruction missing.
r 1 (A); r 2 (B); r 1 (B); ???; w 1 (C); w 2 (A);
Here ri(X) and wi(X) denote a read and a write instruction respectively on data item X by transaction Ti. (a) Which read operation ri(X) can replace the ??? and make the schedule non-conflict- serializable. Here X is a data item from A, B and C. Draw the corresponding precedence graph. (5)
Solution: The read operation r 2 (C) in place of ??? will make the schedule non- conflict-serializable. The precedence graph on the set of vertices {T 1 , T 2 } will then have both the edges (T 1 , T 2 ) and (T 2 , T 1 ), and hence contain a cycle.
(b) Which write operation wi(X) can replace the ??? and make the schedule non- conflict-serializable. Here X is a data item from A, B and C. Draw the corre- sponding precedence graph. (5)
Solution: The write operation w 1 (B) (or alternatively, w 2 (C)) in place of ??? will make the schedule non-conflict-serializable. The precedence graph on the set of vertices {T 1 , T 2 } will then have both the edges (T 1 , T 2 ) and (T 2 , T 1 ), and hence contain a cycle.
- Consider the following tree of lockable items in a database used by transactions following the tree protocol.
A
B
D E
F G
C
The transactions that operate on the tree are shown below, where ri(X) and wi(X) denote a read and a write instruction respectively on data item X by transaction Ti.
T 1 : r 1 (A); r 1 (B); r 1 (E); T 2 : r 2 (A); r 2 (C); r 2 (B); T 3 : r 3 (B); r 3 (E); r 3 (F );
Show a valid concurrent schedule with these three transactions when they follow the tree protocol, such that each transaction completes at least one instruction successfully before any other transaction completes. Recall that the tree protocol uses only one kind of lock, an exclusive lock. Your schedule must have explicit lock and unlock instructions along with the read and write operations, for example li(X) means Ti requests the lock on X and ui(X) means Ti releases the lock on X. (10)
Solution: The following is one possible concurrent schedule with the mentioned property.
T 1 T 2 T 3
l(A) r(A) l(B) r(B) u(A) l(A) r(A) l(C) r(C) u(A) l(E) u(B) l(B) r(B) r(E) u(E) l(E) u(B) r(E) l(F ) r(F ) u(F ) l(B) r(B) u(B)
- When T 1 attempts w 1 (C) it waits for T 2 , as T 1 is older than T 2.
- When T 4 attempts w 4 (D) by T 4 it is rolled back as T 4 is younger than T 2.
- When T 2 attempts w 2 (D) it is rolled back as T 2 is younger than T 1. Once T 2 is rolled back, T 1 can make progress.
- Suppose the timestamp-ordering protocol is used for concurrency control and consider the following sequence of events.
st 1 ; st 2 ; st 3 ; r 1 (A); r 3 (B); w 1 (C); r 2 (B); r 2 (C); w 3 (B); w 2 (A).
Here sti means the transaction Ti starts. The events ri(X) and wi(X) denote the read and write operations by transaction Ti on data item X. The sequence shows the intended order of execution of the events and this may be different from the actual order because of rollbacks. Assume that the scheduler allocates timestamps to the transactions in the order of their start time, and that a rolled back transaction is not executed again during the execution of this sequence. What happens as the above sequence executes? Mention which operations succeed and which ones fail, leading to rollback of the corresponding transaction. Give proper justification for each operation. (10)
Solution:
- We have T S(T 1 ) < T S(T 2 ) < T S(T 3 ) from the order in which the transactions start.
- The initial event sequence r 1 (A); r 3 (B); w 1 (C); executes successfully since these are the first accesses to the corresponding data item. The timestamps R-T S(A), R-T S(B) and W - T S(C) are set to T S(T 1 ), T S(T 3 ) and T S(T 1 ) respectively.
- The event r 2 (B) by T 2 succeeds as it does not conflict with the earlier r 3 (B) by T 3 , since two reads on a data item are non-conflicting. The timestamp R-T S(B) does not change since T 3 is younger than T 2.
- The event r 2 (C) by T 2 succeeds as T 2 is younger than T 1 which did w 1 (C) earlier. The R-T S(C) is updated to the value of T S(T 2 ).
- The event w 3 (B) by T 3 succeeds as T 3 is younger than T 2 which did r 2 (B) earlier. The timestamp W - T S(B) is updated to the value of T S(T 3 ).
- The event w 2 (A) by T 2 succeeds as T 2 is younger than T 1 which did r 1 (A) earlier. The W - T S(C) is updated to the value of T S(T 2 ).
Hence no transaction is rolled back in this sequence of events.
