- LPU MCA Semester 1
- CAP570
- Unit 3
CAP570 · Unit 3: Database normalisation
BCNF and denormalization
10 practice questions · in the MTE and ETE syllabus
Key points
- BCNF requires every determinant to be a superkey
- 3NF but not BCNF is possible when a prime attribute is determined
- Denormalization trades redundancy for read speed
- BCNF decomposition may lose dependency preservation
Boyce-Codd normal form (BCNF) is stricter than 3NF: for every non-trivial FD X → Y, X must be a superkey. It removes the 3NF exception that lets a non-superkey determine a prime attribute.
Example: R(A, B, C) with AB → C and C → B has keys AB and AC. B is prime, so the relation is in 3NF, but C is not a superkey, so it is not in BCNF. Splitting to R1(C, B) and R2(A, C) is lossless, but AB → C cannot then be checked in one table, so BCNF may cost dependency preservation.
Denormalization deliberately adds redundancy, for example copying CustomerName into every order row, to cut joins and speed up read-heavy queries such as reports. The price is more storage, more update anomalies and extra code to keep copies in step. A common compromise is to keep core tables normalised and add a refreshed summary table.
Exam trap: denormalization is a performance decision made after normalising, not a substitute for design.
Try two questions
A designer copies CustomerName into every row of the Orders table. Which risk does this create?
- Failure of atomicity, because copied names cannot be rolled back
- Insertion of null keys, because copied names replace identifiers
- Violation of first normal form, because names are no longer atomic
- Update anomalies, because a rename must change many rows
Show the answer
D. A repeated value must be changed everywhere, or the copies disagree.
Why not A: Atomicity concerns transactions, not duplicated data.
Why not B: Names do not replace keys, so key nulls are not caused.
Why not C: A name is still a single atomic value.
R(A, B, C) has FDs AB → C and C → B. What is its highest normal form?
- 3NF but not BCNF
- 2NF but not 3NF
- 1NF but not 2NF
- BCNF
Show the answer
A. The keys are AB and AC, so B is prime; C → B has a non-superkey determinant, which BCNF forbids.
Why not B: B is prime, so C → B does not break 3NF.
Why not C: No non-prime attribute depends on part of a key here.
Why not D: C is not a superkey, so C → B violates BCNF.