ReadyToGo Logo ReadyToGo
Login Sign Up
› Module 4: Normalization & Functional Dependencies › 4.2 1NF, 2NF, and 3NF: Step-by-Step Normalization
M O D U L E

Module 4: Normalization & Functional Dependencies

Eliminating data redundancy and update anomalies to achieve database purity

4.1 Why Normalization? Anomalies & Functional Dependencies

Database Normalization is the systematic process of organizing relation schemas to minimize data redundancy, avoid update anomalies, and maximize referential integrity. In an unnormalized database, duplicate data wastes disk space and creates operational vulnerabilities during routine inserts, updates, and deletes.

Figure 4.1: Functional Dependency (X → Y) & Relational Anomalies Functional Dependency: X → Y X (Det) Determinant Y (Dep) Dependent Rule: Whenever two tuples agree on X, they MUST strictly agree on Y. Example: Roll_No → Student_Name The Three Relational Anomalies 1. Insertion Anomaly Cannot insert course without enrolling a student first (null PK). 2. Deletion Anomaly Deleting the only student drops course information completely. 3. Update Anomaly Updating address requires changing 100 duplicate rows.

Figure 4.1: Functional Dependencies dictate that determinant X uniquely identifies dependent Y. Flawed schemas cause Insertion, Deletion, and Update anomalies.

Types of Functional Dependencies

  • Trivial FD: If $Y subseteq X$, then $X ightarrow Y$ is trivial (e.g., ${Roll_No, Name} ightarrow Name$). It conveys no new constraints.
  • Non-Trivial FD: If $Y otsubseteq X$, then $X ightarrow Y$ is non-trivial (e.g., $Roll_No ightarrow Name$).
  • Full Functional Dependency: Attribute $Y$ is functionally dependent on composite attribute $X$, and cannot depend on any proper subset of $X$.
  • Partial Dependency: Attribute $Y$ is dependent on a proper subset of a composite candidate key. (Violates 2NF).
  • Transitive Dependency: If $X ightarrow Y$ and $Y ightarrow Z$ (where $Y$ is not a candidate key and $Z$ is non-prime), then $X ightarrow Z$ is transitive. (Violates 3NF).

Armstrong's Axioms

A sound and complete set of inference rules used to derive all functional dependencies implied by a given set $F$:

  1. Reflexivity: If $Y subseteq X$, then $X ightarrow Y$.
  2. Augmentation: If $X ightarrow Y$, then $XZ ightarrow YZ$ for any attribute set $Z$.
  3. Transitivity: If $X ightarrow Y$ and $Y ightarrow Z$, then $X ightarrow Z$.
  4. Union (Derived): If $X ightarrow Y$ and $X ightarrow Z$, then $X ightarrow YZ$.
  5. Decomposition (Derived): If $X ightarrow YZ$, then $X ightarrow Y$ and $X ightarrow Z$.
  6. Pseudo-transitivity (Derived): If $X ightarrow Y$ and $WY ightarrow Z$, then $WX ightarrow Z$.

4.2 1NF, 2NF, and 3NF: Step-by-Step Normalization

Normalization progresses in stages called Normal Forms (NFs). Each normal form enforces a stricter set of structural constraints than the preceding one, systematically stripping away partial and transitive dependencies.

Figure 4.2: The Normal Forms Progression Hierarchy 1NF Atomic Values • No multi-valued attrs • No repeating groups • Unique row ordering • Defined primary key 2NF No Partial Deps • Must be in 1NF • Non-prime attributes must depend on FULL candidate key • Split composite tables 3NF No Transitive Deps • Must be in 2NF • For every X → A: - X is Super Key, OR - A is Prime Attribute • Eliminates non-key hops BCNF Strict Determinant • Must be in 3NF • For EVERY X → Y: X MUST be a Super Key strictly • No prime exception

Figure 4.2: Normal Forms are strictly nested: BCNF ⊂ 3NF ⊂ 2NF ⊂ 1NF. Each level eliminates a distinct class of redundancy.

1NF: First Normal Form

A relation is in 1NF if and only if all attribute values are atomic (indivisible). Columns containing comma-separated lists, JSON blobs, or repeating groups violate 1NF.

-- VIOLATION of 1NF:
-- Student(ID: 101, Name: "Aman", Phones: "9876543210, 9123456789")

-- RESOLUTION into 1NF:
-- Split multi-valued attribute into separate rows or a dedicated child table:
-- Student_Phones(StudentID, Phone) -> (101, "9876543210"), (101, "9123456789")

2NF: Second Normal Form

A relation is in 2NF if it is in 1NF and no non-prime attribute is partially dependent on any candidate key. Note: If a table's candidate key contains only a single column, it is automatically in 2NF!

-- Given Enrollments(StudentID, CourseID, CourseFee, Grade)
-- Composite Candidate Key: {StudentID, CourseID}
-- Partial Dependency: CourseID -> CourseFee (CourseFee depends only on part of the PK!)

-- RESOLUTION into 2NF: Decompose into two tables:
-- 1. Enrollments(StudentID, CourseID, Grade) -> PK: {StudentID, CourseID}
-- 2. Courses(CourseID, CourseFee)           -> PK: CourseID

3NF: Third Normal Form

A relation is in 3NF if it is in 2NF and no non-prime attribute is transitively dependent on the primary key. Formally, for every non-trivial functional dependency $X ightarrow A$:

  • Either $X$ is a Super Key, OR
  • $A$ is a Prime Attribute (member of some candidate key).

4.3 Boyce-Codd Normal Form (BCNF) & Higher Normal Forms

Boyce-Codd Normal Form (BCNF) is a stricter version of 3NF (often referred to as 3.5NF). It eliminates the "prime attribute exception" permitted by 3NF.

Formal BCNF Definition:

A relation $R$ is in BCNF if and only if for every non-trivial functional dependency $X ightarrow Y$, the determinant $X$ is strictly a Super Key.

The Classic 3NF vs BCNF Dilemma

Consider a relation Student_Counselor(StudentID, Subject, Advisor) with constraints:

  • A student has one advisor per subject: {StudentID, Subject} → Advisor
  • Each advisor teaches only one subject: Advisor → Subject
  • Candidate Keys: {StudentID, Subject} and {StudentID, Advisor}

Check Advisor → Subject: Advisor is NOT a super key, but Subject is a prime attribute (part of {StudentID, Subject}). Therefore, this table is in 3NF, but violates BCNF!

Lossless Join vs Dependency Preservation

Normal Form Lossless Join Guaranteed? Dependency Preservation Guaranteed?
3NF YES YES
BCNF YES NO (May be lost!)

4NF and 5NF Overview

  • 4NF (Fourth Normal Form): Deals with Multi-valued Dependencies ($X woheadrightarrow Y$). Enforces that for every non-trivial MVD, $X$ is a superkey.
  • 5NF (Project-Join Normal Form / PJNF): Eliminates Join Dependencies, where a table cannot be losslessly reconstructed from smaller projections without including all original columns.
MODULE COMPREHENSIVE QUIZ

Module 4 Final Quiz

Test your understanding across all 3 topics of this module with interview-grade questions, tricky edge cases, and code output predictions.

Take Module 4 Final Quiz →
ReadyToGo · DBMS & SQL Study Guide
Yukti Mascot