The two previous lessons have given us the vocabulary and the catalog. We know how to write functional dependencies, compute closures, find candidate keys and decide whether a relation is in a particular normal form. What we have not done yet is normalize something for real, from start to finish.

And there is a substantial difference between the two. The examples in lesson 05-02 were built to illustrate one normal form in isolation: three or four columns, one problematic dependency, the obvious fix. Reality does not show up like that. Reality shows up as BiblioRed's loans spreadsheet: thirteen columns, eleven functional dependencies, non-atomic values, partial and transitive dependencies at the same time, eighty-four thousand rows with typos accumulated since 2018, and a front desk that serves members every day and cannot be stopped.

This lesson is the procedure. Six steps, applied to that sheet from start to finish, with the data in front of us at each stage and the SQL that decomposes and migrates. Then, the two properties every decomposition must satisfy to be correct —and the counterexample, with data, of one that does not satisfy them and invents rows when joined back—. And finally, the part that is least taught and most needed: how you normalize a database that is already in production, and the table-by-table examination of the extended schema we built in module 4.

A word about scope: no normal form is redefined here. They are applied. If at any point you are unsure what exactly 2NF forbids or why BCNF is more demanding than 3NF, go back to 05-02.

1. The six-step procedure
2. Step 1: gather the business rules and write the dependencies
3. Step 2: determine the candidate keys
4. Step 3a: check and reach 1NF
5. Steps 3b and 4a: check and reach 2NF
6. Steps 3c and 4b: check and reach 3NF
7. The final result and the reunion with the module 2 schema
8. Lossless decomposition and Heath's condition
9. The counterexample: a decomposition that invents rows
10. Dependency preservation
11. Minimal cover and the 3NF synthesis algorithm
12. Step 5: verification with control queries
13. Step 6: putting the foreign keys back
14. Normalizing in real life: new design versus production
15. Examination of the extended module 4 schema

This is the complete script. It is the same whether you are designing from scratch or rescuing an existing schema; what changes is the amount of work in step 1 and the risk in step 4.

Two observations before we start.

The order of step 3 is not negotiable. You check bottom-up because the normal forms are cumulative: it makes no sense to look for transitive dependencies in a table that still has lists inside its cells. And as soon as one check fails, you decompose (step 4) and go back to step 2 —because the new tables have new keys— before climbing any further.

Step 5 is the one people skip and the one that costs the most. A decomposition can look flawless on paper and still be losing information or leaving a business rule with nobody watching it. Sections 8 to 12 are entirely about this.

We start from the spreadsheet as it is. And here a correction is needed with respect to lesson 05-01: there we worked with a simplified version of the table so that we could reason about the structure. The real file, beyond the five sample rows we saw in 01-01, has two more columns and one additional problem.

The librarian at the North branch explains it like this: "we kept widening the phone column, because a lot of members give us their mobile and their home landline and we wrote both down separated by a slash. And in the author column, when a book has two authors, we put them with a comma."

This, then, is the complete starting table:

loans_sheet — the real starting point

Six rows. Notice that the original defects from the 01-01 diagnosis are now visible: M. Alsina, north, Ken Follet, m.alsina@exemple.org, the truncated ISBN. We are going to have to deal with them, but later and separately: normalization is work on the structure and cleaning is work on the data. Mixing them up is the surest way of finishing neither.

These are the rules BiblioRed confirms, each with the dependency it produces:

And these are the non-rules, just as important, that came out when we asked about the exceptions:

• "Can two members share a phone number?" → Yes, families giving the home landline. So member_phone → member_email does not exist, however much the sample data seems to suggest it.
• "Can two different editions have the same title?" → Yes, and in fact there are some. So title → isbn does not exist.
• "Does a copy ever change branch?" → Yes, when it is transferred, but a copy is in a single branch at any given moment. The dependency copy_code → branch_name holds, but note that the value is mutable.

Written in split form (a single attribute on the right), which is how you want it for working.

Before building anything on F, it is worth firing off a refutation query for each dependency. Remember from 05-01: data cannot confirm a dependency, but it can refute one, and if it refutes one then either there is dirty data or the rule is not what we were told. Both things have to be known before migrating.

Result of the second one:

There it is: two names (Marta Alsina and M. Alsina) for the same email. This does not refute the business rule: it refutes the quality of the data. The rule is still true —a member has one name— and what the query has found is defect 3 from the 01-01 diagnosis, located with surgical precision.

This is one of the less advertised benefits of normalizing: refutation queries are the best dirty-data detector there is, because they find exactly the contradictions the new schema is going to reject. Run them all and make the list before you start migrating; afterwards is too late.

We apply the method from 05-01, section 13.

Attribute classification. Left side only: copy_code, loan_date. Right side only: return_date, member_name, title, author_nat, branch_city. On both sides: member_email, isbn, branch_name, branch_postal_code. Not appearing in F at all: member_phones, authors (they are multivalued and produce no functional dependencies).

Mandatory core. The "left only" ones and the ones that do not appear are in every candidate key:

