DBMS: Normalization

Normalisation is the foundational process of organising data within a relational database. A well-normalised database is crucial for performance, scalability, and accuracy.

The primary goals of normalisation are to:

• Reduce data redundancy: Eliminate unnecessary repetition of data.

• Avoid data inconsistency: Ensure that a data update in one place doesn't leave conflicting older data in another.

• Eliminate anomalies: Prevent errors from occurring during data INSERT, UPDATE, or DELETE operations.

• Improve data integrity: Enforce logical relationships between tables.

• Simplify maintenance: Make the database structure cleaner and easier to manage over time.

The Normalisation Pipeline

The normalization process happens in stages, known as Normal Forms. A database schema typically moves through these stages sequentially:

Code snippet

flowchart LR
    UNF((UNF)) --> 1NF((1NF))
    1NF --> 2NF((2NF))
    2NF --> 3NF((3NF))
    3NF --> BCNF((BCNF))
    BCNF --> 4NF((4NF))
    4NF --> 5NF((5NF))
  
(Note: Most real-world business applications stop at 3NF or BCNF, as this offers the best balance between performance and data integrity.)

1. Unnormalised Form (UNF)

A table is considered to be in unnormalised form (UNF) when it contains repeating groups or multi-valued attributes—meaning a single column holds multiple distinct values.

Example of a UNF Table (Student_Courses)

Student_ID	Student_Name	Courses
101	Rahul	DBMS, OS, Python
102	Priya	DBMS, Java

Why this design fails in production:

• Searching is painfully slow: To find all students taking "DBMS", the database has to perform a slow string search (e.g., WHERE Courses LIKE '%DBMS%') rather than an exact index match.

• Updating is risky: If you want to drop "OS" for Rahul, you have to parse the string, remove the specific word, and rewrite the string without breaking the commas.

• Counting is impossible: You cannot easily write a COUNT() aggregate function to see how many total courses Rahul is taking.

• Data integrity is compromised: Nothing prevents a user from typing "DBMS OS,, Python" with typos or extra spaces.

To solve these issues, the data must be broken down. So, we move toward the First Normal Form (1NF).

2. First Normal Form (1NF)

A database table is considered to be in First Normal Form (1NF) if it meets the following criteria:

1. Atomic Values Only: Every column must contain a single, indivisible value (no comma-separated lists).

2. No Repeating Groups: Each column must contain unique data categories.

3. Unique Records: Each row must be uniquely identifiable.

Let's look at how we fix the problematic table from our previous step.

Before 1NF (The Problem)

Student_ID	Student_Name	Courses
101	Rahul	DBMS, OS, Python

After 1NF (The Solution)

To achieve 1NF, we split the multiple courses into their own individual rows.

Student_ID	Student_Name	Course
101	Rahul	DBMS
101	Rahul	OS
101	Rahul	Python

Now, each cell contains exactly one value.

Important Takeaway for 1NF: While 1NF removes multi-valued attributes, it does not remove redundancy. Notice how Rahul’s name and ID are now repeated for every single course he takes. This repetition leads us directly to the next stage.

3. Second Normal Form (2NF)

A relation is in Second Normal Form (2NF) if:

1. It is already in 1NF.

2. There is no partial dependency.

What is partial dependency?

