In the previous lesson you started from a client telling you what they needed and arrived at a schema. Here the starting point is the opposite: a table that already exists and is wrong. A flat listing exported from a spreadsheet, a history with the member's name copied into every row, a table where changing one phone number forces you to touch fourteen records. Your job is to prove formally what is wrong with it, predict what it is going to break and fix it without losing information.

How to work through this lesson. Normalization is one of the few parts of databases that can be done without a computer, with pencil and paper, and it is worth doing it that way at least the first few times. The procedure for every exercise is always the same:

1. Write the functional dependencies that follow from the business rules and from the data.
2. Compute the closures you need and determine all the candidate keys.
3. Classify the attributes as prime and non-prime.
4. Go through the normal forms in order —1NF, 2NF, 3NF, BCNF, 4NF— and stop at the first one that is violated, pointing at the guilty dependency.
5. Decompose, check that the decomposition is lossless and check whether it preserves the dependencies.

A warning about the data: in several exercises you will see a table with specific rows. Data does not prove a functional dependency, it can only refute it. The fact that in five rows each advisor appears with a single specialty does not prove that advisor → specialty; what proves it is the business rule. On the other hand, if two rows had the same advisor with different specialties, it would be refuted. The data is the check, the rules are the source.

In this lesson there is no design from requirements (that is 07-02) and no execution plans (that is 07-04). Only formal analysis, decomposition and a final denormalization decision.

You do not need any previous dataset: each exercise brings its own and the ones in block C include the SQL to create the starting table. What is worth having at hand is:

• The definition of functional dependency and Armstrong's axioms (reflexivity, augmentation, transitivity) from lesson 05-01.
• The attribute closure algorithm X⁺: start from X, and while there is some dependency Y → Z with Y ⊆ X⁺, add Z to X⁺.
• The definitions of the normal forms from lesson 05-02, in the formulation we will use here:

A vocabulary reminder: an attribute is prime if it belongs to some candidate key; non-prime otherwise. A dependency is partial if a non-prime attribute depends on a proper subset of a candidate key; transitive if it goes from the key to a non-prime attribute via another non-prime attribute; and full or complete if it depends on the whole key and not on any part of it.

If you want to run the SQL of block C, any empty PostgreSQL database will do. The INSERT ... SELECT DISTINCT statements work the same in SQLite.

1. Block A — Basic: functional dependencies, closures and candidate keys (exercises 1-3)
2. Block B — Intermediate: diagnosing real tables (exercises 4-6)
3. Block C — Advanced: complete normalization, BCNF, 4NF and denormalization (exercises 7-10)
4. Common mistakes and tips
5. Reinforcement exercises

Difficulty: Basic

Task. BiblioRed offers a photocopying service. Each copy job is recorded in a single table with these attributes:

job_id, member_id, member_email, job_date, pages, paper_type, page_rate, amount

Business rules:

• Each copy job has an identifier of its own, job_id.
• Each member has a single email address and no two members share an email.
• The rate per page depends exclusively on the paper type (plain, recycled, glossy).
• The amount is the result of multiplying the pages by the rate per page.

You are asked to:

• (a) Write the set F of functional dependencies.
• (b) Compute {job_id}⁺ step by step, stating which dependency is used at each step.
• (c) Compute {member_email}⁺ and {paper_type, pages}⁺.

Hint. "No two members share an email" is a functional dependency in the direction you may not expect.

Solution

(a) The set F:

(b) Closure of {job_id} step by step:

(c)

Expected result

Since {job_id}⁺ contains all the attributes of the relation, job_id is a superkey. And since it is a single attribute, it is also minimal: it is a candidate key.

{member_email}⁺ stops at two attributes: member_email is not a superkey, even though it does determine member_id. {paper_type, pages}⁺ stops at four: it is not one either.

Explanation. Three points worth fixing in your mind:

• f3 is the dependency most people forget. "No two members share an email" means that the email determines the member: member_email → member_id. It is a UNIQUE constraint translated into the language of functional dependencies, and it is what makes the email an alternate key of the member entity. Ignoring it leads to miscounting the candidate keys in later exercises.
• f5 is a real dependency, not a formula. The temptation is to say "the amount is computed, it does not depend". It does depend: given specific values of pages and page_rate, the amount is determined. Being computable does not exclude it from the analysis; what happens is that in the final design we will probably solve it with a generated column rather than with a new table.
• The closure is computed until it stops growing, not until you have gone through the list once. At step 3 page_rate appears, which is what allows f5 to fire at step 4. Had you stopped after one pass, you would have concluded that amount is not in the closure and job_id would not be a superkey.

Difficulty: Basic

Task. BiblioRed's room occupancy schedule has been exported to a single table:

OCCUPANCY(room, day, hour, event, capacity, branch)

Business rules:

