Homework: E-Commerce Database Design

Estimated Time: 2-3 hours

Module 3: Session 2 Homework

Assignment Overview

Design and implement a complete, normalized database schema for an e-commerce website similar to Amazon or Shopify. Apply all the concepts learned in Session 2!

🎯

Learning Goals

This homework will test your understanding of:

Entity-Relationship modeling
Database normalization (3NF)
Primary and foreign keys
Appropriate use of constraints
Index optimization
Real-world database design

Business Requirements

Detailed Requirements

1. User Management

Customers can register and manage their profiles
Multiple shipping and billing addresses per customer
Password reset functionality
Wishlist/Favorites functionality
Order history tracking

2. Product Catalog

Hierarchical categories (parent-child relationships)
Products can belong to multiple categories
Product variants (size, color, etc.)
Inventory tracking per variant
Product images gallery
Product attributes (brand, material, etc.)

3. Shopping Cart & Orders

Persistent cart for logged-in users
Guest checkout capability
Order status workflow (pending → processing → shipped → delivered)
Order items with price at time of purchase
Shipping methods and tracking

4. Payments

Multiple payment methods (credit card, PayPal, etc.)
Transaction history
Refund processing
Invoice generation

5. Reviews & Ratings

Product reviews with ratings (1-5 stars)
Review helpfulness voting
Verified purchase indicator
Q&A for products

Part 1: Entity-Relationship Diagram

📝

Task 1: Create an ER Diagram

Design an ER diagram showing:

All entities (minimum 15 tables)
Attributes for each entity
Primary keys clearly marked
Relationships with cardinality
Foreign keys indicated

Tools you can use:draw.io, Lucidchart, MySQL Workbench, or hand-drawn

