Microsoft SQL Server

Third Normal Form (3NF) is a step in the database normalization process that builds upon Second Normal Form (2NF). A table is in 3NF if it is already in 2NF and all the non-key columns are not only fully dependent on the primary key but also independent of each other. In simpler terms, 3NF ensures that no non-key column is dependent on another non-key column. This prevents transitive dependencies, which occur when one non-key column depends on another non-key column instead of the primary key.

Key Concepts of Third Normal Form (3NF):

1. Remove Transitive Dependencies

   • In 3NF, there should be no transitive dependencies, where a non-key column is dependent on another non-key column.
   • If a column can be derived from another non-key column, it should be placed in a separate table.

   Example SQL (violating 3NF):

   CREATE TABLE Customers (
       CustomerID INT PRIMARY KEY,
       CustomerName VARCHAR(100),
       ContactNumber VARCHAR(15),
       StoreLocation VARCHAR(100),  -- StoreLocation depends on StoreID, not CustomerID
       StoreID INT
   );

2. Move Dependent Columns to Separate Tables

   • To remove the transitive dependency, move the dependent column to another table. For example, StoreLocation should be in a Stores table rather than the Customers table.

   Example SQL (corrected to 3NF):

   CREATE TABLE Customers (
       CustomerID INT PRIMARY KEY,
       CustomerName VARCHAR(100),
       ContactNumber VARCHAR(15),
       StoreID INT
   );
   
   CREATE TABLE Stores (
       StoreID INT PRIMARY KEY,
       StoreLocation VARCHAR(100)
   );

3. Ensure Full Dependency on the Primary Key

   • All non-key attributes in the table should depend only on the primary key. If any non-key attribute is dependent on another non-key attribute, it needs to be moved to a new table.

   Example SQL (adding foreign key to maintain relationship):

   CREATE TABLE Customers (
       CustomerID INT PRIMARY KEY,
       CustomerName VARCHAR(100),
       ContactNumber VARCHAR(15),
       StoreID INT,
       FOREIGN KEY (StoreID) REFERENCES Stores(StoreID)
   );

4. Normalization through Decomposition

   • In 3NF, you decompose your tables to remove transitive dependencies while maintaining relationships between the tables.
   • For instance, moving StoreLocation from the Customers table to a Stores table ensures that StoreLocation depends directly on StoreID, not CustomerID.

   Example SQL (decomposed tables):

   CREATE TABLE Products (
       ProductID INT PRIMARY KEY,
       ProductName VARCHAR(100),
       StoreID INT,
       FOREIGN KEY (StoreID) REFERENCES Stores(StoreID)
   );

5. Example of Transitive Dependency

   • Let’s say ProductPrice depends on ProductID, and ProductDiscount depends on ProductPrice. This is a transitive dependency, and we should move ProductDiscount to another table to remove it from the original table.

   Example SQL (removing transitive dependency):

   CREATE TABLE ProductPrices (
       ProductID INT PRIMARY KEY,
       ProductPrice DECIMAL(10, 2)
   );
   
   CREATE TABLE ProductDiscounts (
       ProductID INT PRIMARY KEY,
       Discount DECIMAL(5, 2),
       FOREIGN KEY (ProductID) REFERENCES ProductPrices(ProductID)
   );

By achieving Third Normal Form (3NF), you eliminate transitive dependencies, ensuring that every non-key column depends solely on the primary key. This improves the structure of the database, reduces redundancy, and makes maintenance easier.

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.