Consider the following two transactions: TI-R(B), W(B), R(C), R(A) T2-R(B). W(B) Say that sharing lock, exclusive lock, and unlock actions are inserted by the scheduler, resulting in the following annotated transactions: T1= X(B), R(B), W(B), S(C), R(C), S(A), U(B), R(A), U(C), U(A) T2-X(B), R(B), W(B), U(B) Cascading rollback is not possible for the above schedule. O True O False

1) For the following “Producer-consumer Problem using Semaphores”, (a) Will there be any deadlock if the order of the downs were switched, for the producer or for the consumer individually as well as together (so, 3 combinations)? Explain. Now, (b) similarly consider the order of ups and show (3 combinations again) – when you assume that the ups are switched then keep the downs intact (not switched).

Find the minimal number of block transfers and seeksrequired using the nested-loop join strategy on the instructor and department relations. Assume that the block size is 1000 bytes, instructor has 500 tuples of 10 bytes each, and department has 70 tuples of 20 bytes each. Assume that no blocks are kept resident in memory (i.e. worst case scenario).

Please explain

Consider the join r ▷◁ s of two relations r and s whose common attribute set is {A}. Physically, r is stored on 25 blocks and s on 21 blocks on the disk, tuples in both relations are unordered. Assume that the buffer pool allocated for carrying out the join has 3 frames. Compare block nested-loop join against merge join in facilitating r ▷◁ s by analyzing their I/O costs. The I/Os for exporting the final joined results to the disk is called the reporting cost, which shall be excluded from the calculation of the I/Os of r ▷◁ s, because the reporting costs of both nested-loop join and merge join cancel each other out in the comparison. Specifically, (a) Compute the # of I/Os, excluding the reporting cost, engaged by block nested-loop join. (b) Compute the # of I/Os, excluding the reporting cost, engaged by merge join in the worst-caseand best-case scenarios, respectively. Here a scenario indicates an instantiation of the tuples in rand s.

Q2. Give the optimal estimate for the disk-IO cost of r JOIN s using (2.a) Sort-merge join, assuming that the two relations are sorted on the join attributes and r's join attribute is a key for r. (2.b) Block nested loop join Assume r has 2,000 tuples with 20 tuples per block; s has 4,000 tuples with 4 tuples per block; and the main memory buffer for this operation is 22 blocks.

344 Q2 (No HANDWRITING):

Consider a transaction dataset where the possible items are P, Q, R, S, T, X, Y, and Z. Now consider the following 3-itemsets:       {P, Q, R},  {P, Q, X},  {P, Q, T},  {P, T, X},  {P, T, Y},  {Y, Q, X},  {T, X, Y} If {Q, X} is infrequent, which of the above 3-itemsets must also be infrequent?

Suppose user A, who has all authorization privileges on a relation r, grants select on relation r to public with grant option. Suppose user B then grants select on r to A. Does this cause a cycle in the authorization graph? Explain why.

A transaction database contains ten transactions shown below and minimum_support = 30%. Using A-priori, find all frequent itemsets that contain item i. How many frequent itemsets that contain i do we have? TID Items bought { s, u, s, a, n} { s, a, r, a} { s, a, m} 1 2 3 {r, i, c, h, a, r, d} {e,r, i, c} {n, i, c, k} {p, a, t, r, i, c, k} {e, m, i, 1, y} {c,h, a, r, l, e, s} 6. 7 8 9. 10 [lidal