17 - BCNF & Higher Normal Forms

🎯 Learning Objectives

• Define BCNF and check if a relation satisfies it
• Decompose a relation into BCNF
• Identify multivalued dependencies (MVDs) and 4NF violations
• Understand 5NF (PJNF) and join dependencies
• Explain temporal databases (valid time vs. transaction time)

📋 Prerequisites

• 16 - Normalization (1NF-3NF) — 3NF, candidate keys, FDs

📖 Core Content

17.1 Intuition: 3NF Isn't Good Enough

Why This Matters: BCNF is the gold standard for most practical schemas. While 3NF preserves all FDs, BCNF may not — but it eliminates virtually all redundancy from FDs.

17.2 BCNF Definition

17.3 BCNF Decomposition Algorithm

pseudo
Input: R (relation), F (FD set)
Output: Decomposition of R into BCNF relations
1. Compute closure F⁺
2. Find an FD X → Y in F that violates BCNF (X is not a superkey)
3. Compute X⁺
4. Decompose R into:
   - R₁ = X⁺ (contains the violating FD)
   - R₂ = R - (X⁺ - X) ∪ X (remaining attributes + X)
5. Repeat on R₁ and R₂ until all relations are in BCNF

Worked Example

• CKs = {A,B} and {A,C}
• Check $C \rightarrow B$: C⁺ = {B,C} ≠ R. C is NOT a superkey. Violation! Step 2: Compute C⁺ = {B, C} Step 3: Decompose:
• $R₁ = C^+ = \{B, C\}$ (covers $C \rightarrow B$)
• $R₂ = R - (C^+ - C) \cup C = \{A, B, C\} - \{B\} \cup \{C\} = \{A, C\}$ Step 4: Check BCNF for $R₁(B, C)$:
• FDs: $C \rightarrow B$
• CK of R₁: {C}. C is a superkey for R₁. ✓
• R₁ is in BCNF. Step 5: Check BCNF for $R₂(A, C)$:
• No non-trivial FDs (A and C have no FD between them)
• CK = {A, C}. R₂ is in BCNF. Result: BCNF decomposition = $R₁(B, C)$, $R₂(A, C)$ Note: $AB \rightarrow C$ is NOT preserved (we lost the connection between A and B). BCNF guarantees lossless join but NOT dependency preservation.

17.4 Multivalued Dependencies (MVDs)

• $t_3[X] = t_1[X]$
• $t_3[Y] = t_1[Y]$
• $t_3[R - XY] = t_2[R - XY]$

17.5 Fourth Normal Form (4NF)

1. It is in BCNF
2. For every non-trivial MVD $X \rightarrow\rightarrow Y$, X is a superkey Worked Example: The employee-dependent-project table above:

