Second Normal Form (2NF) in DBMS

Normal Forms ensure that the data in the tables remain structured and efficient. After achieving First Normal Form (1NF), the next step in normalization is the Second Normal Form (2NF) that helps eliminate certain types of redundancy by addressing partial dependency. Read this chapter to learn in detail what is 2NF and how it is applied.

What is Second Normal Form (2NF)?

The rules for 2NF are straightforward. According to E. F. Codd, the father of relational databases,

• The table must already be in First Normal Form (1NF).
• There should be no partial dependency in the table.

Let's break down these rules further −

• 1NF Requirement − It means the table should not have multi-valued attributes. Each cell should have one and only one value.
• No Partial Dependency − A partial dependency occurs when a non-prime attribute depends on only a part of a composite candidate key; not only on the entire key.

To understand this better, let us elaborate some of the important terms −

• Candidate Key − A minimal set of attributes that can uniquely identify each row.
• Prime Attributes − Attributes that are part of a candidate key.
• Non-Prime Attributes − Attributes that are not part of any candidate key.

What is Partial Dependency?

A partial dependency exists when a non-prime attribute is dependent on just a part of a composite candidate key. It creates redundancy and anomalies in the database.

Example of Partial Dependency

Let's consider the following table −

In this case,

• Candidate Key − The combination of Customer ID and Store ID uniquely identifies each row.
• Prime Attributes − Customer ID, Store ID
• Non-Prime Attribute − Location

If we look closely, the attribute "Location" is dependent only on "Store ID". For example, wherever Store ID is 1, the location is Delhi. It violates the second rule of 2NF because Location does not depend on the entire composite key (Customer ID, Store ID).

How to Convert a Table to 2NF?

Whenever a table violates 2NF, the solution is to split it into smaller tables. We call this process "Decomposition". Let us see how it works with the above example.

Step 1: Divide the Table

We break the table into two smaller tables −

• One table stores the composite key along with its prime attributes.
• Another table stores the partial dependency.

Step 2: Identify the Keys

• In the Customer Table, the candidate key remains Customer ID, Store ID.
• In the Store Table, the primary key is Store ID.

Now, the non-prime attribute Location depends fully on the candidate key of its table (Store ID). This is ensuring that both tables are in 2NF.

General Steps for Achieving 2NF

Given below are the general steps to make any table comply with 2NF −

• Identify the Candidate Key − Use functional dependencies to find all possible candidate keys.
• Classify the Attributes − Prime attributes are part of the candidate key. Non-prime attributes are everything else.
• Check for Partial Dependencies − If any non-prime attribute depends on only part of a composite candidate key. It is a partial dependency.
• Split the Table − Create separate tables to resolve partial dependencies.

Another Example: Functional Dependency

Let us explore a more complex example with functional dependencies.

Table and Functional Dependencies

We have a relation: {A, B, C, D, E, F} with these dependencies −

• C → F
• E → A
• E, C → D
• A → B

Step 1: Identify the Candidate Key

To find the candidate key, we must look at the right-hand side (RHS) of the functional dependencies: {F, A, D, B}. Attributes not on the RHS (E and C) must be part of the candidate key.

Start with E, C −

• E → A
• C → F
• E, C → D
• A → B

Thus, the closure of E, C covers all attributes: {E, C, A, F, D, B}. So, the candidate key is {E, C}.

Step 2: Prime and Non-Prime Attributes

• Prime Attributes − E, C.
• Non-Prime Attributes − A, B, D, F.

Step 3: Check for Partial Dependencies

A partial dependency occurs when −

• The left-hand side (LHS) of a functional dependency is a proper subset of the candidate key.
• The RHS is a non-prime attribute.

In our example,

• C → F: C is a proper subset of {E, C}, and F is non-prime. Partial dependency exists.
• E → A: E is a proper subset of {E, C}, and A is non-prime. Partial dependency exists.

Step 4: Split the Table

In this step, to eliminate partial dependencies, we divide the table into smaller tables −

The derived tables are given below –

Each table satisfies 2NF because all non-prime attributes fully depend on the candidate key of their respective tables.

Key Concepts in 2NF

Following are the key concepts in 2NF –

• Prime Attribute − Part of a candidate key.
• Non-Prime Attribute − Not part of any candidate key.
• Partial Dependency − A non-prime attribute depends on part of a composite candidate key.
• Full Functional Dependency − A non-prime attribute depends on the entire candidate key.

Conclusion

In this chapter, we explained in detail the concept of Second Normal Form (2NF) and how to address partial dependency in relational databases. We started with the basic rules of 2NF, highlighting the importance of achieving 1NF first.

Through detailed examples, we explored the concept of partial dependency and demonstrated how to split tables into smaller ones to remove redundancy. By ensuring 2NF, we make the databases more efficient by reducing the data redundancy and setting the stage for higher levels of normalization.