🛠 04 Projects¶
Hands-on projects that apply the notes to realistic schema-design and query work — built around two recurring case studies (San Juan Sailboat Charters, James River Jewelry) and the Wedgewood Pacific sample database. Each project has its own folder with a full README (objective, scenario, step-by-step tasks, verification, deliverables, and stretch goals). All SQL runs in PostgreSQL.
| # | Project | Type | Primary module(s) | Difficulty |
|---|---|---|---|---|
| 01 | Conceptual ER Modeling | Modeling | 01, 04 | ⭐⭐ |
| 02 | Relational Schema & Keys | Schema design | 02, 05 | ⭐⭐ |
| 03 | SQL Querying & Joins | Query lab | 03 | ⭐⭐⭐ |
| 04 | Full ER Diagram Design | Modeling | 04 | ⭐⭐⭐ |
| 05 | Normalization & Schema Refinement | Schema design | 05 | ⭐⭐⭐ |
| 06 | Transactions & Concurrency Lab | Hands-on lab | 06 | ⭐⭐⭐⭐ |
| 07 | Mini Data Warehouse & BI | Schema + analytics | 07 | ⭐⭐⭐⭐ |
| 08 | Advanced SQL Capstone | Query lab | 08 | ⭐⭐⭐⭐ |
🧭 Module Coverage¶
Every module (01–08) is exercised by at least one project:
| Module | Projects |
|---|---|
| 01 Getting Started | 01 |
| 02 The Relational Model | 02 |
| 03 SQL Fundamentals | 03 |
| 04 Data Modeling & E-R Diagrams | 01, 04 |
| 05 Database Design & Normalization | 02, 05 |
| 06 Database Administration | 06 |
| 07 Data Warehousing, BI & Big Data | 07 |
| 08 Advanced SQL | 08 |
🧰 Tools¶
- PostgreSQL (free, postgresql.org) — every project's SQL runs here; SQLite works as a zero-install fallback for the query-only projects.
- pgAdmin or DBeaver — browsing schemas and running queries interactively.
- draw.io / diagrams.net (free) — E-R diagramming for projects 01 and 04.
Database dump/export files and any local
.sqlscratch files stay local — the repo tracks the README, design notes, and final schema/query scripts you choose to commit.
🗺 Suggested Order¶
- 01 → 02 — read a case, draft entities, then turn them into real tables and keys.
- 03 — stand up Wedgewood Pacific and get comfortable querying it.
- 04 → 05 — a full E-R diagram, then normalize a messy table to 3NF.
- 06 — transactions and concurrency, once schemas feel natural.
- 07 → 08 — analytics (star schema) and advanced SQL as the capstone.