Database Design ICT211 Part 2

This document provides a complete database design and implementation of Australian Native Plants Ltd. (ANP) including an ER diagram, relational schema, supplementary design requirements, assumptions, create statements, create trigger, create procedure, and insert into statements.

Database Design ICT211
Part 2
Student ID:
Student Name:
6/4/2019

Report
Contents
Report..........................................................................................................................................................2
Introduction.................................................................................................................................................3
Part A..........................................................................................................................................................3
ER Diagram..............................................................................................................................................3
Relational Schema...................................................................................................................................5
Supplementary Design Requirements.....................................................................................................6
Assumptions............................................................................................................................................8
Part B...........................................................................................................................................................9
Create Statements....................................................................................................................................9
Create Trigger........................................................................................................................................14
Create Procedure...................................................................................................................................15
Insert into Statements............................................................................................................................16
Queries..................................................................................................................................................23
Conclusion.................................................................................................................................................26
Reference...................................................................................................................................................27

Introduction
The report is showing the complete database design and implementation of Australian Native
Plants Ltd. (ANP). The database design is being shown by ER diagram while the database
implementation is done in MYSQL database. Different types of queries, stored procedures etc.
all are made in the assignment.
Part A
ER Diagram

(B. Peter n.d.) (Cinergix Pty Ltd. 2011)

Relational Schema

Supplementary Design Requirements
1. TblClient
Attribute Data Type Key Compulsory
fldclientID INTEGER PRIMARY KEY Yes
fldclientName VARCHAR(50) Yes
fldemail VARCHAR(50) Yes
fldlocation VARCHAR(100) Yes
fldstartDate DATETIME Yes
flddeliveryAddress VARCHAR(100) Yes
fldaccountNumber VARCHAR(12) Yes
2. TblMember
Attribute Data Type Key Compulsory
fldmemberID INTEGER PRIMARY KEY Yes
fldmemberName VARCHAR(50) Yes
fldcontactName VARCHAR(50) Yes
fldstartDate DATETIME Yes
fldendDate DATETIME No
fldnurseryName VARCHAR(50) Yes
fldnurseryAddress VARCHAR(100) Yes
fldnurseryPhone VARCHAR(20) Yes
fldnurseryEmail VARCHAR(50) Yes
fldmemberDescription VARCHAR(100) Yes
3. TblPlant
Attribute Data Type Key Compulsory
fldplantID INTEGER PRIMARY KEY Yes
fldbotanicalName VARCHAR(50) Yes
fldcommonName VARCHAR(50) Yes
flddescription VARCHAR(100) Yes
4. TblShippingMultiplier
Attribute Data Type Key Compulsory
fldnurseryState VARCHAR(3) PRIMARY KEY Yes
flddestinationState VARCHAR(3) PRIMARY KEY Yes
fldshippingCostMultiplier Double Yes
5. TblStock

Attribute Data Type Key Compulsory
fldmemberID INTEGER PRIMARY KEY
FOREIGN KEY
REFERENCES
TblMember
(fldmemberID)
Yes
fldplantID INTEGER PRIMARY KEY
FOREIGN KEY
REFERENCES
TblPlant (fldplantID)
Yes
fldpriceDate DATETIME PRIMARY KEY Yes
fldprice DOUBLE Yes
fldunitShoppingCost DOUBLE Yes
fldinStock INTEGER Yes
6. TblOrder
Attribute Data Type Key Compulsory
fldorderID INTEGER PRIMARY KEY Yes
fldmemberID INTEGER FOREIGN KEY
REFERENCES
TblMember
(fldmemberID)
Yes
fldclientID INTEGER FOREIGN KEY
REFERENCES
TblClient
(fldclientID)
Yes
fldorderDate DATETIME Yes
fldstatus VARCHAR(20) Yes
fldshippingDate DATETIME Yes
fldcourierName VARCHAR(50) Yes
fldnurseryState VARCHAR(3) FOREIGN KEY
REFERENCES
TblShippingMultiplier
(fldnurseryState)
Yes
flddestinationState VARCHAR(3) FOREIGN KEY
REFERENCES
TblShippingMultiplier
(flddestinationState)
Yes
fldshippingReferenceNumber VARCHAR(20) Yes
7. TblOrderItem

