COMP9311 Assignment 2 Solution

Consider a relation 𝑅(𝐴, 𝐵, 𝐶, 𝐷, 𝐸, 𝐺, 𝐻, 𝐼, 𝐽) and its FD set 𝐹 = {𝐴𝐵 → 𝐶𝐸, 𝐷 → 𝐺𝐻, 𝐸 → 𝐵𝐶𝐷, 𝐶 → 𝐷𝐼, 𝐻 → 𝐺, 𝐸𝐻 → 𝐼}.
1) Check if 𝐶 → 𝐽 ∈ F+. (1 mark)
2) List all the candidate keys for 𝑅. (2 marks)
3) Find a minimal cover 𝐹𝑚 for 𝐹. (2 marks)
4) 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. (3 marks)
Question 2 (12 marks)
Consider a relation 𝑅(𝐴, 𝐵, 𝐶, 𝐷, 𝐸, 𝐺, 𝐻, 𝐼, 𝐽) and its FD set 𝐹 = {𝐴𝐵 → 𝐶𝐸, 𝐷 → 𝐺𝐻, 𝐸 → 𝐵𝐶𝐷, 𝐶 → 𝐷𝐼, 𝐻 → 𝐺, 𝐸𝐻 → 𝐼}.
1) How many super keys can be found for R? Compute the total number of super keys and list 5 of them. (2 marks)
2) Determine the highest normal form of 𝑅 with respect to 𝐹. Justify your answer. (2 marks)
3) Regarding F, is the decomposition R1 = {𝐴𝐵𝐶𝐷𝐸}, R2 = {𝐸𝐺𝐻}, R3 = {𝐸𝐼𝐽} of 𝑅 dependency-preserving? Please justify your answer. (2 marks)
4) Regarding F, is the decomposition R1 = {𝐴𝐵𝐶𝐷𝐸}, R2 = {𝐸𝐺𝐻}, R3 = {𝐸𝐼𝐽} of 𝑅 lossless-join? Please justify your answer. (3 marks)
5) 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. (3 marks)
Assignment Submission
We accept electronic submissions only. Please submit your assignments as follows:
• The file name should be ass2.pdf.
• 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.
• Please keep a screen capture (including timestamp and the size of the submitted file) for your submissions as proof in case that the system is not working properly. If you are not sure how, please have a look at the FAQ.
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.
Zero mark