Question 1 (Normalization) [20 points]

Consider the following table which is used to store records of students and course information.

(a)  [5 points] Explain if it is the first normal form. If not, explain the reason and normalize the table into

the first normal form (1NF).

(b)  [5 points] Explain the criteria for the second normal form (2NF) informally and normalize the table you obtained from the previous part to meet them. Then explain which anomalies (at least two) still can occur in your answer.

(c)  [5 points] Explain the criteria for the third normal form (3NF) informally and normalize the table you obtained for the previous question to meet them.

(d)  [5 points] Explain if the tables you obtained for the previous question are in BCNF and, if not, nor- malize them to BCNF.

Question 2 (Normal Forms) [20 points]

(a)  [5 points] Let R(A,B, C,D, E , F, G) be a relation schema, and let S = {AB → C,B → EF, CF → G} be a set of functional dependencies. Determine if the relation R is in 2NF. Explain your answer. If R is not in 2NF, normalize it to 2NF.

(b)  [5 points] Explain the conditions of 3NF. Are the relation schemas from the answer of the previous

question in 3NF? Explain your answer.

(c)  [5 points] Let R(A,B, C,D, E) be a relation schema, and let F = {AB → CDE, CD → ABE, E → D} be a set of functional dependencies. Determine the highest normal form of R. (Check from 2NF to BCNF)

(d)  [5 points] Normalize the relation schema R of the previous question to the next higher normal form.

Question 3 (Lossless Join Decomposition & Dependency Preservation)                     [20 points]

(a)  [10 points] Let R(A,B, C,D, E , F) be a relation schema, and let S = {A → B,BC → D, E → C,D → A,B → D} be a set of functional dependencies.  Use the Chase test to determine if the following decomposition is lossless.

Decomposed relation schemas: R1(ABD), R2(DF), R3(EF), R4(BCEF)

(b)  [5 points] Let R(A,B, C,D, E , F) be a relation schema, and let S = {A → B,BC → D, E → C,D → A,B → D} be a set of functional dependencies. Use the Nonadditive Join Testfor Binary Decomposi-

tion to determine if the following decomposition is lossless.

Decomposed relation schemas: R1(ACDE),R2(BDF)

(c)  [5 points] Let R(A,B, C,D, E , F) be a relation schema, and let S = {A → B,BC → D, E → C,D → A,B → D} be a set of functional dependencies. Use one of the algorithms introduced in the lecture to

determine if the following decomposition is dependency preserving.

Decomposed relation schemas: R1(ABCF),R2(ABDE)

Question 4 (Normalization) [25 points]

(a)  [13 points] Let R(A,B, C,D, E) be a relation schema, and let F = {A → BC, CD → AE,ABD → CD, CE → AD} be a set of functional dependencies. Decompose R into 3NF by using the 3NF syn- thesis algorithm. Show all steps and argue precisely. Is this decomposition also in BCNF? If so, why? If not, explain the reasons and decompose into BCNF.

(b)  [12 points] Let R(A,B, C,D, E) be a relation schema, and let S = {AB → CD,D → E,A → C,B → D} be a set of functional dependencies.  Decompose R into BCNF by using the BCNF decomposition algorithm introduced in the lecture. Show all steps and argue precisely.