Part II — Database Modeling and Design

Chapter 7. Functional Dependencies and Normalization

Chapter 6 mapped the ER model into tables, and the mapping rules — followed honestly — already produce a clean schema. But real databases are rarely born clean: they are born as reports, spreadsheets, and "just one wide table" that someone needed yesterday. This chapter is the theory and the repair kit. Functional dependencies state, precisely, which facts determine which; normalization uses them to split badly shaped tables into well-shaped ones — provably without losing information. The theory (Codd again, with Boyce, Heath, and Fagin) is one of the most complete and most examinable bodies of knowledge in this book.

The chapter's engine is a single running example: a denormalized student_course_report table — exactly the kind a registrar's office exports from an old system — that we will normalize, step by step, until it is the canonical six-table university schema of Appendix H. Normalization is not abstract ritual; it is how the canonical schema got its shape.

After studying this chapter you will be able to:

• Recognize insertion, update, and deletion anomalies in a real table and name their cause.
• State, read, and reason with functional dependencies using Armstrong's axioms.
• Compute attribute closures and use them to test keys and derived dependencies.
• Find all candidate keys of a relation from its dependencies.
• Define 1NF through 5NF, detect each violation, and repair it by decomposition.
• Distinguish 3NF from BCNF with the classic example where they differ.
• Test any binary decomposition for losslessness and any decomposition for dependency preservation.
• Normalize practical datasets — the skill Chapter 29's case studies assume.

7.1 Database anomalies

Here is the registrar's export — one row per enrollment, carrying every fact the report needed (the book's "now" is Fall 2026):

student_course_report(student_id, full_name, dept_id, dept_name,
                     section_id, course_id, course_title, semester,
                     section_year, room, instructor_id, instructor_name, grade)

 21100001 | Nusrat Jahan | 1 | Computer Science and Engineering
          | 1 | CSE215 | Programming Language II | Fall 2024
          | SAC-304 | 101 | Ahmed Kabir      | A
 21100001 | Nusrat Jahan | 1 | Computer Science and Engineering
          | 3 | CSE221 | Database Systems | Fall 2024
          | SAC-401 | 102 | Farhana Rahman   | A-
 21100001 | Nusrat Jahan | 1 | Computer Science and Engineering
          | 8 | MAT116 | Calculus I | Fall 2024
          | MAB-102 | 105 | Mahmudul Islam   | B+
 21100002 | Rakib Hasan  | 1 | Computer Science and Engineering
          | 1 | CSE215 | Programming Language II | Fall 2024
          | SAC-304 | 101 | Ahmed Kabir      | B+
 21300003 | Arif Mahmud  | 1 | Computer Science and Engineering
          | 1 | CSE215 | Programming Language II | Fall 2024
          | SAC-304 | 101 | Ahmed Kabir      | A

The table answers its report. As a database it fails in three named ways:

• Insertion anomaly. A new section of CSE251 is created before anyone enrolls — it cannot be recorded, because the only candidate key is (student_id, section_id) and there is no student yet. A fact that exists in the world has no home in the table until an unrelated fact (an enrollment) arrives.
• Update anomaly. The department is renamed "Computer Science and Engineering" → the name appears in every row of every CSE student's enrollments (in the full data, dozens of rows). One missed row and the table now asserts two different department names — redundancy in, inconsistency out.
• Deletion anomaly. Zara Hossain (21600001) drops her only enrollment, section 13 — delete the row and the section's facts (room SAC-303, instructor 101, Fall 2026) vanish with it. Deleting one fact destroyed others.

The three anomalies are one disease: facts stored in the wrong place, each repeated under conditions it does not depend on. The department's name depends only on the department; the course title only on the course; the room only on the section; the grade only on the (student, section) pair — yet the table stores them all keyed to the enrollment. Functional dependencies make "depends only on" precise; normalization moves every fact to a table keyed by exactly what it depends on.

7.2 Functional dependencies

A functional dependency (FD) X → Y on relation R says: whenever two rows agree on all attributes of X, they must agree on all attributes of Y. X functionally determines Y. In the report table, the business rules give:

F1  student_id  → full_name, dept_id
F2  dept_id     → dept_name
F3  section_id  → course_id, semester, section_year, room, instructor_id
F4  course_id   → course_title
F5  instructor_id → instructor_name
F6  (student_id, section_id) → grade

Read F1 aloud: "the student determines the name and department — two rows about the same student cannot disagree about them." F6: "the student-and-section pair determines the grade." These are business facts (no two students share an ID; a department has one name), not consequences of the current data — an FD is a promise about every legal future instance, which is why it belongs in the schema's design, not in a query's WHERE clause.

FDs are trivial when Y ⊆ X (section_id → section_id): true of every table, useless for design. Armstrong's three axioms generate every FD implied by a set — they are sound (produce only true dependencies) and complete (produce all of them):

• Reflexivity: if Y ⊆ X, then X → Y.
• Augmentation: if X → Y, then XZ → YZ (agree on more, still agree on Y).
• Transitivity: if X → Y and Y → Z, then X → Z.

Three derived rules shorten derivations: union (X → Y and X → Z give X → YZ), decomposition (X → YZ gives X → Y and X → Z — which is why F1 splits freely into student_id → full_name and student_id → dept_id), and pseudotransitivity (X → Y and WY → Z give WX → Z). Transitivity already exposes the report's disease: student_id → dept_id (F1) and dept_id → dept_name (F2) give student_id → dept_name — the student determines the department name without the enrollment being involved at all, yet the table stores the name 28 times, once per enrollment.

7.3 Attribute closure

The closure X⁺ of an attribute set X (under a set F of FDs) is everything X determines, directly or through chains: start with X, repeatedly add the right-hand sides of any FD whose left-hand side is already in the set, until nothing changes. The closure is the workhorse of the whole chapter — computing it once answers three questions.

Computing {section_id}⁺ under F1–F6:

start:      {section_id}
F3 applies: + course_id, semester, section_year, room, instructor_id
F4 applies: + course_title
F5 applies: + instructor_name
result:     {section_id, course_id, course_title, semester,
             section_year, room, instructor_id, instructor_name}

Three uses:

1. Superkey test. X is a superkey iff X⁺ = all attributes of R. {student_id, section_id}⁺ adds F1's right side, F6's grade, and then F2's dept_name — everything. So the pair is a superkey; section_id alone is not (student facts and grade never arrive).
2. Derived-FD test. X → Y holds iff Y ⊆ X⁺ — no derivation by hand needed. Is section_id → instructor_name implied? It is in the closure above: yes (via section_id → instructor_id → instructor_name).
3. Equivalence test. Two FD sets are equivalent iff every FD of each is contained in the other's closures — how tools swap your dependency set for a smaller equivalent one.

The companion algorithm, the canonical (minimal) cover, removes redundant FDs and redundant attributes on left sides — the tidy set a synthesis algorithm decomposes from. For F1–F6 the set is already essentially minimal.

7.4 Candidate-key identification

A candidate key is a minimal superkey. The closure machinery finds them systematically:

1. Attributes never appearing on any right-hand side must be in every key — nothing determines them, so only they can start a key. In the report: student_id and section_id appear on no right side; every other attribute is determined by something.
2. Compute the closure of that set. If it is the whole relation, it is a superkey. {student_id, section_id}⁺ = R — a superkey exists.
3. Minimize. Try removing each attribute: {student_id}⁺ misses section/grade facts; {section_id}⁺ misses student/grade facts. Neither removal works, so the pair is minimal — the only candidate key, and (in this table) everything else is a non-prime attribute (an attribute in no candidate key; prime attributes are those in some key).

The report table is the worst case normalization cares about: a composite key plus non-prime facts hanging off each half of it. Real schemas repeat this procedure per table as part of design review — an "attribute nobody determines" is either a key piece or a design smell (a fact with no anchor).

7.5 First Normal Form (1NF)

1NF: every attribute holds atomic values — one value per cell, no repeating groups, no nested structures. The relational model's oldest rule (Chapter 2's atomicity) is violated the moment a cell contains a list: department.phones = '556610, 556611'. The classic repair is the multivalued-attribute rule of Chapter 6 — a bridge table dept_phone(dept_id, phone) — because the cell cannot hold the set, but the database can hold it as rows.

Two practical notes. First, atomicity is a modeling judgment, not a physics law: a full_name string is atomic for a registrar who never sorts by surname, and non-atomic for one who does (then it becomes two columns, or a nested structure in a JSON-aware extension — Chapter 15). Second, 1NF violations are almost always silent: nothing stops a program from writing 'CSE221, CSE251' into a courses cell, and nothing helps the query that then needs to find "students who took CSE251" — string matching over lists is the classic 1F symptom. Our report table passes 1NF; the disease lives higher up.

