🛒 Simple E-Commerce Database (MySQL)

This project sets up a basic e-commerce database system using MySQL. It includes three primary tables—customers, orders, and products—with normalized extensions and a set of queries for data retrieval and manipulation.

---

📌 Project Objectives:

• Create a database named ecommerce.
• Design and implement three core tables:
  • customers
  • orders
  • products
• Normalize the database by adding an order_items table.
• Insert sample data into all tables.
• Write essential SQL queries to interact with the data.

---

📁 Database Structure:

📦 Database Name:

ecommerce

🧱 Tables

1. customers

• id (INT, PK, AUTO_INCREMENT): Unique customer ID
• name (VARCHAR): Customer's name
• email (VARCHAR): Customer's email
• address (VARCHAR): Customer's address

2. orders

• id (INT, PK, AUTO_INCREMENT): Unique order ID
• customer_id (INT, FK): References customers.id
• order_date (DATE): Date of the order
• total_amount (DECIMAL): Total order value

3. products

• id (INT, PK, AUTO_INCREMENT): Unique product ID
• name (VARCHAR): Product name
• price (DECIMAL): Product price
• description (TEXT): Product description

---

📄 Sample Data:

Sample entries are inserted into all three tables to demonstrate database functionality and facilitate query testing.

---

🔍 Queries Included:

1. Retrieve all customers who have placed an order in the last 30 days
2. Get total amount of all orders placed by each customer
3. Update the price of "Product C" to 45.00
4. Add a discount column to the products table
5. Retrieve the top 3 products with the highest price
6. Get the names of customers who have ordered "Product A"
7. Join orders and customers to get customer name and order date
8. Retrieve orders with total amount greater than 150.00
9. Normalize database with a new order_items table
10. Retrieve the average total of all orders

All queries use MySQL syntax and include clear comments for documentation.

---

⚙️ Normalization:

A new table order_items is created to normalize the schema:

• Supports many-to-many relationships between orders and products.
• Contains: id, order_id, product_id, quantity.

---

🧩 ERD Overview:

Customers └──< Orders └──< Order_Items >──┐ └── Products

---

ER Diagram (DB Design):

---

🙋‍♂️ Author & Contact:

• Developed by: Vignesh R
• GitHub: @VigneshRav
• Email: vignesh212000@gmail.com

---