Lab Assignment
Create both logical and physical database models following the requirements below. show your model and table design using screenshots or text. Then, implement your database.
The database for this project can be created on one of the major cloud platforms (AWS/Azure/Google Cloud) using a free student account. Otherwise, you may utilize any database tool available such as OpenOffice Base, MySQL, or Microsoft Access. Consult the Lab Activities section of the Syllabus for additional details.
Requirements: This database will support an application that tracks movies, as well as the performers and production crew for those movies (think IMDB). For each movie, the title, year of release, genre, and parental advisory rating should be stored. For each performer/crew member, first and last name should be stored, as well as date of birth and hometown.
Each performer/crew member can be related to any number of movies. The database should also track the role the individual played in the production of the movieproducer, director, screenwriter, actor, and so forth. For acting roles, the relationship should also track the name of the character portrayed. The database must allow for the same individual to perform multiple roles in the same movie.
Additionally, the database should track reviews of movies. Each review should involve a reviewer and a rating of 1 through 5 stars.
Insert at least 5 movies, 50 cast/crew, and 100 reviews into the database. [CO1]
Write a query to extract the 5 most recent movies. [CO1]
Write a query to extract the movie with the highest average rating. [CO1]
Write a stored procedure that takes two actors and returns all movies that they have appeared in together. [CO1]
Write a CHECK constraint that requires that an actor cannot be associated with a movie released before their date of birth. [CO4]
Write a trigger to automatically generate a 5-star review by an anonymous user when a movie is inserted. [CO4]
Write a transaction that creates a duplicate of a movie and all associated actors/crew (for all the sequels being written!) [CO7]
Describe a set of recommended indexes for this database. Which columns and tables should be included? Assume that read operations happen far more frequently than write operations, and that database space is unlimited. [CO6]
Write a brief paper (no more than 2 pages/550 words) discussing your table design. Be sure to take screenshots of all of the above queries and outputs, and insert into this document. Please use a format where the database file is available (Microsoft Access, OpenOffice Base, etc.).
Note: Submission requirements for this lab assignment include the database file, screenshots, and a brief paper.
