sree-harsha-ps/e-commerce-fashion-hub-application

Welcome to Ecommerce Fashion Hub

Project Overview

Welcome to Ecommerce Fashion Hub, a cutting-edge project developed in understanding of Database Management Systems (DBMS). This project encompasses both theoretical concepts and practical implementations in SQL, offering a comprehensive understanding of database management in the realm of fashion ecommerce. If you find this project useful, show your support by starring it!

Prerequisites

To run this project, ensure you have MariaDB installed.

Table of Contents

• Project Description
• Requirements
  • Functional Requirements
• Relational Database Schema
  • Entities and their Attributes
• Entities and Relations
• ER-Diagram
• Queries

1. Project Description

In today's digital age, the fashion industry is embracing online platforms for exponential growth. Ecommerce Fashion Hub aims to facilitate this transition by providing a robust database system tailored for fashion ecommerce. Our project focuses on enabling small-scale fashion sellers to efficiently sell their products online.

Key Objectives:

• Facilitating viewing and placing orders
• Updating the database seamlessly
• Providing a platform for reviewing products
• Ensuring data consistency across all tables

2. Requirements

2.1 Functional Requirements

• Customers can view and update their account details.
• Customers can search for products by category.
• Customers can add items to their wishlist and cart, viewing the total amount.
• Customers can update their cart and choose a mode of payment.
• Customers can track their order status and review purchased products.
• Sellers can update product stock availability and track total sales.
• Sellers can monitor sales trends on specific days, months, or years.

3. Relational Database Schema - E-commerce

Check the Relational Schema Diagram for a clearer view.

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

Explore the relationships between entities, their cardinality, and participation types in the repository.

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

View the ER-Diagram image for a visual representation.

7. Queries on the Above Relational Schema

1. Stored procedure for retrieving customer details.
2. View for obtaining sales by product category.
3. Utilizing triggers to update product quantities upon payment.
4. Trigger to update the total amount whenever a user adds items to the payment table.
5. Stored procedure for accessing order history.
6. Processing orders efficiently.

For detailed implementation of the above queries and more, feel free to reach out via email: [email protected].

---