Reference no: EM132991270

Assignment

Question 1. We have the schema R = (A, B, C, D, E) with the following set F of functional dependencies.

F = { A → B, C → D, AC → E }

1) What is (or are) a candidate key (or candidate keys)? (Explain how to compute it or them)

2) R is in 2NF? (Yes or No), and Why?

3) If R is not in 2NF, give a lossless-join decomposition into 2NF of schema R. (Show details).

Question 2. We have the schema R = (A, B, C, D, E) with the following set F of functional dependencies.

F = { CD → E, BD → AC, C → A }

1) What is (or are) a candidate key (or candidate keys)? (Explain how to compute it or them)

2) R is in BCNF? (Yes or No), and Why?

3) If R is not in BCNF, give a lossless-join decomposition into BCNF of schema R. (Show details).

Question 3. Consider the following set F of functional dependencies on the relation schema r = (A, B, C, D, E):

F = { B → CD, C → AE, E→B }

1) What is (or are) a candidate key (or candidate keys)? (Explain how to compute it or them).

2) r is in 3NF? (Yes or No), and Why?

3) If r is not in 3NF, give a lossless-join decomposition into 3NF of schema R. (Show details).

Question 4. Consider the relation:
Let us consider a relation TEACHERS below. This relation stores information regarding different teachers in a School working in different departments hence teaching different subjects. This relation has three attributes. The first attribute stores the information regarding Department in which the teacher works. The second attribute stores the names of the teachers. And the third attribute stores the subjects the teachers teach. You need to answer the following questions based on the only values in the table below.

1) Based on a common-sense understanding of the above data, what are the possible candidate keys of this relation? You need to consider 4NF definition.

2) Is there any functional dependency in the above?

3) What are the multivalued dependencies in the above?

4) What will the final decomposition look like? You need to use 4NF decomposition algorithm.

Note: Need only Question 2, 3, 4.