Lab: Actors and Movie Functional Dependencies¶
This lab takes you through the process of creating an intersection table to handle a Multivalued Dependency.
You must have completed last week’s lab Referential Integrity.
Step 1: Add Movies¶
Create a table to store movies. The table should have the following definition:
movie ( title, year )
Add the following information to your movie table (it’s the movies from the Oscar table:
The Godfather: Part II, 1975
Kramer vs. Kramer: 1980
Raging Bull: 1981
Sophie’s Choice: 1983
The Iron Lady: 2012
Darkest Hour: 2018
Now add another actor to your table:
Al Pacino, April 25 1940, New York, NY
Step 2: Create an Intersection Table Between Actor and Move¶
Intersection tables sometimes have funny names like
actor_has_movie because they form a bridge between two tables when there’s an MVD. In this case we have a better name for our intersection table. We will call it
starring and the definition should look like this:
starring ( ActorID, Movie )
Notice! Both attributes are primary keys and foreign keys.
Intersection tables will always have at least two foreign keys becasue they join two tables together.
Step 3: Populate the Intersection Table¶
Populate the intersection table with the movies that actors have appeared in:
Meryl Streep - Kramer vs. Kramer and The Iron Lady
Robert DeNiro - The Godfather: Part II and Raging Bull
Gary Oldman - The Darkest Hour
Al Pacino - The Godfather: Part II
Step 4: Replace Columns in the Oscar Table¶
The oscar table now refers to data that’s in the
movie table. Place a foreign key constraint in the
oscar table that ensures an oscar is given to a movie that’s in the
Step 5: Run Some Queries¶
You now have an intersection table that satisfies the MVDs:
ActorName → → MovieTitle MovieTitle → → ActorName
You can query actors and movies with the following SQL.
select * from actor, starring, movie where actor.id = starring.ActorID AND starring.Movie = movie.title;
Export your schema to a file called