CSE Roadmap

Specialist tier · about 3 hours · 4 parts

Part 4 of 4 · 3 min read

Exercises

Easy

Easy 1: Spot the needless combination (15 minutes)

An employee has 2 skills and 3 certifications, stored in one table with a row per skill-certification pair. State how many rows this forces, and whether that number reflects real information.

Done when: 6 rows is correctly computed, with a note that this count is artificial, not based on any real link between skills and certifications.

Hint

2 x 3 = 6 rows, none of which reflect any genuine link between a specific skill and a specific certification.

Easy 2: Name the violated form (15 minutes)

State which normal form employee_skills_projects (employee_id, skill, project) violates, given the two facts are independent.

Done when: 4NF is correctly named.

Hint

4NF, since skill and project are independent multivalued facts about the same employee.

Easy 3: 4NF or 5NF (15 minutes)

State which of 4NF or 5NF deals with two independent multivalued facts crammed into one table.

Done when: 4NF is correctly chosen.

Hint

4NF.

Medium

Medium 1: Fix a 4NF violation (25 minutes)

Redesign employee_skills_projects into two tables satisfying 4NF.

Done when: employee_skills (employee_id, skill) and employee_projects (employee_id, project) are correctly given.

Hint

Split into employee_skills (employee_id, skill) and employee_projects (employee_id, project), independent of each other.

Medium 2: Count the savings (25 minutes)

For an employee with 4 skills and 5 projects, compute the row count under the violating design and under the fixed design.

Done when: 20 rows (violating) versus 9 rows (fixed, 4+5) is correctly computed.

Hint

Violating: 4 x 5 = 20 rows; fixed: 4 + 5 = 9 rows.

Medium 3: Recognizing when 4NF doesn't apply (25 minutes)

Explain why order_items (order_id, book_id, quantity) does not violate 4NF, even though it has two foreign keys.

Done when: the explanation correctly notes book_id and quantity are not two independent multivalued facts about order_id; quantity depends on the specific book within the order, unlike the employee skills/projects case.

Hint

quantity is tied specifically to the (order_id, book_id) pairing, a genuine fact about that combination, unlike skill and project, which have no real connection to each other.

Hard

Hard 1: Distinguishing a real many-to-many from a 4NF violation (30 minutes)

Explain the key difference between order_items, a legitimate many-to-many junction table, and employee_skills_projects, a 4NF violation, even though both pair two foreign keys in one table.

Done when: the explanation correctly notes that order_items' pairing reflects a real fact (which books were in which order), while employee_skills_projects' pairing is artificial, with skills and projects having no real relationship to each other.

Hint

order_items' combination (which book, in which order) is a real fact worth recording; employee_skills_projects' combination (which skill, with which project) carries no real meaning, since the two facts are unrelated.

Hard 2: Why 5NF is rarely checked directly (35 minutes)

Explain why most database designers never explicitly check a schema against 5NF, even in a careful, professional design process.

Done when: the explanation correctly notes that join dependencies not implied by keys are rare in practice, and careful ER modeling tends to avoid the structures that would create one.

Hint

Careful ER modeling tends to model independent multivalued facts as separate relationships from the start, which naturally avoids creating the kind of table a join dependency would apply to.

Hard 3: Deciding when the extra normalization is worth it (40 minutes)

Argue for or against normalizing employee_skills_projects into 4NF, if the two facts are rarely queried together and the needless combination has caused zero real reported bugs so far.

Done when: a reasoned position is given either way, weighing the real but modest cost of the current redundancy against the real engineering effort and risk of migrating existing data to the normalized design.

Hint

A reasonable position notes the current redundancy's cost is low and unproven in practice, while a migration carries real risk and effort, suggesting the normalization may not be worth doing proactively without a concrete driving problem.