Project 1: Multi-Table E-Commerce Platform Database0%

Project 2: Banking Transaction & Ledger System with ACID Guarantees

Project 1: Multi-Table E-Commerce Platform Database

Intermediate20 min read•Updated: 2026-09-12

Project 1: Multi-Table E-Commerce Platform Database

Capstone Project 1: Multi-Table E-Commerce Platform Database

Welcome to the first Intermediate Capstone Project! In this comprehensive hands-on project, you will design, implement, populate, and query a production-ready Relational E-Commerce Database.

You will bridge everything learned across multi-table relationships, foreign key cascade constraints, multi-table joins, views, and Common Table Expressions.

1. Architectural Schema Design

Our platform consists of 5 normalized relational tables:

1
customers: Core user identity and contact details.
2
categories: Self-referencing hierarchical product categories.
3
products: Catalog items linked to categories.
4
orders: Order headers linked to customers.
5
order_items: Line-item junction table bridging orders and products with historical prices.

1[customers] 1 ────< N [orders] 1 ────< N [order_items] N >──── 1 [products]
2                                                                     │
3                                                              [categories] (Parent/Child)

2. Complete DDL Implementation Script

1CREATE DATABASE IF NOT EXISTS ecommerce_platform_db;
2USE ecommerce_platform_db;
3 
4-- 1. Customers Table
5CREATE TABLE customers (
6    customer_id INT AUTO_INCREMENT PRIMARY KEY,
7    first_name VARCHAR(50) NOT NULL,
8    last_name VARCHAR(50) NOT NULL,
9    email VARCHAR(100) NOT NULL UNIQUE,
10    country VARCHAR(50) NOT NULL,
11    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
12) ENGINE = InnoDB;
13 
14-- 2. Hierarchical Categories Table
15CREATE TABLE categories (
16    category_id INT AUTO_INCREMENT PRIMARY KEY,
17    name VARCHAR(100) NOT NULL,
18    parent_id INT NULL,
19    CONSTRAINT fk_cat_parent 
20        FOREIGN KEY (parent_id) REFERENCES categories(category_id) 
21        ON DELETE SET NULL
22) ENGINE = InnoDB;
23 
24-- 3. Products Table
25CREATE TABLE products (
26    product_id INT AUTO_INCREMENT PRIMARY KEY,
27    category_id INT NOT NULL,
28    sku VARCHAR(30) NOT NULL UNIQUE,
29    name VARCHAR(150) NOT NULL,
30    cost_price DECIMAL(10, 2) NOT NULL,
31    retail_price DECIMAL(10, 2) NOT NULL,
32    stock_quantity INT NOT NULL DEFAULT 0,
33    CONSTRAINT fk_prod_category 
34        FOREIGN KEY (category_id) REFERENCES categories(category_id)
35) ENGINE = InnoDB;
36 
37-- 4. Orders Table
38CREATE TABLE orders (
39    order_id INT AUTO_INCREMENT PRIMARY KEY,
40    customer_id INT NOT NULL,
41    order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
42    status ENUM('pending', 'shipped', 'delivered', 'cancelled') NOT NULL DEFAULT 'pending',
43    CONSTRAINT fk_order_customer 
44        FOREIGN KEY (customer_id) REFERENCES customers(customer_id) 
45        ON DELETE RESTRICT
46) ENGINE = InnoDB;
47 
48-- 5. Order Items Junction Table
49CREATE TABLE order_items (
50    order_item_id INT AUTO_INCREMENT PRIMARY KEY,
51    order_id INT NOT NULL,
52    product_id INT NOT NULL,
53    quantity INT NOT NULL CHECK (quantity > 0),
54    unit_price DECIMAL(10, 2) NOT NULL,
55    CONSTRAINT fk_item_order 
56        FOREIGN KEY (order_id) REFERENCES orders(order_id) 
57        ON DELETE CASCADE,
58    CONSTRAINT fk_item_product 
59        FOREIGN KEY (product_id) REFERENCES products(product_id)
60) ENGINE = InnoDB;

3. Inserting Realistic Seed Data

