2NF (Second Normal Form) is a normalization level that an already 1NF table reaches when it has no partial functional dependencies. A table is in 2NF if:

  • It is already in 1NF.

  • Every non‑key attribute is fully functionally dependent on the entire primary key, not just a part of it.

2NF mainly deals with tables that have composite primary keys (made of two or more attributes).

What Is a Partial Dependency?

A partial dependency occurs when a non‑key attribute depends on only a subset of the composite key, not the whole key.

Example:
Suppose a table RESULT has:

  • Roll_No, Course_ID, Branch, Marks.

If:

  • Roll_No → Branch (each student is in one branch),

  • but the primary key is {Roll_No, Course_ID},

then Branch is partially dependent on the key. This is not allowed in 2NF.

How to Achieve 2NF

To convert a 1NF table to 2NF:

  1. Identify partial dependencies (non‑key attributes that depend on part of the key).

  2. Create new tables to remove them.

Using the RESULT example:

  • Original 1NF table (with partial dependency):

    Roll_NoCourse_IDBranchMarks
    101C101CSE85
    101C102CSE78
    102C101ECE80
    Here, Branch depends only on Roll_No (partial dependency).

  1. Split into:

    • STUDENT( Roll_No, Branch ) → primary key: Roll_No

    • RESULT( Roll_No, Course_ID, Marks ) → primary key: {Roll_No, Course_ID}

Now:

  • In STUDENT, Roll_No → Branch (full dependency).

  • In RESULT, all non‑key attributes depend on the full key {Roll_No, Course_ID}.

  • Both tables are in 2NF.

Why 2NF Matters?

  • It removes redundant storage of repeated non‑key values (like Branch repeated for every course of a student).

  • It reduces update anomalies: you only need to update Branch once in the STUDENT table.

  • It prepares the design for 3NF by clearly separating dependencies.

Summary

2NF in DBMS means that a table is in 1NF and has no partial dependencies: every non‑key attribute depends on the whole primary key. To achieve 2NF, tables with composite keys and partial dependencies are split into smaller tables. This reduces redundancy and makes the database more consistent and easier to maintain.