Normalization Theory
Functional dependencies and the ladder of normal forms that tell you when a table should be split — and when to deliberately walk it back. Interviewers use 'explain 3NF vs BCNF' as a fast litmus for whether you understand why schemas are shaped the way they are.
part ofRelational database conceptsoverview, primer and where to startread it →on this pageshowhide
explore
- Functional Dependencies & Keys6 questions
- Redundancy & Update Anomalies4 questions
- First Normal Form (1NF)5 questions
- Second Normal Form (2NF)4 questions
- Third Normal Form (3NF)4 questions
- Boyce–Codd Normal Form (BCNF)4 questions
- Higher Normal Forms (4NF/5NF)4 questions
- Denormalization Trade-offs4 questions
- AI & Data Scientistrole
- AI Engineerrole
- BI Analystrole
- Backend Developerrole
- Computer Scienceskill
- Cyber Security Expertrole
- Data Analystrole
- Data Engineerrole
- Forward Deployed Engineerrole
- Full Stack Developerrole
- Java Backend Developerrole
- Java SDETrole
- Kotlin Backend Developerrole
- MLOps Engineerrole
- Machine Learning Engineerrole
- PostgreSQL DBArole
- QA Engineerrole
- SQLskill
- Server-Side Game Developerrole
- Software Architectrole
questions
page 2 of 2When you split one table into two, which property of the functional dependencies guarantees that the split is lossless - that rejoining the fragments cannot invent rows - and what does 'dependency preserving' add on top of that?
basics
~20 sA split of R into R1 and R2 is lossless when the shared attributes functionally determine all of R1 or all of R2 - the overlap must be a key of at least one fragment. Dependency preservation additionally requires that every original dependency can be checked inside a single fragment.
What is a join dependency, and what does Fifth Normal Form (also called Project-Join Normal Form) require? Describe the kind of table that can be in 4NF but not in 5NF.
basics
~20 sA join dependency says a table equals the join of several of its projections. 5NF requires every nontrivial join dependency to be implied by the candidate keys. The classic violation is a three-way relationship, such as supplier-part-project, that is exactly reconstructable from its three pairwise projections because of a cyclic business rule.
For a schema you are designing, how do you decide whether to decompose all the way to Boyce-Codd Normal Form or stop at Third Normal Form?
basics
~20 sDecide per table. Go to BCNF when the redundant fact changes often enough that multi-row updates risk inconsistency. Stay at 3NF when reaching BCNF would lose a dependency you must enforce and no key on a single table could replace it. Most tables never present the choice.
Your team keeps adding precomputed columns and summary tables to the transactional database. How do you govern that so the schema stays trustworthy?
basics
~20 sTreat every redundancy as a contract, not a column: named owner, sync mechanism, stated staleness, reconciliation query with alerting, and the measurement that justified it. Keep a register of them, review it periodically, and remove ones whose justification no longer measures. Undocumented copies are how a schema stops being trustworthy.
You are redesigning a schema for a system that has been running for years, and the only artefacts available are production data and conversations with domain experts. How do you establish which functional dependencies genuinely hold, and what goes wrong if you get them wrong?
basics
~20 sMine the data for candidate dependencies, then confirm each with a domain rule - data can only rule candidates out. Watch for time-varying values, tenant scope, nulls and small samples. A wrong dependency yields a wrong key and an unsafe split that silently discards rows.
showing 31–35 of 35