1-- Seed Customers
2INSERT INTO customers (first_name, last_name, email, country) VALUES
3('Liam', 'Smith', 'liam.smith@example.com', 'USA'),
4('Emma', 'Johnson', 'emma.j@example.com', 'UK'),
5('Noah', 'Williams', 'noah.w@example.com', 'Canada'),
6('Olivia', 'Brown', 'olivia.b@example.com', 'USA');
7 
8-- Seed Hierarchical Categories
9INSERT INTO categories (category_id, name, parent_id) VALUES
10(1, 'Electronics', NULL),
11(2, 'Computers', 1),
12(3, 'Laptops', 2),
13(4, 'Audio', 1),
14(5, 'Headphones', 4);
15 
16-- Seed Products
17INSERT INTO products (category_id, sku, name, cost_price, retail_price, stock_quantity) VALUES
18(3, 'LAP-MAC-16', 'MacBook Pro 16"', 1800.00, 2499.00, 25),
19(3, 'LAP-XPS-15', 'Dell XPS 15', 1200.00, 1699.00, 40),
20(5, 'AUD-WH-1000', 'Sony WH-1000XM5 Headphones', 220.00, 399.00, 80),
21(5, 'AUD-AIR-MAX', 'Apple AirPods Max', 350.00, 549.00, 15);
22 
23-- Seed Orders
24INSERT INTO orders (order_id, customer_id, order_date, status) VALUES
25(1001, 1, '2026-08-01 10:30:00', 'delivered'),
26(1002, 2, '2026-08-05 14:15:00', 'delivered'),
27(1003, 1, '2026-08-10 16:45:00', 'shipped'),
28(1004, 3, '2026-08-12 09:00:00', 'pending');
29 
30-- Seed Order Items
31INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
32(1001, 1, 1, 2499.00),
33(1001, 3, 2, 399.00),
34(1002, 2, 1, 1699.00),
35(1003, 4, 1, 549.00),
36(1004, 3, 1, 399.00);

4. Advanced Analytics & Production Queries

Query A: Customer Lifetime Value (LTV) with Revenue Ranking

1WITH CustomerSpend AS (
  SELECT 
      c.customer_id,
      CONCAT(c.first_name, ' ', c.last_name) AS full_name,
      c.country,
      COALESCE(SUM(oi.quantity * oi.unit_price), 0.00) AS total_spent,
      COUNT(DISTINCT o.order_id) AS orders_count
  FROM customers c
  LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.status != 'cancelled'
  LEFT JOIN order_items oi ON o.order_id = oi.order_id
  GROUP BY c.customer_id, c.first_name, c.last_name, c.country
12)
13SELECT 
  full_name,
  country,
  orders_count,
  total_spent,
  DENSE_RANK() OVER (ORDER BY total_spent DESC) AS revenue_rank
19FROM CustomerSpend;

Query B: Product Gross Margin & Sales Velocity View

1CREATE OR REPLACE VIEW v_product_performance AS
2SELECT 
3    p.product_id,
4    p.name AS product_name,
5    p.retail_price,
6    ROUND((p.retail_price - p.cost_price), 2) AS unit_margin,
7    ROUND(((p.retail_price - p.cost_price) / p.retail_price) * 100, 1) AS margin_percentage,
8    COALESCE(SUM(oi.quantity), 0) AS total_units_sold,
9    COALESCE(SUM(oi.quantity * oi.unit_price), 0.00) AS gross_revenue
10FROM products p
11LEFT JOIN order_items oi ON p.product_id = oi.product_id
12GROUP BY p.product_id, p.name, p.retail_price, p.cost_price;
13 
14-- Query top margin generators:
15SELECT * FROM v_product_performance ORDER BY gross_revenue DESC;

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Why does order_items store unit_price when products table already contains retail_price?

Question 2

In our schema, what happens to order_items when an order is deleted, based on ON DELETE CASCADE?

Question 3

What relationship exists between orders and products?

Question 4

Which category relationship model was implemented in categories?

Question 5

Why is ON DELETE RESTRICT specified on the customer_id foreign key in orders?

Next Lesson

Project 2: Banking Transaction & Ledger System with ACID Guarantees

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Password Management & Account Locking	Project 2: Banking Transaction & Ledger System with ACID Guarantees

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Project 2: Banking Transaction & Ledger System with ACID Guarantees

