Questions: Join Dependencies and Fifth Normal Form

Question 1

A table (supplier, part, project) records supply relationships. No functional dependency exists between any two columns and the other. Under what condition does 5NF require decomposing this table?

• A. Whenever a supplier appears with multiple parts
• B. When the table violates BCNF due to a functional dependency
• C. When the table's facts are fully derivable from three independent pairwise relationships: supplier-part, supplier-project, and part-project
• D. When there is a multivalued dependency between supplier and part

Answer: (C)

5NF targets join dependencies: when the table can be losslessly decomposed into projections and the facts are derivable from independent pairwise relationships. If the business rule is 'if S can supply P in any project, S works on J with any part, and P is needed by J from any supplier, then S supplies P to J,' then any triple satisfying those pairwise conditions must appear — a join dependency. This is not a functional dependency (option B) or a simple MVD (option D); it's a three-way constraint. Decomposing into the three pairwise projections eliminates the redundancy.

Question 2

What distinguishes a join dependency from a multivalued dependency (MVD)?

• A. MVDs involve primary keys; join dependencies do not
• B. An MVD decomposes a table into exactly two projections; a join dependency may require three or more
• C. Join dependencies only apply to tables with composite primary keys
• D. MVDs are semantic constraints; join dependencies are purely syntactic

Answer: (B)

Fourth Normal Form handles multivalued dependencies by decomposing a table into two projections — the defining feature of an MVD is that it involves two independent multi-valued facts about a key that can be separated. A join dependency generalizes this: it describes cases where a table can be losslessly reconstructed from three or more projections. Every MVD implies a join dependency (into two components), but not every join dependency reduces to an MVD. 5NF extends 4NF by catching these multi-way constraints.

Question 3

A table in 5NF is guaranteed to have no redundancy caused by join dependencies.

• T. True
• F. False

Answer: True

Correct. 5NF is defined precisely as the condition where every join dependency is implied by the table's candidate keys. A join dependency implied by a key is trivial — it doesn't represent redundancy because the key already uniquely determines the other attributes. If all non-trivial join dependencies have been eliminated through decomposition, the resulting projections store independent facts and no redundant rows remain. 5NF is the theoretical endpoint of lossless decomposition.

Question 4

If a table satisfies 5NF, it is typically practical to use that schema in a production database without further consideration.

• T. True
• F. False

Answer: False

False. Reaching 5NF often requires decomposing a table into three or more projections. Reconstructing data then requires multi-way joins, which can be computationally expensive. In practice, most databases deliberately stop at BCNF or 4NF because: (1) 5NF violations are rare in typical business data; (2) detecting join dependencies requires domain knowledge about business rules, not just data inspection; (3) the performance cost of extra joins often outweighs the benefit of eliminating subtle redundancy.

Question 5

Explain why 5NF violations cannot be detected by inspecting the data alone, unlike violations of 1NF, 2NF, or 3NF.

Model answer: Functional dependencies (1NF–3NF/BCNF) and multivalued dependencies (4NF) can often be inferred from data patterns: if column A always determines column B, you can hypothesize a functional dependency. But a join dependency describes a business rule — the constraint that three-way combinations are fully determined by their pairwise projections. This is a semantic claim about what combinations are possible, not just what happens to appear in the current dataset. A table might satisfy a join dependency coincidentally without the underlying business rule holding.

This distinction matters for practice. Functional dependencies can be discovered by profiling data (looking for deterministic relationships). Join dependencies require a conceptual model of the business rules: 'Is it always true that if S can supply P and S works on J and P is needed by J, then S supplies P to J?' That's a question about the world, not the data. This is why 5NF is rarely explicitly targeted — you'd need to enumerate and verify all possible join dependencies from domain knowledge, which is impractical for complex schemas.