1804ICT Database Design for Transport Services

This document presents a database design project for Commonwealth Transport Services, completed as part of the 1804ICT Data Management and 7003ICT Database Design courses at the School of Information & Communication Technology. The project includes the design of various database tables such as ADDRESS, CUSTOMER, JOBTYPE, DEPARTMENT, STORE, BRAND, EMPLOYEE, PAYSLIP, PRODUCT, INVENTORY, CUSTOMERORDER, and ORDERLINE. It also features SQL queries for data retrieval, manipulation, and updates, along with the corresponding output tables. The queries cover a range of operations, including selecting distinct records, joining tables, ordering results, using aggregate functions, and updating data. The project also includes INSERT statements to add new data and UPDATE statements to modify existing data. The document concludes with a bibliography, noting that only course materials were used.

1804ICT – DATA MANAGEMENT
7003ICT – DATABASE DESIGN
School of Information & Communication Technology
Trimester 2, 2018
Assignment Part 2:
Designing a Database for Commonwealth
Transport Services
ASSIGNMENT TITLE: _________________________________________________________
Student ID _________________Student Name ____________________________________________
Course Code ______________Course Name ____________________________________________
Date Submitted ____________Lecturer’s Name __________________________________________
Tutor’s Name ________________________________________________________________
Marks obtained: ________. [For marker to fill up.]

Acknowledgements:
I would like to acknowledge all those who facilitated this work, especially the fellow students
who helped me throughout the project. I would also like to acknowledge the college facility
for providing me continuous guidance to ease my work.

Table of Contents
List of Illustrations.................................................................................................................................4
Reports on SQL Queries in Task 3..........................................................................................................7
Reports on SQL Queries in Task 4........................................................................................................10
Bibliography.........................................................................................................................................12

List of Illustrations
ADDRESS:
ADDRESSTYPE:
CUSTOMER:
JOBTYPE:
DEPARTMENT:
STORE:

STOREDEPARTMENT:
BRAND:
EMPLOYEE:
PAYSLIP:
PRODUCT:
INVENTORY:
CUSTOMERORDER:

ORDERLINE:

Reports on SQL Queries in Task 3
Query 1:
SELECT DISTINCT CONCAT(a.Emp_FName,' ',a.Emp_LName) AS NAME, CONCAT(b.Address_St,'
',b.Address_City,' ',b.Address_State,' ',b.Address_Postcode) AS ADDRESS
FROM EMPLOYEE a INNER JOIN ADDRESS b
ON a.Address_ID_Postal = b.Address_ID;
Output table:
Query 2:
SELECT DISTINCT CONCAT(a.Cust_FName,' ',a.Cust_LName) AS NAME, b.CustOrd_Date AS
Date_of_Order
FROM CUSTOMER a INNER JOIN CUSTOMERORDER b
ON a.Cust_Number = b.Cust_Number
ORDER BY b.CustOrd_Date DESC LIMIT 1;
Output table:
Query 3:
SELECT DISTINCT a.Str_Name AS NAME,CONCAT(b.Address_St,' ',b.Address_City,' ',b.Address_State,'
',b.Address_Postcode) AS ADDRESS
FROM STORE a INNER JOIN ADDRESS b
ON a.Address_ID = b.Address_ID
ORDER BY a.Str_Name;
Output table:
Query 4:
SELECT DISTINCT a.Cust_Number AS Customer_Number,CONCAT(a.Cust_FName,' ',a.Cust_LName) AS
NAME
FROM CUSTOMER a
WHERE a.Cust_Number NOT IN (SELECT Cust_Number FROM CUSTOMERORDER);
Output table:
Query 5:
CREATE TABLE FINAL AS
SELECT DISTINCT CONCAT(a.Emp_FName,' ',a.Emp_LName) AS NAME,b.JobType_Name
FROM EMPLOYEE a INNER JOIN JOBTYPE b
ON a.Emp_JobTypeID = b.JobType_ID;
SELECT NAME FROM FINAL WHERE JobType_Name='Accountant';

Output table:
Query 6:
SELECT DISTINCT a.Prod_Num as Product_Number,a.Prod_Desc as Description,b.Inv_QntyOnHand as
Quantity_On_Hand
FROM PRODUCT a INNER JOIN INVENTORY b
ON a.Prod_Num = b.ProductNum;
Output table:
Query 7:
SELECT DISTINCT a.Prod_Num as Product_Number,a.Prod_Desc as Name,b.OrdLn_Qnty as Quantity_Sold
FROM PRODUCT a INNER JOIN ORDERLINE b
ON b.OrdLn_DatePicked <= '2018-05-20' AND a.Prod_Num = b.Prod_Num
ORDER BY a.Prod_Num,b.OrdLn_Qnty;
Output table:
Query 8:
SELECT DISTINCT a.Prod_Num as Product_Number,a.Prod_Desc as Description,a.Prod_Price as Price
FROM PRODUCT a
WHERE a.Prod_Price <= (SELECT AVG(Prod_Price) FROM PRODUCT);
Output table:
Query 9:
UPDATE EMPLOYEE SET Emp_HourlySalary = Emp_HourlySalary*1.075;
SELECT CONCAT(a.Emp_FName,' ',a.Emp_LName) as NAME,a.Emp_HourlySalary as SALARY
FROM EMPLOYEE a;
Output table:
Query 10:
SELECT CONCAT(a.Emp_FName,' ',a.Emp_LName) as NAME,b.Pay_num_of_hours as
Hours_Paid,b.Pay_amount_gross as Amount_Paid
FROM EMPLOYEE a, PAYSLIP b,(SELECT MAX(Pay_Date) AS Recent,Emp_ID FROM PAYSLIP GROUP
BY Emp_ID) AS c
WHERE c.Emp_ID = b.Emp_ID AND a.Emp_ID = b.Emp_ID AND c.Recent = b.Pay_Date;

Output table:
Query 11:
SELECT DISTINCT a.Prod_Desc as Product_Description,a.Prod_Price as PRICE
FROM PRODUCT a INNER JOIN BRAND b
ON a.Prod_BrandID = b.Brand_ID AND b.Brand_Name = 'Armani'
ORDER BY a.Prod_Price;
Output table:
Query 12:
CREATE TABLE TEST AS
SELECT SupvisorID AS Supervisor_ID,Emp_ID AS Employee_ID,CONCAT(Emp_FName, ' ',Emp_LName)
AS Employee_Name
FROM Employee;
SELECT a.Supervisor_ID,CONCAT(b.Emp_FName,' ',b.Emp_LName) AS
Supervisor_Name,a.Employee_ID,a.Employee_Name
FROM TEST a INNER JOIN EMPLOYEE b
ON a.Supervisor_ID = b.Emp_ID
ORDER BY a.Supervisor_ID;
Output table:

Reports on SQL Queries in Task 4
INSERT INTO ADDRESS(Address_St,Address_City,Address_State,Address_Postcode,AddType_ID)
VALUES('11 Fuller Road','Marsden','QLD','4132','R');
INSERT INTO CUSTOMER(Cust_FName,Cust_LName,Cust_Phone,Address_ID)
VALUES ('Daniel','Ortega','0431xxx668','6');
INSERT INTO BRAND(Brand_Name)
VALUES ('Prada');
INSERT INTO PRODUCT(Prod_Desc,Prod_Size,Prod_Price)
VALUES ('Shirts for man','XL','19.00');
UPDATE PRODUCT SET PRODUCT.Prod_BrandID = (SELECT Brand_ID FROM BRAND WHERE
Brand_Name = 'Prada') WHERE Prod_Desc = 'Shirts for man';
INSERT INTO INVENTORY(ProductNum,Str_Num,Inv_QntyOnHand,Inv_QtyOrdered)
VALUES ((SELECT Prod_Num FROM PRODUCT WHERE Prod_Desc = 'Shirts for man'),'3','45','0');
INSERT INTO CUSTOMERORDER(CustOrd_Date,Cust_Number,Str_Num)
VALUES ('2018-09-06',(SELECT Cust_Number FROM CUSTOMER WHERE Cust_Phone =
'0431xxx668'),'3');
INSERT INTO ORDERLINE(CustOrd_ID,Prod_Num,OrdLn_DateArrived,OrdLn_DatePicked,OrdLn_Qnty)
VALUES ((SELECT CustOrd_ID FROM CUSTOMERORDER WHERE Cust_Number = (SELECT
Cust_Number FROM CUSTOMER WHERE Cust_Phone = '0431xxx668')),(SELECT Prod_Num FROM
PRODUCT WHERE Prod_Desc = 'Shirts for man'),'2018-09-08','2018-09-10','2');
Output tables:
ADDRESS:
CUSTOMER:
BRAND:

PRODUCT:
INVENTORY:
CUSTOMERORDER:
ORDERLINE:

Bibliography
No resources apart from course material is used for this assignment.