Problem 1 (4.8 pts, 0.4 pts/question)
Assume the relation Sailors(sid: integer, sname: string, rating: integer, age: real), where attribute sid is the primary key of the relation, and the following queries:
Q1: SELECT * FROM Sailors S WHERE S.age = 20
Q2: SELECT * FROM Sailors S WHERE S.age > 20
Q3: SELECT * FROM Sailors S WHERE S.age = 20 AND S.rating = 10
For each of the following indexes, answer whether the index matches (see definition in textbook on page 398) the selection predicate in each of the above three queries.
Index1: A hash index on age.
Index2: A B+-tree index on age.
Index3: A hash index on (age, rating).
Index4: A B+-tree index on (age, rating).
Problem 2 (5.2 pts., 1.7 pts for (a) and 1.75 pts each for (b) and (c))
Consider a database of two items A and B. Initial values are A = B = 0. The consistency requirement is that A = 0 or B = 0 (i.e., at least one of A and B must be zero).
Assume that transactions T1 and T2 are pseudo-coded as follows:
T1: read(A); read(B); if (A == 0) then {B = B+1; write(B)}
T2: read(B); read(A); if (B == 0) then {A = A+1; write(A)}
Show that T1 and T2 re correct by showing that every serial execution involving these two transactions preserves the consistency of the database.
Show a concurrent (interleaved) execution of T1 and T2 which produces a non serializable execution, i.e., an execution that is not equivalent to any serial schedule.
Is it possible that T1 and T2 would run concurrently under Strict 2PL? If yes, show such a schedule; if no, explain why not.