Explain 3NF? Give One Example?

Third Normal Form (3NF) is a level of database normalization that ensures that all data in a table is related only to the primary key of that table, and not to any other non-key fields. The goal of 3NF is to eliminate data redundancy and improve data integrity.

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

  1. The table must already be in 2NF.
  2. Non-key attributes must be functionally dependent on the primary key.

For example, consider a table called 'Orders' that contains the following fields: Order_ID, Customer_Name, Customer_Address, Product_Name, Product_Price, and Order_Date. In this table, the primary key is Order_ID.

At first glance, this table may appear to be normalized, but it violates 3NF because the fields Customer_Name and Customer_Address are not dependent on the primary key Order_ID. Instead, they are dependent on each other.

To normalize this table to 3NF, we need to split it into two separate tables. The first table would contain the Order_ID, Product_Name, Product_Price, and Order_Date fields. The second table would contain the Customer_Name, Customer_Address, and Order_ID fields. This would ensure that all fields in each table are dependent only on the primary key for that table.

By normalizing this table to 3NF, we can eliminate data redundancy and ensure that each piece of data is stored in only one place. This can improve the efficiency of the database, reduce storage space, and make it easier to maintain and update.