Chapter 19 — Quiz

14 questions. Answers at the bottom.

Multiple choice

Q1. The root cause of update/insertion/deletion anomalies is: - A) Missing indexes - B) Redundancy (the same fact stored in multiple places) - C) Too many tables - D) NULL values

Q2. A → B (a functional dependency) means: - A) A and B are equal - B) For each value of A there is exactly one value of B - C) B is the primary key - D) A references B

Q3. The normalization slogan: every non-key attribute depends on: - A) Any column - B) The key, the whole key, and nothing but the key - C) An index - D) A foreign key

Q4. 1NF requires: - A) No transitive dependencies - B) Atomic values; no repeating groups/lists in a cell - C) No partial dependencies - D) Every determinant is a key

Q5. 2NF eliminates: - A) Transitive dependencies - B) Partial dependencies on part of a composite key - C) NULLs - D) Foreign keys

Q6. 3NF eliminates: - A) Partial dependencies - B) Transitive dependencies (non-key → non-key) - C) Atomic values - D) Indexes

Q7. A table orders(order_id, customer_id, customer_email, customer_name) with customer_id → customer_email → customer_name violates: - A) 1NF - B) 2NF - C) 3NF (transitive dependency) - D) None

Q8. 2NF violations only occur in tables with: - A) A single-column key - B) A composite (multi-column) key - C) No key - D) Foreign keys

Q9. An insertion anomaly is when: - A) You can't add a fact (e.g., a product) without an unrelated fact (an order) - B) Updating one copy leaves others stale - C) Deleting a row loses unrelated facts - D) A NULL appears

Q10. BCNF requires that: - A) There are no NULLs - B) Every determinant is a candidate key - C) The table has one column - D) All keys are surrogate

Q11. The practical normalization baseline for most schemas is: - A) 1NF - B) 2NF - C) 3NF (often = BCNF) - D) 5NF

True/False

Q12. Normalization optimizes for write integrity and can make read-heavy workloads slower (more joins). (True / False)

Q13. A jsonb column for genuinely variable data automatically violates 1NF. (True / False)

Short answer

Q14. Normalize enrollment(student_id, student_name, course_id, course_title, grade) to 3NF, listing the resulting tables.

---

Answer key

Q1 — B. Redundancy is the disease; the anomalies are symptoms.

Q2 — B. Each A maps to exactly one B.

Q3 — B. "The key, the whole key, and nothing but the key."

Q4 — B. Atomic values, no repeating groups.

Q5 — B. Partial dependencies (on part of a composite key).

Q6 — B. Transitive dependencies (non-key determines non-key).

Q7 — C. Transitive dependency → 3NF violation.

Q8 — B. Partial dependence requires a composite key to be "partial" of.

Q9 — A. Can't record a fact without an unrelated one (a product with no order).

Q10 — B. Every determinant must be a candidate key.

Q11 — C. 3NF (usually coinciding with BCNF) is the practical target.

Q12 — True. It minimizes redundancy/anomalies (writes) at the cost of joins (reads) — hence denormalization (Ch. 20).

Q13 — False. 1NF forbids cramming a queryable list into a cell; a jsonb value for genuinely variable data is a legitimate atomic value (Ch. 16).

Q14. Split into: students(student_id, student_name), courses(course_id, course_title), and enrollments(student_id, course_id, grade) with PK (student_id, course_id). student_name depended on student_id and course_title on course_id (partial/transitive in the flat table); each now lives once, and grade (depending on the pair) stays on the junction.

Scoring: 12–14 you've mastered normalization; 9–11 re-drill 2NF vs 3NF; below 9, redo Exercises C.