This result is itself a diagnosis. That member_phones and authors have to be part of the key is absurd from a business point of view —nobody identifies a loan by the member's list of phone numbers— and it is the formal sign that those columns do not fit the relational model as they stand. It is the 1NF violation we will deal with in the next step.

To be able to move on, let's set those two columns aside for the moment and compute over the remaining eleven:

{copy_code, loan_date}⁺:

It contains the eleven attributes under consideration. It is a superkey. And we already checked in 05-01 that it is minimal ({copy_code}⁺ does not get there and {loan_date}⁺ does not grow). Single candidate key: {copy_code, loan_date}.

Prime attributes: copy_code, loan_date. Everything else, non-prime.

Note that author_nat has not entered the closure: f6 is author → author_nat, and author (in the singular) is not even a column of the table, because the column is called authors and contains a list. Dependency f6 cannot be evaluated in this table. It is another manifestation of the same 1NF problem.

Check. Are all the values atomic?

• member_phones = '600111222 / 938880011' → No. Two values in one cell.
• authors = 'Rosa Vinyals, Pere Coll' → No. Two values in one cell.

The table is not in 1NF. There is no point checking anything else until this is fixed.

Decomposition. Each multivalued attribute goes out into its own table, with the owning entity's key plus the value as the primary key (transformation rule 3 from 04-03, which we now know is the standard 1NF fix).

An important detail shows up here: to pull the phone numbers out you need to know which member they belong to, and the member's identifier in this table is member_email. Same with the authors: they need to hang off the isbn. So the 1NF decomposition already produces three tables.

Migrating the data. This is what you really do when there are eighty-four thousand rows and they cannot be typed by hand. PostgreSQL has functions for splitting strings, and they are exactly the right tool for undoing a 1NF violation:

The DISTINCT is indispensable: Marta Alsina appears in three loans and her two phone numbers would come out nine times without it. It is the first appearance of a pattern that will repeat throughout the process: INSERT ... SELECT DISTINCT is the canonical way of migrating data when decomposing, because the target table holds each fact once and the source table has it repeated.

Result:

p1_phones

p1_work_authors

And here the dirty data leaps out with a clarity it did not have in the original sheet: m.alsina@exemple.org generates a ghost member with duplicate phone numbers; Ken Follet and Ken Follett are two different authors for the same ISBN; the truncated ISBN 978840133909 creates a work that does not exist. Normalization has not created these problems: it has made them visible. Before, they were spread across six wide rows and could not be counted; now they are extra rows that can be listed and corrected.

Note them down on the cleanup list and let's carry on with the structure.

A note on nationality. By pulling author_nat out into p1_work_authors we have put the nationality next to each work-author pair, so it is still repeated once per work by the same author. It is a transitive dependency that we will drag along until the 3NF step. That is normal: each normal form fixes its own part, and the intermediate decompositions are not the final result.

We go back to step 2 with p1_loans, whose key is {copy_code, loan_date}.

2NF check. Are there non-prime attributes depending on part of the key? We compute the closure of each part:

• {copy_code}⁺ = {copy_code, isbn, title, branch_name, branch_postal_code, branch_city}. It contains five non-prime attributes. There are partial dependencies.
• {loan_date}⁺ = {loan_date}. It contributes nothing.

p1_loans is not in 2NF. The offending dependencies are f3 (copy_code → isbn) and f4 (copy_code → branch_name), and they drag along everything that hangs off them: title, branch_postal_code, branch_city.

Before the decomposition, this is the redundancy we are going to remove. Look at rows 1 and 2: they are the same copy lent twice, and the nine columns on the right are identical:

Decomposition. Everything that depends on copy_code goes to a table with copy_code as the primary key; in the loans table, copy_code stays as a reference.

A warning about this DISTINCT. If the data were dirty in one particular way —the same copy_code with two different branches because of a typo, or with North and north— the SELECT DISTINCT would return two rows for the same copy and the INSERT would fail with a primary key violation. And that is exactly what happens here:

Because EJ-3081 appears with North in one row and with north in another. The error is not a failure of the migration: it is the migration working. The new schema is rejecting a contradiction the old one allowed. This is the moment to apply the cleanup, which in this case is trivial:

And run the INSERT again. This cycle —migrate, fail, clean, retry— is the normal rhythm of normalizing historical data, and that is why it is always done on a copy and inside a transaction.

Result after the cleanup:

p2_copies

p2_loans

There is no longer a single row where the title of "The Map of Time" is repeated because of a loan. With six rows the saving is modest; with eighty-four thousand loans over forty thousand copies, the title column goes from eighty-four thousand values to forty thousand, and —what really matters— from eighty-four thousand opportunities to misspell it to forty thousand.

Now there are three tables to check. Let's take the two that have candidates for transitive dependencies.

Key: {copy_code, loan_date}. Dependencies that hold here: f1, f2 and f7 (member_email → member_name).

We apply the mechanical 3NF check to f7:

• Is member_email a superkey? {member_email}⁺ = {member_email, member_name}. It does not contain the key. No.
• Is member_name prime? The prime attributes are copy_code and loan_date. No.

It violates 3NF. It is the transitive dependency {copy_code, loan_date} → member_email → member_name, and its symptom in the data is that Marta Alsina appears twice and Nuria Bastos twice more.

And once again:

Marta Alsina and M. Alsina. The new schema does not allow a member to have two names, which is precisely what we wanted. Cleanup and retry:

p3_members

Three members. Before normalizing, a COUNT(DISTINCT member_name) over the sheet gave five. That is defect 3 from 01-01, solved.

Key: {copy_code}. Dependencies that hold: f3, f4, f5 (isbn → title), f8 (branch_name → branch_postal_code) and f9 (branch_postal_code → branch_city).

Three chained 3NF violations:

All three are pulled out. Note that branch_name → branch_postal_code → branch_city is a two-hop chain and produces two tables, not one: the branches table and the postal codes table. It is the case we saw in 05-02, section 6.

p3_works

Four works where there are three: the truncated ISBN is still there. This time the INSERT does not fail, because technically 978840133909 and 9788401339097 are different keys. It is an important warning: normalization only detects automatically the errors that produce contradictions; a mistyped identifier that collides with nothing else goes unnoticed. Here you need a domain validation —the ISBN check digit, a length CHECK— which is what we learned to add in 04-04.

After cleaning up the ISBN by hand:

p3_works

p3_branches

p3_postal_codes

Key: {isbn, author}. f6 holds: author → author_nat.

• Is author a superkey? No, it is half the key.
• And since it is half the key, the dependency is partial: it violates 2NF, not just 3NF.

It is decomposed the same way:

After correcting Ken Follet → Ken Follett:

p3_authors

Key: {member_email, number}. Both attributes are prime and there is no other. As we saw in 05-02, every two-attribute table whose key is the pair is in BCNF by construction. Nothing to do, beyond reissuing it with the corrected emails.

This is the schema we have arrived at, starting from one thirteen-column table:

Nine tables. And all of them in BCNF, not just in 3NF: since each one has a single candidate key and is in 3NF, the rule from 05-02 applies automatically.

Now compare it with the diagram we drew in lesson 01-01 —members, loans, copies, books, branches— and with the schema we wrote in module 2 by understanding the domain. They are the same schema. Formal normalization has arrived, by a completely different route and without consulting the earlier result, at the same structure the intuitive design arrived at.

This is not a coincidence or a teaching trick: it is the expected result, and it is the best possible justification of the theory. An experienced designer arrives at a 3NF schema without thinking about functional dependencies, because they have internalized the same constraints. Normalization is good for three things intuition does not provide: verifying that the intuitive design is correct, arbitrating when two people disagree, and resolving the rare cases where intuition gets it wrong.

The differences from the module 2 schema are matters of detail and they all point in that direction:

• Our tables use natural keys (member_email, copy_code, isbn, author). The real schema uses surrogate keys (member_id, copy_id, book_id, author_id), for the reasons in 04-01: the email changes, the author's name is written several ways, and a key that changes propagates to every table referencing it. Normalization does not force you to use natural keys; we did because the sheet had nothing else. Replacing them with surrogates is the next step and does not alter the normal form.
• The real schema has books as a view over materials + materials_book, the result of the generalization hierarchy from 04-02 and 04-03. Our p3_works is the version without the hierarchy.
• The real schema stores addr_city in branches instead of having postal_codes. It is a deliberate denormalization on a four-row table, and we will discuss it in 05-04.

We have decomposed eight times. How do we know we have not lost anything along the way?

Definition. A decomposition of a relation R into R1 and R2 is lossless (or non-additive join) if, when you take the natural JOIN of R1 and R2, you get exactly R: not one row fewer, not one row more.

The name is slightly misleading, because the problem is not usually losing rows: it is gaining them. A badly done decomposition produces, when joined back, rows that never existed. And those rows are lies: combinations of data that the database asserts and that never happened.

The condition that guarantees it is simple and has a name of its own:

Heath's condition. Let R be a relation whose attributes are divided into three groups X, Y and Z. If the functional dependency X → Y holds, then decomposing R into R1(X, Y) and R2(X, Z) is lossless.

In plain language: always decompose along a determinant. If the attribute (or set of attributes) that stays in both tables —the one that acts as "glue" for the JOIN— is a key of at least one of them, you cannot lose or invent anything.

The intuitive reason is this: if X is a key of R1, then each value of X appears exactly once in R1. When you JOIN, every row of R2 finds exactly one partner, so the number of rows in the result is the number of rows in R2, which was the original number. No multiplication is possible.

Look at the 2NF step. We split p1_loans into:

• p2_loans(copy_code, loan_date, return_date, member_email, member_name)
• p2_copies(copy_code, isbn, title, branch_name, branch_city, branch_postal_code)

The shared attribute is copy_code, and it is the primary key of p2_copies. Heath's condition satisfied: the decomposition is lossless.

And it can be verified empirically, which is what you should always do:

Zero rows in both directions: an exact reconstruction.

