Database Design for Online Shopping

This assignment discusses the database design for an online shopping application, including the class diagram, implementation, and relational model. It also covers the tables and data insertion for different categories and subcategories.

1
Table of Contents
Table of Contents........................................................................................................................................1
Chapter 1: Introduction................................................................................................................................2
 Aims: -.............................................................................................................................................2
 Objectives: -....................................................................................................................................2
Chapter 2: The Class Diagram.....................................................................................................................4
Chapter 3:....................................................................................................................................................6
The Relational Model..................................................................................................................................7
Chapter 4: Implementation........................................................................................................................11
Chapter 5: Queries.....................................................................................................................................20
Conclusion.................................................................................................................................................23
References: -..............................................................................................................................................25

2
Chapter 1: Introduction
 Aims: -
In this assignment, we are creating a database for “All About Furniture (AAF)”. This is a chain
of stores that sell various household goods from store locations throughout the UK. This
database stores information related to products, customers, their basket and orders. The aim of
this database is to provide online shopping services to the customers. We will not need to go to
the market and visit any shop for getting anything. Just create an account on this site and then the
user can view all the available products. The user can go through the products and purchase their
favorite products. Then the user can pay online for the products or they can also pay after
product delivery.
This database will simplify the overall shopping and payment process. A customer can select and
add any number of products to the bucket. Then these products can be ordered from the bucket.
There are a number of product categories and each category can have a number of subcategories.
Each subcategory can have a number of products. An online shopping application can be easily
designed using the provided class diagram and database design.
 Objectives: -
- This database stores all the information related to household goods and provides
better services to the customers.
- It provides the facilities to the customers to shop online.

3
- This database does not store information related to customer payment methods due to
privacy reasons. This information is just collected temporarily for the payment
purpose.
- This database provides full detail of all the available products like product name, their
cost, how many products are available, etc.
- By using this database, we can save our money and time.
- It will help the client in increasing the overall business. The ease in use and the
simple design will helps in growing the sales of the company significantly.
- This helps in providing secured access to users. We are using a unique combination
of username and password for authenticating each user. It will help in to increase the
overall security of the database.

4
Chapter 2: The Class Diagram
(www.tutorialspoint.com, 2019)

5
We have various classes in our object-oriented design. All the attributes and operations of the
objects are grouped together in classes. Each category can have many subcategories and each
subcategory can have various products. A basket will store all the products added by the
customer in the cart. An order can have a number of products selected from the basket. Also,
each order will have a shipping method.
Constraints and assumptions:
1. Each product will have a unique reference number and it will act as a primary key for each
product.
2. There are a number of product categories.
3. Each product category can further have a number of subcategories.
4. Each subcategory can further have a number of products.
5. One or more products can be added to the customer basket at any given time.
6. A customer can have only one basket and each basket can have zero or many products stored
in it.
7. A customer can have one or many orders and each order further can have one or many
products.
8. There is a shipping method associated with each order and it is stored along with the order
details.
9. An order will have at least one product. An order cannot exist without a product.
10. All the attributes of each class are private. So these attributes cannot be accessed directly
outside these classes. The external classes can use the getters and setters provided with each
object to access these attributes.

6
Chapter 3:
 The major design decision taken in the construction of the class diagram is that basket
and the order will be different. Each of them can have a number of products.
 We have stored the quantities of the products, delivery Dates and the orders in three
parallel arrays in the Order class. The user will have to add the required data in all these
arrays.
 We have stored the quantities of the products, added Dates and the orders in three parallel
arrays in the Basket class. The user will have to add the required data in all these arrays.
 Each user will have a unique username and they will have to use their unique username
and password to login into the application.
 The delivery date of all the products in an order will be stored differently. Two orders in
the same order can have a different delivery date.
 The quantity of the order in basket and order cannot be negative or zero. It must be a
number greater than or equal to one.
 To convert the class diagram into a relational model, I analyzed the class diagram. Then I
converted all the classes into entities/tables and class attributes into table attribute with
their data type. Then I established the relationship between the tables according to the
constructed class diagram and normalized the tables up to 3NF by Decomposing the
tables to remove many to many relationships.
 We have prepared a normalized database from the class diagram and it is in the third
normal form. There are no repeating groups, no partial dependency and no functional
dependency in the implemented database design.

7
 Our database design allows us to implement all the required queries and all the queries
