DATABASE NORMALIZATION — COMPLETE EXAM
GUIDE
Full Marks Reference Sheet | Functional Dependencies & Normal Forms
1. Closure (X⁺)
The closure of a set of attributes X is the set of all attributes that can be functionally determined by X
using the given set of FDs.
How to Compute X⁺:
1. Start with X⁺ = X
2. Look for any FD where the left-hand side is a subset of X⁺
3. Add the right-hand side attributes to X⁺
4. Repeat until no more attributes can be added
KEY RULE: If X⁺ = all attributes in the relation → X is a Superkey
2. Candidate Key — Fast Method
5. Find all attributes that NEVER appear on the Right-Hand Side (RHS) of any FD → these MUST
be part of every candidate key
6. Find attributes that appear ONLY on the Left-Hand Side (LHS) → these MUST be in every
candidate key
7. Add minimum additional attributes until closure = all attributes
8. Compute closure once to verify
DEFINITION: Minimal Superkey = Candidate Key (no proper subset is also a superkey)
3. Superkey Count Formula
If a relation has:
• Total attributes = n
• Candidate key size = k (one candidate key)
Number of Superkeys = 2^(n − k)
NOTE: This formula applies directly when there is exactly ONE candidate key. With multiple
candidate keys, use inclusion-exclusion.
4. Prime vs. Non-Prime Attributes
Prime Attribute: Appears in AT LEAST ONE candidate key
Non-Prime Attribute: Does NOT appear in ANY candidate key
EXAM TIP: 2NF and 3NF rules apply ONLY to non-prime attributes. Always identify prime/non-prime
first.
5. Second Normal Form (2NF)
Condition: No partial dependency of any non-prime attribute on the candidate key
When to check:
• Only relevant if the candidate key is COMPOSITE (more than one attribute)
Violation — Partial Dependency:
Part of Key → Non-Prime Attribute ← VIOLATION ❌
Fix (Decomposition):
If X → Y is a partial dependency:
R1 ( X, Y )
R2 ( Full Key + all remaining attributes EXCEPT Y )
REMEMBER: A single-attribute candidate key automatically satisfies 2NF.
6. Third Normal Form (3NF)
Condition: For every FD X → Y, at least ONE of the following must hold:
• X is a Superkey ✔
• Y is a Prime attribute ✔
Violation — Transitive Dependency:
Non-Key → Non-Prime Attribute e.g., A → B → C
Fix:
Split B and C into a new table: R_new(B, C)
Original table retains: R(A, B)
QUICK TEST: If neither condition holds for any FD → Relation is NOT in 3NF.
7. Boyce-Codd Normal Form (BCNF)
Condition: For every non-trivial FD X → Y, X must be a Superkey
Violation:
If X → Y AND X is NOT a superkey → VIOLATION ❌
Fix — Decomposition:
R1 ( X, Y ) ← use the violating FD
R2 ( X + remaining attributes not in Y )
NOTE: BCNF is stricter than 3NF. A relation in BCNF is always in 3NF, but not vice versa.
CAUTION: BCNF decomposition may NOT always preserve all FDs. Mention this in exams.
8. Canonical Cover (F_c) — 3 Steps
Step 1: Split the RHS
• Convert every FD so that the right-hand side has exactly ONE attribute
A → BC becomes A → B and A → C
Step 2: Remove Redundant FDs
• For each FD, check if it can be derived from the remaining FDs
• If yes → remove it (it is redundant)
Step 3: Remove Extra Attributes from LHS
• For each FD X → Y where X has multiple attributes:
• Try removing each attribute from X one at a time
• If the closure still gives Y → that attribute is extraneous, remove it
RESULT: The final minimal set of FDs is the Canonical Cover F_c.
9. Fourth Normal Form (4NF)
Condition: For every non-trivial Multi-Valued Dependency (MVD) X →→ Y, X must be a Superkey
Violation:
X →→ Y AND X is NOT a superkey → VIOLATION ❌
Fix — Decomposition:
R1 ( X, Y )
R2 ( X, remaining attributes )
REMEMBER: Every FD is also an MVD. 4NF is stricter than BCNF.
10. Order to Solve Any Normalization Question
Always follow this exact sequence:
9. Find all Candidate Keys (compute closures)
10. Mark Prime and Non-Prime Attributes
11. Check 2NF (partial dependencies — only if composite key)
12. Check 3NF (transitive dependencies)
13. Check BCNF (every determinant must be a superkey)
14. Check 4NF (multi-valued dependencies)
ALWAYS IN THIS ORDER — never skip steps!
11. Quick Diagnosis Table
Condition Normal Form to Check
Composite key present 2NF — check for partial dependencies
Transitive dependency A→B→C 3NF — split transitive chain
Determinant is not a superkey BCNF — decompose using that FD
Independent multivalued attributes 4NF — split MVD
EXAM FORMULA SUMMARY
X⁺ = All Attributes → Superkey | Minimal Superkey = Candidate Key |
Superkeys = 2^(n−k)
2NF: No partial dep. | 3NF: X is superkey OR Y is prime | BCNF: X must
be superkey | 4NF: X must be superkey (for MVD)