The previous lesson built the instruments: functional dependencies, their kinds, Armstrong's axioms, the closure X⁺, candidate keys and the distinction between prime and non-prime attributes. All of that existed for a single purpose, and it is the purpose of this lesson: to be able to state precisely what a well-designed table is.

A normal form is a condition that a relation either satisfies or does not. It is not advice or a best practice: it is a verifiable property, like saying that a number is even. Each normal form forbids one specific kind of badly placed dependency, and the prohibitions stack up: each level requires everything the previous one required, plus something more.

This lesson is the catalog. We will go through the six normal forms that are actually used —first, second, third, Boyce-Codd, fourth and fifth— plus a seventh that is only of theoretical interest. For each one we will see four things: the formal definition, a minimal BiblioRed example that violates it with concrete data, the fix, and the reason it matters. What we will not do here is apply a complete methodology to a real table: that is lesson 05-03. Here the point is to have the whole catalog before starting to use it, in the same way that you learn the tools before the building work.

1. What it means for a relation to "be in" a normal form
2. Why normal forms are cumulative
3. First normal form (1NF): atomic values
4. What "atomic" exactly means
5. Second normal form (2NF): no partial dependencies
6. Third normal form (3NF): no transitive dependencies
7. Boyce-Codd normal form (BCNF): every determinant is a superkey
8. When decomposing to BCNF does not preserve dependencies
9. Fourth normal form (4NF): multivalued dependencies
10. Fifth normal form (5NF): join dependencies
11. Domain-key normal form (DKNF): the theoretical limit
12. Summary table of all the normal forms
13. The industry's real criterion

Let's start with the basics, because the language used here confuses a lot of people.

When somebody says "the members table is in third normal form", they are asserting something about the structure of the table and its functional dependencies, not about the data it holds today. It is a property of the design. If tomorrow you insert a thousand more rows, the table is still in third normal form; if you change its structure or discover a new business rule, it may stop being so.

Three important clarifications:

Normal forms are predicated of relations, not of databases. There is no such single statement as "the BiblioRed database is in 3NF": each table is evaluated separately. What is said, as shorthand, is that a schema is in 3NF when all its tables are.

You need the functional dependencies in order to answer. Looking only at the CREATE TABLE you cannot tell which normal form a table is in, because normal forms talk about dependencies, and dependencies come from the business. Two tables with identical columns can be one in 3NF and the other not, if the rules governing them are different. This is surprising, but it is the direct consequence of what we saw in section 6 of 05-01.

The adjective "normal" does not mean "usual". It comes from mathematical vocabulary, where a "normal form" is a canonical form an object is brought into so that it can be compared with others. It has nothing to do with statistical normality.

The normal forms are ordered and each one includes the previous one: to be in 3NF you first have to be in 2NF, and to be in 2NF you first have to be in 1NF. This is not an arbitrary convention: it is in the definition of each form, which literally starts by saying "a relation is in nNF if it is in (n−1)NF and, in addition…".

Read it as a ladder of demands: the higher up you go, the more restrictive the condition and the fewer tables satisfy it. The two highlighted steps are the usual practical target, and in section 13 we will explain why.

The logical consequence of the stacking is useful in both directions:

• If a table is not in 2NF, it is not in 3NF or in anything above it either. One failure at the bottom is enough to rule everything out.
• If a table is in BCNF, it is automatically in 3NF, 2NF and 1NF. There is no need to check them.

That is why the checks are done bottom-up, and as soon as one fails it is fixed before climbing any further. It is exactly the order the process in lesson 05-03 will follow.

A note on naming before we go on: in Spanish-language literature and in some translations you will see 1FN, 2FN, 3FN and FNBC (forma normal, forma normal de Boyce-Codd). They are exactly the same thing as 1NF, 2NF, 3NF and BCNF. We will use the English names.

Definition. A relation is in first normal form if the value of every attribute in every row is atomic: a single indivisible value from the attribute's domain. In particular, there are no lists inside a cell, no repeated groups of columns, and no duplicate rows.

It is the most basic normal form and also the most misunderstood. Let's take a BiblioRed example we already know by another route.

Lesson 04-01 called out the "comma-separated list" anti-pattern and 04-03 solved it with transformation rule 3. Now we can say formally what was wrong with it: it violated first normal form.

This is what the members table looked like in BiblioRed's original sheet:

