Chapter 3: ER Diagrams & Database Design

---

Metadata Card

Dimension	Value
Difficulty	(Intermediate Beginner)
Prerequisites	Chapter 1 SQL (CREATE TABLE, JOIN), Chapter 2 Relational Algebra
Keywords	ER diagram, entity, attribute, relationship, functional dependency, normalization, 1NF, 2NF, 3NF, BCNF
Code Language	SQL / ER diagrams (text)

---

Your Progress

Master Chen hands you a yellowed blueprint paper, edges curled, a few water stain marks.

"This is the blueprint left by the previous warehouse manager," Chen says. "I only found it after he left. Look — can you see what's wrong?"

You unfold it. The layout shows a large warehouse — all shelves, aisles, and receiving counters densely marked. It looks complete at first glance, but you notice discrepancies: an item's ID appears on three different shelf labels; the returns area is drawn overlapping with the receiving counter, no separate desk; a corner labeled "temporary storage" has no labels, only five words — "sort later."

Database design is the same. You CREATE TABLE casually, stuff all fields into one table — it looks fine at first glance, but it's already planted the seeds of duplicate writes, update omissions, and deletion loss.

Problem: One Table for Everything

CREATE TABLE quest_records (
  quest_id INTEGER PRIMARY KEY,
  adventurer_id INTEGER,
  adventurer_name TEXT,
  contact_rune TEXT,
  item_id INTEGER,
  item_name TEXT,
  item_type TEXT,
  value REAL,
  quantity INTEGER,
  start_date TEXT,
  target_location TEXT
);

Problems: Redundancy (adventurer info repeated across rows); Update anomaly (must update every row); Insertion anomaly (can't add an adventurer without a quest); Deletion anomaly (delete quest, lose all adventurer info).

Root cause: One table contains two or more different "things" — adventurer attributes, item attributes, and quest attributes all mixed together.

ER Diagram: Draw Before Building

Three core constructs:

1. Entity — A "thing" you can count: Adventurer, Item, Quest, Guild
2. Attribute — Characteristics of an entity: name, level, value
3. Relationship — How entities relate: Adventurer "accepts" Quest, Quest "contains" Item

Cardinality:

• 1:1 (One-to-One): One guard per post. Use foreign key + unique constraint
• 1:N (One-to-Many): One adventurer has many quests. Foreign key on "many" side
• M:N (Many-to-Many): Many quests contain many items. Intersection table required

Functional Dependencies

X → Y means "X determines Y" — given the value of X, you can uniquely determine Y.

adventurer_id → name, contact_rune
item_id → item_name, item_type, value
(quest_id, item_id) → quantity

Normal Forms

Normal Form	Rule	What It Solves
1NF	Every column is atomic (no arrays, no nested tables)	Unquerable structures
2NF	1NF + Every non-key column depends on the entire primary key	Partial dependency
3NF	2NF + No transitive dependency (non-key → non-key)	Transitive dependency
BCNF	For every X → Y, X must be a superkey	"Just in case" anomalies
4NF	No multi-valued dependencies	Independent multi-valued facts
5NF	Every join dependency implied by candidate keys	Theoretical completeness

Example: An quest_records table with repeated adventurer info violates 2NF. Split into adventurers, items, and quests tables.

---

Traveler's Notes

Don't treat normalization as dogma. When you've seen a 50-column table and been bitten by data inconsistency three times, you'll come back grateful for these "constraints" — they're not restrictions, they're guardrails. Good database design isn't about putting as many columns as possible into one table; it's about splitting tables as cleanly as needed.