Tuesday, 12 April 2016

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.

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












No comments:

Post a Comment

SQL exercises for beginners one

SQL Exercises for Beginners / Simple SQL Exercises with Answers / Solved SQL exercises / SQL solved exercises with simple conditions / So...