ATMC ITC211 Database Design Task 2: ER Diagram & Relational Schema

This assignment solution presents a comprehensive database design for the ITC211 course, focusing on ER diagrams and relational schemas. The solution details the design of multiple tables, including Client, Member, Plant, ShippingMultiplier, Stock, Order, OrderItem, and Message. Each table's attributes, data types, keys, and compulsory fields are meticulously outlined. The design incorporates foreign key relationships to ensure data integrity and reflect the specified assumptions, such as an order containing multiple plants and shipping costs varying based on source and destination. The document also references the sources used for the design, including CS Odessa Corp. and W3schools.in. This assignment provides a practical example of database design principles and their application in a real-world scenario. It is a valuable resource for students studying database systems and related topics.

ER Diagram
(CS Odessa Corp. 2017)

Relational Schema
(W3schools.in n.d.)

Supplementary Design Requirements
1. TabClient
Attribute Data Type Key Compulsory
fildclientID INTEGER PRIMARY KEY Y
fildclientName VARCHAR(50) Y
fildemail VARCHAR(50) Y
fildlocation VARCHAR(100) Y
fildstartDate DATETIME Y
filddeliveryAddress VARCHAR(100) Y
fildaccountNumber VARCHAR(12) Y
2. TabMember
Attribute Data Type Key Compulsory
fildmemberID INTEGER PRIMARY KEY Y
fildmemberName VARCHAR(50) Y
fildcontactName VARCHAR(50) Y
fildstartDate DATETIME Y
fildendDate DATETIME No
fildnurseryName VARCHAR(50) Y
fildnurseryAddress VARCHAR(100) Y
fildnurseryPhone VARCHAR(20) Y
fildnurseryEmail VARCHAR(50) Y
fildmemberDescription VARCHAR(100) Y
3. TabPlant
Attribute Data Type Key Compulsory
fildplantID INTEGER PRIMARY KEY Y
fildbotanicalName VARCHAR(50) Y
fildcommonName VARCHAR(50) Y
filddescription VARCHAR(100) Y
4. TabShippingMultiplier
Attribute Data Type Key Compulsory
fildnurseryState VARCHAR(3) PRIMARY KEY Y
filddestinationState VARCHAR(3) PRIMARY KEY Y
fildshippingCostMultiplier Double Y
5. TabStock

Attribute Data Type Key Compulsory
fildmemberID INTEGER PRIMARY KEY
FOREIGN KEY
REFERENCES
TabMember
(fildmemberID)
Y
fildplantID INTEGER PRIMARY KEY
FOREIGN KEY
REFERENCES
TabPlant
(fildplantID)
Y
fildpriceDate DATETIME PRIMARY KEY Y
fildprice DOUBLE Y
fildunitShoppingCost DOUBLE Y
fildinStock INTEGER Y
6. TabOrder
Attribute Data Type Key Compulsory
fildorderID INTEGER PRIMARY KEY Y
fildmemberID INTEGER FOREIGN KEY
REFERENCES
TabMember
(fildmemberID)
Y
fildclientID INTEGER FOREIGN KEY
REFERENCES
TabClient
(fildclientID)
Y
fildorderDate DATETIME Y
fildstatus VARCHAR(20) Y
fildshippingDate DATETIME Y
fildcourierName VARCHAR(50) Y
fildnurseryState VARCHAR(3) FOREIGN KEY
REFERENCES
TabShippingMultiplier
(fildnurseryState)
Y
filddestinationState VARCHAR(3) FOREIGN KEY
REFERENCES
TabShippingMultiplier
(filddestinationState)
Y
fildshippingReferenceNumber VARCHAR(20) Y
7. TabOrderItem

Attribute Data Type Key Compulsory
fildorderID INTEGER PRIMARY KEY
FOREIGN KEY
REFERENCES
TabOrder
(fildorderID)
Y
fildplantID INTEGER PRIMARY KEY
FOREIGN KEY
REFERENCES
TabPlant
(fildplantID)
Y
fildcost DOUBLE Y
fildquantity INTEGER Y
fildunitShippingcost DOUBLE Y
8. TabMessage
Attribute Data Type Key Compulsory
fildmessageID INTEGER PRIMARY KEY Y
fildclientID INTEGER FOREIGN KEY
REFERENCES
TabClient
(fildclientID)
Y
fildmemberID INTEGER FOREIGN KEY
REFERENCES
TabMember
(fildmemberID)
Y
fildmessageDate DATETIME Y
fildmessage VARCHAR(100) Y
Assumptions
1. An order will contain any number of plants.
2. A member can monitor the stocks of number of plants.
3. The shipping cost depends upon the places of source and destination.

Reference
CS Odessa Corp. (2017). Entity Relationship Diagram Symbols. [Online]. Available:
http://www.conceptdraw.com/How-To-Guide/erd-entity-relationship-diagram-symbols.
[Accessed: 7-June-2019]
W3schools.in (n.d.). Database Normalization. [Online]. Available:
https://www.w3schools.in/dbms/database-normalization/. [Accessed: 7-June-2019]