Chapter 22 of 41

1NF, 2NF & 3NF

In the previous chapter, Normalization & Anomalies, we saw how one badly designed table caused insertion, update and deletion anomalies, and how splitting it into smaller tables fixed everything.

But we split that table mostly by common sense. In exams, interviews and real projects you need a precise test: given a table and its functional dependencies, is the design good or not? And if not, what exactly is wrong?

That is what the normal forms give us. In this chapter we cover the first three:

  • First Normal Form (1NF): every cell holds a single value.

  • Second Normal Form (2NF): no non-key attribute depends on part of a key.

  • Third Normal Form (3NF): no non-key attribute depends on another non-key attribute.

Each one builds on the previous one, so a table in 3NF is automatically in 2NF and 1NF.


Quick Recap: Terms You Need

These terms come from the Keys in DBMS, Functional Dependencies and Attribute Closure & Candidate Keys chapters:

Term

Meaning

Candidate key

A minimal set of attributes whose closure contains all attributes of the relation

Superkey

Any set of attributes that contains a candidate key

Prime attribute

An attribute that belongs to at least one candidate key

Non-prime attribute

An attribute that belongs to no candidate key

Trivial FD

X → Y where Y ⊆ X (always true, tells us nothing)

The normal forms are all about the non-trivial FDs, and in particular about what sits on their left side.


First Normal Form (1NF)

A relation is in 1NF if the domain of every attribute contains only atomic (indivisible) values, and each attribute holds a single value from that domain in every tuple.

In practice, a table violates 1NF when it has:

  • Multi-valued cells: a list of values packed into one column.

  • Repeating groups: columns like phone1, phone2, phone3 that store the same kind of value again and again.

Example: Not in 1NF

roll_no

name

phones

101

Aarav

98100 11111, 98100 22222

102

Diya

99200 33333

Why is this bad?

  • "Find the student with phone 98100 22222" now needs string searching (LIKE '%...%'), which is slow and error-prone.

  • You cannot put a UNIQUE constraint or an index on an individual phone number.

  • Removing one phone means editing a string instead of deleting a row.

Converting to 1NF

The clean fix is to move the multi-valued attribute into its own table, one value per row (exactly what we did for multivalued attributes in Converting ER Diagrams to Tables):

student(roll_no, name)            student_phone(roll_no, phone)
-----------------------            -----------------------------
101  Aarav                         101  98100 11111
102  Diya                          101  98100 22222
                                   102  99200 33333

The key of student_phone is (roll_no, phone). Now each cell holds one value.

(Another option is to repeat the whole row once per phone in a single table. That is also 1NF, but it duplicates the name, so it immediately creates the redundancy that 2NF will complain about. The separate table is better.)

What "Atomic" Really Means

Atomicity depends on how the data is used, not on the data type:

  • A date like 2026-11-02 is atomic if you always use it as one date, even though it has a day, month and year inside it.

  • A full name in one column is fine if you never need the first name separately. If you regularly search by surname, store it separately.

  • A comma-separated list in a VARCHAR is almost never atomic, because you will want individual items.

By definition, the relational model itself assumes 1NF: a relation is a set of tuples with atomic values. So every proper relation is already in 1NF; 1NF is mainly about converting raw spreadsheets and forms into relations.


Second Normal Form (2NF)

To understand 2NF, we need one new idea.

Partial Dependency

A partial dependency is an FD X → A where A is a non-prime attribute and X is a proper subset of some candidate key.

In plain words: a non-key attribute is determined by only part of the key, not the whole key.

Definition of 2NF

A relation is in 2NF if it is in 1NF and no non-prime attribute is partially dependent on any candidate key.

Equivalently: every non-prime attribute is fully functionally dependent on every candidate key.

Example

Let's take the student_course table from the previous chapter:

student_course(roll_no, name, dept, hod, course_id, course_name, grade)

FDs:
  roll_no            → name, dept
  dept               → hod
  course_id          → course_name
  roll_no, course_id → grade

Step 1: Find the candidate keys.

roll_no and course_id never appear on the right side of any FD, so they must be in every key. Their closure:

{roll_no, course_id}+
  start:                        roll_no, course_id
  roll_no → name, dept:         + name, dept
  dept → hod:                   + hod
  course_id → course_name:      + course_name
  roll_no, course_id → grade:   + grade
  = all 7 attributes  ✓

So {roll_no, course_id} is the only candidate key.

  • Prime attributes: roll_no, course_id

  • Non-prime attributes: name, dept, hod, course_name, grade

Step 2: Look for non-prime attributes that depend on part of the key.

FD

Left side is…

Right side is…

Partial?

roll_no → name, dept

Part of the key

Non-prime

Yes

roll_no → hod (via dept)

Part of the key

Non-prime

Yes

course_id → course_name

Part of the key

Non-prime

Yes

roll_no, course_id → grade

Whole key

Non-prime

No

There are partial dependencies, so the table is not in 2NF.

Notice the hidden one: roll_no → hod is not written in the FD list, but it follows by transitivity. Always think in terms of what the FDs imply, not only what is written. The easiest way is to compute the closure of each proper subset of the key: {roll_no}+ = {roll_no, name, dept, hod}, so everything non-prime in it besides roll_no depends on part of the key.

