Online Store using E-Commerce and Database Design and Implementation

Online Store using E-Commerce and Database Design and Implementation

1Md. Tariqul Islam

Department of Electronics and Communication Engineering

Khulna University of Engineering and Technology

2Md. Shohel Mojumder Master in Data Science

University of Potsdam, Germany

Abstract E-commerce is a platform through which the consumer can buy his/her desired product and seller can continue his business without leaving their own houses. In the modern world the IT sector is running the recent business which produces the commercial development universally and the internet technology doing numerous activities within very short time. The activities of E-commerce shows increasing pattern as the result of attachment of recent internet technology. Bangladesh is a developing country and the platform of E- commerce in our country is under developing condition. The popularity of online shopping in Bangladesh is growing rapidly and most of the people showing their interest on online purchase so that the can shave their time and able to make their best choice. Though online shopping is time saving process, the business owner and buyer need to face some risk. It is become very difficult for business holder to manage the different types of product and the variety of customer. On the other hand the customer cant keep trust properly on the online shopping due to having chance to face some cheating situation. There have a strong solution of these problems to design a strong database for business man and a trustable website for buyer and seller. In this paper we develop an E-commerce module and designing and develop a strong flexible database considering the above problem. If the business man conducts their business through this module they have great chance to compete globally with global scale and they can save staff need for public facing premises.

Keywords E-commerce, Online shoping, MYSQL, Database, E-R diagram.

I. INTRODUCTION

The IT sector in Bangladesh is developing very quickly and Bangladesh is a developing country. The Bangladeshi Government tried to increase the mobility to develop modern technology and for spreading the IT facilities over the nation invested a lot every year. For developing the IT market in Bangladesh, the Government provides numerous [1] facilities and spends lots of money for campaigning and connecting people with IT market. Bangladesh has a massive store market and the Bangladeshi people always facing communication time consume problem during go to store for buying some product and sometime they are unable to buy their desired goods. During occasion this problem becomes more dangerous. As a result it becomes keen desired to find out a appropriate solution to solve this problem in effective way.

Among lots of process online shopping is very effective and efficient method to cope with this problem through which the buyer and seller can interact with each other without any interaction of intermediate party. This process is a real time method so it saves lots of time for buyer and seller both

parties. Online shopping is an Internet system [2] through which a trader sells this goods and the buyer buy these product form these trader. WWW (World Wide Web) is must for merchants for sell their product as a result they can sell their product only those person who have internet connection. Customer can easily choose their product from website and make order through their device. Online store are decorated numerous types of product and shopper can purchase any- thing what they need for instance book, clothing, household goods, toys, electronics devices, software, ect. Online shopping is very malleable and user friendly method as a result numerous people favor to online shopping. If a person wants to buy a product from a brick-and-mortar store, need to drive and search a appropriate parking for parking his car and need to working until he is able to find his desired product. As soon as he selects his required item he needs to stand in a long line for payment.

Online shopping is very convenience but everyone is not showing interest for purchase product from online shop. Several people show their interest to purchase product to reach the shop physically and enjoy the shopping experience. They feel better to touch the trader, trying to cloth and moved around the people. Online shopping does not give permission to conduct the product and they dont allow the buyer to reach the seller shop or house in the same day. This shopping technique provides you permission to browse [3] borderless possibilities and give chance to the trader to display as many products as he can. The product which you want to purchase is not distributed locally as a result you need to search it through internet. If one want to check its quality and compare with others, online shopping is very helpful for this task.

During online business, the businessman faces lots of challenges. Numerous business man are available on the online market. So a strong and healthy competition is available in this system. The man who is able to manage their product and customer according to various customer demands becomes a successful business man within very short time. For managing the variation of product and different tasteful customer, he needs to design a very powerful database and website. In this paper we successfully design and implement a database. We mainly discuss about the product selling efficiently and process and store the information accurately so that the owner of the shop can see the present position of his shop and can take immediate action accordingly. We mainly covered:

1. Purchase Management

2. User Management

3. Stock Management.

4. Sell Management.

II. PROPOSED SYSTEM STUDY AND ANALYSIS Numerous reasons are available for instigating the Information systems such as increasing the data processing speed, gaining excellent accuracy, steadiness improving, amalgamates different business areas, reducing cost and better safekeeping. For designing a system everyone need to include three parts:

1. Request clarification,

2. Feasibility study, and

3. Request approval.

Lots of systems are available in the IT market. But most of them have some deficiency for example the system are time consuming, expensive to maintain, need agent and physical location and customer need to out for that.

