Database Normal Forms: 1NF, 2NF, 3NF, and BCNF Explained

Overview: The Normalization Ladder

Normalization is organized into hierarchical levels called Normal Forms. Each higher normal form is strictly stricter than the one before it:

1. First Normal Form (1NF)

Rule:

A relation is in 1NF if and only if all attribute values are Atomic (indivisible single values), and there are no repeating groups or multi-valued arrays in any cell.

Violating Example (Unnormalized Table):

• The cell 9826011111, 9425022222 contains multiple values. This violates 1NF!

Converted to 1NF:

• Every cell now holds strictly one single atomic value. The relation is in 1NF.

2. Second Normal Form (2NF)

Rule:

A relation is in 2NF if and only if:

1. It is already in 1NF.
2. There are NO Partial Functional Dependencies (Every non-prime attribute must be fully functionally dependent on the primary key).

• Prime Attribute: An attribute that is part of any Candidate Key.
• Non-Prime Attribute: An attribute that is NOT part of any Candidate Key.
• Partial Dependency: When a non-prime attribute depends on only a part of a composite candidate key.
  (Note: If a table’s candidate key consists of only a single column, the table is automatically in 2NF!).

Violating Example:

Consider table PROJECT_ASSIGNMENT: [ Emp_ID, Project_ID, Hours_Worked, Emp_Name ]

• Candidate Key: {Emp_ID, Project_ID} (Composite Key).
• Dependencies:
  1. {Emp_ID, Project_ID} → Hours_Worked (Full dependency - valid!).
  2. Emp_ID → Emp_Name (Partial Dependency! Emp_Name depends only on Emp_ID, ignoring Project_ID).

Decomposing into 2NF:

We split the offending partial dependency into a separate table:

1. EMPLOYEE: [ <u>Emp_ID</u>, Emp_Name ] (Candidate Key: Emp_ID)
2. PROJECT_ASSIGNMENT: [ <u>Emp_ID, Project_ID</u>, Hours_Worked ] (Candidate Key: {Emp_ID, Project_ID})

Both tables are now in 2NF!

3. Third Normal Form (3NF)

Rule:

A relation is in 3NF if and only if:

1. It is already in 2NF.
2. There are NO Transitive Dependencies for non-prime attributes.

Formal Mathematical Condition for 3NF:

For every non-trivial functional dependency X → Y:

• Either X is a Super Key, OR
• Y is a Prime Attribute (part of a candidate key).

Violating Example:

Consider table STUDENT_DETAILS: [ <u>Roll_No</u>, Student_Name, Dept_ID, Dept_Name, Dept_HOD ]

• Candidate Key: Roll_No.
• Dependencies:
  1. Roll_No → Dept_ID (Roll_No is a Super Key - valid!).
  2. Dept_ID → {Dept_Name, Dept_HOD} (Transitive Dependency! Roll_No → Dept_ID and Dept_ID → Dept_Name. Neither is Dept_ID a super key, nor is Dept_Name a prime attribute!).

Decomposing into 3NF:

Remove the transitive relationship into its own relation:

1. STUDENT: [ <u>Roll_No</u>, Student_Name, Dept_ID ]
2. DEPARTMENT: [ <u>Dept_ID</u>, Dept_Name, Dept_HOD ]

Now, both tables are in 3NF!

4. Boyce-Codd Normal Form (BCNF / 3.5 NF)

Developed by Raymond F. Boyce and Edgar F. Codd in 1974, BCNF is a stricter version of 3NF designed to handle tables that have multiple overlapping candidate keys.

Rule:

A relation is in BCNF if and only if for every non-trivial functional dependency X → Y, X MUST BE A SUPER KEY.

• Notice that BCNF removes the second lenient loophole of 3NF (“Y is a prime attribute”).
• In BCNF, the left-hand determinant X must always be a Super Key, with zero exceptions!

Classic Exam Scenario:

Consider relation STUDENT_COUNSELOR(Student, Subject, Counselor):

• Rules:
  1. A student can choose multiple subjects.
  2. For each subject, multiple counselors exist, but each counselor teaches only one subject (Counselor → Subject).
  3. A student is assigned at most one counselor per subject ({Student, Subject} → Counselor).
• Candidate Keys: {Student, Subject} and {Student, Counselor}.
• Prime Attributes: Student, Subject, Counselor.
• Check 3NF:
  • For Counselor → Subject: Counselor is NOT a super key, BUT Subject is a Prime Attribute! Therefore, the relation IS in 3NF.
• Check BCNF:
  • In Counselor → Subject, Counselor is NOT a Super Key! Therefore, it FAILS BCNF.

Decomposing into BCNF:

1. COUNSELOR_SUBJECT: [ <u>Counselor</u>, Subject ] (Key: Counselor)
2. STUDENT_COUNSELOR: [ <u>Student, Counselor</u> ] (Key: {Student, Counselor})

Both tables are now in BCNF!

5. Summary Cheat Sheet of Normal Forms

💡 DAVV Exam Preparation Tips

5-Mark Question: “What is BCNF? How does BCNF differ from 3NF?”

• State the formal condition: For all X → Y, X must be a super key.
• Explain that 3NF allows Y to be a prime attribute when X is not a super key, whereas BCNF strictly disallows this.
• Conclude that every BCNF relation is in 3NF, but not every 3NF relation is in BCNF.

10-Mark Question: “Explain 1NF, 2NF, 3NF, and BCNF with step-by-step examples. Take an unnormalized table and decompose it through all stages.”

• Show the unnormalized multi-valued table and convert it to 1NF.
• Identify the composite key and show partial dependency removal for 2NF.
• Identify transitive dependency (Dept_ID → HOD) and decompose into 3NF.
• Provide the Student-Subject-Counselor example to demonstrate BCNF decomposition.