SQL Sandbox

Interactive Relational Database Architecture & Query Reference

1. Relational Normalization (1NF – 3NF)

First Normal Form (1NF)

Each column must contain atomic (indivisible) values. No repeating groups or arrays stored in single cells.

Second Normal Form (2NF)

Table is in 1NF and all non-key attributes are fully functional-dependent on the primary key, eliminating partial dependencies.

Third Normal Form (3NF)

Table is in 2NF and has no transitive functional dependencies. Non-key columns depend exclusively on the candidate key.

2. Query Optimization & Index Strategies

B-Tree indexes provide logarithmic search complexity for point lookups and range scans. Hash indexes optimize direct equality tests.

-- Composite Index Optimization
CREATE INDEX idx_orders_customer_date 
ON orders (customer_id, order_date DESC);

-- Query Execution Plan Analysis
EXPLAIN ANALYZE
SELECT o.id, o.order_date, c.name, SUM(i.quantity * i.unit_price) AS total
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items i ON o.id = i.order_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY o.id, o.order_date, c.name;

3. Entity Relationship Architecture

CUSTOMERS PK id (UUID) email, created_at ORDERS PK id (BIGINT) FK customer_id ORDER_ITEMS PK id (BIGINT) FK order_id, sku