Attribute Data Type Key Compulsory
fldorderID INTEGER PRIMARY KEY
FOREIGN KEY
REFERENCES
TblOrder
(fldorderID)
Yes
fldplantID INTEGER PRIMARY KEY
FOREIGN KEY
REFERENCES
TblPlant (fldplantID)
Yes
fldcost DOUBLE Yes
fldquantity INTEGER Yes
fldunitShippingcost DOUBLE Yes
8. TblMessage
Attribute Data Type Key Compulsory
fldmessageID INTEGER PRIMARY KEY Yes
fldclientID INTEGER FOREIGN KEY
REFERENCES
TblClient
(fldclientID)
Yes
fldmemberID INTEGER FOREIGN KEY
REFERENCES
TblMember
(fldmemberID)
Yes
fldmessageDate DATETIME Yes
fldmessage VARCHAR(100) Yes
Assumptions
1. There may be number of items in an order.
2. A client can do any number of orders.
3. Stocks of all plants along with members should be stored into the database.
4. Shipping cost multiplier is based upon the source and destinations.
Part B

Create Statements
DROP Database If EXISTS ANPDB;
create database ANPDB;
use ANPDB;
CREATE TABLE IF NOT EXISTS TblClient (
fldclientID INT(11) NOT NULL,
fldclientName VARCHAR(50) NOT NULL,
fldemail VARCHAR(50) NOT NULL,
fldlocation VARCHAR(100) NOT NULL,
fldstartDate datetime NOT NULL,
flddeliveryAddress VARCHAR(100) NOT NULL,
fldaccountNumber varchar(12) NOT NULL,
PRIMARY KEY (fldclientID))
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS TblMember (
fldmemberID INT(11) NOT NULL,
fldmemberName VARCHAR(50) NOT NULL,
fldcontactName VARCHAR(50) NOT NULL,
fldstartDate datetime NOT NULL,
fldendDate datetime NULL,
fldnurseryName VARCHAR(50) NOT NULL,
fldnurseryAddress VARCHAR(100) NOT NULL,

fldnurseryPhone VARCHAR(20) NOT NULL,
fldnurseryEmail VARCHAR(50) NOT NULL,
fldmemberDescription VARCHAR(100) NOT NULL,
PRIMARY KEY (fldmemberID))
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS TblPlant (
fldplantID INT(11) NOT NULL,
fldbotanicalName VARCHAR(50) NOT NULL,
fldcommonName VARCHAR(50) NOT NULL,
flddescription VARCHAR(100) NOT NULL,
PRIMARY KEY (fldplantID))
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS TblShippingMultiplier (
fldnurseryState VARCHAR(3) NOT NULL,
flddestinationState VARCHAR(3) NOT NULL,
fldshippingCostMultiplier double(10,2) NOT NULL,
PRIMARY KEY (fldnurseryState, flddestinationState))
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS TblStock (
fldmemberID INT(11) NOT NULL,
fldplantID INT(11) NOT NULL,
fldpriceDate datetime NOT NULL,

fldprice double(10,2) NOT NULL,
fldunitShoppingCost double(10,2) NOT NULL,
fldinStock int(11) NOT NULL,
PRIMARY KEY (fldmemberID, fldplantID, fldpriceDate),
FOREIGN KEY (fldmemberID)
REFERENCES TblMember(fldmemberID),
FOREIGN KEY (fldplantID)
REFERENCES TblPlant(fldplantID))
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS TblOrder (
fldOrderID INT(11) NOT NULL,
fldmemberID INT(11) NOT NULL,
fldclientID INT(11) NOT NULL,
fldorderDate datetime NOT NULL,
fldstatus varchar(20) NOT NULL,
fldshippingDate datetime NOT NULL,
fldcourierName varchar(50) NOT NULL,
fldnurseryState VARCHAR(3) NOT NULL,
flddestinationState VARCHAR(3) NOT NULL,
fldshippingReferenceNumber VARCHAR(20) NOT NULL,
PRIMARY KEY (fldOrderID),
FOREIGN KEY (fldmemberID)
REFERENCES TblMember(fldmemberID),
FOREIGN KEY (fldclientID)

REFERENCES TblClient(fldclientID),
FOREIGN KEY (fldnurseryState, flddestinationState)
REFERENCES TblShippingMultiplier(fldnurseryState, flddestinationState))
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS TblOrderItem (
fldOrderID INT(11) NOT NULL,
fldplantID INT(11) NOT NULL,
fldcost double(10,2) NOT NULL,
fldquantity INT(11) NOT NULL,
fldunitShippingcost double(10,2) NOT NULL,
PRIMARY KEY (fldOrderID, fldplantID),
FOREIGN KEY (fldOrderID)
REFERENCES TblOrder(fldOrderID),
FOREIGN KEY (fldplantID)
REFERENCES Tblplant(fldplantID))
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS TblMessage (
fldmessageID INT(11) NOT NULL Auto_increment,
fldclientID INT(11) NOT NULL,
fldmemberID INT(11) NOT NULL,
fldmessageDate datetime NOT NULL,
fldmessage varchar(100) NOT NULL,
PRIMARY KEY (fldmessageID),

FOREIGN KEY (fldclientID)
REFERENCES Tblclient(fldclientID),
FOREIGN KEY (fldmemberID)
REFERENCES Tblmember(fldmemberID))
ENGINE = InnoDB;
The above screen is showing the creation of database tables.
Create Trigger

