03-database-design

03 — Database Design

Exercises for the Database Design course.

This course is less about typing SQL and more about thinking before you type SQL. You'll design schemas from real-world requirements, spot problems in bad schemas, and normalize messy tables into clean 3NF.

Most of the work in these sections is done on paper (or in an ERD tool) before you ever open psql. But every exercise has a concrete deliverable — a CREATE TABLE statement, a rewritten schema, or a set of normalized tables — that you run and verify.

Sections

Section	What you'll practice
02 — Database Design	Turning requirements into entities, attributes, relationships, and CREATE TABLE statements. PKs, FKs, data types, constraints, cardinality.
03 — Normalization	Spotting update/insert/delete anomalies. Walking a denormalized table through 1NF → 2NF → 3NF. Partial and transitive dependencies.
04 — Exercise 1	Design a YouTube-style video platform from a written brief.
05 — Exercise 2	Design "Tick Trade" — a marketplace for buying and selling products.

Prerequisites

• PostgreSQL installed and psql working
• You've completed ../SETUP.md and created a sql_exercise database
• You've worked through 01-up-and-running-with-sql and 02-sql-fundamentals, or are comfortable writing CREATE TABLE, INSERT, SELECT, and JOIN statements

Suggested order

Do them in order. The normalization section builds on the design section, and the two capstone exercises (04 and 05) assume you're comfortable with both.

Before you start each section, read the brief and sketch your answer on paper. Don't jump into psql until you've thought about the entities and how they connect. This is the habit the course is trying to build.

Schemas

Each section uses its own Postgres schema so nothing collides:

Section	Schema
02 — Database Design	dd_design
03 — Normalization	dd_normalization
04 — Exercise 1	dd_exercise1
05 — Exercise 2	dd_exercise2

Each section's exercises.md shows you how to set search_path so you don't have to prefix every table name.

A note on solutions

Schema design is not like writing a SELECT — there's rarely one correct answer. The solutions in solutions/03-database-design/ are reference designs, not the only possible designs. If your PKs, FKs, and data types are sensible and you've captured the requirements, you've done it right even if your table names and shapes differ from mine.