A table contains columns A B C D E F G A B is the primary k

A table contains columns A, B, C, D, E, F, G. A + B is the primary key. The following dependencies are identified: C rightarrow B A rightarrow D, E E rightarrow G Normalize this table to 2NF; underline the primary key of each table. Then normalize to 3NF; underline the primary key of each table. Then normalize to BCNF;; underline the primary key of each table.

Solution

1). 2NF

A relation/table which is in 2NF must not have any non-primary key column that can be determined by partial PK. i.e. we need to look for columns that depend on either A or B, but not both. Looking at the dependencies, we find D,E are such columns, hence we need to split table in following way:

R(A,D,E,G), A is PK. [you may think that G can be in 2nd table, but we want to keep E and G together, so in the next pass, i.e. 3NF we can take care of this dependency]

R(A,B,C,F), (A,B) is PK.

2) 3NF

In 3NF relations, there are no transitive dependencies and the tables are already in 2NF. Now we look into our tables to find transitive dependencies, i.e. X->Y->Z type of dependencies, we find that G is dependent on E, and E is dependent on A,

so our tables are:

R(A,D,E), A is PK.

R(E,G), E is PK.

R(A,B,C,F), (A,B) is PK.

3) BCNF

in this form, in every function dependency X->Y, X must be superkey, i.e. guarantee uniqueness of a row in the table, if we look at the tables from previous answer, we find C->B is a dependency in which C is not a superkey, hence final tables are:

R(A,D,E), A is PK.

R(E,G), E is PK.

R(A,B,F), A,B is PK.

R(B,C), C is PK.

Note: this problem can be solved in more way, since C->B, therefore we could have made (A,C) the primary key and then solve the problem to get alternative answer.