What is Normalization?

Question

Accepted Answer

Normalization is the process of organizing a database's tables and columns to reduce data redundancy and prevent update anomalies. It works by splitting large, repetitive tables into smaller related tables connected through foreign keys, following a series of well-defined normal forms (1NF, 2NF, 3NF, and beyond).

Key Points:
• The goal is to store each piece of information in exactly one place, so updates only need to happen once.
• 1NF requires atomic column values with no repeating groups; 2NF removes partial dependencies on part of a composite key; 3NF removes dependencies between non-key columns.
• Normalization improves data integrity and reduces storage of duplicate data, at the cost of needing more JOINs to reassemble information.
• Over-normalizing can hurt read performance, which is why reporting systems sometimes intentionally denormalize data.

Example:
A training institute's student table repeats each instructor's name and email for every student in that instructor's course. Normalizing splits this into a students table and a separate instructors table linked by an instructor ID, so an email change only needs to happen in one row instead of many.

Code Example:
```
-- Before: instructor info repeated per student row
-- Student_ID | Student_Name | Course | Instructor | Instructor_Email

-- After normalization: two related tables
CREATE TABLE instructors (
    instructor_id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50),
    course VARCHAR(50),
    instructor_id INT,
    FOREIGN KEY (instructor_id) REFERENCES instructors(instructor_id)
);
```

Interview Tip:
A concise interview answer is:

"Normalization is organizing tables so each fact is stored once, typically by splitting a table with repeating data into related tables connected by foreign keys, following normal forms like 1NF through 3NF. It removes redundancy and update anomalies, though it trades off some read performance since you need joins to pull related data back together."