Faculty of CEM: Database Design and Implementation Project, IMAT5103

This assignment presents a comprehensive database design and implementation project for a wholesale shop, 'Top Deals Wholesale Shop'. The project begins with a detailed case scenario outlining the business requirements, including suppliers, products, sales representatives, customers, and orders. The first stage involves conceptual database design, where the entities and attributes are identified, and an Entity Relationship Diagram (ERD) is created to illustrate the relationships between entities. The second stage focuses on logical database design, including the creation of tables with specified key fields, data types, and primary/foreign keys using Oracle DBMS. The assignment further demonstrates the creation of unique indices for efficient data retrieval and population of the tables with sample data. Finally, the project concludes with the writing of several SQL queries to retrieve specific information from the database, such as listing products, sales representatives, customer counts, and order summaries. The project showcases a practical application of database design principles and SQL querying techniques.

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.

STAGE 2: LOGICAL DATABASE DESIGN AND ORACLE SQL
IMPLEMENTATION/QUERYING
Task 1: Logical Database Design
a) Suppliers Table
KEY FIELD DATA TYPE
PK Supplier Id Varchar (12)
Name Varchar (100)
Street Varchar (50)
City Varchar (50)
State Varchar (50)
ZIP Varchar (10)
Phone number Varchar (15)
b) Products Table
KEY FIELD DATA TYPE
PK Product ID Varchar (12)
Product Name Varchar (100)
Category Varchar (50)
Product Description Text
Current stock Number (5)
Price Float

c) Supplier Products
KEY FIELD DATA TYPE
PK Supply ID Varchar (12)
Supply date Date
FK Supplier ID Varchar (12)
FK Product ID Varchar (12)
Quantity Int
Price Double
(Beynon-Davies, 2017)
d) Customers Details Table
KEY FIELD DATA TYPE
PK Customer ID Varchar (12)
Name Varchar (100)
Street Varchar (50)
City Varchar (50)
State Varchar (50)
ZIP Varchar (10)
Phone number Varchar (15)
Rep Number Varchar (12)
e) Sales Representatives Table

KEY FIELD DATA TYPE
PK Rep Number Varchar (12)
Name Varchar (100)
Street Varchar (50)
City Varchar (50)
State Varchar (50)
ZIP Varchar (10)
Phone number Varchar (15)
Commission Float
f) Order details Table
KEY FIELD DATA TYPE
PK Order ID Varchar (12)
Order date Date
FK Customer ID Varchar (12)
g) Orderlines Table
KEY FIELD DATA TYPE
PK ID Number (5)
FK Oder ID Varchar (12)
FK Product ID Varchar (12)
Price Double

Quantity Number (10)
(Helskyaho, 2015)
TASK 2.2: Creating Tables using Oracle DBMS
a) Suppliers_Details
CREATE TABLE Suppliers_Details(Supplier_ID varchar(12), Name varchar(100), Street
varchar(50), City varchar(50), State varchar(50), ZIP varchar(10), Phone_no varchar(15),
PRIMARY KEY(Supplier_ID));
b) Products_Table
CREATE TABLE Products_Table(Product_ID Varchar(12) , Product_Name Varchar(100),
Category varchar(50), Product_Descr varchar(150), Current_stock int, Price float,
PRIMARY KEY(Product_ID));
c) Suppliers_Products
CREATE TABLE Supplier_Products(Supply_ID varchar(12), Supply_date date, Supplier_ID
varchar(12) REFERENCES Suppliers_Details(Supplier_ID), Product_ID varchar(12)
REFERENCES Products_Table(Product_ID), Quantity Number(10), Price float, PRIMARY
KEY(Supply_ID));
d) Sales_Rep_Table
CREATE TABLE Sales_Rep_Table(Rep_No varchar(12), Name varchar(100), Street
varchar(50), City varchar(50), State varchar(50), ZIP varchar(10), Phone_no varchar(15),
Commission float, PRIMARY KEY(Rep_No));

e) Customer_Details
CREATE TABLE Customer_Details(Customer_ID varchar(12), Name varchar(100), Street
varchar(50), City varchar(50), State varchar(50), ZIP varchar(10), Phone_no varchar(15),
Rep_no varchar(12) REFERENCES Sales_Rep_Table(Rep_No) , PRIMARY
KEY(Customer_ID));
f) Orders_Table
CREATE TABLE Orders_Table(Order_ID varchar(12), Order_date date, Customer_ID
varchar(12) REFERENCES Customer_Details(Customer_ID), PRIMARY KEY(Order_ID));
g) Orderlines_Table
CREATE TABLE Orderlines_Table(ID Number(5), Oder_ID varchar(12) REFERENCES
Orders_Table(Order_ID), Product_ID varchar(12) REFERENCES Products_Table(Product_ID),
Price float, Quantity Number(5), PRIMARY KEY(ID));
(Price, 2013)
TASK 2.3: Unique Indices Creation
1. Supplier Index
Create index supplier_idx on Suppliers_Details(Supplier_ID, Name, Phone_No);
This index makes each entry of Supplier_ID, Name and Phone_No unique in every record in the
Suppliers_Details Table.
2. Products Index
Create index products_idx on Products_Tbl(Product_ID, Product_Name);

