E-commerce Database: SQL Implementation and Testing

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

Contents
Introduction......................................................................................................................................2
LO1..................................................................................................................................................3
P1.................................................................................................................................................3
M1................................................................................................................................................7
LO2..................................................................................................................................................8
P2.................................................................................................................................................8
M2..............................................................................................................................................12
P3...............................................................................................................................................12
M3..............................................................................................................................................17
LO3................................................................................................................................................17
P4...............................................................................................................................................17
M4..............................................................................................................................................19
LO4................................................................................................................................................19
P5...............................................................................................................................................19
M5..............................................................................................................................................22
Conclusion.....................................................................................................................................23
References......................................................................................................................................24
Figure 1: Entity relationship diagram..............................................................................................4
Figure 2: Create a query of the Employees table.............................................................................9
Figure 3: Execution Employees.......................................................................................................9
Figure 4: Create a query of Payments table...................................................................................10
Figure 5: Execution Payment.........................................................................................................10
Figure 6: Create a query of Items table.........................................................................................10
Figure 7: Execution Items..............................................................................................................10
Figure 8: Create a query of the Category Item table.....................................................................11
Figure 9: Execution Category Items..............................................................................................11
Figure 10: Create a query of Shopping cart table..........................................................................11
Figure 11: Execution Shopping Cart.............................................................................................11
Figure 12: Create a query of Ship_Item table................................................................................12
Figure 13: Execution Ship_Item....................................................................................................12
Figure 14: Create a query of the Customers table.........................................................................12

Figure 15: Execution Customers...................................................................................................12
Figure 16: Execution 1...................................................................................................................14
Figure 17: Execution 2...................................................................................................................14
Figure 18: Execution 3...................................................................................................................15
Figure 19: Execution 4...................................................................................................................15
Figure 20: Execution 5...................................................................................................................16
Figure 21: Execution 6(a)..............................................................................................................16
Figure 22: Execution 6(b)..............................................................................................................16
Figure 23: Execution 7(a)..............................................................................................................17
Figure 24: Execution 7(b)..............................................................................................................17
Figure 25: Execution 7(c)..............................................................................................................17
Figure 26: Execution 8...................................................................................................................18
Figure 27: Test case 1....................................................................................................................19
Figure 28: Test case 2....................................................................................................................19
Figure 29: Test case 3....................................................................................................................19
Figure 30: Test case 4....................................................................................................................20
Figure 31: SELECT.......................................................................................................................21
Figure 32: CREATE......................................................................................................................21
Figure 33: UPDATE......................................................................................................................21
Figure 34: EXECUTE....................................................................................................................22
Figure 35: DELETE.......................................................................................................................22
Figure 36: Flowchart......................................................................................................................23

Introduction
The given assessment includes a case study for creating a database for online shopping for the
provided fashion outlet expansion. For performing the task various requirements are provided
that need to fulfill and the report contains all the asked requirements. The report contains the P as
well as M parts in order to achieve merit for the provided assignment. Creation of an entity
relationship diagram is done and attached to the report. A list of queries is given in the
assessment that is implemented with the help of phpMyadmin and the queries are mentioned
with the help of screenshots with the outputs as well. The report contains the test cases as well as
with their proper description and screenshots that are provided after the test cases get passed. At
last with the help of a flowchart the flow of working of the e-commerce is also explained and
attached with the file itself.

LO1
P1
Entity relationship diagram for the given case study is created by using seven different tables and
is created by using draw.io. The screenshot is given here:
Figure 1: Entity relationship diagram

Requirements
For every project, there are some basic systems as well as user requirements that need to be
keeping in mind before and during the development of the website. So here a small description
related to the user and system requirements.
User requirements
 There is an option for the users to order more than one items and the user can add these
items in the shopping cart that is provided in the website.
 For the appropriate delivery of the ordered item, it is necessary for users to provide the
correct address while registering on the website.
 For purchasing the items and accessing more features of the website, it is necessary to
register and login into the website (Wu et al., 2017).
System requirements
 The connection with the servers should be appropriate so that the high traffic can be
avoided into the website.
 Proper management of the database should be arranged so that there is no mess in the
data storage on the increment of the customers.
 For make the website run, testing of the website should be done on every platform like
Windows and Linux.
 For the safety of the customer's data and save from any type of data losses and system
failures it is essential to keep a backup for the servers (Omranian et al., 2016).
Assumptions
There are various assumptions that need to follow in order to create an entity relationship
diagram and they are discussed here:
 For one item there can be multiple numbers of category, therefore, there is a relationship
between the table items and category_items.
 An employee can sell multiple items.
 The customer can buy more than one categories of the item.
 In a shopping cart, a customer can add many items.
 The customer can take many shopping carts.
 For one payment there is a possibility of having multiple shipping for one address.
 The single payment can be done for one cart of the shopping.
 Multiple customers can select a single payment mode for paying.