• Each room is in a single branch and has a single capacity.
• In a given room, on a given day and at a given hour, only one event can be held.
• Each event is held in a single place, on a single day and at a single hour.

You are asked to:

• (a) Write F.
• (b) Find all the candidate keys, justifying that there are no more.
• (c) Classify the attributes as prime and non-prime.

Hint. Start by looking for the attributes that do not appear on the right-hand side of any dependency: they have to be in every key.

Solution

(a)

(b) Systematic search for candidate keys.

First, the classification of attributes according to where they appear:

Since no attribute stays out of every right-hand side, there is no mandatory core and combinations have to be tried. We compute closures:

Candidate keys: {event} and {room, day, hour}.

{event} is minimal because it is a single attribute. {room, day, hour} is minimal because none of its three proper two-attribute subsets is a superkey ({room,day}, {room,hour} and {day,hour} all fall short, as the closures show). Any other set that is a superkey contains one of the two, so it is not minimal.

(c)

• Prime attributes (they belong to some candidate key): event, room, day, hour.
• Non-prime attributes: capacity, branch.

Expected result

Explanation. The method worth automating is this one:

1. Attributes that never appear on the right → they are in every candidate key (here there are none).
2. Attributes that never appear on the left → they are in no candidate key (here, capacity and branch; that is why they are non-prime with no further checking needed).
3. The rest have to be tried, starting with the small sets.

The frequent mistake is stopping at the first candidate key that shows up. It is very easy to see {room, day, hour} —the one suggested by reading the task— and not notice that {event} is one too, because g3 is a dependency that reads "backwards" from the way you think about a schedule. And that second key changes the diagnosis: capacity and branch depend partially on {room, day, hour} (because room is a proper subset) and transitively on {event}. The table is not even in 2NF.

Another mistake: counting {event, room} as a candidate key because its closure is everything. It is a superkey, not a candidate key: room is superfluous. Minimality is part of the definition.

Difficulty: Basic

Task. Using the two relations from exercises 1 and 2, classify each of these dependencies with respect to the candidate key indicated. The categories are: full (or complete), partial and transitive. State also which normal form each one violates.

Solution

Expected result

REPROGRAPHICS is in 2NF but not in 3NF (two transitive dependencies: paper_type → page_rate and member_id → member_email).

OCCUPANCY is only in 1NF: room → capacity and room → branch are partial with respect to {room, day, hour}.

Explanation. The point to internalize is in rows 4 and 6: the same dependency is classified differently depending on which key you take as the reference. room → branch is partial with respect to {room, day, hour} and transitive with respect to {event}. It is not a contradiction: they are two descriptions of the same problem.

From that comes the operational rule that saves time: it is enough for a dependency to violate a normal form with respect to one candidate key for the relation not to be in that normal form. It does not have to violate it with respect to all of them. That is why the correct working order is: find all the candidate keys first, and only then evaluate the normal forms.

Second point: REPROGRAPHICS is in 2NF automatically, without checking anything, because its only candidate key has a single attribute. A one-attribute key has no non-empty proper subsets, so there can be no partial dependencies. Every relation with a single, simple candidate key is in 2NF by construction. Checking it by hand is wasted time; recognizing it is a legitimate shortcut.

Third point: row 5 is a reminder that a dependency whose right-hand side is a prime attribute never violates 2NF or 3NF, because both forms speak explicitly of non-prime attributes. It could violate BCNF, which does not make that distinction.

Difficulty: Intermediate

Task. The cultural activities department works with this table, exported from a spreadsheet. Each row is a member's registration for an event.

Business rules: each event has a single main speaker, is held in a single room and on a single date; each room belongs to a branch and has a capacity; each member has a name and a unique email; each speaker has a unique email.

You are asked for:

• (a) The candidate key and the functional dependencies.
• (b) Which normal form it is in and why, pointing at the guilty dependency.
• (c) To predict a specific modification anomaly and a deletion one, naming the exact row and datum of the table above.

Solution

(a) Candidate key: {event_id, member_id}. A row is "this member, at this event", and nothing shorter identifies a row.

Prime attributes: event_id, member_id, member_email (because {event_id, member_email} is also a candidate key, by h6). All the rest are non-prime.

(b) The table is in 1NF and not in 2NF.

The guilty dependencies are h2 and h5:

• event_id → event_title is partial: event_id is a proper subset of the key {event_id, member_id} and event_title is non-prime. The same goes for event_date, room and speaker.
• member_id → member_name is partial for the same reason.

Since it does not reach 2NF, there is no point asking about 3NF yet. But it is worth noting that, once 2NF is resolved, two transitive dependencies would still be pending: h3 (event_id → room → room_capacity, branch) and h4 (event_id → speaker → speaker_email).

(c) Specific anomalies.

