Reference no: EM131496789

Consider the relation schema R = (N, Y, P, M, C) and assume that the following set of functional dependencies hold on R:

F = { N → M, NY → P, M → C}

The letters can be interpreted as follows: R = (Model_Number, Year, Price, Manufacturing_Plant, Color).

1. Evaluate each of the following as a candidate key for R, giving reasons why it can or cannot be a key: N, NY, NC.

2. Find all the candidate keys of R.

3. Give a lossless-join decomposition of R into Boyce-Codd normal form. Make sure to use the algorithm studied in class (slide 8.46) and to show all details.

4. Does your decomposition preserve functional dependencies? Justify your answer.

5. Is R in 3NF?

6. Show that the functional dependency NY → P does not contain extraneous attributes.

7. Show that F is already in canonical cover form.

8. Use the algorithm we studied in class (slide 8.56) to find a lossless-join and dependency preserving decomposition of R into 3NF. Make sure to show all details.

9. Consider the decomposition d = (R1, R2) where R1 = (N, Y, P) and R2 = (N, M, C). Is this decomposition lossless-join? Make sure to justify your answer and to show all details.

10. Consider the decomposition d = (R1, R2, R3) where R1 = (N, M), R2 = (M, C) and R3 = (P, Y). Is this decomposition dependency preserving? Make sure to justify your answer and to show all details.