Database Design and Implementation for an E-commerce Fashion Outlet

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

Contents
Introduction......................................................................................................................................2
LO1..................................................................................................................................................3
P1.................................................................................................................................................3
LO2..................................................................................................................................................6
P2.................................................................................................................................................6
P3...............................................................................................................................................11
LO3................................................................................................................................................18
P4...............................................................................................................................................18
LO4................................................................................................................................................20
P5...............................................................................................................................................20
Conclusion.....................................................................................................................................23
References......................................................................................................................................24
Figure 1: Entity Relationship Diagram............................................................................................4
Figure 2: Creation query for Customer............................................................................................8
Figure 3: Customer table execution.................................................................................................8
Figure 4: Creation query for Employees.........................................................................................9
Figure 5: Employees table execution...............................................................................................9
Figure 6: Creation query for Payments............................................................................................9
Figure 7: Payments table execution...............................................................................................10
Figure 8: Creation query for Products...........................................................................................10
Figure 9: Products table execution................................................................................................10
Figure 10: Creation query for Products_Category.........................................................................11
Figure 11: Products_Category table execution..............................................................................11
Figure 12: Creation query for Shopping_Basket...........................................................................11
Figure 13: Shopping_Basket table execution................................................................................11
Figure 14: Creation query for Shipping.........................................................................................12
Figure 15: Shipping table execution..............................................................................................12
Figure 16: Query 1 execution........................................................................................................13
Figure 17: Query 2 execution........................................................................................................13
Figure 18: Query 3 execution........................................................................................................14
Figure 19: Query 4 execution........................................................................................................14
Figure 20: Query 5 execution........................................................................................................14

Figure 21: Query 6(i) execution....................................................................................................15
Figure 22: Query 6 (ii) executions.................................................................................................15
Figure 23: Query 7 (i) execution...................................................................................................16
Figure 24 : Query 7 (ii) execution.................................................................................................16
Figure 25 : Query 7 (iii) execution................................................................................................16
Figure 26: Query 8 execution........................................................................................................17
Figure 27: SELECT.......................................................................................................................22
Figure 28: CREATE......................................................................................................................22
Figure 29: UPDATE......................................................................................................................23
Figure 30: DELETE.......................................................................................................................23

Introduction
The assessment is about the database creation of a website which relies on a fashion outlet and
contains product related to fashion. The customer has the option of either purchasing the item or
to take on rent. In both the cases the company earns profit and the company can remain available
every time as the website is available on the browser that can be used anytime and anywhere.
This report has the crow’s foot notation that explains all the dependencies of the table with the
help of arrows. And constraints are also added in the crow’s foot notation. The use of structured
query language is also shown by implementing different queries on the phpmyadmin and the
output is displayed in the while with the help of screenshots. The overall report will provide a
strong understanding of the database as well as the query language will also be learned through
this particular assessment.

LO1
P1
Figure 1: Entity Relationship Diagram
Requirements
User requirements
 Customer can buy products in the sale by only one account.
 The total amounts that need to pay for the purchased products are only made by one
payment card.

 The customer has two options: either buying the item and another is he/she can take on
the rent also.
 The customer has option to add multiple products in one shopping basket or shopping
cart (Grunfeld, et al., 2006).
 The customer should have an account on the website for accessing the website and to
purchase products.
 The customer needs an internet connection to access the website.
 Customer should enter a correct address while entering details so that item can be shipped
on the right address of the customer.
System Requirements
 The website works on a web browser so the web browser is essential.
 In order to save from network traffic and smooth working of the website, it is necessary
to have a stable server connection..
 The backups are essential in order to avoid failures.
Assumptions
The assumptions that is required while the creations of the crow’s foot diagram are discussed
here:
 Table Customers is connected with Shopping_Basket as one customer can have multiple
shopping baskets.
 The table employee is connected with table products justifying that one employee may
trade for multiple products.
 The table payments and the table “shipping” connected as one payment is possible for
multiple shipping of the products.
 The products have many categories so another table is created named
“Products_Category” and it is connected with table products.
 For one product only single payment possible, therefore the table Products and Payments
are connected.
 For one category of product, multiple customers so these two tables are also connected.
 For the payment, there is a possibility that multiple customers can use similar payment
mode.
 There can be multiple products in one shopping basket that are added by the customers.
Tables Added
There are seven different tables that are in ER diagram and these tables are dependent on each
other with different relationships. All the tables are discussed here:
a) Products

 The table Product helps in the storing of information of every product in one place
(Ferretti, et al., 2012).
 There are five different attributes in this table.
 Product_Id, Shopbasket_Id, Product_name, Product_price, and Product_Quantity
are the attributes present in it.
 Product_Id assigned as primary key and the Shopbasket_Id assigned as foreign
key here as they are referenced from other tables.
 The table is connected by the tables with different relationships with table
employees, Shopping_basket, Payment, and Products_Category.
b) Products_Category
 The table Product_Category is developed so that the information of every
category of products can be stored in one place.
 There are four different attributes in this table.
 ProductCateg_Id, Payment_Id, Type_of_Purchase and Product_type are the
attributes present in it.
 ProductCateg_Id assigned as primary key & the Payment_Id is assigned as the
FK here as they are referenced from other tables.
 The table is connected with many tables by relationships with table Products and
Customers.
c) Customers
 The table Customers is develop so to store details of every customer.
 There are nine different attributes in this table.
 Customer_Id, ProductCateg_Id, Payment_Id, Customer_name,
Customer_address, Customer_contact, Customer_email, Username and Password
are the attributes present in it.
 Customer_Id is assigned as PK and Payment_Id, ProductCateg_Id are assigned as
