Design of the Final Project
The Movie database for the final project will have the four tables shown above. Begin with MovieRating, since this is the simplest table in the database. It is a lookup table of all the MPAA Rating Codes (plus NR for Not Rated) and it has two fields: RatingID and Rating. Once created it should be populated with the following records:
MovieRating
|
RatingID
|
Rating
|
|
0
|
NR
|
|
1
|
G
|
|
2
|
PG
|
|
3
|
PG-13
|
|
4
|
R
|
|
5
|
NC-17
|
Some of the characteristics you associate with movies are in the table, Movie. They represent most of the data types associated with a relational database. The MovieID is a unique number that identifies a single movie, Title is a text field containing the movie title, YearReleased is the year the movie was first released, RatingID is a foreign key associated with the Rating in the MovieRating table, and the remaining fields are Boolean variables (Yes/No) to identify movies that are in black and white, and foreign films with subtitles. Once created it should be populated with the following records:
Movie
|
MovieID
|
Title
|
YearReleased
|
RatingID
|
BW
|
Subtitles
|
|
1
|
Quiet Man, The
|
1952
|
1
|
|
|
|
2
|
McLintock!
|
1963
|
3
|
|
|
|
3
|
Thin Man, The
|
1934
|
0
|
TRUE
|
|
|
4
|
Tampopo
|
1985
|
4
|
|
TRUE
|
An important part of any movie database is the cast. The table Cast contains information about the actors from the movies listed. Beside the name of the actor (LastName and FirstName) we will include the Date of Birth (DOB) and Date of Death (DOD). Once created it should be populated with the following records:
Cast
|
CastID
|
LastName
|
FirstName
|
DOB
|
DOD
|
|
1
|
Wayne
|
John
|
5/26/1907
|
6/11/1979
|
|
2
|
O'Hara
|
Maureen
|
8/17/1920
|
|
|
3
|
Powell
|
William
|
7/29/1892
|
3/5/1984
|
|
4
|
Loy
|
Myrna
|
8/2/1905
|
12/14/1993
|
|
5
|
Yamazaki
|
Tsutomu
|
12/2/1936
|
|
|
6
|
Miyamoto
|
Nobuko
|
3/27/1945
|
|
|
7
|
Watanabe
|
Ken
|
10/21/1959
|
|
Finally, since there are usually many actors in a single movie, and a single actor is usually in many movies, we need to construct an intersection table named MovieCast that relates the actors with the movies they were in. We will also include the character name of the actor in that movie. Once created it should be populated with the following records:
MovieCast
|
MovieID
|
CastID
|
Persona
|
|
1
|
1
|
Sean Thornton
|
|
1
|
2
|
Mary Kate Danaher
|
|
2
|
1
|
George Washington McLintock
|
|
2
|
2
|
Katherine Gilhooley McLintock
|
|
3
|
3
|
Nick Charles
|
|
3
|
4
|
Nora Charles
|
|
4
|
5
|
Goro
|
|
4
|
6
|
Tampopo
|
|
4
|
7
|
Gun
|
Feel free to add your own movies and actors to these tables; just remember to include entries in MovieCast.