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 of BigM 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 includes writing 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 (at least, 5 to 10 records in each table; some may require less or more) to demonstrate that your queries work.
Specific Design Requirement
There are a number of supertypes and subtypes (entities) in the ERD (see below). For example, for the supertype EMPLOYEE there are two subtypes FULLTIME and CASUAL. Also for the supertype PRODUCT there are two subtypes CD and OTHERPRODUCT. While an employee can work only ‘fulltime' or ‘casual', a product can only be ‘CD' or ‘other product'. Thus, the relationship from a supertype to one of its subtype is one to one and there is an optional participation sign on the subtype side. Also, when an employee works as ‘fulltime', he/she cannot work as ‘casual', and vice versa. Similarly, when a product is a ‘CD', it cannot be in an ‘other product', and vice versa. Therefore, a special design requirement has to be observed as follows:
• An employee working ‘fulltime' in EMPLOYEE table can only be entered into FULLTIME table (i.e., trying to insert it into CASUAL table will show an error),
• An employee working ‘casual' in EMPLOYEE table can only be entered into CASUAL table (i.e., trying to insert it into FULLTIME table will show an error),
• A product designated as ‘CD' in PRODUCT table can only be entered into CD table (i.e., trying to insert it into OTHERPRODUCT will show an error), and
• A product designated as ‘other product' in PRODUCT table can only be entered into OTHERPRODUCT table (i.e., trying to insert into CD table will show an error).
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 BigM[YourStudentID] (eg BigM30011111)
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 will provide SQL commands to:
i. insert sufficient data into each table you have created to test the queries (at least 5 to 10 records in each table; some table may require less or more).
ii. You are required to include your full name as one of the employee and/or customer; but you can provide fake details for your address and contact.
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. List of names and complete address of all employees sorted by their salary. Also indicate if the employee works fulltime or casual.
2. The date on which the most recent customer order has been made. The customer name and date of order will suffice.
3. List of all the store names and their addresses, sorted in dictionary order of the store name.
4. A list of all customers that have not placed an order yet. Displaying customer number and name will be sufficient.
5. A list containing the name of employees, which work as accountant.
6. A list containing the total quantity on hand for each product (product no and description) regardless of stores.
7. A list showing each product sold (picked) on or before May 20, 2015. Show product number, name and quantity sold, sorted by product number and then quantity sold.
8. A list of CDs (show CD title, release date and price) whose price is less than or equal to the average CD price.
9. Increase each fulltime employee's salary by 7.5% and show the updated salary of all fulltime employees (name and salary).
10. Show all the artist names and CDs in which they appear, sorted by artist first name and then by last name and finally by CD title.
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
• 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.