Lesson 1, Topic 1

In Progress

Lesson 1.3 – Normalization and Normal Forms

Lesson 1.3 – Normalization & Normal Forms (T-SQL)

Learning Objectives

By the end of this lesson, you will be able to:

Explain what normalization is and why it matters for a healthy database.
Apply First Normal Form (1NF) by removing repeating groups and ensuring atomic values.
Apply Second Normal Form (2NF) by removing partial dependencies on composite keys.
Apply Third Normal Form (3NF) by removing transitive dependencies.
Identify the three classic data anomalies: insertion, update, and deletion anomalies.
Refactor a messy single-table design into a clean, normalized multi-table schema in T-SQL.

📚 Key Vocabulary

Term	Definition	SchoolHub Example
Normalization	The process of structuring a database to reduce redundancy and prevent anomalies.	Splitting one giant `StudentClasses` table into `Students`, `Classes`, and `Enrollments`.
1NF	First Normal Form — every cell holds a single atomic value; no repeating groups; each row is unique.	Replace `'Math, History, Biology'` in one cell with three separate rows.
2NF	Second Normal Form — already in 1NF, plus every non-key column depends on the whole primary key.	Move `ClassName` out of `Enrollments(StudentID, ClassID)` because it only depends on `ClassID`.
3NF	Third Normal Form — already in 2NF, plus no column depends on another non-key column.	Move `TeacherName` out of `Classes` because it depends on `TeacherID`, not on `ClassID`.
Atomic Value	A single, indivisible value — not a list, not a multi-part string.	`'Alice'` is atomic; `'Alice; Bob; Carol'` is not.
Functional Dependency	When one column's value determines another's. We write it as `A → B`.	`StudentID → FirstName`: knowing the ID tells you the name.
Partial Dependency	A non-key column depends on only part of a composite primary key.	In `(StudentID, ClassID)`, `ClassName` depends only on `ClassID`.
Transitive Dependency	A non-key column depends on another non-key column.	`ClassID → TeacherID → TeacherName`: `TeacherName` depends on `TeacherID`, not directly on `ClassID`.
Anomaly	A problem caused by bad design: data that's wrong, missing, or inconsistent after an operation.	Deleting a student also accidentally deletes a class — that's a deletion anomaly.

💡 Real-World Analogy

Imagine a kitchen drawer where you throw everything: scissors, batteries, spare keys, soy sauce packets, takeout menus, the manual for the toaster oven you got rid of three years ago. Need a battery? Good luck. Need scissors? Empty everything out, find them, shove it all back. Lose the scissors? You won't know until the next emergency.

Now imagine the same drawer organized into compartments — one for stationery, one for batteries, one for keys, one for sauce packets. Each thing has one home. Finding anything takes seconds. Knowing what you have is trivial. Throwing out the toaster manual doesn't risk losing the scissors.

That's normalization. The messy drawer is a denormalized table — everything stuffed into one place, with duplication and tangled dependencies. The compartments are normalized tables: each one holds one type of thing, and the relationships are made explicit through keys.

🎓 SchoolHub connection: Most beginner schemas start as a single giant table — the messy drawer. Lesson 1.3 is where you learn to split that table into compartments so the database can grow without becoming a nightmare.

🧱 Concept Explanation

The messy table we're starting from

Suppose someone built SchoolHub as a single table. It records every student's enrollment, the class they're in, who teaches it, and the teacher's email — all in one row:

SchoolHub_v0.dbo.StudentClasses (denormalized — every problem at once)

+-----------+-----------+----------+------------+--------------------+--------------+----------+-------------------+
| StudentID | FirstName | LastName | GradeLevel | Classes            | TeacherName  | Room     | TeacherEmail      |
+-----------+-----------+----------+------------+--------------------+--------------+----------+-------------------+
|     1     | Alice     | Wong     |     10     | Math, History      | Smith, Lee   | 201, 305 | smith,lee@s.edu   |
|     2     | Bob       | Garcia   |     10     | Math               | Smith        | 201      | smith@s.edu       |
|     3     | Carol     | Patel    |     11     | Biology, History   | Brown, Lee   | 102, 305 | brown,lee@s.edu   |
+-----------+-----------+----------+------------+--------------------+--------------+----------+-------------------+

