Database Design for Online Shopping

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.