E-Commerce Database & Analytics Dashboard

What I built

I created a full e-commerce system with a SQL Server backend and an interactive web dashboard. The database handles the complete order flow - customers, products, orders, payments, and reviews across seven normalized tables. The frontend is a real-time analytics dashboard that visualizes this data and lets you manage products directly.

Database work

Designed normalized schema with proper constraints and relationships. Built stored procedures for order processing and bulk data imports, created views for common queries (top sellers, processing orders), and added triggers to automatically update inventory. Wrote optimized queries with joins and aggregations, added indices on frequently accessed columns.

Dashboard

Built with Dash and Plotly. Seven different visualizations let you explore different aspects of the data - product pricing vs stock levels, order status breakdowns, payment method trends, category performance. Integrated CRUD forms on the side so you can add, update, or delete products and see changes reflected immediately in the dashboard.

Skills

• SQL Server: schema design, stored procedures, triggers, views, query optimization
• Python: Dash, Plotly, SQLAlchemy, pandas
• Database fundamentals: normalization, foreign keys, transactions, error handling
• Data visualization: multi-axis charts, scatter plots, pie charts, bar charts
• UI/UX: responsive layout, dark theme, real-time updates

Project Structure

DB_Git/
├── database/
│   ├── schema/              # Database table definitions and indices
│   ├── procedures/          # Stored procedures for business logic
│   ├── functions/           # User-defined functions
│   ├── views/               # Database views for common queries
│   ├── triggers/            # Automated inventory updates
│   └── sample-data/         # Sample data for testing
├── dashboard/
│   └── app.py              # Interactive Dash web application
├── docs/
│   └── ERD.jpg             # Entity Relationship Diagram
└── setup/
    └── setup-instructions.md  # Installation and setup guide

Features

Database Features

• 7 normalized tables with proper foreign key relationships
• Stored procedures for order placement and total calculation
• Table-valued functions for customer order history
• Views for top-selling products and processing orders
• Trigger for automatic inventory updates
• Optimized indices for common queries

Dashboard Features

• Interactive data visualizations for all database tables
• Real-time CRUD operations for product management
• Dark theme with responsive design
• Multiple chart types: scatter plots, bar charts, pie charts, line graphs
• Automatic data refresh after updates
• Transaction handling with error feedback

Setup Instructions

See setup/setup-instructions.md for detailed installation steps.

Technologies Used

• Database: Microsoft SQL Server
• Backend: Python 3.x, SQLAlchemy
• Frontend: Dash, Plotly
• Data Processing: pandas
• Database Driver: pyodbc

Database Schema