This table breaks all three normal forms. Before we fix it, let's see why bad design causes real-world damage.

The three classic anomalies

➕ Insertion Anomaly

You want to add a new teacher who isn't yet teaching any class. There's nowhere to put them — every row requires a student. The data can't be inserted at all.

✏️ Update Anomaly

Mr. Smith changes his email. You have to update every row where he appears. Miss one, and your data is now inconsistent — half the rows have the new email, half have the old.

🗑️ Deletion Anomaly

Bob (the only Math student) graduates. You delete his row. Now there's no record at all that Math exists or that Mr. Smith teaches it. Deleting one fact accidentally erased others.

Normalization eliminates all three by giving every distinct fact its own home.

Step 1 — First Normal Form (1NF)

The 1NF rule: every cell holds a single, atomic value. No lists. No repeating groups. No "multi-value" cells.

Look at Alice's row: 'Math, History' is two values stuffed into one cell. That breaks 1NF. So does '201, 305' in the Room column. Fix it by splitting the rows so each cell holds exactly one value:

StudentClasses (after 1NF — atomic values, one row per enrollment)

+-----------+-----------+----------+------------+-----------+--------------+------+-----------------+
| StudentID | FirstName | LastName | GradeLevel | ClassName | TeacherName  | Room | TeacherEmail    |
+-----------+-----------+----------+------------+-----------+--------------+------+-----------------+
|     1     | Alice     | Wong     |     10     | Math      | Smith        | 201  | smith@s.edu     |
|     1     | Alice     | Wong     |     10     | History   | Lee          | 305  | lee@s.edu       |
|     2     | Bob       | Garcia   |     10     | Math      | Smith        | 201  | smith@s.edu     |
|     3     | Carol     | Patel    |     11     | Biology   | Brown        | 102  | brown@s.edu     |
|     3     | Carol     | Patel    |     11     | History   | Lee          | 305  | lee@s.edu       |
+-----------+-----------+----------+------------+-----------+--------------+------+-----------------+

The composite primary key is now (StudentID, ClassName). But notice: Alice's name still appears twice, and so does "Lee" the teacher. We've fixed atomicity, but redundancy is alive and well. On to 2NF.

🛒 Quick 1NF check: can you point to any cell that contains a list, a comma-separated string, or "see also" type values? If yes, you're not in 1NF yet.

Step 2 — Second Normal Form (2NF)

The 2NF rule: you're in 1NF and every non-key column depends on the whole primary key — not just part of it.

Our composite key is (StudentID, ClassName). Let's check each non-key column:

FirstName, LastName, GradeLevel — depend only on StudentID. Partial dependency.
TeacherName, Room, TeacherEmail — depend only on ClassName. Partial dependency.

To reach 2NF, we move each partial dependency into its own table:

Students (depends on StudentID alone)

+-----------+-----------+----------+------------+
| StudentID | FirstName | LastName | GradeLevel |
+-----------+-----------+----------+------------+
|     1     | Alice     | Wong     |     10     |
|     2     | Bob       | Garcia   |     10     |
|     3     | Carol     | Patel    |     11     |
+-----------+-----------+----------+------------+

Classes (depends on ClassName alone — for now)

+-----------+--------------+------+-----------------+
| ClassName | TeacherName  | Room | TeacherEmail    |
+-----------+--------------+------+-----------------+
| Math      | Smith        | 201  | smith@s.edu     |
| History   | Lee          | 305  | lee@s.edu       |
| Biology   | Brown        | 102  | brown@s.edu     |
+-----------+--------------+------+-----------------+

Enrollments (the link table)

+-----------+-----------+
| StudentID | ClassName |
+-----------+-----------+
|     1     | Math      |
|     1     | History   |
|     2     | Math      |
|     3     | Biology   |
|     3     | History   |
+-----------+-----------+

Better. But the Classes table still has a problem hiding inside it.

Step 3 — Third Normal Form (3NF)

The 3NF rule: you're in 2NF and no non-key column depends on another non-key column.

Look at the Classes table. The primary key is ClassName. But:

TeacherEmail doesn't really depend on the class — it depends on TeacherName.
If Mr. Smith teaches Math and Algebra, his email will be repeated in both rows. Transitive dependency.

The fix is to give teachers their own table. Introduce a TeacherID in Teachers and reference it from Classes. While we're at it, give classes their own ID too — and replace ClassName in Enrollments with the new ClassID:

Teachers (3NF — no transitive deps)

+-----------+-----------+----------+----------------+
| TeacherID | FirstName | LastName | Email          |
+-----------+-----------+----------+----------------+
|    10     | John      | Smith    | smith@s.edu    |
|    11     | Maria     | Lee      | lee@s.edu      |
|    12     | David     | Brown    | brown@s.edu    |
+-----------+-----------+----------+----------------+

Classes (3NF — links to Teachers by ID)

+---------+-----------+------+-----------+
| ClassID | ClassName | Room | TeacherID |
+---------+-----------+------+-----------+
|   101   | Math      | 201  |    10     |
|   102   | History   | 305  |    11     |
|   103   | Biology   | 102  |    12     |
+---------+-----------+------+-----------+

Enrollments (3NF — links Students and Classes)

+-----------+---------+
| StudentID | ClassID |
+-----------+---------+
|     1     |   101   |
|     1     |   102   |
|     2     |   101   |
|     3     |   103   |
|     3     |   102   |
+-----------+---------+

Now every fact lives in exactly one place. Change Mr. Smith's email? Update one row. Add a teacher who's not yet teaching anything? Just insert into Teachers. Delete Bob? Math still exists in Classes.

Quick recap of all three forms

Form	Rule	Fix It By...
1NF	Atomic values, no repeating groups.	Splitting multi-value cells into separate rows.
2NF	1NF + no partial dependencies on a composite PK.	Moving partially dependent columns into their own table.
3NF	2NF + no transitive dependencies (non-key → non-key).	Extracting groups of columns that depend on each other into a new table linked by ID.

🧠 How far should you normalize? For most real-world apps, 3NF is the sweet spot. Higher forms (BCNF, 4NF, 5NF) exist for niche cases, but 3NF eliminates the vast majority of anomalies while keeping the schema easy to query. When in doubt: get to 3NF and stop.

✍️ Best Practices

🧩 One Fact, One Place

Every fact (a teacher's email, a class's room) lives in exactly one row of exactly one table. If you have it in two places, you'll eventually have it wrong in one.

📜 Aim for 3NF

For schoolwork, business apps, and certification — 3NF is the standard target. Go higher only when you have a clear reason.

🔁 Iterate, Don't Perfect

Start with 1NF, then 2NF, then 3NF. Each pass is small and clear. Don't try to leap to 3NF in one go.

🧪 Test by Imagining Changes

Ask: "If a teacher changes their email, how many rows do I update?" If the answer is more than one, you're not normalized enough.

⚠️ Beware Hidden Lists

Columns like 'Math, Bio, History' or 'Mon/Wed/Fri' are violating 1NF in disguise. Split them.

🔗 Trust Foreign Keys

After normalization you'll have more tables and more JOINs — that's normal. Foreign keys make those joins safe.

🧪 Hands-On Practice

Warm-Up

Spot the Normal Form Violations

1. Which Normal Form is Violated?

For each table below, decide which normal form is violated (1NF, 2NF, or 3NF) and explain why in one sentence.

(a)

+-----------+-----------+------------------+
| StudentID | FullName  | Subjects         |
+-----------+-----------+------------------+
|     1     | Alice W.  | Math, Bio        |
|     2     | Bob G.    | History          |
+-----------+-----------+------------------+

(b) Composite PK is (StudentID, ClassID):

+-----------+---------+-----------+-----------+
| StudentID | ClassID | ClassName | StudentDOB|
+-----------+---------+-----------+-----------+
|     1     |   101   | Math      | 2008-05-01|
|     1     |   102   | History   | 2008-05-01|
+-----------+---------+-----------+-----------+

(c)

+---------+-----------+-----------+-------------+
| ClassID | ClassName | TeacherID | TeacherName |
+---------+-----------+-----------+-------------+
|   101   | Math      |    10     | John Smith  |
|   102   | Algebra   |    10     | John Smith  |
+---------+-----------+-----------+-------------+

