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’);

INSERT INTO Customer_Details (Customer_ID, Name, Street, City, State, ZIP, Phone_no,
Rep_No) VALUES (‘C/003’,’Jonas Blue’, ‘Third St’, ‘Chicago’, ‘IL’, ‘55500’, ‘039 788
288’,’R/003’);
INSERT INTO Customer_Details (Customer_ID, Name, Street, City, State, ZIP, Phone_no,
Rep_No) VALUES (‘C/004’,’John Jack’, ‘Main St’, ‘Wilmette’, ‘IL’, ‘87366’, ‘774 458
231’,’R/005’);
INSERT INTO Customer_Details (Customer_ID, Name, Street, City, State, ZIP, Phone_no,
Rep_No) VALUES (‘C/005’,’Sarah Davis’, ‘River Ave’, ‘Kenosha’, ‘WI’, ‘64775’, ‘670 345
830’,’R/001’);
d) Sales_Rep_Table
INSERT INTO Sales_Rep_Table(Rep_No, Name, Street, City, State, ZIP, Phone_No,
Commission) VALUES (‘R/001’,’Jack Walker’, ‘East St’, ‘Riverdale’,’IL’,’99022’,’446 373
722’,’566.5’);

INSERT INTO Sales_Rep_Table(Rep_No, Name, Street, City, State, ZIP, Phone_No,
Commission) VALUES (‘R/002’,’Jeans Wiliams’, ‘East St’, ‘Riverdale’,’IL’,’68002’,’849 120
346’,’470.5’);
INSERT INTO Sales_Rep_Table(Rep_No, Name, Street, City, State, ZIP, Phone_No,
Commission) VALUES (‘R/003’,’Joe Smith’, ‘Indian Ave’, ‘Belvidere’,’IL’,’99022’,’446 373
722’,’600.5’);
INSERT INTO Sales_Rep_Table(Rep_No, Name, Street, City, State, ZIP, Phone_No,
Commission) VALUES (‘R/004’,’Tom Backer’, ‘First St’, ‘Chicago,’IL’,’88391’,’500 222
903’,’370’);
INSERT INTO Sales_Rep_Table(Rep_No, Name, Street, City, State, ZIP, Phone_No,
Commission) VALUES (‘R/005’,’Kara Jones’, ‘Second St’, ‘Chocago’,’IL’, ’88391’,888 293
002’,’350.5’);
e) Supplier_Products

INSERT INTO Supplier_Products(Supply_ID, Supply_date, Supplier_ID, Product_ID, Quantity,
Price) VALUES (‘001’,’02-JAN-19’,’S/001’, ‘P/001’, 20, 50);
INSERT INTO Supplier_Products(Supply_ID, Supply_date, Supplier_ID, Product_ID, Quantity,
Price) VALUES (‘002’,’09-FEB-19’,’S/002’, ‘P/002’, 10, 25);
INSERT INTO Supplier_Products(Supply_ID, Supply_date, Supplier_ID, Product_ID, Quantity,
Price) VALUES (‘003’,’12-DEC-19’,’S/003’, ‘P/003’, 12, 30);
INSERT INTO Supplier_Products(Supply_ID, Supply_date, Supplier_ID, Product_ID, Quantity,
Price) VALUES (‘004’,’10-NOV-19’,’S/004’, ‘P/004’, 10, 15);
INSERT INTO Supplier_Products(Supply_ID, Supply_date, Supplier_ID, Product_ID, Quantity,
Price) VALUES (‘005’,’14-SEP-19’,’S/005’, ‘P/005’, 20, 12);
f) Orders_Table
INSERT INTO Orders_Table(Order_ID, Order_date, Customer_ID) VALUES (‘O/001’,’10-
NOV-19’,’C/001’);
INSERT INTO Orders_Table(Order_ID, Order_date, Customer_ID) VALUES (‘O/002’,’11-
NOV-19’,’C/002’);

