E-Commerce Database Optimization
The Business Problem
Queries to find 'average delivery time per state' are taking 5 minutes to run. The data needs to be normalized, and the logistics team needs immediate answers.
Project Overview
You have been hired by a rapidly growing E-Commerce company. Their database is a mess—everything is stored in one giant flat table, causing massive performance issues. Your job is to design a proper relational database schema, migrate the data, and write complex queries to analyze shipping delays.
Step-by-Step Implementation
Follow this structured path to complete the project successfully.
Step 1: Diagram the Database
Use Draw.io to design a Star Schema with a central 'Orders' Fact table and Dimensions for Customers, Products, and Reviews.
Step 2: Create Tables
Write DDL (Data Definition Language) scripts to CREATE TABLES with proper Primary and Foreign Keys.
Step 3: Import Data
Load the Kaggle CSVs into your new normalized tables.
Step 4: Business Queries
Write a query to find the top 5 worst-performing delivery cities. Write another to find the correlation between review scores and shipping delays.
Expected Output
A professional ERD diagram proving you understand backend data architecture, alongside clean, optimized SQL scripts.
Common Mistakes to Avoid
More Intermediate Projects
Netflix Content Strategy Analysis
Analyze the entire Netflix catalog using Power BI to uncover content trends, durations, and geographical focus.
View Project
HR Employee Attrition Tracker
Write complex SQL queries to extract employee trends and build a Tableau dashboard for the HR department.
View Project