Overview & Definition (परिचय तथा परिभाषा)

Normalization is a systematic approach to organizing data in a relational database to reduce redundancy and improve data integrity. By dividing large tables into smaller, related ones and defining relationships, normalization eliminates update, insertion, and deletion anomalies, leading to efficient storage and faster query performance.

Core Concepts (मुख्य अवधारणाहरू)

Why Normalization Matters

First Normal Form (1NF)

Definition: A table is in 1NF when every column contains atomic (indivisible) values and each record is unique.

Key requirements:

Example:

Unnormalized table – StudentCourses:

| StudentID | Name   | Courses               |
|-----------|--------|-----------------------|
| 101       | Ram    | Math, Physics         |
| 102       | Sita   | Chemistry             |
  

After converting to 1NF:

| StudentID | Name | Course   |
|-----------|------|----------|
| 101       | Ram  | Math     |
| 101       | Ram  | Physics  |
| 102       | Sita | Chemistry|
  

Second Normal Form (2NF)

Definition: A table is in 2NF if it is already in 1NF and every non‑key attribute is fully functionally dependent on the whole primary key (no partial dependency).

When it applies: Primarily to tables with composite primary keys.

Example:

Table in 1NF – OrderDetails (composite key: OrderID + ProductID):

| OrderID | ProductID | ProductName | UnitPrice | Quantity |
|---------|-----------|-------------|-----------|----------|
| 5001    | 200       | Pen         | 10        | 2        |
| 5001    | 201       | Notebook    | 30        | 1        |
  

Problem: ProductName and UnitPrice depend only on ProductID (partial dependency).

Decompose into two tables:

-- OrderLines (full dependency)
| OrderID | ProductID | Quantity |
|---------|-----------|----------|
| 5001    | 200       | 2        |
| 5001    | 201       | 1        |

-- Products (partial dependency removed)
| ProductID | ProductName | UnitPrice |
|-----------|-------------|-----------|
| 200       | Pen         | 10        |
| 201       | Notebook    | 30        |
  

Third Normal Form (3NF)

Definition: A table is in 3NF if it is in 2NF and no non‑key attribute is transitively dependent on the primary key (i.e., non‑key attributes depend only on the key, not on other non‑key attributes).

Example:

Table in 2NF – Employee:

| EmpID | EmpName | DeptID | DeptName | Manager |
|-------|---------|--------|----------|---------|
| 1     | Asha    | 10     | HR       | Ramesh  |
| 2     | Bimal   | 20     | Finance  | Sita    |
  

Issue: DeptName and Manager depend on DeptID, not directly on EmpID (transitive dependency).

Decompose into:

-- Employees
| EmpID | EmpName | DeptID |
|-------|---------|--------|
| 1     | Asha    | 10     |
| 2     | Bimal   | 20     |

-- Departments
| DeptID | DeptName | Manager |
|--------|----------|---------|
| 10     | HR       | Ramesh  |
| 20     | Finance  | Sita    |
  

Key Points / Rules

Comparison Table

Normal Form Condition Anomalies Resolved Typical Example
1NF Atomic columns, unique primary key Repeating groups, multi‑valued fields Student‑Courses list split into separate rows
2NF 1NF + no partial dependency on composite key Update anomalies for attributes dependent on part of the key Separate Product details from OrderLines
3NF 2NF + no transitive dependency Redundant data causing inconsistent updates Separate Department info from Employee table

Practical Application / Real‑World Relevance

In Nepal’s public sector and private enterprises, databases store citizen records, payroll, inventory, and exam results. Normalization ensures that a change—such as updating a department manager’s name—requires modification in only one place, preventing mismatched records. For competitive exams like Loksewa PSC, understanding normalization helps answer questions on database design, data integrity, and query optimization.

Typical scenarios:

Quick Review Questions & Answers (अभ्यासका लागि प्रश्नोत्तर)

  1. What is the main purpose of 1NF?
    To ensure each column holds atomic values and each row is uniquely identifiable.
  2. Define partial dependency.
    When a non‑key attribute depends on only part of a composite primary key.
  3. Why is 3NF important for data integrity?
    It removes transitive dependencies, preventing redundant storage and inconsistent updates.
  4. Give an example of an update anomaly.
    If a department’s manager is stored in every employee row, changing the manager requires updating many rows; missing one leads to inconsistency.
  5. When might you deliberately denormalize a database?
    When read‑performance is critical and the cost of joins outweighs storage redundancy, such as in reporting warehouses.

Practice MCQs

  1. Which normal form eliminates repeating groups?
    A) 1NF  B) 2NF  C) 3NF  D) BCNF
    Answer: A
  2. In a table with composite key (OrderID, ProductID), the attribute ProductName depends only on ProductID. This violates which normal form?
    A) 1NF  B) 2NF  C) 3NF  D) None
    Answer: B
  3. Which of the following is a transitive dependency?
    A) EmpID → DeptID  B) DeptID → DeptName  C) EmpID → DeptName  D) All of the above
    Answer: C