Implement some basic to intermediate SQL queries for the database schema. Create database views. In this assignment you will be constructing an SQL script that builds a series of database views from the DBMS you created in Practical Assignment 1. You have been tasked by the owners of MovieDirect, a small retailer and online streaming platform, to re- develop their orders and shipments database system. Currently, the system uses four separate spreadsheets to keep track of customers, movies, stock that is currently available and shipments out to the customers. This system is starting to become difficult to use and will prevent MovieDirect from effectively managing its long- term operations in its current state. Your task was to develop a robust and scalable database solution for the information system that will effectively store Movie Direct's information and provide the capabilities for extracting information to improve sales and management of customers. You should have completed this as part of assginment 1, your next task is to create a set of user views to extract meaningful information from the live database. For this assignment, transaction data and a user view template will be provided, make sure to utilise these to check your solution. Your first task was to develop a database using the PostgreSQL DBMS, which will contain four tables (Customers, Shipments, Movies and stock) linked together. Customers. customer_id last_name first_name address city state postcode Shipments shipment id customer_id movie_id media_type shipment_date Stock movie id media type cost_price retail price current_stock Movies movie id movie title director_first_name director_last_name genre release date studio_name Figure 1. The database schema for MovieDirect. The SQL script to import data into your completed database is available here: http://turing.une.edu.au /-cosc210/assignments/a4/MovieDirect_Data.sql 1 of 3 The SQL script to import data into your completed database is available here: http://turing.une.edu.au /~cosc210/assignments/a4/Movie Direct_Data.sql Exercise 2 - A set of user views with SQL queries. Please make sure that you complete your user views using the template that is provided. Your assignment will be marked with the assistance of automated tools and if the names of the views and their attributes are COSC210 Assignment not correct, you may lose marks. The assignment template is available here: http://turing.une.edu.au/~cosc210/assignments /a4/p_template.sql 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. (10 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. (10 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. (10 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%. (10 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. (10 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. (10 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'. (15 Marks) https://turing.une.edu.au/~cosc210/assignments/display_no... Marking 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). (15 Marks) Make sure to test your scripts on turing.une.edu.au and utilise the template and import script provided. Assignment Submission You should submit one file for this assignment: An sql file named in the format: exercise_2_ .sql (e.g. exercise_2_esadgro2.sql) Submit your assignment via turing.une.edu's submit program. You should submit your assignment to the p2 assignment. . The instructions for submit are available here Item Admin Files named correctly SQL script runs without errors (Ex2) Exercise 2 PSQL User View Question 1 PSQL USer View Question 2 9/2/23, 13:03 Marks - /10 - 14 - 16 -/90 - /10 - /10 2 of 3 COSC210 Assignment Item PSQL User View Question 3 PSQL User View Question 4 PSQL User View Question 5 PSQL User View Question 6 PSQL User View Question 7 PSQL User View Question 8 Marks - /10 - /10 -/10 -/10 -/15 -/15] 9/2/23, 13:03 https://turing.une.edu.au/~cosc210/assignments/display_no...