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

EMPLOYEE

LOCATION

PROD_LOCATION

PRODUCT

DEPARTMENT

CLIENT

CLIENT_ADDRESS

STOCK_REQUEST

REQUEST_LIST

PICKING_LIST