INSERT INTO Orders_Table(Order_ID, Order_date, Customer_ID) VALUES (‘O/003’,’08-
NOV-19’,’C/003’);
INSERT INTO Orders_Table(Order_ID, Order_date, Customer_ID) VALUES (‘O/004’,’10-
OCT-19’,’C/004’);
INSERT INTO Orders_Table(Order_ID, Order_date, Customer_ID) VALUES (‘O/005’,’05-
OCT-19’,’C/005’);
g) Orderlines_Table
INSERT INTO Orderlines_Table(ID, Order_ID, Product_ID, Price, Quantity) VALUES
(1,’O/001’,’P/005’,18, 2);
INSERT INTO Orderlines_Table(ID, Order_ID, Product_ID, Price, Quantity) VALUES
(2,’O/001’,’P/006’,12, 2);
INSERT INTO Orderlines_Table(ID, Order_ID, Product_ID, Price, Quantity) VALUES
(3,’O/002’,’P/003’,37, 1);
INSERT INTO Orderlines_Table(ID, Order_ID, Product_ID, Price, Quantity) VALUES
(4,’O/003’,’P/002’,30, 3);
INSERT INTO Orderlines_Table(ID, Order_ID, Product_ID, Price, Quantity) VALUES
(5,’O/003’,’P/001’,60, 1);

INSERT INTO Orderlines_Table(ID, Order_ID, Product_ID, Price, Quantity) VALUES
(6,’O/004’,’P/004’,35, 2);
INSERT INTO Orderlines_Table(ID, Order_ID, Product_ID, Price, Quantity) VALUES
(7,’O/005’,’P/005’,18, 3);
TASK 2.5: SQL QUERY WRITING
1. Query to List Product ID, Name, Price and Current Stock sorted according to price in a
descending order
SELECT Product_ID, Product_Name, Price, Current_Stock FROM Products_Table ORDER
BY Price DESC;
2. Query to List all Sale Representatives’ Rep_No, Name and Commission who earn a
monthly commission of > $400

SELECT Rep_No, Name, Commission FROM Sales_Rep_Table WHERE Commission
>400;
3. Return the number of all the Customers who reside in Chicago City
SELECT Count(Customer_ID) AS Count_of_Chicago_Customers FROM Customer_Details
WHERE City=’Chicago’;
4. Query to Return Cusomer_ID, Name and Name of the Sales Representative.
SELECT Customer_Details.Customer_ID, Customer_Details.Name, Sales_Rep_Table.Name
FROM Customer_Details INNER JOIN Sales_Rep_Table ON Customer_Details.Rep_No =
Sales_Rep_Table.Rep_No;

5. Query to return Order ID, Product ID, Price, Quantity Ordered by the Order_ID in
ascending order.
SELECT Order_ID, Product_ID, Price, Quantity FROM Orderlines_Table ORDER BY
Order_ID ASC;
6. Query to return the Sum of money per customer order
SELECT Order_ID, SUM(Price*Quantity) AS TOTAL FROM Orderlines_Table GROUP
BY Order_ID;
(Ries, 2013)
Conclusion
In conclusion, we have been able to use Structured Query Language (SQL) to design our tables,
insert data into them and to write queries which have enabled us to extract data from these tables.

References
Beynon-Davies, P. (2017). Database Systems. London: Macmillan International Higher
Education.
Helskyaho, H. (2015). Oracle SQL Developer Data Modeler for Database Design Mastery. New
York : McGraw-Hill Education.
Kuhn, D. (2013). Pro Oracle Database 12c Administration. New York: Apress.
Price, J. (2013). Oracle Database 12c SQL. New York: McGraw Hill Professional.
Ries, S. (2013). Oca Oracle Database 11g Database Administration. Birmingham: Packt
Publishing Ltd.
Umanath, N. S., & Scamell, . W. (2014). Data Modeling and Database Design. Boston: Cengage
Learning.