So that you can see what is being avoided, let's deliberately do a bad decomposition.

Take three columns from the sheet: member_email, title and branch_name. This is the real data (one row per loan, dropping exact duplicates):

Original R

Now somebody decides, reasoning "a member has their titles and a member has their branches", to decompose like this:

R1(member_email, title)

R2(member_email, branch_name)

It looks reasonable. Now let's join them back:

With this particular data the result happens to match, because each member has only one branch. Let's add one more loan, a perfectly ordinary one: Nuria Bastos takes "The Pillars of the Earth" from the North branch as well, one day when she was passing by.

Original R (5 rows)

R2 now has two rows for Nuria: (n.bastos, South) and (n.bastos, North). And now the JOIN:

Result of the JOIN (7 rows)

The fifth row is false. Nuria Bastos never took the "Urban Gardening Handbook" out of the North branch; she took it from South. The JOIN has invented it by combining her two titles with her two branches.

And here is the worst part: that row is indistinguishable from the true ones. There is no mark identifying it. Any report on what gets lent at each branch will come out wrong, and nobody will know why.

Why did it fail? Because the shared attribute, member_email, is not a key of either of the two tables: a member has several titles and several branches. Heath's condition does not hold, and therefore the decomposition is not guaranteed.

The correct decomposition of these three attributes would be along a real determinant. Since the title depends on the copy and the copy on the branch, the correct route goes through copy_code, which is exactly what we did in section 5.

Pocket rule: before splitting a table, ask yourself "is the column I am going to join them back on the primary key of at least one of the two?". If the answer is no, do not split: you are about to invent data.

The second property. We already met it in 05-02 when talking about BCNF; here we formalize it and check it on our decomposition.

Definition. A decomposition preserves dependencies if every dependency in the original set F can be checked inside a single one of the resulting tables, without having to join them.

Why it matters, in practical terms: a dependency that lives inside one table is guaranteed by a PRIMARY KEY or a UNIQUE, and the DBMS watches it on every INSERT without anybody having to remember. A dependency spread across two tables needs a trigger, application code or a periodic audit query: that is, something that can be forgotten, disabled or run too late.

All nine dependencies are preserved, and all nine are guaranteed by a primary key. Not one trigger, not one line of application code.

This is no coincidence. When the decomposition is done by pulling each dependency out into a table whose determinant is the primary key, preservation comes for free: the dependency X → Y literally becomes "X is the primary key of the table containing Y", and that is what a primary key means.

The problematic case is the one we saw in 05-02, section 8: when there are overlapping candidate keys and BCNF is forced, some dependency can end up split. That has not happened here because each table has a single candidate key.

If, when a decomposition is finished, there is a dependency that lives in no table, you have three ways out, in order of preference:

What is not an option is not noticing. Always build the table from the previous section: one row per dependency, one column with the table it lives in. If any cell is left empty, decide consciously.

Everything we have done has been by decomposition: start from one big table and keep splitting it. There is the opposite route, called synthesis: start from the set of dependencies and build the tables directly. We present it at the level of the idea, because it is worth knowing it exists.

A minimal cover (or canonical cover) of a set of dependencies F is another set Fc that determines exactly the same as F —it has the same closure— but is reduced to the minimum: each dependency has a single attribute on the right, no attribute on the left is superfluous, and no whole dependency is superfluous.

It is computed in three steps:

1. Split the right-hand sides, using the decomposition rule from 05-01.
2. Remove superfluous attributes from the left: for each {A, B} → C, check whether A → C already follows from the rest; if so, B was superfluous.
3. Remove redundant dependencies: for each X → Y, take it out of the set and check with the closure whether Y ⊆ X⁺ still holds using only the others; if so, it was superfluous.

Our BiblioRed F is already practically a minimal cover: it is split, no left-hand side has surplus attributes, and no dependency follows from the others. You only have to watch out for the ones that follow by transitivity. For instance, if somebody had added copy_code → title to the list, it would be redundant, because it already follows from copy_code → isbn and isbn → title. Including it would lead to creating one table too many.

With the minimal cover computed, the algorithm is surprisingly direct:

Applied to our F, grouping by left-hand side:

Seven tables, and the first contains the candidate key, so step 3 adds nothing. They are exactly the seven tables we reached by decomposition (the other two, p3_phones and p3_works_authors, came out of the multivalued attributes, which produce no functional dependencies and therefore fall outside this algorithm).

Two things have to be known about this algorithm:

• It guarantees 3NF, dependency preservation and lossless decomposition. All three at once. It is a strong result and it is the reason 3NF is considered the default target: it is always reachable without sacrificing anything.
• It does not guarantee BCNF. We already know why: there may be no BCNF decomposition that preserves dependencies.

In practice almost nobody runs this algorithm by hand on a real project —you design by understanding the domain and verify with normalization— but knowing it changes how you look at a schema: every well-designed table corresponds to a group of dependencies with the same determinant, and that determinant is its primary key. If you find a table that does not fit that description, you have something to review.

Once the decomposition and the migration are done, you have to prove the result is correct. Looking at it is not enough: you have to run checks. These are the four that must not be missing from any migration.

