Second Normal Form (2NF)

Introduction

Second Normal Form (2NF): requirement for relational database design. Prerequisite: table must be in First Normal Form (1NF). Goal: eliminate partial dependencies. Non-key attributes must depend on entire primary key, not part of composite key.

Core problem addressed: composite primary keys where non-key attributes depend only on part of key. Example: StudentCourse table (StudentID, CourseID, pk; StudentName depends only on StudentID, not CourseID). Causes redundancy, update anomalies.

Historical: Codd introduced 2NF after 1NF to address functional dependencies. Many tables naturally in 2NF. Becoming clear design practice.

"Second Normal Form eliminates partial dependencies, ensuring non-key attributes depend on complete primary key. Removes redundancy, prevents anomalies in tables with composite keys." -- Database normalization principles

2NF Definition

Formal Definition

Relation in 2NF if: (1) in 1NF, (2) every non-key attribute fully functionally dependent on primary key. No non-key attribute depends on proper subset of composite primary key.

Requirements

Must satisfy 1NF first: atomic values, no repeating groups. Then: if primary key composite (multiple columns), no non-key attribute depends on part of key. All non-key attributes depend on entire key.

Implication

Simple primary keys (single column): all non-key attributes naturally depend on whole key. 2NF automatically satisfied. Composite keys: must verify no partial dependencies.

Practical Meaning

Each row uniquely identified by composite key. Non-key attributes describe that unique combination, not individual key parts. Data organized logically around complete key.

Partial Dependencies

Definition

Partial dependency: non-key attribute depends on part (not whole) of composite primary key. Example: StudentCourse(StudentID, CourseID, StudentName). StudentName depends on StudentID only (partial dependency on composite key).

Identification

Check each non-key attribute: does it depend on entire key? If attribute determines without full key, partial dependency exists. Example: given StudentID alone, can determine StudentName (even without CourseID).

Example

StudentCourse table (violates 2NF):StudentID | CourseID | StudentName | Grade101 | CS101 | Alice | A101 | CS202 | Alice | B102 | CS101 | Bob | AStudentName: partial dependency on StudentIDGrade: full dependency on (StudentID, CourseID)

Why Problematic

Redundancy: StudentName "Alice" repeated for every course. Update anomaly: change Alice's name requires updating multiple rows. Deletion anomaly: remove student from course, lose student info.

Detection Method

For each non-key attribute: can you determine it knowing only part of composite key? Yes = partial dependency. Visual inspection or formal analysis.

Full Functional Dependencies

Definition

Full functional dependency: attribute depends on entire key, not proper subset. Example: Grade depends on (StudentID, CourseID) together,neither alone determines grade.

Notation

A -> B (A functionally determines B). StudentID -> StudentName (StudentName depends on StudentID). (StudentID, CourseID) -> Grade (Grade depends on both).

Verification

For each non-key attribute: does primary key fully functionally determine it? Without all key columns, can you know the value? No = full dependency (good). Yes = partial dependency (bad).

Examples

Employee(EmpID, Name, Salary, DeptID): all non-key attributes fully depend on EmpID (simple key). Grade depends on (StudentID, CourseID) fully (composite key).

Key Property

Primary key always fully determines all other attributes (definition of primary key). Issue: composite keys with non-key attributes depending on part of key.

Composite Keys and Dependencies

Composite Key Definition

Primary key consisting of multiple columns. Together uniquely identify row. Example: (StudentID, CourseID) composite key in Enrollment table.

Dependency Challenge

Composite keys introduce complexity: non-key attributes may depend on part of key. Simple keys avoid: single column key means non-key attributes depend on that column (full dependency).

Problem Scenario

StudentCourse(StudentID pk, CourseID pk, StudentName, Grade):- StudentName depends only on StudentID (partial dependency)- Grade depends on (StudentID, CourseID) (full dependency)Violates 2NF: StudentName is partial dependency

Solution

Decompose into multiple tables: Student(StudentID, StudentName), Enrollment(StudentID fk, CourseID, Grade). Each table: non-key attributes fully depend on primary key.

Decomposition Strategy

For each partial dependency: create separate table. Original table keeps composite key + attributes with full dependency. New table: attribute with partial dependency + key part it depends on.

2NF Violations

Violation Pattern

Table in 1NF but not 2NF: has composite primary key with partial dependencies. Non-key attributes depend on part of key.

Example Violation

Supplier_Product(SupplierID, ProductID, SupplierName, Price):Primary key: (SupplierID, ProductID)SupplierName: depends on SupplierID alone (partial)Price: depends on both (full)Violates 2NF

Consequences

Redundancy: SupplierName repeated for every product. Insertion anomaly: add new supplier without product impossible (composite key requires both). Update anomaly: change supplier name needs multiple updates. Deletion anomaly: remove product loses supplier info.

Recognition

Pattern: composite key, non-key attribute seems to describe only part of key. Example: table has (OrderID, ItemID) key, but Item_Description depends only on ItemID. Indicates 2NF violation.

Converting to 2NF

Decomposition Method

Identify partial dependencies. For each: create new table. Original table keeps composite key + fully dependent attributes. New table: partial key + non-key attribute dependent on it.

Example Conversion

