ITECH1006 Database Management Systems

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:

design and implement a relational database using a database management system
utilise a query language tools and techniques to obtain data and information from a database
describe relational algebra and its relationship to Structured Query Language (SQL)
interpret entity-relationship diagrams to implement a relational database
demonstrate skills in designing and building a database application using a commercially available database management system development tool
use a query language for data manipulation

Assessment Details

Books For Everyone (BFE) is a company that sells books and e-books. 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.

To implement the database

Translate the ER diagram provided into a relational schema using the correct notations and standards for this course. It should include:
1. Table names
2. Attribute names and field types (as required by XAMPP)
3. Primary and foreign keys identified
Create a text file called nnnnsql (wherennnnis your student number). The file should include:
1. The SQL statements to create a database called bfe_nnnn(where nnnnis your student ID)
2. The SQL statements to create the tables and the relationships between them.
Create a text file called nnnn_txt (where nnnnis your student number). The file should include:
1. 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 recordto the existing test data for each table(you will lose marks if you only insert the sample data).
2. You are requiredto 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.
Create a text file called nnnntxt (where nnnnis your student number). The file should include the SQL statements necessary to display the following reports:
1. List all details of all the wholesalers, sorted by suburb in ascending order.
2. List all the titles of all the books in inventory with their wholesaler’s name, email, phone number and suburb.
3. List all the authors who are based in USA and who have a biography on file. Print their name as ONE column with a suitable heading, and show the number of books written by the author in inventory.
4. List all of the inventory by category. Display the title and the category of each, sort in descending order of title.
5. List all reviews with reviewer name and review date and title of inventory item. Also Include book title, author name and surname. Order by reviewer name.
6. List all categories that have no inventory items.
7. Delete the all customers from postcode 3353.
8. Change the price of the inventory Item “A Brief History of Time” to $15.99.
9. List all the books and e-books with their authors. Print the name of the authors and their roles in one column for each with an appropriate title.
10. Insert an e-book as follows: the title is “Astrophysics for People in a Hurry" (category Science) written by Neil deGrasse Tyson in 2017. The author is based in USA, and no details are on file yet for this author. The e-book price is $18 for the Kindle version, and the abstract is: “What is the nature of space and time? How do we fit within the universe? How does the universe fit within us? There’s no better guide through these mind-expanding questions than acclaimed astrophysicist and best-selling author Neil deGrasse Tyson.”.
11. Find the fulfilled orders made since 1^stJuly 2017. List customer name and surname, date of order, number of items ordered and total price.
12. Find the book which was ordered most often. List the author name and surname and date published.

Your documentation should include:

A title page clearly stating your name and student ID and your tutor’s name.
A copy of the relational schema you have developed from the ER diagram provided. Ensure that you ist any assumptions you have made.
A copy of the three files you are required to create included in the document (ie nnnntxt, nnnn_insert.txt and nnnn_query.txt - where nnnnis your student number).
Full APA referencing of any resources you have used to complete your submission

