Autocoder

Normalization Interview Questions with Worked Examples

Last updated 2026-11-19

Normalization questions are rarely about reciting definitions. Interviewers hand you a table and a set of functional dependencies and ask for the candidate key, the highest normal form and a decomposition. This page therefore leans on worked examples: an orders table taken from unnormalized form to 3NF, a BCNF case where dependency preservation is lost, and an attribute closure calculation.

If you need the broader database picture first, start with DBMS interview questions. Keys are covered in DBMS keys, and the study approach is in how to crack a DBMS interview.

Core ideasFunctional dependencies, keys, anomalies, decomposition
Normal forms1NF, 2NF, 3NF, BCNF, 4NF, 5NF
Typical taskFind the key, name the normal form, decompose

A procedure that works under pressure

Write the dependencies down, compute the candidate key using attribute closure, then mark attributes as prime (part of some candidate key) or non-prime. 2NF fails if a non-prime attribute depends on part of a candidate key. 3NF fails if a non-prime attribute depends on another non-prime attribute. BCNF fails if any determinant is not a superkey.

Always finish by checking two properties of your decomposition: is the join lossless, and are the dependencies preserved? A candidate who states both checks sounds far more confident than one who stops at the tables.

Quick reference

Normalization Interview Questions with Worked Examples: questions with worked answers

  1. 1

    What is normalization and why do we do it?

    easy

    Normalization is the process of organising tables and columns so that each fact is stored in one place. It removes redundancy and with it the insertion, update and deletion anomalies. It is done by decomposing a relation into smaller relations using functional dependencies, such that no information is lost. The cost is more joins at query time, which is why reporting systems sometimes denormalize on purpose.

  2. 2

    Explain insertion, update and deletion anomalies with an example.

    easy

    Take student_course(student_id, student_name, course_id, course_name). Insertion anomaly: a new course cannot be recorded until a student enrols, because the key needs a student_id. Update anomaly: if a course is renamed, every row for that course must change, and missing one leaves inconsistent data. Deletion anomaly: deleting the only student of a course also deletes the fact that the course exists. Splitting into student, course and enrolment tables removes all three.

  3. 3

    What is 1NF? Is a column holding "9876, 9123" in 1NF?

    easy

    A relation is in first normal form when every attribute holds a single atomic value and there are no repeating groups or arrays of values within a row. A column storing two phone numbers in one cell violates 1NF. Fix it by moving phones into a separate table phone(person_id, number) or by giving each number its own row. Note that "atomic" is relative to how the application uses the value; a full address string is fine if you never query its parts.

  4. 4

    What is 2NF? When is a relation automatically in 2NF?

    medium

    A relation is in 2NF if it is in 1NF and no non-prime attribute depends on a proper subset of a candidate key (a partial dependency). If every candidate key is a single attribute, no proper subset can determine anything, so the relation is automatically in 2NF. Partial dependencies only arise with composite keys. Example: in order_item(order_id, product_id, qty, product_name), product_name depends on product_id alone, so product_name must move to a product table.

  5. 5

    What is 3NF? State the formal condition.

    medium

    A relation is in 3NF if for every non-trivial functional dependency X to A, either X is a superkey or A is a prime attribute. In plain words, non-prime attributes must depend on the key, the whole key and nothing but the key. Example: employee(emp_id, dept_id, dept_name) has emp_id to dept_id and dept_id to dept_name, a transitive dependency, so dept_name belongs in department(dept_id, dept_name).

  6. 6

    What is BCNF and how is it stricter than 3NF?

    medium

    BCNF requires that for every non-trivial dependency X to A, X is a superkey, with no exception for prime attributes. 3NF allows a dependency whose right side is prime; BCNF does not. The two differ only when a relation has overlapping composite candidate keys. Every BCNF relation is in 3NF but not the reverse. BCNF decomposition is always lossless but may not preserve all dependencies; 3NF synthesis always achieves both.

  7. 7

    Take an unnormalized orders table to 3NF, showing each step.

    hard

    Start with orders(order_id, customer_id, customer_name, customer_city, product_id, product_name, price, qty), key (order_id, product_id). Dependencies: order_id to customer_id; customer_id to customer_name, customer_city; product_id to product_name, price; (order_id, product_id) to qty. Not 2NF: customer_id and the product attributes depend on part of the key. Splitting gives orders(order_id, customer_id, customer_name, customer_city), product(product_id, product_name, price) and order_item(order_id, product_id, qty). Still not 3NF: customer_name and customer_city depend on customer_id, not order_id. Final 3NF schema is customer, orders, product and order_item. In a real shop you would also copy price into order_item to preserve the price at purchase time, which is a deliberate historical fact, not redundancy.

    sql
    CREATE TABLE customer (customer_id INT PRIMARY KEY, name VARCHAR(80), city VARCHAR(60));
    CREATE TABLE product  (product_id  INT PRIMARY KEY, name VARCHAR(80), price DECIMAL(10,2));
    CREATE TABLE orders   (order_id    INT PRIMARY KEY,
                           customer_id INT REFERENCES customer(customer_id));
    CREATE TABLE order_item (order_id INT REFERENCES orders(order_id),
                             product_id INT REFERENCES product(product_id),
                             qty INT, PRIMARY KEY (order_id, product_id));
  8. 8

    What is attribute closure? Find the candidate key of R(A,B,C,D,E) with AB to C, C to D, D to E.

    medium

    The closure X+ is the set of all attributes functionally determined by X, computed by repeatedly adding the right side of any dependency whose left side is already in the set. A and B never appear on a right-hand side, so every key must contain both. Compute (AB)+: start {A,B}; AB to C adds C; C to D adds D; D to E adds E, giving {A,B,C,D,E}, which is every attribute. So AB is a superkey, minimal because neither A+ = {A} nor B+ = {B} is everything, and it is the only candidate key. Prime attributes: A and B.

  9. 9

    R(Student, Subject, Teacher) has (Student, Subject) to Teacher and Teacher to Subject. Which normal form is it in, and how do you decompose it?

    hard

    Candidate keys are (Student, Subject) and (Student, Teacher), since (Student, Teacher)+ includes Subject via Teacher to Subject. All three attributes are prime, so there are no partial or transitive dependencies on non-prime attributes: the relation is in 3NF. It is not in BCNF because Teacher to Subject has a left side, Teacher, that is not a superkey. Decompose into R1(Teacher, Subject) and R2(Student, Teacher). The join is lossless because the common attribute Teacher is a key of R1. But (Student, Subject) to Teacher cannot be checked in either table alone, so that dependency is not preserved.

  10. 10

    How do you test whether a decomposition is lossless?

    hard

    For a binary decomposition of R into R1 and R2, it is lossless if and only if the common attributes R1 intersect R2 functionally determine all of R1 or all of R2, i.e. the intersection is a superkey of at least one part. Example: R(A,B,C) with B to C split into (A,B) and (B,C): the intersection B determines C, so it is lossless. Splitting R(A,B,C) with no dependencies into (A,B) and (B,C) is lossy because joining on B creates spurious tuples. For more than two parts, use the tableau (chase) algorithm.

  11. 11

    What is dependency preservation, and why can BCNF fail it?

    hard

    A decomposition preserves dependencies if the union of the dependencies that can be checked within each sub-relation implies the original set. If a dependency spans two tables, enforcing it needs a join on every insert or update. BCNF can fail this, as in the Student, Subject, Teacher example, because the dependency Student, Subject to Teacher uses attributes now split apart. This is the standard argument for settling on 3NF when preservation matters more than removing every last redundancy.

  12. 12

    What is a minimal cover, and why compute it?

    medium

    A minimal (canonical) cover is a simplified equivalent set of functional dependencies where every right side is a single attribute, no left-side attribute is redundant and no dependency is redundant. It is the first step of the 3NF synthesis algorithm: compute the cover, make one relation per left-side group, add a relation containing a candidate key if none exists, and remove relations that are subsets of others. The result is lossless and dependency preserving.

  13. 13

    What is a multivalued dependency and what is 4NF?

    hard

    A multivalued dependency X to-to Y means that for each X value, the set of Y values is independent of the other attributes. In person_skill_language(person, skill, language), a person's skills and languages are independent, so every combination must be stored: 2 skills and 3 languages require 6 rows. That is redundancy BCNF cannot detect. A relation is in 4NF if it is in BCNF and every non-trivial MVD has a superkey on its left. Decompose into person_skill(person, skill) and person_language(person, language).

  14. 14

    When would you deliberately denormalize?

    medium

    Denormalize when measured read performance matters more than update simplicity: reporting tables, data warehouses with star schemas, cached counters such as comment_count, or precomputed totals. The price is extra storage and the need to keep copies consistent, using triggers, application logic or batch refresh. Do it after profiling a real query, not by default, and document which table is the source of truth. For a related discussion in distributed stores see NoSQL questions.

Frequently asked questions

Which normal form do most real databases target?

Third normal form, or BCNF where it costs nothing. Higher forms such as 4NF and 5NF matter only when multivalued or join dependencies exist.

Does 3NF always preserve dependencies?

Yes. The standard 3NF synthesis algorithm produces a decomposition that is both lossless and dependency preserving. BCNF decomposition is lossless but may lose dependencies.

Is a prime attribute the same as a key attribute?

Yes in most textbooks: a prime attribute is one that belongs to at least one candidate key; others are non-prime.

How do I find all candidate keys quickly?

Attributes never on a right-hand side must be in every key; attributes only on right sides are in none. Start with the first set, take its closure, and add attributes until the closure covers the relation.

Related guides

More in CS Fundamentals Interview Questions

See all in this section