members_sheet — not in 1NF

The value 600111222, 938880011 is not a phone number: it is two phone numbers stuffed into a text string. The DBMS sees a string and treats it as such, with very concrete consequences:

It does not find them, because the stored value is '600111222, 938880011', which is not equal to '938880011'. The usual way out is LIKE, and it is worse than the problem:

That works by luck and fails as soon as one number contains another as a substring. Besides: you cannot put a format constraint on an individual phone number, you cannot count how many phone numbers there are without slicing strings, you cannot index it, and you cannot stop the same number being repeated.

Fix: rule 3 from module 4. A new table with the foreign key and the value, and the primary key made up of both:

member_phones — in 1NF

Now every cell holds one value, equality searches work, the format CHECK can be applied number by number, and the composite primary key prevents repeating a phone number for the same member. It is the table that has been in the BiblioRed schema since lesson 04-03; what is new is knowing that its formal justification is called first normal form.

Exactly the same case, with exactly the same remedy, is that of a DVD's subtitle languages. Storing subtitles = 'es,ca,en' in materials_dvd is a 1NF violation, and the solution is dvd_subtitles(material_id, language), with language over a validated domain. Once again: the table already existed; now we know its formal name.

The second way of breaking 1NF is subtler because every cell does hold a single value. The problem is the column structure:

members_sheet_v2 — also not in 1NF

It is the "numbered columns" anti-pattern from 04-01. Every cell is atomic, yes, but the group phone_1, phone_2, phone_3 is a multivalued attribute disguised as three columns, and the three columns are not three different attributes: they are three occurrences of the same one. The symptoms give it away:

And if tomorrow a member has four phone numbers, you need an ALTER TABLE —changing the schema because of a change in the data— and to rewrite every query. The fix is the same member_phones table as before.

The classic definition of a relation (the one from 02-01) says that a relation is a set of tuples, and a set has no repeated elements. So, in strict theory, two identical rows violate 1NF.

In practice, SQL allows tables with no primary key and with duplicate rows. The operational recommendation is unambiguous and we have been following it since module 2: every table must have a declared primary key. With that, duplicate rows are impossible and this aspect of 1NF is guaranteed by the DBMS.

Here we have to be honest, because this is where 1NF generates the most pointless arguments.

"Atomic" is not a property of the data: it is a property of the relationship between the data and the use made of it. A value is atomic if the application never needs to look inside it to do its job.

Look at these four BiblioRed cases:

The JSONB case deserves a paragraph, because it is where 1970's 1NF meets today's databases. Purists would say that a JSONB column with several values inside violates 1NF. In practice it is accepted when two conditions hold: the content takes part in no relationship with other tables (there are no foreign keys pointing inside the JSON) and there are no business rules depending on its individual fields. In BiblioRed, event_reports.survey_responses satisfies both: they are free-form answers to a questionnaire that are only ever read whole to produce a report. If tomorrow it became necessary to aggregate by question, compute per-item averages or add constraints, it would stop being justified and it would have to be pulled out into a survey_responses(event_id, member_id, question, value) table.

We already had this discussion in different vocabulary in 03-04, when talking about jsonb in PostgreSQL. The rule in short:

Store a composite value only if you are always going to use it whole. The moment you need to search, filter, group or constrain by one of its parts, that part has to be a column or a row.

Definition. A relation is in second normal form if it is in 1NF and, in addition, every non-prime attribute depends functionally on the complete candidate key, and not on part of it. Put another way: there are no partial dependencies of non-prime attributes on any candidate key.

Let's recall the vocabulary from 05-01: a non-prime attribute is one that is not part of any candidate key, and a dependency is partial when a proper subset of the key already determines the attribute.

An enormous shortcut follows from the definition:

If every candidate key is a single attribute, the relation is automatically in 2NF. There are no "parts" of a single-attribute key, so there can be no partial dependencies.

That is why 2NF is only ever a problem in tables with a composite key: junction tables, weak entities, and flat tables inherited from spreadsheets.

BiblioRed once considered letting a member take several copies away in a single front-desk operation, with a "loan" grouping several lines. Somebody proposed this table:

loan_lines — in 1NF but NOT in 2NF

Candidate key: {loan_id, copy_id} (a copy appears only once within a loan).

The dependencies, according to the business rules:

There are two partial dependencies, one for each half of the key. And their consequences are visible in the table above: 2026-04-09 and 14 are written three times because loan 5001 has three lines; North is written three times for copy 3081 because that copy appears in two loans.

The corresponding anomalies are the usual ones:

Fix: each partial dependency is pulled out into its own table, with the part of the key that determines it as the primary key.

Result, with the same data:

loans

copies

loan_lines

Count the repetitions: the date 2026-04-09 appears once instead of three times. Copy 3081's branch appears once instead of twice. And now you really can register a copy nobody has asked for yet. Notice too that loan_lines has been left with just the key: it is a pure association table, and that is perfectly correct —it means that the only fact it contributes is "this copy was part of this loan".

Definition. A relation is in third normal form if it is in 2NF and, in addition, no non-prime attribute depends transitively on any candidate key. Equivalently: for every non-trivial dependency X → A that holds in the relation, either X is a superkey, or A is a prime attribute.

The second formulation is the one used for checking, because it is mechanical: you go through the dependencies one by one and ask each of them two questions.

The intuitive idea is the usual one: a non-prime attribute must not depend on another non-prime attribute. If it does, it means that this second attribute is really the key of another entity that has sneaked into the table.

This is the textbook 3NF violation, and BiblioRed serves it up ready-made.

branches_v0 — in 2NF but NOT in 3NF

Candidate key: {branch_id} (a single attribute, so 2NF is guaranteed).

Dependencies:

The second one comes from a real business rule: a postal code belongs to a single municipality. And it produces a transitive dependency branch_id → postal_code → city, with postal_code not a superkey (two branches share 08110) and city not prime.

Applying the mechanical formulation to postal_code → city:

• Is postal_code a superkey? {postal_code}⁺ = {postal_code, city}. It does not contain every attribute. No.
• Is city prime? The only candidate key is {branch_id}, and city is not in it. No.

Both answers are "no", so it violates 3NF.

And the anomaly is immediate:

Fix: pull the transitive dependency out into its own table, with the determinant as the primary key, and leave a foreign key in the original one.

postal_codes

branches

Now each postal code's municipality is written once. Changing it is a one-row UPDATE, and there is no physical way for 08110 to appear in two cities. It is the same branches table we have been using since module 2, now with its formal justification.

