Skip to content
Tutorials Center
My learning

Topics

Lesson 33 of 79 ยท Database Design Theory

Multivalued Dependencies, 4NF and Denormalization

Understand multivalued dependencies, fourth and fifth normal form, and when to denormalize on purpose, with live SQL showing a 4NF violation and its fix.

Functional dependencies catch a lot, but they miss one kind of redundancy. Imagine an employee who has several skills and speaks several languages, and the two lists have nothing to do with each other. If you store them in one table you must write a row for every combination, and no FD explains why. That redundancy comes from a multivalued dependency (MVD), and the normal form that removes it is fourth normal form (4NF).

This lesson goes up the ladder one more step, glances at fifth normal form, and then comes back down: real systems sometimes denormalize on purpose to make reads cheaper, and a good designer knows exactly what is being traded away. The lesson closes with a rule of thumb for where to stop.

Beyond BCNF

Independent lists Two unrelated sets about one entity.
Forced combinations One row per pair, repeated forever.
4NF Splits each list into its own table.
1 / 2

Multivalued dependencies

Take the schema Person(emp, skill, lang). An employee's skills are independent of the languages they speak: Ana knows SQL and Python, speaks English and French. To record this without losing the independence, you need all four combinations. The table is in BCNF, because its only key is the whole row (emp, skill, lang) and there are no non-trivial FDs at all. Yet it is clearly wasteful.

A multivalued dependency X ->> Y ("X multidetermines Y") holds in R when each value of X is associated with a set of Y values that does not depend on the remaining attributes Z = R - X - Y. Formally: if two tuples t1 and t2 agree on X, then a third tuple t3 must exist that takes X and Y from t1 and Z from t2 (and symmetrically a fourth with the roles swapped). In plain words, for each X value the Y values and Z values form a full cross product. Here emp ->> skill and emp ->> lang.

Some facts to hold on to:

  • Every FD is also an MVD (if X -> Y, the set of Y values is a single value, trivially independent).
  • Complementation: if X ->> Y then X ->> Z, where Z is everything else. MVDs always come in pairs.
  • An MVD X ->> Y is trivial if Y is a subset of X, or if X โˆช Y is all of R.

Fourth normal form

A relation is in 4NF when, for every non-trivial MVD X ->> Y, X is a superkey. This is BCNF's rule with "FD" replaced by "MVD". It follows that 4NF implies BCNF. In Person, emp is not a superkey, so the relation violates 4NF. The fix is the same kind of split as before, driven by the MVD: X โˆช Y and X โˆช Z.

TEXT
EmpSkill(emp, skill)
EmpLang(emp, lang)

An easy test for an MVD in data: for each X value, the number of rows must equal (distinct Y values) ร— (distinct Z values). If it is smaller, a combination is missing and the MVD does not hold in that instance. Let's see the redundancy, and the anomaly that appears when one combination is forgotten.

SQL A 4NF violation: the combinations must all be there

Press Run (or Ctrl + Enter) to see the result here.

Before the insert, both employees satisfy the test (4 and 4 for Ana, 1 and 1 for Bo). After it, Ana is expected to have 6 rows but has only 5: the table now claims she speaks German only when using SQL, which is nonsense. This is the insertion anomaly of a 4NF violation: one new fact needs several rows, and forgetting any of them corrupts the meaning. The matching deletion anomaly is that removing one language means deleting a row for every skill.

The 4NF fix

After the split, each fact is stored once. Adding German is a single row in emp_lang, and the original table is recreated by joining the two lists on emp. As with any decomposition, the shared column emp must make the join lossless; here the MVD itself guarantees that.

SQL Decompose into 4NF and verify nothing was lost

Press Run (or Ctrl + Enter) to see the result here.

The first result reads 5 original rows against 3 and 3 after the split. The comparison shows 0 missing and 0 extra rows, so the join gives back the original exactly. The last query lists six combinations for Ana: both skills with all three languages, from a single new row.

The test needs real knowledge of the business. If a person's languages were actually tied to specific jobs (Ana uses French only when doing SQL consulting), the lists would not be independent, there would be no MVD, and splitting would lose information. Decide from the meaning of the data, not from the shape of a sample.