The main table must have exactly the same rows as the original:

If the target has fewer, loans have been lost (probably through exact duplicates removed by a misplaced DISTINCT). If it has more, something has multiplied.

Each new table must have as many rows as there were distinct values in the original after the cleanup:

The discrepancies are not errors: they are the record of the cleanup. Four distinct emails gave three members (m.alsina@exemple.org was merged); four ISBNs gave three works (the truncated one was corrected); three branch names gave two (North/north). Every difference must be justified and noted down. A difference you cannot explain is an error.

The definitive test: join everything back and compare with the original.

Six rows, the same as there were. Not one extra: we have invented nothing.

Notice one thing: using JOIN and not LEFT JOIN is part of the test. If any loan pointed at a member, a copy or a work that did not exist, the inner JOIN would leave it out and the count would drop. A count that matches with an inner JOIN proves at once that nothing is missing and that referential integrity holds.

Finally, and this is what convinces whoever is paying: the queries the business used to run on the sheet have to keep working and give the same result.

On the original sheet, that query gave four rows and credited Marta with only two loans, because the third one was under the misspelled email. The normalized schema does not just give the same answer: it gives the right answer, which the sheet did not.

The decomposition has left columns pointing at other tables without declaring it. You have to tell the DBMS, because until you do nothing prevents a loan by a nonexistent member.

And with that, the constraint covering defect 6 of the 01-01 diagnosis —Iván Pereda's return date earlier than his loan date— is now prevented by the CHECK. If any historical row breaks it, the ALTER TABLE will fail and you will have to decide: fix the data or add the constraint as NOT VALID, as we saw in 04-04.

The criteria for choosing CASCADE, RESTRICT or SET NULL on each foreign key are the ones from lesson 02-06; here they are only applied.

The final result: out of a sheet with eight cataloged defects, six have become structurally impossible (member redundancy, book redundancy, name inconsistency, author inconsistency, inconsistent branch formatting, fragmentation across files), one is now prevented by a CHECK (the impossible date), and one —the truncated ISBN— requires a domain validation, which is the job of 04-04 and not of normalization.

Everything above has been done on a table that was standing still. In reality there are two very different situations.

This is the easy case and the most frequent one in a project that is starting. You do not normalize at the end: you design normalized from the beginning. You build the ER model (04-02), transform it (04-03), choose types and constraints (04-04), and normalization is used as a checklist before writing the first line of application code.

The review is quick when the design is well made: table by table, you write its dependencies, compute its candidate key and check 3NF. Half an hour for a twenty-table schema. And what it finds is usually little but valuable: a column that sneaked into the wrong place, a business rule nobody had written down.

Here the problem is not the theory: it is that there are eighty thousand rows, forty queries written against the old schema and a front desk that opens at nine tomorrow morning. Everybody knows how to do the normalization correctly; the hard part is applying it without cutting off the service.

The standard technique is phased migration with expand and contract:

Phase 1 — Expand (without breaking anything). The new tables are created alongside the old one, empty. Nothing uses them yet. The application keeps working exactly as before.

Phase 2 — Backfill in the background. The historical data is migrated with the INSERT ... SELECT DISTINCT statements we have seen, in batches, at quiet times, and with the refutation queries run beforehand so that you know what is going to break.

Phase 3 — Dual writing. The application is modified so that it writes to both structures at once, the old one and the new one, within the same transaction. It still reads from the old one. This is the gentlest possible point of no return: if something goes wrong, you disable the new write and nothing has happened.

Phase 4 — Switch the reads. The queries are moved to the new schema one at a time, starting with the least critical. A view with the name of the old table that reads from the new schema lets you move many queries without touching the code:

Phase 5 — Contract. When no query uses the old structure any more, the dual writing is removed and the old table is dropped. Before dropping it, a copy is saved: always.

Five rules not to skip:

1. Everything is rehearsed first on a copy of production. With the real volume, not with six rows.
2. The refutation queries are run before starting, so that you have the dirty-data list and can decide what to do with each case. You do not discover it halfway through the migration.
3. Each phase is reversible on its own. If phase 4 goes badly, you go back to reading from the old one.
4. The control queries from section 12 are run after each phase, not only at the end.
5. The data cleanup is documented. Every merged row, every corrected value, with its rationale. Somebody will ask in two years' time why Marta Alsina has three loans and not two.

And now the test promised at the close of module 4: putting the schema of the BiblioRed extension through the formal instruments. Let's go table by table with the four that the module's brief flagged as the most exposed.

Single candidate key: {event_id}, a single attribute. 2NF guaranteed.

3NF check on the second dependency:

• Is {start_time, end_time} a superkey? No: two different events can start and finish at the same time in different rooms.
• Is duration_min prime? No.

Formally, events violates 3NF. duration_min is a transitive dependency: it depends on start_time and end_time, which are not a key.

And yet we leave it. The reason is in the CREATE TABLE from 04-04:

