🛒 E-commerce Analytics Management System (SQL Capstone Project)

📌 Project Overview

This is a PostgreSQL-based E-commerce Management System.
It simulates a real-world e-commerce database with customers, products, orders, payments, and shipments, and showcases:

• Database design with ER Diagram
• Data generation using Python Faker
• Advanced SQL queries for business insights
• Stored procedures & triggers
• Python integration with Pandas + Matplotlib for analytics

---

📑 Table of Contents

1. Project Overview
2. Key Features
3. Project Structure
4. Tech Stack
5. Concepts Covered
6. Analytics Queries
7. Author

---

🚀 Key Features

• ✅ Structured normalized schema: Customers, Products, Orders, Order Items, Payments, Shipments
• ✅ ER Diagram-based design (3NF normalization)
• ✅ Data generated using Python Faker
• ✅ Complex SQL queries: aggregations, joins, CTEs, window functions
• ✅ Stored procedures & triggers for automation
• ✅ Business-focused analytics queries
• ✅ Python integration with Pandas & Matplotlib for visualization
• ✅ Organized GitHub project structure

---

📂 Project Structure

Ecommerce Database Project

ecommerce-sql-project/ │── README.md # Project overview │── sql/ │ ├── create_tables.sql # Table creation scripts │ ├── insert_data.sql # Insert queries (optional if using CSVs) │── data/ │ ├── customers.csv │ ├── products.csv │ ├── orders.csv │ ├── order_items.csv │ ├── payments.csv │ ├── shipments.csv │── queries/ │ ├── analysis_queries.sql # Business insights queries │ ├── stored_procedures.sql # Functions & procedures │ ├── triggers.sql # Trigger functions │── notebooks/ │ ├── data_generation.ipynb # Jupyter notebook for Faker dataset │ ├── db_connection.py # Python + SQLAlchemy connection │ ├── analysis.ipynb # Python visualization with matplotlib │── er_diagram.png # ER diagram of database │── .gitignore

---

🛠️ Tech Stack

Category	Tool/Technology
Database	PostgreSQL
Language	SQL, Python
Libraries	psycopg2, SQLAlchemy, Pandas, Matplotlib, Faker
Tools	pgAdmin, Jupyter Notebook
Version Control	Git & GitHub

---

📘 Concepts Covered

• Relational DB design & normalization (up to 3NF)
• Foreign key constraints and integrity
• Stored procedures & triggers
• Advanced SQL: joins, aggregates, CTEs, window functions
• Business-focused analytics (customer behavior, revenue trends, product performance)
• Query tuning with EXPLAIN ANALYZE
• Python integration for querying & visualization

---

📊 Analytics Queries

Located in queries/analysis_queries.sql.
Covers:

• Monthly revenue trend
• Top 5 best-selling products
• Revenue by payment mode
• Customers with highest lifetime value
• Orders with delayed shipments
• Repeat customers vs. one-time buyers

---

👤 Author

Hisham C T Aspiring Data Scientist | SQL & Python Enthusiast