Recent Question/Assignment

Assignment 2
Database Implementation and Queries
Due Date:
Friday Week 11 (See Course Description for further date and time).
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.
ITECH5006 Students Only
1. Make a list of all ‘action’ movie CDs and their price in ascending order of price. Show the CD title and price.
2. A list of supervisors (employee id, name) and all of their subordinates (employee id, name).
3. Show the pay information (employee name, hours paid, amount paid) of all casual employees in the most recent pay date.
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 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.
What to submit
An electronic copy of your assignment should be submitted through Moodle and should include a copy of your report, completed according to the Federation University Australia Guide for the Presentation of Academic Work (available http://www.ballarat.edu.au/generalguide) and the three files described in Implementation of the Database above.
Your document should include:
• A copy of the SITE Assignment Coversheet that includes a copy of the plagiarism statement.
• A copy of the SQL statements required to:
o create the database and tables;
o insert sufficient sample data into each table to demonstrate that your queries work.
o a report of the results from running the SQL queries by using Copy/paste of their output.
• A bibliography containing a list of all resources used to complete the assignment. If no resources, apart from the course materials, have been used please indicate this.
Assessment Criteria
• Adhere to the specific design requirement given on Page 1 (check the supplementary document for an example).
• How clear and well organised your presentation is. On the front page of your report you should include a list of acknowledgements of all people who have assisted you with this assignment including fellow students, along with a statement of completion.
• Adherence to our standards. How clear and well organised your presentation is. You should write all the queries in consistent style and use indent format.
• Data correctness and quality. Please use appropriate data for your examples (e.g. do not use inappropriate person names)
• Joining of data from multiple tables should be completed using a WHERE statement only. JOINs are not to be used within any of the SQL statements. Use of any JOINs will result in 0 (zero) marks being allocated for each SQL statement that utilizes them.
• Please refer to the provided marking guide (below) to see the distribution of marks. Take it as a guide, but NOT as an exact distribution of marks.
Assignment Resources:
• The Standard ER Diagram
• Relational Database Schema
• A supplementary document (from online) to help you how to implement the specific design requirement involving supertype and subtype entities in the ERD.
Hint: you need to decide the order that tables need to be created; and the order of tables in which data need to be inserted into.

Notes for the ERD:
• Primary keys are underlined and foreign keys are made italic (also coloured for easy identification).
• Supertype and subtype relationships are: “EMPLOYEE to FULLTIME and CASUAL” and “PRODUCT to CD and OTHERPRODUCT”.
2. Relational Database Schema
Relation Attributes Data type Comment
ADDRESSTYPE AddType_ID CHAR(1) Primary Key, Expected examples ‘R’ for residential, ‘P’ for postal and ‘B’ for both res. & pos.
Add_Type VARCHAR(15)
CUSTOMER Cust_Number INT Primary Key, Auto Increase
Cust_FName VARCHAR(30)
Cust_LName VARCHAR(30)
Cust_Phone CHAR(10) Includes area code
Address_ID INT Foreign key reference
ADDRESS (Address_ID)
ADDRESS Address_ID INT Primary Key, Auto Increase
Address_St VARCHAR(50) e.g., 152 Temple St
Address_City VARCHAR(30) e.g., Ballarat
Address_State VARCHAR (3) Examples – VIC, NSW
Address_Postcode CHAR(4) Examples – 3350, 2001
Add_TypeID CHAR(1) Foreign key reference
ADRESSTYPE(AddType_ID)
JOBTYPE JobType_ID INT Primary Key, Auto Increase
JobType_Name VARCHAR(30) e.g., Accountant
ARTIST Artist_ID INT Primary Key, Auto Increase
Artist_FName VARCHAR(30)
Artist_LName VARCHAR(30)
Artist_DoB DATE Format: ‘YYYY-MM-DD’
Artist_Website VARCHAR(100)
CATEGORY Category_ID VARCHAR(5) Primary Key, example, ‘M’ for music, ‘AM’ for action movies
Category_Desc VARCHAR(40) e.g., Music, Adult movies
DEPARTMENT Dept_ID INT Primary Key, Auto Increase
Dept_Name VARCHAR(40) e.g., Sales, Training, Payroll
STORE Str_Num INT Primary Key, Auto Increase
Str_Name VARCHAR(50) e.g., BigM Ballarat
Str_Phone CHAR(10) Includes area code
Str_Fax CHAR(10) Includes area code
Str_Email VARCHAR(40)
StoreManagerID INT Foreign key reference
EMPLOYEE(Emp_ID)
SupStore_Num INT Foreign key reference
STORE(Str_Num)
Address_ID INT Foreign key reference
ADDRESS (Address_ID)
STOREDEPARTMENT StrDept_ID INT Primary Key, Auto Increase
StrDept_Phone CHAR(10) Includes area code
StrDept_Email VARCHAR(40)
DeptSupervisorID INT Foreign key reference
EMPLOYEE(Emp_ID)
Str_Num INT Foreign key reference
STORE(Str_Num)
Dept_ID INT Foreign key reference
DEPARTMENT (Dept_ID)
JOBTIME JobTime_ID CHAR(1) Primary Key, expected ‘F’ for fulltime and ‘C’ for casual
JobTime_Desc VARCHAR(10) e.g., fulltime, casual
PRODUCTTYPE ProdType_ID CHAR(1) Primary Key, expected ‘C’ for CD and ‘O’ for Other product
ProdType_Desc VARCHAR(30) e.g., ‘CD’ and ‘Other product’
EMPLOYEE Emp_ID INT Primary Key, Auto Increase
Emp_FName VARCHAR(30)
Emp_LName VARCHAR(30)
Emp_Phone CHAR(10) Includes area code
Emp_DoB DATE Format: ‘YYYY-MM-DD’
Emp_StartDate DATE Format: ‘YYYY-MM-DD’
Emp_TaxFNum CHAR(12) e.g., ‘123456789101’
StrDept_ID INT Foreign key reference
STOREDEPARTMENT
(StrDept_ID)
SupvisorID INT Foreign key reference
EMPLOYEE(Emp_ID)
Address_ID_Postal INT Foreign key reference
ADDRESS (Address_ID)
Address_ID_Resident INT Foreign key reference
ADDRESS (Address_ID)
JobTime_ID CHAR(1) Foreign key reference
JOBTIME (JobTime_ID)
JOBFULLTIME FullTimeJob_ID CHAR(1) Primary Key
JOBCASUALTIME CasualJob_ID CHAR(1) Primary Key
FULLTIME Emp_ID INT Primary Key, Foreign key reference
EMPLOYEE(Emp_ID)
Emp_salary_yearly DOUBLE(10,2) e.g., 110,000.34
JobType_ID INT Foreign key reference
JOBTYPE (JobType_ID)
FullTimeJob_ID CHAR(1) NOT NULL, DEFAULT ‘F’,
Foreign key reference
JOBFULLTIME
(FullTimeJob_ID)
CASUAL Emp_ID INT Primary Key, Foreign key reference
EMPLOYEE(Emp_ID)
Emp_salary_hourly DOUBLE(10,2) e.g., 58.50
JobType_ID INT Foreign key reference
JOBTYPE (JobType_ID)
CasualJob_ID CHAR(1) NOT NULL, DEFAULT ‘C’,
Foreign key reference
JOBCASUALTIME
(CasualJob_ID)
PAYSLIP Pay_ID INT Primary Key, Auto Increase
Pay_date DATE Format: ‘YYYY-MM-DD’
Pay_num_of_hours DOUBLE(4,2) e.g., 35.50
Pay_amount_gross DOUBLE(6,2) e.g., 345.35
Emp_ID INT Foreign key reference
EMPLOYEE(Emp_ID)
Str_Num INT Foreign key reference
STORE(Str_Num)
CDTYPE CDType_ID CHAR(1) Primary Key
OTHERTYPE OtherType_ID CHAR(1) Primary Key
PRODUCT Prod_Num INT Primary Key, Auto Increase
Foreign key reference
ProdType_ID CHAR(1) PRODUCTTYPE
(ProdType_ID)
CDARTIST Prod_Num INT Primary Key, Foreign key reference CD (Prod_Num)
Artist_ID INT Primary Key , Foreign key reference ARTIST (Artist_ID)
CDArtist_NumTracks INT
CD Prod_Num INT Primary Key, Foreign key reference PRODUCT (Prod_Num)
CD_Title VARCHAR(50)
CD_Length TIME Format: 'HH:MM:SS'
CD_NumOfTracks INT
CD_ReleaseDate DATE Format: ‘YYYY-MM-DD’
CD_Price DOUBLE(4,2) e.g., 99.50
CD_ReorderLevel VARCHAR(10)
CD_CategoryID VARCHAR(5) Foreign key reference
CATEGORY (Category_ID)
CDType_ID CHAR(1) NOT NULL, DEFAULT ‘C’,
Foreign key reference
CDTYPE (CDType_ID)
OTHERPRODUCT Prod_Num INT Primary Key, Foreign key reference PRODUCT (Prod_Num)
OthProd_Desc VARCHAR(50) e.g., Pant for man, skirt for girl
OthProd_Size VARCHAR(10) e.g., Large, Medium, etc.
OthProd_Price DOUBLE(4,2) e.g., 10.50
OthProd_brand VARCHAR(20) e.g., Masters, Century
OtherType_ID CHAR(1) NOT NULL, DEFAULT ‘O’,
Foreign key reference
OTHERTYPE
(OtherType_ID)
INVENTORY ProductNum INT Primary Key, Foreign key reference PRODUCT (Prod_Num)
Str_Num INT Primary Key, Foreign key reference STORE (Str_Num)
Inv_QntyOnHand INT
Inv_QtyOrdered INT
CUSTOMERORDER CustOrd_ID INT Primary Key, Auto Increase
CustOrd_Date DATE Format: ‘YYYY-MM-DD’
Cust_Number INT Foreign key reference
CUSTOMER (Cust_Number)
Str_Num INT Foreign key reference STORE (Str_Num)
ORDERLINE CustOrd_ID INT Primary Key, Foreign key reference
CUSTOMERORDER
(CustOrd_ID)
Prod_Num INT Primary Key, Foreign key reference PRODUCT (Prod_Num)
OrdLn_DateArrived DATE Format: ‘YYYY-MM-DD’
OrdLn_DatePicked DATE Format: ‘YYYY-MM-DD’
OrdLn_Qnty INT
End of specification.
Assignment 2 – Marking Overview
Documentation /5
Create statements /15
insert statements /15
Queries /45
ITECH5006 Queries /15
Total: ITECH1006 /80 ITECH5006 /95

ITECH5006 Assignment 2 Marking Guide
Student name: Student ID: Marker:
ITEM Max Mar k
Documentation /5
document well laid out and easy to read contains a coversheet or front page
contains a table of contents and is set out according to the university guidelines
contains all of the required components of the assignment bibliography included in APA format 1
1
1
1
1
Create statement /15
Create table,
relationship, correct supertypesubtype design Create (all/most/some/no) of the relevant new tables
Database correctly named and created (Includes own name and number) Includes all of the PKs and FKs and relationships in the database
No evidence that tables have been created using the GUI
Maintains the specific design requirement for supertype and subtype They work properly 4
3
2
1
3
2
Insert statement /15
Insert, correct supertypesubtype design Successfully inserts data into (all/most/some/no) of the tables
Includes own details as one of the customers
INSERT file includes primary keys
NO evidence exporting from GUI
Maintains the specific design requirement for supertype and subtype They work properly 2
2
2
1
2
1
Sufficient data Contains (sufficient/some/no) data to test (all/some/no) of the queries (thoroughly/ only with limited entries/no entries)
5
SQL Queries /45
Q1 - appropriate statement formatted as per instructions (e.g., name and address)
- correct joins and where clause
- appropriate ORDER BY
- SQL runs well 5
Q2 - appropriate statement formatted as per instructions (e.g., name)
- Includes required where clause
- correct joins
- SQL runs well 4
Q3 - appropriate statement formatted as per instructions (e.g., address)
- Includes required where clause
- correct joins and ORDER BY
- SQL runs well 4
Q4 - appropriate statement formatted as per instructions (e.g., name)
- Includes required where clause
- correct subquery
- SQL runs well 5
Q5 - appropriate statement
- Includes required where clause
- correct joins and union
- SQL runs well 4
Q6 - correct statement according to the description (AS [Total: ]) 5
- correct joins and where clause
- Includes required GROUP BY
- SQL runs well
Q7 - correct statement according to the description
- Includes required where clause
- Correct use of joins and ORDER BY
- Includes required GROUP BY
- SQL runs well 5
Q8 - appropriate statement
- Includes required where clause
- correct use of sub-query
- SQL runs well 4
Q9
- correct statement formatted as per instructions (e.g., name, $ salary)
- Includes required where clause
- correct joins & where clause
- SQL runs well 5
Q10
- correct statement according to the description
- Includes required where clause
- correct joins and ORDER BY
- SQL runs well 4
ITECH5006 ONLY 15
Q1 - correct statement formatted as per instructions (e.g., $ price)
- Includes required where clause
- correct joins and ORDER BY
- SQL runs well 4
Q2 - correct statement formatted as per instructions (e.g., name)
- Includes required where clause
- correct joins
- SQL runs well 5
Q3 - correct statement and formatting (e.g., $ payment)
- Includes required where clause
- correct joins
- Includes required sub-query
- SQL runs well 6
Total ITECH 5006 /95
Total ITECH1006 /80
Weight /20%
General Comment: