Database Management System: Design, Implementation, and Queries

This document presents a complete solution to a database management system assignment. It begins with an Entity Relationship Diagram (ERD) and relational schema, defining tables for clients, members, plants, orders, messages, member stocks, and plan orders. Data attribute information is provided for each table, specifying data types and lengths. Part B focuses on SQL implementation, including CREATE TABLE statements for each table, and ALTER TABLE statements to define primary and foreign keys. The assignment includes a CREATE TRIGGER statement to insert messages based on order notes and a stored procedure for item cost calculations. Data insertion is demonstrated with INSERT statements, and SELECT statements are used to query and retrieve data from the database. The document concludes with a bibliography of relevant database systems resources. The database design covers various aspects of database management, including table creation, relationships, and data manipulation using SQL.

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