Chapter 19 — Exercises

Mostly design/analysis exercises. Work them on paper, checking against Mercado's (3NF) schema. (answer in Appendix) = worked solution in Answers. ⭐ = stretch.

Group A — Anomalies

19.1 For the flat orders table in the chapter, give a concrete example of each anomaly: update, insertion, deletion. (answer in Appendix)

19.2 Which anomaly is the Chapter 1 "customer changed her email, 46 rows went stale" story? Explain.

19.3 ⭐ Invent a flat "employees and departments" table that suffers all three anomalies, and describe each.

Group B — Functional dependencies

19.4 Write the functional dependencies you'd expect in a clean products table. (answer in Appendix)

19.5 In the flat orders table, identify two FDs whose left side is not the table's key (the partial/transitive culprits).

19.6 State the one-sentence normalization slogan and explain how it maps to 2NF and 3NF. (answer in Appendix)

Group C — 1NF / 2NF / 3NF

19.7 A students(student_id, name, courses) table stores courses as 'CS200, MATH101'. Which normal form does it violate, and how do you fix it? (answer in Appendix)

19.8 A table order_items(order_id, product_id, qty, product_name, product_price) has key (order_id, product_id). Which normal form does it violate (and why), and what's the fix? (answer in Appendix)

19.9 A table orders(order_id, customer_id, customer_email, customer_name) — which normal form does it violate (transitive dependency), and how do you fix it?

19.10 ⭐ Normalize this flat table to 3NF, showing each step: enrollment(student_id, student_name, course_id, course_title, instructor_name, grade).

Group D — Recognize the form

19.11 For each, name the highest normal form it satisfies and the violation (if any): (a) Mercado's customers; (b) order_items(order_id, product_id, qty, product_name); (c) invoice(invoice_id, customer_id, customer_zip, customer_city) where zip → city. (answer in Appendix)

19.12 ⭐ Explain the difference between 3NF and BCNF with a short example where they differ.

Group E — Judgment

19.13 Why is 3NF the practical baseline rather than stopping at 2NF? (answer in Appendix)

19.14 Give an example of over-normalization and the problem it causes.

19.15 ⭐ Is a jsonb column a 1NF violation? Argue carefully (atomic vs. queryable-list).

Group F — Progressive project

19.16 Check each of your project's tables for 1NF, 2NF, and 3NF violations. Fix any you find, showing the FDs that drove each split.

19.17 Demonstrate the three anomalies on a deliberately denormalized version of one of your tables, then show they're gone after normalization.

19.18 ⭐ Confirm your final schema is in 3NF and that every fact lives in exactly one place.

Self-check. If you can take any flat table, write its FDs, and normalize it to 3NF step by step — explaining which anomaly each step kills — you've mastered the most important design theory. Next: when to deliberately reverse it.