Database Design ITC211.

needs designing a database

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]