Capstone: An Online Store Database
Design it, build it, protect it, and report on it — end to end
Big Idea: A real application database is not one clever query — it is a system. In this project you will model a small online store as four related tables, give it a normalized schema guarded by constraints, add the indexes its queries need, place orders safely inside a transaction, and answer business questions with CTEs and CASE. Every skill from topics 1-13 comes together here.
By the end you will be able to:
- turn store requirements into entities and relationships and read an ER diagram,
- write a normalized (3NF) schema whose
CREATE TABLEstatements enforce integrity with keys,NOT NULL,UNIQUEandCHECK, - choose indexes that match the store's real queries,
- place an order atomically so stock and orders never drift apart,
- build reports (top customers, revenue by product, monthly sales) with
WITHandCASE.
1. The domain: entities and relationships
Start from the language of the business. A store has customers who place orders; each order contains one or more products, and for each product on an order we record how many were bought and the price at that moment. Those nouns become our tables:
| Entity | Meaning | Key relationship |
|---|---|---|
| customers | people who can buy | one customer -> many orders |
| orders | a single purchase event | one order -> many order_items |
| products | things for sale | one product -> many order_items |
| order_items | one product line on one order | links an order to a product |
An order can hold many products, and a product can appear on many orders — a many-to-many relationship. We never join two tables directly across a many-to-many link; instead we introduce a junction (bridge) table, order_items, that holds one row per product-on-an-order plus the extra facts (quantity, unit price) that belong to that pairing.
order_items bridge.