2023年11月25日

数据库设计代写 | Database Design Assignment 2

本次澳洲代写主要为数据库设计的assignment

Question 1 (15 marks)

Consider a relation ?(�, �, �,�, �, �,�, �, �) and its FD set � = { � → ��, �� → ��, �� →
��, � → �,�� → ��,� → �}.
1) Check if � → � ∈ F+. (2 marks)
2) Find a candidate key for ?. (2 marks)
3) Determine the highest normal form of ? with respect to �. Justify your answer. (2 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)
6) Decompose into a set of BCNF relations if it is not in BCNF step by step. Make sure your
decomposition is lossless-join. (3 marks)

Question 2 (10 marks)

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 t13 t14
R(A) W(A) R(C) W(C)
R(A) W(A)
R(B) W(B) R(A) W(A)
R(A) R(B) W(A) 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) Assume a checkpoint is made between t5 and t6, what should be done to the four
transactions when the crash happens between t11 and t12. (2 marks)

2) Is the transaction schedule conflict serializable? Give the precedence graph to justify your
answer. (2 marks)

3) Construct a schedule (which is different from above) of these four transactions which
causes deadlock when using two-phase locking protocol. You should clearly indicate all
the locks and the corresponding unlocks in your schedule. If no such schedule exists,
explain why. (3 marks)

4) Construct a schedule (which is different from above) of these four transactions which
does not cause deadlock when using two-phase locking protocol. You should clearly
indicate all the locks and the corresponding unlocks in your schedule. If no such schedule
exists, explain why. (3 marks)