Ecommerce Database Management System

As a part of our University PTU Curriculum, I made this project for Database Management Systems (DBMS)
This project contains theoretical as well as implementation in backend-SQL and FrontEnd-ReactJs.
If you liked the repo do ⭐ it.

********************* FrontEnd *************************

Frontend implementation of an e-commerce platform built using React. It provides users with an intuitive interface to browse products, add them to the cart, and proceed to checkout. Leveraging React's component-based architecture, it offers a seamless user experience with dynamic updates and efficient state management.

Key Features

• Product Catalog: Displaying a wide range of products with detailed information.
• User Authentication: Secure authentication and registration functionalities.
• Shopping Cart: Easy management of selected products before checkout.
• Checkout Process: Streamlined process for payment and order placement.

This project aims to deliver a modern and responsive web application for e-commerce businesses, allowing for easy customization and scalability.

LoginPage

Product Page

User Dashboard Page

Admin Dashboard

Cart Page

Filter Page

*********************** BackEnd **********************

Pre-requisite

MariaDB

Contents

• Project Description

• Basic structure

  • Functional requirements
  • Entity Relation (ER) diagram and constraints
  • Relational database schema

• Implementation

  • Creating tables
  • Inserting data

• Queries

  • Basic queries
  • PL/SQL function
  • Trigger function
  • Stored procedures
  • Functions
  • Transactions

1. Project Description

In this new modern era of online shopping no seller wants to be left behind and every seller want to the shift from offline selling model to an online selling model for a rampant growth.
Therefore, as an software engineer our job is to ease the path of this transition for the seller. Amongst many things that an online site requires the most important is a database system. Hence in this project we are planning to design a database where small sellers can sell their product online.

The Prime Objective of our database project is to design a robust E-commerce database by performing operations such as

• Viewing orders
• Placing orders
• Updating database
• Reviewing products
• Maintaining data consistency across tables

2. REQUIREMENTS

• A Customer can see the account details and can update if required.
• Customer can search the products according to the category.
• Customer can add his wishlist to the cart and can see the total amount.
• Customer can update the cart whenever required.
• Customer can choose the mode of payment.
• Customer can keep track of the order by seeing order status.
• Customer can review the products which have been purchased.
• Seller can update the stock of a particular product whether it is available or not.
• Seller can keep track of total sales of his products.
• Seller can know the sales on a particular day or month or year.

2.1 Functional Requirements

• A Customer cannot access the Seller details and vice-versa.
• There should not be any inconsistency in the data.
• There should not be any loss of data.

3. Relational Database Schema - e commerce

For more clear view, click here Relational Schema Pdf

4. Entities and their Attributes

ENTITIES	ATTRIBUTES	ATTRIBUTE TYPE	Entity Type
Customer	Customer_CustomerId Name Email DateOfBirth Phone Age	Simple Composite Simple Simple Multivalued Derived	Strong
Order	OrderId ShippingDate OrderDate OrderAmount Cart_CartID	Simple Simple Simple Simple Simple	Strong
OrderItem	Order_OrderId (PK) Product_ProductId(FK) MRP Quantity	Simple Simple Simple Simple	Weak
Product	productId (PK) ProductName sellerId MRP CategoryID Stock Brand	Simple Simple Simple Simple Simple Simple Simple	Strong
Review	ReviewId(PK) Description Ratings Product_ProductId Customer_CustomerID	Simple Simple Simple Simple	Strong
Cart	cartId (PK) Customer_customerId(FK) GrandTotal ItemsTotal	Simple Simple Derived Derived	Strong
Category	CategoryID(PK) CategoryName DESCRIPTION	Simple Simple Simple	Strong
seller	sellerId (PK) Name Phone Total_Sales	Simple Simple Multivalued Derived	Strong
Payment	payment_id Order_OrderId PaymentMode Customer_CustomerId PaymentDate	Simple Simple Simple Simple Simple	Strong

5. Entities and Relations

ENTITIES	RELATION	CARDINALITY	TYPE OF PARTICIPATION
Customer Address	Stays At	OneToOne	Total Partial
Customer Cart	Shops	OneToOne	Partial Total
Customer Order	Places	OneToMany	Partial Total
Customer Payment	Makes	OneToMany	Partial Total
Customer Review	Write	OneToMany	Partial Total
Seller Product	Sells	ManyToMany	Partial Total
Category Product	Categorizes	OneToMany	Partial Total
Cart Product	Contains	ManyToMany	Partial Partial
Product Orderltem	Includes	OneToMany	Partial Total
Order Orderltem	Includes	OneToOne	Partial Total
Payment Order	For	OneToOne	Total Total

6. ER-Diagram

for more clear view, click here ER Diagram pdf

7. QUERIES ON THE ABOVE RELATIONAL SCHEMA

1. Stored procedure for the details of the customer.
2. View for getting sales by category of products.
3. Using triggers to update the no.of products as soon as the payment is made.
4. Trigger to update the total amount of user everytime he adds something to payment table.
5. Stored procedure for getting order history.
6. Processing an order

• To process an order, one should check whether those items are in stock.
• If items are in stock, they need to be reserved so that they go in hands of those who have expressed them in wishlist/order.
• Once ordered the available quantity must be reduced to reflect the correct value in the stock.
• Any items not in stock cannot be sanctioned; this requires confirmation from the seller.
• The customer needs to be informed as to which items are in stock (and can be shipped immediately) and which are cancelled.

7. Check whether the specified customer exists

• IF NOT EXISTS add him/her
• COMMIT the info
• Fetch the customer id
• INSERT a row to Order tables
• If unable to do so,ROLLBACK;
• Fetch the new orderid in orders table
• INSERT row to the order table for every product ordered
• If adding tuples to orderitems fails ROLL BACK all tuples of products added for and the tuple in order row

QUERY 1: Customers to find products with highest ratings for a given category.
QUERY 2: Customers to filter out the products according to their brand and price.
QUERY 3: If a customer want to know the total price for all product present in the cart.
QUERY 4: Customers to find the best seller of a particular product.

QUERY 5: List the orders which are to be delivered at a particular pincode.

QUERY 6: List the product whose sale is the highest on a particular day.

QUERY 7: List the category of product which has been sold the highest on a particular day.

QUERY 8: List the customers who bought products from a particular seller the most.

QUERY 9: List all the orders whose payment mode is not CoD and yet to be delivered.
QUERY 10: List all orders of customers whose total amount is greater than 5000. QUERY 11: If customer wants to modify the cart that is he want to delete some products from the cart. QUERY 12: List the seller who has the highest stock of a particular product. QUERY 13: Customers to compare the products based on their ratings and reviews.

To get implemented part of above queries and other details, you can mail me: [email protected].
For any personalized and expedited support, feel free to ping me on my telegram. (please do mention subject as this repo name). thanks

Contributors

SAURABH KISHOR

Want to support my coding journey? Buy me a coffee and keep the code flowing! Buy me a Coffee!.

Thanks and I highly appreciate your support.