Modify and enhance a Database Project. 

Movies Table:

Column Name	Data Type	Constraints
movie_id	INT	PRIMARY KEY
title	VARCHAR	NOT NULL
release_year	INT	NOT NULL
runtime	INT	NOT NULL
rating	VARCHAR	NOT NULL

 

Actors Table:

Column Name	Data Type	Constraints
actor_id	INT	PRIMARY KEY
name	VARCHAR	NOT NULL
birthdate	DATE	NOT NULL

 

Directors Table:

Column Name	Data Type	Constraints
director_id	INT	PRIMARY KEY
name	VARCHAR	NOT NULL
birthdate	DATE	NOT NULL

 

Producers Table:

Column Name	Data Type	Constraints
producer_id	INT	PRIMARY KEY
name	VARCHAR	NOT NULL
company	VARCHAR	NOT NULL

 

Characters Table:

Column Name	Data Type	Constraints
character_id	INT	PRIMARY KEY
name	VARCHAR	NOT NULL
actor_id	INT	FOREIGN KEY REFERENCES Actors(actor_id)

 

Genres Table:

Column Name	Data Type	Constraints
genre_id	INT	PRIMARY KEY
name	VARCHAR	NOT NULL

 

Reviews Table:

Column Name	Data Type	Constraints
review_id	INT	PRIMARY KEY
reviewer	VARCHAR	NOT NULL
rating	INT	NOT NULL
movie_id	INT	FOREIGN KEY REFERENCES Movies(movie_id)

 

Songs Table:

Column Name	Data Type	Constraints
song_id	INT	PRIMARY KEY
title	VARCHAR	NOT NULL
artist	VARCHAR	NOT NULL

 

MovieActors Linking Table:

Column Name	Data Type	Constraints
movie_id	INT	FOREIGN KEY REFERENCES Movies(movie_id)
actor_id	INT	FOREIGN KEY REFERENCES Actors(actor_id)

 

MovieDirectors Linking Table:

Column Name	Data Type	Constraints
movie_id	INT	FOREIGN KEY REFERENCES Movies(movie_id)
director_id	INT	FOREIGN KEY REFERENCES Directors(director_id)

 

MovieProducers Linking Table:

Column Name	Data Type	Constraints
movie_id	INT	FOREIGN KEY REFERENCES Movies(movie_id)
producer_id	INT	FOREIGN KEY REFERENCES Producers(producer_id)

 

MovieGenres Linking Table:

Column Name	Data Type	Constraints
movie_id	INT	FOREIGN KEY REFERENCES Movies(movie_id)
genre_id	INT	FOREIGN KEY REFERENCES Genres(genre_id)

 

MovieCharacters Linking Table:

Column Name	Data Type	Constraints
movie_id	INT	FOREIGN KEY REFERENCES Movies(movie_id)
character_id	INT	FOREIGN KEY REFERENCES Characters(character_id

 

1. Database must be able to track the following information about movies: Title, release date, runtime, rating (PG etc...), cast and crew (actors, directors etc...) characters, songs and reviews. 

2. The database design must conform to correct relational database design as outlined in Chapter 10, and must be normalized to the 3rd normal form. 

3. Provide a detailed EER diagram created using the design tool in MySQL Workbench as demonstrated in class and shown in Chapter 10 

4. Apply appropriate constraints in your table definitions. This includes, column constraints such as "not null", primary key constraints, and foreign key constraints as described in chapter 11. 

5. Create the database once designed using Workbench (use the "forward engineer" option from the EER diagram). 

6. Once the Movie DB is created, you must populate all tables with data for at least two movies. The cast and crew details do not have to be complete (i.e., you don't have to list every person involved in the movie), but you need data for all tables 

7. Test your DB design by writing the SQL to find all the data for a particular movie. This means you would retrieve the data required to list the specifics of the movie (release date, runtime etc...) the cast and crew, the characters, songs and reviews. It is ok to have multiple SELECT statements for this, but use joins where appropriate. Once you have working SELECT statements, save them in an external Notepad++ file