A. Proposed System

The online shopping system is very easy to run, maintain, it is reliable to cost, scale able, affordable and user friendly. Our system provides some benefit and characteristics which is given below.

1 Providing security

2 Low cost

3 Basic computer knowledge required

4 Configurable and extensible application UI design

The system which we introduces here need not any educative, experienced, technical expertise in IT field person. This system can run all stages and native people. But the person who has a little bit knowledge on computer operating is good suit for using this system.

Feasibility study:

The short and focused study for answering numerous questions are known as feasibility study and the example of question is:

1. Does the system contribute to the overall objectives of the organizations?

2. Can the system be implemented using current technology and within given cost and schedule constrains?

3. Can the system be integrated with systems which are already in place?

Technical Feasibility:

1. Is the project feasibility within the limits of current technology?

2. Does the technology exist at all?

3. Is it available within given resourceconstraints (i.e., budget, schedule)?

Financial Feasibility:

1. Is the project possible, given resource constraints?

2. Are the benefits that will accrue from the new

system worth the costs?

3. What are the savings that will result from the system, including tangible and intangible ones?

4. What are the development and operational costs?

Operational Feasibility:

If the system is developed for solving the problem and acceptability, will it work? The people-oriented, social issues and internal issues for example manpower problems, labor objections, manager resistance, organizational conflicts and policies; also external issues, including social acceptability, legal aspects and government regulations are include here.

B. Proposed System Analysis

The system operation which is designed is introduced step by step is given below.

1. This system is all about the converting the shopping system from manual to online.

2. Customer can buy products online after login to the site.

3. Administrator is adding product to database.

4. Administrator can edit or delete the products from the database.

5. After buying and making payment the products are send to customers address that has been given.

6. Customer can see the price list of the products.

7. Admin can change password and add new admin.

8. Admin can see daily and previous order lists.

9. Admin can delete daily and previous order lists.

III. SYSTEM IMPLEMENTATION

For implement the system collecting the required content and design the database and software.

A. Store Detail Gathering

For maintain a store the following information need to gather the title or heads unless they are unavoidable.

Category Detail: For Input:

The category table stores the input category name and it should be unique so that the category name cannot be contradicted with each other.

For Output:

All the pages of the website display entire category name.

Subcategory Details: For Input:

The subcategory table store the input subcategory name and the subcategory name are included under the category name. For Output:

Subcategory name should be displayed when user clicks on the category name in the dropdown menu.

Product Details: For Input:

Input the details of the product that is product name, price, discount, description of the Product, Upload Photo, and quantity.

For Output:

The details of the product display on the website.

For Input:

Input whether the product is featured or not. For Output:

Then we would display featured product in the front page of the website.

Customer Details: For Input:

Input Username, Password, email of the user. For Output:

User will login using this username and password to add products to cart.

For Input:

Input first name, last name, address, city, zip code, Phone number, email of the customer for the checkout.

For Output:

Administrator will see the details of the customer in the order list page.

Administrator Details: For Input:

Input Username, password & confirm password to create new administrators.

For Output:

Using the created username and password administrator will access the admin section.

For Input:

Input Username, old password, new password & confirm new password to create new admin user.

For Output:

Using the changed username and password administrator will access the admin section.

For Input:

Input about us and shipping and delivery section. For Output:

Customer will see the about us and shipping and delivery details information in the website.

Role of Administrator:

Administrator collects the entire category name and its Subcategory Name .Then under the subcategory Administrator will collect the entire products name and its price, discount, description of the product, upload photo, quantity. Administrator will keep details registration of the user and manipulate it. And keep track of the order and manipulate the order properly.

B. Systems Functionality

Functional Requirement:

The system must provide the following functionality-

1. Keeping records of customers.

2. Keeping records of the category, subcategory and their products.

3. Keeping the daily and previous orders list.

4. Storing the items selected by the customer in the temporary storage.

5. Provide the checkout functionalities for the customer to buy the products.

6. Keeping company and its shipping and delivery information.

7. Provide the products searching facilities for the users.

Not Functional Requirement:

Following Non-functional requirements will be there in the insurance on internet:

1. Secure access of confidential data (customers details).

2. 24 X 7 availability.

3. Better component design to get better performance at peak time.

4. Flexible service based architecture will be highly desirable for future extension.

Nonfunctional requirements define system properties and constraints. It arise through user needs, because of budget constraints or organizational policies, or due to the external factors such as safety regulations, privacy registration and so on.

Various other Non-functional requirements are:

1. Security

2. Reliability