This index makes each entry of Product_ID and Product_Name unique in every record in the
Products_Tbl Table.
3. Representatives Index
Create index reps_idx on Sales_Rep_Table (Rep_No, Name, Phone_No);
This index makes each entry of Rep_No, Name and Phone_No unique in every record in the
Sales_Rep_Table.
4. Customer Index
Create index customer_idx on Customer_Details (Customer_ID, Name, Phone_No);
This index makes each entry of Customer_ID, Name and Phone_No unique in every record in
the Customer_Details.
TASK 2.4: Data Population
a) Supplier_Details
INSERT INTO Suppliers_Details(Supplier_ID, Name, Street, City, State, ZIP, Phone_NO)
VALUES (‘S/001’, ‘William Torrens’, ‘Second St’,’Chicago’,’IL’,’58993’,’986 266 290’);
INSERT INTO Suppliers_Details(Supplier_ID, Name, Street, City, State, ZIP, Phone_NO)
VALUES (‘S/002’, ‘Amor Stuff’, ‘First St’,’Chicago’,’IL’,’58993’,’882 029 277’);
INSERT INTO Suppliers_Details(Supplier_ID, Name, Street, City, State, ZIP, Phone_NO)
VALUES (‘S/003’,’Evans Klopp’, ’Third St’,’Chicago’,’IL’,’58993’,’869 630 100’);
INSERT INTO Suppliers_Details(Supplier_ID, Name, Street, City, State, ZIP, Phone_NO)
VALUES (‘S/004’,’James Reid’, ’River Ave’, ‘Kenosha’, ‘WI’, ‘64550’, ‘889 002 112’);

INSERT INTO Suppliers_Details(Supplier_ID, Name, Street, City, State, ZIP, Phone_NO)
VALUES (‘S/005’, ‘Nadine Lustre’, ‘North Ave’ ‘Gary’, ‘IN’, ‘63352’, ‘477 906 267’);
(Kuhn, 2013)
b) Products_Table
INSERT INTO Products_Tbl(Product_ID, Product_Name, Category, Product_Descr,
Current_stock, Price) VALUES (‘P/001’, ‘Amenican Suit’, ‘Mens’,’Black suit’,12, 60);
INSERT INTO Products_Tbl(Product_ID, Product_Name, Category, Product_Descr,
Current_stock, Price) VALUES (‘P/002’, ‘Rock Black Shoe’, ‘Mens’,’Black leather’,10, 30);
INSERT INTO Products_Tbl(Product_ID, Product_Name, Category, Product_Descr,
Current_stock, Price) VALUES (‘P/003’, ‘Trench Coat’, ‘Women’, ’American Grey’,8, 37);
INSERT INTO Products_Tbl(Product_ID, Product_Name, Category, Product_Descr,
Current_stock, Price) VALUES (‘P/004’, ‘Jeans Trousers’, ‘Women’, ‘Blue and Black Jeans’,35,
22);

INSERT INTO Products_Tbl(Product_ID, Product_Name, Category, Product_Descr,
Current_stock, Price) VALUES (‘P/005’, ‘Sweaters’, ‘Kids’, ‘Woolen Sweater’,32, 18);
INSERT INTO Products_Tbl(Product_ID, Product_Name, Category, Product_Descr,
Current_stock, Price) VALUES (‘P/006’, ‘Socks’, ‘Kids’, ‘Cotton Socks’,21, 12);
c) Customer Details
INSERT INTO Customer_Details (Customer_ID, Name, Street, City, State, ZIP, Phone_no,
Rep_No) VALUES (‘C/001’,’Rayd Blue’, ‘Third St’, ‘Chicago’, ‘IL’, ‘55500’, ‘390 458
230’,’R/002’);
INSERT INTO Customer_Details (Customer_ID, Name, Street, City, State, ZIP, Phone_no,
Rep_No) VALUES (‘C/002’,’Evans Miller’, ‘First St’, ‘Chicago’, ‘IL’, ‘55500’, ‘999 290
188’,’R/002’);