Chapter 3 — Key Takeaways

The big idea

The relational model represents data as relations (tables) that are sets of tuples (rows) of attribute (column) values from domains (types). Because relations are sets, rows have no inherent order and should be uniquely identifiable. This precise vocabulary — plus keys, NULL, and constraints — underlies every later chapter.

Vocabulary

Formal Everyday
relation table
tuple row
attribute column
domain the allowed values (≈ type + constraints)
degree / cardinality number of columns / number of rows
  • A relation is a set → no duplicate rows, no inherent order (use ORDER BY), values are atomic.

Keys

  • Superkey: any unique-identifying column set. Candidate key: a minimal superkey. Primary key: the chosen candidate — unique + never NULL (entity integrity).
  • Surrogate (generated, meaningless, stable) vs. natural (from the data, can change). Prefer a surrogate PK; protect natural keys with UNIQUE. (Case Study 1.)
  • Composite keys span multiple columns: order_items(order_id, product_id), inventory(product_id, warehouse_id).

Foreign keys

  • A foreign key must match an existing primary-key value (or be NULL): this is referential integrity.
  • Foreign keys make "an order for a customer who doesn't exist" impossible, and control deletes via ON DELETE CASCADE / SET NULL / RESTRICT (Chapter 14).

NULL and three-valued logic

  • NULL means unknown or absent — not zero, not empty string, not false.
  • Logic is three-valued: 5 = NULL → NULL; NULL = NULL → NULL. Test with IS NULL / IS NOT NULL, never =.
  • WHERE keeps only rows that are exactly true, so <>, IN, NOT IN silently exclude NULL rows (Case Study 2).
  • Aggregates (SUM, AVG, COUNT(col)) skip NULLs; COUNT(*) counts rows.

Integrity constraints (the database enforces your rules, once, for everyone)

NOT NULL · UNIQUE · primary key · foreign key · CHECK. Declaring CHECK (rating BETWEEN 1 AND 5) means no path into the database can ever store a 7.

Why the model endured

  1. Mathematical (sets + logic) → queries can be optimized and proven correct.
  2. Data independence → logical model is separate from physical storage; queries survive storage changes.
  3. Declarative integrity → rules enforced universally, not re-coded per app.

You can now…

  • ☐ Use relation/tuple/attribute/domain/degree/cardinality precisely.
  • ☐ Explain why rows have no order and what that means for queries.
  • ☐ Distinguish super/candidate/primary keys; choose surrogate vs. natural; design composite keys.
  • ☐ Explain referential integrity and what foreign keys guarantee.
  • ☐ Apply three-valued logic and test for NULL correctly.
  • ☐ Name the constraints and explain how they make integrity automatic.

Looking ahead

Chapter 4 — Relational Algebra. The operations (selection, projection, join, union, difference) that every SQL query is translated into. Seeing the algebra beneath the SQL turns queries from incantations into compositions you can reason about — and sets up the whole of Part II.

One sentence to carry forward: A table is not a grid — it's a set of uniquely identifiable tuples whose every rule the database can enforce for you, forever.