MySQL

Second Normal Form (2NF) builds on the principles of First Normal Form (1NF) by addressing partial dependency, which occurs when a non-key attribute is dependent on only a part of a composite primary key. A table is in Second Normal Form if:

• It is already in First Normal Form (1NF).
• All non-key attributes are fully functionally dependent on the entire primary key, not just part of it.

Here’s a more detailed look at Second Normal Form:

1. Understanding Partial Dependency

   • Partial dependency occurs in tables with a composite primary key when a non-key attribute depends on only one part of the composite key.
   • To achieve 2NF, you need to ensure that non-key attributes depend on the whole primary key.

   -- Example of a table not in 2NF due to partial dependency
   CREATE TABLE EmployeeProjects (
       EmployeeID INT,
       ProjectID INT,
       EmployeeName VARCHAR(50), -- Partial dependency on EmployeeID
       ProjectName VARCHAR(50), -- Partial dependency on ProjectID
       PRIMARY KEY (EmployeeID, ProjectID)
   );

2. Removing Partial Dependencies

   • To normalize to 2NF, separate the table into multiple tables where non-key attributes depend on the entire primary key.

   -- Applying 2NF by splitting the table
   CREATE TABLE Employees (
       EmployeeID INT PRIMARY KEY,
       EmployeeName VARCHAR(50)
   );
   
   CREATE TABLE Projects (
       ProjectID INT PRIMARY KEY,
       ProjectName VARCHAR(50)
   );
   
   CREATE TABLE EmployeeProjects (
       EmployeeID INT,
       ProjectID INT,
       FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID),
       FOREIGN KEY (ProjectID) REFERENCES Projects(ProjectID),
       PRIMARY KEY (EmployeeID, ProjectID)
   );

3. Ensuring Full Functional Dependency

   • Verify that all non-key attributes in a table are fully functionally dependent on the entire primary key.
   • This means that the value of a non-key attribute must be determined by the whole primary key, not just a part of it.

   -- Example with no partial dependencies
   CREATE TABLE EmployeeDetails (
       EmployeeID INT PRIMARY KEY,
       DepartmentID INT,
       EmployeeName VARCHAR(50),
       DepartmentName VARCHAR(50),
       FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
   );

4. Benefits of 2NF

   • Eliminates redundancy and anomalies related to partial dependencies.
   • Enhances data integrity by ensuring that all non-key attributes are appropriately related to the primary key.
   • Simplifies data management and updates by breaking down tables into smaller, more focused tables.

5. Example with Composite Key

   • Consider a table storing employee assignments to projects. If the table includes information about both employees and projects, it might have a composite key consisting of EmployeeID and ProjectID.

   -- Initial table with composite key but not in 2NF
   CREATE TABLE EmployeeAssignments (
       EmployeeID INT,
       ProjectID INT,
       EmployeeName VARCHAR(50), -- Partial dependency on EmployeeID
       ProjectName VARCHAR(50), -- Partial dependency on ProjectID
       PRIMARY KEY (EmployeeID, ProjectID)
   );
   
   -- Applying 2NF by creating separate tables
   CREATE TABLE Employees (
       EmployeeID INT PRIMARY KEY,
       EmployeeName VARCHAR(50)
   );
   
   CREATE TABLE Projects (
       ProjectID INT PRIMARY KEY,
       ProjectName VARCHAR(50)
   );
   
   CREATE TABLE EmployeeAssignments (
       EmployeeID INT,
       ProjectID INT,
       FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID),
       FOREIGN KEY (ProjectID) REFERENCES Projects(ProjectID),
       PRIMARY KEY (EmployeeID, ProjectID)
   );

Second Normal Form is essential for organizing data to eliminate partial dependencies, leading to a more structured and efficient database design. It helps ensure that all non-key attributes are properly related to the entire primary key, thereby enhancing data integrity and reducing redundancy.

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.