Database Management Systems Assignment
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.
Learning Outcomes - 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 - 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
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 bfe_nnnn (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 (you will lose marks if you only insert the sample data).
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 wholesalers, sorted by suburb in ascending order.
b. List all the titles of all the books in inventory with their wholesaler's name, email, phone number and suburb.
c. 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.
d. List all of the inventory by category. Display the title and the category of each, sort in descending order of title.
e. 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.
f. List all categories that have no inventory items.
g. Delete the all customers from postcode 3353.
h. Change the price of the inventory Item "A Brief History of Time" to $15.99.
i. 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.
j. 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.".
k. Find the fulfilled orders made since 1st July 2017. List customer name and surname, date of order, number of items ordered and total price.
l. Find the book which was ordered most often. List the author name and surname and date published.
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. Ensure that you ist any assumptions you have made.
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 - where nnnn is your student number).
4. Full APA referencing of any resources you have used to complete your submission.
Attachment:- Assignment File.rar