7.6 Second Normal Form (2NF)

2NF: 1NF, and no non-prime attribute depends on a proper subset of a candidate key (a partial dependency). Partial dependencies can only exist when a key is composite — with single-column keys, 1NF → 2NF is automatic.

The report's violations are textbook-perfect. With key (student_id, section_id) and all other attributes non-prime:

student_id → full_name, dept_id        (half the key determines student facts)
section_id → course_id, semester, section_year, room, instructor_id
                                       (the other half determines section facts)

Full_name does not depend on the whole key — it survives removal of section_id — so the enrollment row is the wrong home for it. The repair is decomposition into three tables, each keyed by exactly what its facts depend on:

student_report(student_id, full_name, dept_id, dept_name)
section_report(section_id, course_id, course_title, semester,
               section_year, room, instructor_id, instructor_name)
enrollment(student_id, section_id, grade)

The anomalies improve immediately: a studentless section can now be inserted (into section_report); a student's name is stored once. But each half-table still hides a chain: student_id → dept_id → dept_name and section_id → course_id → course_title, instructor_id → instructor_name. Those are 3NF's business.

7.7 Third Normal Form (3NF)

3NF: 2NF, and no non-prime attribute transitively depends on a candidate key — formally, every non-trivial FD X → A must satisfy: X is a superkey, or A is prime. The escape clause is deliberate; Section 7.8 shows why it exists.

Both post-2NF tables violate 3NF:

student_report: student_id → dept_id and dept_id → dept_name
                ⇒ dept_name depends on the key through dept_id (transitive)
section_report: section_id → course_id → course_title
               section_id → instructor_id → instructor_name

Why does a transitive dependency matter? Same disease, new shape: the department's name is stored once per student (dozens of rows in real data) instead of once per department; renaming means touching every student. The repair is one more round of "let the determinant be a key":

department(dept_id, dept_name)                 ← from student_report
student(student_id, full_name, dept_id)
course(course_id, course_title)                ← from section_report
instructor(instructor_id, instructor_name)
course_section(section_id, course_id, semester,
               section_year, room, instructor_id)
enrollment(student_id, section_id, grade)

Six tables: the canonical university schema of Appendix H (which additionally carries building and budget on department, hire_date and salary on instructor — facts the report never exported, and normalization has nothing to say about facts that were never there). The registrar's export has become the registrar's database, and every anomaly of Section 7.1 is gone. The mnemonic is Kent's: every non-key attribute must depend on "the key, the whole key, and nothing but the key."

7.8 Boyce–Codd Normal Form (BCNF)

BCNF: every non-trivial FD X → Y has X a superkey. That is — drop 3NF's "or A is prime" escape. BCNF is 3NF's stricter sibling: every 3NF violation is a BCNF violation, and a few more besides.

The classic separating example has no university flavor, so meet it exactly as Codd's colleagues did: a tutoring arrangement where each student takes a course with exactly one instructor, and each instructor teaches exactly one course:

teach(student, course, instructor)
FDs:  (student, course) → instructor
      instructor → course

Candidate keys: (student, course) and (student, instructor) — so every attribute is prime, and the relation is automatically in 3NF (no non-prime attributes to violate). But instructor → course has a non-superkey determinant, so BCNF fails, and the redundancy is visible: instructor Kabir teaching CSE215 to five students stores "Kabir teaches CSE215" five times. Decompose on the offending FD:

takes(student, instructor)      teaches(instructor, course)

The pair is lossless (Section 7.11 tests it). The cost appears in Section 7.12: the FD (student, course) → instructor can no longer be checked inside a single table. That trade — BCNF's strictness versus preserving dependencies — is why theory stops recommending at "3NF always; BCNF when the decomposition preserves dependencies," and why synthesis algorithms (Section 7.12) target 3NF by construction.

7.9 Fourth Normal Form (4NF)

BCNF handles single-valued determination; multivalued dependencies (MVDs) handle sets of independent facts. X ↠ Y ("X multivalues Y") says: for each X value, the Y values form a set, independent of the other attributes. The signature instance: a professor advises a set of students and sits on a set of committees — neither set constrains the other — stored in one table:

service(instructor_id, student_id, committee)
101 advises {21100001, 21300003};  101 serves {Curriculum, Admissions}

