Advanced Database Management System - Tutorials and Notes: Is the given table in second normal form 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.

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

## Featured Content

### Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...