Normalize the table into 3NF

Normalize the given table into third normal form, 3nf example, 3nf solved exercise, How to normalize a relational schema into 3NF? Third Normal Form Normalization Process, Normalize to 3nf

Normalization Exercises

Consider the relation PLAYER with relational schema PLAYER (Player-no, Player-name, Team, Team-color, Coach-no, Coach-name, Player-position, Team-captain) and set of functional dependencies as follows;

F = {Player-no → Player-name, Player-no → Player-position, Player-no → Team, Coach-no → Coach-name, Team → Team-color, Team → Coach-no, Team → Team-captain}

Answer the questions given below;

a) Is PLAYER in 2NF? If not, convert into 2NF.

b) Is PLAYER in 3NF? If not, convert into 3NF.

a) Is PLAYER in 2NF?

To answer this question, we need to find the key for PLAYER. (Refer here - How to find key?)

Let us find the closure for all the left hand side attributes of all the FDs of F.

(Player-no)⁺ = Player-no, Player-name, Player-position, Team, Team-color, Coach-no, Team-captain, Coach-name.

(Team)⁺ = Team, Team-color, Team-captain, Coach-no, Coach-name

(Coach-no)⁺ = Coach-no, Coach-name

When we find closure, only Player-no can uniquely determine all the attributes of PLAYER. Hence, Player-no is the only candidate key.

Question to think: (Team, Player-no)+ = Player-no, Player-name, Player-position, Team, Team-color, Coach-no, Team-captain, Coach-name. (Team, Player-no) combination, as given above can uniquely determine all the attributes of PLAYER. Hence, (Team, Player-no) can be a candidate-key? NO. Reason - The proper subset of (Team, Player-no) is already a candidate key (Player-no).

As the key (Player-no) is single and simple attribute, there is no possibilities for partial-key dependencies. Hence, PLAYER is in 2NF.

b) Is PLAYER is in 3NF?

To answer this question, we need to check for non-key dependencies or transitive dependencies. That is, we have to look for dependencies like the one follows;

Non-key attribute(s) → Non-key attribute(s)

From the given set of functional dependencies F, we could derive the following non-key dependencies;

Team → Team, Team-color, Team-captain, Coach-no, Coach-name

Coach-no → Coach-no, Coach-name

Hence, PLAYER is not in 3NF.

Solution:

Decompose PLAYER into more tables based on the non-key dependencies. Then we shall get the tables as follows;

PLAYER (Player-no, Player-name, Player-position, Team)

TEAM (Team, Team-color, Team-captain, Coach-no, Coach-name)

The key for PLAYER is Player-no, and all the others are non-key attributes. Hence, PLAYER is in 2NF (no partial dependencies) and 3NF (no transitive dependencies).

The key for TEAM is Team. All the other attributes are non-key attributes and depends on Team-no. Hence, TEAM is in 2NF. TEAM has following transitive dependency;

Team → Coach-no → Coach-name.

Hence, TEAM is not in 3NF. To convert, decompose TEAM as follows;

TEAM (Team, Team-color, Team-captain, Coach-no)

COACH (Coach-no, Coach-name)

Now, TEAM and COACH are both in 2NF and 3NF.

Final set of decomposed tables that are in 3NF are;

PLAYER (Player-no, Player-name, Player-position, Team)

TEAM (Team, Team-color, Team-captain, Coach-no)

COACH (Coach-no, Coach-name)

********************

Go back to Normalization – solved exercises page.