Question

State and explain 2NF with example.

Answer 1

Of course. Here is a clear and detailed explanation of Second Normal Form (2NF), complete with its rule and a step-by-step example.

---

Introduction

Second Normal Form (2NF) is the second step in the process of database normalization. Its primary goal is to remove a specific type of data redundancy called partial dependency.

State the Rule

A table is in Second Normal Form (2NF) if it meets two conditions:
1. It is already in First Normal Form (1NF).
2. It contains no partial dependencies.

This means that every non-primary key attribute must be fully functionally dependent on the entire primary key.

Explain the Rule

Let's break that down:

• First Normal Form (1NF): The table must have all atomic values (one value per cell) and a primary key.
• Partial Dependency: This is the key concept for 2NF. A partial dependency exists only when a table has a composite primary key (a primary key made up of two or more columns). It occurs when a non-key column depends on only a part of the composite primary key, not the whole key.
• Fully Functionally Dependent: A column is fully dependent on the primary key if it needs all parts of the key to be uniquely identified.

In simpler terms: The 2NF rule says, "Every non-key column must provide information about the entire key, not just a piece of it." If a column only describes a part of the key, it belongs in a different table.

Important Note: If a table is in 1NF and has a single-column primary key, it is automatically in 2NF because partial dependencies are impossible.

---

Example

Let's walk through an example of a table that is in 1NF but violates 2NF, and then fix it.

Scenario: We have a table to track which students are enrolled in which courses and their grades.

Step 1: The Unnormalized Table (Not in 2NF)

To make each row unique, the primary key for this table must be a composite key of (StudentID, CourseID).

Student_Grades Table:
| StudentID (PK) | CourseID (PK) | StudentName | CourseName | Grade |
| :--- | :--- | :--- | :--- | :--- |
| 101 | CS101 | Alice | Intro to C++ | A |
| 101 | MTH203 | Alice | Calculus II | B |
| 102 | CS101 | Bob | Intro to C++ | B |
| 103 | PHY101 | Charlie | Physics I | C |

This table is in 1NF because every cell has a single, atomic value.

Step 2: Identify the Partial Dependencies

Now, let's analyze the dependencies of the non-key columns (StudentName, CourseName, Grade) on the composite primary key (StudentID, CourseID):

1. Grade: To know a student's grade, you need to know both the StudentID and the CourseID. Therefore, Grade is fully dependent on the entire primary key. (This is good!)

2. StudentName: To know the student's name, you only need the StudentID. The CourseID is irrelevant. Therefore, StudentName is partially dependent on the primary key. (This violates 2NF!)

3. CourseName: To know the course's name, you only need the CourseID. The StudentID is irrelevant. Therefore, CourseName is also partially dependent on the primary key. (This also violates 2NF!)

Step 3: Recognize the Problems (Anomalies)

These partial dependencies cause data redundancy and lead to several problems:

• Update Anomaly: If student Alice changes her name, you must update it in multiple rows. If you miss one, the data becomes inconsistent. Similarly, if the name of course "CS101" changes, it must be updated in multiple places.
• Insertion Anomaly: You cannot add a new student to the system until they enroll in at least one course because CourseID is part of the primary key and cannot be null.
• Deletion Anomaly: If Charlie is the only student in "Physics I" and he drops the course, deleting that row will also delete the information that a course named "Physics I" even exists.

Step 4: Decompose the Table to Achieve 2NF

The solution is to remove the partial dependencies by splitting the original table into smaller, well-structured tables. The columns that are partially dependent are moved into new tables along with the part of the key they depend on.

1. Students Table:
This table holds the information that depends only on StudentID.
| StudentID (PK)| StudentName |
| :--- | :--- |
| 101 | Alice |
| 102 | Bob |
| 103 | Charlie |

2. Courses Table:
This table holds the information that depends only on CourseID.
| CourseID (PK)| CourseName |
| :--- | :--- |
| CS101 | Intro to C++ |
| MTH203 | Calculus II |
| PHY101 | Physics I |

3. Enrollment Table:
This table holds the information that is fully dependent on the composite key (StudentID, CourseID).
| StudentID (PK, FK) | CourseID (PK, FK) | Grade |
| :--- | :--- | :--- |
| 101 | CS101 | A |
| 101 | MTH203 | B |
| 102 | CS101 | B |
| 103 | PHY101 | C |

Now, all three tables are in 2NF.
The Students and Courses tables have single-column primary keys, so they are automatically in 2NF.
In the Enrollment table, the only non-key attribute (Grade) is fully dependent on the entire composite key.
* The data redundancy and the update/insertion/deletion anomalies have been eliminated.