Modification anomaly. Rosa Calduch changes her email to [email protected]. Her email appears in three rows: the three of event 101 (with members 14, 11 and 13) and the one of event 105 (member 15) — four rows in total. All four have to be updated. If the operator filters by event_id = 101 and updates only those three, the result is a table where the same speaker has two different emails: [email protected] at event 101 and [email protected] at event 105. The table becomes internally contradictory and there is no database constraint that prevents it, because speaker_email is not the key of anything.

Deletion anomaly. Iván Pereda (member 15) cancels his registration for event 105 and the last row is deleted. With it disappears all the information about event 105: that it was called "Book club: Norwegian Wood", that it took place on July 16, 2026, that it was held in the East Room of the East branch, that the room has a capacity of 50 and that Rosa Calduch moderated it. The event existed, but the database no longer knows so. And as a bonus, if that was the only row where the East Room appeared, we also lose that its capacity is 50 and that it belongs to the East branch.

Expected result

Explanation. The value of the exercise is in part (c), and that is why the task demands naming the exact row and datum. Saying "there is redundancy" or "there may be inconsistencies" is not a diagnosis: it is a description of the smell. A diagnosis is "changing Rosa Calduch's email forces you to update four rows, and one of them being left unupdated is enough for the table to assert two incompatible things".

Notice too that the insertion anomaly shows up all by itself when you try to register something that does not have children yet. Event 107 is scheduled for October and has no registrations yet: in this table it is inexpressible, except by putting in a row with a null member_id, which would break the primary key. That impossibility is the clearest sign that two different entities have been put into the same table.

A modeling nuance worth seeing: we have assumed "a single main speaker per event". If BiblioRed allowed several speakers per event —and in fact it does: event 104 has two—, h2 would stop being true and the table would not even be cleanly in 1NF, because we would have a disguised repeating group. That case is exactly the one in exercise 9.

Difficulty: Intermediate

Task. The collections department maintains this table for its monthly report:

branch_id is the branch the member is registered at.

You are asked for: the candidate key, the dependencies, the normal form and the guilty dependency, and one specific anomaly of each kind with the exact row and datum.

Hint. The key is a single attribute. That automatically rules out one kind of violation and forces you to look for the other.

Solution

Candidate key: {fine_id}.

Normal form: 2NF, not 3NF.

It is in 2NF automatically: the candidate key is a single attribute, so there can be no partial dependencies.

It is not in 3NF. There are two transitive chains, and both are guilty:

• fine_id → member_id → member_name (and → branch_id), with member_id non-prime.
• fine_id → member_id → branch_id → branch_name, branch_phone, with branch_id non-prime. This one is a two-hop transitivity, and it is still transitivity.

Specific anomalies.