Why Partial Dependency Causes Trouble

The key is (roll_no, course_id), so a student appears once per course. But name depends only on roll_no. So Aarav's name is copied into every one of his course rows. That copying is exactly the update anomaly we saw earlier.

Converting to 2NF

For each part of the key that determines something on its own, move that part together with what it determines into a new table:

{roll_no}+   = {roll_no, name, dept, hod}   → student(roll_no, name, dept, hod)
{course_id}+ = {course_id, course_name}     → course(course_id, course_name)
what remains with the full key              → enrollment(roll_no, course_id, grade)

Each new table is in 2NF:

  • student and course have single-attribute keys. A single attribute has no proper non-empty subset, so a partial dependency is impossible.

  • enrollment: grade needs the whole key.

Shortcut: if every candidate key of a relation is a single attribute, the relation is automatically in 2NF (assuming it is in 1NF). The same is true if the relation has no non-prime attributes at all.


Third Normal Form (3NF)

Our student table is in 2NF, but it still has a problem:

student(roll_no, name, dept, hod)

roll_no → dept
dept    → hod

The HOD of CSE is still stored once per CSE student. Change the HOD and you must update many rows. A department with no students still cannot have an HOD recorded. The anomalies are back, just in a smaller table.

The cause is a transitive dependency.

Transitive Dependency

A transitive dependency exists when a non-prime attribute A depends on a key K through another set of attributes X: K → X and X → A, where X is not a superkey (and A is not in X).

roll_no ──→ dept ──→ hod
 (key)    (not a key)  (non-prime)

In plain words: a non-key attribute depends on another non-key attribute, instead of directly on the key.

Definition of 3NF

There are two equivalent ways to state 3NF. The first is the classic one:

A relation is in 3NF if it is in 2NF and no non-prime attribute is transitively dependent on any candidate key.

The second form is the one you should use for solving problems, because you can check it FD by FD:

A relation R is in 3NF if, for every non-trivial functional dependency X → A that holds on R, at least one of the following is true: (1) X is a superkey of R, or (2) A is a prime attribute.

Read it as: "the left side must be a superkey, unless the right side is part of some candidate key".

Why is it equivalent? If X → A breaks both conditions, then X is not a superkey and A is non-prime. Either X is part of a key (a partial dependency, which also breaks 2NF) or it isn't (a transitive dependency). Either way the classic definition fails too.

Converting to 3NF

Move the transitive part into its own table:

student(roll_no, name, dept, hod)
        ↓  split on dept → hod
student(roll_no, name, dept)      department(dept, hod)

Final design for the whole example:

Table

Key

FDs inside it

student(roll_no, name, dept)

roll_no

roll_no → name, dept

department(dept, hod)

dept

dept → hod

course(course_id, course_name)

course_id

course_id → course_name

enrollment(roll_no, course_id, grade)

(roll_no, course_id)

roll_no, course_id → grade

In every table, every left side is a key, so all four are in 3NF (in fact in BCNF). Every original FD lives inside one table, and the pieces join back without spurious rows (you can check both properties with the methods of the Lossless Join & Dependency Preservation chapter). These are exactly the four tables we ran in MySQL in the previous chapter.


The Subtle Part: Prime Attributes Are Exempt

Both 2NF and 3NF only protect non-prime attributes. An FD whose right side is a prime attribute never violates 2NF or 3NF. This catches many students out.

Example: R(A, B, C, D) with

AB → CD
C  → B

Candidate keys: A appears on no right side, so it is in every key. {A}+ = {A}, not enough.

{A,B}+ = {A,B,C,D}   ✓ key
{A,C}+ = {A,C,B,D}   ✓ key   (C → B, then AB → CD)
{A,D}+ = {A,D}       ✗

So the keys are AB and AC. Prime attributes: A, B, C. Non-prime: D.

Now check C → B. C is part of the key AC and is not a superkey. Is this a partial dependency? No, because B is prime. Is it a 3NF violation? No, again because B is prime.

So R is in 3NF. It is still not perfect (the fact "C determines B" can repeat across rows), and that remaining problem is exactly what BCNF, the next chapter, removes.


How to Find the Highest Normal Form

This is a very common GATE and university exam question. Use this procedure:

1. Find ALL candidate keys (using attribute closure).
2. Mark prime and non-prime attributes.
3. For each non-trivial FD X → A (split right sides into single attributes):
     X is a superkey?                          → fine for every normal form
     else A is prime?                          → fine for 2NF and 3NF (breaks BCNF)
     else X is a proper subset of some key?    → partial dependency  → breaks 2NF
     else                                      → transitive dependency → breaks 3NF
4. Highest NF = the highest one that no FD breaks.

(For a clean answer, also look at implied FDs, not just the listed ones. Computing the closure of each proper subset of each key is the safest way to catch hidden partial dependencies.)

Practice Problems

Q1. R(A, B, C, D), F = { AB → C, B → D }. Highest normal form?

A and B appear on no right side; {A,B}+ = {A,B,C,D}, so the only key is AB. Prime: A, B. Non-prime: C, D.

