DBMS Third Normal Form (3NF)

Third Normal Form (3NF) is a database normalization technique used in database management systems (DBMS) to eliminate data redundancy and improve data integrity. It builds upon the concepts of the first normal form (1NF) and the second normal form (2NF).

To understand 3NF, let’s review the previous normalization forms briefly:

  1. First Normal Form (1NF): It ensures that the database table has a primary key and that each column in the table contains only atomic values, meaning that it cannot be further divided.
  2. Second Normal Form (2NF): It addresses the concept of partial dependencies. It requires that each non-key column in a table depends on the entire primary key rather than only part of it. If a table has a composite primary key (multiple columns), each non-key column must be functionally dependent on the entire composite key.

Now, let’s move on to the third normal form (3NF):

  1. Third Normal Form (3NF): It deals with the concept of transitive dependencies. A transitive dependency occurs when a non-key column depends on another non-key column rather than directly depending on the primary key.

To achieve 3NF, a table must satisfy the following conditions:

a. It must already be in 2NF. b. All non-key columns must be functionally dependent on the primary key. c. There should be no transitive dependencies between non-key columns.

To eliminate transitive dependencies, we identify columns that depend on other non-key columns and create separate tables for them. The original table is left with only the key and the columns it is directly dependent on. The dependent columns are moved to a new table along with their dependencies, including a foreign key referencing the primary key of the original table.

By eliminating transitive dependencies, 3NF reduces data redundancy and ensures that changes in non-key columns are not propagated through the database, improving data integrity and making the database more flexible and efficient.

It’s important to note that normalization beyond 3NF, such as Boyce-Codd Normal Form (BCNF) and Fourth Normal Form (4NF), exists but may not be necessary for all database designs. The level of normalization depends on the specific requirements and complexities of the data model.

Example of DBMS Third Normal Form (3NF):

Sure! Let’s consider a hypothetical example to demonstrate the application of Third Normal Form (3NF) in a database.

Suppose we have a database for a library, and we want to store information about books, authors, and publishers. We start with a single table that contains all the data:

Table: Books

| Book ID | Book Title | Author | Publisher |

| 1 | Book A | Author X | Publisher 1 | | 2 | Book B | Author Y | Publisher 2 | | 3 | Book C | Author X | Publisher 3 | | 4 | Book D | Author Z | Publisher 1 |

In this initial form, the table is not in 3NF because it contains transitive dependencies. The author column depends on the book title, and the publisher column also depends on the book title.

To normalize this table into 3NF, we need to break it down into multiple tables, ensuring that each table represents a single entity and eliminates transitive dependencies.

Table: Books

| Book ID | Book Title |

| 1 | Book A | | 2 | Book B | | 3 | Book C | | 4 | Book D |

Table: Authors

| Author ID | Author Name |

| 1 | Author X | | 2 | Author Y | | 3 | Author Z |

Table: Publishers

| Publisher ID | Publisher Name |

| 1 | Publisher 1 | | 2 | Publisher 2 | | 3 | Publisher 3 |

In this normalized structure, we have separated the entities into individual tables, eliminating the transitive dependencies. Each table has a primary key (Book ID for the Books table, Author ID for the Authors table, and Publisher ID for the Publishers table) and stores only the relevant information for that entity.

To establish relationships between these tables, we can introduce foreign keys. The Books table can have foreign keys referencing the Author ID from the Authors table and the Publisher ID from the Publishers table.

Table: Books

| Book ID | Book Title | Author ID (FK) | Publisher ID (FK) |

| 1 | Book A | 1 | 1 | | 2 | Book B | 2 | 2 | | 3 | Book C | 1 | 3 | | 4 | Book D | 3 | 1 |

With this 3NF structure, we have eliminated data redundancy and ensured that each non-key column depends directly on the primary key of its respective table. The relationships between entities are established through foreign keys, allowing for efficient querying and maintaining data integrity.