MySQL

Third Normal Form (3NF) is a database normalization step that builds upon the principles of First Normal Form (1NF) and Second Normal Form (2NF). A table is in Third Normal Form if:

• It is already in Second Normal Form (2NF).
• All non-key attributes are not only fully functionally dependent on the primary key but are also non-transitively dependent on it. This means that non-key attributes should not depend on other non-key attributes.

In simpler terms, 3NF ensures that all columns are directly dependent on the primary key and not on other columns.

Here’s a detailed explanation of Third Normal Form:

1. Understanding Transitive Dependency

   • Transitive dependency occurs when a non-key attribute depends on another non-key attribute, which in turn depends on the primary key.
   • To be in 3NF, there should be no transitive dependencies.

   -- Example of a table not in 3NF due to transitive dependency
   CREATE TABLE EmployeeDetails (
       EmployeeID INT PRIMARY KEY,
       DepartmentID INT,
       DepartmentName VARCHAR(50), -- Transitive dependency on DepartmentID
       EmployeeName VARCHAR(50),
       FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
   );

2. Removing Transitive Dependencies

   • To achieve 3NF, separate the table into additional tables where non-key attributes only depend on the primary key.

   -- Applying 3NF by removing transitive dependencies
   CREATE TABLE Employees (
       EmployeeID INT PRIMARY KEY,
       DepartmentID INT,
       EmployeeName VARCHAR(50),
       FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
   );
   
   CREATE TABLE Departments (
       DepartmentID INT PRIMARY KEY,
       DepartmentName VARCHAR(50)
   );

3. Ensuring Non-Transitive Dependencies

   • Make sure that all non-key attributes in a table are directly dependent on the primary key, and not on other non-key attributes.
   • This often involves splitting tables further to ensure that every non-key attribute depends solely on the primary key.

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

4. Benefits of 3NF

   • Eliminates transitive dependencies and redundant data.
   • Enhances data integrity by ensuring that all attributes depend only on the primary key.
   • Simplifies data updates and management by reducing data redundancy.

5. Example with Composite Key

   • Consider a table that records both employees and their department assignments. Initially, it might include redundant data because of transitive dependencies.

   -- Initial table with transitive dependencies
   CREATE TABLE EmployeeAssignments (
       EmployeeID INT,
       DepartmentID INT,
       DepartmentName VARCHAR(50), -- Transitive dependency on DepartmentID
       EmployeeName VARCHAR(50),
       PRIMARY KEY (EmployeeID, DepartmentID)
   );
   
   -- Applying 3NF by creating separate tables
   CREATE TABLE Employees (
       EmployeeID INT PRIMARY KEY,
       EmployeeName VARCHAR(50),
       DepartmentID INT,
       FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
   );
   
   CREATE TABLE Departments (
       DepartmentID INT PRIMARY KEY,
       DepartmentName VARCHAR(50)
   );
   
   CREATE TABLE EmployeeAssignments (
       EmployeeID INT,
       DepartmentID INT,
       FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID),
       FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID),
       PRIMARY KEY (EmployeeID, DepartmentID)
   );

Third Normal Form helps in organizing data to remove transitive dependencies, ensuring that every non-key attribute is directly related to the primary key. This leads to a more efficient and consistent database design by reducing redundancy and improving data integrity.

Third Normal Form (3NF)

Third Normal Form (3NF) is a crucial concept in database normalization that ensures data is structured in a way that reduces redundancy and avoids undesirable dependencies. To be in 3NF, a database table must first satisfy the requirements of the Second Normal Form (2NF), and then go a step further to eliminate transitive dependencies among non-key attributes. Let's dive into the details of 3NF with a clear example.

Understanding 3NF

For a table to be in 3NF:

• It must already be in 2NF. This means that the table is in 1NF (all attributes contain only atomic values and the table contains no repeating groups), and all non-key attributes are fully functionally dependent on the primary key (i.e., no partial dependencies for composite primary keys).
• It must have no transitive dependencies. A transitive dependency occurs when a non-key attribute depends on another non-key attribute. To meet the 3NF requirements, every non-key attribute must directly depend on the primary key.

Why 3NF?

3NF is designed to improve database efficiency and integrity by:

• Reducing redundancy:Ensuring that all information is stored only once, which makes the database easier to maintain.
• Preventing update anomalies:Eliminating transitive dependencies helps prevent inconsistencies that can arise when data is updated in one place but not another.

Suppose we have a table calledEmployee_Departmentwith the following attributes:

• Employee_ID (Primary Key)
• Employee_Name
• Department_ID
• Department_Name
• Manager_ID

To achieve 3NF, we would decompose this table into two separate tables:

Table 1:Employee_Details:

• Employee_ID (Primary Key)
• Employee_Name
• Department_ID (Foreign Key)

Table 2:Department_Details:

• Department_ID (Primary Key)
• Department_Name
• Manager_ID (Foreign Key)

By doing this, we eliminate the transitive dependency betweenDepartment_NameandManager_ID, ensuring that each table represents a single entity and all attributes are functionally dependent on the primary key.

THIRD NORMAL FORM EXAMPLE

• 3NF Violation: The original books inventory table violates Third Normal Form (3NF) due to a transitive dependency.
• Transitive Dependency: The author birth year attribute does not depend functionally on the primary key (book id), but rather on another non-primary key attribute (author).
• Solution for 3NF Compliance: To resolve this issue, transfer the author name and author birth year from the books table to a separate dedicated Authors table.