3. Maintainability

4. Portability

5. Extensibility

6. Reusability

7. Application Affinity/Compatibility

8. Resource Utilization

C. Hardware Requirement

Hardware requirements for Insurance on internet will be same for both the parties which are follows:

Processor : Dual Core or above.

RAM : 1GB or above.

HD : 20 GB or above.

NIC : For each party

D. Software Requirement

Software required to make working of product is

1. Operating System: Windows XP/vista/7 or later version, Linux, Ubuntu.

2. Development platform: Dreamweaver, Photoshop, Notepad++, Apache, MySQL

3. Communication Interfaces 2.6.4

4. The two parties should be connected through either by LAN or WAN for the communication.

IV. SYSTEM DESIGN

A. User Case Diagram

User case diagrams are used to model the functional interaction between users and system.

Fig.4.1 User Case Diagram

B. Data Flow Diagram

Data Flow Diagrams (DFD) is a modular design process where the required system is represented graphically. A DFD describes what data flow (logical) rather than how they are processed, so it does not depend on hardware, software, data structure or file organization. A Data Flow Diagrams is a structured analysis and design tool that can be used for flowcharting in place of, or in association with, information- oriented and process oriented systems flowcharts. A DFD is a network that describes the flow of data and the processes that change, or transform, data throughout a system. This network is constructed by using a set of symbols that do not imply a physical implementation. It has the purpose of clarifying system requirements and identifying major transformations that will become programs in system design. So it is the starting point of the design phase that functionality decomposes the requirement specifications down to the lowest level of detail.

The symbols used to prepare DFD do not imply a physical implementation, a DFD can be considered to an abstract of the logic of an information-oriented or a process-oriented system flow-chart. For these reasons DFDs are often referred to as logical data flow diagrams. The four basic symbols used to construct data flow diagrams are shown below.

These are symbols that represent data flows, data sources, data transformations and data storage. The points at which data are transformed are represented by enclosed figures, usually circles, which are called nodes. The principle processes that take place at nodes are:

1. Combining data streams

2. Splittingdata streams

3. Modifying data streams.

Fig.4.2 Data Flow Diagram on online shopping system

C. Context Analysis Diagram (CAD)

Fig.4.3 Context analysis diagram of online shopping

C Context Analysis Diagram (CAD)

The System is recognized as a complete system Smart Shop. Admin, Users, Shipping Agent are the main actors for this system.

DFL Level 1:

Fig. 4.4.1 Level 1 DFD

Customer/User can browse the catalog and can add items to their cart for ordering. Moreover Customer can remove items from their cart. And after adding items to their cart they can checkout and move to Ordering. For Order a customer need to be log-in.

DFD Level 2 (Chart)

Fig. 4.4.2 Level 2 DFD

Customer/User can browse the catalog and can add items to their cart for ordering. Moreover Customer can remove items from their cart. And after adding items to their cart they can checkout and move to Ordering. For Order a customer need to be log-in.

DFD Level 3 (Registration)

Fig.4.4.3 Level 3 DFD

DFD Level 4 (Maintenance)

Fig. 4.4.4 Level 4 DFD

Admin can maintain the Catalog. The Catalog contains all the items are in stock with their rates. Admin can also change the catalog information like Dynamic rates according to market value. He can add remove items from catalog and stocks.

DFD Level 5 (Login)

Fig.4.4.5 Level 5 DFD

User enters their login-id along with password. If the entered data is valid combination then home page will be displayed otherwise a message intimating user about their in- appropriate login details.

D E-R Diagram

E Relational Table for Databse Design

Fig. 4.5 Database Tables and Relations

F User Case Diagram

Fig.4.6 User Case Diagram

G Relational Table for Databse Design

Fig.4.7 System Flowchart

H Database Design

A database design is a collection of stored data organized in such a way that the data requirements are satisfied by the database. The general objective is to make information access easy, quick, inexpensive and flexible for the user. There are also some specific objectives like controlled redundancy from failure, privacy, security and performance. A collection of relative records make up a table. To design and store data to the needed forms database tables are prepared. Two essential settings for a database are:

1. Primary key: – The field that is unique for all the record occurrences.

2. Foreign key: -The field used to set relation between tables. Normalization is a technique to avoid redundancy in the tables.

H Database Table Design

adminLogin Table

column Name	Data Type	Key Constraints	Extra
adminId	int(11)	Primary Key NOT NULL	auto_incre ment
username	varchar(40)	NOT NULL
password	varchar(40)	NOT NULL

Category Table

