Introduction of 3NF

(continuation of… A Beginner’s Attempt at Understanding Data Normalization)

Valerie Poh Yun Ting
Nov 5 · 3 min read
Photo by Chor Tsang on Unsplash

In my previous post, titles “A Beginner’s Attempt at Understanding Data Normalization”, I attempted to do up a simple explanation of what 1NF and 2NF normalization entails.

This time, I’m going to ‘level up’ and seek to introduce 3NF normalization.

Firstly, I figured it’ll be helpful to learn about the good that will come out of normalizing your database to 3NF;

Benefits of 3NF Normalization?

  • Ensures no data duplication

And before we delve back into data normalization, I also think it would be best to equip or remind ourselves with the understanding of the definition of certain terms commonly used in normalization;

Key Column VS Non-Key Column…?

  • Key column is a column of values in the table that is used to identify a record uniquely.

Transitive functional dependencies…?

  • A transitive functional dependency is when changes to a non-key column, could possibly change any other non-key column too.

NOW, since the foundations are made clear, let’s dive into what 3NF is really all about!

Rules of 3NF Normalization:

  • Must be in 1nf (duh.)

I’m going to be using an example to illustrate how exactly to go about normalizing a database to 3NF.

Context: this is a namelist of participants attending a Freshmen Orientation Camp.

Figure 1: Database in 2NF.

With ‘Participant ID’ being the primary key, the relation flows this way;

‘Participant ID’ → ‘Name’ → ‘Orientation Group’ → ‘House’

In this database (Figure 1), ‘House’ is transitively dependent on ‘Orientation Group’, which breaks the last rule of 3NF normalization. Therefore, to transform this database of values to be in 3NF, we decompose this table to form 2 tables.

Figure 2a

The key of ‘House’ is now a foreign key, that references the primary key of Figure 2b; ‘House ID’.

Figure 2b

And this finished up what 3NF data normalization could look like for a database like this. The fascinating thing about data normalization is that with each level of normalization, there is a reduction in efficiency, and also storage space required. The more you decompose the database, the more benefits one reaps. I’m stopping at 3NF for now, but normalization can go on to Boyce-Codd Normal Form (also known as 3.5NF), 4NF, 5NF…

Even 6NF, which has not been standardised yet, from what I know of.

Understanding how one can go about normalizing their database, I believe it’s a skill everyone should master. It brings much value in terms of efficiency, and utilization of resources.

Analytics Vidhya

Analytics Vidhya is a community of Analytics and Data Science professionals. We are building the next-gen data science ecosystem https://www.analyticsvidhya.com

Valerie Poh Yun Ting

Written by

Analytics Vidhya

Analytics Vidhya is a community of Analytics and Data Science professionals. We are building the next-gen data science ecosystem https://www.analyticsvidhya.com

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade