INTRODUCTION

Project Overview

E-shopping, also referred to as online shopping or e-commerce, has become

one of the most revolutionary transformations in the way people purchase
goods and services in the modern era. Instead of physically visiting a store,
customers now have the convenience of exploring a vast catalog of
products from anywhere in the world, at any time of the day. This shift has
been made possible by the rapid growth of the internet, digital payment
technologies, and, most importantly, robust database systems that manage
the vast amounts of information involved in online transactions. The ability
to browse products, read descriptions, compare prices, place orders, and
complete payments seamlessly depends on a powerful back-end that
stores, retrieves, and processes data accurately and securely.

The E-Shopping System in Oracle is designed to serve as a complete back-

end solution for an online shopping platform, focusing on managing
customers, products, orders, payments, and inventory in a centralized
database. The system is implemented using Oracle Database, a highly
reliable and scalable relational database management system known for its
efficiency, security, and support for advanced features such as PL/SQL,
triggers, procedures, and constraints. The project’s primary aim is to create
a secure, efficient, and reliable database structure that can handle the
essential operations of an online store while ensuring data accuracy and
integrity.

Before the widespread adoption of online shopping, purchasing goods

involved visiting markets or retail outlets physically, which often meant
spending considerable time traveling and searching for the desired
products. This process was time-consuming, limited in options, and
sometimes more expensive due to geographical constraints. However, with
the introduction of e-commerce platforms, the shopping experience has
been completely transformed. Businesses can now display their products to
customers across the globe, allowing buyers to make informed purchasing
decisions without geographical limitations. The database plays a crucial
role in this transformation by acting as the foundation upon which product
catalogs, customer profiles, orders, and transactions are stored and
managed. Without a well-structured database, even the most attractive e-
commerce interface would fail to deliver a smooth and error-free
experience.

The purpose of developing this E-Shopping System in Oracle is to

implement a robust database that can support every stage of the shopping
process. This includes storing and retrieving customer details, maintaining
a dynamic and up-to-date product catalog, processing and tracking orders,
updating stock levels automatically, and recording payment details.
Additionally, it provides administrative capabilities such as product
management, sales reporting, and inventory control, ensuring that both the
customer and the store administrator have a seamless and efficient
experience.

Oracle Database has been chosen for this project because of its enterprise-
level capabilities and proven track record in managing large-scale
transactional systems. Oracle supports high performance, secure role-
based access, strong data integrity through constraints, and advanced
automation using PL/SQL. Its ACID-compliant transaction processing
ensures that even in the event of failures, data remains consistent and
reliable. The database’s scalability allows it to handle increasing volumes of
products, customers, and transactions as the business grows, making it
suitable for both small online stores and large-scale e-commerce platforms.

The E-Shopping System built in Oracle is designed to cover the essential

functions required for a real-world online shopping experience. Customers
can create accounts, browse and search for products, add items to a
shopping cart, proceed to checkout, and simulate payment processes. The
system records all transactions, updates stock levels automatically through
triggers, and stores detailed order information for future reference. On the
administrative side, the system allows authorized personnel to add new
products, modify existing ones, remove outdated products, manage
inventory, and analyze sales performance through generated reports.

A critical aspect of this project is the database design, which consists of

several interconnected tables such as Users, Products, Orders,
 Order_Details, Payments, and Categories. Each table serves a specific
function, and relationships between them are carefully defined to maintain
data integrity. For example, the Users table stores customer and
administrator details, while the Products table contains the product
catalog. Orders are linked to customers through foreign keys, and the
Order_Details table records product-wise quantities for each order.
Payments are associated with orders to ensure complete transaction
history tracking. The database uses primary keys to uniquely identify
records, foreign keys to establish relationships, and constraints to prevent
invalid data entries.

To ensure smooth functioning and automation, the project utilizes various

PL/SQL features such as stored procedures for order processing, functions
for calculating totals, and triggers for updating stock levels after each
purchase. Triggers, in particular, play a vital role in maintaining real-time
inventory accuracy without requiring manual updates. This automation not
only saves time but also reduces the possibility of human errors, which is
crucial in an environment where multiple transactions can occur
simultaneously.

Security is another important aspect of this project. The system

incorporates measures such as unique constraints on email IDs to avoid
duplicate registrations, check constraints to validate data like prices and
stock quantities, and role-based access control to differentiate between
customer and administrator privileges. While password encryption can be
implemented for added security, this project focuses primarily on
database-side security controls to ensure that only authorized users can
perform sensitive operations.