(Practical note: in a small system with four branches, pulling out a postal codes table may look excessive, and in fact BiblioRed's real schema keeps addr_city directly in branches. It is a defensible decision —a conscious denormalization on a four-row table that almost never changes— but it is worth making it knowing that it is a denormalization, not because you failed to spot the dependency. The whole of lesson 05-04 is about how to make that decision with judgment.)

Both forbid a non-prime attribute from depending on something other than the complete key. What changes is what it improperly depends on:

Third normal form leaves a loophole open. Its definition forgives a dependency X → A if A happens to be a prime attribute, even when X is not a superkey. Raymond Boyce and Edgar Codd proposed in 1974 to close that loophole, and the result is a far simpler definition:

Definition. A relation is in Boyce-Codd normal form if, for every non-trivial functional dependency X → Y that holds in it, X is a superkey.

No exceptions, no distinction between prime and non-prime. It is the cleanest definition of all the normal forms, and that is why many authors teach it before 3NF.

Side by side:

BCNF is strictly more demanding. Every relation in BCNF is in 3NF; the converse is not always true.

For the difference to show up, three conditions have to hold at once, which is why the case is uncommon: the relation must have several candidate keys, those keys must be composite, and they must overlap (share some attribute).

Let's build it in BiblioRed. Suppose —and this is a hypothesis for the example, not the real rule of the module 4 schema— that BiblioRed imposes two rules when assigning speakers to events:

• N1: within one event, each role is performed by a single person. There are no two moderators in the same talk.
• N2: each speaker on the register has a fixed role assigned by contract. Clara Ferrán always moderates; she never runs workshops.

assignments — in 3NF but NOT in BCNF

The dependencies that follow from N1 and N2:

Candidate keys. Let's compute closures:

• {event_id, role}⁺ = {event_id, role, speaker_id} = everything. Superkey. Minimal (neither event_id nor role is enough alone). Candidate key 1.
• {event_id, speaker_id}⁺: through h2, role comes in, and now we have everything. Superkey. Minimal. Candidate key 2.

Two candidate keys, composite, and overlapping: both contain event_id. All three conditions hold.

Prime attributes: event_id (in both), role (in the first), speaker_id (in the second). All three attributes are prime, and there is no non-prime one.

Is it in 3NF? 3NF requires that in every non-trivial dependency X → A, either X is a superkey or A is prime. Let's check:

• h1: X = {event_id, role} is a superkey. Satisfied.
• h2: X = {speaker_id} is not a superkey ({speaker_id}⁺ = {speaker_id, role}, attributes are missing). But A = role is prime. Satisfied by the second route.

Yes, it is in 3NF.

Is it in BCNF? BCNF only admits the first route:

• h2: speaker_id is not a superkey. Violated.

It is not in BCNF.

And what harm does it do in practice? Look at the data: the fact "speaker 7 is a moderator" is written twice (rows 210 and 211), and the fact "speaker 9 is a workshop leader", twice more. If Clara Ferrán renegotiates her contract and becomes a workshop leader, every one of her rows has to be updated or the database will say she moderates some events and runs workshops at others, contradicting N2. It is a classic update anomaly, inside a table that is in 3NF. That is exactly what BCNF is there to catch.

On top of that, the insertion anomaly: you cannot record that a new speaker has the workshop leader role until she is assigned to some event.

Fix: the rule is always the same. The offending dependency is pulled out into its own table, with the determinant as the primary key.

speaker_role

assignments

Each speaker's role, written once. Both tables are in BCNF.

And now the small print, which is what makes BCNF not always the best idea.

Look again at the decomposition we have just made and ask yourself this: where did rule N1 end up ("within one event, each role is performed by a single person")?

Formally, the dependency h1: {event_id, role} → speaker_id. In the decomposition:

• It is not in speaker_role: there is no event_id.
• It is not in assignments: there is no role.

The dependency has disappeared from both tables. And its disappearance has a very concrete practical consequence: there is no longer any combination of PRIMARY KEY, UNIQUE or CHECK that prevents this:

If speaker 20 is also a moderator according to speaker_role, we have just put two moderators into the same event, violating N1, and the DBMS has not complained because the primary keys of both tables are respected.

To check N1 you have to JOIN the two tables:

And a query is not a constraint: you have to run it, and by then the bad data is already inside.

Definition. A decomposition preserves dependencies if every functional dependency in the original set can be checked by looking at a single table of the decomposition, without having to join them.

The theoretical result is this, and it is worth knowing:

• There always exists a 3NF decomposition that is both lossless and dependency-preserving.
• There does not always exist a BCNF decomposition that preserves dependencies.

Our example is precisely one of the latter cases. Faced with it, you have to choose:

There is no universal answer. The decision depends on which dependency gets broken more often in practice and which is more serious. What is not acceptable is decomposing to BCNF without realizing that a business rule has been lost: the rule then simply stops being enforced and nobody notices until somebody asks why there are two moderators.

A good part of lesson 05-03 is about how these two properties —dependency preservation and lossless decomposition— are formally verified, and about Heath's condition, which guarantees the second.

(A note to avoid confusion: the participations table of BiblioRed's real schema, keyed on {event_id, speaker_id, role}, does not have this problem, because N2 does not apply there: a speaker can moderate one event and run a workshop at another. Without the dependency speaker_id → role, participations is in BCNF. The example in this section is a hypothetical variant built to illustrate the case.)

Up to here, everything has revolved around functional dependencies. There is another kind of dependency that functional ones do not capture, and it needs a normal form of its own.

BiblioRed organizes event 210, "Book club: historical fiction". That event has:

• Two speakers: 7 and 9.
• Three recommended materials: 101, 102 and 103.

And here is the key fact: the speakers and the materials have nothing to do with each other. Speaker 7 is not associated with any particular material; the three materials belong to the event, not to a speaker. They are two independent lists hanging off the same event.

Now imagine somebody puts them into a single table:

event_resources — in BCNF but NOT in 4NF

Six rows to represent two facts and three facts. 2 × 3 = 6: the table is a Cartesian product in disguise. With three speakers and eight materials it would be 24 rows for eleven facts.

Note how odd this is: there is no problematic functional dependency at all. The only candidate key is the whole table, {event_id, speaker_id, material_id}; every attribute is prime; there is no determinant that is not a superkey. The table is in BCNF and it is still a disaster:

Definition. In a relation R, there is a multivalued dependency of Y on X, written X ↠ Y (with a double arrowhead), if the set of Y values associated with a value of X depends only on X and is independent of the other attributes of the relation.

It is read "X multidetermines Y". In our case:

"The event multidetermines its speakers": an event's list of speakers is what it is, regardless of which materials it has. And vice versa.

A multivalued dependency always comes in a pair: if X ↠ Y in a relation with attributes X, Y, Z, then X ↠ Z too. That is why they are written together: event_id ↠ speaker_id | material_id.

Note the relationship between the two kinds of dependency: every functional dependency is a multivalued dependency (if X → Y, the set of Y values for each X has exactly one element and does not depend on anything else). The converse is not true: event_id ↠ speaker_id is not functional, because an event has several speakers.

Definition. A relation is in fourth normal form if it is in BCNF and, for every non-trivial multivalued dependency X ↠ Y, X is a superkey.

In event_resources, event_id ↠ speaker_id is non-trivial and event_id is not a superkey (it does not determine the whole row). It violates 4NF.

Fix: split the two independent lists into two tables. Which is exactly what the module 4 schema already does:

participations (2 rows) + events_materials (3 rows) = 5 rows instead of 6. And, more important than the saving: adding a material is one row, and removing a speaker is one row.

This is a good moment to point out something encouraging. When, back in module 4, we decided by understanding the domain that an event's speakers and its materials were two different N:M relationships and gave them two tables, we were putting the schema into fourth normal form without ever having heard of it. Design by understanding the domain and formal normalization almost always end up in the same place; normalization is there to verify it and for the cases where intuition fails.

4NF is only violated when there are two or more independent multivalued relationships in the same table. If the two lists were not independent —for example, if each material were associated with a particular speaker ("material 101 is brought by speaker 7")— then the three-column table would be correct and necessary: it would represent a genuine ternary relationship, of the kind we saw in 04-02 and resolved with rule 9 in 04-03.

The diagnostic question is always the same: does the set of values of this column for an event change according to the value of the other column? If the answer is no, they are independent and they have to be separated.

Fifth normal form, also called project-join normal form, is the last step with any practical content, and it has to be said honestly that it rarely turns up in real life. It is explained here so that you know it exists and so that you recognize the case if you ever meet it, not because you are going to apply it next month.

The idea generalizes what came before. 4NF talks about tables that can be split into two without losing information. 5NF talks about tables that cannot be split into two, but can be split into three or more.

Definition. A relation is in fifth normal form if every join dependency that holds in it is implied by its candidate keys. A join dependency exists when the relation is equal to the join of several of its projections.

The typical example requires a cyclic business rule. Suppose BiblioRed acquires stock under this rule:

N3: if a branch works with a publisher, and that publisher issues a collection, and that collection is present in that branch, then that branch buys that collection from that publisher.

It sounds contrived, and it is: that is why 5NF is rare. But if that rule holds, the table purchases(branch, publisher, collection) is exactly reconstructible from three projections —(branch, publisher), (publisher, collection) and (branch, collection)— and storing it whole is redundant.

What to take away:

• A 5NF violation only appears when there is a cyclic rule of this kind between three or more attributes.
• Detecting it demands very fine-grained domain analysis, and getting the diagnosis wrong produces decompositions that invent rows when joined back (the information-loss problem, which we will see in 05-03).
• Any relation in 4NF whose candidate key is the whole relation, or whose dependencies are all functional, is usually already in 5NF.

Practical recommendation: do not go looking for 5NF violations systematically. If a table with three or more foreign keys strikes you as suspiciously redundant and you spot a cyclic business rule, then investigate it. Otherwise, it is not there.

In 1981 Ronald Fagin defined domain-key normal form:

A relation is in DKNF if every constraint it must satisfy is a logical consequence solely of the domain constraints (the values allowed in each column) and the key constraints (the candidate keys).

It is a supremely elegant definition with a remarkable property: a relation in DKNF has no modification anomaly of any kind, neither the known ones nor any that might be discovered in the future. It is the theoretical ceiling of normalization.

Its problem is twofold: there is no general algorithm for bringing a relation into DKNF, and many relations admit no decomposition that gets them there. For instance, BiblioRed's rule "the sum of the payments against a fine cannot exceed its amount" is not reducible to domains or keys, and therefore no table that needs it will ever be in DKNF. The practical solution for rules like that is the one we already know from 04-04: a trigger or the application logic.

Mention it if somebody asks you; do not chase it.

And a complementary table that helps you decide where to start looking:

The last row is especially useful: if the table has a single candidate key, 3NF and BCNF coincide. Since the vast majority of tables with a surrogate key have a single candidate key, in practice getting to 3NF usually means having got to BCNF.

With seven normal forms on the table, the obvious question is: how far do you have to go?

The answer applied in practically every serious transactional system is this:

Up to 3NF or BCNF, always. Beyond that, only if the specific case calls for it.

And the reasons are solid, not an excuse to do less work:

Up to 3NF/BCNF the benefit is enormous and the cost is low. Removing partial and transitive dependencies eliminates virtually all the anomalies that occur in a real system, and the resulting tables are the ones any professional in the field expects to find. Besides, the decompositions are easy to reason about and to explain.

Beyond BCNF, the diminishing returns are abrupt. 4NF violations are infrequent and, when they appear, are usually so obvious (a table with six rows where five facts should fit) that they are caught by common sense. 5NF violations are extremely rare, hard to diagnose and easy to misdiagnose.

A schema in 3NF is a schema other people understand. This carries more weight than it seems. If in three years' time somebody has to maintain BiblioRed's database, they will find tables in the shape they expect. A schema decomposed all the way to fifth normal form, with tables that exist for reasons you can only follow with the dependency sheet in front of you, is a schema the next person will break by accident.

The operational criterion, then:

And the corollary, which foreshadows the last lesson of the module: normalize to 3NF/BCNF as the baseline, and only then, with performance figures in hand, consider deliberately stepping back. Stepping back from a normalized design is an informed decision; never having normalized at all is simply not having done the work.

Believing you can determine the normal form by looking at the table alone. You cannot. Without knowing the functional dependencies —that is, without knowing the business rules— any answer is a guess. If somebody shows you a CREATE TABLE and asks which normal form it is in, the correct answer starts with "it depends on which rules govern these columns".

Skipping levels when checking. It is tempting to go straight to 3NF because it looks like the most important one. But if the table has a comma-separated list, it is not even in 1NF, and talking about transitive dependencies on it makes no sense. Check bottom-up and fix before climbing.

Looking for partial dependencies in tables with a simple key. It is wasted time: they cannot exist. If the key is a single attribute, the table is in 2NF by construction. That shortcut saves half the work in a schema with surrogate keys.

Confusing "many values" with "non-atomic". A member having three phone numbers does not violate 1NF; what violates it is putting them in the same cell or in three columns. The member_phones table with three rows is perfectly 1NF: every cell holds one value.

Applying 1NF rigidly against modern composite types. A JSONB column with data that is always read whole, with no business rules on its fields, is acceptable. The useful discussion is not "is this atomic?" but "am I going to need to filter, group or constrain by a part of this?".

Decomposing to BCNF without checking which dependencies are lost. It is the most expensive mistake in this lesson, because the result looks better: less redundancy, cleaner tables. And yet a business rule has stopped being guaranteed. Before decomposing to BCNF, list the original dependencies and check one by one which table each of them ends up in. If any of them ends up in none, decide consciously between staying in 3NF and adding a trigger.

Chasing 5NF. If you find yourself reasoning about join dependencies in an ordinary project, you have almost certainly misdiagnosed something lower down. Go back and check 3NF.

Method tip: when you review an existing schema, do it table by table and write three lines for each: its dependencies, its candidate keys and its highest normal form. It is a half-hour document that serves for years and that turns design arguments into arguments with data.

For each of these three BiblioRed tables, work out the highest normal form it satisfies and, if it does not reach 3NF, say which dependency prevents it and which anomaly that produces.

a) reservations_v0(member_id, material_id, reservation_date, member_email, status)

Rules: a member can only have one live reservation per material; the email identifies the member.

b) fines_v0(fine_id, member_id, reason, daily_rate, days_late, amount)

