CSE Roadmap

Core tier · about 4 hours · 5 parts

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.