Third Normal Form (3NF) is an essential concept in database normalization that helps to minimize redundancy and avoid undesirable characteristics like update anomalies. Achieving 3NF involves organizing a database schema to ensure that every non-key attribute is fully functionally dependent on the primary key and is not transitively dependent on other non-key attributes. This means that all fields in a table must be directly related to the primary key and not to other non-key fields.

Here’s how you can design your PostgreSQL tables to achieve 3NF, with examples related to banking systems including customers, accounts, and transactions:

1. Identify the Tables and Attributes
   • Define your tables based on the entities in your system.
   • For a banking system, the key entities might be Customers, Accounts, and Transactions.
2. Design the Tables in 1NF
   • Ensure that each table has a primary key and that each column contains atomic (indivisible) values.
3. Ensure 2NF
   • Make sure that every non-key attribute is fully dependent on the primary key, not just part of it.
4. Achieve 3NF
   • Remove transitive dependencies where non-key attributes depend on other non-key attributes.

Example: Banking System Schema in 3NF

1. Create the Customers Table

   CREATE TABLE Customers (
   
       CustomerID SERIAL PRIMARY KEY,
   
       FirstName VARCHAR(50) NOT NULL,
   
       LastName VARCHAR(50) NOT NULL,
   
       Email VARCHAR(100) UNIQUE,
   
       PhoneNumber VARCHAR(15)
   
   );

2. Create the Accounts Table

   CREATE TABLE Accounts (
   
       AccountID SERIAL PRIMARY KEY,
   
       AccountNumber VARCHAR(20) UNIQUE NOT NULL,
   
       CustomerID INT REFERENCES Customers(CustomerID),
   
       AccountType VARCHAR(20) NOT NULL,
   
       Balance DECIMAL(15, 2) NOT NULL
   
   );

3. Create the Transactions Table

   CREATE TABLE Transactions (
   
       TransactionID SERIAL PRIMARY KEY,
   
       AccountID INT REFERENCES Accounts(AccountID),
   
       TransactionDate TIMESTAMP NOT NULL,
   
       Amount DECIMAL(15, 2) NOT NULL,
   
       TransactionType VARCHAR(20) NOT NULL
   
   );

4. Normalization Steps Explained
   • First Normal Form (1NF):
     • Each table has a primary key.
     • All columns contain atomic values.
   • Second Normal Form (2NF):
     • All non-key attributes are fully dependent on the primary key.
     • For example, in the Accounts table, AccountType and Balance depend on AccountID, not just a part of it.
   • Third Normal Form (3NF):
     • There are no transitive dependencies.
     • In the Customers table, FirstName and LastName depend directly on CustomerID, and not on each other.
     • In the Accounts table, AccountType and Balance depend only on AccountID.

By following these steps and examples, you can structure your PostgreSQL database to achieve Third Normal Form and ensure efficient data management and 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 called Employee_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.