June 3 ICT 701 Relational Database Systems.

ICT 701 Relational Database
Systems
June 3
2019
Student ID:
Student Name:

Part A
ER Diagram
(Tutorialcup 2015)
(Ziff Davis 2017)

Relational Schema and Supplementary Design Requirements
1. Customer
Attribute Data Type Key Compulsory
customerNumber INT PRIMARY KEY Yes
customerName VARCHAR(40) Yes
phone VARCHAR(20) Yes
email VARCHAR(30) Yes
address VARCHAR(50) Yes
balanceOwing DOUBLE Yes
creditLimit INT Yes
2. Item
Attribute Data Type Key Compulsory
itemCode INT PRIMARY KEY Yes
description VARCHAR(50) Yes
category VARCHAR(20) Yes
suggestedRetail DOUBLE Yes
cost DOUBLE Yes
overseas BIT Yes
QOH INT Yes
minStockLevel INT Yes
3. DeliveryMethod
Attribute Data Type Key Compulsory
deliveryMethod VARCHAR(20) PRIMARY KEY Yes
charge DOUBLE Yes

4. SalesRep
Attribute Data Type Key Compulsory
salesRepID INT PRIMARY KEY Yes
salesRepName VARCHAR(40) Yes
address VARCHAR(50) Yes
mobile VARCHAR(20) Yes
email VARCHAR(30) Yes
hiredDate DATETIME Yes
agreedSalary DOUBLE Yes
commissionRate INT Yes
5. Order
Attribute Data Type Key Compulsory
orderNumber INT PRIMARY KEY Yes
salesRepID INT FOREIGN KEY
references SalesRep
(salesRepID)
Yes
orderDate DATETIME Yes
requiredDate DATETIME Yes
customerNumber INT FOREIGN KEY
references Customer
(customerNumber)
Yes
deliveryMethod VARCHAR(20) FOREIGN KEY
references
DeliveryMethod
(deliveryMethod)
Yes
6. OrderItems
Attribute Data Type Key Compulsory
orderNumber INT PRIMARY KEY
FOREIGN KEY
references Order
(orderNumber)
Yes
itemCode INT PRIMARY KEY
FOREIGN KEY
references Item
(itemCode)
Yes
quantity INT Yes

7. Invoice
Attribute Data Type Key Compulsory
invoiceNumber INT PRIMARY KEY Yes
deliveryCharge DOUBLE Yes
totalAmount DOUBLE Yes
orderNumber INT PRIMARY KEY
FOREIGN KEY
references Order
(orderNumber)
Yes
8. Payment
Attribute Data Type Key Compulsory
paymentID INT PRIMARY KEY Yes
customerNumber INT FOREIGN KEY
references Customer
(customerNumber)
Yes
invoiceNumber INT FOREIGN KEY
references Invoice
(invoiceNumber)
Yes
paymentDate DATETIME Yes
amount DOUBLE Yes
Assumptions
1. A sales representative can take care of many orders.
2. There may be number of items in an order.
3. A customer can do payments in instalments for a specific invoice.
4. The customer’s credit limit will be increased while doing payments for their orders.
5. For each order the delivery method should be associated.

Reference
Tutorialcup (2015), ER Data Model [online] Available from:
https://www.tutorialcup.com/dbms/er-data-model.htm [Accessed: 4 June 2019]
Ziff Davis (2017), Understanding Relationships in E-R Diagrams [online] Available from:
http://it.toolbox.com/blogs/enterprise-solutions/understanding-relationships-in-er-diagrams-
14310 [Accessed: 4 June 2019]