Part 5 of 5 · 3 min read
Exercises
Easy
Easy 1: Spot a 1NF violation (15 minutes)
A students table has a column phone_numbers holding "555-1111, 555-2222" as one string. Explain why this violates 1NF.
Done when: the explanation correctly identifies the packed multi-value column as the violation.
Hint
A single column holding more than one phone number violates the "one atomic value per column" rule of 1NF.
Easy 2: Spot a 2NF violation (15 minutes)
enrollments (student_id, course_id, grade, course_name) has a composite key (student_id, course_id). Identify the column violating 2NF.
Done when: course_name, depending only on course_id, is correctly identified.
Hint
course_name depends only on course_id, not on the full (student_id, course_id) key, violating 2NF.
Easy 3: Spot a 3NF violation (15 minutes)
employees (employee_id, department_id, department_name) has employee_id as its key. Identify the transitive dependency violating 3NF.
Done when: department_id → department_name is correctly identified as the transitive dependency.
Hint
department_id → department_name means department_name depends transitively on employee_id through department_id, a non-key column.
Medium
Medium 1: Fix a 1NF violation (25 minutes)
Redesign the students/phone_numbers table from Easy 1 to satisfy 1NF.
Done when: a correct split into students and a separate student_phones table is given.
Hint
students (student_id, name) and student_phones (student_id, phone_number), one row per phone number.
Medium 2: Fix a 2NF violation (25 minutes)
Redesign enrollments from Easy 2 to satisfy 2NF.
Done when: course_name is correctly moved into a separate courses table, referenced by course_id.
Hint
enrollments (student_id, course_id, grade) and courses (course_id, course_name).
Medium 3: Fix a 3NF violation (25 minutes)
Redesign employees from Easy 3 to satisfy 3NF.
Done when: department_name is correctly moved into a separate departments table, referenced by department_id.
Hint
employees (employee_id, department_id, ...) and departments (department_id, department_name).
Hard
Hard 1: Checking all four forms at once (30 minutes)
Given orders (order_id, customer_id, customer_address, placed_on), identify every normal form it violates, starting from 1NF upward.
Done when: 1NF is correctly judged satisfied, and a transitive dependency (customer_id → customer_address, violating 3NF, since customer_address doesn't depend on order_id directly) is correctly identified.
Hint
orders satisfies 1NF (no packed lists), but violates 3NF, since customer_address depends on customer_id, a non-key column, not directly on order_id.
Hard 2: A 3NF table that is not BCNF (35 minutes)
Describe, at a conceptual level, why a table with two overlapping candidate keys could satisfy 3NF while still failing BCNF, without needing to construct the exact example.
Done when: the explanation correctly notes that 3NF has a specific exception allowing a dependency from part of one candidate key to a non-key attribute belonging to a different candidate key, which BCNF does not allow.
Hint
3NF explicitly permits a dependency from part of a candidate key to an attribute that is itself part of another candidate key, an exception BCNF's stricter rule removes.
Hard 3: Normalizing versus the practical cost (40 minutes)
Explain why a fully normalized schema, with every table split to satisfy BCNF, might still sometimes be deliberately denormalized in a real production system.
Done when: the explanation correctly points toward the next module's topic: that joining many small, fully normalized tables back together at query time has a real performance cost, which sometimes outweighs normalization's benefits.
Hint
Joining many small, fully normalized tables at query time costs real performance; the next module covers trading some of that normalization back for read speed, deliberately and with eyes open.