Violates 2NF:StudentCourse(StudentID, CourseID, StudentName, Grade)Partial dependency: StudentName -> StudentIDDecompose:Student(StudentID pk, StudentName)Enrollment(StudentID fk, CourseID pk, Grade)

Preservation

Original data preserved: join Student and Enrollment recovers original table. No information lost, only restructured. Foreign key ensures consistency.

Verification

After conversion: check each table. Single primary key OR (if composite) all non-key attributes fully depend on entire key. Verify no partial dependencies remain.

Foreign Keys

Critical: establish foreign keys between decomposed tables. Maintains relationships. Example: Enrollment.StudentID references Student.StudentID.

Detailed Examples

Example 1: Supplier-Product

Before (violates 2NF):Supplier_Product:SupplierID | ProductID | SupplierName | ProductPrice | CityS1 | P1 | ACME | 10.00 | NYCS1 | P2 | ACME | 15.00 | NYCS2 | P1 | TechCorp | 12.00 | LAPartial dependencies:SupplierName, City -> SupplierID onlyProductPrice -> ProductID onlyAfter (2NF):Supplier(SupplierID pk, SupplierName, City)Product(ProductID pk, ProductPrice)Supplier_Product(SupplierID fk, ProductID fk)

Example 2: Course Registration

Before (violates 2NF):StudentCourse:StudentID | CourseID | StudentName | Instructor | Grade1 | C1 | Alice | Dr. Smith | A1 | C2 | Alice | Dr. Jones | B2 | C1 | Bob | Dr. Smith | APartial dependencies:StudentName -> StudentIDInstructor -> CourseIDAfter (2NF):Student(StudentID pk, StudentName)Course(CourseID pk, Instructor)Enrollment(StudentID fk, CourseID fk, Grade)

Example 3: Simple Key (Already 2NF)

Employee(EmpID pk, Name, Salary, DeptID):No composite key, so all non-key attributes fully depend on EmpID.Already in 2NF. No decomposition needed.

Redundancy Elimination

Redundancy Source

Partial dependencies cause redundancy: attribute appears multiple times unnecessarily. Example: "ACME" supplier name repeated for every product ACME supplies.

Storage Waste

Significant for large datasets. Thousands of products: supplier name repeated thousands of times. 2NF eliminates: store name once.

Maintenance Burden

Update supplier name: must update all rows (error-prone). 2NF: update once in Supplier table. Efficient, less error-prone.

Quantification

Before 2NF: storage proportional to product count per supplier. After: constant. For supplier supplying 1000 products: save 999 copies of name.

Consistency

Redundancy risks inconsistency: one copy updated, others miss. 2NF single source of truth. Consistency guaranteed.

Fixing Update Anomalies

Insertion Anomaly

Before: add new supplier without product impossible (composite key requires both). After: insert Supplier row independently. Can exist without products.

Update Anomaly

Before: change supplier name requires updating all rows for all products. Expensive, error-prone. After: single update in Supplier table. Efficient, safe.

Deletion Anomaly

Before: delete last product loses supplier information. After: delete Enrollment row, Supplier remains. Information preserved.

Verification

After 2NF conversion: verify anomalies resolved. Can insert partial data. Updates localized. Deletions non-destructive (logically).

Real-World Impact

Large tables: anomalies critical performance/correctness issues. 2NF essential for reliable data management. Standard practice.

Composite Keys vs. Simple Keys

Simple Keys

Single column primary key: always in 2NF (if in 1NF). All non-key attributes depend on single key. No partial dependencies possible.

Composite Keys

Multiple column primary key: must verify 2NF. Possible partial dependencies. More careful design required.

Trade-off

Composite keys: represent real-world relationships directly. Example: (StudentID, CourseID) naturally identifies enrollment. But risk partial dependencies.

Design Strategy

Often: use surrogate key (single system-generated ID) to avoid composite key issues. Example: EnrollmentID instead of (StudentID, CourseID). Simpler, automatically 2NF.

Comparison

Practical Applications

Database Design

2NF standard practice: most business databases in 2NF. Eliminates common anomalies. Required for reliable data management.

Schema Validation

Tools check 2NF compliance. Warning if partial dependencies detected. Guides designers toward better schemas.

Legacy System Modernization

Old systems may violate 2NF. Modernization: convert to 2NF. Improves reliability, reduces maintenance burden.

Performance Tuning

Sometimes: denormalize (violate 2NF) for performance. Join elimination, caching. Trade consistency for speed. Justified only when necessary, documented.

Data Migration

Moving data between systems: target 2NF schema. Ensures consistency, maintainability. Source data restructured if necessary.

References

• Codd, E. F. "Further Normalization of the Data Base Relational Model." IBM Research Report RJ909, 1971.
• Elmasri, R., and Navathe, S. B. "Fundamentals of Database Systems." Pearson, 7th edition, 2016.
• Date, C. J. "Database in Depth: Relational Theory for Practitioners." O'Reilly Media, 2005.
• Silberschatz, A., Korth, H. F., and Sudarshan, S. "Database System Concepts." McGraw-Hill, 6th edition, 2010.
• Kent, W. "A Simple Guide to Five Normal Forms in Relational Database Theory." Communications of the ACM, vol. 26, no. 2, 1983, pp. 120-125.