[SOLVED] COMP9311-Assignment 2

Description

Question 1  

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

• Check if 𝐶 → 𝐽 ∈ F⁺. ()
• List all the candidate keys for 𝑅. ()
• Find a minimal cover 𝐹_𝑚 for 𝐹. ()
• Decompose into a set of 3NF relations if it is not in 3NF. Make sure your decomposition is dependency-preserving and lossless-join. Justify your answers. )

Question 2

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

• How many super keys can be found for R? Compute the total number of super keys and list 5 of them.
• Determine the highest normal form of 𝑅 with respect to 𝐹. Justify your answer. ()
• Regarding F, is the decomposition R₁ = {𝐴𝐵𝐶𝐷𝐸}, R₂ = {𝐸𝐺𝐻}, R₃ = {𝐸𝐼𝐽} of 𝑅 dependency-preserving? Please justify your answer. )
• Regarding F, is the decomposition R₁ = {𝐴𝐵𝐶𝐷𝐸}, R₂ = {𝐸𝐺𝐻}, R₃ = {𝐸𝐼𝐽} of 𝑅 lossless-join? Please justify your answer. )
• Decompose it into a collection of BCNF relations if it is not in BCNF. Make sure your decomposition is lossless-join and briefly justify your answers. )