E-commerce Database Management System

Date
TagsMongoDBNext.jsSasSTypescript
  • 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.

Job descriptions

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

Database Design

ENTITIESRELATIONCARDINALITYTYPE OF PARTICIPATION
CustomerAddressStays AtOneToOneTotalPartial
CustomerCartShopsOneToOnePartialTotal
CustomerOrderPlacesOneToManyPartialTotal
CustomerPaymentMakesOneToManyPartialTotal
CustomerReviewWriteOneToManyPartialTotal
SellerProductSellsManyToManyPartialTotal
CategoryProductCategorizesOneToManyPartialTotal
CartProductContainsManyToManyPartialPartial
ProductOrderltemIncludesOneToManyPartialTotal
OrderOrderltemIncludesOneToOnePartialTotal
PaymentOrderForOneToOneTotalTotal

QUERIES ON THE ABOVE RELATIONAL SCHEMA

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

Queries

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.