are giving us the required results.
The Relational Model
--1. Category Table
CREATE TABLE Category
(
Category_No VARCHAR2 (10) PRIMARY KEY,
CG_Name VARCHAR2 (50) NOT NULL,
Description LONG
);
--2. Sub_Category Table
CREATE TABLE Sub_Category
(
SC_No VARCHAR2 (10) PRIMARY KEY,
Category_No VARCHAR2 (10) NOT NULL,
SC_Name VARCHAR2 (50) NOT NULL,
Description LONG,
FOREIGN KEY (Category_No) REFERENCES Category (Category_No)
);
--3. Product Table

8
CREATE TABLE Product
(
PReference_No VARCHAR2 (10) PRIMARY KEY,
SC_No VARCHAR2 (10) NOT NULL,
PName VARCHAR2 (50) NOT NULL,
Description LONG,
Price NUMBER (10,2) NOT NULL,
Quantity NUMBER (10) NOT NULL,
FOREIGN KEY (SC_No) REFERENCES Sub_Category (SC_No)
);
--4. Customer Table
CREATE TABLE Customer
(
UserName VARCHAR2 (10) PRIMARY KEY,
Password VARCHAR2 (10) NOT NULL,
Title VARCHAR 2(5) CHECK (Title IN ('Mr','Mrs','Miss')),
Initials VARCHAR2 (20) NOT NULL,
Last_Name VARCHAR2 (20) NOT NULL,
HN_Street VARCHAR2 (20) NOT NULL,
Town VARCHAR2 (20) NOT NULL,
PostCode VARCHAR2 (10) NOT NULL,
TelePhone VARCHAR2 (12) NOT NULL,

9
Email VARCHAR2 (50) NOT NULL
);
--5. Basket Table
CREATE TABLE Basket
(
Basket_No VARCHAR2 (10) PRIMARY KEY,
UserName VARCHAR2 (10) NOT NULL,
Create_Date DATE NOT NULL,
Status VARCHAR2 (15) CHECK (Status IN ('Ordered','Not Ordered')) NOT NULL,
FOREIGN KEY (UserName) REFERENCES Customer (UserName)
);
--6. Basket_Product Table
CREATE TABLE Basket_Product
(
Basket_No VARCHAR2 (10) NOT NULL,
PReference_No VARCHAR2 (10) NOT NULL,
Quantity NUMBER (10) NOT NULL,
Add_Date DATE NOT NULL,
PRIMARY KEY (Basket_No, PReference_No),
FOREIGN KEY (Basket_No) REFERENCES Basket (Basket_No),
FOREIGN KEY (PReference_No) REFERENCES Product (PReference_No)

10
);
--7. Shipping_Method Table
CREATE TABLE Shipping_Method
(
SM_No VARCHAR2 (10) PRIMARY KEY,
SM_Name VARCHAR2 (50) NOT NULL,
Charges NUMBER (10,2) NOT NULL
);
--8. Orders Table
CREATE TABLE Orders
(
Order_No VARCHAR2 (10) PRIMARY KEY,
Order_Date DATE NOT NULL,
PostPack_Insurance NUMBER (10,2) NOT NULL,
Despatch_To VARCHAR2 (50) NOT NULL,
Total_Amount NUMBER (10,2) NOT NULL,
SM_No VARCHAR2 (10),
FOREIGN KEY (SM_No) REFERENCES Shipping_Method (SM_No),
FOREIGN KEY (Order_No) REFERENCES Basket (Basket_No)
);

11
--9. Delivery_Item Table
CREATE TABLE Delivery_Item
(
Basket_No VARCHAR2 (10) NOT NULL,
PReference_No VARCHAR2 (10) NOT NULL,
Expected_Date DATE NOT NULL,
Delivery_Date DATE,
PRIMARY KEY (Basket_No, PReference_No),
FOREIGN KEY (Basket_No, PReference_No) REFERENCES Basket_Product (Basket_No,
PReference_No)
);
Chapter 4: Implementation
-----------------CREATE TABLES ------------------
-----------------1. Category Table---------------
CREATE TABLE Category
(
Category_No VARCHAR2 (10) PRIMARY KEY,
CG_Name VARCHAR2 (50) NOT NULL,
Description LONG
);
----------------2. Sub_Category Table--------------

