Database Management System

This document provides a comprehensive study material on Database Management System. It includes an entity relationship diagram, relational schema, data attribute information, create table statements, create trigger statements, stored procedure, insert data statements, and more. The document also includes assumptions made during the database design process.

Running head: DATABASE MANAGEMENT SYSTEM
Database Management System
Name of the Student
Name of the University
Author’s note:

1DATABASE MANAGEMENT SYSTEM
Table of Contents
Part A:..............................................................................................................................................2
Entity Relationship Diagram:......................................................................................................2
Relational Schema:......................................................................................................................2
Data Attribute Information:.........................................................................................................5
Part B:..............................................................................................................................................8
Create Table:................................................................................................................................8
Create Trigger:...........................................................................................................................15
Stored Procedure:.......................................................................................................................15
Insert Data:.................................................................................................................................16
Select Statement:.......................................................................................................................22
Bibliography:.................................................................................................................................24

2DATABASE MANAGEMENT SYSTEM
Part A:
Entity Relationship Diagram:
Relational Schema:
Client Table:

3DATABASE MANAGEMENT SYSTEM
Member Table:
Plant Table:
Order Table:

4DATABASE MANAGEMENT SYSTEM
Message Table:
MemberStock Table:
PlanOrder Table:

5DATABASE MANAGEMENT SYSTEM
Data Attribute Information:
Table: client
Attribute Data Type Length Primary Key Foreign Key
clientId int 11 Yes
firstName varchar 150
lastName varchar 150
emailAddress varchar 150
location varchar 150
startDate DATE
deliveryAddress varchar 150
Table: member
Attribute Data Type Length Primary Key Foreign Key
membeIid int 11 Yes
memberName varchar 150
contactName varchar 150
startDate DATE
endDate DATE

6DATABASE MANAGEMENT SYSTEM
startDate DATE
nurseryName varchar 150
nurseryAddress varchar 150
phone varchar 150
email varchar 150
description varchar 150
state varchar 150
Table: memberstock
Attribute Data Type Length Primary Key Foreign Key
plantId int 11 Yes Yes
memberid int 11 Yes Yes
price decimal 7,2
priceDate DATE
unitShippingCost decimal 7,2
inStock int 11
Table: message
Attribute Data Type Length Primary Key Foreign Key
message_id int 11 Yes
clientId int 11 Yes
membeIid int 11 Yes

7DATABASE MANAGEMENT SYSTEM
message_date DATE
message varchar 150
Table: orders
Attribute Data Type Length Primary Key Foreign Key
orderId int 11 Yes
clientId int 11
membeIid int 11
orderDate DATE
orderStatus varchar 150
shippingDate DATE
courierName varchar 150
shippingCostMultiplier decimal 10,1
referenceNumber int 11
note varchar 150
Assumptions: During the database design it was assumed that unit shipping charge will
be in the member stock table only. It is because, putting the same information into two separate
tables is not right database development practice. It was also assumed that message will be create
based on the note a client puts while submitting the order. It is because, there is not specific
mention of how message is captured from client.

8DATABASE MANAGEMENT SYSTEM
Part B:
Create Table:
CREATE TABLE client (
clientId int(11) NOT NULL,
firstName varchar(150) NOT NULL,
lastName varchar(150) NOT NULL,
emailAddress varchar(150) NOT NULL,
location varchar(150) NOT NULL,
startDate date NOT NULL,
deliveryAddress varchar(150) NOT NULL
);
CREATE TABLE member (
membeIid int(11) NOT NULL,
memberName varchar(150) NOT NULL,
contactName varchar(150) NOT NULL,
startDate date NOT NULL,
endDate date DEFAULT NULL,
nurseryName varchar(150) NOT NULL,
nurseryAddress varchar(150) NOT NULL,

9DATABASE MANAGEMENT SYSTEM
phone varchar(150) NOT NULL,
email varchar(150) NOT NULL,
description varchar(150) NOT NULL,
state varchar(150) NOT NULL
);
CREATE TABLE memberstock (
plantId int(11) NOT NULL,
memberid int(11) NOT NULL,
price decimal(7,2) NOT NULL,
priceDate date NOT NULL,
unitShippingCost decimal(7,2) NOT NULL,
inStock int(11) NOT NULL
);
CREATE TABLE message (
message_id int(11) NOT NULL,
clientId int(11) NOT NULL,
membeIid int(11) NOT NULL,
message_date date NOT NULL,

10DATABASE MANAGEMENT SYSTEM
message varchar(250) NOT NULL
);
CREATE TABLE orders (
orderId int(11) NOT NULL,
clientId int(11) NOT NULL,
membeIid int(11) NOT NULL,
orderDate date NOT NULL,
orderStatus varchar(150) NOT NULL,
shippingDate date NOT NULL,
courierName varchar(150) NOT NULL,
shippingCostMultiplier decimal(10,1) NOT NULL,
referenceNumber int(11) NOT NULL,
note varchar(150) NOT NULL
);
CREATE TABLE plant (
plantId int(11) NOT NULL,
botanicalName varchar(150) NOT NULL,
commonName varchar(150) NOT NULL,

11DATABASE MANAGEMENT SYSTEM
description varchar(150) NOT NULL
);
CREATE TABLE plantorders (
plantId int(11) NOT NULL,
orderId int(11) NOT NULL,
quantity int(11) NOT NULL,
itemCost decimal(7,2) DEFAULT NULL
);
CREATE TABLE shippingcostmultiplier (
multiplierid int(11) NOT NULL,
APNNurseryState varchar(150) NOT NULL,
DestinationClientState varchar(150) NOT NULL,
shippingCostMultiplier decimal(7,2) NOT NULL
);
ALTER TABLE client
ADD PRIMARY KEY (clientId);
ALTER TABLE member

12DATABASE MANAGEMENT SYSTEM
ADD PRIMARY KEY (membeIid);
ALTER TABLE memberstock
ADD PRIMARY KEY (plantId,memberid),
ADD KEY membeIid (memberid);
ALTER TABLE message
ADD PRIMARY KEY (message_id),
ADD KEY clientId (clientId),
ADD KEY membeIid (membeIid);
ALTER TABLE orders
ADD PRIMARY KEY (orderId),
ADD KEY clientId (clientId),
ADD KEY membeIid (membeIid);
ALTER TABLE plant
ADD PRIMARY KEY (plantId);

13DATABASE MANAGEMENT SYSTEM
ALTER TABLE plantorders
ADD PRIMARY KEY (plantId,orderId),
ADD KEY orderId (orderId);
ALTER TABLE shippingcostmultiplier
ADD PRIMARY KEY (multiplierid);
ALTER TABLE message
MODIFY message_id int(11) NOT NULL AUTO_INCREMENT,
AUTO_INCREMENT=7;
ALTER TABLE shippingcostmultiplier
MODIFY multiplierid int(11) NOT NULL AUTO_INCREMENT,
AUTO_INCREMENT=37;
ALTER TABLE memberstock
ADD CONSTRAINT memberstock_ibfk_1 FOREIGN KEY (plantId) REFERENCES
plant (plantId),

14DATABASE MANAGEMENT SYSTEM
ADD CONSTRAINT memberstock_ibfk_2 FOREIGN KEY (memberid)
REFERENCES member (membeIid);
ALTER TABLE message
ADD CONSTRAINT message_ibfk_1 FOREIGN KEY (clientId) REFERENCES client
(clientId),
ADD CONSTRAINT message_ibfk_2 FOREIGN KEY (membeIid) REFERENCES
member (membeIid);
ALTER TABLE orders
ADD CONSTRAINT orders_ibfk_1 FOREIGN KEY (clientId) REFERENCES client
(clientId),
ADD CONSTRAINT orders_ibfk_2 FOREIGN KEY (membeIid) REFERENCES
member (membeIid);
ALTER TABLE plantorders
ADD CONSTRAINT plantorders_ibfk_1 FOREIGN KEY (plantId) REFERENCES
plant (plantId),
ADD CONSTRAINT plantorders_ibfk_2 FOREIGN KEY (orderId) REFERENCES
orders (orderId);

15DATABASE MANAGEMENT SYSTEM
COMMIT;
Create Trigger:
DELIMITER $$
CREATE TRIGGER `insert_message` AFTER INSERT ON `orders` FOR EACH ROW
BEGIN
INSERT INTO `message` (clientId, membeIid, message_date, message)
VALUES (NEW.clientId, NEW.membeIid, NEW.orderDate, NEW.note);
END
$$
DELIMITER ;
Stored Procedure:
DELIMITER $$
--
-- Procedures
--
CREATE DEFINER=`root`@`localhost` PROCEDURE `itemCostCalculation` (IN
`custAdd` VARCHAR(150), IN `memAdd` VARCHAR(150), IN `pNo` INT, IN `mNo` INT, IN
`oNo` INT) BEGIN
DECLARE shipCost, itemCost, shipCharge, cost decimal(7,2);
DECLARE requestedAmount INT;

