Fourth Normal Form (4NF)

BCNF governs facts with 1 value: a tutor has 1 subject. Many facts carry a list of values, like the several languages a host speaks and the several listings a host manages. A table that stores both lists side by side has no functional dependency at all, passes BCNF, and still multiplies rows.

Host 41 speaks 2 languages and manages 3 listings, so the table needs 6 rows, 1 per combination. Nothing determines anything: the same host appears with 2 languages and the same language with 3 listings, so the key is all 3 columns and BCNF has no complaint. Now host 41 learns Italian. The complete update inserts 3 rows, 1 per listing. A job that inserts only (41, Italian, L100) leaves the table claiming Italian goes with 1 listing and not the other 2, a claim nobody ever made, because a language belongs to the host and applies to every listing.

Multi-valued dependency

The relationship is a multi-valued dependency, written host_id ->> language: for a given host, the set of languages across the rows is the same whatever listing those rows carry. The symmetric dependency host_id ->> listing_id holds for the same reason. 4NF requires that the left side of every such dependency is a candidate key. host_id alone is not a key of host_profile, so the table fails. The repair puts each list in its own table keyed by the host.

Adding Italian is now 1 row in host_languages, and no listing is involved. The saving grows with the lists: a host with 5 languages and 10 listings costs 50 rows in the combined table and 15 across the pair.

The test that tells split from keep

Not every 3-column table with repeated values is a cross product. listing_translations(listing_id, language, translated_title) stores the languages a particular listing has been translated into. Adding a Japanese translation to L100 says nothing about L205. The language is a fact about that listing, so the columns stay together, and the table is already in 4NF. One question separates the 2 cases: when you add a value to column B for a given A, must you also add it for every value of column C that A has? If yes, B and C are independent lists and the table splits. If no, B and C form a single fact and the table stays.

Real data can answer the question for you. Count the distinct values of each list per host and compare the product with the rows stored.

SELECT
    host_id,
    COUNT(DISTINCT language) AS languages,
    COUNT(DISTINCT listing_id) AS listings,
    COUNT(*) AS stored_rows
FROM host_profile
GROUP BY host_id

When stored_rows equals languages times listings for every host, the table is storing 2 independent lists as a product and belongs in 2 tables. When it falls short for some hosts, either a job skipped rows or the 2 columns share a real relationship, and the data owners can tell you which.

A university stores course_offerings(course_id, textbook_isbn, instructor_id). The textbooks for a course are fixed by the department, and the instructors for a course are assigned by the scheduling office. Neither list depends on the other. A course with 3 textbooks and 4 instructors holds 12 rows. What does 4NF call for?

• Keep the table as it is, because course_id determines nothing on its own and so there is no dependency to repair.
• Split into course_textbooks(course_id, textbook_isbn) and textbook_instructors(textbook_isbn, instructor_id), with 2 smaller tables.
• Add a section_id column so that each row records 1 section taught with 1 textbook by 1 instructor.
• Split into course_textbooks(course_id, textbook_isbn) and course_instructors(course_id, instructor_id), 7 rows in place of 12.

About this lesson

This section is part of the Beyond 3NF lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.