Assignment 2
Overview
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%
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
Assessment Details
BM Holdings 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.

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 BMHoldings_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 suburb in ascending order.
b. List all the titles of all the items in inventory with their supplier’s name, suburb 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 DVDs in the shop. Include DVD name, genre, classification, star rating and director name.
f. List all genres that have no titles.
g. Delete the customer Pat Jones.
h. Change customer Mary Spring's name to Mary Fitzpatrick.
i. List all the DVDs 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 book with the title -Food Forests for Dummies- (genre gardening) written byFlora Birch in 2016. The author was born in Sherwood (UK) on the 1st of April 1950. The book was published by Green and Friendly Publications at 123 Poplar Dr, Oakwood, 3546, email greenandfriendly@greenwood.com.uk, phone 4561327895, and contact Daisy Appleyard. The wholesale price is $20 and the mark up is 40%. The shop has 5 in stock and there is no discount applicable to this item.
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 book or a dvd.
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.

ER Diagram

Sample Data for Tables.
GENRE
GenreName
Comedy
Horror
SciFi
Crime Fiction
Historical Fiction
RomCom
Nonfiction
Fantasy
SciFi Fantasy
Fantasy Adventure
BOOK
ID year cover type publisher
1 1997 paperback 3
2 1998 paperback 3
3 2009 paperback 2
4 2013 paperback 2
5 1895 paperback 3
6 1895 paperback 3
7 1861 hardback 3
8 1846 paperback 3
9 1843 paperback 3
10 1983 paperback 2
11 1986 paperback 2
12 1987 paperback 2
16 2013 paperback 3
Supplier
Name Contact Phone Email Address Suburb Postcode
Murphy's Books Paddy Murphy 0456789163 murphysbooks.com.au 123 Smith St Collingwood 3066
Jones Book and Film Supply Jim Jones +61355551549 jonesbookandfilm.com.au 32 Easy St Collingwood 3066
Books R Us Suzie Smith 0494335701 booksrus.com.au 853 Burwood Hwy Burwood 3125
Budgie Books Jonathon Budgerigar 0435789654 budgiebooks.com.au 316 Aviation St Eltham 3095
Rare Films Samantha Stevens 0751672924 rarefilms.com.au 9 Cabramatta Ave Cabramatta 2166
Premiere Entertainment Winston Jones 0657168342 premiereentertainment.com.au 6548 Hume Hwy Glenfield 2167
Books 'n' Things Sandra Hilman 0407529817 booksnthings.com.au 5 Commodore Ave Melville 6156
SA Entertainment Albert Georges 0498528417 saentertainment.gov.au 62 Gawler Pde Collingwood 6118-
Samson Films Delilah Jones +6174019841 samsonfilms.com.au 27 Fortitude Dr Brisbane 4001-

AUTHOR
Surname Name Place of birth Date of Birth Date of death
Reichs Kathy Chicago, llinios (USA) 7th July, 1948
Castle Richard California 23rd December, 1966
Wells Herbert George Bromlee Kent (UK) 21st September, 1866 13th August, 1946
Dickens Charles Landport, Portsmouth (UK) 7th February, 1812 9th August, 1870
Pratchett Terry Beaconsfield (UK) 28th April, 1948 12 March, 2015
Sewell Anna Great Yarmouth (UK) 30th March, 1820 25th April, 1878
Lee Harper Monroeville Alabama (USA) 128th April, 1926 19 February, 2016
French Dawn Holyhead (UK) 11th October, 1957
King Steven Portland Maine (USA) 21st September, 1947
Tolkien John Ronald Bloemfontein, (Sth Africa) 3rd January, 1892 2nd September, 1973
Clarke Arthur C. Minehead (UK) 16 December, 1917 19th March, 2008
ACTOR
Surname Name Place of birth Date of birth Date of Death
Lee Christopher Belgravia London (UK) 27th May, 1922 7th June, 2015
Hemsworth Chris Melbourne Vic (Aus) 11th August, 1983
Jolie Angelina Los Angeles, Californaia (USA) 4th June, 1975
Streep Meryl Summit New Jersey (USA) 22nd June, 1949
Monroe Marilyn Los Angeles California (USA) 1st June, 1926 5th August, 1962
Theron Charlize Benoni Gauteng (Sth Africa) 7th August, 1975
Barrymore Drew Culver City California (USA) 22nd February, 1975
Liu Lucy Jackson Heights New York (USA) 2nd December, 1968
Tennant David Bathgate (UK) 18th April, 1971
Johnson Dwayne (The Rock) Hayward California (USA) 2nd May, 1972
Downey Robert (Jnr) Manhattan, New York (USA) 4th April, 1966
DIRECTOR
Surname Name Place of birth Date of birth Date of death
Jolie Angelina Los Angeles California (USA) 4th June, 1975-06
Barrymore Drew Culver City, California (USA) 22 February, 1975
Spielberg Steven Cincinnati, Ohio (USA) 18th December, 1946
Heyman David London (UK) 26th July, 1961
Hitchcock Alfred London (UK) 13 August, 1899 29 April, 1980
Tarantino Quinton Knoxville Tennessee (USA) 27th March 1963
Fisher Terence Maida Vale, London (UK) 23 February, 1904 18th June, 1980
Branagh Kenneth Belfast (UK) 10th December, 1960

