Databases
Relational model: free-recall review
Retrieval beats re-reading. For each prompt, say or write a full answer from memory before you open the model answer — the effort of recall is what makes the schema-design instincts stick.
Reconstruct the unit’s core mechanisms — keys, 3NF, the JSONB decision rule, FK economics, physical storage, and zero-downtime schema change — without looking back at the lessons.
- 01What is the difference between a candidate key and a primary key, and why is the 2026 default a surrogate primary key plus a UNIQUE NOT NULL on the business key?
- 02Why is 3NF the production-default normal form, and what are the two legitimate directions to leave it?
- 03State the decision rule for JSONB versus a typed column versus a side table, with one example of each.
- 04What does a foreign key actually cost, what does it buy, and under what specific conditions is disabling it a defensible engineering choice?
- 05Explain the TOAST mechanism and the two performance implications it has for schema and query design.
- 06Describe the expand-then-contract pattern and explain why it is required to rename a column on a large shared-schema table with zero downtime.
If you reconstructed each answer from memory, you hold the unit’s spine: identity lives in an immutable surrogate key while business keys are guarded by UNIQUE NOT NULL; 3NF stops two rows from disagreeing and denormalization is a measured exception with explicit reconciliation; the JSONB/typed-column/side-table choice is driven entirely by how you query the value; foreign keys are cheap insurance you keep unless a specific scale constraint forces otherwise; physical storage (TOAST, page alignment) makes SELECT * on wide rows expensive; and a live schema changes through expand-then-contract, never an in-place break.