BCNF & Denormalization

While 3NF is the standard for most production databases, high-integrity systems often require BCNF (Boyce-Codd Normal Form). Conversely, high-performance systems sometimes intentionally break normalization rules using Denormalization.

---

Learning Objectives

After completing this chapter, you will be able to:

• Explain BCNF and how it differs from 3NF.
• Identify when a table is in 3NF but not BCNF.
• Understand lossless join decomposition.
• Differentiate between lossless and lossy decompositions.
• Explain dependency preservation.
• Understand when and why to denormalize.
• Answer interview questions on BCNF and denormalization.

---

BCNF (Boyce-Codd Normal Form)

BCNF is a stricter version of 3NF. A table is in BCNF if:

For every non-trivial functional dependency X → Y, X must be a Super Key.

In simple terms: Every determinant (the left side of an FD) must be a candidate key.

BCNF vs 3NF

Feature	3NF	BCNF
Rule	No transitive dependencies	Every determinant is a candidate key
Overlapping CKs	Allowed	Not allowed
Dependency preservation	Always preserved	May not be preserved
Redundancy	Some possible	Minimal
Difficulty	Easy to achieve	Harder — may require more splits

---

Example: 3NF but NOT BCNF

Consider a table tracking student advisors:

Student	Major	Advisor
Rahul	CS	Dr. A
Priya	CS	Dr. A
Amit	EE	Dr. B
Sneha	EE	Dr. C

Assumptions:

• Each student has one major and one advisor
• Each advisor advises only ONE major (Dr. A only CS)
• A major can have multiple advisors

Candidate Keys:

• {Student, Major} — a student-major pair is unique
• {Student, Advisor} — a student-advisor pair is unique

FDs:

• Student → Major (a student has one major)
• Advisor → Major (an advisor advises only one major)

Is it 3NF? Yes. All non-key attributes depend on the key. There are no transitive dependencies.

Why NOT BCNF? Advisor → Major — Advisor is a determinant but NOT a candidate key (Dr. A appears in multiple rows).

Problems:

• Redundancy: Dr. A is associated with CS in every row (twice)
• Update anomaly: If Dr. A moves from CS to EE, both rows must be updated

Fix: Decompose to BCNF

Students table: {Student, Advisor} — PK: {Student}

Student	Advisor
Rahul	Dr. A
Priya	Dr. A
Amit	Dr. B
Sneha	Dr. C

Advisors table: {Advisor, Major} — PK: {Advisor}

Advisor	Major
Dr. A	CS
Dr. B	EE
Dr. C	EE

Now every determinant is a candidate key. Both tables are in BCNF.

---

Lossless Join Decomposition

When you decompose a table, you must ensure that joining the resulting tables back produces the exact same original data.

Lossless vs Lossy

Decomposition	JOIN Result	Problem
Lossless	Same as original	None
Lossy	Extra spurious rows	Data integrity compromised

Condition for Lossless Join

For decomposition of R into R1 and R2: The common attribute between R1 and R2 must be a Super Key in at least one of them.

Example: Lossy Decomposition

R(A, B, C) with FD: A → B

Split into:

• R1(A, C)
• R2(B, C)

Common attribute: C. Is C a super key in either table? No. Joining R1 and R2 will produce extra rows (Cartesian product). Lossy.

Example: Lossless Decomposition

R(A, B, C) with FD: A → B

Split into:

• R1(A, B) — A is a key
• R2(A, C) — A is NOT a key here, but R1 has A as super key

Common attribute: A. Super key in R1? Yes. Lossless.

---

Dependency Preservation

A decomposition is dependency preserving if all original FDs can be enforced locally on the decomposed tables without requiring a JOIN.

Why It Matters

Without dependency preservation, you must JOIN tables to check constraints — slower and more complex.

Property	3NF	BCNF
Dependency preservation	Always guaranteed	May not be guaranteed
Practical impact	Easy to maintain	May require application-level enforcement

Example: Non-Dependency Preservation in BCNF

R(A, B, C) with FDs: AB → C, C → B

Candidate Keys: {A, B}, {A, C}

BCNF decomposition:

• R1(A, C) ← FD: C → B cannot be enforced here (no B column)
• R2(C, B) ← FD: C → B can be enforced (C is key here)

FD AB → C cannot be enforced in either R1 or R2 alone — it spans both tables. To check it, you must JOIN R1 and R2 back.

This is why 3NF is sometimes preferred over BCNF — 3NF always preserves dependencies.

---

Denormalization

Denormalization is the intentional introduction of redundancy into a normalized database to optimize read performance.

Why Denormalize?

Reason	Explanation
Avoid expensive JOINs	Reading one table is faster than joining five
Reduce query complexity	Fewer tables = simpler queries
Improve read latency	Pre-computed data is ready to read

When to Denormalize

System Type	Normalize?	Why
OLTP (Banking, e-commerce)	Yes, high NF	Writes are frequent; integrity matters most
OLAP (Data warehouse)	No, denormalize	Reads dominate; speed matters most
Reporting	Usually denormalize	Star/snowflake schemas are denormalized

Example: Denormalization

Normalized (3NF):

Orders ──→ Order_Items ──→ Products
  │                          │
  └──────────────────────────┘

To get total sales per product: JOIN Orders + Order_Items + Products + GROUP BY + SUM.

Denormalized:

Orders
├── Order_ID
├── Customer_ID
├── Product_Name (denormalized)
├── Category (denormalized)
├── Price (denormalized)
└── Total_Sales (pre-computed)

Fast read — one table, no JOINs. But updating the product name requires updating all related order rows.

The Big Risk

Data Inconsistency. If you duplicate data for performance, you must write extra code (triggers, application logic) to ensure that when the original data changes, the duplicates are also updated.

---

Summary of Normal Forms

Normal Form	Rule	How to Fix
1NF	Atomic values, no repeating groups	Split multi-valued cells
2NF	No partial dependencies	Remove dependencies on part of composite PK
3NF	No transitive dependencies	Move inter-non-key dependencies to separate tables
BCNF	Every determinant is a candidate key	Split where non-key determines non-key

---

Interview Deep Dive

---

Key Takeaways

• BCNF: For every FD X → Y, X must be a Super Key.
• A table can be 3NF but not BCNF if it has overlapping candidate keys with inter-key dependencies.
• Lossless Join: The common attribute between decomposed tables must be a super key in at least one.
• Dependency Preservation: All FDs should be enforceable locally (3NF guarantees this; BCNF may not).
• Denormalization: Intentionally add redundancy for read performance — common in OLAP/data warehouses.
• Trade-off: Higher normalization = better integrity but more JOINs. Denormalization = faster reads but risk of inconsistency.