Normalization & Database Design

1. What is Normalization, and what is the primary goal of applying it to a database design?

Normalization is the process of organizing a database into tables so that data is stored only once and dependencies make sense. The goal is to cut down redundancy and improve integrity.

Why redundancy is bad: Suppose the same customer address appears in 20 order rows. Update it in one place and miss it in another, and now you have two addresses for the same person. Normalization splits that repeating information into its own table and references it with a key, so the address is stored exactly once.

The normal forms, from 1NF up to 5NF, each remove one more kind of problem:

• 1NF removes repeating groups.
• 2NF removes partial dependencies.
• 3NF removes transitive dependencies.

For most real-world designs, reaching 3NF is enough. The trade-off is performance — heavily normalized databases need more JOINs, so sometimes you deliberately denormalize for read speed.

2. What is Normalization and its primary purpose?

Normalization is the practice of structuring tables to store each piece of data only once. Its main goal is to remove redundant data and keep it consistent.

The problem it solves: Imagine an address stored in ten different order records. Update it in five and forget the rest — the data is now inconsistent. Normalization moves the address to a customers table and references it by ID, so there’s a single source of truth.

Normalization also protects against anomalies:

• Insert anomaly — can’t add a record because part of its data is missing.
• Update anomaly — change data in one place but not another.
• Delete anomaly — deleting a record accidentally deletes unrelated data.

Progressing through 1NF → 2NF → 3NF each removes a class of these problems.

3. What is 1NF, 2NF, and 3NF?

These are the first three normal forms — successive stages of cleaning up a table design.

1NF — eliminate repeating groups. Every cell holds a single value, not a list. If an order row has products = "Phone, Laptop, Tablet", that’s a violation. Split each product into its own row.

2NF — eliminate partial dependencies. Relevant only for composite primary keys. Every non-key column must depend on the whole key, not just part of it. In an OrderDetails(OrderID, ProductID, ProductName) table, ProductName depends only on ProductID, not the full key — move it out.

3NF — eliminate transitive dependencies. Non-key columns shouldn’t depend on other non-key columns. In Employee(ID, Department, DepartmentHead), DepartmentHead depends on Department, not the ID. Move departments to their own table.

Think of it as a ladder: 1NF kills lists, 2NF kills half-dependencies, 3NF kills indirect dependencies.

4. What is Denormalization?

Denormalization is the opposite of normalization — you intentionally add controlled redundancy to a design.

Why would you do that? Heavy normalization means many tables and expensive JOINs. When read performance matters more than write efficiency, you might duplicate a column to avoid a JOIN on every query.

Example: An orders table might store customer_name directly, even though it also lives in customers. Saves a JOIN when showing orders, at the cost of keeping the name in sync.

The trade-off:

• Normalized: clean, consistent, but slower reads.
• Denormalized: faster reads, but you must manage the duplicated data carefully.

Denormalization is a deliberate, measured choice for read-heavy systems — not sloppy design.

5. What distinguishes 3NF (Third Normal Form) from 2NF (Second Normal Form)?

The two forms target different kinds of bad dependencies.

• 2NF removes partial dependencies — where a non-key column depends on only part of a composite primary key.
• 3NF removes transitive dependencies — where a non-key column depends on another non-key column rather than the primary key.

Example of a transitive dependency: In Employee(EmpID, DeptID, DeptHead), DeptHead depends on DeptID, which is itself a non-key column. That’s a transitive dependency on EmpID. Fix: move department details (including DeptHead) into a separate Departments table.

So: 2NF kills half-key dependencies, 3NF kills indirect ones.

6. What is the difference between a Functional Dependency and a Trivial Functional Dependency?

A functional dependency X → Y means: for every row, the value of Y is uniquely determined by the value of X. Same X always gives the same Y.

A dependency X → Y is trivial when Y is a subset of X.

Examples:

• EmpID → Name — non-trivial. Knowing the ID tells you the name.
• {EmpID, Name} → Name — trivial. The right side is part of the left side. It’s obviously true and carries no information.

Trivial dependencies are always true by definition, so they’re never interesting for normalization. Only non-trivial dependencies matter when analyzing a design.

7. What is a Lossless-Join Decomposition?

When you split a relation into smaller relations, a lossless-join decomposition guarantees that joining them back together reproduces the original table exactly — no extra, spurious rows.

Example: R(A, B, C) decomposed into R1(A, B) and R2(B, C). Rejoining R1 and R2 on B must give back exactly R.

When it’s lossless: a common sufficient condition is that the two decomposed relations share a common attribute that is a key (or superkey) of one of them.

Why it matters: a decomposition with loss (extra fake rows appearing on join) is worse than useless — it corrupts query results. Every normalization step must preserve lossless joins.

8. What is the requirement for a relation to be in First Normal Form (1NF)?

1NF has two requirements:

1. Atomic values — every cell holds a single, indivisible value. No lists, no comma-separated values.
2. No repeating groups — no columns like phone1, phone2, phone3, and no storing multiple values in one cell.

Violating example:

OrderID	Products
1	Phone, Laptop, Tablet
2	Keyboard

The Products cell holds a list — that’s not atomic, so it’s not in 1NF. Fix: split each product into its own row.

1NF is the baseline — every relation must be in 1NF before the higher normal forms even apply.

9. What type of dependency is addressed by Fourth Normal Form (4NF)?

4NF addresses multivalued dependencies — situations where one attribute determines a set of independent values.

Example: A table storing each employee’s skills and languages:

EmpID	Skill	Language
1	Java	English
1	Java	Hindi
1	Python	English
1	Python	Hindi

Every skill pairs with every language — that’s a multivalued dependency. The table is huge and full of repetition even though it might be in 3NF.

4NF fixes it by splitting into two tables: Employee-Skill and Employee-Language.

So the ladder: 3NF handles transitive dependencies, 4NF handles multivalued dependencies, 5NF handles join dependencies.

10. What is BCNF and how does it differ from 3NF?

BCNF (Boyce-Codd Normal Form) is a stricter version of 3NF. A relation is in BCNF if for every functional dependency X → Y, X is a super key — i.e., every determinant must be a candidate key.

How it differs from 3NF: 3NF allows a dependency where the determinant is not a super key, provided Y is a prime attribute (part of some candidate key). BCNF removes that loophole — no non-super-key determinants at all.

The classic example (the one that always appears):

R(Student, Subject, Teacher)
FDs: (Student, Subject) → Teacher
     Teacher → Subject          ← Teacher is NOT a super key

A teacher teaches only one subject, so Teacher determines Subject — but Teacher isn’t a key. This violates BCNF (it satisfies 3NF, since Subject is a prime attribute). It causes a redundancy: every student taking that teacher’s subject repeats the subject.

The fix: decompose into R1(Student, Teacher) and R2(Teacher, Subject).

R1: (Student, Teacher)
R2: (Teacher, Subject)

Now every determinant is a key. BCNF guarantees no redundancy from functional dependencies, and since every BCNF relation is also 3NF, it’s the stronger, preferred target in practice (though 3NF is sometimes kept because BCNF can lose a dependency — it’s not always dependency-preserving).