Database Design for E-commerce Fashion Outlet Expansion: A Case Study

Desklib provides solved assignments and past papers to help students excel in their studies. Download this solved database design assignment now!

Contents
Introduction......................................................................................................................................2
LO1..................................................................................................................................................3
P1.................................................................................................................................................3
LO2..................................................................................................................................................6
P2.................................................................................................................................................6
P3...............................................................................................................................................11
LO3................................................................................................................................................17
P4...............................................................................................................................................17
LO4................................................................................................................................................19
P5...............................................................................................................................................19
Conclusion.....................................................................................................................................21
References......................................................................................................................................22
Figure 1: ER diagram.......................................................................................................................4
Figure 2: Item table creation............................................................................................................8
Figure 3: Item table execution.........................................................................................................8
Figure 4: Item_Category creation....................................................................................................9
Figure 5: Item_Category execution.................................................................................................9
Figure 6: Employee table creation...................................................................................................9
Figure 7: Employee table execution................................................................................................9
Figure 8: Shop_Basket creation.....................................................................................................10
Figure 9: Shop_basket execution...................................................................................................10
Figure 10: Payment table creation.................................................................................................10
Figure 11: Payment table execution..............................................................................................10
Figure 12: Shipping table creation.................................................................................................11
Figure 13: Shipping table execution..............................................................................................11
Figure 14: Customer table creation................................................................................................12
Figure 15: Customer table execution.............................................................................................12
Figure 16: Output query 1..............................................................................................................13
Figure 17: Output query 2..............................................................................................................13
Figure 18: Output query 3..............................................................................................................14
Figure 19: Output query 4..............................................................................................................14
Figure 20: Output query 5..............................................................................................................15

Figure 21: Output query 6 (i).........................................................................................................15
Figure 22: Output query 6 (ii)........................................................................................................15
Figure 23: Output query 7 (i).........................................................................................................16
Figure 24: Output query 7 (ii)........................................................................................................16
Figure 25: Output query 7 (iii).......................................................................................................16
Figure 26: Output query 8..............................................................................................................17
Figure 27: Create command...........................................................................................................21
Figure 28: Select command...........................................................................................................21
Figure 29: Update command.........................................................................................................22
Figure 30: Delete command...........................................................................................................22

Introduction
The report is based on the case scenario which is based on the e-commerce website that is
located in London and is a fashion outlet expansion; this e-commerce website is adopted by the
company so as to increase the sale as well as the profit for the company. The e-commerce
website is one of the optimal solutions to expand the business and get the profit as it is available
for customers all the time, so for maintaining website data there is a requirement of the database
system. This report contains the database which is related on this fashion expansion, the queries
are given which are implemented by using phpMyadmin, an ER diagram, as well as a flowchart,
is provided so as to make the reader understand about the working of the website. A discussion is
made on every point covered in the report so that understanding can be enhanced about the work
which is performed.

LO1
P1
Figure 1: ER diagram
User Requirements
 The customer can order multiple items and add them to the shopping basket.
 The item that needs to be delivered should be on the exact address of the customer.
 From the items, there is an option that the customer can either buy the item or the
customer can take the item on rent. This option is shown with the attribute
Type_of_purchase that is present in table Items_Category.

 For accessing all the features of the website it is important that the customer should have
proper internet connectivity.
System Requirements
 For the website to avoid high traffic and proper working, the website should have a
proper connection with the server.
 The website works on Windows, Linux, and all other windows.
 For avoiding any type of failures, use of backups should be done for servers which by
default carry out the operations.
Assumptions
While the creation of the entity relationship diagram of the given case study, there are some
assumptions that are taken into consideration and are discussed below:
 One item has multiple categories so the table “’ Items” is interconnected with one-to-
many relationship with the table “Items_category”.
 The customer can choose multiple items and add them in the shopping basket so there is a
connection of one-to-many relationship between the tables “Items” and “Shop_Basket”.
 One employee can sell more than one items.
 Customer can have multiple shopping baskets.
 On a single payment, multiple shipping is possible so the entities “Payment” and
“Shipping” are connected with each other.
 One mode of payment can be used by more than one customer so the table is connected
with each other.
 For one shopping basket, there can be one single payment.
 One item category can be selected by multiple customers.
Tables
The tables present in the entity relationship diagram are connected with each other and every
table is explained here:
1. Employees
 Employee table is interconnected with table Items and has many different
attributes that are Emp_Id, Emp_name, Emp_contact, Emp_address, Emp_email.
This table is created so that all the information of employee can be stored.
 From these attributes, Emp_Id is assigned as a primary key so that every
employee can be identified individually with a unique id.
 This is connected with a one-to-many relationship with the table items.

2. Items
 The table item has a different attribute named as Item_Id, Basket_Id, Item_name,
Item_price, and Quantity.
 From these items, Item_Id is the primary key for the unique identification of
every item.
 The attribute Basket_Id is the foreign key in this table as this attribute is taking
reference from the Shop_Basket table.
 This table is interconnected with other tables which are Item_Category,
Shop_Basket, and Employees.
3. Items_Category
 This table is created so as to store the data of the categories of items that are
present.
 The table has Categ_Id, Payment_Id, Product_type, and Type_of_purchase
attributes.
 From all these, Categ_Id is the primary key and Payment_Id is the foreign key as
it is taking reference from table Payment.
 This table is created so as to store the information of the purchase as a customer
can either buy the item or can take the item on rent so Type_of_purchase attribute
is created in this table.
 This table has a relationship with table Items.
4. Shop_Basket
 This table is created so that the customer can store the multiple items selected in
one place.
 This table has attributes Basket_Id, Payment_Id, Customer_Id and Total Amount.
 The attribute Basket_Id is the primary key here and Payment_Id, Customer_id are
