Database Design: ATMC ITC211 Task 2 Solution, Semester 1, 2019

This document presents a comprehensive solution for the ITC211 Database Design Task 2 assignment. It includes an ER Diagram, relational schemas, and detailed table attributes for TbClient, TbMember, TbPlant, TbShippingMultiplier, TbStock, TbOrder, TbOrderItem, and TbMessage. The solution also incorporates assumptions regarding client orders and shipping costs. The database design covers various aspects, including data types, primary and foreign keys, and compulsory attributes for each table. References to external resources on database normalization are also provided, ensuring a well-structured and informative solution. This resource is designed to help students understand and complete similar database design tasks effectively.

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]