A database which refers to an organized

INTRODUCTION
A database which refers to an organized collection of files that enables quick retrieval and
modification of data is a very essential tool to many organizations. Database systems have many
advantages if adopted by an organization. They enhance storage, retrieval, modification of data,
they enforce data integrity, they reduce data redundancy just to mention a few. In this case
scenario, we shall be implementing a database for Top Deals Wholesale Shop. Oracle DBMS
shall be used to design database. Furthermore, a detailed case scenario, a conceptual database
design, logical database design and sample queries have been included.

Stage 1: Scenario and Conceptual Database Design
Task 1.1: Selection of Case Study
Top deals is a wholesale market place that sells wears to its customers at a factory price.
Commodities sold are clothes and shoes for women, men and kids. This shop gets its stock from
different suppliers who reside within Australia. Selling process is organized in such a manner
that for each customer, there is a sales representative who carries out the sales on behalf of the
shop. For each supplier, details of name, address, phone number and the items supplied are
recorded. For each sales representative, details of ID, name, gender, address, phone number and
commission are captured. For every product sold, ID, product name, product category, product
description, current stock and price are captured. Customer name, address and contact details are
captured for each customer. For every order that the customer places, order ID, order date,
customer details and the products ordered are captured as orderlines.

TASK 1.2: Conceptual Database Design
Enterprise Rules
1. A supplier can supply one or many products to the shop.
2. A sales representative can have many customers but a customer has only one sales
representative.
3. A customer can make one or many orders.
4. Each order can have one or many orderlines.
ENTITY RELATIONSHIP DIAGRAM
The following entities and attributes can be derived from the case scenario
No Entity Attributes
1. Supplier Supplier ID, Name, Address (Street, City, State, ZIP), Phone
number
2. Product Product ID, Product Name, Category, Product Description,
Current stock, Price
3. Supplier Product Supply ID, Supply date, Supplier ID, Product ID, Quantity, Price
4. Customer Customer ID, Name, Address (Street, City, State, ZIP), Phone
number, Rep Number
5. Sales Representative Rep Number, Name, Address (Street, City, State, ZIP), Phone
number, Commission
6. Order Order ID, Order date, Customer ID
7. Orderlines ID, Oder ID, Product ID, Price, Quantity

(Umanath & Scamell, 2014)
ENTITY RELATIONSHIPS EXPLANATION
From the entity relationship diagram, 5 strong entities (Supplier, Sales Rep, Product, Customer
and order) and 2 weak entities (Supplier product and Ordeline) can be identified.
Basically, a supplier can supply many products to the shop and one product can be supplied by
many suppliers.
Each sales representative can have many customers whereas each customer has only one sales
representative.
A customer can make one or many orders where each order can have one or many products.