For this assignment, you will implement and query a database from a supplied ER Diagram and Schema. You will be required to write the SQL statements to create the database structures, to fill the database with data and to run queries on the data.
Timelines and Expectations
Percentage Value of Task: 20%
Due: Wednesday Week 11 (11:55 pm)
Minimum time expectation: 20 hours
Learning Outcomes Assessed
The following course learning outcomes are assessed by completing this assessment:
A1.design and implement a relational database using a database management system
A2.utilise a query language tools and techniques to obtain data and information from a database
K5. describe relational algebra and its relationship to Structured Query Language (SQL)
S1.interpret entity-relationship diagrams to implement a relational database
S2.demonstrate skills in designing and building a database application using a commercially available database management system development tool
S3.use a query language for data manipulation
Starstruck Entertainment is a company that sells movie and movie merchandise. The company has provided you with a standard solution for the conceptual data model (the ER diagram). You have been commissioned to continue your role (in your capacity as a Database Management System consultant) and to write the SQL statements to create the database and its tables, add data, and provide answer to some of their everyday queries. The database should be created based on the ER diagram provided at the end of this document. Some sample data are also provided to be used for inserting data to tables. You are required to insert sufficient data to test the reports given below. CRICOS Provider No. 00103D ITECH 1006 Assignment 2 Semester 2 2017 V2 – corrected.docx Page 2 of 10
To implement the database
1. 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 Startstruck_Entertainment_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 to 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 suppliers, sorted by suburb in ascending order.
b. List all the titles of all the movie items in inventory with their supplier’s name, email, phone number and suburb.
c. List all the actors who are still alive. Print their name as ONE column with a suitable heading, and their age in years.
d. List all of the inventory by genre. Display the title and the genre of each, sort in descending order of title.
e. List all inventory items that not movies in alphabetical order. Include title, unit price, mark-up, quantity in stock and supplier name, as well as manufacturer name
f. List all genres that have no inventory items.
g. Delete the all customers from postcode 3353.
h. Change the name of the inventory Item “Wookie Figure” to ” Wookie Figure – live size”.
CRICOS Provider No. 00103D ITECH 1006 Assignment 2 Semester 2 2017 V2 – corrected.docx Page 3 of 10
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 movie with the title “Solaris” (genre scifi) directed by Steven Soderbergh in 2002. The director was born on 14 January 1963 in Atlanta. The wholesale price is $18 and the mark up is 35%. The shop has 27 in stock and there is a 10% discount applicable to this item.
k. List all the inventory with its title, its quantity on hand and its sales price (price plus markup).
l. List the actors by first and surname, showing every movie they have been cast in, and the genre of the movies. Show in descending order of actor’s last name.
Your documentation should include:
1. A title page clearly stating your name and student ID and your tutor’s name.
2. A copy of the relational schema you have developed from the ER diagram provided.
3. A copy of the three files you are required to create included in the document (ie nnnn_create.txt, nnnn_insert.txt and nnnn_query.txt).
4. Full APA referencing of any resources you have used to complete your submission
You should submit the following:
1. The documentation file described above EITHER as a MS word file or a PDF. If you are using a mac, please submit in a PDF so I can be sure of opening it.
2. The nnnn_create.txt file (where nnnn is your student number).
3. The nnnn_insert.txt file (where nnnn is your student number).
4. The nnnn_query.txt file (where nnnn is your student number).
Please note that the SQL files must be either .txt or .sql because I will be running them. If they are in a word or pdf file there will be extra characters that will prevent the files from running without errors and THIS WILL COST YOU MARKS!
The assignment is to be submitted via the Assignment 2 submission box in Moodle. This can be found in the Assessments section of the course Moodle shell.