Database Normalization and Normal Forms - Quiz
Total: 7 questions
1. What is database normalization, how many normal forms are there, and what problems does it solve?
What is database normalization, how many normal forms are there, and what problems does it solve?
Database normalization is a step-by-step process of splitting tables into smaller ones so that every fact is stored in exactly one place and no information is lost.
Textbooks usually list six levels: 1NF, 2NF, 3NF, BCNF (Boyce–Codd normal form), 4NF and 5NF; each one is stricter than the previous. 6NF and domain-key normal form (DKNF) also exist but are rare in business systems. In practice 3NF is almost always enough, sometimes BCNF.
Normalization removes three kinds of anomalies:
- update — the same value is stored in several rows, and only some of the copies get changed;
- insert — a fact cannot be added until related data exists (a new position without an employee);
- delete — deleting a row destroys unrelated information (the last employee in a position is deleted and the salary of the position is lost).
2. What is a functional dependency? What is the difference between a candidate key, a primary key and a superkey?
What is a functional dependency? What is the difference between a candidate key, a primary key and a superkey?
A functional dependency A → B means that the value of A uniquely determines the value of B, for example Employee number → Last name. All normal forms except the first are defined through it.
- A candidate key is a minimal set of columns on which all other columns functionally depend. A table can have several.
- The primary key is one of the candidate keys, chosen as the main one.
- A superkey is any set of columns that identifies a row uniquely: a candidate key plus any extra columns.
- A prime attribute belongs to at least one candidate key; a non-prime (non-key) attribute belongs to none.
Strictly speaking, the 2NF and 3NF rules apply to all candidate keys, not only to the primary key.
3. What are the criteria of first, second and third normal form (1NF, 2NF, 3NF)?
What are the criteria of first, second and third normal form (1NF, 2NF, 3NF)?
- 1NF — the table has a key, all rows are distinct and every cell holds an atomic value. A comma-separated list of products in one cell violates 1NF: each product needs its own row.
- 2NF — the table is in 1NF, and every non-key column depends on the whole key, not on part of it. Violation example: the key is (Part, Warehouse), but Warehouse address depends on Warehouse alone. If the key is a single column, there are no other composite candidate keys and the table is in 1NF, it is automatically in 2NF — that is all an auto-increment
idgives you. - 3NF — the table is in 2NF, and no non-key column depends on the key transitively, through another non-key column. Violation example: Employee number → Position code → Salary (an
idcolumn does not fix this).
In short: 1NF — atomic values, 2NF — dependency on the whole key, 3NF — only on the key, with no intermediaries.
4. What is a transitive dependency, and how do you remove it when moving to 3NF?
What is a transitive dependency, and how do you remove it when moving to 3NF?
A transitive dependency is a dependency of a column on the key that goes not directly but through an intermediate non-key column: if A → B and B → C, then C depends transitively on A (provided that B is not a key and does not determine A, and C is a non-key column). Example: Employee number → Position code → Salary. Such a chain violates 3NF: the salary is repeated for every employee in the same position.
It is removed by decomposition:
- find the chain A → B → C;
- create a new table from B and C with B as the primary key (Positions: position code, salary);
- remove C from the original table, keeping B as a foreign key.
The original data is rebuilt with a JOIN, and a salary change is one UPDATE. A sign of a transitive dependency in the data: the same pairs of non-key values repeat in different rows.
5. What is Boyce–Codd normal form (BCNF), and how does it differ from 3NF?
What is Boyce–Codd normal form (BCNF), and how does it differ from 3NF?
BCNF is a stronger version of 3NF: for every non-trivial functional dependency X → Y the left side X must be a superkey.
3NF allows a dependency X → Y where X is not a superkey if Y is a prime attribute. BCNF makes no such exception. So every table in BCNF is in 3NF, but not the other way round. The two can differ only when a table has several overlapping composite candidate keys; with a single candidate key, 3NF and BCNF coincide.
Example: a table (Student, Subject, Teacher) where each teacher teaches one subject. The candidate keys are (Student, Subject) and (Student, Teacher). The dependency Teacher → Subject violates BCNF. Decomposition: Teachers (Teacher, Subject) and Enrolment (Student, Teacher). The price: the rule “a student has one teacher per subject” can no longer be expressed with a key and needs a trigger or application code.
6. What do fourth (4NF) and fifth (5NF) normal forms require?
What do fourth (4NF) and fifth (5NF) normal forms require?
4NF requires BCNF and no non-trivial multivalued dependencies: one table must not store two independent multivalued sets. Example: the skills and languages of an employee in a table (Employee, Skill, Language) force you to store every combination — 3 skills and 2 languages give 6 rows instead of 5 facts. The fix is two tables: Employee–Skill and Employee–Language.
5NF requires every join dependency to follow from the candidate keys. The classic example is a table (Supplier, Part, Project) with a cyclic rule: it can be rebuilt without loss only by joining all three two-column projections (any two are not enough), so it is stored as three tables.
In practice both forms are rarely needed: if independent sets already live in separate junction tables, the schema is in 4NF without extra effort.
7. What is denormalization, why is it used, and what are its risks?
What is denormalization, why is it used, and what are its risks?
Denormalization is a deliberate violation of normal forms: redundant data is put back into tables to make reads faster. It is the step after normalization: normalize the schema first, measure, and only then denormalize the bottlenecks selectively.
Why: fewer JOINs over large tables, fast reports and aggregates, read-heavy workloads.
Techniques: a redundant column (the customer name in orders), a stored aggregate (total_amount, orders_count), a summary table or materialized view (sales_by_month), an array or JSON in a column (violates 1NF). MySQL has no materialized views — they are imitated with a summary table rebuilt on a schedule (EVENT) or maintained by triggers.
Risks: update anomalies (copies diverge), more data and larger indexes, more complex writes.
Justified when there is a measured performance problem that indexes did not solve; when data is read often but changes rarely; when you build a data warehouse or a reporting database. The price in an order line (the price at the time of purchase) is not denormalization — it is a separate fact.