ITC701 Relational Database Systems Task 2: ER Diagram & Schema Design

This document presents a comprehensive solution for the ITC701 Relational Database Systems Task 2 assignment. The solution includes a detailed ER diagram illustrating the entities and relationships within the database, such as Customers, Items, Orders, Sales Representatives, Invoices, and Payments. The relational schema is provided, defining tables with their attributes, data types, primary keys, foreign keys, and compulsory fields. The assignment incorporates assumptions like handling payments in installments, storing delivery methods, and managing credit limits. The solution is designed to help students understand the principles of relational database design, data modeling, and database implementation, including normalization and the creation of efficient and reliable database structures. The document also references external sources for additional context and information.

Student ID:
Student Name:

Part A
ER Diagram
(Holowczak.com n.d.)

Relational Schema and Supplementary Design Requirements
1. TblCustomer
Attribute Data Type Key Compulsory
fldcustomerNumber INTEGER PRIMARY KEY Y
fldcustomerName VARCHAR(40) Y
fldphone VARCHAR(20) Y
fldemail VARCHAR(30) Y
fldaddress VARCHAR(50) Y
fldbalanceOwing DOUBLE Y
fldcreditLimit INTEGER Y
2. TblItem
Attribute Data Type Key Compulsory
flditemCode INTEGER PRIMARY KEY Y
flddescription VARCHAR(50) Y
fldcategory VARCHAR(20) Y
fldsuggestedRetail DOUBLE Y
fldcost DOUBLE Y
fldoverseas BIT Y
fldQOH INTEGER Y
fldminStockLevel INTEGER Y
3. TblDeliveryMethod
Attribute Data Type Key Compulsory
flddeliveryMethod VARCHAR(20) PRIMARY KEY Y
fldcharge DOUBLE Y
4. TblSalesRep
fldAttribute Data Type Key Compulsory
fldsalesRepID INTEGER PRIMARY KEY Y
fldsalesRepName VARCHAR(40) Y
fldaddress VARCHAR(50) Y
fldmobile VARCHAR(20) Y
fldemail VARCHAR(30) Y
fldhiredDate DATETIME Y
fldagreedSalary DOUBLE Y
fldcommissionRate INTEGER Y

5. TblOrder
Attribute Data Type Key Compulsory
fldorderNumber INTEGER PRIMARY KEY Y
fldsalesRepID INTEGER FOREIGN KEY
references
TblSalesRep
(fldsalesRepID)
Y
fldorderDate DATETIME Y
fldrequiredDate DATETIME Y
fldcustomerNumber INTEGER FOREIGN KEY
references
TblCustomer
(fldcustomerNumber)
Y
flddeliveryMethod VARCHAR(20) FOREIGN KEY
references
TblDeliveryMethod
(flddeliveryMethod)
Y
6. TblOrderItems
Attribute Data Type Key Compulsory
fldorderNumber INTEGER PRIMARY KEY
FOREIGN KEY
references TblOrder
(fldorderNumber)
Y
flditemCode INTEGER PRIMARY KEY
FOREIGN KEY
references TblItem
(flditemCode)
Y
fldquantity INTEGER Y
7. TblInvoice
Attribute Data Type Key Compulsory
fldinvoiceNumber INTEGER PRIMARY KEY Y
flddeliveryCharge DOUBLE Y
fldtotalAmount DOUBLE Y
fldorderNumber INTEGER PRIMARY KEY
FOREIGN KEY
references TblOrder
(fldorderNumber)
Y

8. TblPayment
Attribute Data Type Key Compulsory
fldpaymentID INTEGER PRIMARY KEY Y
fldcustomerNumber INTEGER FOREIGN KEY
references
TblCustomer
(fldcustomerNumber)
Y
fldinvoiceNumber INTEGER FOREIGN KEY
references TblInvoice
(fldinvoiceNumber)
Y
fldpaymentDate DATETIME Y
fldamount DOUBLE Y
(Edugrabs.com 2015)
Assumptions
a. An order may contain any number of items.
b. There is option to do payments in instalments for an invoice.
c. The delivery method is necessary to be stored while ordering.
d. The credit limit of customer will increase after payments.
e. The credit limit of customer will decrease after orders.
f. A sales representative can manage number of orders.

Reference
Holowczak.com (n.d.). Database Normalization. [Online]. Available:
http://holowczak.com/database-normalization/. [Accessed: 5-June-2019]
Edugrabs.com (2015). Types of Attributes in DBMS with Example (ER MODEL – Part 2).
[Online]. Available: http://www.edugrabs.com/type-of-attributes-in-dbms/. [Accessed: 5-June-
2019]