Question 1
Consider a relation π (π΄, π΅, πΆ, π·, πΈ, πΊ, π», πΌ, π½) and its FD set πΉ = {π΄π΅ β πΆπΈ, π· β πΊπ», πΈ β π΅πΆπ·, πΆ β π·πΌ, π» β πΊ, πΈπ» β πΌ}.
- Check if πΆ β π½ β F+. (1 mark)
- List all the candidate keys for π . (2 marks)
- Find a minimal cover πΉπ for πΉ. (2 marks)
- 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
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. (2 marks)
- Determine the highest normal form of π with respect to πΉ. Justify your answer. (2 marks)
- Regarding F, is the decomposition R1 = {π΄π΅πΆπ·πΈ}, R2 = {πΈπΊπ»}, R3 = {πΈπΌπ½} of π dependency-preserving? Please justify your answer. (2 marks)
- Regarding F, is the decomposition R1 = {π΄π΅πΆπ·πΈ}, R2 = {πΈπΊπ»}, R3 = {πΈπΌπ½} of π lossless-join? Please justify your answer. (3 marks)
- 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)