2. Anomaly Spotter

Using the messy denormalized table from the concept section, describe one insertion, one update, and one deletion anomaly in your own words. Use specific SchoolHub data.

3. Normalize It

Take this single-table design and walk it through 1NF → 2NF → 3NF on paper. List the final tables, their columns, and which column is the PK or FK.

LibraryLoans (denormalized)

+--------+--------------+----------+---------------+--------------+----------------+
| LoanID | MemberName   | MemberID | Books         | BookAuthors  | LoanDate       |
+--------+--------------+----------+---------------+--------------+----------------+
| 1      | Alice W.     | 1        | Dune, 1984    | Herbert, Orwell | 2024-09-01  |
| 2      | Bob G.       | 2        | Dune          | Herbert      | 2024-09-02     |
| 3      | Carol P.     | 3        | 1984          | Orwell       | 2024-09-03     |
+--------+--------------+----------+---------------+--------------+----------------+

4. Functional Dependency Drill

Write the functional dependencies you see in this list of columns, using A → B notation:

StudentID, FirstName, LastName, GradeLevel, GradeLevelDescription

(Hint: "GradeLevel 10" always corresponds to "Sophomore". What does GradeLevelDescription depend on?)

💻 Coding Lab — Activity 1.3

Coding Challenge

Refactor a Messy Table into 3NF

You're going to take a single denormalized table and refactor it into a clean 3NF schema in T-SQL. Then you'll prove the new schema works by running a JOIN that reconstructs the original information.

Open the Coding Lab and create a new file lesson1_3.sql.
Build the denormalized starting table to see the problem first-hand.
Drop it and rebuild as three normalized tables with proper PKs and FKs.
Insert sample data into all three tables.
Run a 3-table INNER JOIN that recreates the original combined view.
Save your file as lesson1_3_(FirstName_LastName).sql.

lesson1_3.sql — Start: the messy version

-- The denormalized "before" table. Note the redundancy.
CREATE TABLE StudentClasses_Bad (
    EnrollmentID INT          IDENTITY(1,1) PRIMARY KEY,
    StudentName  NVARCHAR(100) NOT NULL,
    GradeLevel   INT          NOT NULL,
    ClassName    NVARCHAR(100) NOT NULL,
    TeacherName  NVARCHAR(100) NOT NULL,
    TeacherEmail NVARCHAR(100) NOT NULL
);
GO
 
INSERT INTO StudentClasses_Bad
    (StudentName, GradeLevel, ClassName, TeacherName, TeacherEmail)
VALUES
    ('Alice Wong',  10, 'Math',    'John Smith', 'smith@s.edu'),
    ('Alice Wong',  10, 'History', 'Maria Lee',  'lee@s.edu'),
    ('Bob Garcia',  10, 'Math',    'John Smith', 'smith@s.edu'),
    ('Carol Patel', 11, 'Biology', 'David Brown','brown@s.edu'),
    ('Carol Patel', 11, 'History', 'Maria Lee',  'lee@s.edu');
GO
 
-- Notice: Smith's email is stored twice. Alice's name and grade are stored twice.
-- Imagine Smith changes his email. We'd need to update multiple rows!
SELECT * FROM StudentClasses_Bad;

lesson1_3.sql — Clean it up: drop and rebuild in 3NF

-- Start fresh
DROP TABLE StudentClasses_Bad;
GO
 
-- Teachers — one row per teacher
CREATE TABLE Teachers (
    TeacherID INT          IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName  NVARCHAR(50) NOT NULL,
    Email     NVARCHAR(100) NOT NULL
);
GO
 
-- Students — one row per student
CREATE TABLE Students (
    StudentID  INT          IDENTITY(1,1) PRIMARY KEY,
    FirstName  NVARCHAR(50) NOT NULL,
    LastName   NVARCHAR(50) NOT NULL,
    GradeLevel INT          NOT NULL
);
GO
 
