Database Design and Implementation for an E-commerce Website

Desklib provides solved assignments and past papers to help students succeed.

Table of Contents
Introduction......................................................................................................................................2
LO1..................................................................................................................................................3
P1.................................................................................................................................................3
L02...................................................................................................................................................7
P2.................................................................................................................................................7
P3...............................................................................................................................................10
LO3................................................................................................................................................17
P4...............................................................................................................................................17
L04.................................................................................................................................................19
P5...............................................................................................................................................19
Conclusion.....................................................................................................................................21
List of Figures
Figure 1: ERD..................................................................................................................................4
Figure 2: Table Products..................................................................................................................8
Figure 3: Executed...........................................................................................................................8
Figure 4: Table CategoryOfItems....................................................................................................8
Figure 5: Executed...........................................................................................................................8
Figure 6: Table Employees..............................................................................................................9
Figure 7: Execution..........................................................................................................................9
Figure 8: Table Shippings................................................................................................................9
Figure 9: Execution..........................................................................................................................9
Figure 10: Table Payments............................................................................................................10
Figure 11: Executed.......................................................................................................................10
Figure 12: BasketOfShop..............................................................................................................10
Figure 13: Executed.......................................................................................................................10
Figure 14: Table Customer............................................................................................................11
Figure 15: Executed.......................................................................................................................11
Figure 16: Q1.................................................................................................................................12
Figure 17: Q2.................................................................................................................................12
Figure 18: Q3.................................................................................................................................13
Figure 19: Q4.................................................................................................................................13
Figure 20: Q5.................................................................................................................................13
Figure 21: Q6.................................................................................................................................14

Figure 22: Q7.................................................................................................................................14
Figure 23: Q8.................................................................................................................................15
Figure 24: TD 1.............................................................................................................................20
Figure 25: TD 2.............................................................................................................................20
Figure 26: TD 3.............................................................................................................................21
Figure 27: TD 4.............................................................................................................................21

Introduction
The given assessment is on the e-commerce website of the fashion company who need to expand
the business by providing more services to the customer and this is possible by using the online
website that is available for customer 24x7 that will increase the sale of the company as there is
no fixed time of closing the sales and products for the customer. By using this website the
customer can shop from anywhere and at any time so there is a surety of increment in the sale of
the business. For creating an e-commerce website, it is also necessary to maintain a database so
that all the transactions and sales of the business can be stored in the form of a database. The use
of a database makes the tracking and analysis of the business easier and efficient. So in this
particular report, the database is created after the creation of an ER diagram that will provide all
the details regarding the relationships of the tables and entities that are involved and the
dependencies as well. The use of structured query language is also there for creating the query
and implemented by using the phpMyadmin. The execution and outputs are provided in the form
of screenshots.

LO1
P1
Figure 1: ERD
User requirements
 The internet connectivity is essential to operate the website properly.
 The delivery of the product should be exactly on the customer’s address.
 Customer can select more than one product and can add the products in the cart which is
provided.
 There is a choice provided to the customer about the products either buying or taken on rent
so this provides great help to customers who are not willing to purchase the product but can
take on rent.
System requirements
 The connection with the server should be proper and strong.
 The website work on every window and browsers.

 There is a use of backups for the servers that are used so that they can automatically recover
in case of failures and losses.
Assumptions
 The entity Employee is connected to the entity Products and shows the one to many
relationship with it as One Employee can produce many products (Henry, R., 2017).
 The next relationship is shown between the entity BasketOfShop and Products where one
Basket can contain multiple Products.
 The next relationship is shown between the entity Payments and BasketOfShop where one
Payment can be made for multiple Baskets.
 The next relationship is shown between the entity Payments and Shippings where one
Payment can be made for multiple Shippings (Goelman, D. and Dietrich, S.W., 2018).
 The next relationship is shown between the entity Payments and Customer where one
Payment can be made by multiple Customers.
 The next relationship is shown between the entity CategoryOfItems and Customers where
one CategoryOfItems can be owned by multiple Customers.
 The next relationship is shown between the entity Products and CategoryOfItems where one
