Translate the ER diagram provided into a relational schema using the correct notations and standards for this course. It should include: a. Table names b. Attribute names and field types (as required...

Translate the ER diagram provided into a relational schema using the correct notations and standards for this course.  It should include: a. Table names b. Attribute names and field types (as required by XAMPP) c. Primary and foreign keys identified 2. Create a text file called nnnn_create.sql (where nnnn is your student number).   The file should include: a. The SQL statements to create a database called BHE_Holdings_nnnn.txt (where nnnn is your student ID) b. The SQL statements to create the tables and the relationships between them.  3. Create a text file called nnnn_insert.txt (where nnnn is your student number).  The file should include: a. The SQL statements to load sufficient data into each table you have created to test the queries given below.  Sample data has been provided, but you will need to include further data test the requirements.  You should add at least one record to the existing test data for each table. b. You are required to include your name as one of the customers (you don’t have to have your address if you don’t want to), but you MUST have your name and give your student id as the phone number.  4. Create a text file called nnnn_query.txt (where nnnn is your student number).  The file should include the SQL statements necessary to display the following reports:  a. List all details of all the records in the customers table, sorted by postcode in descending order. b. List all the titles of all the items in inventory with their supplier’s name, email, phone number and postcode. c. List all the actors who are deceased.  Print their name as ONE column with a suitable heading, their date of birth and their date of death. d. List all of the inventory by genre. Display the title and the genre of each. e. List all the movies in the shop in alphabetical order. Include movie name, genre, classification, star rating and director name. f. List all genres that have no titles. g. Delete the all customers from Beaufort. h. Change customer Mary Spring's name to Mary O’Sullivan. i. List all the movie titles with their cast and their director. Print the name of the actors and the directors in one column for each with an appropriate title. j. Insert a game with the title "Faraway Stars" (genre scifi) published by Moonstruck Games in 2016. The company is located at 1020 Oak Dr, Longwood, 3546, email [email protected], phone 4561327895, and contact is Jessie Fandango.  The wholesale price is $38 and the mark up is 40%.  The shop has 13 in stock and there is no discount applicable to this item.



CRICOS Provider No. 00103D ITECH 1006 Assignment 2 Summer Semester 2016.docx Page 3 of 10

k. List all the inventory with its title, its quantity on hand and its sales price (wholesale plus markup). l. List every item in inventory with its title, genre and whether it is a game or a movie