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.

Posted by:

Connect with us

Techopedia on Linkedin
Techopedia on Linkedin
Techopedia on Twitter

Sign up for Techopedia's Free Newsletter!
Techwise Webinar Series
Understanding the Big Data Landscape
Understanding the Big Data Landscape
Big Data is everywhere, that's for sure. But the big question for today's savvy enterprise is: exactly where does Big Data fit into your organization?

Email Newsletter

Join 138,000+ IT pros on our weekly newsletter

Free 30 Day Trial: SolarWinds® Log & Event Manager
Free 30 Day Trial: SolarWinds® Log & Event Manager:
Use this powerful SIEM to tackle compliance audits, perform root cause analysis and manage all logs in one place.
Free Whitepaper – Bridging the IT Operations Application Owner Gap
Free Whitepaper – Bridging the IT Operations Application Owner Gap:
Conflicting organizational needs all too often create tension between functional silos. Application owners demand more and more resources.
Free 30 Day Trial – VMTurbo Operations Manager
Free 30 Day Trial – VMTurbo Operations Manager:
VMTurbo's flagship product, Operations Manager maintains your virtual and cloud environments in a healthy state. It manages homogeneous and...