• emp_id is NOT a superkey (emp_id alone doesn't determine a unique row)
• MVDs exist: $emp_id \rightarrow\rightarrow dependent$, $emp_id \rightarrow\rightarrow project$
• This violates 4NF Fix: Decompose into $R₁(emp_id, dependent)$ and $R₂(emp_id, project)$.

17.6 Fifth Normal Form (5NF / PJNF)

17.7 Comparison of Normal Forms

17.8 Temporal Databases

• Valid time allows historical queries ("Show me the department structure as of Jan 2020")
• Transaction time supports rollback queries ("Show me what the database looked like yesterday")

📐 Key Formulas / Concepts

⚠️ Common Pitfalls

Pitfall 1: Thinking BCNF is Always Better Than 3NF

Pitfall 2: Confusing MVDs with FDs

Pitfall 3: Decomposing Without Checking BCNF First

📝 Practice Questions

Q1. Is $R(A, B, C)$ with $F = \{A \rightarrow B, C \rightarrow B\}$ in BCNF?

Answer

Candidate key: {A, C} ($\{A,C\}^+$ = {A,B,C} = R).

Check FDs:

• $A \rightarrow B$: A is NOT a superkey ($A^+$ = {A,B} ≠ R). Violation!
• $C \rightarrow B$: C is NOT a superkey ($C^+$ = {B,C} ≠ R). Violation!

R is NOT in BCNF (but is in 3NF — check: A→B, A is not a superkey but B? B is non-prime... Actually wait — CK = {A,C}, so prime = {A,C}, B is non-prime. 3NF requires X superkey or A prime. Neither holds. So it's NOT in 3NF either.)

Q2. Check BCNF for $R(A, B, C, D)$ with $F = \{A \rightarrow B, AC \rightarrow D\}$.

Answer

Candidate key: {A, C} ($\{A,C\}^+$ = {A,B,C,D} = R).

Check FDs:

• $A \rightarrow B$: A is NOT a superkey ($A^+$ = {A,B} ≠ R). BCNF violation!
• $AC \rightarrow D$: {A,C} is a superkey. ✓

Decompose on $A \rightarrow B$:

• $A^+$ = {A, B}
• $R₁(A, B)$, $R₂(A, C, D)$

Check $R₂$: CK = {A, C}. FD $AC \rightarrow D$: AC is a superkey. ✓. R₂ is in BCNF.

BCNF decomposition: $R₁(A, B)$, $R₂(A, C, D)$

Q3. What is a multivalued dependency? How does it differ from an FD?

Answer

• FD ($X \rightarrow Y$): Each X value maps to exactly ONE Y value
• MVD ($X \rightarrow\rightarrow Y$): Each X value maps to a SET of Y values, independent of other attributes

MVDs arise when a relation has two or more independent multi-valued attributes. Example: An employee has multiple dependents AND multiple projects — these are independent.

Q4. What is 4NF? Give an example of a 4NF violation.

Answer

A relation is in 4NF if it's in BCNF and for every non-trivial MVD $X \rightarrow\rightarrow Y$, X is a superkey.

Violation example:

MVDs: $emp_id \rightarrow\rightarrow skill, emp_id \rightarrow\rightarrow dependent$ emp_id is not a superkey (many rows per emp_id).

Fix: Decompose into $R₁(emp_id, skill)$ and $R₂(emp_id, dependent)$.

Q5. Compare 3NF and BCNF. When would you choose 3NF over BCNF?

Answer

Choose 3NF when you need all FDs preserved and the redundancy from the "prime RHS exception" is acceptable. Choose BCNF when eliminating all FD redundancy is critical.

Q6. What is a join dependency? How does it relate to 5NF?

Answer

A join dependency (JD) means a relation can be reconstructed by joining its projections.

A relation is in 5NF (PJNF) if for every non-trivial JD, every component of the JD is a superkey.

In practice, if a relation is in 4NF and has no composite keys, it's almost certainly in 5NF. 5NF violations usually require specific constraints involving overlapping composite keys.

Q7. Explain valid time vs. transaction time in temporal databases.

Answer

• Valid time: When the fact was true in the real world (e.g., "Alice was employed from Jan 2020 to Dec 2023"). Useful for historical queries.
• Transaction time: When the fact was stored in the database (e.g., "this record was inserted at 10:30 AM on Jan 15"). Useful for audit and rollback queries.

A bitemporal relation stores both — allowing questions like "Show me the database state as of yesterday that reflects the real-world state in 2020."

Q8. Decompose $R(A, B, C, D)$ with $F = \{A \rightarrow B, B \rightarrow C\}$ into BCNF.

Answer

Candidate key: {A, D} ($\{A,D\}^+$ = {A,B,C,D}).

Violation: $A \rightarrow B$ (A not a superkey). Also $B \rightarrow C$ (B not a superkey).

Decompose on $A \rightarrow B$:

• $A^+$ = {A, B, C}
• $R₁(A, B, C)$ with FDs: $A \rightarrow B, B \rightarrow C$. CK = {A}.

Check $R₁$: $B \rightarrow C$ — B is NOT a superkey for R₁ ($B^+$ = {B,C} ≠ {A,B,C}). Violation!

• Decompose R₁ on $B \rightarrow C$: $R₁₁(B, C)$, $R₁₂(A, B)$

Result: $R₁₁(B, C)$, $R₁₂(A, B)$, $R₂(A, D)$

All in BCNF.

🔗 Cross-References

• Next Topic: 18 - Decomposition
• Previous Topic: 16 - Normalization (1NF-3NF)
• Related: 14 - Functional Dependencies (FD theory underpinning BCNF)
• Textbook: Silberschatz, Korth, Sudarshan — Chapter 8 (Relational Database Design) Join Discord Previous16 - Normalization (1NF-3NF)Next18 - Decomposition