Partial dependency occurs when a non-key attribute (like a student's name) depends on only part of a composite primary key, rather than the entire key.

Let's examine a table named STUDENT_COURSE to understand this:

Student_ID	Course_ID	Student_Name	Course_Name	Marks
101	C01	Rahul	DBMS	85
101	C02	Rahul	OS	78
102	C01	Priya	DBMS	91

In this table, no single column can uniquely identify a row. A student can take many courses, and a course can have many students. Therefore, the Primary Key is composite: (Student_ID, Course_ID).

Now, let's examine the relationships (dependencies) in this table:

• Student_Name only depends on Student_ID. (It doesn't care about the Course_ID).

• Course_Name only depends on Course_ID. (It doesn't care about the Student_ID).

• Marks depends on both Student_ID and Course_ID. (You need both to know exactly which score belongs to whom).

Here is a visual breakdown of the flaw:

Code snippet

graph TD
    subgraph Composite Primary Key
    SID(Student_ID)
    CID(Course_ID)
    end
    
    SID -->|Partial Dependency| SName[Student_Name]
    CID -->|Partial Dependency| CName[Course_Name]
    SID & CID -->|Full Dependency| Marks[Marks]

Because Student_Name and Course_Name depend on only part of the composite key, we have partial dependency. This is bad design.

How to Convert into 2NF

To fix this, we break the data down into separate, dedicated tables where every non-key attribute depends on the entire primary key of its respective table.

Table 1: STUDENT

Student_ID (PK)	Student_Name
101	Rahul
102	Priya

Table 2: COURSE

Course_ID (PK)	Course_Name
C01	DBMS
C02	OS

Table 3: ENROLLMENT

Student_ID (PK)	Course_ID (PK)	Marks
101	C01	85
101	C02	78
102	C01	91

Now, the non-key attributes depend on the whole key in every single table. The design perfectly satisfies 2NF!

The Golden Rule for 2NF: 1NF + No Partial Dependency = 2NF

4. Third Normal Form (3NF)

A relation is in Third Normal Form (3NF) if:

1. It is already in 2NF.

2. There is no transitive dependency of a non-key attribute on the primary key.

What is Transitive Dependency?

Transitive dependency happens when a non-key column depends on another non-key column, which in turn depends on the primary key. (Think of it as: A → B and B → C, therefore A → C).

Let's look at a problematic table:

Student_ID (PK)	Student_Name	Dept_ID	Dept_Name
101	Rahul	D01	CSE
102	Priya	D02	ECE
103	Amit	D01	CSE

The Dependencies:

• Student_ID → Student_Name (Valid)

• Student_ID → Dept_ID (Valid)

• Dept_ID → Dept_Name (Problem!)

Here, Dept_Name does not directly depend on the Student_ID. Instead, it depends on Dept_ID. This is a transitive dependency.

Code snippet

graph LR
    A[Student_ID] -->|Direct| B(Dept_ID)
    B -->|Direct| C(Dept_Name)
    A -.->|Transitive Dependency| C

How to Convert into 3NF

To fix this, we remove the transitively dependent column and put it in its own table.

Table 1: STUDENT

Student_ID (PK)	Student_Name	Dept_ID (FK)
101	Rahul	D01
102	Priya	D02
103	Amit	D01

Table 2: DEPARTMENT

Dept_ID (PK)	Dept_Name
D01	CSE
D02	ECE

Now, Dept_ID determines Dept_Name in its own table, and the transitive dependency is gone.

BCNF Takeaway: If a column determines another column, it must hold the power of a primary/candidate key.

5. Boyce-Codd Normal Form (BCNF)

BCNF is a stricter, stronger version of 3NF. A relation is in BCNF if:

• For every non-trivial functional dependency X → Y, X must be a super key.

In simple words: Every determinant must be a candidate key.

The Problem Scenario

Imagine a table where a student takes a course taught by a specific instructor.

Rule: An instructor teaches only one course, but a course can have multiple instructors.

Student	Course	Instructor
Rahul	DBMS	Sharma
Priya	DBMS	Sharma
Amit	OS	Verma

Here, the composite Primary Key is (Student, Course).

However, because an instructor teaches only one course, Instructor → Course.

The problem? Instructor is a determinant, but it is NOT a candidate key for the whole table. This violates BCNF and causes redundancy (notice how "Sharma" and "DBMS" are repeated).

Decomposing to BCNF

We fix this by splitting the tables so that the determinant becomes a primary key.

Table 1: INSTRUCTOR_COURSE

Instructor (PK)	Course
Sharma	DBMS
Verma	OS

Table 2: STUDENT_INSTRUCTOR

Student (PK)	Instructor (PK)
Rahul	Sharma
Priya	Sharma
Amit	Verma

BCNF Takeaway: If a column determines another column, it must hold the power of a Primary/Candidate key.

6. Fourth Normal Form (4NF)

4NF deals specifically with multivalued dependencies. A relation is in 4NF if:

1. It is in BCNF.

2. It has no problematic multivalued dependency.

Example of Multivalued Dependency

Suppose we track a student's skills and their hobbies. Skills and hobbies are completely independent of each other.

Student	Skill	Hobby
Rahul	Python	Cricket
Rahul	Python	Music
Rahul	Java	Cricket
Rahul	Java	Music

Because Rahul has 2 skills and 2 hobbies, the database is forced to store every possible combination (2x2 = 4 rows). If he adds a new hobby, we have to add two new rows to pair it with both Python and Java. This is a nightmare to maintain.

Decomposing to 4NF

Separate the independent multivalued facts into their own tables:

Table 1: STUDENT_SKILL

Student	Skill
Rahul	Python
Rahul	Java

Table 2: STUDENT_HOBBY

Student	Hobby
Rahul	Cricket
Rahul	Music

4NF Takeaway: Don't cram independent, multi-valued attributes into a single table. Separate them to avoid forced data combinations.

7. Fifth Normal Form (5NF)

5NF is also known as Project-Join Normal Form (PJ/NF). It deals with join dependencies.

A relation is in 5NF when:

• Every non-trivial join dependency is implied by the candidate keys.

In simpler terms, 5NF ensures that if you break a table down into smaller tables, you can join them back together to recreate the exact original table without creating fake/incorrect records (known as the "lossless join" property).

Example Scenario

Consider a complex 3-way relationship between Suppliers, Parts, and Projects:

Supplier	Part	Project
S1	P1	J1
S1	P2	J1
S2	P1	J2

In highly complex situations, this single table might suffer from update anomalies. To reach 5NF, this data would be decomposed into three separate relationship tables:

1. Supplier ↔ Part

2. Supplier ↔ Project

3. Part ↔ Project

Note: 5NF is highly theoretical and relatively uncommon in ordinary application databases. For 99% of web and business applications, normalizing up to 3NF or BCNF is perfectly sufficient!

Quick Comparison

Normal Form	Main Problem Removed
1NF	Repeating groups / non-atomic values
2NF	Partial dependency
3NF	Transitive dependency
BCNF	Determinants that are not candidate keys
4NF	Multivalued dependency
5NF	Join dependency

Easy Way to Remember

Think of normalization as progressively cleaning a database:

UNF
 ↓
Remove repeating values
 ↓
1NF
 ↓
Remove partial dependency
 ↓
2NF
 ↓
Remove transitive dependency
 ↓
3NF
 ↓
Strengthen determinant rules
 ↓
BCNF
 ↓
Remove multivalued dependency
 ↓
4NF
 ↓
Remove join dependency
 ↓
5NF

The most important for exams

For most DBMS examinations, focus especially on:

1NF → 2NF → 3NF → BCNF

And remember these four keywords:

1NF = Atomicity

2NF = Partial Dependency

3NF = Transitive Dependency

BCNF = Every determinant is a candidate key