Answer Happy

customer_id shipment id movie id movie id last_name customer_id media type movie_title first_name movie_id cost_price director_first_name media_type retail price director_last_name address shipment_date current_stock genre city release_date state studio_name postcode Figure 1. The database schema for MovieDirect. The SQL definitions for each of the tables will have the following fields: Customers • customer_id - an integer and primary key for the customers table. • last_name - a character field that allows for up to 50 characters. This field should always have a value. • first_name - a character field that allows for up to 50 characters. This field should always have a value. https://turing.une.edu.au/-cosc210/assi ... signment=4 1/4 13/04/2022, 11:24 COSC210 Assignment address - a character field that allows for up to 200 characters. • city - a character field that allows for up to 50 characters. state - character field that allows for up to 3 characters. This field should always contain a value from the following list: NSW, VIC, QLD, ACT, TAS, NT, SA, WA. • postcode - a character field that allows for up to 8 characters. Movies • movie_id- an integer and primary key for the "Movies" table. • movie_title - a character field that allows for up to 100 characters. This field should always have a value. • director_last_name - a character field that allows for up to 50 characters. This field should always have a value. director_first_name - a character field that allows for up to 50 characters. This field should always have a value. • genre - a character field that allows for up to 20 characters that should always contain a value from the following list of genres: Action, Adventure, Comedy, Romance, Science Fiction, Documentary, Drama, Horror. release_date - a date field. • studio_name - a character field that allows for up to 50 characters. Stock • movie_id - an integer and partial primary key from the "Movies" table (composite primary key and foriegn key). • media_type - a character field of up to 20 characters that should always contain a value from the following list of mediums: DVD, Blu-Ray and Stream-Media and is part of a composite primary key. • cost_price- A real field that should always have a positive value. • retail_price- A real field that should always have a positive value. • current_stock - A real field that should have a value of zero or more. Shipments • shipment_id- an integer and primary key for the shipments table. • customer_id - an integer that should always have a value that references the 'customer_id" field in the "Customers" table. movie_id - an integer that should always have a value that references the movie_id' field of the "Stock" table and is a composite foreign key with 'media_type". • media_type - a character field of up to 20 characters that references 'media_type' from the "Stock" table and is a composite foreign key with 'movie_id'. • shipment_date-a date-type field.
Question Set Construct an SQL script (i.e. a file with the .sql extension) that contains the definitions of the following views. 1. Create a view called 'movie_summary' which returns the movie_title, release_date, media_type and retail_price for all movies in the database. This will contain some duplicates for media_type. (5 Mark) 2. Create a view called 'old_shipments that lists the customer first_name and last_name, movie_id, shipment_id and shipment_date for every shipment before 2010. (5 Mark) 3. Create a view called 'trilogy' that Returns a list of the titles of all movies with the words 'Rings' or 'Wars' in the title. (5 Mark) 4. Create a view called 'retail_price_hike' that returns the movie_id, retail_price and a final column that contains the retail price increased by 25%. (5 Mark) 5. Create a view called 'value_summary' that returns the total cost value (cost*stock) and total retail value (retail*stock) across all stock. (5 Mark) • Note: this question includes infinite values for Stream-media, this is not a requirement, but it may be good to remove stream- media from the results. 6. Create a view called 'profits_from_movie' that returns the movie_id and movie_title for each movie along with the difference between the sum of the cost and retail values across all shipments for each movie. The results should be grouped by movie_title. (5 Mark) 7. Create a view called 'followers_of_melkor that returns the first name, last name of any customer who has not purchased any media_type of the movie named 'The Lord of the Rings: The Fellowship of the Ring'. (10 Marks) 8. Create a view called 'sole_angry_watcher' that returns the first_name and last_name of any customer (If one exists) who is the only customer to buy '12 Angry Men' (Note the customer may buy other movies as well, but if anyone else buys the movie, no records should be returned). (10 Marks) Make sure to test your scripts on turing.une.edu.au and utilise the template and import script provided.