It is a generated column, and the keyword is ALWAYS: PostgreSQL computes the value on every INSERT and every UPDATE, and does not allow it to be written by hand. The contradiction 3NF exists to prevent —that duration_min says one thing and the timestamps say another— is physically impossible.

General criterion: a column generated by the DBMS is a denormalization with a guarantee. It violates the normal form in the letter, not in the spirit, because the risk the normal form prevents has been eliminated by another mechanism. Note it as such in the schema documentation and move on.

Verdict: 3NF for practical purposes. A documented denormalization guaranteed by the DBMS.

There is a second, more debatable point: the status column, which can hold 'full'. That value can be derived by comparing offered_seats with the sum of occupied_seats across the registrations. It is information derived from another table, and nothing guarantees it. There is no functional dependency here that captures it —normalization theory does not talk across tables— but it is redundancy all the same, and of the dangerous kind: nothing prevents status = 'full' while seats are free. It is a textbook case for the treatment in lesson 05-04.

Candidate key: {event_id, member_id}, composite. 2NF has to be checked carefully.

• {event_id}⁺ = {event_id}. No dependency starts with event_id alone. No partial dependencies there.
• {member_id}⁺ = {member_id}. Same. No partial dependencies.

It is in 2NF. And that is a good result: it means that registration_date, status and companions are genuinely facts about the registration, not about the event or the member. If somebody had put member_name or event_title in here —the usual temptation— there would be immediate partial dependencies.

For 3NF, the only candidate is companions → occupied_seats, and it is exactly the same case as duration_min: a generated column ALWAYS AS (1 + companions) STORED. Same conclusion.

Verdict: BCNF for practical purposes (single candidate key + 3NF ⟹ BCNF), with a denormalization guaranteed by the DBMS.

Here is the find of the examination. The dependencies:

The second one comes from a rule we had never written down but that is obvious: a loan was made by a particular member. If fine 900 is associated with loan 5001, and loan 5001 was made by member 14, then the member on fine 900 has to be 14. There is no choice.

3NF check:

• Is loan_id a superkey of fines? No: one loan can generate two fines (one for a late return and another for damage; in fact the constraint uq_fines_loan_reason explicitly allows for it).
• Is member_id prime? The only candidate key is {fine_id}. No.

fines violates 3NF. It is a transitive dependency fine_id → loan_id → member_id, and the anomaly is real and serious:

The DBMS accepts it. Both foreign keys are satisfied —member 16 exists, loan 5001 exists— but the data is false: we have just fined Nuria Bastos for Marta Alsina's late return. And in a fines system, that is not an academic detail: it is a complaint.

The query that detects it:

What is the fix? There are three options and all three are defensible depending on the case:

Option C is the most elegant one and the one to know, because it is a design trick that solves many cases of this kind:

Now the false INSERT from before is impossible:

And when loan_id is NULL —a fine with no loan— the foreign key is not checked (MATCH SIMPLE behavior, the SQL default), so those cases keep working. The best of both options.

Verdict: fines was not in 3NF. Fixed with a composite foreign key, the dependency is now guaranteed by the DBMS. This is the kind of find that justifies the whole module: it is a real defect, with real consequences, that the intuitive design of module 4 did not see and that formal analysis finds in two minutes.

A second point about fines, subtler. If BiblioRed had a fixed rate per reason —reason → amount— that would be another 3NF violation and the rate should be in a fine_rates table. But the amount is not derived from the reason: it depends on the number of days late, and above all it has to stay frozen at the value it had on the day it was issued, even if the by-law changes later. Storing it in fines is correct and necessary. It is a case of frozen historical duplication, exactly like the one we saw in 03-03 for document modeling, and its treatment is material for 05-04.

Single candidate key: {payment_id}. 2NF guaranteed.

Are there transitive dependencies? Let's go through the columns: payment_date, amount, method and reference are all facts about the particular payment. method → reference does not hold (several card payments have different references). amount → nothing. fine_id → nothing else within this table.

Verdict: payments is in BCNF. No observations.

Although it is worth pointing out the trap that was avoided: if somebody had added member_id to payments "so as not to have to do two JOINs", we would have fine_id → member_id and exactly the same problem as in fines. And if they had added fine_amount in order to compare, we would have fine_id → fine_amount. Both are common temptations and both are 3NF violations. That they are not there is to the credit of the design in 04-03.

(The asterisk marks the tables whose only formal deviation is a column generated by the DBMS.)

Conclusion of the examination: the module 4 schema holds up. Out of eleven new tables, ten were correct and one had a real defect that is now fixed. That is a good result for a design made by understanding the domain, and at the same time the proof that formal verification is not superfluous: that defect was there and nobody had seen it.

Skipping step 1 and deriving the dependencies from the data. We warned about this in 05-01 and here you pay for it twice over, because an invented dependency produces a decomposition that will reject legitimate data in production. Refutation queries are for detecting contradictions, not for discovering rules.

Decomposing without checking Heath's condition. It is the cause of the counterexample in section 9, and its symptom is that the reconstruction JOIN returns more rows than the original. Before each CREATE TABLE, ask yourself which column will be the join column and whether it is the primary key of either of the two tables.

