Today's Offer - SQL Server Certification Training - Enroll at Flat 20% Off.

- SQL Server Blogs -

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

Normalization: Removing Duplicates is called Normalization.

Benefits:

  • 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.

SQL Server Curriculum

To solve this, we normalize 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 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

Read: What is SQL Server? Microsoft SQL Server Tutorial Guide for Beginners
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

Read More: Different Type of SQL Joins

First Normal Form(1NF) A table is said to me 1NF, if

  • No Multiple values separated by comma.

Example:

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.

 

SQL Server Quiz

Example:

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.

Read: Different Types of SQL Server & SQL Database Functions
  • By using Primary key, each record should identify Uniquely.

Example: Table1:

DId DName
1 Finance
2 HR

Table2:

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

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

Read: What Does SQL Stand For? The Structured Query Language Explained

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.

SQL Server training

Fourth Normal Form(4NF)

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

Example: Course, Lecture, Books

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

 


    Janbask Training

    JanBask Training is a leading Global Online Training Provider through Live Sessions. The Live classes provide a blended approach of hands on experience along with theoretical knowledge which is driven by certified professionals.


Trending Courses

AWS

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

Upcoming Class

8 days 14 Dec 2019

DevOps

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

Upcoming Class

-1 day 05 Dec 2019

Data Science

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

Upcoming Class

-1 day 05 Dec 2019

Hadoop

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

Upcoming Class

0 day 06 Dec 2019

Salesforce

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

Upcoming Class

14 days 20 Dec 2019

Course for testing

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

Upcoming Class

18 days 24 Dec 2019

QA

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

Upcoming Class

3 days 09 Dec 2019

Business Analyst

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

Upcoming Class

0 day 06 Dec 2019

SQL Server

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

Upcoming Class

3 days 09 Dec 2019

Comments

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews