ICT701: Relational Database Systems Task 2 Solution - Semester 1, 2019

This assignment solution presents a complete database design for the "TufftoysDB" database, including the creation of tables such as Customer, Item, DeliveryMethod, SalesRep, Order, OrderItems, Invoice, and Payment. The solution defines the schema with appropriate data types, constraints, and primary/foreign key relationships. It includes insert statements to populate the tables with sample data. Furthermore, it incorporates SQL functions and triggers to automate database operations. Specifically, a function `total_order_item` is created to calculate the total cost of an order item, and triggers are implemented to update the item quantity and customer balance after order insertion and payment insertion. Finally, the solution provides SQL queries to retrieve specific data from the database, such as items not included in any order and the total amount for each order, demonstrating a comprehensive understanding of relational database concepts and SQL.

Script
Drop database if exists TufftoysDB;
create database TufftoysDB;
Use TufftoysDB;
/* Create Database */
DROP TABLE IF EXISTS Customer;
CREATE TABLE Customer (
customerNumber int not null,
customerName varchar(40) NOT NULL,
phone varchar(20) NOT NULL,
email varchar(30) NOT NULL,
address varchar(50) NOT NULL,
balance double NOT NULL,
creditLimit int NOT NULL,
Primary Key (customerNumber)
);
DROP TABLE IF EXISTS Item;
CREATE TABLE Item (

itemCode int not null,
description varchar(40) NOT NULL,
category varchar(20) NOT NULL,
suggestedRetail varchar(30) NOT NULL,
cost varchar(50) NOT NULL,
overseas double NOT NULL,
QOH int NOT NULL,
minStockLevel int NOT NULL,
Primary Key (itemCode)
);
DROP TABLE IF EXISTS DeliveryMethod;
CREATE TABLE DeliveryMethod (
deliveryMethod varchar(20) not null,
charge double NOT NULL,
Primary Key (deliveryMethod)
);
DROP TABLE IF EXISTS SalesRep;
CREATE TABLE SalesRep (
salesRepID int not null,
salesRepName varchar(40) NOT NULL,
address varchar(50) NOT NULL,

mobile varchar(20) NOT NULL,
email varchar(30) NOT NULL,
hiredDate datetime NOT NULL,
agreedSalary double NOT NULL,
commissionRate int NOT NULL,
Primary Key (salesRepID)
);
DROP TABLE IF EXISTS `Order`;
CREATE TABLE `Order` (
orderNumber int not null,
salesRepID int NOT NULL,
orderDate datetime NOT NULL,
requiredDate datetime NOT NULL,
customerNumber int not null,
deliveryMethod varchar(20) not null,
Primary Key (orderNumber),
Constraint fk_customer foreign key (customerNumber) references customer (customerNumber),
Constraint fk_delMethod foreign key (deliveryMethod) references DeliveryMethod (deliveryMethod)
);
DROP TABLE IF EXISTS OrderItems;
CREATE TABLE OrderItems (

orderNumber int not null,
itemCode int NOT NULL,
quantity int NOT NULL,
Primary Key (orderNumber, itemCode),
Constraint fk_order foreign key (orderNumber) references `Order` (orderNumber),
Constraint fk_item foreign key (itemCode) references Item (itemCode)
);
DROP TABLE IF EXISTS Invoice;
CREATE TABLE Invoice (
invoiceNumber int not null,
orderNumber int not null,
deliveryCharge double NOT NULL,
totalAmount double NOT NULL,
Primary Key (invoiceNumber),
Constraint fk_ord foreign key (orderNumber) references `Order` (orderNumber)
);
DROP TABLE IF EXISTS Payment;
CREATE TABLE Payment (
paymentID int not null,
invoiceNumber int not null,
customerNumber int not null,

paymentDate datetime NOT NULL,
amount double NOT NULL,
Primary Key (paymentID),
Constraint fk_invoice foreign key (invoiceNumber) references Invoice (invoiceNumber),
Constraint fk_cust foreign key (customerNumber) references Customer (customerNumber)
);
/* Insert Statements */
insert into Customer (customerNumber, customerName, phone, email, address, balance, creditLimit)
values (10225, 'Cottage Capers', '4538723984','[e-mail]', '12 Linkfield Rd, Mango Hill 4509',
641.5, 7000),
(11331, 'Tanias Toys', '8763284674','[e-mail]', '8 Avondale Street, Brisbane 4000', 199.4, 5000),
(12009, 'Kmart Flagstone', '7648468747','[e-mail]', '43 Dale Avenue, Flagstone 4280', 1095,
15000),
(12993, 'McCains Dept Store', '6237674374','[e-mail]', '28 Lara Street, South Brisbane 4101',
0, 10000),
(13810, 'Magic Land', '4372648277','[e-mail]', '18 Waller Road, Ormeau 4208', 755, 8000);
insert into Item (itemCode, description, category, suggestedRetail, cost, overseas, QOH, minStockLevel)
values (104, 'Patton War Kit', 'Pretend', 10.5, 8, 1, 22, 10),
(107, 'Our Generation Doll', 'Dolls', 31.5, 19, 1, 15, 10),
(110, 'Scrabble', 'Puzzles and games', 29, 15, 0, 10, 5),
(126, 'Super Hero costume bundle', 'Pretend', 15.95, 7.5, 0, 30, 15),
(133, 'Baby Alive - Judy', 'Dolls', 22, 17, 0, 26, 8),
(142, 'Trail Chopper', 'Remote control', 25.99, 16, 0, 17, 10),
(177, 'Giant Teddy Bear, Red and Brown', 'Soft toys', 50, 33, 0, 8, 5),

