Decomposing a relation into 3NF/BCNF

normalizationrelational-theory

I am having trouble decomposing a relation to 3NF/BCNF.

I have determined that for the relation:

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

where:

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

…the primary (only) key is A,D,E.

I have determined that the relation is not in 2NF, and therefore also not in 3NF and BCNF, so I must start by decomposing it into 2NF.

I have worked through the steps and come up with the following decomposition:

+----------+-----+------------+
| Relation | Key | Attributes |
+----------+-----+------------+
| R1A      | A   | G,B,C      |
| R1B      | G   | H          |
| R2       | D   | I          |
| R3       | E   | F          |
+----------+-----+------------+

Is this the correct decomposition for 3NF/BCNF?

Best Answer

No, your decomposition is noteven in 2nd normal form.

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

A, D and E cannot be found on the right side of a dependency, so they are members of every key. {A, D, E} generate all attributes, so it is a superkey and therefore the only minimal key But your decomposition is wrong AA is not a key of R1A, because G does not depend on A. So the key of R1A is {A,G}. So R1A is not in 2nd Normal Form because B depends on {A} which is a proper subset of the candidate key. So R should be decomposed to {A,B,C}, {E,F}, {D,I}, {A,D,G}, {G,H}. These relations are in 2nd, 3rd and even in Boyce-Codd normal form.