Rules: each reason has a fixed daily rate set by by-law (late_return = €0.10/day, damage = €2.00/day); the amount is the rate times the days.

c) dvd_subtitles(material_id, language)

Rules: a DVD can have subtitles in several languages; there are no other rules.

BiblioRed assigns each member a reference librarian with this table:

referrals(member_id, specialty, librarian_id)

Business rules:

• P1: for each member and each specialty (children's, literature, technical) there is a single reference librarian.
• P2: each librarian specializes in a single specialty.

You are asked to:

• a) Write the functional dependencies.
• b) Find every candidate key by computing closures.
• c) Prove that the table is in 3NF but not in BCNF.
• d) Propose the decomposition to BCNF and say which dependency is lost.

For each of these two cases, say whether the three-column table violates 4NF (and has to be split in two) or whether it represents a legitimate ternary relationship (and has to be left as it is). Justify it with the diagnostic question from section 9.

a) event_languages_accessibility(event_id, language, accessibility_service)

An event is offered in several languages (Catalan, Spanish) and provides several accessibility services (hearing loop, sign language interpreter, live captioning). The services are available for the whole event, regardless of the language.

b) participations(event_id, speaker_id, role)

In BiblioRed's real schema: an event has several speakers, and each speaker performs one or several roles at that particular event. Speaker 7 being a moderator at event 210 says nothing about what he does at 211.

