Database Normalization and Application Guide with Example

💡 What Will You Learn in This Guide?

In this guide, you will learn the basic concept of database normalization.
Our goal is to reduce data duplication, strengthen data integrity, and enable you to establish scalable, efficient database structures.

We will cover the following topics step by step:

• Purpose of normalization and why it is necessary
• Differences of 1NF, 2NF, 3NF and BCNF stages
• Practical SQL examples for each form
• When to prefer denormalization

🔍 In short: This guide teaches you how to transform disorganized data into logical tables.

🧠 What is Database Normalization?

Normalization is a systematic design technique used to organize data in relational databases to reduce duplication and increase consistency.
The goal is to split a large table into smaller, meaningful and related tables.

🎯 Key Goals

1. Avoiding Data Duplication: Not storing the same data in more than one place.
2. Ensuring Data Integrity: Preventing incorrect data entries by clarifying the relationships between tables.
3. Preventing Abnormalities:
   • Add anomaly: Failure to add a new record due to missing data.
   • Update anomaly: The same information needs to be changed in many places.
   • Deletion anomaly: Loss of unwanted data when a record is deleted.
4. Improving Query Performance: Regular tables are queried and managed faster.

---

🧱 Normalization Stages

Normalization consists of four basic successive stages:
1NF → 2NF → 3NF → BCNF

1️⃣ First Normal Form (1NF)

Rule: Each column must contain an atomic (singular) value.

Customer ID	Customer Name	Purchased Products
101	Ahmet Kaya	Laptop, Mouse
102	Elif Demir	Tablet

Issue: The “Purchased Products” cell contains more than one value.

Converted to 1NF:

Customer ID	Customer Name	Product
101	Ahmet Kaya	Laptop
101	Ahmet Kaya	Mouse
102	Elif Demir	Tablet

CREATE TABLE MusteriUrunleri (
    MusteriID INT,
    MusteriAdi VARCHAR(100),
    Urun VARCHAR(100)
);

🧩 This table is now 1NF compatible. Each cell has a single value.

---

2️⃣ Second Normal Form (2NF)

Rule: The table must be 1NF and partial dependencies must be removed.

Order ID Customer ID Customer Name Product 201 101 Ahmet Kaya Laptop 202 101 Ahmet Kaya Mouse

Problem: “Customer Name” is only linked to “Customer ID”, not the composite key.

Conversion to 2NF:

Customer information is moved to a separate table.

CREATE TABLE Musteriler (
    MusteriID INT PRIMARY KEY,
    MusteriAdi VARCHAR(100)
);

CREATE TABLE Siparisler (
    SiparisID INT PRIMARY KEY,
    MusteriID INT,
    Urun VARCHAR(100),
    FOREIGN KEY (MusteriID) REFERENCES Musteriler(MusteriID)
);

🧠 No more partial dependencies. Customer information is stored in one place.

---

3️⃣ Third Normal Form (3NF)

Rule: The table must be 2NF and transitive dependencies must be removed.

Order ID	Product	Supplier
201	Laptop	HP
202	Mouse	Logitech

Problem: “Supplier” is directly connected to “Product”. This is transitive dependency.

Conversion to 3NF: Product and supplier information must be moved to separate tables.

CREATE TABLE Tedarikciler (
    TedarikciID INT PRIMARY KEY,
    TedarikciAdi VARCHAR(100)
);

CREATE TABLE Urunler (
    UrunID INT PRIMARY KEY,
    UrunAdi VARCHAR(100),
    TedarikciID INT,
    FOREIGN KEY (TedarikciID) REFERENCES Tedarikciler(TedarikciID)
);

💡 Now each column depends only on its key. Data integrity is complete.

---

4️⃣ Boyce-Codd Normal Form (BCNF)

Rule: Every functional dependency must have a determining (X) super key.

Student ID	Lesson	Instructor
1	Mathematics	Dr. Yilmaz
2	Mathematics	Dr. Yilmaz

Problem: There is a Lesson → Instructor relationship, but “Lesson” is not the super key.

Conversion to BCNF:

CREATE TABLE OgrenciDersler (
    OgrenciID INT,
    Ders VARCHAR(100),
    PRIMARY KEY (OgrenciID, Ders)
);

CREATE TABLE DersEgitmenler (
    Ders VARCHAR(100) PRIMARY KEY,
    Egitmen VARCHAR(100)
);

🧩 BCNF has stricter rules than 3NF and eliminates complex dependencies.

⚖️ Normalization and Denormalization Balance Criterion Normalization Denormalization Data Repetition Less More Consistency High Medium Performance Fast in updates Fast in readings Area of Use Transaction-based systems Analytical systems

🚀 Tip: Denormalization in reporting or analytics systems can significantly improve reading performance.

---

💬 Frequently Asked Questions (FAQ)

1. What level of normalization is usually sufficient?

In most systems, 3NF is sufficient. BCNF is only required in cases of complex dependency.

2. What is the difference between partial and transitive dependency?

Partial dependency, dependency on part of the composite key; transitive dependency is the dependency of a non-key field on another non-key field.

3. Is BCNF always necessary?

No. BCNF is generally preferred for special, very complex data relationships.

4. Is normalization valid in NoSQL databases?

In general, no. NoSQL structures prefer denormalization for performance.

5. Does normalization reduce performance?

Splitting into too many tables increases join operations; However, this loss is acceptable in terms of data integrity.

---

🏁 Result

Database normalization is the foundation of orderly, reliable and scalable systems. When implemented correctly, it increases efficiency and eliminates data errors.

💡 You can establish strong data foundations by applying these principles in your projects. Create your own database on GenixNode and experience the difference of normalization.