🛒 E-Commerce Database Management System

A Relational Database System for Online Shopping Platforms — Built with MySQL

---

📖 Project Overview

The E-Commerce Database Management System is a SQL-based project built with MySQL that models the backend database of an online shopping platform. It covers the core operations of an e-commerce system — user accounts, product catalogs, categories, shopping carts, orders, payments, and product reviews — all through a clean, normalized relational schema.

The goal of this project is to demonstrate solid database design principles alongside practical SQL skills: table creation, primary and foreign key relationships, joins, aggregate functions, subqueries, and views.

🎯 Objective

To design and implement a relational database that efficiently stores and manages e-commerce data, while demonstrating core SQL concepts such as table creation, relationships, joins, aggregate functions, subqueries, and views.

---

✨ Features

• 👤 User Management — Store and manage customer account details
• 📦 Product Management — Maintain a catalog of products with pricing and stock info
• 🗂️ Category Management — Organize products under different categories
• 🛍️ Shopping Cart — Track items added by users before checkout
• 📑 Order Management — Handle order creation and multi-product orders
• 💳 Payment Tracking — Record payment method and status for each order
• ⭐ Product Reviews — Allow users to rate and review products
• 👁️ SQL Views — Simplified, reusable virtual tables for common queries
• 🔍 Basic SQL Queries — Filtering, sorting, and searching data
• 🔗 Join Queries — Combine data across multiple related tables
• 📊 Aggregate Queries — Generate insights using SUM, AVG, COUNT, etc.
• 🧠 Subqueries — Handle complex, nested data retrieval

---

🛠️ Technologies Used

Technology	Purpose
MySQL	Relational database management system
SQL	Querying, schema design, and data manipulation
VS Code	Development and script editing environment

---

🗃️ Database Tables

#	Table Name	Description
1	Users	Stores customer account information
2	Categories	Stores product category details
3	Products	Stores product listings and details
4	Cart	Stores items added by users to their cart
5	Orders	Stores order details placed by users
6	Order_Items	Stores individual products within each order
7	Payments	Stores payment information linked to orders
8	Reviews	Stores user reviews and ratings for products

📋 Table Columns

Users user_id name email phone Categories category_id category_name

Products product_id product_name price stock category_id Cart cart_id user_id product_id quantity

Orders order_id user_id order_date total_amount Order_Items order_item_id order_id product_id quantity price

Payments payment_id order_id payment_method payment_status Reviews review_id user_id product_id rating review

---

🔗 Table Relationships

• 👤 One user can place many orders
• 🗂️ One category contains many products
• 📦 One product belongs to one category
• 📑 One order contains multiple products through Order_Items
• 💳 One order has one payment
• ⭐ One user can write multiple reviews
• ⭐ One product can receive multiple reviews
• 🛍️ One user can add multiple products to the cart

---

📁 Folder Structure

E-COMMERCE-DATABASE-MANAGEMENT-SYSTEM
│
├── 📂 Database & Table Creation
│   ├── create_database.sql
│   └── create_tables.sql
│
├── 📂 Data Insertion
│   └── insert_data.sql
│
├── 📂 Queries
│   ├── basic_queries.sql
│   ├── join_queries.sql
│   ├── aggregate_queries.sql
│   ├── subqueries.sql
│   └── views.sql
│
├── 📂 Images
│   └── ecommerce_er_diagram.png
│
└── README.md

---

🧠 SQL Concepts Covered

Database & Table Basics CREATE DATABASE CREATE TABLE PRIMARY KEY FOREIGN KEY INSERT INTO

Querying & Filtering SELECT WHERE ORDER BY DISTINCT LIKE BETWEEN LIMIT

Advanced SQL COUNT, SUM, AVG, MAX, MIN GROUP BY, HAVING INNER JOIN Multiple Table JOINs Subqueries SQL Views

---

📊 Sample Data

Table	Records
Users	50
Categories	8
Products	60
Cart Records	50
Orders	50
Order Items	80
Payments	50
Reviews	50

---

🧩 ER Diagram

---

⚙️ How to Run the Project

