Microsoft SQL Server

Second Normal Form (2NF) is a step in the database normalization process that builds upon First Normal Form (1NF). A table is in 2NF if it is already in 1NF and all non-key columns are fully dependent on the entire primary key. This means that there should be no partial dependency, where a non-key column is dependent on only part of a composite primary key. If there is a composite key, every non-key attribute must depend on all parts of the key, not just a portion of it.

Key Concepts of Second Normal Form (2NF):

1. Remove Partial Dependencies

   • In a table with a composite primary key (where the primary key consists of more than one column), all non-key columns must depend on the entire primary key, not just part of it.
   • If a non-key column depends on only one part of the composite key, it violates 2NF.

   Example SQL (violating 2NF):

   CREATE TABLE OrderDetails (
       OrderID INT,
       ProductID INT,
       StoreName VARCHAR(100),  -- Dependent only on ProductID
       PRIMARY KEY (OrderID, ProductID)
   );

2. Break Down the Table

   • To achieve 2NF, we break the table into smaller tables where each non-key attribute depends entirely on the primary key. In the example above, StoreName should be moved to another table because it only depends on ProductID, not OrderID.

   Example SQL (corrected to 2NF):

   CREATE TABLE OrderDetails (
       OrderID INT,
       ProductID INT,
       PRIMARY KEY (OrderID, ProductID)
   );
   
   CREATE TABLE Products (
       ProductID INT PRIMARY KEY,
       ProductName VARCHAR(100),
       StoreName VARCHAR(100)
   );

3. Full Functional Dependency

   • Every non-key column must be fully dependent on the entire primary key, not just part of it. If you have a single primary key (not composite), the table is automatically in 2NF if it’s in 1NF.

   Example SQL (achieving full functional dependency):

   CREATE TABLE Sales (
       SaleID INT PRIMARY KEY,
       CustomerID INT,
       ProductID INT,
       SaleAmount DECIMAL(10, 2)
   );

4. Decomposition

   • When partial dependencies are found, the table is decomposed into two or more related tables to ensure that every column is fully functionally dependent on the primary key.

   Example SQL (decomposition to remove partial dependencies):

   -- Orders table contains information specific to orders
   CREATE TABLE Orders (
       OrderID INT PRIMARY KEY,
       CustomerID INT,
       OrderDate DATE
   );
   
   -- Products table contains product-specific details
   CREATE TABLE Products (
       ProductID INT PRIMARY KEY,
       ProductName VARCHAR(100),
       StoreName VARCHAR(100)
   );
   
   -- OrderDetails table links orders to products
   CREATE TABLE OrderDetails (
       OrderID INT,
       ProductID INT,
       Quantity INT,
       PRIMARY KEY (OrderID, ProductID)
   );

5. Foreign Keys to Maintain Relationships

   • After breaking down the table into multiple tables, foreign keys are used to link the tables together, preserving the relationships between the data.

   Example SQL (adding foreign keys to link tables):

   CREATE TABLE OrderDetails (
       OrderID INT,
       ProductID INT,
       Quantity INT,
       PRIMARY KEY (OrderID, ProductID),
       FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
       FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
   );

By achieving Second Normal Form (2NF), you reduce redundancy and ensure that each non-key column is fully dependent on the primary key, improving the efficiency and integrity of 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.