Chapter 14 — Quiz

13 questions. Answers at the bottom.

Multiple choice

Q1. DDL stands for and includes: - A) Data Display Language — SELECT - B) Data Definition Language — CREATE/ALTER/DROP - C) Data Deletion Language — DELETE - D) Database Driver Layer

Q2. The correct type for money is: - A) real - B) double precision - C) numeric(p,s) - D) float

Q3. In PostgreSQL, text vs varchar(255): - A) varchar is much faster - B) Identical performance; pick a limit only if it's a real rule - C) text can't be indexed - D) varchar is required for keys

Q4. A constraint that spans multiple columns (e.g., a composite primary key) must be: - A) A column constraint - B) A table constraint - C) Impossible - D) A trigger

Q5. ON DELETE CASCADE means deleting the parent row: - A) Is blocked if children exist - B) Deletes the child rows too - C) Sets children's FK to NULL - D) Does nothing

Q6. GENERATED ALWAYS AS IDENTITY: - A) Lets you insert any id you want freely - B) Auto-generates the key and prevents manual values (unless overridden) - C) Is the same as text - D) Requires a trigger

Q7. ALTER TABLE big ADD COLUMN s text NOT NULL; on a populated table: - A) Succeeds, filling NULLs - B) Fails — existing rows would violate NOT NULL; add nullable/default then backfill - C) Deletes the table - D) Is always instant

Q8. The most destructive of these is: - A) DELETE FROM t WHERE ... - B) TRUNCATE t - C) DROP TABLE t - D) SELECT * FROM t

Q9. A schema in PostgreSQL is: - A) Your overall database design only - B) A namespace (folder) for objects within a database - C) A backup - D) An index

Q10. DROP TABLE products CASCADE: - A) Only drops products - B) Also drops dependent objects (views, FKs) — potentially more than expected - C) Keeps the data - D) Is reversible without a backup

True/False

Q11. Choosing a column's data type is purely a storage decision with no correctness impact. (True / False)

Q12. The constraints you declare are enforced for every client and program automatically. (True / False)

Short answer

Q13. Give the safe 3-step pattern for adding a NOT NULL column to a large, populated table, and explain why each step is needed.

---

Answer key

Q1 — B. DDL = CREATE/ALTER/DROP (structure). DML changes rows; SELECT reads.

Q2 — C. numeric (exact). Floats can't represent decimal cents exactly → money drift.

Q3 — B. Identical performance; a length limit should encode a real rule, not habit.

Q4 — B. Multi-column rules are table constraints.

Q5 — B. CASCADE deletes children. (Convenient but dangerous.)

Q6 — B. Auto-generates and (with ALWAYS) blocks manual values unless you use OVERRIDING.

Q7 — B. Existing rows would violate it; add nullable or with a default, backfill, then set NOT NULL.

Q8 — C. DROP TABLE removes the table itself (and with CASCADE, its dependents).

Q9 — B. A namespace for objects; default is public.

Q10 — B. CASCADE drops everything depending on the table — read \d+ first.

Q11 — False. Type is a correctness decision (money-as-float, timezone handling, valid ranges).

Q12 — True. That universal, automatic enforcement is the whole point of database constraints.

Q13. (1) ADD COLUMN as nullable (or with a DEFAULT) — fast, doesn't violate existing rows. (2) Backfill the column with correct values via UPDATE. (3) ALTER COLUMN ... SET NOT NULL once every row has a value. Doing it in one step fails because existing rows have no value for a non-default NOT NULL column.

Scoring: 11–13 you can build solid schemas; 8–10 review constraints and FK actions; below 8, redo Exercises B–D and the project build.