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
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.
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).