You should submit the following:

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. If you submit a compressed archive, please use zip only.
The nnnntxt file (where nnnnis your student number).
The nnnntxt file (where nnnnis your student number).
The nnnntxt file (where nnnnis 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

ITECH1006 Database Management Systems img1

Sample Data for Tables.

CATEGORY
name	description
Cooking	Cookbooks, food and beverage encyclopedias
Sports	Sports documentaries, biographies and stories
Children	Books for all age groups to 15
Science Fiction
Crime Fiction
History	Historical research and ancient book reprints
General Fiction	Any other fiction
Academic	School, college, university books
Health & Wellbeing
Science	Popular Science

	Wholesaler
Name	Contact	Phone	Email	Address	Suburb	Postcode	Country	Comments
Tuggeranong Bookbarn	Jack Pratt	02 6606 1458	[email protected]	1 Jones St	Tuggeranong	2601	Australia	Good prices on health books
Mega Books	James McGrath	+61355551549	[email protected]	32 East St	Perth	6140	Australia
Sporting World	Nadine McMaster	0448789987	[email protected]	8 North Road	Auckland	1141	New Zealand	Christmas orders by December 10
Rare Books	Steven Saunders	0751672924	[email protected]	15 Albury Street	Cabramatta	2166	Australia
Premiere Books	Jodie Vaughn	0657168342	[email protected]	1548 Hume Hwy	Glenfield	2167	Australia
Book Magic	Peter Buttler	+4107529817	[email protected]	15 Boulos Parade	London	9911	UK	Recalculate VAT
VIC Entertainment	Rupert Ballinger	0498528417	[email protected]	62 Yellowstone Ave	Kew	3056	Australia	Great Fiction selection
Monsoon Books & Films	Doris Mackenzie	+1174019841	[email protected]	3 Ironworks Turnpike	Lexington	78428	USA	Minimum order $500 USD

REVIEW
Reviewer	Inventory	Date	Review
2	3	1/7/2015	Extremly well written book, a pleasure to read from cover to cover.
1	2	15/6/2008	Well researched and a thoroughly great read!
4	1	30/9/2017	Enjoyed the recipes!
5	6	24/12/2012	A rip-roaring great story. Fantastic description of characters. Can’t wait for the next book in the series.
6	3	24/2/2017	Good read, but not as detailed as I expected.
1	5	15/12/2016	A masterfully written tale.
3	7	1/4/2014	The kids loved this book!

E-Book Type
File Type	Description
PDF	Portable document format – all devices
Kindle	Kindle device, also readers
Audiobook
HTML	Online only

CUSTOMER
First name	Last Name	Phone	Email	Address	Suburb	Post-code	Loyalty Discount
John	Brigg	53159510	[email protected]	1 Bells Road	Napoleons	3352	1.3%
Joe	Summers	45613208	[email protected]	52 Main St	Maryborough	3465
Sam	deWinter	12356713	[email protected]	2156 Skipton Rd	Sebastopol	3356
Mary	Donald	13265413	[email protected]	98 Neil St	Cressy	3373
Sally	Fall	15626543	[email protected]	65 Commercial St	Clunes	3370	2.5%
Toby	McMillan	040853185	[email protected]	65 Crest Ave	Ballarat	3353
Pat	Jones	049513572	[email protected]	46 Wattle Ave	Wendouree	3355	2.5%
Dick	Matthews	21356467	[email protected]	7 Hepburn Springs Blvd	Daylesford	3460
Tommy	Jones	51657896	[email protected]	1254 Sturt St	Ballarat	3353	4%
Barry	McKenzie	32165489	[email protected]	47 Gillies St	Ballarat	3350

AUTHOR
First Name	Surname	Country	Bio	Books written
Ina	Garten	UK	In 1978, Ina Garten left her job as a budget analyst in the White House to pursue her dream of operating a specialty food store in the Hamptons. She is a frequent contributor to major national magazines and writes a recurring column in O Magazine.	1 (author) 2 (author)
Jack L.	Chalker	USA	No info available	5 (author) 6 (author)
Stephen	Hawking	USA	Stephen Hawking's ability to make science understandable and compelling to a lay audience was established with the publication of his first book, A Brief History of Time, which has sold nearly 10 million copies in 40 languages.	3 (author) 4 (author)
Vera	Brosgol	USA	No info available	7 (illustrator)
Susan D.	Kalior	USA	No info available	8 (author)
Aaron	Reynolds	USA	No info available	9 (author)
Gary	Mack	USA	Gary Mack is a leading sports psychology consultant and counselor who has worked with athletes in the NBA, NFL, NHL, WNBA, and Major League Baseball. He is president of two consulting firms, Sports Assist and Planning Solutions; the director of sports psychology for Griffey International; and team counselor for the Phoenix Suns and Phoenix Mercury	10 (co-author)
David	Casstevens	USA	No info available	10 (co-author)

	INVENTORY
ID	Title	Cat.	Unit Price	abstract	Year published	Quantity in Stock (Book)	Whole-saler (Book)	E-book type (E-Book)
1	Cooking for Jeffrey	1	$21.38	Ina’s most personal cookbook yet, Cooking for Jeffrey is filled with the recipes Jeffrey and their friends request most often as well as charming stories from Ina and Jeffrey’s many years together.	2016	5	2	n/a
2	Barefoot in Paris	1	$24.79	Hearty boeuf Bourguignon served in deep bowls over a garlic-rubbed slice of baguette toast; decadently rich croque monsieur, eggy and oozing with cheese; gossamer crème brulee, its sweetness offset by a brittle burnt-sugar topping. Here is the book that helps you bring that spirit, those evocative dishes, into your own home.	2004	8	2	n/a
3	A Brief History of Time	10	$14.00	A landmark volume in science writing by one of the great minds of our time, Stephen Hawking’s book explores many profound questions.	1998	10	2	n/a
4	The Grand Design	10	$10.00	A landmark volume in science writing by one of the great minds of our time, Stephen Hawking’s book explores many profound questions.	2012	n/a	n/a	Kindle
5	Midnight at the Well of Souls	4	$14.99	Midnight at the Well of Souls is the first book set in the Well World universe.	2014	5	7	n/a
6	The Sea Is Full Of Stars	1	$7.99	This exciting, action-packed novel marks Jack Chalker's triumphant return to his celebrated multivolume saga: The Well World. The Sea Is Full of Stars explores an unknown interstellar civilization.	1999	4	7	n/a
7	Leave Me Alone!	3	$10.33	One day, a grandmother shouts, "LEAVE ME ALONE!" and leaves her tiny home and her very big family to journey to the moon and beyond to find peace and quiet to finish her knitting. Along the way, she encounters ravenous bears, obnoxious goats, and even hordes of aliens!	2016	16	3	n/a
8	The Simple Guide to Feeling Better	1	$6.00	A decade of solutions from a psychotherapist regarding family and romantic problems, high anxiety, deep depression, self esteem, and life success.	2017	n/a	n/a	Kindle
9	Creepy Carrots	3	$11.95	Jasper Rabbit loves carrots—especially Crackenhopper Field carrots. He eats them on the way to school.	2017	n/a	n/a	Kindle
10	Mind Gym: An Athlete's Guide to Inner Excellence	2	$24.68	In Mind Gym, noted sports psychology consultant Gary Mack explains how your mind influences your performance on the field or on the court as much as your physical skill does, if not more so.	2002	n/a	n/a	Audio book

ORDER
ID	Customer	Date Ordered	Date Fulfilled	Postage	Disc.Applied	Order-lines (inventory id, quantity, amount)
1	1	18/1/2017	19/1/2017	-	1.3%	1, 1, $21.38 8, 1, $6.00
2	6	15/12/2017	18/12/2017	$5.50	-	3, 1, $14
3	2	1/9/2017	6/9/2017	$9.50	-	7, 2, $10.33 9, 1, $9.90 4, 1, $10
4	5	11/10/2017	13/10/2017	$9.50	2.5%	1, 1, $21.38
5	3	1/12/2017	3/12/2017	$9.50	10%	1, 1, $19.24
6	6	8/1/2018		-	-	4, 1, $10
7	4	5/6/2017	10/6/2017	$9.50	-	1, 1, $21.38 2, 1, $24.79 6, 1, $7.99
8	7	3/1/2018		$5.50	-	5, 1, $14.99
9	8	28/5/2017	30/5/2017	$5.50	-	8, 1, $6.00 3, 1, $14
10	9	7/10/2017	7/10/2017	-	4%	4, 1, $10

Lost Password