ICT211 Database Design Task 2: ER Diagram & Relational Schema 2019

This assignment presents a solution to a database design task, including an Entity-Relationship (ER) diagram and a corresponding relational schema. The database design incorporates several tables such as TClient, TMember, TPlant, TShippingMultiplier, TStock, TOrder, TOrderItem, and TMessage, each with defined attributes, data types, and primary/foreign key relationships. The design also outlines assumptions related to shipping costs and order management, and includes references to database normalization principles. This comprehensive design provides a structured approach to managing data within the specified system. Desklib offers a wide range of solved assignments and past papers to assist students with their studies.

Student ID:
Student Name:

ER Diagram
(Holowczak.com n.d.)
(Perucci D. n.d.)

Relational Schema and Supplementary Design Requirements
1. TClient
Attribute Data Type Key Compulsory
FclientID INTEGER PRIMARY KEY Yes
FclientName NVARCHAR(50) Yes
Femail NVARCHAR(50) Yes
Flocation NVARCHAR(100) Yes
FstartDate DATETIME Yes
FdeliveryAddress NVARCHAR(100) Yes
FaccountNumber NVARCHAR(12) Yes
2. TMember
Attribute Data Type Key Compulsory
FmemberID INTEGER PRIMARY KEY Yes
FmemberName NVARCHAR(50) Yes
FcontactName NVARCHAR(50) Yes
FstartDate DATETIME Yes
FendDate DATETIME No
FnurseryName NVARCHAR(50) Yes
FnurseryAddress NVARCHAR(100) Yes
FnurseryPhone NVARCHAR(20) Yes
FnurseryEmail NVARCHAR(50) Yes
FmemberDescription NVARCHAR(100) Yes
3. TPlant
Attribute Data Type Key Compulsory
FplantID INTEGER PRIMARY KEY Yes
FbotanicalName NVARCHAR(50) Yes
FcommonName NVARCHAR(50) Yes
Fdescription NVARCHAR(100) Yes
4. TShippingMultiplier
Attribute Data Type Key Compulsory
FnurseryState NVARCHAR(3) PRIMARY KEY Yes
FdestinationState NVARCHAR(3) PRIMARY KEY Yes
FshippingCostMultiplier Double Yes
5. TStock

Attribute Data Type Key Compulsory
FmemberID INTEGER PRIMARY KEY
FOREIGN KEY
REFERENCES
TMember
(FmemberID)
Yes
FplantID INTEGER PRIMARY KEY
FOREIGN KEY
REFERENCES
TPlant (FplantID)
Yes
FpriceDate DATETIME PRIMARY KEY Yes
Fprice DOUBLE Yes
FunitShoppingCost DOUBLE Yes
FinStock INTEGER Yes
6. TOrder
Attribute Data Type Key Compulsory
ForderID INTEGER PRIMARY KEY Yes
FmemberID INTEGER FOREIGN KEY
REFERENCES
TMember
(FmemberID)
Yes
FclientID INTEGER FOREIGN KEY
REFERENCES
TClient (FclientID)
Yes
ForderDate DATETIME Yes
Fstatus NVARCHAR(20) Yes
FshippingDate DATETIME Yes
FcourierName NVARCHAR(50) Yes
FnurseryState NVARCHAR(3) FOREIGN KEY
REFERENCES
TShippingMultiplier
(FnurseryState)
Yes
FdestinationState NVARCHAR(3) FOREIGN KEY
REFERENCES
TShippingMultiplier
(FdestinationState)
Yes
FshippingReferenceNumber NVARCHAR(20) Yes

7. TOrderItem
Attribute Data Type Key Compulsory
ForderID INTEGER PRIMARY KEY
FOREIGN KEY
REFERENCES
TOrder (ForderID)
Yes
FplantID INTEGER PRIMARY KEY
FOREIGN KEY
REFERENCES
TPlant (FplantID)
Yes
Fcost DOUBLE Yes
Fquantity INTEGER Yes
FunitShippingcost DOUBLE Yes
8. TMessage
Attribute Data Type Key Compulsory
FmessageID INTEGER PRIMARY KEY Yes
FclientID INTEGER FOREIGN KEY
REFERENCES
TClient (FclientID)
Yes
FmemberID INTEGER FOREIGN KEY
REFERENCES
TMember
(FmemberID)
Yes
FmessageDate DATETIME Yes
Fmessage NVARCHAR(100) Yes
Assumptions
1. Cost of shipping is based upon the destination and source both.
2. An order may contain any number of items.
3. Any number of orders may belong to a single client.
Reference

Holowczak.com (n.d.). Database Normalization. [Online]. Available:
http://holowczak.com/database-normalization/. [Accessed: 4-June-2019]
Perucci D. (n.d.). Pros and Cons of Database Normalization. Online. Available:
https://dzone.com/articles/pros-and-cons-of-database-normalization. [Accessed: 4-June-2019]