Forgetting the DISTINCT in the migration. INSERT INTO members SELECT member_email, member_name FROM loans_sheet will fail with a primary key violation as soon as a member has two loans. The DISTINCT is not an optimization: it is part of the meaning of the migration.

Reading a duplicate key error as a failure of the migration. It is almost always the opposite: it is the new schema rejecting a contradiction the old one allowed. Before touching the INSERT, look at which rows clash; that is your dirty-data list.

Mixing data cleaning and normalization in the same step. They are two jobs with different criteria and they have to be kept apart: structure first, and when the structure rejects something, you note it down, agree the cleanup criterion with the business and apply it. Fixing things on the fly produces improvised decisions that nobody documents.

Calling the migration finished without the control queries. The fact table count, the catalog counts, the reconstruction with an inner JOIN and the comparison of the business queries. Four queries, fifteen minutes, and they are the difference between "I think it is fine" and "it is fine".

Normalizing in production in one go. Never. Expand, backfill, dual write, switch the reads, contract. Every phase reversible, every phase verified.

Not documenting why a table is left as it is. The duration_min case is the perfect example: somebody auditing the schema in two years' time will see a 3NF violation and "fix" it. Write next to it that it is an ALWAYS generated column and that the decision is deliberate. The schema documentation from 04-01 is the place.

BiblioRed receives this flat table from a neighboring library that is joining the network:

donations(donor_tax_id, donor_name, donor_city, city_province, material_isbn, material_title, donation_date, condition_status)

Confirmed business rules:

• A donor can donate the same material on different dates.
• The tax ID identifies the donor, with their name and their city.
• Each city belongs to a province.
• The ISBN identifies the material and its title.
• The condition status is recorded at the moment of each particular donation.

You are asked to:

• a) Write the set F of functional dependencies.
• b) Determine the candidate key by computing the closure.
• c) Identify the 2NF and 3NF violations.
• d) Write the CREATE TABLE for the resulting 3NF tables and the INSERT ... SELECT DISTINCT that would migrate the data.

A colleague proposes decomposing BiblioRed's participations(event_id, speaker_id, role) table into two:

• R1(event_id, speaker_id)
• R2(event_id, role)

with the argument that "this way we separate who comes from what gets done".

With this data:

You are asked to:

• a) Build R1 and R2 and take the natural JOIN on event_id.
• b) Say how many rows come out and which ones are false.
• c) Explain with Heath's condition why it fails.
• d) Say what information has been irreversibly destroyed.

BiblioRed wants to add a table to the schema for advance room bookings by external organizations:

Knowing that each room has a capacity and belongs to a branch, that the tax ID identifies the organization, that the hourly rate depends on the branch, and that the total amount is the rate times the hours booked:

• a) Write the functional dependencies.
• b) Determine which normal form it is in and which dependencies violate it.
• c) Propose the fix, saying which column is best not removed even though it is redundant, and why.

Solution 1

a) Functional dependencies:

b) Candidate key. Attributes on the left only: donation_date. Attributes on both sides: donor_tax_id, material_isbn, donor_city. On the right only: the rest.

The mandatory core includes donation_date. We try {donor_tax_id, material_isbn, donation_date}:

All eight attributes. It is a superkey. And it is minimal: without donation_date you cannot determine condition_status (the same donor can donate the same material twice in different conditions); without donor_tax_id you cannot reach the donor's details; without material_isbn you cannot reach the title.

Candidate key: {donor_tax_id, material_isbn, donation_date}. Prime: those three. Non-prime: the other five.

c) Violations.

2NF — partial dependencies (parts of the key determining non-prime attributes):

• g2 and g3: donor_tax_id is a third of the key and determines donor_name and donor_city. Partial.
• g5: material_isbn is a third of the key and determines material_title. Partial.

3NF — transitive dependency:

• g4: donor_city → city_province, with donor_city not a superkey and city_province not prime. Transitive.

d) Resulting schema:

Migration, in dependency order (referenced tables first):

Note that only the last one has no DISTINCT: it is the fact table and it must keep exactly the same rows as the original.

Solution 2

a) The two projections:

R1(event_id, speaker_id)

R2(event_id, role)

Natural JOIN on event_id:

b) Five rows where there were three. Two are false: they say that speaker 7 was a workshop leader at event 210 and that speaker 9 was a moderator, when it was exactly the other way round.

c) Heath's condition fails. The shared attribute is event_id, and it is not the primary key of either table: event 210 appears twice in R1 and twice in R2. When joined, those two rows on each side combine with each other and produce 2 × 2 = 4 rows where there were 2. It is the same mechanism as the counterexample in section 9.

For the decomposition to be lossless you would need a dependency event_id → speaker_id or event_id → role, and neither holds: an event has several speakers and several roles.

d) The association between speaker and role has been destroyed. That is the fact the table existed to store: not "speakers 7 and 9 took part in event 210", nor "at event 210 there was a moderator and a workshop leader", but "7 was the moderator and 9 the workshop leader". That information is in neither projection and there is no way of recovering it.