The E-Shopping System offers numerous benefits to both customers and

store administrators. Customers gain the convenience of shopping anytime,
access to a wider range of products, and a more efficient purchasing
process. Store administrators benefit from centralized data storage, real-
time inventory updates, detailed sales reports, and better decision-making
capabilities. The combination of these features results in an overall
improvement in user satisfaction and business efficiency.
 However, like any system, this project has certain limitations. The current
implementation simulates payments instead of integrating with real-world
payment gateways, lacks a dedicated mobile application interface, and
requires Oracle Database installation, which may demand significant
system resources. Despite these limitations, the system serves as a strong
foundation that can be expanded in the future. Possible enhancements
include integrating with payment services like PayPal or Razorpay,
incorporating AI-based product recommendations, adding multi-language
support, and creating mobile app versions to increase accessibility.

In conclusion, the E-Shopping System in Oracle is a demonstration of how a

well-designed database can efficiently manage the complex operations of
an e-commerce platform. It ensures accuracy, reliability, and security in
handling customer information, product details, orders, and payments.
With its scalable and secure architecture, the system not only meets the
academic requirements of a database project but also lays the groundwork
for a real-world commercial application. By leveraging Oracle’s advanced
database features, the project achieves a level of functionality that can be
adapted and expanded to meet the needs of both small businesses and
large enterprises in the ever-growing world of online shopping.

In today’s technology-driven world, e-commerce has transformed the way

people buy and sell goods. Instead of physically visiting a store, customers
can browse thousands of products online, compare prices, and place orders
from the comfort of their homes. This revolution has been possible due to
advancements in database systems and web technologies.

The E-Shopping System developed in Oracle Database aims to create a

secure, efficient, and user-friendly platform for online product browsing,
selection, and purchase. The project focuses on the back-end database
implementation — storing customer data, managing products, tracking
orders, updating stock, and generating reports.
 Objective of the Project
The primary goal of the E-Shopping project is to build a centralized
database system that can handle:

 Customer information management

 Product catalog storage and retrieval
 Order placement and tracking
 Inventory management
 Transaction history and reporting

The project ensures data consistency, security, and accuracy using Oracle’s
relational database features such as PL/SQL, triggers, constraints, and
views.

Need for the System

Why E-Shopping Systems are essential:

Convenience – Customers can shop anytime, anywhere.

Variety – Wide range of products available in one place.

Speed – Faster product search and checkout process.

Business Reach – Sellers can target customers across the globe.

Efficiency – Automated stock updates and order tracking.

Features of the Project

1. User Registration & Login
o Secure sign-up with unique email IDs.
 o Login validation using stored credentials.
2. Product Catalog
o View product details: name, price, description, stock quantity.
o Search and filter products by category or price.
3. Shopping Cart
o Add, update, or remove products before checkout.
o Calculate total price with tax or discounts.
4. Order Processing
o Place orders and generate order IDs.
o Auto-update product stock.
5. Payment Simulation
o Basic simulation of payment status (success/failure).
o Records payment details.

6. Admin Module
o Add/update/delete products.
o View sales reports.
o Manage inventory.

Technology Stack

Front-End (optional for UI)

 HTML/CSS/JavaScript (if web UI is implemented)

 Oracle APEX (if using Oracle’s inbuilt UI tool)
 Back-End

 Oracle 19c / 21c Database

 SQL & PL/SQL

Tools

 SQL*Plus / Oracle SQL Developer

 ER Diagram tools (Lucidchart / Draw.io)

Database Design Overview

A well-structured database is the backbone of the project.

Key tables include:

Table Name Purpose

Users Stores customer and admin details
Products Stores product catalog information
Orders Stores main order details
Order_Details Stores product-wise details of each order
Payments Stores payment transaction details
Categories Stores product categories for filtering

Example Table Creation Script

CREATE TABLE Users (

user_id NUMBER PRIMARY KEY,
name VARCHAR2(50) NOT NULL,
email VARCHAR2(50) UNIQUE NOT NULL,
password VARCHAR2(100) NOT NULL,
role VARCHAR2(10) DEFAULT 'customer'
 );

CREATE TABLE Products (

product_id NUMBER PRIMARY KEY,
product_name VARCHAR2(100) NOT NULL,
category_id NUMBER,
price NUMBER(10,2) NOT NULL,
stock NUMBER NOT NULL,
description VARCHAR2(255)
);

