Database Design and Implementation for an E-commerce Website

Desklib provides past papers and solved assignments for students. This project focuses on e-commerce database design and implementation.

Contents
Introduction......................................................................................................................................2
LO1..................................................................................................................................................3
P1.................................................................................................................................................3
LO2..................................................................................................................................................5
P2.................................................................................................................................................5
P3.................................................................................................................................................5
LO3..................................................................................................................................................5
P4.................................................................................................................................................5
LO4..................................................................................................................................................5
P5.................................................................................................................................................5
Conclusion.......................................................................................................................................5
References........................................................................................................................................5
List of Figures
Figure 1: ER Diagram......................................................................................................................4
Figure 2: Product table Query..........................................................................................................7
Figure 3: Product table execution....................................................................................................7
Figure 4: Shopping_Cart table Query..............................................................................................8
Figure 5: Shopping_Cart table Execution........................................................................................8
Figure 6: Customers table Query.....................................................................................................9
Figure 7: Customers table Execution...............................................................................................9
Figure 8: Shipping table Query........................................................................................................9
Figure 9: Shipping table Execution.................................................................................................9
Figure 10: Pay table Query............................................................................................................10
Figure 11: Pay table Execution......................................................................................................10
Figure 12: Categories_of_Product table Query.............................................................................10
Figure 13: Categories_of_Product table Execution.......................................................................10
Figure 14: Query Number 1...........................................................................................................11
Figure 15: Query Number 2...........................................................................................................11
Figure 16: Query Number 3...........................................................................................................12
Figure 17: Query Number 4...........................................................................................................12
Figure 18: Query Number 5...........................................................................................................12
Figure 19: Query Number 6 (a).....................................................................................................13

Figure 20: Query Number 6 (b).....................................................................................................13
Figure 21: Query Number 7 (a).....................................................................................................13
Figure 22: Query Number 7 (b).....................................................................................................13
Figure 23: Query Number 7 (c).....................................................................................................14
Figure 24: Query Number 8...........................................................................................................14
Figure 25: Create Command example...........................................................................................16
Figure 26: Select Command example............................................................................................17
Figure 27: Update Command example..........................................................................................17
Figure 28: Delete Command example...........................................................................................17
Figure 29: Flowchart of the system...............................................................................................18

Introduction
As the businesses in today’s world are increasingly getting digital so as to provide an online
platform for the customers as well as for the owner of the business, it is important to keep track
of all the data and information about the business which can be possible with the help of an E-
Commerce site. This is the best possible way for a business to target more customers to increase
the sales and benefits to the business. So this particular assessment is specifically based on an
online E-commerce website of a fashion outlet expansion which is situated in London. In this
report, the database, queries, ER diagram and flowchart of working of the database is described
clearly and discussed thoroughly. There are various queries that are performed by using the
phpMyadmin and screenshots of all the implemented queries are mentioned in the report and
explained.

LO1
P1
Figure 1: ER Diagram
Assumptions
There are various assumptions that are taken into consideration while creating this particular ER
diagram:
 The entity Customer is related to categories entity with the one-to-many relationship as
for one particular category there can be multiple customers, therefore, this justifies the
relationship among them.
 In a single shopping cart, there can be multiple products so the entity product and
shopping cart are interconnected.
 For single product there can be many categories like for women dresses the category can
be, party wear, casuals, etc.
 Entities Shipping and pay are interconnected with one-to-many relationship with each
other.

 Similarly, shopping cart and entity pay are also interconnected with a one-to-many
relationship.
Moving on, let’s discuss all the entities in detail that are present in the above ER diagram:
a) Products:
 In the entity Products, the detail of the product is stored that are available on the
website.
 These details are present in the form of different attributes named as P_ID which
holds every unique ID for every different product, product name, Cart_ID, price,
and quantity of the product.
 Among all the mentioned attributes, P_ID is the primary key and Cart_ID is the
foreign key as it is taking reference from the entity shopping cart.
 This entity is interconnected with table shopping cart and table categories of the
product(Rodrigues, et al., 2018).
b) Shopping_cart:
 In this entity, there are four different attributes that help in the overall description
of this entity.
 These entities are Cart_ID, Pay_ID, C_ID and Amount.
 The attribute Cart_ID is the primary key and attributes Pay_ID and C_ID are
foreign keys as they are taking reference from other tables.
 The entity is interconnected with table products, customers and pay.
c) Categories_of_Products:
 This entity consists of three different attributes that are Category_ID, P_ID, and
type_of_product.
 This entity is interconnected with table customer by one-to-many relationship.
