Higher Normal Forms: 4NF, 5NF (Project-Join), and Domain/Key Normal Form (DKNF)

Introduction: Why Do We Need Higher Normal Forms?

While Boyce-Codd Normal Form (BCNF) eliminates all anomalies based on Functional Dependencies (FDs), some tables still experience severe data duplication caused by independent multi-valued relationships.

To achieve complete normalization, database theorists developed:

• Fourth Normal Form (4NF): Eliminates Multi-Valued Dependencies (MVD).
• Fifth Normal Form (5NF / PJNF): Eliminates Join Dependencies.
• Domain/Key Normal Form (DKNF): The ultimate theoretical ideal of database normalization.

1. Fourth Normal Form (4NF) & Multi-Valued Dependencies

What is a Multi-Valued Dependency (MVD)?

A Multi-Valued Dependency (written as X ↠ Y, read as “X multidetermines Y”) exists in a relation R when for a single value of X, there exists a set of multiple independent values of Y, and this set is completely independent of other attributes in R.

Violating Example:

Suppose a student participates in multiple independent Hobbies and can speak multiple independent Languages: Table: STUDENT_HOBBY_LANG(Student, Hobby, Language)

• Notice that Hobby and Language have zero logical connection to each other!
• Yet, because both are multivalued attributes of Rahul, the database is forced to store 4 separate rows (Cartesian Product of hobbies and languages) to remain consistent.
• If Rahul picks up a 3rd hobby (“Painting”), we must insert 2 new rows for Hindi and English!
• This table has zero functional dependencies, meaning it is technically already in BCNF, yet it is plagued with terrible redundancy!

Definition of 4NF:

A relation is in 4NF if and only if:

1. It is already in BCNF.
2. For every non-trivial multi-valued dependency X ↠ Y, X is a Super Key.

Decomposing into 4NF:

We decompose the table into two independent binary relations:

1. STUDENT_HOBBY: [ <u>Student, Hobby</u> ]
2. STUDENT_LANG: [ <u>Student, Language</u> ]

Now both tables are in 4NF, and the combinatorial cross-product redundancy is eliminated!

2. Fifth Normal Form (5NF / Project-Join Normal Form - PJNF)

What is a Join Dependency (JD)?

A relation R has a Join Dependency (written as *(R1, R2, R3, ... Rn)) if R can be decomposed into smaller projections R1, R2, ... Rn such that joining them back together using natural joins reconstructs the original table R with zero spurious (phantom) tuples.

Definition of 5NF:

A relation is in 5NF (PJNF) if and only if:

1. It is already in 4NF.
2. Every join dependency in the relation is implied by the Candidate Keys of the relation.

Real-World Example (The Agent-Company-Product Scenario):

Suppose an enterprise records which Agent represents which Company to sell which Product:

• Table R(Agent, Company, Product) has a composite key of all three fields.
• But there exists a business constraint:
  • If Agent A represents Company C,
  • and Agent A sells Product P,
  • and Company C makes Product P,
  • then Agent A MUST sell Product P for Company C!
• This table cannot be decomposed into 2 tables without loss, but it CAN be losslessly decomposed into three separate tables:
  1. R1(Agent, Company)
  2. R2(Agent, Product)
  3. R3(Company, Product)

When all three tables are joined together, the original table is restored without false tuples. The relation is now in 5NF.

3. Domain/Key Normal Form (DKNF)

Introduced by Ronald Fagin in 1981, Domain/Key Normal Form (DKNF) represents the highest theoretical peak of relational normalization.

Definition of DKNF:

A relation is in DKNF if every constraint on the relation is a logical consequence of the definition of Domains and Keys.

• Domain Constraint: Values for an attribute belong to its legal, allowed domain (e.g., Salary > 0, Age between 18 and 65).
• Key Constraint: Uniqueness enforced by Candidate Keys.
• If a database design is in DKNF, it is impossible to have any insertion, deletion, or update anomalies whatsoever!
• Practical Note: DKNF is an idealized theoretical goal. There is no general-purpose automated algorithm to guarantee DKNF for complex real-world databases.

4. Comprehensive Comparison of All Normal Forms

💡 DAVV Exam Preparation Tips

5-Mark Question: “What is a Multi-Valued Dependency (MVD)? Define Fourth Normal Form (4NF) with a student example.”

• Define MVD X ↠ Y (X determines an independent set of Y values).
• Give the Student-Hobby-Language table example.
• Show the decomposition into (Student, Hobby) and (Student, Language).

10-Mark Question: “Explain 4NF, 5NF (Project-Join Normal Form), and Domain/Key Normal Form (DKNF). Provide the complete comparison table of all normal forms from 1NF to DKNF.”

• Explain why BCNF is not always enough when multivalued attributes exist.
• Detail 4NF with MVDs and 5NF with 3-way join dependencies.
• Define DKNF and explain why it is the theoretical ultimate form.
• Conclude with the full 7-row summary table.