Project 1: Multi-Table E-Commerce Platform Database

Intermediate20 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Project 1: Multi-Table E-Commerce Platform Database

Capstone Project 1: Multi-Table E-Commerce Platform Database

Welcome to the first Intermediate Capstone Project! In this comprehensive hands-on project, you will design, implement, populate, and query a production-ready Relational E-Commerce Database.

You will bridge everything learned across multi-table relationships, foreign key cascade constraints, multi-table joins, views, and Common Table Expressions.

1. Architectural Schema Design

Our platform consists of 5 normalized relational tables:

1
customers: Core user identity and contact details.
2
categories: Self-referencing hierarchical product categories.
3
products: Catalog items linked to categories.
4
orders: Order headers linked to customers.
5
order_items: Line-item junction table bridging orders and products with historical prices.

1[customers] 1 ────< N [orders] 1 ────< N [order_items] N >──── 1 [products]
2                                                                     │
3                                                              [categories] (Parent/Child)

2. Complete DDL Implementation Script

1CREATE DATABASE IF NOT EXISTS ecommerce_platform_db;
2USE ecommerce_platform_db;
3 
4-- 1. Customers Table
5CREATE TABLE customers (
6    customer_id INT AUTO_INCREMENT PRIMARY KEY,
7    first_name VARCHAR(50) NOT NULL,
8    last_name VARCHAR(50) NOT NULL,
9    email VARCHAR(100) NOT NULL UNIQUE,
10    country VARCHAR(50) NOT NULL,
11    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
12) ENGINE = InnoDB;
13 
14-- 2. Hierarchical Categories Table
15CREATE TABLE categories (
16    category_id INT AUTO_INCREMENT PRIMARY KEY,
17    name VARCHAR(100) NOT NULL,
18    parent_id INT NULL,
19    CONSTRAINT fk_cat_parent 
20        FOREIGN KEY (parent_id) REFERENCES categories(category_id) 
21        ON DELETE SET NULL
22) ENGINE = InnoDB;
23 
24-- 3. Products Table
25CREATE TABLE products (
26    product_id INT AUTO_INCREMENT PRIMARY KEY,
27    category_id INT NOT NULL,
28    sku VARCHAR(30) NOT NULL UNIQUE,
29    name VARCHAR(150) NOT NULL,
30    cost_price DECIMAL(10, 2) NOT NULL,
31    retail_price DECIMAL(10, 2) NOT NULL,
32    stock_quantity INT NOT NULL DEFAULT 0,
33    CONSTRAINT fk_prod_category 
34        FOREIGN KEY (category_id) REFERENCES categories(category_id)
35) ENGINE = InnoDB;
36 
37-- 4. Orders Table
38CREATE TABLE orders (
39    order_id INT AUTO_INCREMENT PRIMARY KEY,
40    customer_id INT NOT NULL,
41    order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
42    status ENUM('pending', 'shipped', 'delivered', 'cancelled') NOT NULL DEFAULT 'pending',
43    CONSTRAINT fk_order_customer 
44        FOREIGN KEY (customer_id) REFERENCES customers(customer_id) 
45        ON DELETE RESTRICT
46) ENGINE = InnoDB;
47 
48-- 5. Order Items Junction Table
49CREATE TABLE order_items (
50    order_item_id INT AUTO_INCREMENT PRIMARY KEY,
51    order_id INT NOT NULL,
52    product_id INT NOT NULL,
53    quantity INT NOT NULL CHECK (quantity > 0),
54    unit_price DECIMAL(10, 2) NOT NULL,
55    CONSTRAINT fk_item_order 
56        FOREIGN KEY (order_id) REFERENCES orders(order_id) 
57        ON DELETE CASCADE,
58    CONSTRAINT fk_item_product 
59        FOREIGN KEY (product_id) REFERENCES products(product_id)
60) ENGINE = InnoDB;

3. Inserting Realistic Seed Data