/* Trigger */
DELIMITER $$
CREATE TRIGGER after_order_insert
AFTER INSERT ON TblOrder
FOR EACH ROW
BEGIN
INSERT INTO TblMessage (fldclientID, fldmemberID, fldmessageDate, fldmessage)
values (New.fldclientID, New.fldmemberID, NOW(), 'Order Places');
END$$
DELIMITER ;
The above screen is showing the creation of trigger on order table.

Create Procedure
/* Cost Calculation Procedure */
DELIMITER //
CREATE Procedure OrderCost(p_orderid int)
DETERMINISTIC
BEGIN
select ((fldshippingCostMultiplier* fldquantity*fldcost)+(fldquantity*fldunitShippingCost)) from TblOrder
inner join TblOrderItem on TblOrder.fldorderID=TblOrderItem.fldorderID inner join
TblShippingMultiplier on TblShippingMultiplier.fldnurseryState=TblOrder.fldnurseryState and
TblShippingMultiplier.flddestinationState=TblOrder.flddestinationState where
TblOrder.fldorderID=p_orderid;
END //
DELIMITER ;
The above screen is showing the creation of procedure to calculate order cost.
Insert into Statements

Insert into Plant Table
insert into TblPlant(fldplantID, fldbotanicalName, fldcommonName, flddescription)
values (1,'Eutaxia obovata','eggs and bacon','Native shrub grows in WA'),
(2,'Eremophila glabra','common emu bush','Native bush grows in SA'),
(3,'Acacia floribunda','gossamer wattle','Large native tree grows in Qld, NSW and Vic'),
(4,'Goodenia amplexans','clasping goodenia','Native shrub grows in SA and Vic'),
(5,'Eucalyptus viridis','green mallee','Large native tree grows in Qld, NT and Vic'),
(6,'Grevillea banksii','red silky oak','Large native shrub grows in Qld'),
(7,'Eutaxia diffusa','spreading malleepea','Native shrub grows in SA'),
(8,'Acacia gracilifolia','graceful wattle','Native shrub grows in every state'),
(9,'Tetrathecaciliata','pink bells','Native shrub grows in SA, NT, Tas and Vic'),
(10,'Grevillea pauciflora','Pt Lincoln grevillea','Native shrub grows in SA'),
(11,'Eucalyptus sepulcralis','weeping mallee','Native WA tree');
Insert into Client Table

insert into TblClient(fldclientID,fldclientName,fldemail,fldlocation,fldstartDate,flddeliveryAddress,
fldaccountNumber)
values (1, 'Noah Lee', '[e-mail]','11 Church Road Sydney NSW 5623',STR_TO_DATE("2-11-
2018","%m-%d-%Y"),'32 Smith Rd. Townsville QLD 4112','436257257181'),
(2, 'Eli Smith', '[e-mail]','14 Mountain Street Mount Gambier SA 1287',STR_TO_DATE("6-10-
2018","%m-%d-%Y"),'11 Sea View Ave Port Augusta SA 3452','436257257111'),
(3, 'Methew Black', '[e-mail]','21 Magic St. Bairnsdale VIC 8723',STR_TO_DATE("3-12-
2018","%m-%d-%Y"),'44 Classic St. Ulverstone TAS 5623','436257257102');
Insert into Member Table
insert into TblMember(fldmemberID, fldmemberName, fldcontactName, fldstartDate, fldnurseryName,
fldnurseryAddress, fldnurseryPhone, fldnurseryEmail,fldmemberDescription)
values (1, 'Anna Zikov','Anna Zikov',STR_TO_DATE("4-10-2010","%m-%d-%Y"),'Greenock Australian
Native Nursery','37 Greenock Road Greenock 5360
SA','3453356225','[e-mail]','experience of eggs and bacon'),
(2, 'Brenda Coen','Brenda Coen',STR_TO_DATE("3-12-2011","%m-%d-%Y"),'Kimberly Native Plants','45
Frederick Street Broome WA 6725','3453352341','[e-mail]','experience of bushes'),
(3, 'Jaden Bess','Jaden Bess',STR_TO_DATE("5-09-2009","%m-%d-%Y"),'Native to Kingston','22
Summerleas Road Kingston Tas 7050','3457826225','[e-mail]','experience of silky oak'),
(4, 'Adam Ross','Adam Ross',STR_TO_DATE("4-13-2010","%m-%d-%Y"),'Highland Native plants','34 Lowe
Street Queanbeyan NSW 2620','3721356225','[e-mail]','experience of eggs and bacon'),
(5, 'Susan Gahn','Susan Gahn',STR_TO_DATE("2-10-2010","%m-%d-%Y"),'Australian Capital Native
Plants','13 Sternberg Cresent 2903 ACT','1253356225','[e-mail]','experience of bushes'),
(6, 'Peter Back','Peter Back',STR_TO_DATE("1-10-2015","%m-%d-%Y"),'Dalby native plants','158 Bunya
Highway Dalby Qld 4405','3905456225','[e-mail]','experience of eggs and bacon');

Insert into ShippingMultiplier Table
insert into TblShippingMultiplier (fldnurseryState,flddestinationState,fldshippingCostMultiplier)
values ('NSW','NSW',1),
('NSW','QLD',1.5),
('NSW','NT',1.9),
('NSW','VIC',1.2),
('NSW','SA',1.4),
('NSW','ACT',1),
('NSW','WA',2.4),
('NSW','TAS',2),
('QLD','QLD',1),
('QLD','NT',1.4),
('QLD','VIC',1.9),
('QLD','SA',1.8),
('QLD','ACT',1.5),

('QLD','WA',2.5),
('QLD','TAS',2.1),
('NT','NT',1),
('NT','VIC',2.2),
('NT','SA',1.2),
('NT','ACT',1.9),
('NT','WA',1.5),
('NT','TAS',2.5),
('VIC','VIC',1),
('VIC','SA',1.2),
('VIC','ACT',1.3),
('VIC','WA',2.2),
('VIC','TAS',1.3),
('SA','SA',1),
('SA','ACT',1.5),
('SA','WA',1.6),
('SA','TAS',2.1),
('ACT','ACT',1),
('ACT','WA',2.6),
('ACT','TAS',1.7),
('WA','WA',1),
('WA','TAS',2.4),
('TAS','TAS',1);

Insert into Stock Table
insert into TblStock (fldmemberID, fldplantID, fldpriceDate, fldprice, fldunitShoppingCost, fldinStock)
values (1,1, STR_TO_DATE("4-10-2019","%m-%d-%Y"), 70,100,40),
(1,2, STR_TO_DATE("4-10-2019","%m-%d-%Y"), 50,110,40),
(1,4, STR_TO_DATE("8-11-2019","%m-%d-%Y"), 50,110,40),
(2,3, STR_TO_DATE("4-12-2019","%m-%d-%Y"), 40,112,90),
(2,2, STR_TO_DATE("7-10-2019","%m-%d-%Y"), 55,115,50),
(2,4, STR_TO_DATE("9-11-2019","%m-%d-%Y"), 30,125,50),
(3,1, STR_TO_DATE("4-12-2019","%m-%d-%Y"), 30,135,90),
(3,2, STR_TO_DATE("6-10-2019","%m-%d-%Y"), 30,105,50),
(3,4, STR_TO_DATE("6-11-2019","%m-%d-%Y"), 30,115,40);

Insert into Order Table
insert into TblOrder (fldOrderID, fldmemberID, fldclientID, fldorderDate, fldstatus, fldshippingDate,
fldcourierName, fldnurseryState, flddestinationState, fldshippingReferenceNumber)
values (1,1,1,STR_TO_DATE("8-10-2018","%m-%d-%Y"),'Completed',STR_TO_DATE("6-12-2018","%m-
%d-%Y"),'ABP','QLD','QLD','34726'),
(2,2,1,STR_TO_DATE("1-10-2019","%m-%d-%Y"),'Pending',STR_TO_DATE("1-15-2019","%m-%d-
%Y"),'ABP','NSW','SA','34726'),
(3,3,1,STR_TO_DATE("2-11-2019","%m-%d-%Y"),'Completed',STR_TO_DATE("2-15-2019","%m-%d-
%Y"),'ABP','VIC','ACT','34726'),
(4,2,2,STR_TO_DATE("3-09-2019","%m-%d-%Y"),'Completed',STR_TO_DATE("3-12-2019","%m-%d-
%Y"),'ABP','NSW','TAS','45222'),
(5,3,3,STR_TO_DATE("5-09-2019","%m-%d-%Y"),'Completed',STR_TO_DATE("5-15-2019","%m-%d-
%Y"),'ABP','SA','SA','53783');

Insert into OrderItem Table
insert into TblOrderItem (fldOrderID, fldplantID, fldcost, fldquantity, fldunitShippingcost)
values (1, 1,65,2,130),
(1, 2,60,1,130),
(1, 3,40,1,130),
(2,1,40,1,113),
(3,3,40,2,124),
(4,1,55,2,123),
(5,2,40,1,123);

Queries
Query 1
DELIMITER //
create procedure clientOrder(pOrderid int)
begin
select TblClient.fldclientID, fldaccountNumber, TblOrder.fldorderID, ((fldshippingCostMultiplier*
fldquantity*fldcost)+(fldquantity*fldunitShippingCost)) as TotalAmount, fldorderDate from TblOrder
inner join TblOrderItem on TblOrder.fldorderID=TblOrderItem.fldorderID inner join
TblShippingMultiplier on TblShippingMultiplier.fldnurseryState=TblOrder.fldnurseryState and
TblShippingMultiplier.flddestinationState=TblOrder.flddestinationState inner join TblClient on
TblClient.fldclientID=TblOrder.fldclientID where TblOrder.fldorderID=pOrderid;
end //
DELIMITER ;
CALL clientOrder(1);
The above screen is showing the details of orders for order 1.

Query 2
DELIMITER //
create procedure itemCost(p_orderid int)
begin
select fldbotanicalName,fldquantity, fldcost, TblOrder.fldorderID, ((fldshippingCostMultiplier*
fldquantity*fldcost)+(fldquantity*fldunitShippingCost)) as TotalAmount from TblOrder inner join
TblOrderItem
on TblOrder.fldorderID=TblOrderItem.fldorderID inner join TblShippingMultiplier on
TblShippingMultiplier.fldnurseryState=TblOrder.fldnurseryState
and TblShippingMultiplier.flddestinationState=TblOrder.flddestinationState
inner join TblPlant on TblOrderItem.fldplantID=TblPlant.fldplantID where TblOrder.fldorderID=1;
end //
DELIMITER ;
CALL itemCost(1);
The above screen is showing the details of order items of order number 1.

Query 3
DELIMITER //
create procedure OrdersbyStatus()
begin
select Tblmember.fldmemberID, Tblmember.fldmemberName, Tblorder.fldorderID, fldorderDate,
fldnurseryName, fldnurseryAddress, fldstatus
from TblOrder inner join Tblmember on Tblorder.fldmemberID=Tblmember.fldmemberID where
fldstatus='Completed';
end //
DELIMITER ;
Call OrdersbyStatus();
The above screen is showing the details of members and their orders.
Query 4
DELIMITER //
create procedure nurseryOrders()
begin

select fldnurseryName, fldnurseryAddress, count(Tblorder.fldorderID) as NumofOrder,
((fldshippingCostMultiplier* fldquantity*fldcost)+(fldquantity*fldunitShippingCost)) as TotalAmount,
TblOrder.fldStatus
from TblOrder inner join TblOrderItem
on TblOrder.fldorderID=TblOrderItem.fldorderID inner join TblShippingMultiplier on
TblShippingMultiplier.fldnurseryState=TblOrder.fldnurseryState
and TblShippingMultiplier.flddestinationState=TblOrder.flddestinationState
inner join Tblmember on Tblorder.fldmemberID=Tblmember.fldmemberID
group by fldnurseryName, fldnurseryAddress;
end //
DELIMITER ;
Call nurseryOrders();
The above screen is showing the details of nurseries and its orders..
Conclusion
The report is showing the database design and development from scratch. It is showing the entire
database of ANP. After studying the report, any user can easily understand the database of ANP.

Reference
B. Peter n.d., SQL SERVER Database Normalization Basics for Developers, [Online].
Available: http://www.nullskull.com/a/1629/sql-server-database-normalization-basics-for-
developers.aspx. [Accessed: 4-June-2019]
Cinergix Pty Ltd. 2011, Ultimate Guide to ER Diagrams, [Online].
Available:http://creately.com/blog/diagrams/er-diagrams-tutorial/. [Accessed: 4-June-2019]