Reference no: EM13375177

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.

2. Relational Database Schema

WAREHOUSE

Column name	Data type	Comments
warehouseID	VARCHAR(3)	Primary key
street	VARCHAR(20)
city	VARCHAR(15)
state	VARCHAR(3)	Examples - VIC, NSW, QLD
postcode	VARCHAR(4)	Examples - 3350, 2001, 3001
managerID	INT	FK - References MPLOYEE.StaffID

EMPLOYEE

Column name	Data type	Comments
staffID	INT A_I	Primary key
surname	VARCHAR(20)
firstName	VARCHAR(15)
dob	Date	Short date - Example 06/09/1982
street	VARCHAR(20)
city	VARCHAR(15)
state	VARCHAR(3)	Examples - VIC, NSW, QLD, TAS
postcode	VARCHAR(4)	Examples - 3350, 3355, 2001, 3001
salary	Decimal(19,4)
warehouseID	VARCHAR(3)	FK - References AREHOUSE.warehouseID
supervisedBy	INT	FK - References MPLOYEE.StaffID

LOCATION

Column name	Data type	Comments
warehouseID	VARCHAR(3)	Primary key  - Examples W01, W02 FK - References WAREHOUSE. warehouseID
locationID	VARCHAR(3)	Primary key -Examples L01, L02
Aisle	INT
Shelf	INT
Bin	INT
capacity	Double	Capacity in cubic meters

PROD_LOCATION

Column name	Data type	Comments
warehouseID	VARCHAR(3)	Primary key FK - References WAREHOUSE. warehouseID
locationID	VARCHAR(3)	Primary key FK - References LOCATION. locationID
productNum	INT	Primary key FK - References PRODUCT. productNum
quantityOnHand	INT

PRODUCT

Column name	Data type	Comments
productNum	INT A_I	Primary key
description	VARCHAR(30)
packSize	INT
Price dptNumber	Decimal (10,2) INT	FK - References DEPARTMENT.dptNumber

DEPARTMENT

Column name	Data type	Comments
dptNumber	INT A_I	Primary key
dptName	VARCHAR(20)

CLIENT

Column name	Data type	Comments
clientNum	INT A_I	Primary key
clientName	VARCHAR(40)

CLIENT_ADDRESS

Column name	Data type	Comments
clientNum	INT A_I	Primary key FK - References CLIENT.clientNum
addressType	VARCHAR(1)	Primary key. Expected examples 'R' for residential, 'M' for main and 'P' for postal
street	VARCHAR(20)
city	VARCHAR(15)
state	VARCHAR(3)	Examples - VIC, NSW, QLD
postcode	VARCHAR(4)	Examples - 3350, 2001, 3001

STOCK_REQUEST

Column name	Data type	Comments
requestNum	INT A_I	Primary key
requestDate	Date	Short date - Example 06/09/2012
clientNum	INT	FK - References CLIENT.clientNum

REQUEST_LIST

Column name	Data type	Comments
requestNum	INT	Primary key FK - References STOCK_REQUEST.requestNum
productNum	INT	Primary key FK - References PRODUCT.productNum
qtyRequested	INT

PICKING_LIST

Column name	Data type	Comments
warehouseID	VARCHAR(3)	Primary key. FK - References WAREHOUSE. warehouseID
locationID	VARCHAR(3)	Primary key. FK - References LOCATION. locationID
productNum	INT	Primary key. FK - References PRODUCT. productNum
requestNum	INT	Primary key. FK - References STOCK_REQUEST.requestNum
quantityPicked	INT
datePicked	Date	Short date - Example 06/09/2014
pickerStaffID	INT	Foreign Key - References EMPLOYEE.staffID