the foreign keys.
 It is connected with table Payment, Items, Customers.
5. Payment
 This table is created so that the information regarding the payments can be stored
and can be taken care of.
 The table payment has many attributes that include Payment_Id, Total Amount
and Payment_mode.
 From these attributes, the attribute Payment_Id is the primary key because for
every payment of customer there should be unique identification which can be
identified by the payment id.
 Payment table is connected with the table Shipping, Customers, Shop_basket.

 For selecting different modes of payment there is a different attribute named as
Payment_mode through which different modes of payment can be chosen by the
customer according to their suitability.
 The attribute total amount is created so as to provide the information on the grand
total of chosen items.
6. Customers
 This table is created so that the information about the customer can be stored.
 This table has many attributes named as Customer_Id, Categ_Id, Payment_Id,
Customer_name, Customer_contact, Customer_address, Customer_email,
username and password.
 Among all these attributes the attribute Customer_id is the primary key for every
customer it is different and unique.
 The attributes Categ_Id and Payment_Id are the foreign keys here as they both are
taking reference from another table.
 This table has multiple relationships with other tables named as Shop_basket,
Payment, items_category (Coronel and Morris, 2016).
7. Shipping
 This table is created to keep the record of shipping information of the items.
 The attributes that are present in this table is Shipping_Id, Payment_Id,
Address_of_Shipping, and Date_of_shipping.
 From these attributes, Shipping_Id is primary key and the Payment_Id is the
foreign ket.
 This table is connected with table Payment as for one payment multiple shipping
is possible.
LO2
P2
After the Entity relationship diagram is created, another task is to create the database and this is
done by using the structured query language. This language is used to basically access the
databases and extract essential information. A database system is the management of the data
that is stored and is fully functioned system in which there are properly specified relationships
are mentioned. All other constraints are also clearly mentioned that include the primary keys as
well as the foreign keys. The language used is SQL that is used for the execution of the queries
and only functions appropriately with the data that is properly structured. The structured data is

the data in which the columns as well as the rows are integrated and arranged properly and
contains the relationship clearly. For the scenario, the ERD is already created and the database is
created by using the query language so there are the screenshots that justify the creation of the
database:
Items table
The table “Items” is created with the help of creating command query and there are different
attributes that are present in this table which include the Item_Id, Basket_Id, Item_name,
Item_price, and Quantity. These are the attributes that are included in this table. Different data
type and constraints are shown in this screenshot. This is created by using phpMyadmin.
Figure 2: Item table creation
Figure 3: Item table execution
Items_Category table
Table Items_category has different attributes that are _Id, Payment_Id, Product_type, and
Type_of_purchase. These attributes have different data types some are of varchar and some are
of integer type. Using the columns the table is created by using the phpmyadmin and the query is
given below which is implemented. After the execution, the screenshot is provided below:

Figure 4: Item_Category creation
Figure 5: Item_Category execution
Employees table
The table employee is given below which has many different attributes named as Emp_Id,
Emp_name, Emp_contact, Emp_address, Emp_email. All these attributes have different data
types like varchar or integer. The attribute emp_id is assigned as primary key and on combining
the attributes, a table is created and after that, the execution is given in the form of a screenshot.
Figure 6: Employee table creation
Figure 7: Employee table execution

Shop_Basket table
The table shop_basket is created by combining the other attributes named as Basket_Id,
Payment_Id, Customer_Id and Total Amount. From all these attributes they have different data
types of varchar and integer. These are combined and executed by using the creation command
and the execution is given in the form of a screenshot (Ceri, 2017).
Figure 8: Shop_Basket creation
Figure 9: Shop_basket execution
Payment table
The table payment is created by the use of creation query in which there are different attributes
that are Payment_id, total amount and payment_mode (Parisi and Grant, 2019). These attributes
have different data types like integer and varchar. Constraint like the primary key is also
mentioned in the provided screenshot. The query used is given below with the screenshot:
Figure 10: Payment table creation
Figure 11: Payment table execution

Shipping table
The shipping table is created so as to store the information regarding the shipping details and
there are various attributes that are present that include the Shipping_id, Payment_Id,
Address_of_Shipping, and Date_of_shipping. All these attributes have different data types that
are varchar, integer, and date. Constraint like primary is also added which is the shipping_id in
this table and the query used is given below with the execution screenshot (Yang and Cao, 2016).
Figure 12: Shipping table creation
Figure 13: Shipping table execution
Customers table
The table customer is given so as to store the data of the customers that sign up for buying the
products of the website. There are attributes that hold the information of the customers and they
are Customer_Id, Categ_Id, Payment_Id, Customer_name, Customer_contact,
Customer_address, Customer_email, username, and password. All these attributes have different
data types like varchar and integer. The constraint of the primary key is also included in the table
and the query used is given along with the screenshot of the execution of the query (Lu, et al.,
2015).

Figure 14: Customer table creation
Figure 15: Customer table execution
P3
The data in any database is stored and properly organized in the form of rows as well as in the
columns and this is referred to as the structured data. The most common operation is known as
CRUD is performed by the help of SQL that is structured query language. This is performed so
as to make the queries execute and get the output of the queries. The process is performed with
the backend after the queries get passed. The commands are used in order to perform the queries
that are select, insert, delete, create, drop and many more. These queries are performed so to
extract the essential information from the database that is created in the minimum time.
Queries:
(a) SELECT `FIRST_NAME`,`LAST_NAME`,`GENDER`,`SALARY`
FROM `EMPLOYEES` Natural JOIN `SALARIES`
WHERE `EMPLOYEES`.`EMP_NO` = `SALARIES`.`EMP_NO`