INVENTORY
title genre wholesale markup discount Quantity in Stock Supplier
Deja Dead (Temperance Brennan #1) 4 $10.00 150% 0 8 1
Death Du Jour (Temperance Brennan #2) 4 $10.00 150% 0 6 1
Heat Wave 4 $12.00 120% 0 6 2
Storm Front 4 $12.00 120% 0 6 2
Time Machine 3 $10.00 130% 0 4 3
War of the Worlds 3 $10.00 130% 0 4 3
Great Expectations 5 $10.00 230% 0 4 4
Oliver Twist 5 $10.00 250% 0 2 4
A Christmas Carol 2 $15.00 160% 0 5 4
The Colour of Magic 1 $8.00 210% 0 6 4
Light Fantastic 1 $8.00 210% 0 6 4
Equal Rites 1 $8.00 210% 0 6 4
Dracula 2 $10.00 120% 0 2 2
Thor 10 $10.00 210% 0 4 9
Devil Wears Prada 1 $10.00 130% 0 5 8
Derrick Storm Meets Temperance Brennen 4 $10.00 140% 0 7 4
PUBLISHER
Name Contact Name Phone Email Address Suburb PostCode
Simon & Schuster Suzie Matthews +61299836600 cservice@simanandschuster.com.au 450 Miller St Cammeray 2062
Random House John Clarke +61385374599 iformation@penguinrandomhouse.com.au 707 Collins St Melbourne 3008
Pocket Books David White +1456123789 info@pocketbooks.com 1230 Avenue of the Americas New York 1020
DVD
ID Year classification stars director
13 1958 M 4 7
14 2011 M 4 8
15 2006 MA 3 2
Customer
Surname Name Phone Email Address Suburb Postcode Discount
John Smith 53159510 johnno@gmail.com 123 Smith St Ballarat 3350
Joe Summers 45613208 jsummers@yahoo.com
52 Main St Maryborough 3465
Sam Winter 12356713 winter@xyz.net.au
2156 Skipton Rd Sebastopol 3356 0.05
Mary Spring 13265413 flowers@iprimus.com
98 Neil St Beaufort 3373
Sally Fall 15626543 autumn@gmail.com
65 Commercial St Clunes 3370
Fred Nerke 040853185 Fred.nerke@gmail.com
653 Old Melbourne Rd Ballarat 3350 0.10
Pat Jones 049513572 patsyJ@anet.com.au 46 Wattle Ave Wendouree 3355
Margie Matthews 21356467 mm2@mynet.com 87 Hepburn Rd Daylesford 3460
Tom Jones 51657896 whatsnew@wawa.uk 1254 Sturt St Ballarat 3350
Barry McKenzie 32165489 bazza@adventure.com 47 Gillies St Ballarat 3350 0.20
Marking Criteria/Rubric
Assessment Criteria and Marking Overview Tasks Marks
1. Presentation
• Cover page indicating student name and number and tutor name.
• Page numbers included in report
• Index giving page numbers of various sections
• Overall presentation of the report
• Full APA referencing of all materials used and full disclosure of assistance from all sources including tutors and other students. 5
2 Revised Schema
• Any assumptions explained
• All tables included
• Data types correct
• Primary and foreign keys indicated and referenced correctly 10
3. Create Statements
• File runs with no error messages
• Database created correctly
• All tables created correctly
• All primary and foreign keys created correctly
• Any deletion anomalies corrected 10
4. Insert Statements
• File runs with no error messages
• Default data is inserted correctly
• Additional data for testing is inserted
• Student’s name and number are included in customer file 10
5. SQL statements Total 65 Marks
Q a
• Correct syntax
• Correct table used
• Correct use of sorting statement 3
Q b
• Correct syntax
• Correct tables used
• Where statement correctly formulated 4
Q c
• Correct syntax
• Correct table used
• Where statement correctly formulated 4
Q d
• Correct syntax
• Correct table used
• Where statement correctly formulated
• Correct indication of desired values 4
Q e
• Correct syntax
• Correct tables used
• Where statement correctly formulated
• Correct sorting statement used. 5
Q f
• Correct syntax
• Correct tables used
• Correct use of aggregation statement
• Sub-query formulated correctly
• Correct answer given 5
Q g
• Correct syntax
• Correct table used
• Correct result for deletion 6
Q h
• Correct syntax
• Correct table used
• Correct result for update 6
Q i
• Correct syntax
• Correct tables used
• Where statement correctly formulated
• Output formatted correctly 6
Q j
• Correct syntax
• Correct tables used
• Inserts done in correct order with correct results 7
Q k
• Correct syntax
• Correct tables used
• Correct calculation of sales price
• Where statement correctly formatted 7
Q l
• Correct syntax
• Correct tables used
• Output formatted correctly 8
Total 100
Feedback
Feedback will be provided via the marked rubric with comments handed out in the tutorial 2 weeks after the submission date. The marks will also be available on FDL Marks at this time.
Plagiarism:
Plagiarism is the presentation of the expressed thought or work of another person as though it is one's own without properly acknowledging that person. You must not allow other students to copy your work and must take care to safeguard against this happening. More information about the plagiarism policy and procedure for the university can be found at http://federation.edu.au/students/learning-and-study/online-help-with/plagiarism.