Normalization in DBMS Interview Questions

Normalization splits tables so each fact is stored once, removing redundancy and the insert, update and delete anomalies it causes. You decompose by functional dependencies until each table reaches the target form, usually 3NF or BCNF, at the cost of more joins. The mnemonic: every non-key column depends on "the key, the whole key, and nothing but the key".

They are the three ways a table that repeats a fact goes wrong. In enrollments(student_id, student_name, course_id, course_fee), you cannot add a course's fee until someone enrolls (insertion); changing a fee means updating every row, and missing one leaves two fees (update); deleting a course's last student loses its fee (deletion). The fix stores each fact once: students, courses and enrollments.

It prints 2: the table now puts Sales in both Pune and Mumbai. dept_city depends on dept, not on the employee, so it is stored once per employee and a partial update contradicts itself. The transitive dependency emp → dept → dept_city breaks 3NF; storing the city once in departments(dept, city) makes the move one update.

A table is in 1NF when every column holds one atomic value and there are no repeating groups such as phone1, phone2.

-- not 1NF: skills is a list inside one column
CREATE TABLE dev_bad (name TEXT, skills TEXT);
INSERT INTO dev_bad VALUES ('Asha', 'sql,python'), ('Ravi', 'java,sql');

-- 1NF: one row per value
CREATE TABLE dev_skills (name TEXT, skill TEXT, PRIMARY KEY (name, skill));
INSERT INTO dev_skills VALUES ('Asha', 'sql'), ('Asha', 'python'),
                              ('Ravi', 'java'), ('Ravi', 'sql');

SELECT skill, count(*) AS devs FROM dev_skills GROUP BY skill ORDER BY skill;

With a list column, "who knows SQL" needs LIKE '%sql%', which also matches mysql and can use neither an index nor a foreign key. With one row per value it is WHERE skill = 'sql'.

A table is in 2NF when it is in 1NF and no non-prime column depends on only part of a candidate key (a partial dependency). In enrollments(student_id, course_id, student_name, grade), student_name depends on student_id alone, so it repeats for every course; move it to students. A table whose candidate keys are all single columns is automatically in 2NF.

A table is in 3NF when it is in 2NF and no non-prime column depends on a key through another non-key column (a transitive dependency, A → B → C). In employees(emp_id, dept_id, dept_name), dept_name depends on emp_id only through dept_id; move it to departments. Formally, for every non-trivial X → A, X is a super key or A is prime.

BCNF requires every non-trivial FD X → Y to have a super key on the left. 3NF also allows it when Y is prime, so every BCNF table is in 3NF but not the reverse.

Example: teaching(student, course, instructor), where each instructor teaches one course, so instructor → course. All columns are prime, so 3NF holds, but instructor is not a super key. Splitting into (instructor, course) and (student, instructor) is lossless but loses {student, course} → instructor. A lossless, dependency-preserving 3NF decomposition always exists; a BCNF one may not.

X → Y means rows with the same X have the same Y: emp_id → name holds, while name → emp_id fails if names repeat. Armstrong's axioms derive every implied FD:

1. Reflexivity: if Y ⊆ X, then X → Y.
2. Augmentation: if X → Y, then XZ → YZ.
3. Transitivity: if X → Y and Y → Z, then X → Z.

They are sound and complete; union and decomposition follow from them.

Compute closures: X is a super key when X+ holds every attribute, and a candidate key when no subset of it does. An attribute missing from every right side is in every key. For R(A, B, C, D, E) with A → B, B → C, CD → E, those are A and D, and AD+ = ABCDE, so AD is the only key:

from itertools import combinations

attrs = "ABCDE"
fds = [("A", "B"), ("B", "C"), ("CD", "E")]

def closure(xs):
    result = set(xs)
    changed = True
    while changed:
        changed = False
        for left, right in fds:
            if set(left) <= result and not set(right) <= result:
                result |= set(right)
                changed = True
    return "".join(sorted(result))

print("A+ =", closure("A"))
print("AD+ =", closure("AD"))

keys = []
for size in range(1, len(attrs) + 1):
    for combo in combinations(attrs, size):
        if closure(combo) == attrs and not any(set(k) <= set(combo) for k in keys):
            keys.append("".join(combo))
print("candidate keys:", keys)

A → B is a partial dependency on AD, so R is not in 2NF.

A decomposition is lossless when joining the parts gives back exactly the original rows. The test: the shared attributes are a super key of at least one part. Splitting works(emp, dept, city) on dept, which does not determine city, is lossy:

CREATE TABLE works (emp TEXT, dept TEXT, city TEXT);
INSERT INTO works VALUES ('Asha', 'Sales', 'Pune'), ('Ravi', 'Sales', 'Delhi');

-- decompose on dept, which is not a key of either part
CREATE TABLE emp_dept AS SELECT emp, dept FROM works;
CREATE TABLE dept_city AS SELECT DISTINCT dept, city FROM works;

SELECT e.emp, d.city
FROM emp_dept e JOIN dept_city d ON e.dept = d.dept
ORDER BY e.emp, d.city;

Four rows come back from two; Asha in Delhi never existed. Splitting on emp is lossless. Dependency preservation is the second check: desirable, though BCNF cannot always keep it.

4NF removes multivalued dependencies and 5NF join dependencies; both are about independent facts in one table. In teacher_info(teacher, subject, language), subjects and languages are unrelated lists, so 2 subjects and 3 languages need 6 rows. The table is in BCNF (its key is all three columns) yet redundant, and 4NF splits it into teacher_subjects and teacher_languages. 5NF says a table cannot be split into three or more parts that join back losslessly unless the keys imply the split (the supplier, part, project case). 5NF problems are rare.

Denormalization adds redundancy on purpose, such as a stored order_total or a reporting star schema, to make reads faster, at the cost of keeping copies in sync on writes. Update the copy in the same transaction, with a trigger, or rebuild it on a schedule (a materialized view). Normalize first; denormalize specific read paths after measuring.