ICT701 Semester 1, 2019: Relational Database Systems Task 2

This document presents a solution for Task 2 of the ICT701 Relational Database Systems course, focusing on the design and implementation of a database system. The solution includes an Entity-Relationship (ER) diagram, a relational schema, and supplementary design requirements for a business scenario involving customers, items, sales representatives, orders, invoices, and payments. The assignment addresses various aspects of database modeling, including data types, primary and foreign keys, and the relationships between different entities. Assumptions are made regarding business rules, such as a sales representative handling multiple orders, multiple items in an order, installment payments, and delivery methods. The document references external resources for ER diagrams and database relationships. The assignment aims to provide a comprehensive understanding of relational database design principles and their practical application in a real-world business context, offering a complete database solution for the given scenario. The assignment is designed to test the student's understanding of database concepts and their ability to apply these concepts to design and implement a functional database system.

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]