the FK here as they are referenced from other tables.
 The table is interconnected with other tables by relationships with table Payments,
Shopping_basket and Products_Category.
d) Employees
 The table Employees is develop in order to store details of every employee.
 There are five different attributes in this table.
 Employee_Id, Employee_name, Employee_contact, Employee_address, and
Employee_email are the attributes present in it.
 Employee_Id is assigned as the PK in the table and no foreign key is present.
 The table is interconnected with table Products with a one-to-many relationship.

e) Shopping_Basket
 The table Shopping_Basket is created in order to store details of every customer’s
basket.
 There are four different attributes in this table.
 Shoppingbasket_Id, Payment_Id, Customer_Id, and TotalAmount are the
attributes present in it.
 Shoppingbasket_Id is assigned as the PK and Payment_Id, Customer_Id are
assigned as the FK here as they are referenced from other tables.
 The table is connected with others by relationships with table Payments,
Customers and Products.
f) Payments
 The table Payments is created in order to store details of every payment.
 There are three different attributes in table.
 Payment_Id, TotalAmount, and Payment_mode are the attributes present in it.
 Payment_Id is assigned as the PK here and there is no foreign key present in this
table.
 The table is interconnected with other tables by relationships with table
Shopping_basket, Products, Customers and Shipping.
g) Shipping
 The table Shipping is created in order to keep details of every shipped product.
 There are four different attributes in this table.
 Shipping_Id, Payment_Id, Shipping_address, and Shipping_date are the attributes
present in it.
 Shipping__Id is assigned as the PK here and Payment_Id is assigned as the
foreign key in this table as it is taking reference from table Payments.
 The table is interconnected with one-to-many relationships with table Payments.
LO2
P2
Now the database is created with the help of SQL language. This is the query language that
works on queries and helps in extracting the information that is needed from the database and
that too in the minimum time (Smart et al., 2008). A database is used to store the data in a
properly functioned system and the dependencies of these entities are given. The constraints like
primary key and foreign key are mentioned. The structured query language is used in order to get
the outputs that are needed for the given queries. The structured data is a type of data where the
rows and columns are properly arranged and have the relationships. The given case study is
based on a fashion outlet e-commerce website & database for the same is developed with the

help of the queries and shown by using the screenshots of the creation as well as execution
queries (Kroenke and Auer, 2013).
Screenshots of creation query as well as the execution of these queries are given for every table:
Table Customers
The table customer is developed by creation query and is created by using various attributes
present in it. These attributes are given as Customer_Id, ProductCateg_Id, Payment_Id,
Customer_name, Customer_address, Customer_contact, Customer_email, Username and
Password. All these have a various data type that is shown in the form of the screenshot and the
constraint like primary is also there.
Figure 2: Creation query for Customer
Execution of the query:
Figure 3: Customer table execution
Table Employees
The table employee is created with the help of creation query and is created by the help of
various attributes. These attributes are given as Employee_Id, Employee_name,
Employee_contact, Employee_address, and Employee_email. All these have a many data type
that is shown with the help of the screenshot and the constraint like primary is also there.

Figure 4: Creation query for Employees
Execution of the query:
Figure 5: Employees table execution
Table Payments
The table payment is created with the help of creation query and is created by the help of various
attributes present in it. These attributes are given as Payment_Id, TotalAmount, and
Payment_mode. All these have a different data type that is shown in screenshot..
Figure 6: Creation query for Payments
Execution of the query:

Figure 7: Payments table execution
Table Products
The table product is developed with the help of creation query and created with the help of
different attributes present in it. These attributes are given as Product_Id, Shopbasket_Id,
Product_name, Product_price, and Product_Quantity. All these have a different data type that is
shown in the screenshot and the constraint like primary is also there (Bowman et al., 1996).
Figure 8: Creation query for Products
Figure 9: Products table execution
Table Products_Category
The table Products_Category is created with the help of creation query and is created by the help
of various attributes present in it. These attributes are given as ProductCateg_Id, Payment_Id,
Product_type, and Type_of_Purchase. All these have a different data type that is shown in the
form of the screenshot and the constraint like primary is also there.

Figure 10: Creation query for Products_Category
Figure 11: Products_Category table execution
Table Shopping_Basket
The table Shopping_Basket is created with the help of create query and is created by the help of
various attributes present in it. These attributes are given as Shoppingbasket_Id, Payment_Id,
Customer_Id, and TotalAmount. All these have a different data type that is shown in the form of
the screenshot and the constraint of PK is also there (Spertus And Stein,2000).
Figure 12: Creation query for Shopping_Basket
Figure 13: Shopping_Basket table execution

Table Shipping
The table Shipping is created with the help of creation query and is created by the help of
different attributes that are present in it. These attributes are given as Shipping_Id, Payment_Id,
Shipping_address, and Shipping_date. All these have a different data type that is shown in the
form of the screenshot and the constraint like primary is also there.
Figure 14: Creation query for Shipping
Figure 15: Shipping table execution
P3
The structured data is the data of database in which the data is arranged appropriately in the rows
and columns. The operation named as CRUD operation is used in order to perform the SQL. The
operation provides help in the proper execution of queries and extracts the essential data from the
database using these queries. Various commands used so that these queries can be performed
easily and they are: Select, create, insert, delete, etc. The use of these commands helps in
searching for the information that is specified in a huge database in the least amount of time.
Queries:
(1.) Select ‘First_name’ , ‘Last_name’, ‘Gender’, ‘Salary’ from ‘Employees’ Join ‘Salaries’
where ‘Employees’.’Emp_no’ = ‘Salaries’.’Emp_no’;