Product can be included in multiple CategoryOfItems.
 The next relationship is shown between the entity Customers and BasketOfShop where one
Customer can have multiple BasketOfShop.
Entities defined:
Entity Products
 The Products entity contains the P_ID as a Primary Key in order to uniquely identify the
information of each of the Product.
 The other attributes are B_ID, P_Name, P_Price, and TotalQuantity.
 The relationships shown here is between the entity Products and CategoryOfItems where one
Product can be included in multiple CategoryOfItems. The other relationship defined here is,
between the entity BasketOfShop and Products where one Basket can contain multiple
Products.
 B_ID is the foreign key defined in this entity.
Entity CategoryOfItems
 The CategoryOfItems entity contains the Ctgry_ID as a Primary Key in order to uniquely
identify the information of each of the Category.

 The other attributes are Paymnt_ID, TypeOfProduct, and Type_Purchase.
 The next relationship is shown between the entity CategoryOfItems and Customers where
one CategoryOfItems can be owned by multiple Customers.
 Paymnt_ID is the foreign key defined in this entity.
Entity Employee
 The Employee entity contains the Empl_ID as a Primary Key in order to uniquely identify
the information of each of the Employee.
 The other attributes are Empl_Name, Empl_Contact, Empl_Address, and Empl_Email.
 The entity Employee is connected to the entity Products and shows the one to many
relationship with it as One Employee can produce many products.
Entity Shippings
 The Shippings entity contains the S_ID as a Primary Key in order to uniquely identify the
information of each of the Shippings.
 The other attributes are Paymnt_ID, Address, and ShippingDate.
 Paymnt_ID is the foreign key defined in this entity.
Entity Payments
 The Payments entity contains the Paymnt_ID as a Primary Key in order to uniquely identify
the information of each of the Payments.
 The other attributes are FullAmount and ModeOfPayment.
 The relationship is shown between the entity Payments and Shippings where one Payment
can be made for multiple Shippings. The next relationship is shown between the entity
Payments and Customer where one Payment can be made by multiple Customers. The other
relationship is shown between the entity Payments and BasketOfShop where one Payment
can be made for multiple Baskets.

Entity BasketOfShop
 The BasketOfShop entity contains the B_ID as a Primary Key in order to uniquely identify
the information of each of the Baskets.
 The other attributes are Paymnt_ID, Cstmr_ID, and TotalPrice (Olaleye, K., 2017).
 The relationship is shown between the entity BasketOfShop and Products where one Basket
can contain multiple Products.
 Paymnt_ID and Cstmr_ID are the foreign keys defined in this entity.
Entity Customer
 The Customer entity contains the Cstmr_ID as a Primary Key in order to uniquely identify
the information of each of the Customers.
 The other attributes are Paymnt_ID, Ctgry_ID, Cstmr_Name, Cstmr_Contact,
Cstmr_Address and Cstmr_Email.
 The relationship is shown between the entity Customers and BasketOfShop where one
Customer can have multiple BasketOfShop.
 Paymnt_ID and Ctgry_ID are the foreign keys defined in this entity.

L02
P2
For creating the database based on the above made ERD. The software named as phpMyAdmin
is being used and queries are also being performed.
Table Products
The Products table contains the P_ID as a Primary Key in order to uniquely identify the
information of each of the Product, it contains integer data type. The other columns are B_ID
that too contains integer datatype; P_Name contains the Varchar datatype, P_Price contains
integer datatype, and TotalQuantity also contains integer datatype. B_ID is the foreign key
defined in this table that is referenced from the BasketOfShop table.
Figure 2: Table Products
Figure 3: Executed
Table CategoryOfItems
The CategoryOfItems table contains the Ctgry_ID as a Primary Key in order to uniquely identify
the information of each of the Category, it contains integer data type. The other columns are
Paymnt_ID contains integer datatype, TypeOfProduct contains the Varchar datatype, and
Type_Purchase contains the Varchar datatype. Paymnt_ID is the foreign key defined in this table
that is referenced from the Payments table.
Figure 4: Table CategoryOfItems

