ATMC Semester 1 ICT211: Database Design for Australian Native Plants

This report details the database design and implementation for Australian Native Plants Ltd. (ANP), featuring an ER diagram for the database design and MySQL implementation. The report includes relational schema, supplementary design requirements, and assumptions made during the design process. Part B of the report provides SQL create statements for tables, a trigger for the TblOrder table, a stored procedure for order cost calculation, and insert statements for populating the tables with data. Finally, the report includes several queries to retrieve specific information from the database, such as client orders, item costs, orders by status, and nursery orders. The conclusion summarizes the comprehensive database design and its easy understandability.

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