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 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.

• 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.

MariaDB

• 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

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

• 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.

• 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.

For more clear view, click here Relational Schema Pdf

for more clear view, click here ER Diagram pdf

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: abhinav221sk@gmail.com.
For any personalized and expedited support, feel free to ping me on my telegram. (please do mention subject as this repo name). thanks

SAURABH KISHOR

Thanks and I highly appreciate your support.