---

Second Normal Form (2NF) in DBMS

---

The Second Normal Form (2NF) is the next stage in the normalization process after the First Normal Form (1NF). A relation is in 2NF if:

1. It is already in 1NF:

• This means the relation contains only atomic values, there are no duplicate rows, and it has a primary key.

2. No Partial Dependencies:

• All non-key attributes (i.e., columns that aren't part of the primary key) should be functionally dependent on the *entire* primary key. This rule is especially relevant for tables with composite primary keys (i.e., primary keys made up of more than one column).
• In simpler terms, no column should depend on just a part of the composite primary key.

Example for Second Normal Form

Let's consider a table that keeps track of the courses that students are enrolled in, with the faculty who teach those courses:

| Student_ID | Course_ID | Course_Name   | Faculty     |
|------------|-----------|---------------|-------------|
| 1          | C1        | Math          | Mr. A       |
| 1          | C2        | English       | Ms. B       |
| 2          | C1        | Math          | Mr. A       |
| 3          | C3        | History       | Ms. C       |

Here, a combination of `Student_ID` and `Course_ID` can be considered as a primary key because a student can be enrolled in multiple courses, and each course might be taken by many students.

However, you'll notice that `Course_Name` and `Faculty` depend only on `Course_ID` and not on the combination of `Student_ID` and `Course_ID`. This is a partial dependency.

To bring the table to 2NF, we need to remove the partial dependencies:

StudentCourse Table

| Student_ID | Course_ID |
|------------|-----------|
| 1          | C1        |
| 1          | C2        |
| 2          | C1        |
| 3          | C3        |

Course Table

| Course_ID | Course_Name   | Faculty     |
|-----------|---------------|-------------|
| C1        | Math          | Mr. A       |
| C2        | English       | Ms. B       |
| C3        | History       | Ms. C       |

Now, the `StudentCourse` table relates students to courses, and the `Course` table holds information about each course. There are no more partial dependencies.

It's worth noting that while 2NF does improve the structure of our database by reducing redundancy and eliminating partial dependencies, it might not eliminate all anomalies or redundancy. Further normalization forms (like 3NF and BCNF) address additional types of dependencies and potential issues.

---

Next Topic :Third Normal Form (3NF) in DBMS