Modification. The Central branch changes its phone number to 935550099. The value 935550001 appears in three rows: fines 1, 4 and 7. All three have to be updated. If an UPDATE with WHERE member_id = 14 updates only fines 1 and 4, fine 7 (Sonia Mestre's, also from Central) still says 935550001 and the table asserts that the Central branch has two phone numbers. With 12,000 members and four branches, the number of rows to touch for a single phone change runs into the thousands.

Insertion. BiblioRed opens a fifth branch, "West", phone 935550005. There is no way whatsoever of recording it in this table until a member registered at West receives a fine. The branch's data exists only as an accompaniment to a fine.

Deletion. Fine 5 (Nuria Bastos, damage, €6.50) is voided and the row is deleted. With it disappears the fact that a South branch exists, that its phone is 935550003, that member 16 is called Nuria Bastos and that she is registered at South. It is the only row in the table where that branch appears.

Expected result

Decomposition to 3NF (the result only; the complete procedure is in exercise 7):

BRANCHES(branch_id, branch_name, branch_phone) MEMBERS(member_id, member_name, branch_id) FINES(fine_id, member_id, reason, amount, status)

Explanation. This is the textbook case of a 3NF violation, and it is worth seeing why the correct diagnosis is 2NF and not "1NF" or "3NF":

• 1NF is not the diagnosis because all the attributes are atomic: there are no lists, no repeating groups, no cells with several values.
• It does not reach 3NF because there are non-prime attributes that depend on other non-prime attributes.
• The shortcut from the hint: with a simple candidate key, 2NF is guaranteed and all the attention must go to looking for key → X → Y chains.

A detail that gets overlooked: chained transitivity. branch_phone is two hops from the key (fine_id → member_id → branch_id → branch_phone). It is still a 3NF violation, and the correct decomposition is not to put the branch inside the fines table: it is to recognize that there are three hidden entities —fine, member and branch— and to pull them out in cascade. A frequent mistake is to decompose only one level and end up with MEMBERS(member_id, member_name, branch_id, branch_name, branch_phone), which is still not in 3NF.

Difficulty: Intermediate

Task. A consultancy firm in Vallmar assigns advisors to clients by specialty:

Business rules:

• Each advisor works in a single specialty.
• For each client and each specialty there is exactly one advisor assigned.
• A client can have several advisors (one per specialty) and an advisor can handle several clients.

You are asked for: the dependencies, all the candidate keys, the prime attributes, the exact normal form and the guilty dependency, and the anomalies.

Hint. Check first whether any attribute is non-prime. The answer changes the whole analysis.

Solution

Candidate keys. We compute closures:

The two two-attribute superkeys are minimal (none of their proper subsets is one). Candidate keys: {client, specialty} and {client, advisor}.

Prime attributes: client, specialty, advisor. All three. There is no non-prime attribute.

Normal form: 3NF, but not BCNF.

• 2NF: it holds vacuously. The definitions of 2NF and 3NF speak of non-prime attributes, and here there are none. With no non-prime attributes there can be no partial or transitive dependencies of non-prime attributes.
• 3NF: it holds for the same reason.
• BCNF: it does not hold. The definition of BCNF does not distinguish prime from non-prime: it demands that the determinant of every non-trivial dependency be a superkey. The guilty dependency is m2: advisor → specialty, because {advisor}⁺ = {advisor, specialty} does not contain client: advisor is not a superkey.

Anomalies.

Insertion. The firm hires Lidia Serna, a specialist in corporate law. There is no way of recording it: the primary key requires a client, and Lidia does not have one yet. The fact "Lidia Serna is a corporate specialist" is inexpressible.

Deletion. Vilamar Optics terminates the contract and its two rows are deleted. With the row (Vilamar Optics, tax, Sergi Prat) disappears the only record that Sergi Prat is a tax advisor: it is his only client in the table.

Modification. Rita Bonet retrains and moves from tax to accounting. Her specialty appears in two rows (Solé Bakery and Ferrer Garage). If only one is updated, the table asserts that Rita Bonet is tax for one client and accounting for another, violating the first business rule.

Expected result

Explanation. This exercise exists because it is the counterexample proving that 3NF is not enough. Everybody reaches 3NF and considers the job done; this table is in textbook 3NF and still has the three classic anomalies.

The alarm signal to learn to recognize: a dependency whose left-hand side is a single attribute that is also part of a key, but which on its own is not a key. Here, advisor is prime (it is in {client, advisor}) but it is not a superkey. That configuration —a prime determinant that is not a superkey— is exactly the gap through which 3NF lets redundancy pass.

The other lesson: overlapping candidate keys. The two keys share the attribute client. Whenever two candidate keys overlap, it is worth checking BCNF explicitly, because that is the situation in which 3NF and BCNF diverge. If the candidate keys are disjoint or there is only one, 3NF and BCNF coincide in practice almost always.

The decomposition of this table —and the serious problem it brings— is exercise 8.

Difficulty: Advanced

Task. BiblioRed manages its supplier orders with this table, exactly as the person who retired left it:

Catalog data: 9788401337208 is "The Pillars of the Earth" by Ken Follett; 9788401339097, "The Map of Time" by Félix J. Palma; 9788483835609, "Norwegian Wood" by Haruki Murakami.

Take this table from 1NF to 3NF, showing the state of the data after each normal form and writing the SQL of the decomposition.

Solution

Step 0 → 1NF: atomize.

The table is not even in 1NF: supplier_phones is a comma-separated list and lines is a complete repeating group inside a cell. 1NF demands atomic values and no repeating groups.

Result after 1NF, with the phone numbers in their own table and one row per order line:

ORDERS_1NF(order_id, order_date, supplier_id, supplier_name, supplier_tax_id, supplier_city, isbn, title, author, units, unit_price)

And SUPPLIER_PHONES(supplier_id, phone) with four rows: (P1, 973551111), (P1, 610222333), (P2, 935559999).

Analysis. Candidate key of ORDERS_1NF: {order_id, isbn}. Dependencies:

Step 1NF → 2NF: remove the partial dependencies.

Guilty: n2 and n3. Both have as determinant a proper subset of the key and as dependents non-prime attributes.

State of the data after 2NF:

orders_2nf (3 rows)

books_2nf (3 rows)

lines_2nf (5 rows)

Step 2NF → 3NF: remove the transitive dependencies.

orders_2nf still has order_id → supplier_id → supplier_name, supplier_tax_id, supplier_city. It is transitive: supplier_id is non-prime in that relation.

Final 3NF schema, with keys and constraints:

Expected result

Check that the decomposition is lossless: rebuilding the original must give exactly the five rows of ORDERS_1NF.

Explanation. Four things this exercise teaches that you do not see in toy examples:

1. unit_price stays in order_lines, and that is correct. The temptation is to move it out to the book catalog, "because the price belongs to the book". It does not: look at the data. "The Pillars of the Earth" was bought at €21.90 in order 5001 and at €22.50 in 5002, from different suppliers. And "Norwegian Wood" at €12.95 and at €13.20. The price depends on the whole line —{order_id, isbn}—, not on the ISBN. It is a full dependency, and therefore it stays where it is. Moving it out would have been a loss of information, not a normalization.

2. SELECT DISTINCT is the heart of the migration. Each CREATE TABLE ... AS SELECT DISTINCT projects the columns of the new relation and deduplicates. It is exactly the projection of relational algebra. If after the DISTINCT the new table has more rows than expected, that is the sign that the data contradicts the functional dependency: for instance, if suppliers came out with three rows, it would mean that in some order "P1" appears with a different city, and the data would have to be cleaned before putting on the primary key.

3. The phone numbers come out at 1NF, not at 3NF. A multivalued attribute is not a functional dependency problem: it is an atomicity problem. It is solved in the first step, by creating a table whose primary key is (supplier_id, phone).

4. supplier_tax_id → supplier_id (n5) does not generate a new table. It is a dependency between two attributes that already live together in suppliers, and it means that the tax ID is an alternate key. It is materialized with UNIQUE, not with a decomposition. Confusing "there is a dependency" with "we must decompose" is a frequent mistake: you only decompose when the dependency violates the normal form you are aiming for.

About SQLite: CREATE TABLE ... AS SELECT works the same, but it does not allow declaring keys or constraints in the same statement, and ALTER TABLE ADD CONSTRAINT does not exist. There the pattern is to create the final tables with their complete DDL and then INSERT INTO ... SELECT DISTINCT ....

Difficulty: Advanced

Task. Go back to the table of exercise 6, ADVISORY(client, specialty, advisor) with m1: {client, specialty} → advisor and m2: advisor → specialty.

• (a) Decompose to BCNF, removing the guilty dependency.
• (b) Prove that the decomposition is lossless.
• (c) Prove that it does not preserve the dependencies and identify which one is lost.
• (d) Show with specific data what can slip into the decomposed schema that the original prevented.
• (e) Decide what to do and justify the decision.

Solution

(a) Decomposition. The standard procedure for reaching BCNF: given the guilty dependency X → Y (here advisor → specialty), you decompose into X ∪ Y and into R − Y.

Both are in BCNF: in R1 the only dependency is advisor → specialty and advisor is the key; in R2 the only dependency is the trivial one and the key is {client, advisor}, the whole relation.

With the data of exercise 6:

R1 (4 rows)

R2 (6 rows)

(b) Lossless. The criterion: a binary decomposition of R into R1 and R2 is lossless if the common attributes functionally determine all the attributes of at least one of the two.

{advisor} is a key of R1, so the decomposition is lossless. Check with data: the JOIN of R1 and R2 on advisor returns exactly the 6 original rows, not one more.

(c) It does not preserve the dependencies. The projection of F onto the two relations gives:

The dependency m1: {client, specialty} → advisor has been lost. It does not follow from F₁ ∪ F₂: the closure of {client, specialty} under F₁ ∪ F₂ is {client, specialty} and does not contain advisor.

And what is worse in practice: it cannot be checked by looking at a single table. R1 knows nothing about clients; R2 knows nothing about specialties. Verifying it requires joining the two.

(d) What slips in now. We insert into R2 a row that is perfectly legal for the decomposed schema:

No constraint complains: R2 only requires that the pair not be repeated, and it is not repeated. But on rebuilding:

Solé Bakery has two tax advisors. The business rule "for each client and specialty there is exactly one advisor" is broken, and the original 3NF schema prevented it with its primary key. BCNF has removed the redundancy at the cost of losing a guarantee.

(e) The decision. There are three defensible ways out and one that is not:

Option 3 deserves to be seen written out, because it is the pattern that solves most of these cases in practice:

Expected result

Explanation. This is the exercise that separates whoever has memorized the normal forms from whoever understands them. The underlying theorem is blunt:

Every relation admits a lossless, dependency-preserving decomposition to 3NF. Not every relation admits a dependency-preserving decomposition to BCNF.

BCNF is stricter and sometimes it is too strict: it removes the redundancy at the price of a business rule no longer being verifiable inside a single table. When that happens, normalization stops being a technical decision and becomes a business decision: which hurts more, the redundancy or the risk of inconsistent data?

In practice, the answer is almost always that the risk of inconsistency hurts more, because redundancy can be watched with an audit query and inconsistency, once it has happened, is very expensive to clean up. That is why option 3 —replicating the attribute and shielding it with a composite foreign key, the same trick that closed the hierarchy of the auto repair shop in 07-02— is the compromise that wins most often: you pay one repeated column, but the engine guarantees that it never gets out of step.

Option 1 (staying in 3NF) is perfectly respectable when the volume is small and the table is rarely queried. What is never respectable is option 4: decomposing, losing the dependency and not telling anybody.

Difficulty: Advanced

Task. To prepare the material for events, BiblioRed used to keep this table:

EVENT_RESOURCES(event_id, speaker, material)

where the speakers taking part in an event and the materials exhibited at it are recorded. The speakers and the materials are independent of each other: which materials are exhibited does not depend on which speaker is talking, and vice versa.

Data for event 104 ("Book launch: The Wishing Box"), with two speakers and two materials:

You are asked to:

• (a) Determine the candidate key and check whether it is in BCNF.
• (b) Write the multivalued dependencies.
• (c) Decompose to 4NF and show the tables with data.
• (d) Quantify the saving if the event had 3 speakers and 4 materials.

Solution

(a) The candidate key is {event_id, speaker, material}: the whole relation. There is no non-trivial functional dependency —neither event_id → speaker (there are two), nor speaker → material (each speaker appears with both materials)—, so the only way of identifying a row is with all three attributes.

Since every attribute is prime and there are no non-trivial functional dependencies, the table is in BCNF. And yet the redundancy is glaring.

(b) Multivalued dependencies. A multivalued dependency X ↠ Y says that the set of values of Y associated with a value of X is independent of the remaining attributes. Here:

Both are non-trivial (speaker does not contain event_id, and event_id ∪ speaker ≠ R) and event_id is not a superkey. Therefore the table is not in 4NF.

The empirical sign of a multivalued dependency: the table contains the Cartesian product of two independent sets. 2 speakers × 2 materials = 4 rows, and all 4 have to be there. If the row (104, Rosa Calduch, Vallmar Science 42) were missing, the table would be asserting something false: that Rosa Calduch has something to do with which material is exhibited.

(c) Decomposition to 4NF.

event_speakers (2 rows)

event_materials (2 rows)

The decomposition is lossless by Fagin's theorem: if X ↠ Y holds in R, then R decomposes losslessly into R[X ∪ Y] and R[X ∪ (R − Y)]. The JOIN on event_id rebuilds exactly the 4 original rows.

(d) The saving. With 3 speakers and 4 materials:

And with 5 speakers and 20 materials: 100 rows against 25. The growth is multiplicative in the unnormalized schema and additive in the normalized one.

Expected result

Explanation. 4NF is the first normal form that cannot be diagnosed by looking at functional dependencies, and that is why it slips past so often. The table is in BCNF —the functional analysis says it is perfect— and it still has a brutal update anomaly: adding a third speaker to event 104 forces you to insert two rows, one per material, and forgetting one leaves the table in a state asserting a relationship that does not exist.

The practical criterion for detecting a multivalued dependency, more useful than the formal definition:

If, on adding a new value of Y, you have to insert one row for each existing value of Z, and Y and Z have nothing to do with each other, there is a multivalued dependency.

When there is NO multivalued dependency, which is the symmetric mistake. If in BiblioRed each speaker presented their own material —Delia Marchetti her novel and Rosa Calduch the magazine—, then speaker and material would be related, there would be no Cartesian product, the table would be in 4NF and decomposing it would lose information: on rebuilding with a JOIN, false pairs would appear. The independence of the two sets is a business rule, not something deduced from the data, and it is the first thing to confirm before decomposing.

Note: in BiblioRed's real schema this decomposition is already done —participations and events_materials are exactly the two tables of part (c)—. The exercise shows where that design decision comes from.

Difficulty: Advanced

Task. BiblioRed's public catalog shows, for each material, how many available copies there are in each branch. It is the most-executed query in the system: some 400 times a minute at peak hours. With 40,000 copies, the current query

takes 380 ms even with an index, because the catalog runs it once for each material on the results page (up to 20 materials) and that is almost 8 seconds per page.

The status changes of a copy, on the other hand, are around 3,000 a day: loans, returns, withdrawals.

You are asked to:

• (a) Propose what to denormalize and why that option and not another.
• (b) Write the SQL of the denormalized structure.
• (c) Explain how consistency is maintained.
• (d) List what is accepted in exchange.

Solution

(a) What to denormalize: a summary table.

Let us compare the three options from the catalog in 05-04:

(b) The structure.

The catalog query becomes:

One primary-key access: less than 1 ms, against the 380 ms of the GROUP BY.

(c) How consistency is maintained: a trigger on copies.

And a nightly reconciliation query comparing the summary with the source of truth and reporting any mismatch:

(d) What is accepted in exchange.

Expected result

Explanation. Denormalization is an engineering decision, not a design failure, and that is why it has to be justified with numbers. The three numbers that justify this one are: 400 reads per minute, 3,000 writes a day and 380 ms per query. Without those three, the proposal would be an opinion.

The rule governing the decision: denormalize when the read/write ratio is very high and the cost of the query is structural (a GROUP BY over many rows is not fixed with a better index). Here the ratio is about 200 reads for every write.

Three design details worth not losing:

• ON CONFLICT ... DO UPDATE (PostgreSQL's "upsert") saves you having to check whether the row exists. It is essential: the first available copy of a material at a branch has no previous row.
• The trigger is declared AFTER ... OF status, branch_id, material_id. Restricting the columns prevents an UPDATE of cover_url from firing useless work.
• CHECK (n_available >= 0) is a canary. If the counter tries to go below zero, there is a bug in the logic and it is better for the transaction to fail loudly than to leave the summary lying in silence.

And the final warning: the availability table must never be used to take transactional decisions. To know whether a specific copy can be loaned out you go to copies with a lock, not to the summary. Confusing a derived datum with the source of truth is the most common way for a denormalization to end up lending the same copy twice.

1. Deducing the functional dependencies from the data. Data can only refute a dependency, never prove it. The fact that in six rows each advisor has a single specialty does not prove advisor → specialty; the business rule proves it. Always ask "can this change?" before writing an arrow.

2. Stopping at the first candidate key. It is the mistake of exercise 2. Before evaluating normal forms you have to have all the candidate keys, because a single one of them is enough for the relation to fail a normal form.

3. Confusing superkey with candidate key. A superkey determines every attribute; a candidate key is a minimal superkey. {event, room} is a superkey and is not a candidate key.

4. Computing the closure in a single pass. You have to iterate until the set stops growing. Many dependencies only fire after another one has added its attribute.

5. Skipping 2NF when the key is simple. It is not a mistake, it is a legitimate shortcut: with a single one-attribute candidate key, 2NF always holds. What is a mistake is assuming the same for 3NF.

6. Decomposing every dependency that shows up. supplier_tax_id → supplier_id does not require a new table: it is an alternate key and it is solved with UNIQUE. You only decompose what violates the normal form you are aiming for.

7. Moving out of the detail table an attribute that depends on the whole key. The unit_price of exercise 7 depends on {order_id, isbn}, not on the ISBN. Moving it out to the catalog destroys information. Always check whether the value changes between rows with the same candidate determinant.

8. Decomposing to BCNF without checking dependency preservation. It is exercise 8. Before applying the algorithm, project F onto the resulting relations and check what you have lost.

9. Confusing a functional dependency with a multivalued one. If the two sets are independent (an obligatory Cartesian product), it is multivalued and you have to decompose. If they are related, decomposing invents rows on rebuilding.

10. Denormalizing with no numbers and no reconciliation. A denormalization with no prior measurement is a superstition, and one with no reconciliation process is a time bomb.

A method tip. Always work in this order and do not change it: dependencies → closures → all the candidate keys → prime and non-prime → normal forms in order. Skipping a step makes the diagnosis come out wrong half the time, and the worst of it is that it comes out wrong in a plausible way.

A checking tip. After each decomposition, count the rows of the new tables and rebuild the original with a JOIN. If on rebuilding you get more rows than the originals, the decomposition is lossy (you have generated spurious tuples). If you get fewer, you have lost data. Only if you get exactly the same ones is it right.

No hints. Write the complete analysis for each one.

Go back to OCCUPANCY(room, day, hour, event, capacity, branch) from exercise 2, with:

Take it to BCNF, showing the decomposition step by step, checking that it is lossless and saying whether it preserves the dependencies. Write the final schema with its keys and its constraints.

A delivery company in Vallmar uses this table:

Rules: each route is done by one driver with one vehicle on one date; within a route the stops are numbered; each client has a single address; each vehicle has a maximum load. Determine the candidate keys, the exact normal form, the guilty dependency and one anomaly of each kind naming the row and the datum.

BiblioRed's management wants a dashboard that is opened 30 times a day and shows, for each of the last 24 months: total loans, distinct members who borrowed something, fines issued and amount collected. The current query scans loans, fines and payments in full —some 900,000 records— and takes 4 seconds. The data of closed months never changes. Propose the denormalization, justify it, say how it is maintained and what is accepted in exchange. Compare explicitly with the solution of exercise 10 and explain why the choice here is different.

Solution A

Initial state. Candidate keys {event} and {room, day, hour}; prime: event, room, day, hour; non-prime: capacity, branch. It is only in 1NF, because g1 is partial with respect to {room, day, hour}.

Step to 2NF/3NF. The guilty determinant is room. It is pulled out:

ROOMS is in BCNF: its only dependency is room → capacity, branch and room is the key.

SCHEDULE keeps g2 and g3. Its two candidate keys are still {room, day, hour} and {event}; all its attributes are prime, so it is in 3NF. And in BCNF? The two remaining dependencies are g2 and g3, and in both the determinant is a candidate key, hence a superkey. SCHEDULE is in BCNF.

Lossless. ROOMS ∩ SCHEDULE = {room}, and room → capacity, branch makes {room} the key of ROOMS. It meets the criterion.

Dependency preservation. g1 lives entirely in ROOMS; g2 and g3 live entirely in SCHEDULE. F₁ ∪ F₂ = F: they are all preserved. This case is the usual one and contrasts with exercise 8: here BCNF comes for free.

Final schema:

The important thing about the final schema: both candidate keys end up declared, one as PRIMARY KEY and the other as UNIQUE. Declaring only one of the two would leave g2 without a guarantee and would allow two events in the same room at the same time.

Solution B

Candidate keys. A row is a stop within a route: {route_id, stop_number}. Check: {route_id, stop_number}⁺ reaches client, and from there address; route_id gives route_date, driver_id, vehicle_plate; and from there driver_name and vehicle_load_kg. Complete closure. Nothing shorter achieves it.

Dependencies:

Normal form: 1NF, not 2NF. The guilty dependency is d2: route_id is a proper subset of the key and route_date, driver_id and vehicle_plate are non-prime. d3, d4 and d5 are transitive as well and would be resolved in the step to 3NF.

Anomalies with the exact row and datum:

• Modification. Vehicle 8820 BNM gets an overhaul and its maximum load goes to 1,300 kg. The value 1400 appears in two rows: the one of R-101 and the one of R-102. If only the R-101 one is updated, the table asserts that the same vehicle has two different maximum loads depending on the day.
• Insertion. A new driver is hired, C9 / Berta Quintana. They cannot be recorded until they are assigned a route with at least one stop. The same goes for a new client: their address does not exist until they have a delivery.
• Deletion. The stop at Vilamar Optics on route R-101 is cancelled and that row is deleted. Since it is the only row of R-101, the whole route disappears: that it existed on July 6, that driver C7 (Aitor Lemus) did it and that it used vehicle 8820 BNM. And with it we also lose that Vilamar Optics is at Plaza Mayor 3, because it is its only appearance... unless another one remained, which is not the case here.

Decomposition to 3NF: clients(client, address), drivers(driver_id, driver_name), vehicles(vehicle_plate, vehicle_load_kg), routes(route_id, route_date, driver_id, vehicle_plate), stops(route_id, stop_number, client, packages).

Solution C

Proposal: a summary table of closed months, populated by a monthly process.

How it is maintained. A scheduled process on the 1st of each month inserts the row of the month just closed with a single pass over the source tables. The current month is not stored: it is computed on the fly, because it is the only one that changes. The dashboard joins the 23 summary rows with the live computation of the current month by means of a UNION ALL.

Why the choice is different from the one in exercise 10. The decisive difference is the volatility of the datum:

A trigger on loans to maintain the monthly summary would be disproportionate: it would add work to every loan in order to serve 30 reads a day. And a materialized view, which in exercise 10 was a bad idea because it recomputes everything, is perfectly reasonable here: it is refreshed once a month and the full recomputation bothers nobody.

What is accepted in exchange: the current month is still computed on the fly (some 200 ms, acceptable); you have to watch that no retroactive correction touches an already closed month, and if there is one, recompute that row explicitly; and the dashboard needs the UNION ALL, which is a complication of the query that did not exist before.

You have gone through normalization end to end with ten exercises: you have written sets of functional dependencies from business rules, computed closures step by step, found all the candidate keys of relations with overlapping keys, classified attributes as prime and non-prime, and seen that one and the same dependency is called partial or transitive depending on the key you take as the reference.

In the diagnosis block you have put a first and last name on the anomalies: not "there is redundancy", but "Rosa Calduch's email is in four rows and one of them being left unupdated is enough". That precision is what turns an analysis into an argument you can use to convince somebody that a table has to be redone.

And in the advanced block you have done the complete job: the migration from 1NF to 3NF with its decomposition SQL and its rebuild check; the awkward case in which reaching BCNF costs you a dependency and you have to choose between redundancy and guarantee —with the composite foreign key pattern as the best compromise—; the fourth normal form and multivalued dependencies, which no amount of functional analysis detects; and denormalization reasoned with numbers, with its maintenance mechanism and its mandatory reconciliation.

If you take away one single idea, let it be this: normalizing is not applying rules, it is making each fact live in a single place. The normal forms are the formal way of checking whether you have managed it, and denormalization is the conscious decision to break that rule at a specific point, with a measurement in front of you and a monitoring process behind you.

The module's last lesson, 07-04, Advanced Queries and Transactions Exercises, is the most demanding of the four and the one that most resembles real production work. You will go back to BiblioRed to write window functions —the most-loaned material of each branch, the month-against-month comparison with LAG, the running totals—, a recursive CTE, queries over jsonb in the event survey responses, and a manual pivot. Then come the transactions: the complete loan one with its error handling, SAVEPOINT in a batch process, and five exercises with two psql sessions in parallel where you will reproduce a lost update, see the difference between READ COMMITTED and REPEATABLE READ, cause a deadlock on purpose, implement the optimistic locking of the last seat and consume a queue with SKIP LOCKED. And to close, four exercises on indexes and execution plans. Have both terminals ready.