What is a join dependency, and what does Fifth Normal Form (also called Project-Join Normal Form) require? Describe the kind of table that can be in 4NF but not in 5NF.

## Join dependencies A join dependency on a table R names a set of column subsets R1..Rn and asserts that R always equals the natural join of its projections onto them. Nothing is lost by splitting R into those n pieces, and nothing spurious appears when they are rejoined. A multivalued dependency is exactly the n equals 2 case, which is why 5NF sits above 4NF. A join dependency is trivial if one of the pieces is the whole table. It is implied by the candidate keys when every piece contains a candidate key, in which case the split is uninteresting. ## The definition A table is in 5NF, also called Project-Join Normal Form, when every nontrivial join dependency it satisfies is implied by its candidate keys. Equivalently: the only lossless decompositions available are the boring ones that just repeat the key. 5NF implies 4NF, which implies BCNF. ## The classic counterexample SupplierPartProject(supplier, part, project) records that a given supplier supplies a given part to a given project. All three columns together form the key, so the table is in BCNF. Suppose neither pair of columns determines a set independently of the third, so no multivalued dependency holds and the table is in 4NF too. Now add a business rule with a cycle in it: whenever supplier S supplies part P at all, and part P is used by project J, and supplier S already serves project J, then S supplies P to J. Under that rule the three-way fact is fully recoverable from the three two-way facts. The join dependency on the three pairwise projections holds, none of those pieces contains the key, so the table violates 5NF. Decomposing into three binary tables removes redundancy: each pairwise fact is stored once instead of once per third value. Crucially, splitting into any two of the three pieces is lossy and produces spurious rows. Only the three-way split works, which is why 5NF is not reachable by repeatedly applying binary decompositions. ## Why this is rare and awkward Three things make 5NF a niche concern. First, the trigger is a specific cyclic rule of the form the pairwise facts imply the three-way fact. Most genuine ternary relationships are not like that; usually a supplier supplies a part to some projects and not others, and then no join dependency holds and the table is already in 5NF. Second, you cannot detect it from data. A snapshot might satisfy the join dependency by accident. Only the domain expert can say whether the cyclic rule is a permanent constraint. If you split on a coincidence and the rule later fails, the join now invents supplier-part-project rows that were never asserted, silent data corruption. Third, the decomposition is not self-enforcing. After splitting, inserting into the binary tables can create new three-way facts implicitly, which is desirable only if the rule really holds. There is no portable declarative constraint that says these three tables must remain consistent with the rule, so enforcement falls to application logic or triggers. ## What to say in an interview Define join dependency as the n-way generalisation of the multivalued dependency, state the key-implied condition, give the supplier-part-project shape with its cyclic rule, and then say the honest engineering conclusion: 5NF is a theoretical completeness result that almost never changes a production schema, and when it appears the deciding question is whether the rule is truly invariant.