Normalization: 1NF and 2NF

The Multi Value Cell Problem

A university registrar puts together a quick spreadsheet to track student enrollments. It seems efficient at first: one row per student, with all their courses in one cell.

Now try to answer: "Which students are taking Physics?"

You cannot query it with a simple WHERE Courses = 'Physics'. You'd have to search inside the text of every cell. And what if a course is renamed from "Physics" to "Physics I"? You'd have to edit every row that contains it.

This table has a structural problem. Normalization is the process of restructuring tables to eliminate these problems.

What Is Normalization?

Normalization is the systematic process of organizing a relational database to:

• Reduce data redundancy the same fact stored in only one place
• Eliminate data anomalies errors that arise from redundancy
• Make the structure reflect the real world model each table represents one concept

Normalization is a series of rules, each called a Normal Form (NF). Each form fixes a specific class of structural problem. You achieve them in order: 1NF first, then 2NF, then 3NF.

The Three Anomalies That Normalization Prevents

Before understanding what normalization fixes, understand what goes wrong without it.

Consider this single unnormalized table for a course registration system:

RegistrationRaw (before normalization)

Update Anomaly: The Computer Science department moves from Hall A to Hall C. You must update every row for every Computer Science student. Miss one row and the database becomes inconsistent some rows say Hall A, some say Hall C.

Insert Anomaly: You want to add a new department, "Physics," in Hall D but there are no students yet. You can't insert a row because the table requires a StudentID. You cannot record the department until a student enrolls in it.

Delete Anomaly: Clara (student 3) withdraws from the university. You delete her row. Now the fact that "CS101 is named Intro to CS" exists only because Clara was in it and it's gone too.

These anomalies happen because one table is trying to represent multiple independent facts: student information, department information, course information, and enrollment information. Normalization separates them.

Functional Dependency: The Core Concept

Before applying normal forms, you must understand functional dependency.

Attribute B is functionally dependent on attribute A (written A → B) if knowing the value of A tells you exactly one value of B.

• StudentID → StudentName knowing the ID tells you the name (one student has one name)
• CourseID → CourseName knowing the course ID tells you the course name
• StudentID, CourseID → Grade knowing both the student AND the course tells you the grade

Functional dependencies reveal the real world rules your data must respect. Normalization ensures those rules are enforced by the table structure.

First Normal Form (1NF)

Rule: Every column must contain atomic values (indivisible), and there must be no repeating groups.

A table violates 1NF if:

• A cell contains a list or multiple values ("Math, Physics, Chemistry")
• Multiple columns represent the same type of thing (Course1, Course2, Course3)

Before 1NF:

After 1NF one row per student course combination:

Now every cell holds one value. WHERE CourseID = 'Physics' works perfectly.

The primary key becomes the composite (StudentID, CourseID) neither column alone uniquely identifies a row.

Dependency diagram after 1NF:

Second Normal Form (2NF)

Rule: The table must be in 1NF, AND every non key attribute must depend on the entire primary key not just part of it.

This rule only applies when the primary key is composite. If the PK is a single column, 1NF → 2NF is automatic.

A partial dependency exists when a non key attribute depends on only part of the composite key.

Identifying violations in the registration table:

The PK is (StudentID, CourseID).

• Grade depends on (StudentID, CourseID) ✓ full dependency
• StudentName depends on StudentID alone ✗ partial dependency
• Department depends on StudentID alone ✗ partial dependency
• DeptBuilding depends on StudentID alone ✗ partial dependency
• CourseName depends on CourseID alone ✗ partial dependency

Dependency diagram showing violations:

Fixing 2NF: Separate each partial dependency into its own table.

Students table:

Courses table:

Enrollments table (junction):

After 2NF:

• Updating Computer Science's building requires changing one row in Students, not every enrollment row
• Adding a new course requires inserting only into Courses no fake enrollment needed
• Deleting a student doesn't destroy course name information

The Transformation: Before and After

Before normalization:

RegistrationRaw
┌───────────┬─────────────┬──────────────┬──────────────┬──────────┬────────────┬───────┐
│ StudentID │ StudentName │ Department   │ DeptBuilding │ CourseID │ CourseName │ Grade │
└───────────┴─────────────┴──────────────┴──────────────┴──────────┴────────────┴───────┘
         Everything in one table — redundant, anomaly-prone

After 1NF + 2NF:

Students                      Courses                  Enrollments
┌───────────┬─────────────┐   ┌──────────┬────────────┐  ┌───────────┬──────────┬───────┐
│ StudentID │ StudentName │   │ CourseID │ CourseName │  │ StudentID │ CourseID │ Grade │
│ Department│ DeptBuilding│   └──────────┴────────────┘  └───────────┴──────────┴───────┘
└───────────┴─────────────┘

Each table represents one concept. Each fact is stored once.

How to Identify 2NF Violations

1. Is the primary key composite? If not, the table is already in 2NF.
2. List every non key attribute.
3. Ask: does this attribute need the full key to be determined, or just part of it?
4. Any attribute that needs only part of the key is a partial dependency move it to a separate table.

Where We Are

But our Students table still has a problem: DeptBuilding depends on Department, not on StudentID. That's a different kind of redundancy a transitive dependency. It's what 3NF fixes.

Next lesson: 3NF and BCNF removing transitive dependencies and reaching a fully clean schema.