Showing posts with label Solved Exercises. Show all posts
Showing posts with label Solved Exercises. Show all posts

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)



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










Is the given table in second normal form 2nf?

Is the given table in Second Normal Form (2NF)? Normalization solved exercise, second normal form example, normalize the given table to 2nf.


Normalization Solved Exercise


Consider a relation Movies_Screened with attributes Theatre_name, Movie, Day, Time, and Age_restriction. A sample table is given below;

Theatre_name
Movie
Day
Time
Age_restriction
Abirami
Gethu
Wed
10:00
15
Abirami
Gethu
Wed
15:00
15
Palazzo
Theri
Fri
10:00
15
Palazzo
Enthiran
Fri
10:00
12
Sathyam
Enthiran
Thu
10:00
12

A theatre can screen more than one movie on the same day and time but on different screens. Age_restriction provides the minimum age limit to watch that particular movie as instructed by the central board of film certification. Does this relation satisfy 2NF conditions? Or, is Movies_Screened in 2NF?

Solution:
The properties to be satisfied for 2NF relations are;

  • Table should be in 1NF
  • All non-key attributes must fully functionally dependent on the whole key (primary key).

Is Movies_Screened in 1NF?
YES. All the attributes contain only atomic values. Hence, Movies_Screened is in 1NF.

Is Movies_Screened in 2NF?
To check this, first we need to find the key for Movies_Screened. To find the key we need the possible functional dependencies that are held in the given table.

No attributes uniquely identify any other attributes in the given table.

For example,


  • Theatre_name Movie – Not hold, because it is given that a theatre may show more than one movies on different screens.
  • Theatre_name Day – Not hold, because it is given that a theatre may show more than one shows on the same day.
And so on. [Readers are requested to work on other attributes/attribute sets like this.]

  • Movie Age_restriction – Holds, because the value of Age_restriction can be uniquely identified by the value of Movie.
  • Theatre_name, Movie, Day, Time Age_restriction – Holds, because all these attributes Theatre_name, Movie, Day, and Time as a whole (together) uniquely determines the value of Age_restriction. Hence, this is the valid functional dependency.

From the above functional dependency, it is very clear that (Theatre_name, Movie, Day, Time) is the key for Movies_Screened table. It is a composite primary key.

For a table to be in 2NF, all non-key attributes must fully functionally dependent on the whole key. In our table, Age_restriction is the only non-key attribute. Also, it can be identified by the whole key and by Movie attribute. That means, the functional dependency
Movie Age_restriction,
shows partial dependency.
Hence, the table Movies_Screened is not in 2NF.

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












Wikipedia

Search results

Followers