🧹

Second Normal Form (2NF)

Fixing what 1NF left behind. A table is in 2NF when it's in 1NF and every non-key column depends on the whole composite key — never just part of it. Kill partial dependencies, kill the anomalies.

Partial Dependency Anomalies Decompose Foreign Keys

Press Next → or use ← → arrow keys

Section 01

The Story — The Tidy Table That Still Lied

🧾 REAL-WORLD ANALOGY

Atomic, but still repeating itself

After reaching 1NF the enrolment sheet looks tidy — every cell holds a single value. Yet the same student name appears on every course they take, and each course name repeats for every enrolled student. The data is organized, but it's still redundant — and that redundancy causes real problems.

💡

2NF in One Line

A table is in 2NF if it's in 1NF and every non-key column depends on the entire primary key — never merely on part of it. Removing those partial-key dependencies is the whole job of 2NF.

Section 02

A Quick Recap of 1NF — and Its Catch

✅

What 1NF fixes

Atomicity: one value per cell, no repeating groups. It's the foundational first step — and that's all it addresses.

⚠️

What 1NF does NOT fix

Redundancy. Flattening a multivalued column makes the key composite — and the other columns start repeating on every row.

🔁

The Catch, Concretely

Making STUDENT(Roll, Phone, Name) atomic forces the key to become {Roll, Phone} — so the student's Name repeats on every phone row. That leftover repetition is exactly what 2NF resolves.

Section 03

Partial Dependency & Its Three Anomalies

A partial dependency is when a non-prime attribute depends on only part of a composite key. One flaw → three classic anomalies.

Stu_ID	Crs_ID	Stu_Name	Crs_Name
S1	C1	Raj	DBMS
S1	C2	Raj	Maths
S2	C1	Sara	DBMS

✏️

Update anomaly

Renaming course C1 means editing every C1 row — miss one and names disagree.

➕

Insertion anomaly

Can't record a new course until a student enrols — the key needs Student_ID.

🗑️

Deletion anomaly

Delete the last enrolment for a course and the course's existence vanishes too.

Section 04

What Is Second Normal Form?

1️⃣

Gate 1

The table is already in 1NF (all values atomic).

2️⃣

Gate 2

There is no partial dependency — every non-prime attribute is fully dependent on the entire key.

🔑

The Single-Key Shortcut

A partial dependency needs a composite key. If the primary key is a single attribute, there's no "part" to depend on — so any 1NF table with a single-column key is automatically in 2NF.

🧠

Prime vs Non-Prime

Prime = belongs to some candidate key; non-prime = belongs to none. 2NF forbids the pattern (part of key) → (non-prime attribute).

Section 05

A Table That Fails 2NF — Full vs Partial

ENROLLMENT is in 1NF (atomic) but not in 2NF. PK = {Student_ID, Course_ID}. Watch which columns depend on only half the key.

🔎

The Diagnosis

Student_Name depends only on Student_ID and Course_Name only on Course_ID — both partial (violate 2NF). Only Marks depends on the whole key — that one's fine.

Section 06

Convert to 2NF — One Table Splits Into Three

STUDENT

ID	Name
S1	Raj
S2	Sara

COURSE

ID	Name
C1	DBMS
C2	Maths

ENROLLMENT

S	C	Mk
S1	C1	80
S1	C2	75
S2	C1	90

Section 06 · Result

Every Column Now Depends on Its Whole Key

Attribute	Depends on	Verdict
Student_Name	Student_ID (its full key)	Full ✓
Course_Name	Course_ID (its full key)	Full ✓
Marks	{Student_ID, Course_ID}	Full ✓

🎉

All Three Anomalies Gone

"Raj" lives once in STUDENT and "DBMS" once in COURSE. Rename a course in one place · add a course with no students enrolled · delete an enrolment without erasing the course. And the split is lossless — rejoin on the shared keys and you get the original back exactly.

Sections 07–08

Two Lenses on 2NF

🎓

Academic lens

2NF = 1NF + no partial dependency, stated purely via functional dependencies. The correcting decomposition is provably lossless. Single-key tables are automatically 2NF.

🏭

Industry lens

One fact in one place: no anomalies, less storage, simpler app code. The cost is extra joins at read time — usually cheap, occasionally worth denormalizing.

⚖️

Normalize for Writes, Denormalize for Reads

OLTP systems normalize to 2NF+ to keep inserts/updates safe. Analytics (OLAP) systems sometimes deliberately reintroduce redundancy to avoid expensive joins — an intentional trade-off, never an accident.

Section 10

The 2NF Verification Checklist

✅ FIVE QUESTIONS

1

Is the table already in 1NF (atomic values)?

2

Is the primary key composite? If single-column → already in 2NF.

3

Does any non-prime attribute depend on only part of the key? (a partial dependency)

4

If yes, decompose — move that attribute with its part-key into a new table.

5

Confirm every remaining non-prime attribute depends on the whole key, and add foreign keys.

🔗

Don't Forget the Foreign Keys

Decomposition must stay lossless — keep the shared column as a foreign key so the tables can rejoin without losing the relationship.

Section 11

Three Common Mistakes

🔑

Partial deps under a single key

A single-attribute key has no "part" — partial dependency is impossible, so the 1NF table is already 2NF.

🔀

Confusing 2NF with 3NF

2NF removes partial deps (on part of a key). Transitive deps (non-key → non-key) are 3NF's job — a 2NF table can still have them.

🔗

Forgetting foreign keys

Without the shared FK, decomposition loses the relationship. Losslessness depends on it.

🧭

The Test

Composite key? For each non-prime column ask: "does it need both key parts, or just one?" Just one → move it out.

Section 12

Golden Rules of Second Normal Form

🏆 NON-NEGOTIABLE PRINCIPLES

1

2NF = 1NF + no partial dependency. Every non-key attribute must depend on the whole key.

2

Partial dependency needs a composite key — single-column keys are automatically 2NF.

3

The 1NF issue 2NF fixes is redundancy — part-of-key dependencies repeat data and cause insert/update/delete anomalies.

4

Fix by lossless decomposition — move each partial attribute with its part-key into a new table, linked by a foreign key.

5

Academically a functional-dependency rule; industrially, consistent, anomaly-free data.

6

2NF is not the finish line — transitive dependencies remain, and that's what 3NF addresses.

FINAL

One Fact, One Place

1NF+Prerequisite

0Partial dependencies

3Anomalies removed

FKKeeps it lossless

🎯

The Foundation Is Set

2NF makes every non-key column depend on its whole key — killing the redundancy and the three anomalies 1NF left behind, all via a lossless split linked by foreign keys. But one gap remains: a non-key column can still depend on another non-key column. Removing that transitive dependency is the next step — Third Normal Form (3NF).

🧠

One Sentence to Remember

If a non-key column depends on only part of a composite key, split it out — 2NF means every non-key attribute depends on the whole key, nothing less.

🧹 End of tutorial · Press ← to review, or click Restart