CSE Roadmap

Specialist tier · about 3 hours · 4 parts

Part 2 of 4 · 1 min read

Multivalued dependencies and 4NF

Two independent facts about the same entity, forced into one table, multiply every combination needlessly.

Think of a form asking for every language an employee speaks and every project they worked on. Both facts sit in the same table. One row is needed for every language-and-project combination, even though the two facts have nothing to do with each other.

In plain words: a multivalued dependency exists when, for a given value of X, a set of Y values is determined independently of some other column Z. A table violates 4NF when it holds two independent multivalued facts together, forcing every combination of the two to be stored as a separate row, even though the facts don't actually relate to each other.

A worked example. employee_skills_projects (employee_id, skill, project), where skills and projects are independent facts about an employee, forces every skill-project combination to appear. An employee with 3 skills and 2 projects needs 6 rows, even though no skill is tied to any particular project.

The key lines.

-- violates 4NF: 3 skills x 2 projects = 6 needless rows
employee_skills_projects (employee_id, skill, project)

-- satisfies 4NF: 3 + 2 = 5 rows, no needless combinations
employee_skills (employee_id, skill)
employee_projects (employee_id, project)

Multivalued dependency, fourth normal form (4NF)

A multivalued dependency holds when a set of values for Y, given X, does not depend on another column Z. Fourth normal form requires BCNF, plus no table holding two independent multivalued facts that force needless combinations.

Example: splitting employee_skills_projects into employee_skills and employee_projects removes the forced, meaningless combinations between unrelated facts.