Third Normal Form (3NF)

Definition - What does Third Normal Form (3NF) mean?

Third normal form (3NF) is the third step in normalizing a database and it builds on the first and second normal forms, 1NF and 2NF.

3NF states that all column reference in referenced data that are not dependent on the primary key should be removed. Another way of putting this is that only foreign key columns should be used to reference another table, and no other columns from the parent table should exist in the referenced table.

Techopedia explains Third Normal Form (3NF)

Consider a bank’s database, which contains two tables: CUSTOMER_MASTER for storing customer details, and ACCOUNT_MASTER for storing details about bank accounts, including which customer holds which account. In this case, there needs to be a way to link the two tables in order to tie an account to the customer who owns it. The way to do this is via a foreign key. This is a column in the ACCOUNT_MASTER table that points to or references a corresponding column (called the primary key) in the CUSTOMER_MASTER parent table. Let’s call this column CustID.

Suppose that customer Andrew Smith creates an account in the CUSTOMER_MASTER table with CustID 20454. Mr. Smith holds a savings account with the number S-200802-005, whose details are stored in the ACCOUNT_MASTER table. This means that the ACCOUNT_MASTER table will have a column called CustID, which is not an original piece of data. Instead, it also has the value 20454, which simply references the same CustID in the CUSTOMER_MASTER table.

Now, 3NF dictates that in our ACCOUNT_MASTER table, the only information we hold about the customer should be the CustID (20454) as a foreign key, and it refers to and identifies the customer who owns this same CustID in the CUSTOMER_MASTER table (Andrew Smith). No other data about our customer (such as name, date of birth, gender and so on) should be stored in the ACCOUNT_MASTER table, or indeed any other table, because all this data about him is already stored in CUSTOMER_MASTER. By doing this, the only customer data stored outside the CUSTOMER_MASTER table is the CustID. This pays handsome dividends by ensuring there is no data duplication, which in turn makes queries run much more efficiently and reduces the amount of storage required.

Share this:

Connect with us

Email Newsletter

Join thousands of others with our weekly newsletter

The 4th Era of IT Infrastructure: Superconverged Systems
The 4th Era of IT Infrastructure: Superconverged Systems:
Learn the benefits and limitations of the 3 generations of IT infrastructure – siloed, converged and hyperconverged – and discover how the 4th...
Approaches and Benefits of Network Virtualization
Approaches and Benefits of Network Virtualization:
Businesses today aspire to achieve a software-defined datacenter (SDDC) to enhance business agility and reduce operational complexity. However, the...
Free E-Book: Public Cloud Guide
Free E-Book: Public Cloud Guide:
This white paper is for leaders of Operations, Engineering, or Infrastructure teams who are creating or executing an IT roadmap.
Free Tool: Virtual Health Monitor
Free Tool: Virtual Health Monitor:
Virtual Health Monitor is a free virtualization monitoring and reporting tool for VMware, Hyper-V, RHEV, and XenServer environments.
Free 30 Day Trial – Turbonomic
Free 30 Day Trial – Turbonomic:
Turbonomic delivers an autonomic platform where virtual and cloud environments self-manage in real-time to assure application performance.