- SQL Server Blogs -

SQL Database Normalization: 1NF, 2NF, 3NF, 4NF


Data is stored in a huge quantity in the database. Retrieving and accessing specific data can be tedious if it is not arranged well. Here, the role of normalization in SQL comes to the picture. Normalization helps in organizing data properly and it reduces the redundant content too. With the help of this article, we give you a complete insight of Database Normalization in SQL Server specially 1NF 2NF 3NF with example.

Database Normalization and its Benefits

Normalization: Removing Duplicates is called Normalization. In this blog for Database normalization, we will discuss 1nf 2nf 3nf bcnf with example.


  • Store data efficiently
  • Reduce duplicates
  • Protect data integrity

Example: The above table is not normalized.

EName Gender Salary DName DHead DLocation
Chris Male 4500 Finance Lu Michigan
Mary Female 2000 HR Pete Florida
Den Male 1500 Finance Lu Michigan
Kate Female 3000 HR Pete Florida
Mathew Male 6500 Finance Lu Michigan

Problems with Duplicate data:

  • Wastage of disk space.
  • Inconsistent data.
  • DML(Insert/Update/Delete) queries can become slow.

To solve this, we normalize the tables. We have broken the above table into 2 tables, repeating columns we have moved into a separate table and we don’t have to repeat DName, DHead, DLocation in the Employees Table all we do here refer to Did. And we have given the primary key to Did to uniquely identify each department in the Department table.

Read More: Different Types of SQL Keys

Department Table

DId DName DHead DLocation
1 Finance LU Michigan
2 HR Pete Florida

 Employee Table

EID EName Gender Salary DId
1 Chris Male 4500 1
2 Mary Female 2000 2
3 Den Male 1500 1
4 Kate Female 3000 2
5 Mathew Male 6500 1

There are 6 normal forms: 1NF,2NF,3NF,4NF,5NF,6NF. Most databases are in 3NF. There are certain rules that each normal form follows. You can also download 1nf 2nf 3nf with example PDF from the web for more details.

Read More: Different Type of SQL Joins

Normalization in SQL - Example of 1NF 2NF 3NF BCNF

Register for a Demo class to practice Database normalization in SQL with examples.

Learn SQL Server in the Easiest Way

  • Learn from the videos
  • Learn anytime anywhere
  • Pocket-friendly mode of learning
  • Complimentary eBook available

In this section, we will discuss example of 1nf 2nf 3nf bcnf in detail

First Normal Form (1NF)

A table is said to me 1NF, if

  • No Multiple values separated by comma.


DName EName
Finance Chris, Mary, Den
HR Mathew

Because of multiple values in the above example Insert, Update, Delete is not possible for one Employee.

  • No repeating columns.


DName EName1 EName2 EName3
Finance Chris Den Kate
HR Mary    

Suppose HR department has only one employee, EName2 and EName3 columns have null values in that case, wastage of the disk space.

  • By using Primary key, each record should identify Uniquely.

Example: Table1:

DId DName
1 Finance
2 HR


DId EName
1 Chris
1 Den
1 Kate
2 Mary

Here, DId is the Primary key in the Table1 and DId is the Foreign key in the Table2, which satisfies 1NF.

Read More: Different Types of SQL Database Functions

Signup for SQL training classes online now to know more about Database Normalization.

SQL Server Training & Certification

  • No cost for a Demo Class
  • Industry Expert as your Trainer
  • Available as per your schedule
  • Customer Support Available

Second Normal Form (2NF)

  • Should meet all the requirements of 1NF.
  • Move unwanted data into separate table.
  • Create relationship between these tables by using Foreign key.

Department Table

DId DName DHead DLocation
1 Finance LU Michigan
2 HR Pete Florida

Employee Table

EID EName Gender Salary DId
1 Chris Male 4500 1
2 Mary Female 2000 2
3 Den Male 1500 1
4 Kate Female 3000 2
5 Mathew Male 6500 1

DId is Primary key in Department table and DId in Employee table is Foreign key (Added additional column to make sure the two tables have relationship).

Read More: Different Types of SQL Injection

Third Normal Form (3NF)

  • Should meet all the requirements of 1NF and 2NF.
  • Remove columns which are not fully dependent on Primary key.

 Example: Employee Table

EID EName Gender Salary Annual Salary DId
1 Chris Male 4500 54000 1
2 Mary Female 2000 24000 2
3 Den Male 1500 18000 1
4 Kate Female 3000 36000 2
5 Mathew Male 6500 78000 1

Annual Salary is not fully dependent on Primary key because Annual Salary Column depends on Salary column.

Fourth Normal Form(4NF)

  • Should meet all the requirements of 1NF, 2NF, 3NF
  • NO Multivalued dependencies, other than Candidate key

Example: Course, Lecture, Books

With this discussion, you have a depth idea of normalization 1nf 2nf 3nf example. Let us have a look at a quick comparison between normalization and denormalization in the next section.

Difference between Normalization and Denormalization

Normalization Denormalization
  Divides data into multiple tables so, data retrieval is slow     Data from multiple tables are divided into single table so, data retrieval is fast
  Reduce duplicate data     Create duplicate data
  Because of more tables, we use more joins     Less tables less joins

Final Words

Thus this brings us to the end of Normalization in SQL article. I hope now you have a clear idea about Normalization concepts.

Thus, it brings to the end of normalization in the SQl article. I hope you have a sound idea of 1NF 2NF 3NF with an example. If you wish to learn more about normalization in SQL with examples then check our SQL DBA certification course that comes with instructor-led LIVE classes and real-life project experiences. Our SQL training will help you to understand SQL concepts in-depth and help you to achieve mastery over the subject.

Read: SSRS Pie Chart - Having a Slice of the Pie

fbicons FaceBook twitterTwitter google+Google+ lingedinLinkedIn pinterest Pinterest emailEmail


    Janbask Training

    A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.

  • fb-15
  • twitter-15
  • linkedin-15


Trending Courses

AWS Course


  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53
AWS Course

Upcoming Class

10 days 17 Feb 2023

DevOps Course


  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing
DevOps Course

Upcoming Class

3 days 10 Feb 2023

Data Science Course

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning
Data Science Course

Upcoming Class

10 days 17 Feb 2023

Hadoop Course


  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation
Hadoop Course

Upcoming Class

10 days 17 Feb 2023

Salesforce Course


  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL
Salesforce Course

Upcoming Class

4 days 11 Feb 2023

QA Course


  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing
QA Course

Upcoming Class

7 days 14 Feb 2023

Business Analyst  Course

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum
Business Analyst  Course

Upcoming Class

-1 day 06 Feb 2023

MS SQL Server Course

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design
MS SQL Server Course

Upcoming Class

3 days 10 Feb 2023

Python Course


  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation
Python Course

Upcoming Class

10 days 17 Feb 2023

Artificial Intelligence  Course

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks
Artificial Intelligence  Course

Upcoming Class

4 days 11 Feb 2023

Machine Learning Course

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning
Machine Learning Course

Upcoming Class

17 days 24 Feb 2023

Tableau Course


  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop
Tableau Course

Upcoming Class

10 days 17 Feb 2023

Search Posts


Receive Latest Materials and Offers on SQL Server Course