To represent two independent 2-facts, the single table must store the cross product — four rows — and adding one advisee or one committee multiplies rows again. The table is in BCNF (the only non-trivial FDs are the trivial-ish keys), yet it both bloats and lies: the four rows look like four facts but are two. 4NF: BCNF, and every non-trivial MVD X ↠ Y has X a superkey. The repair is the same medicine, second application — one table per independent set:

advisor(instructor_id, student_id)     committee_member(instructor_id, committee)

The diagnostic question that finds MVDs is Chapter 2's atomicity question at row scale: "does this table combine two relationships that know nothing about each other?" If yes, the join back will invent pairings the world never asserted — spurious tuples, the 4NF smell.

7.10 Fifth Normal Form (5NF)

5NF generalizes the same idea from two-way (MVD) to n-way: a join dependency JD says a table can be losslessly reconstructed from three or more projections. The textbook instance is supplier–part–project: if a supplier supplies a part, supplies a project, and the project uses that part, then business rules may force "the supplier supplies that part to that project" — so the three-way table can be split into three two-way tables and rebuilt without spurious rows iff the constraint holds. 5NF (project-join normal form): every non-trivial join dependency is implied by the candidate keys.

In practice 5NF is a finish line you check, not a target you chase: multi-way JDs are invisible in data samples (they hold or fail on future rows) and must come from stated business rules. Design guidance: normalize to 3NF/BCNF by FDs, check 4NF whenever independent multi-valued facts share a table, and treat 5NF as an exam topic plus an occasional surprise in ternary-relationship designs (a case Chapter 29 revisits).

7.11 Lossless decomposition

