Question

Section D – BCNF Decomposition

For each question in this section, you are required to decompose the given relation into BCNF form
and state any new relations created in the process with their functional dependencies and identify any
functional dependencies which are lost during the decomposition. You must show your working using
the tree method presented in tutorials. Consider the functional dependencies in the order presented
in the question.

Question 1
R [A, B, C, D, E, F, G, H, I, J]
{A} -> {B, C}
{B} -> {D, E, F}
{C} -> {G, H, I}
{H, I} -> {F, J}

Question 2
R [A, B, C, D, E, F, G, H]
{A, B, C} -> {D, E, F, G}
{G, H} -> {A, B, C}
{C} -> {H}

Section E – 3NF Decomposition
Question 1
Based on the following relational schema and functional dependencies, find minimal cover for
relation R.
R [A, B, C, D, E, F, G, H, I, J, K, L, M, N]
{A} -> {C, D, F, G}
{B} -> {E}
{A, G} -> {J, C}
{D, E, B} -> {H, I, J}
{J} -> {K, L, M}
{M} -> {N}

Question 2
The minimal cover has been provided below for a given relation with a set of functional
dependencies. Using the minimal cover, normalise the relation to 3NF such that all functional
dependencies are preserved.

R [A, B, C, D, E, F, G, H]
{A} -> {D, F}
{B} -> {G, E}
{F, G} -> {H}
Minimal Cover: {
{A} -> {D}, {B} -> {G}, {B} -> {E}, {A} -> {F}, {F, G} -> {H}
}

Answer 1

BCNF- Boyce-Codd Normal Form or BCNF is an extension to the third normal form, and is also known as 3.5 Normal Form. For a table to satisfy the Boyce-Codd Normal Form, it should satisfy the following two conditions:

1. It should be in the Third Normal Form.
2. And, for any dependency A → B, A should be a super key.

3-NF- A relation is in 3NF if at least one of the following condition holds in every non-trivial function dependency

X –> Y:

1. X is a super key.
2. Y is a prime attribute (each element of Y is part of some candidate key).

Now coming to the answer-