Part 5 of 5 · 3 min read
Exercises
Easy
Easy 1: Check for losslessness (15 minutes)
State the condition that guarantees a decomposition is lossless.
Done when: the shared column being a key of at least one resulting table is correctly stated.
Hint
The shared column must be a key of at least one of the two resulting tables.
Easy 2: Identify a lossless split (15 minutes)
Confirm that splitting customers (customer_id, postal_code, city) on postal_code is lossless, and state why.
Done when: lossless is correctly confirmed, citing postal_code as a key of the new postal_codes table.
Hint
Lossless, since postal_code becomes the primary key of postal_codes.
Easy 3: Spot a dependency-preservation risk (15 minutes)
Explain, in one sentence, what dependency-preserving means.
Done when: the explanation correctly states every original rule stays checkable within one resulting table.
Hint
Every rule from the original table must remain checkable using columns within a single resulting table, with no join required.
Medium
Medium 1: Decompose and verify (25 minutes)
Decompose employees (employee_id, department_id, department_name) into two tables, and verify the decomposition is lossless.
Done when: a correct split is given, with department_id correctly identified as the key of the new departments table, confirming losslessness.
Hint
employees (employee_id, department_id) and departments (department_id, department_name), with department_id as departments' key, guaranteeing losslessness.
Medium 2: Identify a lossy decomposition (25 minutes)
Explain, conceptually, why splitting a table on a column that is neither table's key risks producing extra rows when joined back.
Done when: the explanation correctly notes the join can match unrelated original rows that happened to share that non-key value, fabricating combinations that never existed.
Hint
If the shared non-key column repeats across unrelated original rows, joining on it recombines rows that were never actually paired together in the real data.
Medium 3: Find a dependency that needs a join to check (25 minutes)
Describe a decomposition where an original rule, like "a book's price cannot exceed its publisher's maximum allowed price," would require a join across the split tables to verify, rather than a simple constraint.
Done when: a plausible split is described, with the cross-table nature of the rule correctly explained as the reason it is no longer dependency-preserving.
Hint
Splitting books and publishers apart means checking "price cannot exceed the publisher's maximum" now requires joining books to publishers, rather than a single CHECK constraint on one table.
Hard
Hard 1: Choosing between two valid decompositions (30 minutes)
A table can be correctly decomposed two different ways, both lossless and dependency-preserving. Describe one practical factor, beyond correctness, that could still make one choice better than the other.
Done when: a practical factor is named, such as which split better matches how the data is actually queried, or which split produces fewer total joins for the most common queries.
Hint
Matching the split to how the data is most commonly queried, minimizing the number of joins needed for frequent queries, is a strong practical factor beyond pure correctness.
Hard 2: Repairing a lossy decomposition (35 minutes)
Given the enrollments example from part 2, propose a corrected decomposition that is actually lossless, keeping the same three original columns in mind.
Done when: a correct decomposition is proposed, such as keeping (student_id, course_id, instructor_id) together, or splitting only on a genuinely independent fact, rather than the flawed instructor_id-based split shown.
Hint
Keeping (student_id, course_id, instructor_id) as one table, or decomposing only along a truly independent additional fact, avoids the phantom-row problem the flawed instructor_id-based split created.
Hard 3: When losing dependency preservation is accepted anyway (40 minutes)
Explain a realistic scenario where a team deliberately accepts a decomposition that is lossless but not dependency-preserving, and describe what they do instead to keep the original rule enforced.
Done when: a realistic scenario is described, along with a compensating mechanism, such as a trigger or an application-level check, used in place of the lost simple constraint.
Hint
A team might accept a lossless-but-not-preserving split for query performance reasons, then add a trigger on the split tables that explicitly re-checks the cross-table rule on every relevant write.