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
- Eliminates duplicate data, saving storage space.
- Ensures consistency – a change in one place updates all related records.
- Prevents anomalies that can corrupt data during CRUD operations.
- Improves query performance by reducing the amount of data scanned.
- Facilitates easier maintenance and scalability of the database schema.
First Normal Form (1NF)
Definition: A table is in 1NF when every column contains atomic (indivisible) values and each record is unique.
Key requirements:
- No repeating groups or arrays.
- Each field holds a single value.
- Presence of a primary key.
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:
ProductNameandUnitPricedepend only onProductID(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:
DeptNameandManagerdepend onDeptID, not directly onEmpID(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
- 1NF → atomic values, unique rows.
- 2NF → eliminate partial dependencies (applies to composite keys).
- 3NF → eliminate transitive dependencies.
- Higher normal forms (BCNF, 4NF, 5NF) exist but are rarely required for PSC exams.
- Normalization is a trade‑off: excessive splitting can increase join complexity; denormalization may be used for performance tuning.
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:
- Government HR systems: employee, department, and designation tables.
- Banking transaction databases: accounts, customers, and transaction logs.
- E‑commerce platforms: product catalog, order details, and supplier information.
Quick Review Questions & Answers (अभ्यासका लागि प्रश्नोत्तर)
- What is the main purpose of 1NF?
To ensure each column holds atomic values and each row is uniquely identifiable. - Define partial dependency.
When a non‑key attribute depends on only part of a composite primary key. - Why is 3NF important for data integrity?
It removes transitive dependencies, preventing redundant storage and inconsistent updates. - 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. - 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
- Which normal form eliminates repeating groups?
A) 1NF B) 2NF C) 3NF D) BCNF
Answer: A - In a table with composite key (OrderID, ProductID), the attribute
ProductNamedepends only onProductID. This violates which normal form?
A) 1NF B) 2NF C) 3NF D) None
Answer: B - Which of the following is a transitive dependency?
A) EmpID → DeptID B) DeptID → DeptName C) EmpID → DeptName D) All of the above
Answer: C