COMP9311 Assignment 2 Solved

Question 1

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

1. 1)  Check if 𝐴 → 𝐼 ∈ F+.
2. 2)  Find a candidate key for 𝑅. (
3. 3)  Determine the highest normal form of 𝑅 with respect to 𝐹. Justify your answer. (3 marks)
4. 4)  Find a minimal cover 𝐹 for 𝐹. (3 marks) 𝑚
5. 5)  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.

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.

1. 1)  Is the transaction schedule conflict serialisable? Give the precedence graph to justify your answer. (4 marks)
2. 2)  Give a serial schedule of these four transactions. (3 marks)