ATMC ICT211 Database Design Task 2: Report on ER Diagram and Schema

This report presents a comprehensive database design, including an Entity-Relationship (ER) diagram, a relational schema, and supplementary design requirements. The report details various tables such as Client, Member, Plant, ShippingMultiplier, Stock, Order, OrderItem, and Message, outlining their attributes, data types, keys, and compulsory fields. Assumptions are made regarding order quantities, client orders, and plant stock management. The report references sources like Smartdraw and Edugrabs.com, providing a structured approach to database design for the ICT211 course, focusing on the relationships and structure of the data within the database.

Student ID:
Student Name:

Report
ER Diagram
(Smartdraw 2016)

Relational Schema and Supplementary Design Requirements
1. TableClient
Attribute Data Type Key Compulsory
fieldclientID INTEGER PRIMARY KEY Yes
fieldclientName VARCHAR(40) Yes
fieldemail VARCHAR(30) Yes
fieldlocation VARCHAR(50) Yes
fieldstartDate DATETIME Yes
fielddeliveryAddress VARCHAR(50) Yes
fieldaccountNumber VARCHAR(12) Yes
2. TableMember
Attribute Data Type Key Compulsory
fieldmemberID INTEGER PRIMARY KEY Yes
fieldmemberName VARCHAR(40) Yes
fieldcontactName VARCHAR(40) Yes
fieldstartDate DATETIME Yes
fieldendDate DATETIME No
fieldnurseryName VARCHAR(50) Yes
fieldnurseryAddress VARCHAR(50) Yes
fieldnurseryPhone VARCHAR(20) Yes
fieldnurseryEmail VARCHAR(30) Yes
fieldmemberDescription VARCHAR(100) Yes
3. TablePlant
Attribute Data Type Key Compulsory
fieldplantID INTEGER PRIMARY KEY Yes
fieldbotanicalName VARCHAR(30) Yes
fieldcommonName VARCHAR(30) Yes
fielddescription VARCHAR(50) Yes
4. TableShippingMultiplier
Attribute Data Type Key Compulsory
fieldnurseryState VARCHAR(3) PRIMARY KEY Yes
fielddestinationState VARCHAR(3) PRIMARY KEY Yes
fieldshippingCostMultiplie
r
Double Yes

5. TableStock
Attribute Data Type Key Compulsory
fieldmemberID INTEGER PRIMARY KEY
FOREIGN KEY
REFERENCES
TableMember
(fieldmemberID)
Yes
fieldplantID INTEGER PRIMARY KEY
FOREIGN KEY
REFERENCES
TablePlant
(fieldplantID)
Yes
fieldpriceDate DATETIME PRIMARY KEY Yes
fieldprice DOUBLE Yes
fieldunitShoppingCost DOUBLE Yes
fieldinStock INTEGER Yes
6. TableOrder
Attribute Data Type Key Compulsory
fieldorderID INTEGER PRIMARY KEY Yes
fieldmemberID INTEGER FOREIGN KEY
REFERENCES
TableMember
(fieldmemberID)
Yes
fieldclientID INTEGER FOREIGN KEY
REFERENCES
TableClient
(fieldclientID)
Yes
fieldorderDate DATETIME Yes
fieldstatus VARCHAR(20) Yes
fieldshippingDate DATETIME Yes
fieldcourierName VARCHAR(30) Yes
fieldnurseryState VARCHAR(3) FOREIGN KEY
REFERENCES
TableShippingMultiplier
(fieldnurseryState)
Yes
fielddestinationState VARCHAR(3) FOREIGN KEY
REFERENCES
TableShippingMultiplier
(fielddestinationState)
Yes

fieldshippingReferenceNumber VARCHAR(20) Yes
7. TableOrderItem
Attribute Data Type Key Compulsory
fieldorderID INTEGER PRIMARY KEY
FOREIGN KEY
REFERENCES
TableOrder
(fieldorderID)
Yes
fieldplantID INTEGER PRIMARY KEY
FOREIGN KEY
REFERENCES
TablePlant
(fieldplantID)
Yes
fieldcost DOUBLE Yes
fieldquantity INTEGER Yes
fieldunitShippingcost DOUBLE Yes
8. TableMessage
Attribute Data Type Key Compulsory
fieldmessageID INTEGER PRIMARY KEY Yes
fieldclientID INTEGER FOREIGN KEY
REFERENCES
TableClient
(fieldclientID)
Yes
fieldmemberID INTEGER FOREIGN KEY
REFERENCES
TableMember
(fieldmemberID)
Yes
fieldmessageDate DATETIME Yes
fieldmessage VARCHAR(100) Yes
(Edugrabs.com 2015)
Assumptions
1. In an order, number of items can be ordered.
2. A client can do any number of orders. There is no limit on it.
3. Plants stocks will be managed into the database.

4. The shipping cost depends upon the source and destinations.
References
Smartdraw (2016). Entity Relationship Diagram. [Online]. Available:
https://www.smartdraw.com/entity-relationship-diagram/. [Accessed: 4-June-2019]
Edugrabs.com (2015). Types of Attributes in DBMS with Example (ER MODEL – Part 2).
[Online]. Available: http://www.edugrabs.com/type-of-attributes-in-dbms/. [Accessed: 4-June-
2019]