Decomposition must never lose or invent information — the property is lossless join: R1 ⋈ R2 must reproduce exactly R (no spurious tuples). The binary decomposition test (Heath's theorem) makes it mechanical:

R split into R1 and R2 is lossless iff R1 ∩ R2 → R1 or R1 ∩ R2 → R2 — the shared attributes functionally determine one whole side (they are a superkey of one piece).

Every split in this chapter passes: student_report ∩ enrollment = {student_id}, and student_id → student_report (it is that table's key); section_report ∩ enrollment = {section_id}, a key of section_report. The classic bad split shows the failure mode — divide enrollment itself wrongly:

e1(student_id, section_id)     e2(student_id, grade)
intersection {student_id} → neither side (a student determines neither
their sections nor their grades)  ⇒  not provably lossless

Join e1 with e2 on student_id and Nusrat's three sections {1, 3, 8} pair with her three grades {A, A-, B+} — nine rows, six of them facts the world never asserted (she did not earn an A in section 3). A decomposition that is not lossless is not normalization; it is fabrication. The test extends to multi-way splits by applying it stepwise (each split along the way must be binary-lossless), and Chapter 29's case studies use it as a final verification gate.

7.12 Dependency preservation

A decomposition is dependency preserving if every FD of the original set can be checked by looking at a single table of the result — because PostgreSQL and MySQL enforce exactly what constraints on a table can express. Split student_report into student + department and every FD (F1, F2) still lives inside one table each: preserved. Split the teach relation of Section 7.8 and the FD (student, course) → instructor is gone — checking it now requires rejoining takes ⋈ teaches on every change: not preserved.

That is the standing trade-off: 3NF synthesis (the Bernstein algorithm) always produces lossless and dependency-preserving decompositions; BCNF cannot promise both. Hence the practical rule taught in every design course: normalize to 3NF by synthesis; push to BCNF per-table when it preserves dependencies; if a genuinely needed BCNF split loses a dependency, enforce it outside the tables — a trigger (Chapter 22) that re-checks the FD, with the cost of procedural code replacing the purity of declarative constraints. SQL's own history also matters here: modern platforms dropped the SQL standard's ASSERTION (cross-table constraint) object, so "the database" enforcing cross-table FDs today means triggers — one more reason the 3NF target is not just tradition but the line where declarative guarantees still hold.

7.13 Normalization exercises using practical datasets

Three datasets, in rising difficulty. Attempt each before reading the answer; Appendix G carries more, with full solutions.

Exercise 1 — retail orders. order_report(order_no, order_date, customer_id, customer_name, city, product_id, product_name, unit_price, quantity) with FDs: order_no → order_date, customer_id; customer_id → customer_name, city; product_id → product_name, unit_price; (order_no, product_id) → quantity. The key is (order_no, product_id); customer and product facts hang partly and transitively off it. Answer: customer(customer_id, customer_name, city); product(product_id, product_name, unit_price); orders(order_no, order_date, customer_id); order_line(order_no, product_id, quantity) — 2NF removes the partials, 3NF the customer/product chains; all splits pass the lossless test by shared keys.

Exercise 2 — clinic visits. visit_report(visit_id, visit_date, patient_id, patient_name, doctor_id, doctor_name, specialty, diagnosis, fee) with FDs: visit_id → visit_date, patient_id, doctor_id, diagnosis, fee; patient_id → patient_name; doctor_id → doctor_name, specialty. Single-column key, so no partials; two transitive chains. Answer: patient(patient_id, patient_name); doctor(doctor_id, doctor_name, specialty); visit(visit_id, visit_date, patient_id, doctor_id, diagnosis, fee) — 1NF → 3NF in one transitive split; visit's key already pins the pair-dependent facts (diagnosis, fee belong to the visit — note the design judgment that diagnosis is per-visit, not per-patient).

Exercise 3 — advising and committees. service(instructor_id, student_id, committee) with no non-trivial FDs but the MVDs instructor_id ↠ student_id and instructor_id ↠ committee, and a business rule that advising and committee service are unrelated. Answer: BCNF (trivially — everything is key) but not 4NF; split advisor(instructor_id, student_id) and committee_member(instructor_id, committee), each now in 4NF; the joined-back original would manufacture advisor-committee pairings the rule never asserted.

The closing judgment is the professional one: normalize to at least 3NF always, to BCNF where dependencies survive, watch for MVD shapes, and denormalize only consciously — for a documented analytical workload, with the redundancy named and its refresh policy written down (Chapter 25 does exactly this, deliberately). What normalization forbids is not redundancy; it is accidental redundancy.

Chapter Summary

• Wide tables breed insertion, update, and deletion anomalies: facts stored under keys they do not depend on.
• An FD X → Y is a business promise: X-values determine Y-values in every legal instance; Armstrong's axioms (reflexivity, augmentation, transitivity) derive all implied FDs.
• Closure X⁺ (iteratively expanded) tests superkeys (X⁺ = R), derived FDs (Y ⊆ X⁺), and FD-set equivalence.
• Candidate keys: attributes never on a right-hand side are mandatory key material; compute closures, then minimize.
• 1NF demands atomic cells (lists become bridge tables); atomicity is a modeling judgment.
• 2NF removes partial dependencies on parts of composite keys; 3NF removes transitive dependencies of non-prime attributes ("the key, the whole key, and nothing but the key").
• BCNF drops 3NF's prime-attribute escape; the teach example (all attributes prime, instructor → course) separates 3NF from BCNF at the cost of dependency preservation.
• 4NF removes independent multivalued facts sharing a table (MVDs); 5NF generalizes to join dependencies and is checked, not chased.
• A binary split is lossless iff the shared attributes are a superkey of one side; the wrong enrollment split manufactures spurious grade–section pairs.
• Dependency preservation keeps every FD checkable in one table; 3NF synthesis guarantees it, BCNF cannot — lost dependencies fall back to triggers.
• Normalizing the registrar's report reproduces the canonical six-table schema exactly.

Key Terms

Laboratory Exercises

1. Build student_course_report from the five rows of Section 7.1 (add dept_id and instructor_id columns as shown), then demonstrate all three anomalies: try inserting a sectionless enrollment-world fact (a section with no student); attempt a department rename; delete Arif Mahmud's row and note what section facts survive. Expected result: the insert fails on the missing student_id (key column NULL); the rename touches multiple rows; the delete leaves section 1's facts only in Nusrat's and Rakib's rows — one student fewer from destroying them.
2. Compute by hand: {student_id}⁺, {section_id}⁺, and {dept_id}⁺ under F1–F6, then verify with a small script or spreadsheet. Expected results: {student_id}⁺ = {student_id, full_name, dept_id, dept_name}; {section_id}⁺ = the nine section/course/instructor attributes; {dept_id}⁺ = {dept_id, dept_name}.
3. Write the six-table canonical DDL (Appendix H) and reproduce the normalization of Sections 7.6–7.7 as INSERT ... SELECT migrations from your report table — then verify equality: the report table equals the join of the six tables restricted to its columns. Expected result: the verification query returns 28 rows identical to the report — a lossless decomposition you executed yourself.
4. Rebuild the canonical database from Appendix H and demonstrate the wrong split of Laboratory 7.11's kind: split enrollment into e1(student_id, section_id) and e2(student_id, grade), rejoin, and count rows. Expected result: more than 28 rows after the join — spurious pairings (Zara's single enrollment is immune; any multi-enrollment student, e.g. Nusrat with 3 and 3, inflates 3×3=9).
5. Build the 4NF example: service(101, 21100001, 'Curriculum'), (101, 21100001, 'Admissions'), (101, 21300003, 'Curriculum'), (101, 21300003, 'Admissions') plus one new advisor row; then split into advisor and committee_member and count rows before and after. Expected result: 5 rows in one table versus 3 + 2 = 5 facts in two tables with no cross product; adding committee 'Library' costs 1 row in the normalized form and 2 in the original.
6. In MySQL, repeat Exercise 3 and confirm the platform-independence of the theory: the six-table decomposition, the lossless verification, and the spurious-join count are identical to PostgreSQL. Expected result: identical results and counts; only minor syntax (backquoted identifiers) differs.

Review Questions and Exercises

1. Name the three anomalies and give each one sentence of cause. Insertion — the fact's would-be key depends on an absent fact; update — a repeated fact must be changed in many rows; deletion — removing one fact removes stored facts that depended on it only incidentally.
2. Why must an FD be a business rule rather than an observation of current data? An FD constrains every future legal instance; an accidental regularity of today's rows stops holding tomorrow, and the schema must not depend on it.
3. Derive student_id → dept_name from F1 and F2, naming the axiom used. Transitivity: student_id → dept_id and dept_id → dept_name.
4. What is {student_id, section_id}⁺ under F1–F6, and what does that prove? All thirteen attributes — the pair is a superkey; with neither half closing to R, it is the single candidate key.
5. Why do single-column-key tables never violate 2NF? Partial dependencies require a proper subset of a composite key; with one key attribute there are no proper subsets.
6. A table is in 2NF but not 3NF. What shape of dependency remains, and what does its repair look like? A transitive dependency of a non-prime attribute (key → X → A); split out the table keyed by X, leaving the key → X reference in place.
7. State the difference between 3NF and BCNF in one sentence, and name the example relation that separates them. 3NF excuses FDs X → A when A is prime; BCNF requires every determinant to be a superkey — teach(student, course, instructor) is 3NF (all attributes prime) but not BCNF (instructor → course).
8. Why does the BCNF split of teach lose a dependency, and what tool re-imposes it? (student, course) → instructor spans the two result tables; a trigger on both tables re-checks it (Chapter 22).
9. Give the MVD definition in words and the diagnostic question that detects MVD trouble. X ↠ Y: each X value owns a set of Y values independent of the remaining attributes; ask "does this table combine two relationships that know nothing about each other?"
10. State the binary lossless-join test and apply it to student_report ∩ enrollment. Lossless iff the intersection determines one whole side; {student_id} → student_report's attributes, so the split is lossless.
11. Why is e1(student_id, section_id) ⋈ e2(student_id, grade) dangerous, with Nusrat as the example? student_id determines neither side; her three sections join with her three grades producing nine rows, six of them grades attached to the wrong sections.
12. Your analytical warehouse deliberately repeats the department name on every enrollment fact row. Which principle does this violate, and what makes it acceptable? One-fact-one-place / normalization; acceptability comes from consciousness — a documented workload reason (Chapter 25) and a named refresh policy, versus accidental redundancy.

Mini-Project

Take a real spreadsheet you know — a grade sheet, a bill, a game stat tracker — or invent a plausible one, and normalize it to BCNF end to end. Deliver: (1) the raw table with a dozen sample rows; (2) the stated FDs and any MVDs, each justified in one line as a business promise; (3) the closures of each attribute set you test and the candidate-key derivation; (4) the stepwise decomposition 1NF → 2NF → 3NF → BCNF, with the form and the violated dependency named at each step; (5) the lossless test applied to every split and a dependency-preservation check; and (6) the final DDL, loaded with your sample rows, plus the equality query proving the join of your result tables reproduces the original exactly. Where you deliberately stop short of 4NF/5NF or denormalize, write the justification paragraph. Grade your Chapter 5 and Chapter 6 mini-project designs with the same machinery — they were exercises in getting it right early; this one proves you can also repair it late.