19. Relational Database Systems. ITC701. Student ID:. S

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]