Figure 5: Executed
Table Employee
The Employee table contains the Empl_ID as a Primary Key in order to uniquely identify the
information of each of the Employee contains the Integer data type. The other columns are
Empl_Name contains the Varchar datatype, Empl_Contact contains the Integer data type,
Empl_Address contains the Varchar datatype and Empl_Email contains the Varchar datatype.
Figure 6: Table Employees
Figure 7: Execution
Table Shippings
The Shippings table contains the S_ID as a Primary Key in order to uniquely identify the
information of each of the Shippings and contains the Integer data type. The other attributes are
Paymnt_ID contains the Integer data type, Address contains the Varchar datatype, and
ShippingDate contains Data datatype. Paymnt_ID is the foreign key defined in this entity that is
referenced from the Payments table.
Figure 8: Table Shippings
Figure 9: Execution

Table Payments
The Payments table contains the Paymnt_ID as a Primary Key in order to uniquely identify the
information of each of the Payments and contains the Integer data type. The other attributes are
FullAmount contains the Integer datatype and ModeOfPayment contains the Varchar datatype.
Figure 10: Table Payments
Figure 11: Executed
Table BasketOfShop
The BasketOfShop table contains the B_ID as a Primary Key in order to uniquely identify the
information of each of the Baskets and contains the Integer data type. The other attributes are
Paymnt_ID contains the Integer datatype; Cstmr_ID contains the Integer datatype and TotalPrice
and contains the Integer data type. Paymnt_ID and Cstmr_ID are the foreign keys defined in this
entity are referenced from the Payments table and Customer table respectively.
Figure 12: BasketOfShop
Figure 13: Executed
Table Customer
The Customer table contains the Cstmr_ID as a Primary Key in order to uniquely identify the
information of each of the Customers and contains the Integer data type. The other attributes are

Paymnt_ID contains the Integer data type, Ctgry_ID contains the Integer data type, Cstmr_Name
contains the Varchar datatype, Cstmr_Contact contains the Integer data type, Cstmr_Address
contains the Varchar datatype and Cstmr_Email contains the Varchar datatype. Paymnt_ID and
Ctgry_ID are the foreign keys defined in this entity are referenced from the Payments table and
CategoryOfItems tables respectively.
Figure 14: Table Customer
Figure 15: Executed
P3
In order to fetch the data from a large amount of data present, Queries are used. Queries help in
fetching a large amount of data in less amount of time (Calvanese et al., 2017). So here, the
following queries are performed in order to get the proper output and that is required.
Queries-
(a) SELECT `FIRST_NAME`,`LAST_NAME`,`GENDER`,`SALARY`
FROM `EMPLOYEES` INNER JOIN `SALARIES`
WHERE `EMPLOYEES`.`EMP_NO` = `SALARIES`.`EMP_NO`

Figure 16: Q1
(b) SELECT `FIRST_NAME`,`LAST_NAME`,`TITLE`,`DEPT_NAME`
FROM `EMPLOYEES` INNER JOIN `DEPARTMENTS` INNER JOIN `DEPT_EMP` INNER
JOIN `TITLES`
WHERE`EMPLOYEES`.`EMP_NO`=`DEPT_EMP`.`EMP_NO`
AND`DEPT_EMP`.`DEPT_NO`=`DEPARTMENTS`.`DEPT_NO`
AND`EMPLOYEES`.`EMP_NO`=`TITLES`.`EMP_NO`
Figure 17: Q2
(c) SELECT `FIRST_NAME`, `LAST_NAME`, `GENDER`
FROM `EMPLOYEES` INNER JOIN `DEPARTMENTS` INNER JOIN `DEPT_EMP`
WHERE `EMPLOYEES`.`EMP_NO`=`DEPT_EMP`.`EMP_NO` AND
`DEPT_EMP`.`DEPT_NO`=`DEPARTMENTS`.`DEPT_NO` AND
`DEPARTMENTS`.`DEPT_NO`= 'd004'