Oracle

Third Normal Form (3NF) is a crucial step in database normalization, designed to reduce redundancy and ensure that data dependencies are logical and consistent. A table is in 3NF if it is already in Second Normal Form (2NF) and if all the columns are dependent only on the primary key. In other words, a table is in 3NF if every non-key attribute is non-transitively dependent on the primary key, which means there are no transitive dependencies.

Here’s a step-by-step guide to understanding 3NF:

1. Ensure the Table is in 1NF and 2NF

   • First Normal Form (1NF): All columns contain atomic values, and each column contains only a single value.
   • Second Normal Form (2NF): The table is in 1NF and all non-key attributes are fully functionally dependent on the entire primary key.

2. Eliminate Transitive Dependencies

   • Identify Transitive Dependencies: A transitive dependency occurs when a non-key attribute depends on another non-key attribute.
   • Example: Consider a table Books with columns BookID, Title, AuthorID, and AuthorName. Here, AuthorName depends on AuthorID, which in turn depends on BookID. This creates a transitive dependency.

3. Decompose the Table

   • Create New Tables: To eliminate transitive dependencies, decompose the original table into multiple tables.
   • Example:
     • Original Table: Books(BookID, Title, AuthorID, AuthorName)
     • Decomposed Tables:
       • Books(BookID, Title, AuthorID)
       • Authors(AuthorID, AuthorName)

4. Maintain Referential Integrity

   • Set Foreign Key Relationships: Ensure that foreign keys correctly reference primary keys in related tables.
   • Example: In the Books table, AuthorID should be a foreign key that references AuthorID in the Authors table.

SQL Code Example

Here’s how you might normalize a table to 3NF:

-- Original Table with transitive dependency
CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(100),
    AuthorID INT,
    AuthorName VARCHAR(100)
);

-- Decomposed Tables to achieve 3NF
CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(100),
    AuthorID INT,
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);

CREATE TABLE Authors (
    AuthorID INT PRIMARY KEY,
    AuthorName VARCHAR(100)
);

This decomposition ensures that Books and Authors tables each focus on a single subject, avoiding redundancy and making the database more efficient and easier to maintain.

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.