SQL 1NF, 2NF, 3NF: Normalization for Structured Data

Welcome to our in-depth SQL tutorial on 1NF, 2NF, and 3NF - the fundamental concepts of database normalization! Let's embark on this journey together.

What is Normalization?

Normalization is a process that organizes data in a database to minimize redundancy, improve data consistency, and enforce dependencies between related data. In other words, it helps us design efficient and effective databases.

Understanding the Normal Forms

First Normal Form (1NF)

📝 Definition: A table is in 1NF when:

It contains only atomic (indivisible) values.
Each column has a unique name.
Each row is unique (has no duplicate values).

🎯 Example:

sql

Create table Students (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Subject1 VARCHAR(50),
    Subject2 VARCHAR(50),
    Subject3 VARCHAR(50)
);

Second Normal Form (2NF)

📝 Definition: A table is in 2NF when:

It is in 1NF.
Every non-key attribute is fully dependent on the primary key.

🎯 Example:

sql

Create table Students (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Subjects VARCHAR(100)
);

Create table Subjects (
    ID INT PRIMARY KEY,
    SubjectName VARCHAR(50)
);

Alter table Students
    ADD FOREIGN KEY (Subjects) REFERENCES Subjects(ID);

Third Normal Form (3NF)

📝 Definition: A table is in 3NF when:

It is in 2NF.
Every non-key attribute is independent of other non-key attributes.

🎯 Example:

sql

Create table Students (
    ID INT PRIMARY KEY,
    Name VARCHAR(50)
);

Create table Grades (
    ID INT PRIMARY KEY,
    StudentID INT,
    SubjectID INT,
    Grade VARCHAR(50),
    FOREIGN KEY (StudentID) REFERENCES Students(ID),
    FOREIGN KEY (SubjectID) REFERENCES Subjects(ID)
);

Quiz Time!

:::quiz Question: Which of the following tables is in 3NF?

sql

Create table Students (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Address VARCHAR(200)
);

sql

Create table Students (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Subjects VARCHAR(100)
);

Create table Subjects (
    ID INT PRIMARY KEY,
    SubjectName VARCHAR(50)
);

Alter table Students
    ADD FOREIGN KEY (Subjects) REFERENCES Subjects(ID);

sql

Create table Students (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Subjects VARCHAR(100)
);

Create table Subjects (
    ID INT PRIMARY KEY,
    SubjectName VARCHAR(50)
);

Create table Grades (
    ID INT PRIMARY KEY,
    StudentID INT,
    SubjectID INT,
    Grade VARCHAR(50),
    FOREIGN KEY (StudentID) REFERENCES Students(ID),
    FOREIGN KEY (SubjectID) REFERENCES Subjects(ID)
);

Correct: C Explanation: The table in option C is in 3NF because it meets all the conditions for 3NF - it is in 2NF, and every non-key attribute (Subjects and Grades) is independent of other non-key attributes.