Solution 1

a) reservations_v0 is in 1NF and does not reach 2NF.

Dependencies:

The candidate key is {member_id, material_id} (from the rule "one live reservation per member and material"). Prime attributes: member_id, material_id.

r2 is a partial dependency: member_id is half the key and already determines member_email, which is non-prime. So the table is not in 2NF, and the highest normal form it satisfies is 1NF. This is a good reminder of why you check bottom-up: anyone who goes straight to looking for transitive dependencies will find that the question does not even arise.

The anomaly is an update one: if Marta Alsina corrects her email, every one of her reservations has to be touched, and forgetting just one leaves two emails for the same person —exactly defect 5 from the diagnosis in 01-01. The fix is to remove member_email from here: it already lives in members, and from reservations you get there through the foreign key member_id.

b) fines_v0 is in 2NF, not in 3NF.

Dependencies:

The candidate key is {fine_id}, a single attribute, so 2NF is guaranteed.

m2 violates 3NF: reason is not a superkey ({reason}⁺ = {reason, daily_rate}) and daily_rate is not prime. It is a transitive dependency fine_id → reason → daily_rate. The anomaly: if the by-law raises the late_return rate to €0.15/day, every late-return fine in the history has to be updated —and that, besides being expensive, is wrong, because the fines already issued were computed with the old rate.

