Modify and enhance a Database Project. Movies Table: Column Name Data Type Constraints movie_id INT PRIMARY KEY
Question:
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