Questions
3–5 questions in university semester exams
Difficulty
Medium-Hard
Importance
Core topic for university DBMS papers
Overview
Normalization is the systematic process of organizing data in a database to minimize redundancy and eliminate undesirable anomalies like insertion, update, and deletion issues. It is a fundamental concept in DBMS exams that tests your ability to decompose complex relations into smaller, well-structured tables using functional dependencies.
Functional Dependencies (FD)
Functional dependencies define the relationship between attributes in a relation, specifically stating that a value of one attribute uniquely determines the value of another. Understanding the closure of an attribute set is crucial for determining candidate keys and identifying the level of normalization.
- A -> B means A functionally determines B
- Armstrong's Axioms: Reflexivity, Augmentation, Transitivity
- Trivial FD: Y is a subset of X
- Closure of X (X+) represents all attributes functionally dependent on X
- Candidate Key: Minimal set of attributes whose closure is the set of all attributes in the relation
Normal Forms: 1NF, 2NF, and 3NF
Normalization moves from 1NF to 3NF by systematically removing partial and transitive dependencies. 1NF requires atomic values, 2NF removes partial functional dependencies, and 3NF ensures no non-prime attribute is transitively dependent on the primary key.
- 1NF: Atomic values, no repeating groups
- 2NF: Relation in 1NF and no partial dependency (non-prime attributes fully dependent on candidate key)
- 3NF: Relation in 2NF and no transitive dependency
- Anomalies: Insertion, Update, and Deletion anomalies in unnormalized tables
- Prime attribute: Any attribute that is part of some candidate key
BCNF and Lossless Decomposition
BCNF is a stricter version of 3NF, addressing anomalies that occur when multiple overlapping candidate keys exist. Lossless decomposition ensures that when a relation is split, the original data can be reconstructed via natural joins without generating spurious tuples.
- BCNF Condition: For every FD X->Y, X must be a superkey
- Every BCNF relation is in 3NF, but not every 3NF is in BCNF
- Lossless Join property: R1 INTERSECT R2 -> R1 or R1 INTERSECT R2 -> R2
- Dependency Preservation: Property where all original FDs are covered by the decomposed schemas
- It is always possible to achieve 3NF with lossless join and dependency preservation
Formula Sheet
X+ (Attribute Closure calculation)
R = R1 U R2 (Decomposition rule)
R1 Intersection R2 -> R1 (Lossless Join condition)
Exam Tip
Always find all candidate keys first using attribute closure before attempting to identify which normal form a relation satisfies.
Common Mistakes
- Confusing partial dependency (part of a key) with transitive dependency (key -> non-prime -> non-prime)
- Forgetting to check for all candidate keys when testing for BCNF
- Assuming that because a decomposition is lossless, it is also dependency-preserving
More Revision Notes
Ready to test yourself?
Play topic-wise Normalization questions in Aspirant Arcade — gamified MCQ practice.
Download Free