d) Customers:
 The table customer consists of different attributes named as C_ID, Category_ID,
Pay_ID, C_Name, C_Email, C_Contact, Username and passwords of the
customer to login into the website.
 From all these entities, C_ID is the primary key and Category_ID, Pay_ID are the
foreign keys.
 The table customer is interconnected with Shopping_cart,
Categories_of_products and Pay tables(Myint, et al., 2018).

e) Pay:
 The table Pay contains three different attributes named as Pay_ID, Total payment
and mode of payment.
 Among these three Pay_ID is the primary key as for every payment there will be
a unique ID.
 This entity is interconnected with the tables, shipping, shopping cart and the table
customers with a one-to-many relationship.
f) Shipping:
 The entity shipping is created to store the details of the shipping of the products.
 This entity contains four different attributes named as Ship_ID, Ship_Address,
Pay_ID, and Ship_date.
 From all the above-mentioned attributes, Ship_ID is the primary key as for every
shipping product there will be different identification.

LO2
P2
Database creation is one of the parts that occur after the Entity-Relationship Diagram gets
created. The creation of the database mainly occurs and is being created with the help of
Structured Query Language. SQL is the standard language that is mainly been used in all the
purposes. A database system is clearly defined as the fully functional where the relationships are
mentioned properly. Primary Keys and the Foreign Keys are also properly defined.
Structured Query Language can be abbreviated as SQL, which is properly meant for the
development and the query executions. It is the language that mainly works with structured data.
Structured data is properly defined as the combination of the columns and the rows and
containing appropriated relationships.
The above-made ER Diagram is properly executed and the database is created. The proper
screenshots are posted below:
Table Products
The below table is created using the Create command. Proper column names are given in order
to make the table clear and better. P_ID is the name given to the column and assigned as the
Primary Key also Cart_ID is assigned as the Foreign Key referenced from the table named
Shopping_Cart. Different column names are also given such as P_Name. Price and Quantity.
Figure 2: Product table Query
Figure 3: Product table execution

Table Shopping_Cart
The below table is created using the Create command. Proper column names are given in order
to make the table clear and better. Cart_ID is the name given to the column and assigned as the
Primary Key also Pay_ID and C_ID are assigned as the Foreign Keys referenced from the table
named Pay and Customer tables. Different column names are also given such as Amount is also
defined.
Figure 4: Shopping_Cart table Query
Figure 5: Shopping_Cart table Execution
Table Customers
The below table is created using the Create command. Proper column names are given in order
to make the table clear and better. C_ID is the name given to the column and assigned as the
Primary Key also Pay_ID and Category_ID are assigned as the Foreign Keys referenced from the
table named Pay and Category_of_products tables. Different column names are also given such
as C_Name, C_email, C_Contact, Username, Password is also defined.

Figure 6: Customers table Query
Figure 7: Customers table Execution
Table Shipping
The below table is created using the Create command. Proper column names are given in order
to make the table clear and better. Ship_ID is the name given to the column and assigned as the
Primary Key also Pay_ID is assigned as the Foreign Key referenced from the table named Pay
table. Different column names are also given such as Ship_Address, Ship_Date are also defined.
Figure 8: Shipping table Query
Figure 9: Shipping table Execution

Table Pay
The below table is created using the Create command. Proper column names are given in order
to make the table clear and better. Pay_ID is the name given to the column and assigned as the
Primary Key. Different column names are also given such as Method_of_Payment,
TotalPayment are also defined.
Figure 10: Pay table Query
Figure 11: Pay table Execution
Table Categories_of_Product
The below table is created using the Create command. Proper column names are given in order
to make the table clear and better. Category_ID is the name given to the column and assigned as
the Primary Key also P_ID is assigned as the Foreign Key referenced from the table named
Products table. Different column names are also given such as Type_of_Product is also defined.
Figure 12: Categories_of_Product table Query
Figure 13: Categories_of_Product table Execution

P3
All the data in the database created is in the form of the rows and the columns and defined as the
structured data. SQL is mainly being used to perform various operations such as CRUD. These
are mainly been done in order to make the execution of the queries. The processes of these are
queries are being passed in the database and mainly the process is passed through the backend.
Various queries mainly done with the help of the commands such as Select Create, Insert, Delete,
Drop, etc. The main purpose of performing the queries is to extract the required information in
less period of time.
Queries to be performed:
1.
Figure 14: Query Number 1
2.
Figure 15: Query Number 2
3.

Figure 16: Query Number 3
4.
Figure 17: Query Number 4
5.
Figure 18: Query Number 5
6.