Question

DATABASE NORMALIZATION

Answer the following questions for this relational schema and functional dependencies:

R (A, B, C, D, E, F, G, H, I)
A -> C ; C -> D ; A,C -> D
B -> E,F ; A,B -> G ; G -> H,I ; A,G -> I

1. List all candidate keys of R. (2 marks)
2. Does the functional dependency A,C -> I hold? (1 mark)
3. Does the functional dependency B -> F hold? (1 mark)
4. Normalize R into BCNF. (6 marks)

Answer 1

R (A, B, C, D, E, F, G, H, I)
A -> C ; C -> D ; A,C -> D
B -> E,F ; A,B -> G ; G -> H,I ; A,G -> I

a) List all candidate keys of R.

A, B -> A, B, C, D, E, F, G, H, I

b) Does the functional dependency A,C -> I hold?

A, C -> A, C, D

A, C can determine only A,C,D

=> A, C -> I does not hold

c) Does the functional dependency B -> F hold?

B -> E, F

=> B -> E, B -> F => holds

d) Normalize R into BCNF.

R (A, B, C, D, E, F, G, H, I)

1. A -> C ;
2. C -> D ;
3. A,C -> D
4. B -> E,F
5. A,B -> G
6. G -> H,I
7. A,G -> I

The key of the above relation is AB. But B individually determines E, F

R => R1 and R2 by (4)

R1 = {A, B, C, D, G, H, I} key ={A, B}

R2 = {B, E, F} key = {B}

R1 => R3 and R4 by (1) and (2)

R3 = {A, B, G, H, I} key = {A, B}

R4 = {A, C, D} key = {A}

R3 => R5 and R6 by (6)

R5 = {A, B, G} key = {A, B}

R6 = {G, H, I} key = {G}

after normalization

R2 = {B, E, F} key = {B}

R4 = {A, C, D} key = {A}

R5 = {A, B, G} key = {A, B}

R6 = {G, H, I} key = {G}