Column Name	Data Type	Key Constraints	Extra
categoryId	int(11)	Primary Key NOT NULL	auto_increme nt
categoryname	varchar(100)	NOT NULL

Subcategory Table

Column Name	Data Type	key Constraints	Extra
subcategoryId	int(11)	Primary Key NOT NULL	auto increment
categoryId	int(11)	NOT NULL
subcategoryNam e	varchar(100)	NOT NULL

productsDescription Table

Column Name	Data Type	Key Constraints
ProductId	int(20)	Primary Key NOT NULL
categoryName	varchar(20)	NOT NULL
subcategoryName	varchar(20)	NOT NULL
productName	varchar(200)	NOT NULL
price	int(10)	NOT NULL
discount	int(10)	NOT NULL
actualPrice	int(10)	NOT NULL
quantity	int(10)	NOT NULL
photo	varchar(500)	NOT NULL
frontDisplay	int(10)
Description	varchar(500)	NOT NULL

Column Name	Data Type	Key Constraints	Extra
userId	int(11)	Primary Key NOT NULL	auto increment
username	varchar(40)	NOT NULL
email	varchar(40)	NOT NULL
password	varchar(40)	NOT NULL

Column Name	Data Type	Key Constraints	Extra
userId	int(11)	Primary Key NOT NULL	auto increment
username	varchar(40)	NOT NULL
email	varchar(40)	NOT NULL
password	varchar(40)	NOT NULL

customerLogin Table

Order Table

Column Name	Data Type	Key Constraints	EXTRA
OrdereId	int(10)	Primary Key NOT NULL	auto increment
customerId	int(10)	NOT NULL
totalAmount	int(10)	NOT NULL
discountPercent	int(10)	NOT NULL
discountAmount	int(10)	NOT NULL
payableAmount	int(10)	NOT NULL

OrderDetail Table

Column Name	Data Type	Key Constraints	Extra
orderDetailsId	int(11)	Primary Key NOT NULL	auto increment
ordereId	int(10)	NOT NULL
ProductId	int(10)	NOT NULL
Quantity	int(10)	NOT NULL
ProductPrice	int(10)	NOT NULL

Auxiliary Table

Column Nam	Data Type	Key Constraints	Extra
auxId	int(11)	Primary Key NOT NULL	auto increment
aboutUsContent	text	NOT NULL
deliveryContent	text	NOT NULL

I CONCLUSION

The main goal of this paper is to design an Online Shop and Database design and Implement with the environment of E- Commerce. Online Shopping system makes our life style more flexible by reducing the waste of time for shopping. It is one of the best platforms for buyer and seller to sell and buy product within in a very short time and easiest way. Online shopping is become very popular and user friendly with the engagement of IT technology specially Database and Website. The person who has internet access and smart device can easily purchases his desired product easily and immediately. In online shopping the buyer and seller always faces lots of challenges and with the increment of its use and cope with the increasing population in Bangladesh its become very difficult for seller to manage their product and byer. So a strong, effective, efficient and flexible system and Database need to design. In this paper I do this job very perfectly and successfully.

Customers Table

REFERENCES

Column Name	Data Type	Key Constraints	EXTRA
customerId	int(10)	Primary Key NOT NULL	auto increment
firstName	varchar(40)	NOT NULL
lastName	varchar(40)	NOT NULL
address	varchar(40)	NOT NULL
city	varchar(40)	NOT NULL
postalCode	varchar(40)	NOT NULL
phoneNumber	varchar(40)	NOT NULL
email	varchar(40)	NOT NULL

Column Name	Data Type	Key Constraints	EXTRA
customerId	int(10)	Primary Key NOT NULL	auto increment
firstName	varchar(40)	NOT NULL
lastName	varchar(40)	NOT NULL
address	varchar(40)	NOT NULL
city	varchar(40)	NOT NULL
postalCode	varchar(40)	NOT NULL
phoneNumber	varchar(40)	NOT NULL
email	varchar(40)	NOT NULL

[1] Patthanid Cheangtawee, Natspun Paopun, and Wanno Fongsuwan, The Development of Key Perforance Indicators for E-Connerce in Hotel Businesses Using Balanced Scorecard, Proceedings of the Fourth International Conference on eBusiness, November 19-20, 2005, Bangkik, Thailand.

[2] Syed Emdad, Tania Alauddin and Hasan U. Zaman, Developing an E- Commerce Website

[3] Yot Amomkitvikai and Cassey Lee, Determinants of E-Commerce Adoption and Utilisation by SMEs in Thailand, January 2020.