Normal forms: from first normal form to BCNF
Goals and model
Classify a schema using all candidate keys and implied FDs, diagnose redundancy, and distinguish 3NF from BCNF. Assume classical relations and that the given FDs capture the constraints relevant to the question. Functional dependencies determine normal-form classification; a particular tiny instance may hide the redundancy.
1. Why normalization matters
Consider Enrol(sid,cid,sname,ctitle,grade), with one row per student–course pair and F={sid→sname,cid→ctitle,sid cid→grade}. The key is (sid,cid). A student's name repeats for every course and a course title repeats for every student. Updating only one copy can create inconsistency; deleting the last enrollment can lose course-description information; inserting a course before any student enrolls is awkward. These are update, deletion and insertion anomalies. Decomposition separates facts according to what determines them, but must later be checked for losslessness and enforceability.
2. First and second normal forms
1NF requires attributes to take single values from their modeled domains, rather than a repeating list of course values in one cell. Atomicity depends on the chosen domain and operations; it does not mean every character string must be split into characters. In this lesson the listed schemas are already in 1NF.
An attribute is prime if it belongs to at least one candidate key. It is non-prime if it belongs to no candidate key. A 1NF relation is in 2NF when no non-prime attribute depends on a proper subset of any candidate key. Inspect every candidate key, not just the designated primary key. If all candidate keys are single-attribute keys, there is no nonempty proper key subset that can cause the usual partial-dependency pattern; empty-determinant dependencies still require attention in a fully general formal treatment.
In Enrol, sid→sname and cid→ctitle are partial dependencies on proper subsets of key (sid,cid), and the right attributes are non-prime. It is not in 2NF. A natural decomposition is Student(sid,sname), Course(cid,ctitle), and EnrolGrade(sid,cid,grade). Correctness of decompositions is the next lesson's topic, not guaranteed just by a table's new appearance.
3. Third normal form
For every nontrivial FD X→a in F+ with singleton right side, 3NF requires either X to be a superkey or a to be prime. The second alternative matters; the shorthand “remove transitive dependencies” is not a complete definition when candidate keys overlap.
Example: R(A,B,C), F={A→B,B→C}. A is the only candidate key. B→C has a non-superkey determinant and non-prime right side C, so 3NF fails. Since A is a singleton key and no empty-determinant FD is implied, this example is in 2NF. The chain A→B→C stores a non-key fact B→C repeatedly. Decomposing into AB and BC removes this repetition.
4. Boyce–Codd normal form
BCNF requires every nontrivial FD X→Y in F+ to have X as a superkey. It has no prime-right-side exception. Thus BCNF implies 3NF, but 3NF need not imply BCNF. For checks using a supplied generating FD set, testing that every nontrivial generator has a superkey determinant is sufficient for BCNF; key computation and implication reasoning must still use the full set.
Worked 3NF-but-not-BCNF case: R(A,B,C), F={AB→C,C→B}. AB+ is ABC; AC+ is ABC because C→B. Neither A, B nor C alone is a key. Candidate keys are AB and AC, so all three attributes are prime. AB→C has a key determinant; C→B has a non-key determinant but prime right side B, permitted in 3NF. BCNF fails on C→B because C+={B,C}, missing A. The 3NF conclusion holds for all implied FDs because every attribute is prime.
5. A reliable classification workflow
- State 1NF and the relevant dependency model.
- Compute every candidate key and mark prime attributes.
- For 2NF, seek a proper key subset determining a non-prime attribute.
- For 3NF, seek X→a where X is not a superkey and a is non-prime.
- For BCNF, any nontrivial dependency with a non-superkey determinant is a violation.
Worked comparison: R(A,B,C,D), F={AB→C,C→D}. A and B never appear on a right side, so both are mandatory. AB determines all four and is the sole key. No proper subset of AB determines C or D, so 2NF holds. C→D violates 3NF (C is not a superkey, D is non-prime); hence the highest of these four normal forms is 2NF. The implied AB→D does not repair C→D.
Pitfalls and boundary
- Prime means membership in any candidate key, not just the primary key.
- A nontrivial dependency on a non-key determinant is not automatically a 3NF violation if its right side is prime.
- BCNF does not guarantee the absence of redundancy caused by independent multivalued facts; multivalued dependencies and 4NF are a separate extension.
- More decomposition is not always better: lossless reconstruction and dependency preservation are separate requirements.
Original practice: six single-correct MCQs
These original teaching questions are not previous-year questions. Choose exactly one answer.
P01. An attribute is prime if it belongs to:
A. Every candidate key B. At least one candidate key C. Only the primary key D. No superkey
Answer: B
A: Membership in every key is unnecessary.
B: Any candidate-key membership is sufficient.
C: Alternate candidate keys also count.
D: A prime attribute necessarily occurs in a candidate key and hence a superkey.
P02. R(A,B,C), F={AB→C,A→C}; AB is the sole candidate key. Highest form among 1NF,2NF,3NF,BCNF, assuming 1NF?
A. 1NF B. 2NF C. 3NF D. BCNF
Answer: A
A: A proper key subset A determines non-prime C, violating 2NF.
B: The partial dependency A→C prevents 2NF.
C: 3NF also fails: A is not a superkey and C is non-prime.
D: A→C has a non-superkey determinant.
P03. R(A,B,C), F={A→B,B→C}, no other non-implied FDs. Highest form?
A. 1NF only B. 2NF C. 3NF D. BCNF
Answer: B
A: The singleton key A has no partial non-prime dependency here.
B: 2NF holds but B→C violates 3NF.
C: B is not a superkey and C is non-prime.
D: B→C also violates BCNF.
P04. In 3NF, a nontrivial X→a with X not a superkey is allowed when:
A. a is prime B. a is non-prime C. X is empty in every case D. The relation has few rows
Answer: A
A: This is the prime-right-side exception.
B: A non-prime a makes it a 3NF violation.
C: An empty determinant is not automatically exempt.
D: Row count does not determine schema normal form.
P05. R(A,B,C), F={AB→C,C→B}, keys AB and AC. Which statement holds?
A. BCNF holds B. 3NF holds but BCNF fails C. 2NF fails D. C is a superkey
Answer: B
A: C→B violates BCNF because C cannot determine A.
B: All attributes are prime, but C is not a superkey.
C: All attributes are prime, so there is no non-prime partial dependency.
D: C+={B,C}, missing A.
P06. Which implication always holds in the classical FD-based hierarchy?
A. 3NF implies BCNF B. 2NF implies 3NF C. BCNF implies 3NF D. 1NF implies 2NF
Answer: C
A: 3NF has a prime-right-side exception.
B: A non-key transitive dependency can violate 3NF.
C: Every BCNF determinant meets the superkey alternative of 3NF.
D: Partial dependencies can remain in 1NF.
Sources and scope
Aligned to the 2026 course. This lesson covers the stated slice, not the entire Databases syllabus. All numerical examples and questions are original. No claim of 2027 completeness.
Sign in to keep your progress. Sign in