Tables
The discussion on the tables that are used in the entity relationship diagram is mentioned and
discussed in detail here:
Category_items:
This table is used so that details regarding the category of the items can be kept.
The table contains four attributes.
Category_id, Payment_id, Type_product, Purchase_type.
Category_id is the primary key.
Payment_id is a foreign key.
Tables connected with this are Customers and Items.
Items:
This table is used so that details regarding Items can be kept and tracked properly.
The table contains five attributes.
Itm_id, Cart_id, Itm_name, Itm_price, and Quantity.
Itm_id is the primary key.
Cart_id is a foreign key.
Tables connected with this are Shopping_cart, Category_items, and Employees.
Shopping_Cart:
This table is used so that details regarding shopping carts can be kept and tracked
properly.
The table contains four attributes.
Cart_id, Payment_id, Cust_Id and Grand_total.
Cart_id is the primary key.
Payment_Id and Cust_Id are foreign keys.
Tables connected with this are Items, Payments, and Customers (Hansson, 2015).
Customers:
This table is used so that details regarding customers can be kept and tracked properly.
The table contains nine attributes.
Cust_Id, Category_id, Payment_Id, Cust_name, Cust_Contact, Cust_Address, Cust_mail,
Username and Password.
Cust_id is the primary key.
Category_Id and Payment _Id are foreign keys.
Tables connected with this are Category_Items, Shopping_Cart, and Payments.

Payments:
This table is used so that details regarding payments of the customers can be kept and
tracked properly.
The table contains three attributes.
Payment_Id, Grand_total and Mode_payments.
Payment_id is the primary key.
Grand_total and Mode_payment are foreign keys.
Ship_Items:
This table is used so that details regarding Ship_Items of the customers can be kept and
tracked properly.
The table contains four attributes.
Ship_Itm_Id, Payment_Id, Ship_Address, and Ship_Date.
Ship_Itm_Id is the primary key.
Payment_Id is the foreign key.
Table connected with this is Payments.
Employees:
This table is used so that details regarding Employees working in the company can be
kept and tracked properly.
The table contains five attributes.
Employ_Id, Employ_name, Employ_address, Employ_mail and Employ_contact.
Employ_Id is the primary key.
No foreign keys are present in this table.
Table connected with this is Items.

M1
The above entity relationship diagram is already normalized. There are no partial as well as
transitive dependencies present in the above diagram. The partial dependency occurs when in a
table there are two different primary keys and another table dependent on one prime attribute and
not on both. So such case does not appear here, therefore, there is no partial dependency and the
transitive dependency occurs when a non-prime attribute is dependent on the non-prime attribute
so this is not occurring in this case, therefore, no transitive dependency is there as well. The 3
normalization form is given as:
3NF:
 Customers: (Cust_Id, Category_Id, Payment_Id, Cust_Name, Cust_Contact,
Cust_Address, Cust_mail, Username, Password)
 Employees: (Employ_Id, Employ_name, Employ_address, Employ_mail
Employ_contact)
 Items: (Itm_id, Cart_id, Itm_name, Itm_price, Quantity)
 Category_Items: (Category_id, Payment_id, Type_product, Purchase_type)

 Shopping_Cart: (Cart_id, Payment_id, Cust_Id, Grand_total)
 Payments: (Payment_Id, Grand_total ,Mode_payments)
 Ship_Items: (Ship_Itm_Id, Payment_Id, Ship_Address, Ship_Date)
LO2
P2
The entity relationship displays the entire connected table that helps in the creation of the
database. The database is created by using the language of the database that is the structured
query language. This is used to access the database efficiently in a minimum amount of time.
The query language is used to find the essential information and to extract the essential data from
the large database of an organization. The implementations of the already provided queries are
shown in this part of the assessment by using phpmyadmin and the screenshots are given further
of the creation query and its output for every particular table.
Employees
For this table creation, the command used is the Create command. After the creation of the table,
there are various numbers of attributes are there with constraints added in them. These attributes
are Employ_Id, Employ_name, Employ_address, Employ_mail Employ_contact.
Figure 2: Create a query of the Employees table
Figure 3: Execution Employees
Payments
For this table creation, the command used is the Create command. After the creation of the table,
there are various numbers of attributes are there with constraints added in them. These attributes
are Payment_Id, Grand_total, and Mode_payments.

Figure 4: Create a query of Payments table
Figure 5: Execution Payment
Items
For this table creation, the command used is the Create command. After the creation of the table,
there are various numbers of attributes are there with constraints added in them. These attributes
are Itm_id, Cart_id, Itm_name, Itm_price, and Quantity.
Figure 6: Create a query of Items table
Figure 7: Execution Items
Category_Items
For this table creation, the command used is the Create command. After the creation of the table,
there are various numbers of attributes are there with constraints added in them. These attributes
are Category_id, Payment_id, Type_product, and Purchase_type.

Figure 8: Create a query of the Category Item table
Figure 9: Execution Category Items
Shopping_Cart
For this table creation, the command used is the Create command. After the creation of the table,
there are various numbers of attributes are there with constraints added in them. These attributes
are Cart_id, Payment_id, Cust_Id, and Grand_total.
Figure 10: Create a query of Shopping cart table
Figure 11: Execution Shopping Cart
Ship_Items
For this table creation, the command used is the Create command. After the creation of the table,
there are various numbers of attributes are there with constraints added in them. These attributes
are Ship_Itm_Id, Payment_Id, Ship_Address, and Ship_Date.

Figure 12: Create a query of Ship_Item table
Figure 13: Execution Ship_Item
Customers
For this table creation, the command used is the Create command. After the creation of the table,
there are various numbers of attributes are there with constraints added in them. These attributes
are Cust_Id, Category_Id, Payment_Id, Cust_Name, Cust_Contact, Cust_Address, Cust_mail,
Username and Password.
Figure 14: Create a query of the Customers table
Figure 15: Execution Customers