📦 E-Commerce Order Management System

A Real-World SQL Final Project

📖 Overview

This project demonstrates the design and implementation of a real-world SQL database for an E-Commerce Order Management System. It covers the full lifecycle of database development — schema design, data population, queries, stored procedures, triggers, optimization, and analytics.

🎯 Key Features

✅ Normalized database schema (3NF) with constraints and relationships

✅ Sample data for testing

✅ Core SQL queries (reports & dashboards)

✅ Stored procedures and triggers for automation

✅ Optimization with indexes & query tuning

✅ Security (role-based access & SQL injection prevention)

✅ Advanced analytics reports (customer retention, revenue trends, stockout risk, etc.)

🛠️ Setup Instructions 1️⃣ Create Database CREATE DATABASE ecommerce_db; USE ecommerce_db;

2️⃣ Run Schema mysql -u root -p ecommerce_db < schema.sql

3️⃣ Insert Data mysql -u root -p ecommerce_db < data.sql

4️⃣ Run Queries & Reports mysql -u root -p ecommerce_db < queries.sql

5️⃣ Add Procedures & Triggers mysql -u root -p ecommerce_db < procedures.sql

6️⃣ Apply Optimization & Security mysql -u root -p ecommerce_db < optimization.sql mysql -u root -p ecommerce_db < security.sql

7️⃣ Generate Analytics Reports mysql -u root -p ecommerce_db < analytics.sql

📊 Example Reports

Top-Selling Products

Monthly Revenue Trends

Customer Lifetime Value

Repeat vs One-Time Buyers

Stockout Risk Products

Payment Method Distribution