Skip to main content

Module 1: Relational Databases & SQL: Worked Examples

These examples turn relational theory into decisions you can inspect. Attempt each problem before reading the worked path.

Example 1: Remove a Repeating Group Without Losing a Constraint​

Problem. An order table stores order_id, customer_email, and comma-separated product_ids. A product may appear in many orders, quantities matter, and an order must not contain the same product twice.

Wrong first attempt. Keep the comma-separated column and use string matching. This makes membership ambiguous, prevents a foreign key to products, and cannot express uniqueness per order.

Correct reasoning. The repeating group represents a many-to-many relationship with an attribute. Decompose it into orders(order_id, customer_id, ...) and order_items(order_id, product_id, quantity). The key of order_items is (order_id, product_id); foreign keys preserve membership and CHECK (quantity > 0) preserves the domain rule.

CREATE TABLE order_items (
  order_id bigint REFERENCES orders(order_id),
  product_id bigint REFERENCES products(product_id),
  quantity integer NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, product_id)
);

Why it works. Each fact has one home, the database can enforce the relationship, and joins replace unreliable string parsing.

Transfer question. If the same product may appear twice with different negotiated prices, which key and columns must change?

Example 2: Diagnose a Join That Inflates Revenue​

Problem. A report joins orders, order_items, and payments, then sums both item totals and payment amounts. Orders may have several items and several partial payments.

Wrong first attempt. Group only by order_id. The join creates an item-by-payment Cartesian multiplication inside each order, so both sums are inflated.

Correct reasoning. Aggregate each one-to-many relation to one row per order before joining.

WITH item_totals AS (
  SELECT order_id, SUM(quantity * unit_price) AS ordered_total
  FROM order_items GROUP BY order_id
), payment_totals AS (
  SELECT order_id, SUM(amount) AS paid_total
  FROM payments GROUP BY order_id
)
SELECT o.order_id, i.ordered_total, COALESCE(p.paid_total, 0) AS paid_total
FROM orders o
JOIN item_totals i USING (order_id)
LEFT JOIN payment_totals p USING (order_id);

Why it works. Each CTE establishes the invariant “one row per order” before the relations meet.

Transfer question. How would you retain orders with no items while also flagging them as invalid?

Example 3: Choose an Index From a Query, Not a Column​

Problem. The hot query filters by tenant_id, a time range on created_at, and returns newest rows first.

Wrong first attempt. Add separate indexes on both columns. The optimizer may combine them, but it still may sort and touch many irrelevant rows.

Correct reasoning. Use a composite B-tree (tenant_id, created_at DESC). Equality on the leading column narrows the tenant; the ordered suffix supports the range and output order.

Verify with EXPLAIN (ANALYZE, BUFFERS) on representative cardinalities. An index is justified only if the plan and measured work improve.

Transfer question. What changes if the query filters only by created_at across all tenants?

Completion Standard​

  • Reproduce the schema decomposition and explain every constraint.
  • Demonstrate the join inflation with a minimal dataset, then repair it.
  • Compare query plans before and after the composite index and record the evidence.