Data Flow in the System

1. User Registration → Data stored in Users table.

2. User Login → Credentials validated from Users.
3. Product Browsing → Data fetched from Products table.
4. Add to Cart → Temporary cart or session data maintained.
5. Place Order → New entry in Orders + Order_Details.
6. Stock Update → Trigger reduces stock automatically.
7. Payment Update → Status recorded in Payments.

PL/SQL Components

The project makes use of:

 Procedures → Automate order placement and stock updates.

 Functions → Calculate order totals.
 Triggers → Maintain data integrity.
 Cursors → Handle multiple rows during order processing.

Example Trigger
 CREATE OR REPLACE TRIGGER stock_update_trigger
AFTER INSERT ON Order_Details
FOR EACH ROW
BEGIN
UPDATE Products
SET stock = stock - :NEW.quantity
WHERE product_id = :NEW.product_id;
END;

Security Measures

 Data Validation – Ensures no incorrect values are inserted.

 Unique Constraints – Prevent duplicate records.
 Role-Based Access – Separate privileges for admin and customer.
 Password Protection – Stores encrypted passwords (if implemented).

Advantages of Using Oracle Database

1. High Reliability – Handles large data without corruption.

2. Strong Security – Role management, privileges, constraints.
3. Scalability – Can grow with increasing data size.
4. ACID Properties – Ensures transaction integrity.
5. Powerful PL/SQL – Supports advanced business logic.

Limitations

 Does not include advanced payment gateway integration (can be added

later).
  UI is basic if built using only Oracle APEX.
 Requires Oracle DB installation which is resource-heavy.

Future Enhancements

 Integration with real payment gateways (Razorpay, PayPal).

 AI-based product recommendations.
 Mobile app interface.
 Multi-language support.

The E-Shopping System in Oracle provides a reliable, efficient, and secure

way to manage online shopping processes. It ensures that customers have a
smooth experience while admins can manage inventory, orders, and
transactions effectively. With Oracle’s advanced features like PL/SQL and
triggers, the system maintains data integrity, performance, and security.
 Related Work
The concept of e-shopping has been widely researched and implemented in
various forms across different platforms and industries. Over the years,
numerous systems have been developed to manage online sales, each
incorporating varying degrees of functionality, scalability, and security.
Understanding these existing systems is important, as it helps identify best
practices, technological advancements, and potential areas for
improvement in the development of new solutions.

Many commercial e-commerce platforms such as Amazon, Flipkart, eBay,

and Alibaba serve as large-scale examples of successful online shopping
systems. These platforms have implemented highly optimized database
structures to handle millions of concurrent transactions, vast product
catalogs, and complex order management processes. Their systems rely
heavily on advanced relational and distributed databases, sophisticated
caching mechanisms, and content delivery networks to provide fast and
reliable service to users across the globe. Although their infrastructure is
much larger than what an academic project would require, studying their
architecture offers valuable insights into scalability, data organization, and
fault tolerance.

In the academic domain, several student projects and research studies have
explored the design and implementation of e-shopping systems. Many of
these projects focus on database management using relational database
systems such as Oracle, MySQL, or SQL Server. For example, some research
has concentrated on building inventory management systems where the
database automatically updates stock levels after each purchase using
triggers and stored procedures, similar to the approach taken in this
project. Other works have emphasized user authentication and secure
transaction handling through role-based access controls, encryption
techniques, and data validation constraints, all of which are relevant to this
system’s design.

Open-source platforms such as Magento, OpenCart, and WooCommerce

provide additional reference points for database-driven e-commerce
solutions. These platforms often use MySQL databases with carefully
normalized schemas to manage products, customers, orders, and payments.
While the current project uses Oracle instead, the underlying principles of
table relationships, referential integrity, and transaction management
remain consistent. By studying these platforms, one can observe how
modular design allows for easy feature additions, such as integrating new
payment gateways or adding support for promotional discounts.

Several related works have also addressed the importance of user

experience in e-shopping systems. Research indicates that the performance
of the back-end database directly affects page load times, search speed, and
overall responsiveness, which in turn impacts customer satisfaction and
sales conversion rates. Hence, efficient query design, indexing, and
optimization — all supported by Oracle — are key considerations in the
project. Additionally, related studies have highlighted the role of real-time
analytics, where data collected from orders and browsing patterns is
analyzed to improve marketing strategies and product recommendations.
While the current implementation focuses primarily on the transactional
aspect, the database can be extended to support analytical processing in
the future.

