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.

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

Go back to Normalization – solved exercises page.