The fix is a fine_rates(reason, daily_rate) table with reason as the primary key and a foreign key from fines.

m3 is also a transitive dependency (amount is a derived value). Here the right answer is not obvious and it is a perfect preview of the next lesson: the amount must stay in fines because it is a historical figure that has to remain frozen at the value it had on the day it was issued, even if the rate changes later. It is a deliberate, justified denormalization, not an oversight.

c) dvd_subtitles is in BCNF (and in 4NF, and in 5NF).

The only possible non-trivial dependency would be between material_id and language, and it does not exist in either direction: a DVD has several languages and a language is on several DVDs. The candidate key is the whole table, {material_id, language}; both attributes are prime; there is no determinant that is not a superkey. Every table of exactly two attributes whose key is the pair is in BCNF by construction. It is an example of pure association tables being the most "normal" there are.

Solution 2

a) Dependencies:

b) Candidate keys.

• {member_id, specialty}⁺: through p1, librarian_id comes in. That is all three. Superkey. Minimal? {member_id}⁺ = {member_id} (no dependency starts with member_id alone); {specialty}⁺ = {specialty}. Neither half is enough. Candidate key 1.
• {member_id, librarian_id}⁺: through p2, specialty comes in. All three. Superkey. Minimal? {librarian_id}⁺ = {librarian_id, specialty}, which does not contain member_id. And we already saw that {member_id}⁺ does not grow. Candidate key 2.

Two candidate keys, composite and overlapping on member_id. Prime attributes: all three (member_id in both, specialty in the first, librarian_id in the second). There are no non-prime attributes.

c) It is in 3NF. 3NF requires, for each non-trivial dependency X → A, that X be a superkey or A be prime:

• p1: {member_id, specialty} is a superkey. Satisfied.
• p2: librarian_id is not a superkey, but specialty is prime (it is in candidate key 1). Satisfied by the second route.

It is not in BCNF, because p2 has a determinant, librarian_id, that is not a superkey, and BCNF does not accept the excuse that the determined attribute is prime.

The concrete harm: each librarian's specialty is repeated in every row of the members they are assigned to. If librarian 22 changes specialty, hundreds of rows have to be updated. And you cannot record the specialty of a newly hired librarian until they are assigned some member.

