Chapter 17 — Key Takeaways

The big idea

ER modeling designs your data conceptually — before any CREATE TABLE. This is theme #1 in action: a good model makes everything downstream (tables, normalization, queries) fall into place; a bad one dooms it. Model first, build second.

The three building blocks

  • Entities — things your system tracks (nouns → tables): customer, order, product.
  • Attributes — properties of entities: simple, composite (→ split into parts), multi-valued (→ separate table), derived (→ often not stored), key (→ primary key).
  • Relationships — associations (verbs): "customer places order."

Cardinality (a relationship's defining property)

  • 1:1 — rare; sometimes a deliberate split.
  • 1:N — most common (one customer, many orders) → foreign key on the "many" side.
  • M:N — many↔many (orders↔products) → requires a junction table (order_items).

Participation

  • Total/mandatory (every order must have a customer) → NOT NULL FK.
  • Partial/optional (an order may have no rep) → nullable FK.
  • Also tells you INNER vs LEFT JOIN when querying (Ch. 6).

Two cures to memorize

  • M:N → junction table (with a composite key). A relationship that carries data (a grade, a tag-applied-date) is a junction entity.
  • Multi-valued attribute → separate table. A list in a column (comma-separated tags!) is an unmodeled M:N. (Case Study 2.)

Notation & process

  • Crow's foot (practical, tool-friendly: │=one, ○=zero, <=many) vs. Chen (academic: rectangles/ovals/diamonds).
  • Weak entity — can't be identified alone; borrows an owner's key (order_items).
  • Process: requirements → conceptual (ER) → logical (tables) → physical (DDL). Do the conceptual step first.

Recurring patterns (spot them)

Order-items (M:N), category/org hierarchy (self-ref 1:N), user-roles & tagging (M:N junctions), polymorphic associations (Ch. 21).

Common mistakes

Cramming a list into one column; missing an M:N; confusing attribute vs. entity; ignoring participation; designing tables before modeling.

You can now…

  • ☐ Extract entities, attributes (all 5 kinds), and relationships from requirements.
  • ☐ Determine cardinality (1:1/1:N/M:N) and participation (total/partial).
  • ☐ Resolve M:N with junctions; move multi-valued attributes to their own tables.
  • ☐ Read/draw crow's-foot and Chen diagrams.
  • ☐ Spot common patterns; follow the design process.

Looking ahead

Chapter 18 — From ER Diagram to Tables. The mechanical rules to turn your model into tables, keys, and foreign keys — resolving M:N, mapping inheritance, and producing the DDL.

One sentence to carry forward: Find the entities (nouns) and relationships (verbs), nail each relationship's cardinality and participation, and resolve every many-to-many with a junction table — that conceptual model is the foundation everything else rests on.