12
CREATE TABLE Sub_Category
(
SC_No VARCHAR2 (10) PRIMARY KEY,
Category_No VARCHAR2 (10) NOT NULL,
SC_Name VARCHAR2 (50) NOT NULL,
Description LONG,
FOREIGN KEY (Category_No) REFERENCES Category (Category_No)
);
----------------3. Product Table---------------
CREATE TABLE Product
(
PReference_No VARCHAR2 (10) PRIMARY KEY,
SC_No VARCHAR2 (10) NOT NULL,
PName VARCHAR2 (50) NOT NULL,
Description LONG,
Price NUMBER (10,2) NOT NULL,
Quantity NUMBER (10) NOT NULL,
FOREIGN KEY (SC_No) REFERENCES Sub_Category (SC_No)
);
----------------4. Customer Table-----------
CREATE TABLE Customer
(
UserName VARCHAR2 (10) PRIMARY KEY,

13
Password VARCHAR2 (10) NOT NULL,
Title VARCHAR2 (5) CHECK (Title IN ('Mr','Mrs','Miss')),
Initials VARCHAR2 (20) NOT NULL,
Last_Name VARCHAR2 (20) NOT NULL,
HN_Street VARCHAR2 (20) NOT NULL,
Town VARCHAR2 (20) NOT NULL,
PostCode VARCHAR2 (10) NOT NULL,
TelePhone VARCHAR2 (12) NOT NULL,
Email VARCHAR2 (50) NOT NULL
);
--------------5. Basket Table--------------
CREATE TABLE Basket
(
Basket_No VARCHAR2 (10) PRIMARY KEY,
UserName VARCHAR2 (10) NOT NULL,
Create_Date DATE NOT NULL,
Status VARCHAR2 (15) CHECK (Status IN ('Ordered','Not Ordered')) NOT NULL,
FOREIGN KEY (UserName) REFERENCES Customer (UserName)
);
-------------6. Basket_Product Table ----------
CREATE TABLE Basket_Product
(
Basket_No VARCHAR2 (10) NOT NULL,

14
PReference_No VARCHAR2 (10) NOT NULL,
Quantity NUMBER (10) NOT NULL,
Add_Date DATE NOT NULL,
PRIMARY KEY (Basket_No, PReference_No),
FOREIGN KEY (Basket_No) REFERENCES Basket (Basket_No),
FOREIGN KEY (PReference_No) REFERENCES Product (PReference_No)
);
---------------7. Shipping_Method Table-------------
CREATE TABLE Shipping_Method
(
SM_No VARCHAR2 (10) PRIMARY KEY,
SM_Name VARCHAR2 (50) NOT NULL,
Charges NUMBER (10,2) NOT NULL
);
---------------8. Orders Table-------------
CREATE TABLE Orders
(
Order_No VARCHAR2 (10) PRIMARY KEY,
Order_Date DATE NOT NULL,
PostPack_Insurance NUMBER (10,2) NOT NULL,
Despatch_To VARCHAR2 (50) NOT NULL,
Total_Amount NUMBER (10,2) NOT NULL,
SM_No VARCHAR2 (10),

15
FOREIGN KEY (SM_No) REFERENCES Shipping_Method (SM_No),
FOREIGN KEY (Order_No) REFERENCES Basket (Basket_No)
);
--------------9. Delivery_Item Table---------
CREATE TABLE Delivery_Item
(
Basket_No VARCHAR2 (10) NOT NULL,
PReference_No VARCHAR2 (10) NOT NULL,
Expected_Date DATE NOT NULL,
Delivery_Date DATE,
PRIMARY KEY (Basket_No, PReference_No),
FOREIGN KEY (Basket_No, PReference_No) REFERENCES Basket_Product (Basket_No, PReference_No)
);

16
(Techonthenet.com, 2019)
------------INSERT DATA -------------
-----------------1. Category Table---------------
INSERT INTO Category VALUES ('C101', 'Kitchenware', NULL);
INSERT INTO Category VALUES ('C102', 'Cleaning Products', NULL);
INSERT INTO Category VALUES ('C103', 'Storage Solutions', NULL);
INSERT INTO Category VALUES ('C104', 'Marys favourites', NULL);
----------------2. Sub_Category Table--------------
INSERT INTO Sub_Category VALUES ('SC101','C103','Bathroom', NULL);
INSERT INTO Sub_Category VALUES ('SC102','C103','Bedroom', NULL);