In summary, the related work in both commercial and academic contexts

demonstrates the necessity of a robust and secure database architecture
for the success of an e-shopping system. By analyzing the strengths and
limitations of existing systems, this project adapts proven techniques —
such as automated stock updates through triggers, secure role-based
access, and structured product categorization — within the framework of
Oracle Database. This approach not only ensures a reliable and scalable
solution but also lays a strong foundation for integrating advanced features
in future iterations.
SYSTEM ANALYSIS
 System analysis is a critical stage in the development of any software
project, as it involves studying the problem domain, identifying the
requirements, and determining the feasibility of the proposed system. For
the E-Shopping System in Oracle, the analysis phase ensures that the
database and related components are designed to meet both functional and
non-functional needs while aligning with the project’s objectives. This
phase bridges the gap between the conceptual idea of the system and its
practical implementation.

The first step in the system analysis process is to understand the nature
and scope of the e-shopping platform. The system must serve two primary
user groups — customers and administrators. Customers require features
such as account registration, login authentication, product browsing, cart
management, order placement, and payment recording. Administrators
need capabilities for product management, inventory control, order
tracking, and report generation. These functional requirements form the
core of the system’s purpose and drive the database design.

In addition to functional requirements, the system must satisfy several

non-functional requirements. Performance is a major consideration, as the
database should handle multiple queries efficiently, especially when
retrieving product lists or processing orders. Security is equally important,
requiring measures like role-based access control, constraints to maintain
data integrity, and validation to prevent incorrect entries. The system must
also be scalable to accommodate an expanding product catalog, growing
customer base, and increased transaction volume over time. Reliability is
another key factor, ensuring that data is consistently accurate and available
without downtime.

The analysis phase also includes gathering technical requirements, which

involve selecting the appropriate hardware and software for the system.
The Oracle Database is the chosen back-end platform due to its stability,
scalability, and advanced PL/SQL capabilities. Development and testing can
be carried out using Oracle SQL Developer or SQL*Plus, and if a front-end
interface is desired, tools like Oracle APEX or basic web technologies may
be integrated. The hardware requirements include a system capable of
 running Oracle Database efficiently, with sufficient storage for product
images, order history, and transactional data.

A feasibility study is conducted to assess the practicality of implementing

the system. From a technical perspective, the system is feasible because
Oracle supports all the required features, including triggers, stored
procedures, and referential integrity through foreign keys. Economically,
the project can be developed using readily available tools without requiring
expensive licensing for small-scale academic deployment. Operational
feasibility is high, as the system’s workflow is simple enough for customers
and administrators to use with minimal training.

During system analysis, potential challenges and risks are also identified.
These may include managing concurrent access to data, ensuring
transactional integrity during simultaneous orders, and maintaining
database performance with large datasets. Solutions such as proper
indexing, optimized queries, and the use of PL/SQL blocks for atomic
transactions are proposed to address these challenges.

In conclusion, the system analysis phase for the E-Shopping System in

Oracle provides a clear understanding of the project’s requirements,
feasibility, and potential risks. This comprehensive analysis lays the
foundation for the next stage — system design — where the conceptual
framework is translated into detailed database schemas, workflows, and
implementation strategies.
 ABOUT THE CURRENT SYSTEM

Advantages of the Current System:

The project Online Shopping Portal System is GUI based system so that it is
easy tohandle. It also increases the efficiency of the end user, because it will
reduce the redunda ntjob, which is tedious to complete. The Online System
also has automated capability tocomplete job, so it reduces the work
manually.

Advantage of Online Shopping Portal:

1. This online program will take less time and gives better results.

2. It reduces the tedious jobs Like (Redundant work, long procedures, Up to

DateInformation).

3. It will improve the online shopping system, since all the information is
availablewhenever required.

4. It provides quick processing thus helps in transaction and updating

inEdit personal view can perform in few seconds.

5. It provides accurate Output.

6. It gives fast answer of queries.

7. The amount of paper work is reduced.

8. Better Control.

Deficiencies of the manual system:

1) Lack of immediate retrieval of information

In manual system, lot of time is wasted in retrieving information. Much

searching isrequired before required information is found. This wastes a
lot of time of the user as wellas the person.

2) Lack of immediate information storage

In manual system, it is difficult to store information at proper place
at thatvery moment. This is because the person is unable to quickly
locate the place where theinformation is to be stored.