This project can be run either in MySQL Workbench (or the MySQL CLI) or in an online SQL editor such as OneCompiler.

1️⃣ Clone the Repository

git clone https://github.com/pujaripunithkumar/E-Commerce-Database-Management-System.git
cd E-Commerce-Database-Management-System

2️⃣ Option A — Run in MySQL Workbench / MySQL CLI

Open MySQL Workbench, the MySQL CLI, or VS Code with a MySQL extension, then run the scripts in order:

SOURCE "Database & Table Creation/create_database.sql";
SOURCE "Database & Table Creation/create_tables.sql";
SOURCE "Data Insertion/insert_data.sql";
SOURCE "Queries/basic_queries.sql";
SOURCE "Queries/join_queries.sql";
SOURCE "Queries/aggregate_queries.sql";
SOURCE "Queries/subqueries.sql";
SOURCE "Queries/views.sql";

3️⃣ Option B — Run in an Online SQL Editor (e.g. OneCompiler)

Most online SQL editors don't allow creating or switching databases, so you'll need to make two small adjustments:

• Skip or remove the CREATE DATABASE statement from create_database.sql
• Skip or remove the USE database_name statement

Then simply run the contents of create_tables.sql, followed by insert_data.sql, and finally the query files in the Queries/ folder — directly in the editor's SQL console.

✅ The database is now fully set up and ready to explore!

---

🔍 Sample Queries

1. Retrieve all products under a specific category

SELECT p.product_name, p.price, c.category_name
FROM Products p
INNER JOIN Categories c ON p.category_id = c.category_id
WHERE c.category_name = 'Electronics';

2. Find the total amount spent by each user

SELECT u.name, SUM(o.total_amount) AS total_spent
FROM Users u
INNER JOIN Orders o ON u.user_id = o.user_id
GROUP BY u.name
ORDER BY total_spent DESC;

3. List the top 5 highest-rated products

SELECT p.product_name, AVG(r.rating) AS avg_rating
FROM Reviews r
INNER JOIN Products p ON r.product_id = p.product_id
GROUP BY p.product_name
ORDER BY avg_rating DESC
LIMIT 5;

4. Find users who have placed more than 3 orders (Subquery + HAVING)

SELECT name
FROM Users
WHERE user_id IN (
    SELECT user_id
    FROM Orders
    GROUP BY user_id
    HAVING COUNT(order_id) > 3
);

5. Create a view for order summaries

CREATE VIEW Order_Summary AS
SELECT o.order_id, u.name AS customer_name, p.product_name,
       oi.quantity, oi.price, pay.payment_status
FROM Orders o
JOIN Users u ON o.user_id = u.user_id
JOIN Order_Items oi ON o.order_id = oi.order_id
JOIN Products p ON oi.product_id = p.product_id
JOIN Payments pay ON o.order_id = pay.order_id;

---

🎓 Learning Outcomes

Through this project, I practiced and demonstrated:

• ✅ Designing normalized relational database schemas
• ✅ Establishing primary key and foreign key relationships
• ✅ Writing efficient SELECT queries with filtering and sorting
• ✅ Performing multi-table joins to combine related data
• ✅ Using aggregate functions for data analysis and reporting
• ✅ Writing subqueries for complex data retrieval
• ✅ Creating and using SQL views for simplified data access
• ✅ Populating and managing a database with realistic sample data

---

🚀 Future Enhancements

• 📈 Visualize sales and order data using a BI tool
• 🧾 Add stored procedures and triggers for common automation tasks
• ☁️ Deploy the database on a cloud platform
• 📬 Extend the schema to track order status changes over time

---

🏁 Conclusion

The E-Commerce Database Management System demonstrates the practical application of relational database concepts in a real-world scenario. From schema design to advanced querying, this project reflects a solid understanding of SQL fundamentals and database management principles, and forms a strong foundation for building scalable, data-driven applications.

---

👨‍💻 Author

Pujari Punith Kumar B.Tech CSE (AI & ML)

GitHub: https://github.com/pujaripunithkumar

---

⭐ If you found this project helpful, consider giving it a star on GitHub!