SOLUTION AVAILABLE AT: http://libraay.com/downloads/1804ict-data-management-7003ict-database-management-designing-a-database-for-bigm/

Problem:

Aims
-To analyse and comprehend a given ER diagram and Relational Database schema To implement a database based on the given ER diagram and Database schema To write required SQL statements to query the database
-To write SQL statements to manipulate the data in the database
Learning Objectives
In the process of this assessment task you will:
-plan, schedule and execute project tasks with a view to improving your personal productivity; gain awareness of the typical challenges related to the practical implementation of databases; learn how to use Data Definition statements to implement a database from a given ER diagram and the corresponding Database schema; learn how to use Data Manipulation statements to query a database, and insert and update data in the tables

Designing a Database for BigM
Assignment Specification
BigM now requires a partial implementation of the design made in Assignment Part 1. In order to keep consistency between the assignments, database specification containing the ER diagram and the corresponding schema are provided in this document. You should create your database according to this documentation. Make sure that your implementation is consistent with this design, i.e., your table names, field names, and data types are according to the specifications provided in this document. The implementation phase includes writing SQL statements to create a database and its tables, populating the tables with appropriate test data, and writing a number of queries to create reports that can be used by the management team. You need to insert at least five records in each of the tables and ensure that each of the query returns at least one record.

Implementation of the Database and Manipulation of the Data
You are required to perform the followings tasks:

1. Task 1: Create a text file named Create_<StudentID>.sql (for example, Create_s3087654.sql) that will contain SQL statements to:
I. Create a database named BigM_<StudentID>.
II. Create all of the tables for the database according to the Database schema given with this document (separately attached).
2. Task 2: Create a text file named Insert_<StudentID>.sql that will contain SQL statements to:
I. Insert at least five records in each of the tables. The test data inserted into the table must ensure that each of the queries, specified in                  Task 3, outputs at least one record.
3. Task 3: Create a text file named Query_<StudentId>.sql that will contain all the queries to display the following
I. List of names and complete postal address of all employees sorted by their salary.
II. The date on which the most recent customer order has been made. The customer name and date of the order will be sufficient.
III. List of all the store names and their addresses, sorted in dictionary order of the store name.
IV. A list of all customers that have not placed an order yet. Displaying customer number and name will be sufficient.
V. A list containing the name of employees, which work as accountant.
VI. A list containing the total quantity on hand for each product (product number and description) regardless of stores.
VII. A list showing each product sold (picked) on or before May 20, 2018. Show product number, name and quantity sold, sorted by                             product number and then quantity sold.
VIII. A list of products (show product number, description and price) whose price is less than or equal to the average product price.
IX. Increase each employee’s salary by 7.5% and show the updated salary of all employees (name and salary).
X. Show the pay information (employee name, hours paid, amount paid) of all employees in the most recent pay date.

4. Task 4: Create a text file named Update_<StudentId>.sql that will perform the followings. Insert additional data in the tables appropriately if needed.
A customer named Daniel Ortega, from 11 Fuller Road, Marsden QLD 4132 and having a mobile number 0431xxx668, orders two shirts of size “XL” of brand “Prada” on 6 Sep 2018. The full order is ready on 8 Sep 2018 for pickup, but the customer picks up on 10 Sep 2018.
You should insert data into appropriate tables for the above order and make sure you update the data in the relevant tables after the order is picked up.

You are required to adhere to the following output formatting conventions:
Any query requiring names of people should be printed as GivenName FamilyName (e.g. John Smith) in a column labelled NAME;
Any query requiring addresses should be printed as Street, Suburb State Postcode (e.g. 123 Anzac Pde, Maroubra NSW 2038) in a column labelled ADDRESS; and All monetary values should be printed with a dollar symbol ($), two digits after the decimal point.

SOLUTION AVAILABLE AT: http://libraay.com/downloads/1804ict-data-management-7003ict-database-management-designing-a-database-for-bigm/