Database Design ITC211

This document provides information about database design in the ITC211 course. It covers topics such as ER diagrams, relational schemas, and design requirements. The document also includes details about various tables such as TbClient, TbMember, TbPlant, TbShippingMultiplier, TbStock, TbOrder, TbOrderItem, and TbMessage.

Database Design ITC211
Task 2
6/5/2019
Student ID:
Student Name:

ER Diagram
(B. Peter n.d.)
(Cinergix Pty Ltd. 2011)

Relational Schema and Supplementary Design Requirements
1. TbClient
Attribute Data Type Key Compulsory
fdclientID INTEGER PRIMARY KEY Y
fdclientName VARCHAR(50) Y
fdemail VARCHAR(50) Y
fdlocation VARCHAR(100) Y
fdstartDate DATETIME Y
fddeliveryAddress VARCHAR(100) Y
fdaccountNumber VARCHAR(12) Y
2. TbMember
Attribute Data Type Key Compulsory
fdmemberID INTEGER PRIMARY KEY Y
fdmemberName VARCHAR(50) Y
fdcontactName VARCHAR(50) Y
fdstartDate DATETIME Y
fdendDate DATETIME No
fdnurseryName VARCHAR(50) Y
fdnurseryAddress VARCHAR(100) Y
fdnurseryPhone VARCHAR(20) Y
fdnurseryEmail VARCHAR(50) Y
fdmemberDescription VARCHAR(100) Y
3. TbPlant
Attribute Data Type Key Compulsory
fdplantID INTEGER PRIMARY KEY Y
fdbotanicalName VARCHAR(50) Y
fdcommonName VARCHAR(50) Y
fddescription VARCHAR(100) Y
4. TbShippingMultiplier
Attribute Data Type Key Compulsory
fdnurseryState VARCHAR(3) PRIMARY KEY Y
fddestinationState VARCHAR(3) PRIMARY KEY Y
fdshippingCostMultiplier Double Y
5. TbStock

Attribute Data Type Key Compulsory
fdmemberID INTEGER PRIMARY KEY
FOREIGN KEY
REFERENCES
TbMember
(fdmemberID)
Y
fdplantID INTEGER PRIMARY KEY
FOREIGN KEY
REFERENCES
TbPlant (fdplantID)
Y
fdpriceDate DATETIME PRIMARY KEY Y
fdprice DOUBLE Y
fdunitShoppingCost DOUBLE Y
fdinStock INTEGER Y
6. TbOrder
Attribute Data Type Key Compulsory
fdorderID INTEGER PRIMARY KEY Y
fdmemberID INTEGER FOREIGN KEY
REFERENCES
TbMember
(fdmemberID)
Y
fdclientID INTEGER FOREIGN KEY
REFERENCES
TbClient (fdclientID)
Y
fdorderDate DATETIME Y
fdstatus VARCHAR(20) Y
fdshippingDate DATETIME Y
fdcourierName VARCHAR(50) Y
fdnurseryState VARCHAR(3) FOREIGN KEY
REFERENCES
TbShippingMultiplier
(fdnurseryState)
Y
fddestinationState VARCHAR(3) FOREIGN KEY
REFERENCES
TbShippingMultiplier
(fddestinationState)
Y
fdshippingReferenceNumber VARCHAR(20) Y

7. TbOrderItem
Attribute Data Type Key Compulsory
fdorderID INTEGER PRIMARY KEY
FOREIGN KEY
REFERENCES
TbOrder (fdorderID)
Y
fdplantID INTEGER PRIMARY KEY
FOREIGN KEY
REFERENCES
TbPlant (fdplantID)
Y
fdcost DOUBLE Y
fdquantity INTEGER Y
fdunitShippingcost DOUBLE Y
8. TbMessage
Attribute Data Type Key Compulsory
fdmessageID INTEGER PRIMARY KEY Y
fdclientID INTEGER FOREIGN KEY
REFERENCES
TbClient
(fdclientID)
Y
fdmemberID INTEGER FOREIGN KEY
REFERENCES
TbMember
(fdmemberID)
Y
fdmessageDate DATETIME Y
fdmessage VARCHAR(100) Y
(hackr.io 2018)
(en.tekstenuitleg.net n.d.)
(ApexSQL 2019)
Assumptions
1. A client can order number of items in a single order.
2. The shipping cost depends upon the source location and destination location.
3. A member can manage stocks of number of plants.

Reference
hackr.io (2018). DBMS Normalization: 1NF, 2NF, 3NF and BCNF with Examples. [Online].
Available: https://hackr.io/blog/dbms-normalization/. [Accessed: 5-June-2019]
en.tekstenuitleg.net (n.d.). Database Normalization. [Online]. Available:
http://en.tekstenuitleg.net/articles/software/database-design-tutorial/database-normalization.html.
[Accessed: 5-June-2019]
ApexSQL (2019). What is Database Normalization in SQL Server? Online. Available:
https://www.sqlshack.com/what-is-database-normalization-in-sql-server/. [Accessed: 5-June-
2019]