B → D: B is a proper subset of the key and D is non-prime, so this is a partial dependency. Answer: 1NF.

2NF decomposition: R1(B, D) and R2(A, B, C). Both are in BCNF.

Q2. R(A, B, C, D), F = { AB → C, C → D }. Highest normal form?

Only key: AB. Non-prime: C, D. {A}+ = {A} and {B}+ = {B}, so no partial dependencies: 2NF holds.

C → D: C is not a superkey and D is non-prime, a transitive dependency (AB → C → D). Answer: 2NF.

3NF decomposition: R1(C, D) and R2(A, B, C).

Q3. R(A, B, C, D), F = { A → B, B → C, C → D }. Highest normal form?

The only key is A (single attribute), so 2NF holds automatically.

B → C: B is not a superkey and C is non-prime. Transitive. Answer: 2NF.

3NF decomposition: R1(A, B), R2(B, C), R3(C, D).

Q4. R(A, B, C, D, E), F = { AB → CDE, D → A }. Highest normal form (up to 3NF)?

B appears on no right side, so it is in every key. {B}+ = {B}. {A,B}+ = all, so AB is a key. {B,D}+: D → A gives A, then AB → CDE gives everything, so BD is also a key. {B,C}+ = {B,C}, {B,E}+ = {B,E}, and every other superset that is a key contains AB or BD.

Keys: AB, BD. Prime: A, B, D. Non-prime: C, E.

D → A: D is not a superkey, but A is prime, so it is allowed in 3NF. AB → CDE: AB is a key. Answer: 3NF (but not BCNF, because of D → A).

Q5. R(A, B, C, D, E, F), F = { AB → C, C → D, D → E, A → F }. Highest normal form, and a 3NF decomposition?

Only key: AB (A and B appear on no right side, and {A,B}+ is everything). Non-prime: C, D, E, F.

A → F is partial. Answer: 1NF.

2NF: R1(A, F), R2(A, B, C, D, E). R2 still has C → D and D → E (transitive).

3NF: R1(A, F), R2(A, B, C), R3(C, D), R4(D, E). Every original FD lies inside one table, and every table is in BCNF.


1NF vs 2NF vs 3NF

1NF

2NF

3NF

Requirement

Atomic values, no repeating groups

1NF + no partial dependency

2NF + no transitive dependency

Forbidden FD shape

(not about FDs)

part of key → non-prime

non-key → non-prime

Typical symptom

Comma lists, phone1/phone2 columns

Entity details copied per relationship row

Lookup data (HOD, city of a pincode) copied per row

Fix

One value per row / separate table

Move the partial key and its dependents out

Move the intermediate attribute and its dependents out

Only an issue when…

Always check

A candidate key is composite

There are FDs between non-key attributes


Common Mistakes

  • Checking only the primary key. 2NF and 3NF are defined with respect to all candidate keys. An attribute in any candidate key is prime.

  • Calling a dependency on a prime attribute a violation. X → A with A prime is always allowed in 2NF and 3NF.

  • Missing implied FDs. roll_no → hod was not written, but it holds. Use closures.

  • Thinking a single-attribute key table can fail 2NF. It can't. It can still fail 3NF.

  • Splitting on the wrong side. When you remove X → A, the new table is (X ∪ A) and X stays in the old table. If you drop X from the old table, you cannot join back.

  • Saying 1NF means "has a primary key". 1NF is about atomic values. Keys matter from 2NF onwards.


Interview Questions

What is 1NF?

Every attribute holds a single atomic value in every row: no lists in one cell and no repeating groups of columns.

What is a partial dependency? Give an example.

A non-prime attribute depending on a proper subset of a candidate key. In enrollment(roll_no, course_id, student_name, grade) with key (roll_no, course_id), roll_no → student_name is partial.

What is a transitive dependency?

A non-prime attribute depending on the key through another non-key attribute: K → X → A where X is not a superkey. Example: roll_no → dept → hod.

State the definition of 3NF that you would use to test a relation.

For every non-trivial FD X → A, either X is a superkey or A is a prime attribute.

Can a relation with only single-attribute candidate keys violate 2NF?

No. A partial dependency needs a proper non-empty subset of a key, which a single attribute does not have. It can still violate 3NF.

If all attributes of a relation are prime, what is its minimum normal form?

3NF. Every right side is prime, so the 3NF condition holds for every FD. (It may still fail BCNF.)


Summary

  • 1NF: atomic values only; move multi-valued attributes into their own table.

  • 2NF: 1NF and no non-prime attribute depends on part of a candidate key.

  • 3NF: for every non-trivial X → A, X is a superkey or A is prime. Equivalently, 2NF with no transitive dependency.

  • Prime attributes are exempt from the 2NF and 3NF rules.

  • To find the highest normal form: find all keys, mark prime attributes, then classify each FD.

  • Fix a violation X → A by moving X together with the non-prime attributes it determines into a new table, and keeping X (but not those attributes) in the original table so the pieces can join back.

2NF: depend on the whole key. 3NF: depend on nothing but the key (unless you are part of a key yourself).