(180, 'Dance mat', 'Electronic', 75, 40, 1, 13, 15),
(212, 'Blue balance bike, 40inch', 'Bikes and Ride ons', 19.99, 9, 0, 10, 10),
(231, 'Pink Tea Set (20 piece)', 'Pretend', 15, 7, 1, 18, 10);
insert into DeliveryMethod (deliveryMethod, charge)
values ('Air Mail', 55),
('Regular Mail', 15),
('Courier', 25.5),
('Pickup', 0);
insert into SalesRep (salesRepID, salesRepName, address, mobile, email, hiredDate, agreedSalary,
commissionRate)
values (366542, 'John Jones', '12 Linkfield Rd, Mango Hill 4509','3467825671', '[e-mail]', '2015-
01-29', 45000,7),
(229844, 'Mark Jacobs', '8 Avondale Street, Brisbane 4000', '3467823371', '[e-mail]', '2016-03-
20', 41000,6),
(340900, 'Mary Mee', '43 Dale Avenue, Flagstone 4280','3467673371', '[e-mail]', '2018-06-15',
49000,3),
(229109, 'Jane Smith', '28 Lara Street, South Brisbane 4101','3467823311', '[e-mail]', '2017-10-
16', 44000,4),
(210567, 'Steve Black', '18 Waller Road, Ormeau 4208', '3467823372', '[e-mail]', '2019-01-07',
47000,3);
insert into `Order` (orderNumber, salesRepID, orderDate, requiredDate, customerNumber,
deliveryMethod)
values (15209, 229844, '2019-02-01', '2019-02-05', 10225, 'Courier'),
(15208, 366542, '2019-02-11','2019-04-12', 10225, 'Regular Mail'),
(15207, 340900, '2019-03-13', '2019-05-14', 10225, 'Pickup'),

(15206, 210567, '2019-05-16', '2019-07-16', 10225, 'Air Mail');
insert into OrderItems (orderNumber, itemCode, quantity)
values (15209, 104, 10),
(15209, 142, 30),
(15209, 231, 20),
(15208, 107, 5),
(15208, 126, 10),
(15207, 212, 1),
(15207, 231, 5),
(15206, 180, 10),
(15206, 177, 2);
insert into Invoice (invoiceNumber, deliveryCharge, totalAmount, orderNumber)
values (17211, 25.50, 651.50, 15209);
insert into Payment (paymentID, invoiceNumber, customerNumber, paymentDate, amount)
values (1, 17211, 10225, '2019-02-06', 100),
(2, 17211, 10225, '2019-02-10', 200),
(3, 17211, 10225, '2019-02-15', 100),
(4, 17211, 10225, '2019-02-25', 200.50);
/* Queries */
/* function */

DELIMITER //
CREATE FUNCTION total_order_item(qty int, price double, overseas double) RETURNS double
DETERMINISTIC
BEGIN
RETURN ((qty*price)+((price*10)/100)+overseas);
END
//
DELIMITER ;
/* Trigger */
DELIMITER $$
CREATE TRIGGER after_order_insert
AFTER INSERT ON OrderItems
FOR EACH ROW
BEGIN
DECLARE price double;
DECLARE overseasValue double;
select Cost into price from Item where itemCode=(New.itemCode);
select overseas into overseasValue from Item where itemCode=(New.itemCode);
update item set QOH=QOH-New.quantity where orderNumber=New.orderNumber;

update customer set balance=balance-total_order_item(quantity, price, overseasValue);
END$$
DELIMITER ;
/* Trigger */
DELIMITER $$
CREATE TRIGGER after_payment_insert
AFTER INSERT ON payment
FOR EACH ROW
BEGIN
update customer set balance=balance+ New.amount;
END$$
DELIMITER ;
/* Query 1 */
select itemCode, description, category from Item where itemCode not in (select itemCode from
OrderItems);

/* Query 2 */
select `order`.orderNumber, orderItems.itemCode, cost, overseas, quantity,
(total_order_item(quantity,cost, overseas) + invoice.deliveryCharge) as TotalAmount
from `order` inner join orderItems on `order`.orderNumber=orderItems.orderNumber
inner join item on orderItems.itemCode=item.itemCode
inner join invoice on invoice.orderNumber=`order`.orderNumber;