d) Decomposition to BCNF: the offending dependency is pulled out.

p1 is lost: {member_id, specialty} → librarian_id. Neither librarian_specialty nor referrals contains member_id and specialty at the same time, so rule P1 ("a single librarian per member and specialty") can no longer be guaranteed by any declarative constraint. Nothing stops Marta Alsina being assigned two librarians who both happen to be literature specialists.

The decision is the one we set out in section 8: stay in 3NF and guarantee P1 with the primary key {member_id, specialty}, accepting the redundancy of the specialty; or decompose to BCNF and guarantee P1 with a trigger. If librarians change specialty very rarely —which is the usual case— the first option is more sensible.

Solution 3

a) It violates 4NF. We apply the diagnostic question: does an event's set of accessibility services change according to the language? The statement says explicitly that it does not: the services are available for the whole event. They are two independent lists hanging off the same event_id, and there are two multivalued dependencies:

An event in 2 languages with 3 services would generate 6 rows for 5 facts, and adding a fourth service would force you to insert 2 rows. The fix is to split it into event_languages(event_id, language) and event_accessibility(event_id, service).

b) It does not violate 4NF: it is a legitimate ternary relationship. Same question: does the set of roles change according to the event? Yes, emphatically: the statement says that speaker 7's role at event 210 says nothing about what he does at 211. The role is an attribute of the particular participation, not an independent property of either the event or the speaker.

Formally, there is no event_id ↠ role independent of speaker_id, so there is no multivalued dependency to violate. Splitting this table would be a serious mistake: it would lose the information about who does what, which is precisely what we want to store. It is exactly the case we resolved with transformation rule 9 in 04-03.

The difference between the two cases is the lesson to take away: the structure of the table does not tell you whether it violates 4NF; the business rule does. Two tables with three columns each, one has to be split and the other does not.

You now have the complete catalog, and with it the ability to deliver a verifiable judgment on any table.

1NF demands atomic values: no lists inside a cell, no numbered columns, no duplicate rows. It is the one that turns the anti-patterns we called out in 04-01 into violations with a name, and its fix is transformation rule 3, which we already applied in 04-03. We also learned that "atomic" is not a property of the data but of its use: a date is atomic, a JSONB that is only ever read whole is atomic in practice, and an address is atomic right up until the day you have to group by postal code.

2NF removes partial dependencies, which can only exist in tables with a composite key. 3NF removes transitive ones, where a non-prime attribute depends on another non-prime one. Both are mandatory and both are fixed the same way: the offending dependency is pulled out into its own table, with the determinant as the primary key and a foreign key in the original.

BCNF closes the loophole 3NF leaves with a one-line definition —every determinant is a superkey— and only differs from it when there are composite, overlapping candidate keys. Its small print is fundamental: decomposing to BCNF can lose dependency preservation, and that means a business rule can no longer be guaranteed inside a single table. It is decided with judgment, not automatically.

4NF attacks a different problem: two independent lists stuffed into the same table, multiplying each other without meaning anything. Its remedy —one table per list— is the one module 4 had already applied by intuition to participations and events_materials. 5NF and DKNF exist, they are coherent, and in a system like BiblioRed they do not apply; it is worth knowing that they are there and not chasing them.

And above the whole catalog, the industry criterion: up to 3NF/BCNF always, beyond that only if the case calls for it. Not out of laziness, but because the diminishing returns are abrupt and because a schema in 3NF is a schema the next person will understand.

What we have not done yet is apply any of this from start to finish on a real table. We have seen six minimal examples, each built to illustrate one normal form in isolation. Reality does not arrive like that: it arrives like BiblioRed's spreadsheet, with thirteen columns, eleven dependencies, non-atomic values, partial and transitive dependencies at the same time, eighty-four thousand rows of historical data that have to be migrated and cleaned, and a production system that cannot be stopped.

In lesson 05-03, The Normalization Process, we do exactly that. A six-step procedure, applied from start to finish on loans_sheet: from a flat, unnormalized table all the way to 3NF, showing at each step the data before and after and the SQL that decomposes and migrates. And with it, the two properties every decomposition must satisfy —lossless and dependency-preserving—, including the counterexample of a badly done decomposition that invents rows when joined back. We will finish by putting the extended schema from module 4 through the examination, table by table, to see whether it holds up.