-- Classes — one row per class, linked to its teacher
CREATE TABLE Classes (
    ClassID   INT           IDENTITY(1,1) PRIMARY KEY,
    ClassName NVARCHAR(100) NOT NULL,
    TeacherID INT           NOT NULL,
    FOREIGN KEY (TeacherID) REFERENCES Teachers(TeacherID)
);
GO
 
-- Enrollments — links Students and Classes (the junction table)
CREATE TABLE Enrollments (
    StudentID INT NOT NULL,
    ClassID   INT NOT NULL,
    PRIMARY KEY (StudentID, ClassID),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (ClassID)   REFERENCES Classes(ClassID)
);
GO

lesson1_3.sql — Populate with the same data, normalized

INSERT INTO Teachers (FirstName, LastName, Email) VALUES
    ('John',  'Smith', 'smith@s.edu'),    -- TeacherID 1
    ('Maria', 'Lee',   'lee@s.edu'),      -- TeacherID 2
    ('David', 'Brown', 'brown@s.edu');    -- TeacherID 3
GO
 
INSERT INTO Students (FirstName, LastName, GradeLevel) VALUES
    ('Alice', 'Wong',   10),  -- StudentID 1
    ('Bob',   'Garcia', 10),  -- StudentID 2
    ('Carol', 'Patel',  11);  -- StudentID 3
GO
 
INSERT INTO Classes (ClassName, TeacherID) VALUES
    ('Math',    1),   -- ClassID 1, taught by Smith
    ('History', 2),   -- ClassID 2, taught by Lee
    ('Biology', 3);   -- ClassID 3, taught by Brown
GO
 
INSERT INTO Enrollments (StudentID, ClassID) VALUES
    (1, 1),  -- Alice -> Math
    (1, 2),  -- Alice -> History
    (2, 1),  -- Bob   -> Math
    (3, 3),  -- Carol -> Biology
    (3, 2);  -- Carol -> History
GO

lesson1_3.sql — Reconstruct the view with a 4-table JOIN

-- This is what the messy table tried to show — now built from clean tables.
SELECT
    s.FirstName + ' ' + s.LastName AS StudentName,
    s.GradeLevel,
    c.ClassName,
    t.FirstName + ' ' + t.LastName AS TeacherName,
    t.Email            AS TeacherEmail
FROM Enrollments e
INNER JOIN Students s ON e.StudentID = s.StudentID
INNER JOIN Classes  c ON e.ClassID   = c.ClassID
INNER JOIN Teachers t ON c.TeacherID = t.TeacherID
ORDER BY StudentName, c.ClassName;

Expected Output

🔑 The big win: the output looks identical to the messy version — but Smith's email is now stored once in Teachers, not five times. To change his email, you update one row. No anomalies. That's the entire point of normalization.

🔁 Bonus Challenge

Update Smith's email to 'jsmith@school.edu' with a single UPDATE Teachers ... statement. Re-run the join. All Smith rows update at once!
Insert a fourth teacher who isn't yet teaching any class. Easy in the normalized schema; impossible in the original.
Delete Bob from Students (after removing his enrollment). Math still exists, Smith still exists. No data lost.

✅ Self-Check

I created the denormalized StudentClasses_Bad table and saw the redundancy first-hand.

I dropped it and built Teachers, Students, Classes, and Enrollments in T-SQL.

Every table has an IDENTITY PK (or composite PK for Enrollments) and the correct FOREIGN KEY constraints.

I inserted sample rows into all four tables in the correct order (parents before children).

My 4-table INNER JOIN returned the 5-row combined view.

I tested the win by updating one teacher's email and seeing every joined row reflect the change.

I saved the file as lesson1_3_(FirstName_LastName).sql.

0 of 7 complete

🎤 Questions to Think About

Discuss with your class or jot down your answers:

Why do you think normalization is so often skipped by beginners — even though the rules are simple?
Can you think of a real situation where you might intentionally denormalize a table (store some redundant data on purpose)? What's the trade-off?
How does the foreign key constraint protect you when working in a 3NF schema? What would go wrong without it?
If your school's report card system stored teacher names in 1000 enrollment rows and a teacher changed names, how many updates would the system have to do? What if it stored only TeacherIDs?
Which of the three anomalies (insertion, update, deletion) do you think is the most dangerous in practice, and why?