16DATABASE MANAGEMENT SYSTEM
select shippingCostMultiplier INTO shipCost from shippingcostmultiplier WHERE
APNNurseryState = memAdd AND DestinationClientState = custAdd;
select price INTO itemCost from MemberStock where plantId = pNo AND memberid =
mNo;
select unitShippingCost INTO shipCharge from MemberStock where plantId = pNo
AND memberid = mNo;
select quantity INTO requestedAmount from Plantorders where orderId = oNo AND
plantId = pNo;
SET cost = (shipCost*(requestedAmount*itemCost))+(requestedAmount*shipCharge);
UPDATE plantorders Set plantorders.itemCost = cost where plantorders.plantId = pNo
AND plantorders.orderId = oNo;
END$$
DELIMITER ;
Insert Data:
INSERT INTO `client` (`clientId`, `firstName`, `lastName`, `emailAddress`, `location`,
`startDate`, `deliveryAddress`) VALUES
(1, 'Layla', 'Moorhouse', '[e-mail]', 'QLD', '2015-06-05', '78
Davidson Street'),
(2, 'Audrey', 'Flick', '[e-mail]', 'WA', '2014-11-26', '63 Seaview Court'),

17DATABASE MANAGEMENT SYSTEM
(3, 'Isabelle', 'Bouton', '[e-mail]', 'TAS', '2018-01-05', '34 Carnegie
Avenue'),
(4, 'Benjamin', 'Mackintosh', '[e-mail]', 'SA', '2015-10-17', '57
Sunset Drive');
INSERT INTO `member` (`membeIid`, `memberName`, `contactName`, `startDate`,
`endDate`, `nurseryName`, `nurseryAddress`, `phone`, `email`, `description`, `state`) VALUES
(1, 'Anna Zikov', 'Brodie McCaughey', '2016-11-21', '2018-10-01', 'Greenock Australian
Native Nursery', '37 Greenock Road Greenock 5360 SA', '[phone]',
'[e-mail]', 'One of the best common emu bush is
available', 'SA'),
(2, 'Brenda Coen', 'Jeremy Farrow', '2017-03-14', '2019-03-01', 'Kimberly Native Plants',
'45 Frederick Street Broome WA 6725', '[phone]', '[e-mail]',
'eggs and bacon are famous', 'WA'),
(3, 'Jaden Bess', 'Alice Mandalis', '2012-11-23', NULL, 'Native to Kingston', '22
Summerleas Road Kingston Tas 7050', '[phone]', '[e-mail]',
'pink bells are very popular', 'TAS'),
(4, 'Adam Ross', 'Jackson Doolan', '2015-08-12', '2019-02-04', 'Highland Native plants',
'34 Lowe Street Queanbeyan NSW 2620', '[phone]',
'[e-mail]', 'Well known for gossamer wattle', 'NSW'),
(5, 'Susan Gahn', 'Charli Jordan', '2013-02-13', NULL, 'Australian Capital Native Plants',
'13 Sternberg Cresent 2903 ACT', '[phone]',

18DATABASE MANAGEMENT SYSTEM
'[e-mail]', 'Australian capital native plants are its
specialization', 'ACT'),
(6, 'Peter Back', 'Henry Rigby', '2015-11-13', '2016-04-24', 'Dalby native plants', '158
Bunya Highway Dalby Qld 4405', '[phone]', '[e-mail]', 'Well
known for gossamer wattle and red silky oak', 'QLD');
INSERT INTO `orders` (`orderId`, `clientId`, `membeIid`, `orderDate`, `orderStatus`,
`shippingDate`, `courierName`, `shippingCostMultiplier`, `referenceNumber`, `note`) VALUES
(1, 1, 4, '2019-04-13', 'Delivered', '2019-05-08', 'PLANTS IN TRANSIT PTY LTD', '1.5',
122343, 'Which fertilizer will be the best for ordered plants'),
(2, 2, 2, '2019-01-08', 'Delivered', '2019-01-20', 'PLANTS IN TRANSIT PTY LTD', '1.6',
1234, 'How to maintain the soil'),
(3, 3, 6, '2019-05-18', 'Delivered', '2019-05-26', 'PLANTS IN TRANSIT PTY LTD', '2.1',
9876, 'What type of weather is best for trees');
INSERT INTO `plant` (`plantId`, `botanicalName`, `commonName`, `description`)
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'),

19DATABASE MANAGEMENT SYSTEM
(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 mallee-\r\npea', 'Native shrub grows in SA'),
(8, 'Acacia gracilifolia', 'graceful wattle', 'Native shrub grows in every state'),
(9, 'Tetratheca ciliata', '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 `memberstock` (`plantId`, `memberid`, `price`, `priceDate`,
`unitShippingCost`, `inStock`) VALUES
(1, 2, '34.50', '2017-06-24', '150.00', 24),
(2, 1, '45.00', '2017-03-14', '150.00', 12),
(3, 4, '56.50', '2017-02-12', '150.00', 9),
(6, 6, '87.00', '2017-03-14', '150.00', 12),
(8, 4, '53.00', '2016-06-03', '150.00', 50),
(10, 1, '23.50', '2017-01-24', '150.00', 24);
INSERT INTO `plantorders` (`plantId`, `orderId`, `quantity`, `itemCost`) VALUES
(3, 1, 1, '234.75'),
(8, 1, 1, '229.50');

20DATABASE MANAGEMENT SYSTEM
INSERT INTO `message` (`message_id`, `clientId`, `membeIid`, `message_date`,
`message`) VALUES
(4, 1, 4, '2019-04-13', 'Which fertilizer will be the best for ordered plants'),
(5, 2, 2, '2019-01-08', 'How to maintain the soil'),
(6, 3, 6, '2019-05-18', 'What type of weather is best for trees');
INSERT INTO `shippingcostmultiplier` (`multiplierid`, `APNNurseryState`,
`DestinationClientState`, `shippingCostMultiplier`) VALUES
(1, 'NSW', 'NSW', '1.00'),
(2, 'NSW', 'QLD', '1.50'),
(3, 'NSW', 'NT', '1.90'),
(4, 'NSW', 'VIC', '1.20'),
(5, 'NSW', 'SA', '1.40'),
(6, 'NSW', 'ACT', '1.00'),
(7, 'NSW', 'WA', '2.40'),
(8, 'NSW', 'TAS', '2.00'),
(9, 'QLD', 'QLD', '1.00'),
(10, 'QLD', 'NT', '1.40'),
(11, 'QLD', 'VIC', '1.90'),
(12, 'QLD', 'SA', '1.80'),

21DATABASE MANAGEMENT SYSTEM
(13, 'QLD', 'ACT', '1.50'),
(14, 'QLD', 'WA', '2.50'),
(15, 'QLD', 'TAS', '2.10'),
(16, 'NT', 'NT', '1.00'),
(17, 'NT', 'VIC', '2.20'),
(18, 'NT', 'SA', '1.20'),
(19, 'NT', 'ACT', '1.90'),
(20, 'NT', 'WA', '1.50'),
(21, 'NT', 'TAS', '2.50'),
(22, 'VIC', 'VIC', '1.00'),
(23, 'VIC', 'SA', '1.20'),
(24, 'VIC', 'ACT', '1.30'),
(25, 'VIC', 'WA', '2.20'),
(26, 'VIC', 'TAS', '1.30'),
(27, 'SA', 'SA', '1.00'),
(28, 'SA', 'ACT', '1.50'),
(29, 'SA', 'WA', '1.60'),
(30, 'SA', 'TAS', '2.10'),

22DATABASE MANAGEMENT SYSTEM
(31, 'ACT', 'ACT', '1.00'),
(32, 'ACT', 'WA', '2.60'),
(33, 'ACT', 'TAS', '1.70'),
(34, 'WA', 'WA', '1.00'),
(35, 'WA', 'TAS', '2.40'),
(36, 'TAS', 'TAS', '1.00');
Select Statement:
select CONCAT(client.firstName, ' ', client.lastName) AS `Client Name`, orders.orderId,
orders.orderDate, plant.botanicalName, plantorders.quantity, memberstock.price,
memberstock.unitShippingCost, plantorders.itemCost
From client
inner join orders ON client.clientId = orders.clientId
inner join plantorders ON orders.orderId = plantorders.orderId
inner join plant ON plantorders.plantId= plant.plantId
inner join memberstock ON plant.plantId = memberstock.plantId
select orders.orderId, orders.orderStatus, member.nurseryName, member.state,
COUNT(orders.orderId) FROM orders
Inner join member ON orders.membeIid = member.membeIid

23DATABASE MANAGEMENT SYSTEM
Group By member.nurseryName

24DATABASE MANAGEMENT SYSTEM
Bibliography:
Connolly, T. and Begg, C., 2015. Database Systems. Pearson Education UK.
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.