4 - Normal Forms: 1NF, 2NF, and 3NF — Topics & Learning Outcomes

Module Topics

Introduction to Normalization

• What Is Database Normalization? — Database normalization is the process of organizing a relational database's structure to reduce redundancy and improve data integrity.
• The Problem: Data Redundancy — Data redundancy occurs when the same piece of information is stored in multiple places within a database, leading to inconsistencies and wasted storage.
• The Goal: Data Integrity — Data integrity ensures that the information stored in a database is accurate, consistent, and trustworthy throughout its lifecycle.
• Types of Data Anomalies Normalization Prevents — Unnormalized databases are prone to three categories of anomalies: insertion anomalies, update anomalies, and deletion anomalies.
• The Concept of Normal Forms — Normal forms are a progressive set of criteria used to evaluate and improve the structure of database tables, numbered from 1NF upward.
• Key Dependencies Driving Normalization — Normalization is fundamentally about managing dependencies — specifically partial dependencies and transitive dependencies — between columns in a table.

First Normal Form (1NF)

• What Is First Normal Form (1NF)? — First Normal Form (1NF) is the foundational level of database normalization that establishes the basic structural rules a table must satisfy.
• The Atomicity Requirement — Atomicity means that each cell in a table must hold one and only one value — it cannot be split into smaller, meaningful parts.
• Eliminating Repeating Groups — Repeating groups occur when a table contains multiple columns that capture the same type of information, effectively encoding a list horizontally rather than vertically.
• Identifying a Primary Key — A valid primary key is required for a table to comply with 1NF, as it uniquely identifies each row in the table.
• Transforming an Unnormalized Table into 1NF: Step-by-Step — Converting an unnormalized table to 1NF involves a systematic process of identifying violations and restructuring the data accordingly.
• Before and After: A 1NF Example — Examining a concrete before-and-after comparison reinforces how 1NF rules apply in practice and what the transformed table should look like.

Second Normal Form (2NF)

• Prerequisites: 2NF Builds on 1NF — A table must already satisfy First Normal Form before it can be evaluated for Second Normal Form. 2NF adds an additional constraint on top of the atomicity and row uniqueness requirements established by 1NF.
• Understanding Functional Dependency — A functional dependency exists when the value of one attribute (or set of attributes) uniquely determines the value of another attribute. Recognizing functional dependencies is the foundation for understanding both partial and full dependencies in 2NF.
• Defining Partial Dependency — A partial dependency occurs when a non-key attribute is functionally dependent on only a subset (part) of a composite primary key, rather than on the full composite key. Partial dependencies are the specific problem that 2NF is designed to eliminate.
• The 2NF Rule: Full Functional Dependency — Second Normal Form requires that every non-key attribute be fully functionally dependent on the entire primary key, not just a part of it. This means removing all partial dependencies from the relation.
• Identifying Partial Dependencies in Practice — To identify partial dependencies, systematically test each non-key attribute against every proper subset of the composite primary key. If any non-key attribute is determined by a subset of the key, a partial dependency exists and must be resolved.
• Eliminating Partial Dependencies Through Decomposition — The solution to partial dependencies is table decomposition: splitting the original table into two or more smaller tables so that each table contains only fully dependent attributes. This process preserves all original data while eliminating redundancy.
• Benefits of Achieving 2NF — Eliminating partial dependencies through 2NF reduces data redundancy and prevents several common data anomalies that occur in tables with partial dependencies. These improvements make the database easier to maintain and more consistent.

Third Normal Form (3NF)

• What Is a Transitive Dependency? — A transitive dependency occurs when a non-key column depends on another non-key column rather than directly on the primary key.
• The 3NF Rule Defined — Third Normal Form (3NF) requires that a table already be in 2NF and that every non-key attribute depends only on the primary key — not on any other non-key attribute.
• Recognizing 3NF Violations in a Table — To spot a 3NF violation, examine each non-key column and ask whether its value is dictated by the primary key or by a different non-key column.
• Decomposing Tables to Achieve 3NF — Removing transitive dependencies requires splitting the original table into two or more tables so that each non-key attribute depends directly on a primary key.
• Step-by-Step 3NF Transformation Example — Walking through a concrete restructuring example reinforces how to apply the 3NF rules systematically from start to finish.
• Benefits of Achieving 3NF — Bringing a database to 3NF eliminates transitive dependencies and produces a cleaner, more maintainable schema with reduced redundancy.

Applying Normal Forms: End-to-End Practice

• Starting Point: Recognizing an Unnormalized Table — The normalization journey begins by examining a raw, unnormalized table and identifying all the structural problems it contains.
• Step 1 — Transforming to First Normal Form (1NF) — The first transformation eliminates repeating groups and multi-valued attributes so that every cell holds exactly one atomic value and every row is uniquely identifiable.
• Step 2 — Identifying Partial Dependencies Before 2NF — Before converting to 2NF, students must carefully inspect every non-key column to determine whether it depends on the entire composite primary key or only on part of it.
• Step 2 — Transforming to Second Normal Form (2NF) — Achieving 2NF means decomposing the 1NF table into smaller tables so that every non-key attribute is fully functionally dependent on the entire primary key of its table.
• Step 3 — Identifying Transitive Dependencies Before 3NF — With 2NF achieved, the next analysis pass looks for transitive dependencies — cases where a non-key attribute determines another non-key attribute rather than being determined solely by the primary key.
• Step 3 — Transforming to Third Normal Form (3NF) — Converting to 3NF removes transitive dependencies by extracting the determining non-key attribute and its dependents into a new table, leaving only direct key dependencies behind.
• Validating the Final Schema and Reviewing the Full Journey — The final step is a structured review to confirm that the fully normalized schema satisfies all three normal forms and that no data has been lost or incorrectly restructured.

Student Learning Outcomes

By the end of this module, students will be able to:

Course Outcomes (reference)