It is also, incidentally, the answer to exercise 3b of lesson 05-02: participations is a legitimate ternary relationship and not a 4NF violation, precisely because role depends on the event-speaker pair and not on the event alone.

Solution 3

a) Functional dependencies:

b) The candidate key is {booking_id}, a single attribute, so 2NF is guaranteed. The violations are all of 3NF, and there are four chained transitive dependencies:

The table is in 2NF and does not reach 3NF. The full chain is booking_id → room_id → branch_id → hourly_rate, three hops.

The anomalies are the expected ones: if a room is refurbished and its capacity changes, every one of its historical bookings has to be updated; if the city council raises a branch's rate, every booking of every one of its rooms has to be touched; and if an organization changes its name, you have to hunt it down everywhere.

c) The fix. The columns whose information already lives in another table are removed and reached through a foreign key:

Gone are room_capacity, branch_id and entity_name: they are obtained with a JOIN to rooms and entities, and that way they cannot contradict each other.

hourly_rate and total_amount stay, and this is the interesting part of the exercise. Formally they are redundant: the rate is in branches and the amount is computed. But they are historical data that must stay frozen: the 12 March booking was invoiced at €18.00/hour, and if the city council raises the rate to €22.00/hour in April, that March invoice cannot change. If hourly_rate were obtained by a JOIN to branches, any query about past bookings would return amounts that do not match the invoices issued.

It is exactly the same case as the amount in fines that we analyzed in section 15.3, and the same "frozen historical duplication" we saw in the document modeling of 03-03. It is a deliberate, correct and necessary denormalization, and it has to be documented as such so that nobody "fixes" it later. How these decisions are made and justified is the entire content of the next lesson.

(Final detail: total_amount can not be an ALWAYS AS generated column, even though it looks like one, precisely because it must keep the historical value and not be recomputed if the rate changes. That is the difference between duration_min —derived from columns of the row itself, which do not change— and an amount derived from an external value that does.)

We have done the complete job. From a spreadsheet with thirteen columns, six sample rows and eight defects cataloged since the first lesson of the course, to nine tables in Boyce-Codd normal form, with their foreign keys, their constraints and their data migrated and verified.

The procedure is six steps: gather the business rules and write the dependencies; determine the candidate keys with the closure; check 1NF, 2NF and 3NF/BCNF in that order, stopping at the first failure; decompose by pulling each offending dependency out into a table whose determinant is the primary key; verify; and put the foreign keys back. Go back to step 2 after each decomposition, because the new tables have new keys.

The two properties every decomposition must satisfy are non-negotiable. Lossless: the JOIN must return exactly the original, and Heath's condition guarantees it if you always decompose along a determinant —if the join column is the primary key of at least one of the two tables—. We saw with data what happens when it does not hold: a decomposition that looked reasonable invented a row asserting that Nuria Bastos took a book out of a branch she was never in, and that row was indistinguishable from the true ones. Dependency preservation: every business rule must be checkable inside a single table, and when that is not possible you have to decide consciously between stepping back to 3NF, adding a trigger or auditing periodically.

We also learned some things that do not appear in the theory books. That INSERT ... SELECT DISTINCT is the canonical way of migrating when decomposing. That a duplicate key error during the migration is almost never a failure of the script: it is the new schema rejecting a contradiction the old one allowed, and there is your dirty-data list. That normalization does not clean the data, it makes it visible: Marta Alsina and M. Alsina were hidden among six wide rows and turned up the moment the primary key of members refused to admit them. And that in production you do not normalize in one go: you expand, backfill, write twice, switch the reads and contract, with the control queries run after each phase.

And we put the module 4 schema through the examination. It held up, which was what had to be checked: events and registrations in 3NF and BCNF respectively, with the sole exception of their ALWAYS generated columns, which are denormalizations with a DBMS guarantee; payments in BCNF with no observations. And one real find: fines violated 3NF through the dependency loan_id → member_id, which made it possible to attribute one member's late-return fine to another. The fix —a composite foreign key (loan_id, member_id) backed by a redundant UNIQUE on loans— leaves the rule guaranteed by the DBMS with no need for triggers. A defect the intuitive design did not see and that formal analysis found in two minutes: that is exactly what this module is for.

And yet, three times over the course of the lesson we have run into the same thing, and all three times we decided not to normalize: duration_min in events, occupied_seats in registrations, the frozen amount in fines and the hourly_rate of the bookings. All four are redundancies. All four violate the letter of third normal form. And all four are correct.

That is neither a contradiction nor an awkward exception: it is the other half of the craft. In lesson 05-04, Denormalization and Its Uses, the opposite decision is studied with the same rigor we have applied to this one. What exactly you gain and what you pay when you denormalize; when it is justified and when it is plain laziness; the techniques one by one —computed columns, summary tables, materialized views, the star schema of analytical warehouses—; how you keep consistent what you have duplicated on purpose; and the golden rule that governs the whole module: normalize first, then denormalize on purpose, measuring, and never the other way round.