Oracle

Second Normal Form (2NF) is a database normalization rule designed to reduce redundancy and improve data integrity by ensuring that all non-key attributes are fully functionally dependent on the primary key. It builds on the first normal form (1NF), which requires that the table only has atomic (indivisible) values and that each column contains only one type of data.

To achieve 2NF, a table must meet the following criteria:

1. Be in First Normal Form (1NF):

   • The table should have a primary key.
   • Each column must contain atomic, indivisible values.
   • Each record must be unique.

2. Remove Partial Dependencies:

   • Ensure that all non-key attributes are fully dependent on the entire primary key.
   • If a table's primary key is a composite key (i.e., it consists of more than one column), then all non-key attributes should be dependent on the entire combination of columns that form the primary key.

Example

Consider a database for a library with the following table, BookRentals, which tracks books rented by members:

CREATE TABLE BookRentals (
    RentalID NUMBER PRIMARY KEY,
    BookID NUMBER,
    MemberID NUMBER,
    BookTitle VARCHAR2(100),
    MemberName VARCHAR2(100)
);

In this table:

• RentalID is the primary key.
• BookID and MemberID are identifiers for books and members, respectively.
• BookTitle and MemberName are non-key attributes.

Problems in 1NF:

• The table is in 1NF as all attributes are atomic and the primary key is defined.

Problems in 2NF:

• BookTitle is dependent on BookID.
• MemberName is dependent on MemberID.

This table is not in 2NF because BookTitle depends only on BookID, not on the entire primary key (RentalID). Similarly, MemberName depends only on MemberID.

Steps to Achieve 2NF:

1. Separate the Table into Related Tables:

   • Create a new table for book details and another for member details.

2. Create New Tables:

   • Books Table:

     CREATE TABLE Books (
         BookID NUMBER PRIMARY KEY,
         BookTitle VARCHAR2(100)
     );
     

   • Members Table:

     CREATE TABLE Members (
         MemberID NUMBER PRIMARY KEY,
         MemberName VARCHAR2(100)
     );
     

   • BookRentals Table:

     CREATE TABLE BookRentals (
         RentalID NUMBER PRIMARY KEY,
         BookID NUMBER,
         MemberID NUMBER,
         FOREIGN KEY (BookID) REFERENCES Books(BookID),
         FOREIGN KEY (MemberID) REFERENCES Members(MemberID)
     );
     

3. Data Integrity and Redundancy Reduction:

   • Books table handles book-related details.
   • Members table handles member-related details.
   • BookRentals table now only includes foreign keys and the rental identifier, reducing redundancy and ensuring that BookTitle and MemberName are stored only once.

By restructuring the tables this way, you ensure that non-key attributes are fully functionally dependent on the primary key, meeting the requirements of 2NF.

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 Dependency Issue: In real-world scenarios, supplier country depends solely on the supplier (constituting a partial primary key dependency), rather than the full composite key (product + supplier + unit weight).
• Solution for 2NF Compliance: To resolve the 2NF violation, relocate supplier country from the product stock table to its dedicated supplier table.