erDiagram CUSTOMER ||--o{ ORDER : places CUSTOMER ||--o{ ADDRESS : has CUSTOMER ||--o{ CART : has CUSTOMER ||--o{ WISHLIST : has CUSTOMER ||--o{ REVIEW : writes PRODUCT ||--o{ CART_ITEM : contains PRODUCT ||--o{ ORDER_ITEM : contains PRODUCT ||--o{ PRODUCT_IMAGE : has PRODUCT ||--o{ REVIEW : receives PRODUCT }o--|| CATEGORY : belongs_to ORDER ||--|{ ORDER_ITEM : contains ORDER ||--|| PAYMENT : has ORDER ||--|| SHIPPING : has CART ||--|{ CART_ITEM : contains

This is a simplified example. Your diagram should be more detailed!

Part 2: Normalization

📝

Task 2: Apply Normalization

Ensure your database design is in Third Normal Form (3NF):

1NF:Eliminate repeating groups, ensure atomic values
2NF:Remove partial dependencies
3NF:Remove transitive dependencies

Document any intentional denormalization and explain why.

Example: Normalizing Order Data

-- Before normalization (problematic design)
CREATE TABLE orders_bad (
    order_id INT,
    customer_name VARCHAR(100),
    customer_email VARCHAR(100),
    customer_phone VARCHAR(20),
    product1_id INT,
    product1_name VARCHAR(100),
    product1_price DECIMAL(10,2),
    product1_quantity INT,
    product2_id INT,
    product2_name VARCHAR(100),
    product2_price DECIMAL(10,2),
    product2_quantity INT
    -- Problems: Repeating groups, redundancy, limited products
);

-- After normalization (proper design)
CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(20)
);

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Part 3: Database Implementation

📝

Task 3: Write SQL Scripts

Create complete SQL scripts that include:

CREATE DATABASE statement
All CREATE TABLE statements with:
- Appropriate data types
- PRIMARY KEY constraints
- FOREIGN KEY constraints with CASCADE rules
- NOT NULL constraints where appropriate
- UNIQUE constraints for unique fields
- CHECK constraints for validation
- DEFAULT values where sensible
CREATE INDEX statements for optimization
Sample INSERT statements (at least 5 per table)

-- Your database should include tables similar to these:

CREATE DATABASE ecommerce_db;
USE ecommerce_db;

-- Categories table (hierarchical)
CREATE TABLE categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    parent_category_id INT,
    category_name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (parent_category_id) REFERENCES categories(category_id)
        ON DELETE SET NULL,
    INDEX idx_parent (parent_category_id),
    INDEX idx_slug (slug),
    INDEX idx_active (is_active)
);

-- Continue with all other tables...
-- Remember to include:
-- customers, addresses, products, product_variants, 
-- product_images, inventory, cart, cart_items,
-- orders, order_items, payments, shipping,
-- reviews, coupons, etc.

Part 4: Advanced Features

📝

Task 4: Implement Advanced Features

Choose at least 3 of these advanced features to implement:

Audit Trail:Track all changes to orders
Soft Deletes:Mark records as deleted without removing
Price History:Track product price changes over time
Multi-currency:Support multiple currencies
Inventory Tracking:Real-time stock management
Discount System:Complex coupon and promotion rules
Search Optimization:Full-text search indexes

Part 5: Query Practice

📝

Task 5: Write Complex Queries

Write SQL queries to answer these business questions:

Find the top 10 best-selling products this month
Calculate the average order value per customer
List products that are low in stock (less than 10 units)
Find customers who haven't ordered in the last 6 months
Calculate monthly revenue for the past year
Find the most reviewed products with average rating > 4
List all orders with their current status and shipping info
Calculate cart abandonment rate

-- Example: Top 10 best-selling products this month
SELECT 
    p.product_id,
    p.product_name,
    COUNT(DISTINCT o.order_id) as order_count,
    SUM(oi.quantity) as total_quantity_sold,
    SUM(oi.quantity * oi.unit_price) as total_revenue
FROM products p
INNER JOIN order_items oi ON p.product_id = oi.product_id
INNER JOIN orders o ON oi.order_id = o.order_id
WHERE 
    o.order_date >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
    AND o.status != 'cancelled'
GROUP BY p.product_id
ORDER BY total_quantity_sold DESC
LIMIT 10;

Submission Requirements

What to Submit

📋

Deliverables Checklist

✅ER Diagram(PDF or image format)
✅Database Schema Documentation(explaining design decisions)
✅SQL Script File(.sql) containing:
- Database creation
- All table definitions
- Indexes
- Sample data inserts
✅Query Solutions(8 business queries)
✅Advanced Featuresimplementation (choose 3)
✅README fileexplaining:
- How to set up the database
- Any assumptions made
- Normalization decisions
- Performance optimizations

Grading Criteria

Criteria	Points	Description
ER Diagram	20	Complete, accurate, properly shows relationships
Normalization	20	Proper 3NF, no redundancy, justified denormalization
SQL Implementation	25	Correct syntax, appropriate constraints, indexes
Business Queries	15	Correct results, efficient queries
Advanced Features	15	Proper implementation of chosen features
Documentation	5	Clear README, good comments

Tips and Hints

Success Tips

🎯 Start with the ER diagram - it's your blueprint
📝 List all entities first, then add attributes
🔗 Identify relationships carefully - they determine foreign keys
🔄 Apply normalization rules systematically
⚡ Think about query performance when adding indexes
🛡️ Use constraints to enforce business rules
📊 Test your queries with sample data
📚 Document your decisions and assumptions

💡

Common Pitfalls to Avoid

❌ Storing calculated values (violates normalization)
❌ Using VARCHAR for dates or numbers
❌ Forgetting to index foreign key columns
❌ Not handling NULL values properly
❌ Circular foreign key dependencies
❌ Over-normalization (too many JOINs needed)
❌ Forgetting CASCADE rules on foreign keys

Bonus Challenge (Optional)

🏆

Extra Credit Opportunities

Implement a recommendation system (customers who bought X also bought Y)
Add multi-vendor marketplace support
Create stored procedures for complex operations
Implement database triggers for audit logging
Add full-text search with relevance scoring
Design for international support (multi-language, multi-currency)
Create views for common reporting queries

PHP WordPress Development

Course Progress