SCENARIO: The GMG Movie Database Implementation SCENARIO: The GMG Movie Database Design Your database development team has been commissioned to design a set of database tables that will be used to track Information for GMG, a movie production studio. The database should track the following information: The names of the movies. The year which a movie was produced. The rating for the movie. (e.g. G, PG, PG-13,r, etc.) The first and last names of the producer for each movie. (assume that there is only one producer per movie) The first and last names of each actor in each movie. Keep track of the main and supporting actors in a movie. The amount of money each actor was paid for making the movie. . The names and addresses of the theatres where each movie was shown. (there can be many theatres, possibly thousands, where each movie was shown) . The number of tickets sold for each movie at each theatre. The price per ticket at each theatre - for the purpose of this assignment you should assume that a theatre charges the same amount of money for every ticket that it sells. There are many different database designs that can be used to store this information. Whatever design you choose, make sure that it is in 3- Normal Form. Create the appropriate amount of tables and the optimum number of fields in those tables. Feel free to create extra fields to hold primary key values if you wish. TASK REQUIREMENTS: 1. Generate the SQL statements to create tables defined by the ER diagram and data dictionary you created in assignment 1. Include any possible constraints that will affect each table, the storage parameters for each table, the relationships between tables. Note: pay attention to the order in which the tables will need to be created, ensuring referential integrity throughout the database. (60 marks) 2. For each of the table you created in task 1, generate an SQL statement to insert the necessary amount of records containing appropriate data of your choosing for the table. Note: pay attention to the order in which the records will need to be inserted, ensuring referential integrity throughout the database. (24 marks) 3. Generate SQL statements to retrieve data for the following tasks: a. List all the movies currently showing using the following output format example: You can watch Mission Impossible: Fallout, rated PG13, starring Tom Cruise at GSC Gurney Plaza b. Give the name of movies that have sold less tickets than the average number of tickets sold. C. List the Top 10 highest paid actors in the database. d. List the Top 10 movies of all times by ticket receipts. Note: pay attention to the amount of records needed to generate the necessary output for each task
