COMP9311- Assignment 2 Solved

Question 1
Consider a relation 𝑅(𝐴, 𝐵, 𝐶, 𝐷, 𝐸, 𝐺, 𝐻, 𝐼, 𝐽) and its FD set 𝐹 = {𝐴 → 𝐷𝐸, 𝐵 → 𝐺𝐼, 𝐸 → 𝐶𝐷, 𝐶𝐸 → 𝐴𝐷𝐻, 𝐻 → 𝐺, 𝐴𝐻 → 𝐼}.

Check if 𝐴 → 𝐼 ∈ F+. 
Find a candidate key for 𝑅. 
Determine the highest normal form of 𝑅 with respect to 𝐹. Justify your answer. 
Find a minimal cover 𝐹𝑚 for 𝐹.
Decompose into a set of 3NF relations if it is not in 3NF step by step. Make sure your decomposition is dependency-preserving and lossless-join. 
Question 2 
Consider the schedule below. Here, R(*) and W(*) stand for ‘Read’ and ‘Write’, respectively.  ,    ,            and      represent four transactions and ti represents a time slot.

Time
t1
t2
t3
t4
t5
t6
t7
t8
t9
t10
t11
t12
 
R(B)
 
 
 
 
R(A)
W(B)
 
 
 
W(A)
 
 
 
 
 
 
 
 
 
R(B)
 
 
 
W(B)
 
 
 
R(A)
W(A)
 
 
 
R(A)
 
 
W(A)
 
 
 
R(B)
W(B)
 
 
Each transaction begins at the time slot of its first Read and commits right after its last Write (same time slot). 

Regarding the following questions, give and justify your answers.

Is the transaction schedule conflict serialisable? Give the precedence graph to justify your answer. (4 marks)
Give a serial schedule of these four transactions. (3 marks)
Lock the transactions  and       according to the simple locking scheme. You only need to consider the order of the operations, not the timestamps. (3 marks)