Join dependencies and fifth normal form

An MVD says a relation can be split in two without loss. A join dependency (JD) generalizes this: a relation satisfies *{R1, R2, ..., Rn} if it equals the join of its projections onto those n parts. Every MVD is a JD with two parts. Fifth normal form (5NF), also called project-join normal form, requires that every join dependency is implied by the candidate keys.

The standard textbook case is a three-way relation Supplies(supplier, part, project) where a business rule says: if a supplier supplies a part, and the supplier works on a project, and the project uses that part, then the supplier supplies that part to that project. The relation then equals the join of its three binary projections (supplier, part), (part, project) and (supplier, project), and it cannot be split into just two without losing information. Such cyclic rules are rare in practice, and you meet them only when a business rule really has that shape. If it does not, a three-column table is just a three-column table. Do not split it speculatively; many designers never need 5NF.

When to denormalize

Normalization trades fewer anomalies for more joins. Usually that is a bargain: joins on indexed keys are what databases do best. But a few situations justify storing redundant data on purpose:

  • A hot read needs a value that otherwise requires aggregating many rows (an order total, a comment count).
  • A reporting query joins a dozen tables and runs constantly.
  • The data is historical: the unit price at the time of sale must not change when the catalog price changes, so copying it into the order line is correct, not redundant.

The rule is to normalize first, measure, then denormalize one specific thing, and to make the copy correct by construction. A stored total can be kept in sync with a trigger. A trigger that only covers inserts is not enough, as the example shows.

SQL A stored total kept in sync by a trigger, until it is not

Press Run (or Ctrl + Enter) to see the result here.

After the inserts both numbers are 38. After the unguarded UPDATE, the stored total is still 38 while the real total is 42.5. Every redundant copy you add needs triggers (or application code) for inserts, updates and deletes on the source, plus a periodic check that recomputes and compares. Compare this with the anomalies in Redundancy and Update Anomalies: denormalization is choosing those risks deliberately, with the paperwork to contain them. Other tools can help without redundancy, such as indexes (Indexing Basics) and views; and in analytical systems the denormalized star schema is the norm (OLTP vs OLAP).

Where to stop

A practical summary for working designers:

  1. Always reach 3NF for transactional data; try for BCNF.
  2. Check for independent multi-valued facts about the same entity (skills, languages, phone numbers, tags) and give each its own table; that is 4NF in practice.
  3. Reach for 5NF only when a real business rule has the cyclic shape above.
  4. Denormalize only a specific, measured hot spot, record why, and protect the copy with triggers, constraints or a periodic audit.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In Person(emp, skill, lang), with skills and languages unrelated, which dependency holds?

    An employee has a set of skills, independent of languages, so emp multidetermines skill (and lang). It is not a functional dependency because one employee has several skills.
  2. 2. What does 4NF require?

    4NF extends BCNF to multivalued dependencies: for every non-trivial X ->> Y, X must be a superkey.
  3. 3. A table has 3 skills and 2 languages for one employee but only 5 rows for that employee. What does this suggest if skills and languages are independent?

    Independence requires 3 x 2 = 6 rows (the full cross product). Five rows means a combination is missing.
  4. 4. Which is the best reason to denormalize?

    Denormalize deliberately, for a measured hot spot, and take responsibility for keeping the redundant data correct.

Summary

  • A multivalued dependency X ->> Y means the Y values and the remaining attributes' values are independent for each X, so rows form a full cross product.
  • 4NF: for every non-trivial MVD X ->> Y, X is a superkey; it implies BCNF, and the fix is to split each independent list into its own table.
  • You can test an MVD in data by comparing the row count per X with distinct Y times distinct Z.
  • 5NF concerns join dependencies that cannot be reduced to two-way splits; it matters only for rare cyclic business rules.
  • Denormalize only after normalizing and measuring, and guard every redundant copy with triggers, constraints or audits (insert, update and delete).
  • Rule of thumb: 3NF or BCNF always, 4NF for independent lists, 5NF rarely, denormalization surgically.

Next: Finding the Highest Normal Form, FD Equivalence and DKNF.