Reference no: EM13120105

Objectives:

To analyse and comprehend a provided ER diagram and Database Schema

To implement a database based on the provided ER diagram and Database Schema

To write required SQL statements to query the database

Project Specification

The management team now require a partial implementation of the design made in Assignment 1. In order to keep consistency between the assignments, database specification containing the ER diagram and the schema is provided in this document. You should create your database according to this documentation. Please make sure that your implementation is consistent with this design. This means that your table names (upper case), field names (mixed case, no spaces) and data types have to be according to the specifications provided in this document. The implementation phase includeswriting SQL statements to create a database and its tables, populating the tables with data, writing a number of queries to create reports that can be used by the management team.  Your database should contain sufficient data in each table (5 - 10 records in each table) to demonstrate that your queries work.

Implementation of the Database

To implement the database system, you are required to

a) provide SQL commands to create the database, its tables, the relationships of the tables

b) Create a text file called YourStudentId-Create.sql (format xxxxxxx-Create.sql) for example 2225991-Create.sql that will provide SQL commands to:

i. create a database called WareMart[YourStudentID] (egWareMart30011111)

ii. create all of the required tables for the database including their primary keys, foreign keys and the relationships of tables. 

c) Create a text file called YourStudentId-Insert.sql (format xxxxxxx-Insert.sql) for example 2225991-Insert.sql that willprovide SQL commands to:

i. Insert sufficient data into each table you have created to test the queries(at least 5 records in each table).

ii. You are required to include our full name as one of the clients; but you can provide fake details for your address.

d) Create a text file called YourStudentId-Queries.sql (format xxxxxxx-Queries.sql - for example 2225991-Queries.sql) that contains all of the queries to display the following reports:

1. An alphabetically sorted list of all clients. Only client number and name are required.
2. List of names and complete address of all employees sorted by their salary.
3. The date on which the most recent stock request has been made. The date itself will suffice.
4. List of all the client names and their  residentialaddresses.
5. A list of all clients that have not placed a stock request yet. Displaying client number will be sufficient.
6. A list containing the name (surname and first name) of any employee that has picked any product(s) for a stock request.
7. A list containing the total quantity on hand for each product (product no and description) regardless of warehouses and location.
8. A list showing each product requested on each client stock request. Show client name, product number and quantity requested. sorted by client name and then product number.
9. A list of employees (surname and first name are sufficient) and their salary for all employees whose salary is less than or equal to average salary.
10. A list of employees as in question 9, but show their salary with a 7.5% increase.

1.   A list of all products (product number and description) and the quantity on hand for that product for each location at which it is stored within each warehouse. Sort it by product number and then place all locations (warehouse number and location number) for a given product together. 2.   A list showing product number, the quantity requested, the quantity picked and the difference between the two. For products stored in more than one location within a warehouse the quantities should be added together. 3.    A list of supervisors (staffid, surname and first name) and all of their subordinates (staffid, surname and first name).

 Note:There are some general requirements when defining your select queries:

You are required to adhere to the following output formatting conventions:

• Any query requiring names of people should be printed as GivenNameFamilyName (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
• All monetary values should be printed with a dollar symbol ($), two digits after the decimal point, and with space for 7 digits before the decimal point
• You must use consistent and legible formatting in laying out your SQL queries. Include (brief) comments for any query or procedure that uses an "unusual" approach.