Copyright

What is Normal Form in DBMS? - Types & Examples

Lesson Transcript
Instructor: Temitayo Odugbesan

Temitayo has 11+ years Industrial Experience in Information Technology and has a master's degree in Computer Science.

Database management systems (DBMS) rely on table structures to accurately store and organize data. Explore how rules are developed to design tables that protect the integrity of data and learn about the types and stages of normal forms using examples. Updated: 03/14/2022

Database Management System Design

As you probably know, a database is often a collection of information or data organized and stored in tables with efforts made to ensure its accuracy and ease of retrieval. During the design process on a database management system, or DBMS, it's of utmost importance that the arrangement of tables and their relation to each other is correct. How data is added, edited, and deleted is also taken into consideration. The table structures, consisting of columns and rows, are important as well.

An error occurred trying to load this video.

Try refreshing the page, or contact customer support.

Coming up next: First Normal Form in DBMS with Examples

You're on a roll. Keep up the good work!

Take Quiz Watch Next Lesson
 Replay
Your next lesson will play in 10 seconds
  • 0:03 Database Management…
  • 0:36 Why Do We Go to This Length?
  • 1:46 What Is Normalization?
  • 2:15 The Normal Forms
  • 2:49 Types of Normal Forms
  • 5:47 Lesson Summary
Save Save Save

Want to watch this again later?

Log in or sign up to add this lesson to a Custom Course.

Log in or Sign up

Timeline
Autoplay
Autoplay
Speed Speed

Why Do We Go to This Length?

Let's look at an example to see why all this is so important. John Tailor (who's last name is spelled with an i not a y) finally gets to go on his dream holiday to Las Vegas. John lodges at a fabulous hotel. Tired from the long haul flight, he woke up late in the middle of the night to discover his room formed a part of the passage to the hotel spa.

The next day, coming in from shopping, he walked to his room and was greeted at the door by a new guest John Taylor (whose last name is spelled with a 'y' rather than an 'i'). His dream holiday is turning into a nightmare and, frustrated, he checks out. So, the question is, how did this mix-up come to be? It looks like hotel design flaws, service disorganization, and data errors are at play.

This isn't different when it comes to DBMS design. As the old computer adage goes: garbage in, garbage out. Poor table relations and erroneous data result in database anomalies, and this impacts information quality. So how do we fix this? We normalize the data.

What Is Normalization?

Normalization is the process of removing existing or possible incidences of anomalies, data redundancies, and data inaccuracy in a database. This limits tables to a particular purpose or entity. In the hotel, for example, a room is a private area exclusive to only one registered guest(s) at a time and shouldn't double in purpose as a passage to the spa or be accessible by more than one spa client.

The Normal Forms

Certain rules in database management system design have been developed to better organize tables and minimize anomalies. The stage at which a table is organized is known as its normal form (or a stage of normalization). There are three stages of normal forms are known as first normal form (or 1NF), second normal form (or 2NF), and third normal form (or 3NF). As a table progressively satisfies the conditions of the different normal forms, it's less prone to the anomalies discussed earlier.

Types of Normal Forms

Let's now talk about each normal form stage in turn:

First Normal Form (or 1NF)

A table is said to be in 1NF if the following rules hold:

  1. Columns must have single values
  2. Columns must have unique names
  3. Values of a given attribute must be of the same data type
  4. No two records (or rows) can be identical

Let's assume you're the new hairdresser in town. You want to store the names and contact details of clients in a database. Your trainee staff creates the following table:

ClientID ClientName ClientNo
015 John 256-2568, 524-4589
016 Princess 487-5485,451-copy
027 Tom 458-4587
028 Claire 478-2689, 265-1486
029 Robert 485-5584, 254-visa

Look closely, and you'll see that the table does not conform to the rules of 1NF. The reason for this is that the trainee staff saved the second mobile numbers of John, Princess, and Robert within the same field which violates rule 1. And, the second entries for Princess and Robert are stored in wrong data format. All the other numbers are saved in number format while their second numbers are stored as strings with numbers and letters, which violates rule 3.

Thus, the table should look like this:

ClientID ClientName ClientNo
015 John 256-2568
015 John 524-4589
016 Princess 487-5485
016 Princess 451-2679
027 Tom 458-4587
028 Claire 478-2689
028 Claire 265-1486
029 Robert 485-5584
029 Robert 254-8472

Second Normal Form (or 2NF)

A table is said to be in 2NF if the following rules hold:

  1. Table is in 1NF
  2. There should be no partial dependencies of any column on the primary key

Now, let's assume a security firm stores client data according to the category of items in safe custody. Clients can bring in multiple items of different classifications for safe keeping.

As such, the following table is created:

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it now
Create an account to start this course today
Used by over 30 million students worldwide
Create an account