Suppose we have a MovieStar relation that describes the information on movies an
ID: 3559946 • Letter: S
Question
Suppose we have a MovieStar relation that describes the information on movies and stars in a movies database. We assume that a movie star gets different pay for different movies. Thus, the pay attribute is determined by both MovieTitle and StarName. All the functional dependencies are shown in Figure 1. The attribute MLength stands for movie length. Using the knowledge about normal forms, decompose the MovieStar table into a set of smaller tables so there are no anomalies. You need to indicate which normalization you are using, and to indicate the primary keys in the resulting tables.
Explanation / Answer
The given functional dependencies are as follows:
Functional dependency1:
{ MovieTitle , StarName } -->pay
Functional dependency2:
MovieTitle --> Mlength
Functional dependency3:
StarName --> {birthday, gender}
First Normal form (1NF):
A relation is said to be in 1NF if it contains no repeating values.
An example for the given Relation:
MovieTitle
StarName
Pay
Mlength
Birthday
Gender
Don
Sharuk
10000
2:35Min
01/05/1988
Male
Dhoom3
Amir
12000
3:35Min
25/04/1984
Male
Dhoom3
Katrina
8000
3:35Min
19/02/1989
Female
Don
Kajal
9000
2:35Min
25/01/1990
Female
3idiots
Amir
15000
2:23Min
25/04/1984
Male
3idiots
Kajal
11000
2:23Min
25/01/1990
Female
From the above table the rows 2 and 5, 4 and 6 contain the repeating values of Birthday and Gender.
Hence the database is not in First Normal form.
The repeating group is {Birthday, Gender}
So, we have to remove the repeating group by decomposing the above database into two tables basing on the 3rd functional dependency.
The resulting tables are as follows:
Movie
MovieTitle
StarName
Pay
Mlength
Don
Sharuk
10000
2:35Min
Dhoom3
Amir
12000
3:35Min
Dhoom3
Katrina
8000
3:35Min
Don
Kajal
9000
2:35Min
3idiots
Amir
15000
2:23Min
3idiots
Kajal
11000
2:23Min
Star
StarName
Birthday
Gender
Sharuk
01/05/1988
Male
Amir
25/04/1984
Male
Katrina
19/02/1989
Female
Kajal
25/01/1990
Female
Now the two tables Movie and Star are in 1NF.
But the Movie table is not second normal form.
Second Normal Form:
A relation that is in first normal form and every non-prime attribute is fully functional dependent on the primary key.
i.e., { MovieTitle, StarName} --> pay
{ MovieTitle, StarName} --> Mlength
But, the attribute Mlength (non-prime attribute) is not fully functional dependent on the key
( { MovieTitle, StarName}).
Mlength is functional dependent on MovieTitle only (Functional dependency 3).
Hence, we have to convert the Movie relation into 2nd Normal form.
Decompose the Movie relation as follows:
Movie
MovieTitle
Star Name
Pay
Don
Sharuk
10000
Dhoom3
Amir
12000
Dhoom3
Katrina
8000
Don
Kajal
9000
3idiots
Amir
15000
3idiots
Kajal
11000
Movie_Length
MovieTitle
Mlength
Don
2:35Min
Dhoom3
3:35Min
Dhoom3
3:35Min
Don
2:35Min
3idiots
2:23Min
3idiots
2:23Min
---------------------------------------------------------------------------------------------------------------------------------------
After 1NF and 2NF the tables are in Normal form. The resultant tables are:
Movie
MovieTitle
Star Name
Pay
Don
Sharuk
10000
Dhoom3
Amir
12000
Dhoom3
Katrina
8000
Don
Kajal
9000
3idiots
Amir
15000
3idiots
Kajal
11000
Movie_Length
MovieTitle
Mlength
Dhoom3
3:35Min
Don
2:35Min
3idiots
2:23Min
Star
Star Name
Birthday
Gender
Sharuk
01/05/1988
Male
Amir
25/04/1984
Male
Katrina
19/02/1989
Female
Kajal
25/01/1990
Female
MovieTitle
StarName
Pay
Mlength
Birthday
Gender
Don
Sharuk
10000
2:35Min
01/05/1988
Male
Dhoom3
Amir
12000
3:35Min
25/04/1984
Male
Dhoom3
Katrina
8000
3:35Min
19/02/1989
Female
Don
Kajal
9000
2:35Min
25/01/1990
Female
3idiots
Amir
15000
2:23Min
25/04/1984
Male
3idiots
Kajal
11000
2:23Min
25/01/1990
Female