Skip to content

TASKS.md

Latest commit

 

History

History
34 lines (27 loc) · 1.93 KB

TASKS.md

File metadata and controls

34 lines (27 loc) · 1.93 KB
Raw

📋 Project Tasks: Online Shopping Database

📌 Problem Statement

Small to Medium E-commerce businesses often face "Data Anomalies" due to poor database design, leading to:

  1. Overselling: Selling products that are out of stock.
  2. Orphaned Records: Deleting a user but leaving their orders in the system.
  3. Lack of Insight: Inability to track "Customer Lifetime Value" or "Churn Risk."

🎯 Project Objectives

  • Build a robust Relational Database Management System (RDBMS).
  • Implement ACID Transactions to ensure financial safety.
  • Automate inventory management using Database Triggers.
  • Visualize sales performance using a Python Dashboard.

✅ Tasks Completed

Phase 1: Schema Design

  • ER Modeling: Designed a schema with Users, Products, Categories, Orders, and Order Items.
  • Normalization: Achieved 3rd Normal Form (3NF) to reduce data redundancy.
  • Constraints: Applied UNIQUE, NOT NULL, and CHECK constraints for data validity.

Phase 2: Backend Logic (SQL)

  • Automated Trigger: Created after_order_item_insert trigger to automatically decrement stock levels.
  • Safe Transactions: Wrote a Stored Procedure place_order using COMMIT and ROLLBACK to handle payment failures.
  • Complex Queries: Developed 20 business queries covering Revenue, Customer Segmentation, and Inventory Analysis.

Phase 3: Data Engineering

  • Synthetic Data: Used Python (Faker) to generate 1,000+ realistic records.
  • Pipeline: Built a Python-to-MySQL connection pipeline for automated data insertion.

Phase 4: Business Intelligence

  • KPI Dashboard: Built a Jupyter Notebook to visualize Monthly Revenue and Category Performance.
  • Customer Analysis: Identified VIP customers and Churn risks using SQL Aggregation.
  • Technical Report: Documented the system architecture and performance results in a professional report.