1-- Seed Customers
2INSERT INTO customers (first_name, last_name, email, country) VALUES
3('Liam', 'Smith', 'liam.smith@example.com', 'USA'),
4('Emma', 'Johnson', 'emma.j@example.com', 'UK'),
5('Noah', 'Williams', 'noah.w@example.com', 'Canada'),
6('Olivia', 'Brown', 'olivia.b@example.com', 'USA');
7 
8-- Seed Hierarchical Categories
9INSERT INTO categories (category_id, name, parent_id) VALUES
10(1, 'Electronics', NULL),
11(2, 'Computers', 1),
12(3, 'Laptops', 2),
13(4, 'Audio', 1),
14(5, 'Headphones', 4);
15 
16-- Seed Products
17INSERT INTO products (category_id, sku, name, cost_price, retail_price, stock_quantity) VALUES
18(3, 'LAP-MAC-16', 'MacBook Pro 16"', 1800.00, 2499.00, 25),
19(3, 'LAP-XPS-15', 'Dell XPS 15', 1200.00, 1699.00, 40),
20(5, 'AUD-WH-1000', 'Sony WH-1000XM5 Headphones', 220.00, 399.00, 80),
21(5, 'AUD-AIR-MAX', 'Apple AirPods Max', 350.00, 549.00, 15);
22 
23-- Seed Orders
24INSERT INTO orders (order_id, customer_id, order_date, status) VALUES
25(1001, 1, '2026-08-01 10:30:00', 'delivered'),
26(1002, 2, '2026-08-05 14:15:00', 'delivered'),
27(1003, 1, '2026-08-10 16:45:00', 'shipped'),
28(1004, 3, '2026-08-12 09:00:00', 'pending');
29 
30-- Seed Order Items
31INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
32(1001, 1, 1, 2499.00),
33(1001, 3, 2, 399.00),
34(1002, 2, 1, 1699.00),
35(1003, 4, 1, 549.00),
36(1004, 3, 1, 399.00);

4. Advanced Analytics & Production Queries

Query A: Customer Lifetime Value (LTV) with Revenue Ranking

1WITH CustomerSpend AS (
  SELECT 
      c.customer_id,
      CONCAT(c.first_name, ' ', c.last_name) AS full_name,
      c.country,
      COALESCE(SUM(oi.quantity * oi.unit_price), 0.00) AS total_spent,
      COUNT(DISTINCT o.order_id) AS orders_count
  FROM customers c
  LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.status != 'cancelled'
  LEFT JOIN order_items oi ON o.order_id = oi.order_id
  GROUP BY c.customer_id, c.first_name, c.last_name, c.country
12)
13SELECT 
  full_name,
  country,
  orders_count,
  total_spent,
  DENSE_RANK() OVER (ORDER BY total_spent DESC) AS revenue_rank
19FROM CustomerSpend;

Query B: Product Gross Margin & Sales Velocity View

1CREATE OR REPLACE VIEW v_product_performance AS
2SELECT 
3    p.product_id,
4    p.name AS product_name,
5    p.retail_price,
6    ROUND((p.retail_price - p.cost_price), 2) AS unit_margin,
7    ROUND(((p.retail_price - p.cost_price) / p.retail_price) * 100, 1) AS margin_percentage,
8    COALESCE(SUM(oi.quantity), 0) AS total_units_sold,
9    COALESCE(SUM(oi.quantity * oi.unit_price), 0.00) AS gross_revenue
10FROM products p
11LEFT JOIN order_items oi ON p.product_id = oi.product_id
12GROUP BY p.product_id, p.name, p.retail_price, p.cost_price;
13 
14-- Query top margin generators:
15SELECT * FROM v_product_performance ORDER BY gross_revenue DESC;

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Why does order_items store unit_price when products table already contains retail_price?

Question 2

In our schema, what happens to order_items when an order is deleted, based on ON DELETE CASCADE?

Question 3

What relationship exists between orders and products?

Question 4

Which category relationship model was implemented in categories?

Question 5

Why is ON DELETE RESTRICT specified on the customer_id foreign key in orders?

Next Lesson

Project 2: Banking Transaction & Ledger System with ACID Guarantees

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Password Management & Account Locking	Project 2: Banking Transaction & Ledger System with ACID Guarantees

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next