🗃️ Database Normalization: 1NF, 2NF, 3NF, BCNF, 4NF, 5NF | 🚀 Exam Prep

💡 Normalization is a frequent topic on data processing certification exams! Master it with this single post. A thorough understanding of each normal form, examples, and the final practice questions will bring you one step closer to passing.

📈 What is Database Normalization?

Normalization is the process of systematically decomposing tables to minimize data redundancy and ensure data integrity in a relational database. Normalization helps prevent anomalies and improves the stability and maintainability of the data structure.

• Anomalies: Problems that can occur due to data redundancy.
  • Insertion Anomaly: The inability to insert desired data without some other unnecessary data.
  • Deletion Anomaly: The loss of unintended data when certain information is deleted.
  • Update Anomaly: Data inconsistency that occurs when only some of the redundant data is updated.

---

🔑 Functional Dependency

To understand normalization, you must first grasp the concept of Functional Dependency.

In a relation, if the value of attribute A uniquely determines the value of attribute B, we say "attribute B is functionally dependent on attribute A," denoted as A → B. Here, A is the Determinant, and B is the Dependent.

• Full Functional Dependency: The dependent is determined by the entire primary key, not by any part of it. (Key to 2NF)
• Partial Functional Dependency: The dependent is determined by a part of the primary key.
• Transitive Functional Dependency: If A → B and B → C, then A → C holds. (Key to 3NF)

---

🔢 Summary of Normalization Stages

---

1️⃣ First Normal Form (1NF)

First Normal Form requires that all attribute values in a relation be atomic. This means a single attribute cannot hold multiple values.

• Example: If you store the courses a student is taking, putting multiple values like 'Data Structures, Algorithms' in a Courses attribute violates 1NF.

[Before Normalization]

[After 1NF Normalization]

---

2️⃣ Second Normal Form (2NF)

Second Normal Form requires that the relation is in 1NF and all partial functional dependencies are removed. This means all non-key attributes must be fully functionally dependent on the entire primary key. (If the primary key is a single attribute, the relation is always in 2NF).

• Example: In a course registration relation with (StudentID, CourseID) as the primary key, Grade depends on both (StudentID, CourseID), but ProfessorName depends only on CourseID (a partial functional dependency).

[Before Normalization] (Primary Key: StudentID, CourseID)

[After 2NF Normalization]

[Registration Table]

[Course Table]

---

3️⃣ Third Normal Form (3NF)

Third Normal Form requires that the relation is in 2NF and all transitive functional dependencies are removed. A transitive dependency exists when A → B and B → C, which leads to A → C.

• Example: If StudentID → DepartmentID and DepartmentID → DepartmentName, then there is a transitive dependency StudentID → DepartmentName.

[Before Normalization] (Primary Key: StudentID)

[After 3NF Normalization]

[Student Table]

[Department Table]

---

BCNF (Boyce-Codd Normal Form)

BCNF is a stricter version of 3NF. It requires that for every functional dependency, the determinant must be a candidate key. It addresses certain rare anomalies not handled by 3NF.

• Example: In a relation where (StudentID, Course) is a candidate key, if there's a constraint that a professor can only teach one course (Professor → Course), then Professor is a determinant but not a candidate key. This violates BCNF.

[Before Normalization] (Candidate Key: (StudentID, Course))

[After BCNF Normalization]

[Enrollment Table]

[ProfessorCourse Table]

---

4️⃣ Fourth Normal Form (4NF) & 5️⃣ Fifth Normal Form (5NF)

4NF and 5NF are rarely encountered in practice, but it's good to know the concepts.

• Fourth Normal Form (4NF): Eliminates multi-valued dependencies (MVD). This dependency arises in 1:N relationships.
• Fifth Normal Form (5NF): Deals with join dependencies (JD). It requires that every join dependency is implied by the candidate keys.

---

🔄 Denormalization

Denormalization is the process of intentionally violating normalization rules by combining tables or allowing redundancy to improve database performance. It is used to avoid costly joins, but it must be applied cautiously as it can risk data inconsistency.

• Common Techniques: Table merging, table splitting (horizontal/vertical), adding redundant columns.

---

📝 Exam Prep Practice Questions

➡️ Recommended Next Reads

• 🗃️ Database Integrity: Entity, Referential, Domain, User, Key | 🚀 Exam Prep with Questions
• Ultimate Guide to Acing the Information Processing Engineer Practical Exam 📝