COMP9311 Assignment 2 Solution

Question 1 
Consider a relation 𝑅(𝐴, 𝐵, 𝐶, 𝐷, 𝐸, 𝐺, 𝐻, 𝐼, 𝐽) and its FD set 𝐹 = {𝐴 → 𝐷𝐸, 𝐵 → 𝐺𝐼, 𝐸 → 𝐶𝐷, 𝐶𝐸 → 𝐴𝐷𝐻, 𝐻 → 𝐺, 𝐴𝐻 → 𝐼}.
1) Check if 𝐴 → 𝐼 ∈ F+. (3 marks)
2) Find a candidate key for 𝑅. (3 marks)
3) Determine the highest normal form of 𝑅 with respect to 𝐹. Justify your answer. (3 marks)
4) Find a minimal cover 𝐹𝑚 for 𝐹. (3 marks)
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. (3 marks)
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.
1) Is the transaction schedule conflict serialisable? Give the precedence graph to justify your answer. (4 marks)
2) Give a serial schedule of these four transactions. (3 marks)
3) 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)

Assignment Submission
We accept electronic submissions only. Please submit your assignments as follows:
• The file name should be ass2.pdf.
• Log into the CSE server, ensure that you are in the directory containing the file to be submitted. (note: we only accept files with .pdf extension)
• Type “give cs9311 ass2 ass2.pdf” to submit.
• You can also use the web give system to submit.
• In case that the system is not working properly, you must take the following actions:
1) Please keep a screen capture (including timestamp and the size of the submitted file) for your submissions as proof. If you are not sure how, please have a look at the guidelines.
2) Please keep a copy of your submitted file on the CSE server. If you are not sure how, please have a look at taggi.
Note:
1. If the size of your pdf file is larger than 2MB, the system will not accept the submission. If you face this problem, try converting to compress pdf.
2. If you have any problems in submissions, please email to comp9311unsw@gmail.com.
3. We do not accept e-mail submissions.
20% per day. In addition, you will receive zero mark if you submit after we release the solution.