17
INSERT INTO Sub_Category VALUES ('SC103','C103','Floors and Doors', NULL);
INSERT INTO Sub_Category VALUES ('SC104','C103','Garden Shed and Garage', NULL);
INSERT INTO Sub_Category VALUES ('SC105','C103','Home Office and Crafts', NULL);
INSERT INTO Sub_Category VALUES ('SC106','C103','In the Kitchen', NULL);
INSERT INTO Sub_Category VALUES ('SC107','C103','Living Room', NULL);
INSERT INTO Sub_Category VALUES ('SC108','C103','On Your Travels', NULL);
INSERT INTO Sub_Category VALUES ('SC109','C103','Protect It', NULL);
INSERT INTO Sub_Category VALUES ('SC110','C103','Recycling and Waste Bags', NULL);
INSERT INTO Sub_Category VALUES ('SC111','C103','Shopping Made Easy', NULL);
INSERT INTO Sub_Category VALUES ('SC112','C103','Space Saving Solutions', NULL);
----------------3. Product Table---------------
INSERT INTO Product VALUES ('7721','SC106','Anti-bacterial Multi-purpose', NULL,222.00,10);
INSERT INTO Product VALUES ('7722','SC106','Anti-bacterial Fresh Cloths', NULL,422.00,20);
INSERT INTO Product VALUES ('7723','SC106','Big Clip', NULL,1000.00,12);
INSERT INTO Product VALUES ('7724','SC106','Brabantia Mini Bin', NULL,800.00,13);
INSERT INTO Product VALUES ('7728','SC106','Brabantia Slimline Bin','Brabantia Slimline Bin
Waste bins donot come any better than this. This bin has a large capacity yet takes up little space.
It has a soft touch lid that opens and closes with the slightest touch and keeps the odors in. Finally,
its of superb quality in smart brushed stainless steel with a removable inner liner for easy emptying and
cleaning.
30 litre capacity.',61.50,1);
INSERT INTO Product VALUES ('7729','SC106','Brabantia Twin Bin', NULL,900.00,14);
----------------4. Customer Table-----------

18
INSERT INTO Customer VALUES ('Kani56','KUOJ','Mr','Kani','Smith','hui 8901','Jh','89091','6789876781',
'[e-mail]');
INSERT INTO Customer VALUES ('Meena789','KUO2','Miss','Meena','Smith','hui
8902','Jk','89092','6789876782', '[e-mail]');
INSERT INTO Customer VALUES ('Jack45g7','KU34','Mr','Jack','Smith','hui 8903','Ju','89093','6789876783',
'[e-mail]');
INSERT INTO Customer VALUES ('Reena890','KU67','Miss','Reena','Smith','hui
8904','Ji','89094','6789876784', '[e-mail]');
--------------5. Basket Table--------------
INSERT INTO Basket VALUES ('BS101','Kani56','01-JAN-2018','Not Ordered');
INSERT INTO Basket VALUES ('BS102','Meena789','01-JAN-2018','Ordered');
INSERT INTO Basket VALUES ('BS103','Jack45g7','01-JAN-2017','Not Ordered');
INSERT INTO Basket VALUES ('BS104','Reena890','01-JAN-2018','Ordered');
-------------6. Basket_Product Table ----------
INSERT INTO Basket_Product VALUES ('BS101','7721',2,'01-MAY-2018');
INSERT INTO Basket_Product VALUES ('BS102','7724',2,'02-MAY-2018');
INSERT INTO Basket_Product VALUES ('BS103','7721',2,'03-MAY-2018');
INSERT INTO Basket_Product VALUES ('BS104','7724',2,'04-MAY-2018');
INSERT INTO Basket_Product VALUES ('BS102','7722',2,'02-MAY-2018');
INSERT INTO Basket_Product VALUES ('BS102','7723',2,'02-MAY-2018');
---------------7. Shipping_Method Table-------------
INSERT INTO Shipping_Method VALUES('SM101','HJKNK',89.00);
INSERT INTO Shipping_Method VALUES('SM102','HJKNQ',19.00);
INSERT INTO Shipping_Method VALUES('SM103','HJKNE',29.00);
INSERT INTO Shipping_Method VALUES('SM104','HJKNS',39.00);

19
---------------8. Orders Table-------------
INSERT INTO Orders VALUES ('BS101','10-MAY-2018',10.00,'HJUN 897 US',879.09, NULL);
INSERT INTO Orders VALUES ('BS102','11-MAY-2018',12.00,'HJNL 897 US',479.09,'SM101');
INSERT INTO Orders VALUES ('BS103','12-MAY-2018',13.00,'YUIH 897 US',679.09,'SM103');
INSERT INTO Orders VALUES ('BS104','13-MAY-2018',14.00,'ERDFT 897 US',7879.09, NULL);
--------------9. Delivery_Item Table---------
INSERT INTO Delivery_Item VALUES ('BS101','7721','12-MAY-2018','14-MAY-2018');
INSERT INTO Delivery_Item VALUES ('BS102','7724','13-MAY-2018','15-MAY-2018');
INSERT INTO Delivery_Item VALUES ('BS103','7721','14-MAY-2018','16-MAY-2018');
INSERT INTO Delivery_Item VALUES ('BS104','7724','15-MAY-2018','17-MAY-2018');

20
(Guru99.com, 2019)
Chapter 5: Queries
--1. Display Product Detail with their subcategory and category--
SELECT Product.PReference_No, Product.PName as "Product Name", Product.Quantity, Product.Price,
Sub_Category.SC_Name as "Sub Category", Category.CG_Name as "Category"
FROM Product, Sub_Category, Category
WHERE Product.SC_No=Sub_Category.SC_No
AND Sub_Category.Category_No=Category.Category_No;

21
(TutorialsPoint, 2019)
--2. Display Those customers who ordered 'Anti-bacterial Multi-purpose' Product--
SELECT *From Customer WHERE UserName IN
(SELECT Basket.UserName FROM Basket, Orders, Basket_Product, Product, Customer
WHERE Customer.UserName=Basket.UserName
AND Basket.Basket_No=Orders.Order_No
AND Basket.Basket_No=Basket_Product.Basket_No
AND Product.PReference_No=Basket_Product.PReference_No
AND PName='Anti-bacterial Multi-purpose');
--3. Display information of 'Miss Meena Smith' customer's Basket--

22
SELECT Basket.Basket_No, Basket.Create_Date, Basket.Status, Product.PReference_No,
Product.PName as "Product Name", Basket_Product.Quantity, Basket_Product.Add_Date, Product.Price,
(Basket_Product.Quantity*Product.Price) AS "Total Cost"
FROM Customer, Basket, Basket_Product, Product
WHERE Customer.UserName=Basket.UserName
AND Basket.Basket_No=Basket_Product.Basket_No
AND Product.PReference_No=Basket_Product.PReference_No
AND Customer.Title='Miss'
AND Customer.Initials='Meena'
AND Customer.Last_Name='Smith';
--4. Display those orders those use any shipment method--
SELECT *FROM Orders
WHERE SM_No IS NOT NULL;

23
--5. Display Customers who Create Basket in 2018--
SELECT *FROM Customer WHERE UserName IN
(SELECT UserName FROM Basket
WHERE Extract(Year FROM Create_Date)=2018);
Conclusion
In this assignment, we learned about how to analyze the case study and create a class diagram
from it. I also learned about how to convert a class diagram into a Relational model. We have

24
implemented a database and tested our database by using some queries. I noticed that this way of
storing the database is easier as compared to storing data in an excel workbook.
In the first exercise, we analyzed the given case study and by using this case study we created a
class diagram. This class diagram includes classes, their attributes, operation and establishes the
relationship between them. In the relational model, I have converted the class diagram into a
relational model. In this model, I have constructed the tables, their attributes, and the relationship
between them. I have also normalized the database up to 3NF. In the next exercise, I have
implemented the database according to the prepared relational model. In the implementation, I
am creating tables and adding some sample data into tables. In the last exercise, I have tested our
database by using various queries.
We have used an object-oriented approach while designing the class diagram for the application.
This application will have a number of objects and each object will have a number of attributes
and operations. All the attributes and operations of each object are binded together into classes.
There are various relationships between different classes and we have used the concept of
abstraction and encapsulation in this system.
Also, we have used Oracle for implementing the database. We have used the prepared class
diagram to implement the database into Oracle. We have designed the application in such a way
that it can be further extended if required. We can add any number of products into the database
and the application will work fine without any errors or duplication of data.
If our database design and class diagram are used for the application design, it will simplify the
shopping experience for all the users.

25
References: -
Guru99.com. (2019). [online] Available at: https://www.guru99.com/pl-sql-tutorials.html [Accessed 15 May
2019].
Techonthenet.com. (2019). Oracle Tutorial. [online] Available at:
https://www.techonthenet.com/oracle/index.php [Accessed 15 May 2019].
TutorialsPoint. (2019). Oracle SQL. [online] Available at:
https://www.tutorialspoint.com/oracle_sql/index.asp [Accessed 15 May 2019].
www.tutorialspoint.com. (2019). UML - Class Diagram. [online] Available at:
https://www.tutorialspoint.com/uml/uml_class_diagram.htm [Accessed 15 May 2019].