Second Normal Form (2NF) is a stage of database normalization designed to reduce redundancy and improve data integrity. For a table to be in 2NF, it must first meet all the criteria for the First Normal Form (1NF). Once that’s achieved, the table must ensure that all non-key attributes are fully functionally dependent on the entire primary key, not just part of it. This means that if the table's primary key is a composite key (a key composed of more than one column), all non-key columns must depend on the entire composite key, rather than just a part of it.

Here's a step-by-step guide to understanding and applying 2NF with a banking example.

1. Define the Problem:
   • We have a table that includes information about banking transactions, where each transaction is associated with a customer and an account. The primary key of this table is a composite key consisting of TransactionID and AccountID.
2. Initial Table Setup:
   • Suppose we have the following table which is not in 2NF:

   CREATE TABLE Transactions (
       TransactionID SERIAL,
       AccountID INT,
       CustomerName VARCHAR(100),
       TransactionDate DATE,
       Amount DECIMAL(10, 2),
       PRIMARY KEY (TransactionID, AccountID)
   );
   

   • In this table, CustomerName depends only on AccountID, not on the whole composite key (TransactionID and AccountID), which violates 2NF.
3. Normalization Steps to Achieve 2NF:
   • Step 1: Identify Partial Dependencies
     • CustomerName is dependent on AccountID alone, not the whole composite key.
   • Step 2: Create Separate Tables
     • Split the original table into two: one for transactions and another for accounts and customers.
   • Step 3: Define New Tables

     -- Table for transactions
     CREATE TABLE Transactions (
         TransactionID SERIAL,
         AccountID INT,
         TransactionDate DATE,
         Amount DECIMAL(10, 2),
         PRIMARY KEY (TransactionID, AccountID),
         FOREIGN KEY (AccountID) REFERENCES Accounts(AccountID)
     );
     
     -- Table for accounts
     CREATE TABLE Accounts (
         AccountID INT PRIMARY KEY,
         CustomerName VARCHAR(100)
     );
     

   • Step 4: Insert Sample Data

     -- Insert sample data into Accounts table
     INSERT INTO Accounts (AccountID, CustomerName) VALUES
     (1, 'John Doe'),
     (2, 'Jane Smith');
     
     -- Insert sample data into Transactions table
     INSERT INTO Transactions (TransactionID, AccountID, TransactionDate, Amount) VALUES
     (1001, 1, '2024-09-17', 150.00),
     (1002, 2, '2024-09-18', 200.00);
     

4. Verify 2NF Compliance:
   • In this new schema, Transactions table no longer contains any attributes that are partially dependent on a composite key. All non-key attributes in Transactions depend on the entire primary key, while CustomerName now resides in the Accounts table, which makes it independent of the composite key of Transactions.

By organizing your tables in this manner, you ensure that each table adheres to 2NF, thereby minimizing redundancy and enhancing data integrity in your database design.

Understanding the Second Normal Form (2NF)

The Second Normal Form (2NF) is a step in database normalization that aims to organize data to reduce redundancy and improve data integrity. A table is in 2NF if it satisfies two conditions:

1. It is in First Normal Form (1NF), meaning all its values are atomic and each row is unique.
2. It has no partial dependency; non-primary key columns are fully functionally dependent on the primary key.

Bookstore Database Example

Let's consider a bookstore database with a table named "Book_Info" having columns:

• Book_ID (Primary Key)
• Author
• Author_Location

In this scenario, Author_Location is dependent only on the Author, not the entire primary key (Book_ID), because an Author can have multiple books and may be located in the same place for each book.

To normalize this table to 2nd Normal Form (2NF), we would split it into two tables:

1. "Author_Info" with columns:
   • Author (Primary Key)
   • Author_Location
2. "Book_Author" with columns:
   • Book_ID (Primary Key)
   • Author (Foreign Key)

Now, each table has attributes fully functionally dependent on their respective primary keys, meeting the requirements of 2NF.

SECOND NORMAL FORM EXAMPLE

• Composite Primary Key: In the initial table representing product stock (which violates 2NF), there exists a composite primary key comprising product, supplier, and unit weight.
• Non-Primary Key Attributes: The attributes stock and supplier country are non-primary key attributes.
• Partial Primary Key Dependency: In real-world scenarios, supplier country is contingent solely on the supplier, constituting a partial primary key (it is not reliant on the entirety of product + supplier + unit weight).
• Solution for 2NF Violation: To address the 2NF violation, relocate supplier country from the product stock table to its dedicated supplier table.