Interview Prep
Career
GenAI
Prompt Engg
ChatGPT
LLM
Langchain
RAG
AI Agents
Machine Learning
Deep Learning
GenAI Tools
LLMOps
Python
NLP
SQL
AIML Projects

Reading list

Machine Learning Basics for a Newbie
6 Steps of Machine learning Lifecycle Introduction to Predictive Modeling
Introduction to Exploratory Data Analysis & Data Insights Descriptive Statistics Inferential Statistics How to Understand Population Distributions?
Reading Data Files into Python Different Variable Datatypes
Probability for Data Science Basic Concepts of Probability Axioms of Probability Conditional Probability
Central Tendencies for Continuous Variables Spread of Data KDE plots for Continuous Variable Overview of Distribution for Continuous variables Normal Distribution Skewed Distribution Skeweness and Kurtosis Distribution for Continuous Variable
Central Tendencies for Categorical Variables Understanding Discrete Distributions Performing EDA on Categorical Variables
Dealing with Missing Values Understanding Outliers Identifying Outliers in Data Outlier Detection in Python Outliers Detection Using IQR, Z-score, LOF and DBSCAN
Sample and Population Central Limit Theorem Confidence Interval and Margin of Error
Bivariate Analysis Introduction
Covariance Pearson Correlation Spearman's Correlation & Kendall's Tau Correlation versus Causation Tabular and Graphical methods for Bivariate Analysis Performing Bivariate Analysis on Continuous-Continuous Variables
Tabular and Graphical methods for Continuous-Categorical Variables Introduction to Hypothesis Testing P-value Two sample Z-test T-test T-test vs Z-test Performing Bivariate Analysis on Continuous-Catagorical variables
Chi-Squares Test Bivariate Analysis on Categorical Categorical Variables
Multivariate Analysis A Comprehensive Guide to Data Exploration The Data Science behind IPL
Supervised Learning vs Unsupervised Learning Reinforcement Learning Generative and Descriminative Models Parametric and Non Parametric model
Machine Learning Pipeline Preparing Dataset Build a Benchmark Model: Regression Build a Benchmark Model: Classification
Evaluation Metrics for Machine Learning Everyone should know Confusion Matrix Accuracy Precision and Recall AUC-ROC Log Loss R2 and Adjusted R2
Dealing with Missing Values Replacing Missing Values Imputing Missing Values in Data Working with Categorical Variables Working with Outliers Preprocessing Data for Model Building
Understanding Cost Function Understanding Gradient Descent Math Behind Gradient Descent Assumptions of Linear Regression Implement Linear Regression from Scratch Train Linear Regression in Python Implementing Linear Regression in R Diagnosing Residual Plots in Linear Regression Models Generalized Linear Models Introduction to Logistic Regression Odds Ratio Implementing Logistic Regression from Scratch Introduction to Scikit-learn in Python Train Logistic Regression in python Multiclass using Logistic Regression How to use Multinomial and Ordinal Logistic Regression in R ? Challenges with Linear Regression Introduction to Regularisation Implementing Regularisation Ridge Regression Lasso Regression
Introduction to K Nearest Neighbours Determining the Right Value of K in KNN Implement KNN from Scratch Implement KNN in Python
Bias Variance Tradeoff Introduction to Overfitting and Underfitting Visualizing Overfitting and Underfitting Selecting the Right Model What is Validation? Hold-Out Validation Understanding K Fold Cross Validation
Introduction to Feature Selection Feature Selection Algorithms Missing Value Ratio Low Variance Filter High Correlation Filter Backward Feature Elimination Forward Feature Selection Implement Feature Selection in Python Implement Feature Selection in R
Introduction to Decision Tree Purity in Decision Tree Terminologies Related to Decision Tree How to Select Best Split Point in Decision Tree? Chi-Squares Information Gain Reduction in Variance Optimizing Performance of Decision Tree Train Decision Tree using Scikit Learn Pruning of Decision Trees
Introduction to Feature Engineering Feature Transformation Feature Scaling Feature Engineering Frequency Encoding Automated Feature Engineering: Feature Tools
Introduction to Naive Bayes Conditional Probability and Bayes Theorem Introduction to Bayesian Adjustment Rating: The Incredible Concept Behind Online Ratings! Working of Naive Bayes Math behind Naive Bayes Types of Naive Bayes Implementation of Naive Bayes
Understanding how to solve Multiclass and Multilabled Classification Problem Evaluation Metrics: Multi Class Classification
Introduction to Ensemble Techniques Basic Ensemble Techniques Implementing Basic Ensemble Techniques Finding Optimal Weights of Ensemble Learner using Neural Network Why Ensemble Models Work well?
Introduction to Stacking Implementing Stacking Variants of Stacking Implementing Variants of Stacking Introduction to Blending Bootstrap Sampling Introduction to Random Sampling Hyper-parameters of Random Forest Implementing Random Forest Out-of-Bag (OOB) Score in the Random Forest IPL Team Win Prediction Project Using Machine Learning Introduction to Boosting Gradient Boosting Algorithm Math behind GBM Implementing GBM in python Regularized Greedy Forests Extreme Gradient Boosting Implementing XGBM in python Tuning Hyperparameters of XGBoost in Python Implement XGBM in R/H2O Adaptive Boosting Implementing Adaptive Boosing LightGBM Implementing LightGBM in Python Catboost Implementing Catboost in Python
Different Hyperparameter Tuning methods Implementing Different Hyperparameter Tuning methods GridsearchCV RandomizedsearchCV Bayesian Optimization for Hyperparameter Tuning Hyperopt
Understanding SVM Algorithm SVM Kernels In-depth Intuition and Practical Implementation SVM Kernel Tricks Kernels and Hyperparameters in SVM Implementing SVM from Scratch in Python and R
Introduction to Principal Component Analysis Steps to Perform Principal Compound Analysis Computation of Covariance Matrix Finding Eigenvectors and Eigenvalues Implementing PCA in python Visualizing PCA A Brief Introduction to Linear Discriminant Analysis Introduction to Factor Analysis
Introduction to Clustering Applications of Clustering Evaluation Metrics for Clustering Understanding K-Means Implementation of K-Means in Python Implementation of K-Means in R Choosing Right Value for K Profiling Market Segments using K-Means Clustering Hierarchical Clustering Implementation of Hierarchial Clustering DBSCAN Defining Similarity between clusters Build Better and Accurate Clusters with Gaussian Mixture Models
Understand Basics of Recommendation Engine with Case Study
8 Ways to Improve Accuracy of Machine Learning Models
Introduction to Dask Working with CuML
Introduction to Machine Learning Interpretability Framework and Interpretable Models model Agnostic Methods for Interpretability Implementing Interpretable Model Understanding SHAP Out-of-Core ML Introduction to Interpretable Machine Learning Models Model Agnostic Methods for Interpretability Game Theory & Shapley Values
Introduction to Machine Learning Interpretability Framework and Interpretable Models model Agnostic Methods for Interpretability Implementing Interpretable Model Understanding SHAP Out-of-Core ML Introduction to Interpretable Machine Learning Models Model Agnostic Methods for Interpretability Game Theory & Shapley Values
Introduction to AutoML Implementation of MLBox Introduction to PyCaret TPOT Auto-Sklearn EvalML
Pickle and Joblib Introduction to Model Deployment
Deploying Machine Learning Model using Streamlit Deploying ML Models in Docker Deploy Using Streamlit Deploy on Heroku Deploy Using Netlify Introduction to Amazon Sagemaker Setting up Amazon SageMaker Using SageMaker Endpoint to Generate Inference Deploy on Microsoft Azure Cloud Introduction to Flask for Model Deploying ML model using Flask
Model Deployment in Android Model Deployment in Iphone

Database Normalization: A Step-by-Step Guide with Examples

SATHISH
SATHISH Last Updated : 17 Jul, 2024
10 min read

Introduction

As an SQL Developer, you regularly work with enormous amounts of data stored in different tables that are present inside databases. It often becomes difficult to extract the information if the data is unorganized. We can solve this problem using Normalization by structuring the database in different forms or stages. This article will help you understand the concept of normalization in DBMS with step-by-step instructions and examples of tables.

We’ll discuss the functional dependencies that may exist in a table and anomalies that occur due to these functional dependencies. We will see the conversion of tables into normal forms to eliminate those anomalies. In this article you will get to understand about the normalization in database with example tables and get proper guide on database normalization with examples

Learning Objectives:

  • Understand the meaning of normalization and the need for it.
  • Learn about the various functional dependencies.
  • Familiarize yourself with the different stages of normalization.

This article was published as a part of the Data Science Blogathon.

Table of Contents

What Is Normalization in DBMS?

Normalization is a technique for organizing the data into multiple related tables to minimize Data Redundancy and Data Inconsistency. It aims to eliminate anomalies in data.

Why Do We Need Normalization?

Data inconsistency results from anything that affects data integrity. This can cause the data to be correct in one place and wrong elsewhere it is stored. This can lead to unreliable and meaningless information. It occurs between tables when similar data is stored in different formats in two different tables.

For example, consider the following tables:

LibraryVisitors (StudentID, Student_Name, Student_Address, InTime, OutTime);
Students (StudentID, Student_Name, Student_Address, Department, RollNo, CourseRegistered);

In the above tables, Student_Address is stored in both tables. For each student_id, the address must be the same in those two tables. Both these relations must be considered to retrieve or update the correct address. The issues mentioned arise due to poorly designed/structured databases.

We can eliminate data inconsistency in databases by using constraints on the relations.

Data Redundancy is the condition where the same data is stored at different locations leading to the wastage of storage space.

Examples:

Student IdStudent NameCourse IDCourse Name
111JohnC08English
112AliceC08English
111JohnC02French

In the above table, we have stored student name John twice as he registered for two different courses and course name English twice as two students registered for it. This is called data redundancy. Data redundancy causes many problems in databases.

We can eliminate data redundancy in the databases by the normalization of relations.

Also Read: Different Types of Normalization Techniques

Functional Dependency

Before diving into normalization, we need to know clearly about functional dependencies.

An attribute is dependent on another attribute if another attribute uniquely identifies it.

It is denoted by  A –> B, meaning A determines B, and B depends upon A.

Example: We can find the Student’s name using the Student_ID.

What Is an Anomaly?

An anomaly is an unexpected side effect of trying to insert, update, or delete a row. Essentially more data must be provided to accomplish an operation than expected.

Consider the following relation:

Retail_Outlet_IDOutlet_LocationItem_CodeDescriptionQty_AvailableRetail_Unit_Price
R1001King Street,
Hyderabad, 540001		I1001Britannia
Marie Gold		251600
R1002Rajaji Nagar,
Bangalore, 600341		I1106Cookies581289
R1003MVP Colony,
Visakhapatnam,
500021		I1200Best Rice222000
R1001King street,
Hyderabad		I1309Dal201500

Types of Anomalies

Here are some of the most common anomalies that happen in database management.

Insertion anomalies

These occur when we cannot insert a new tuple into the table due to a lack of data.

What happens if we try to insert(add) the details of a new retail outlet with no items in its stock?

  • NULL values would be inserted into the item details columns, which is not preferable.
Deletion anomalies

They happen when the deletion of some data deletes the other required data also (Unintended data loss)

What happens if we try to delete the item of item code I1106?

  • The details of the retail outlet R1002 will also be deleted from the database.
Update anomalies:

These happen when an update of a single record requires an update in multiple records.

How many rows will be updated if the retail outlet location of R1002 is changed from King Street to Victoria Street?

  • 2 Rows will be updated
Data redundancy

This happens when new items are supplied to a retail outlet.

What details do we need to insert?

  • Apart from all necessary details, retail_outlet_location will also be inserted, which is redundant.

We have seen insert, delete, update anomalies, and data redundancy in the above-given example. Functional dependencies may lead to anomalies. To minimize anomalies, there is a need to refine functional dependencies using normalization.

Also Read: A beginner’s Guide to Database: Part 1

What Is Normalization?

Database normalization is the process of organizing a relational database in accordance with a series of so-called normal forms in order to reduce data redundancy and improve data integrity. It was first proposed by Edgar F. Codd.

“Normal Forms” (NF) are the different stages of Normalization in DBMS:

  • 1 NF (First Normal Form)
  • 2 NF (Second Normal Form)
  • 3 NF (Third Normal Form)
  • BCNF (Boyce -Codd Normal Form)
  • 4 NF (Fourth Normal Form)
  • 5 NF (Fifth Normal Form)
  • 6 NF (Sixth Normal Form)

4NF to 6NF applies to multivalued dependencies and complex table scenarios. In this article, we discuss up to BCNF.

Different forms or stages of normalization in DBMS

Also Read: Understanding the need for DBMS

NF: First Normal Form

A relation R is said to be in 1 NF (First Normal) if and only if:

  • All the attributes of R are atomic.
  • It does not contain any multi-valued attributes.

In the above-taken example of the Retail_Outlets table, we have stored multiple values in an address field, such as street name, city name, and pin code.

What if we want to know about all retail outlets in a given city? We may need to perform some string operations on the address field, which is not preferable. So we need to store all these atomic values in separate fields.

A multi-valued attribute is an attribute that can have multiple values like Contact numbers. They should also be separated like ContactNo1, ContanctNo2,.. to achieve 1st Normal form.

1 NF | First normal form
1st Normal Form

Advantage: 1 NF allows users to use the database queries effectively as it removes ambiguity by removing the non-atomic and multi-valued attributes, which creates major issues in the future while updating and extracting the data from the database.

Limitation: Data redundancy still exists even after 1st Normal form, so we need further normalization in DBMS.

NF: Second Normal Form

A relation R is said to be in 2 NF (Second Normal) form if and only if:

  • R is already in 1 NF
  • There is no partial dependency in R between non-key attributes and key attributes.

Suppose we have a composite primary or candidate key in our table. Partial dependency occurs when a part of the primary key (Key attribute) determines the non-key attribute.

In the Retail Outlets table, the Item_Code and Retail_Outlet_ID are key attributes. The item description is partially dependent on Item_Code only. Outlet_Location depends on Retail_Outlet_ID. These are partial dependencies.

To achieve normalization in DBMS, we need to eliminate these dependencies by decomposing the relations.

2 NF | Second normal form
2nd Normal Form

From the above decomposition, we eliminated the partial dependency.

Advantage: 2 NF attempts to reduce the amount of redundant data in a table by extracting it, placing it in a new table(s), and creating relationships between those tables.

Limitation: There are still some anomalies, as there might be some indirect dependencies between Non-Key attributes, leading to redundant data.

NF: Third Normal Form

A relation R is said to be in 3 NF (Third Normal Form) if and only if:

  1. R is already in 2 NF
  2. There is no transitive dependency that exists between key attributes and non-key attributes through other non-key attributes.

A transitive dependency exists when another non-key attribute determines a non-key attribute. In other words, If A determines B and B determines C, then automatically, A determines C.

Transitive dependency
Transitive Dependency

Some other examples:

  • The Year of birth determines the Age of the person
  • The price of an Item determines the class of the Item
  • The ZIP code of a city determines the City’s Name
3 NF | Third normal form
3rd Normal Form

Advantage: 3 NF ensures data integrity. It also reduces the amount of data duplication.

Boyce-Codd Normal Form

It is an upgraded version of the 3rd Normal form. It is also called as 3.5 Normal Form.

A relation R is said to be in 3 NF (Third Normal Form) if and only if:

  • R is already in 3 NF
  • For any dependency A –> B, then A should be the Super key.

In simple words, if A –> B, then A cannot be a non-prime Attribute if B is a prime attribute which means that A non-prime attribute cannot determine a prime attribute.

You must be wondering how’s this possible. but Yes, there can be some cases in which the Non-Prime attribute will determine the prime attributes even if the relationship was in the 3rd Normal form. BCNF does not allow this kind of dependency.

Sample Table

Let us understand this better with an example. Look at the below Relation of Student Enrollments table.

Student_IDCourse_NameProfessor
101JAVAProf. Java
102C++Prof. CPP
101PythonProf. Python
103JAVAProf. Java_2
104PythonProf. Python_2

In the above relation:

  • One student can enroll in multiple courses.
  • Multiple professors can teach one course.
  • One professor can be assigned only one course.

So the (Student_ID & Course_Name) will form the primary key. These 2 will compositely determine all other attributes in the relation. In our case, it is only the professor.

  • The Relation is clearly in 1st Normal Form as there are No Multivalued attributes, and all attributes have atomic values.
  • The Relation is in 2nd Normal Form as there are No Partial dependencies.
  • Student_Id cannot determine Course_Name as one student can enroll in multiple courses.
  • Course_Name cannot determine the professor, as multiple professors may teach the same course.
  • The relation is in 3rd normal form as there are no transitive dependencies.

If we observe here, the “Professor” attribute, a non-prime attribute, can determine the Course_Name as each professor teaches only one course. But Course_Name is a prime attribute, and Professor is not a Super Key. That means a non-prime attribute determines the prime attribute.

This is not allowed in BCNF. So, how do we decompose this relation?

BCNF | Boyce-Codd Normal Form | DBMS normalization
Boyce-Codd Normal Form

Until here, we have seen normal forms up to BCNF. Here are some guidelines to follow while normalizing the database.

Guidelines for Using Normalization in DBMS

  • Depending on the business requirements, we can normalize the tables up to the 2nd normal form or the 3rd normal form.
  • Prefer tables in 3 NF in applications with extensive data modifications.
  • Prefer tables in 2 NF in applications with extensive data retrieval.
  • Reason: retrieving data from multiple tables is a costly operation.
  • Converting the tables from higher normal form to lower normal form is called “Denormalization”.

The below picture summarizes how to reach the third normal form from an unnormalized form:

unnormalized to the third stage of normalization

Any relational database without normalization may lead to problems like large tables, difficulty maintaining the database as it involves searching many records, poor disk space utilization, and inconsistencies. If we fail to eliminate this kind of problem, it would lead to data integrity and redundancy problems. Normalization of a relational database helps to solve these problems. Normalization applies to a series of transformations in terms of normal forms. Any relation in a database must be normalized to get efficient access to the database. Each Normal form eliminates each type of dependency and improves the data integrity.

Advantages of Normalization

Normalization helps a lot with organizing data. Here are some of its advantages:

  • It reduces data redundancy: Normalisation assists in removing redundant data from tables, using less storage space, and increasing database effectiveness.
  • It improves data consistency: Normalisation guarantees that the data stays organized and consistent, lowering the possibility of data errors and inconsistencies.
  • It makes database design simple: Normalization offers rules for arranging tables and data linkages. This facilitates database design and maintenance.
  • It handles queries faster: Faster query performance is a result of normalized tables’ generally easier search and data retrieval capabilities.
  • It simplifies database maintenance: By dividing a database’s complexity into smaller, more manageable tables, normalization makes it simpler to add, change, and delete data.

Conclusion

This article was aimed at making you understand the normalization process and how to apply it when you design a database system. There is another multi-valued dependency that 4NF and 5NF can eliminate. Try to explore those also.

I hope this article helped you to understand the concept of normalization better and also we have talk about the normalization in database with examples. If you have any questions, please let me know in the comments.  I wish you great learning ahead.

Key Takeaways:

  • Normalization is a technique for organizing the data into multiple related tables to minimize Data Redundancy and Data Inconsistency.
  • Insertion, deletion, update, and data redundancy are the various possible anomalies that may occur when building a database.
  • There are seven different stages of normalization known as Normal Forms.

Frequently Asked Questions?

Q1. What is normalization with an example?

A. Database normalization is the process of organizing data in a database efficiently. It involves reducing redundancy and dependency by dividing large tables into smaller tables and defining relationships between them.

Q2. What are the four 4 types of database normalization?

A. The four types of database normalization are:
a) First Normal Form (1NF)
b) Second Normal Form (2NF)
c) Third Normal Form (3NF)
d) Boyce-Codd Normal Form (BCNF)

Q3. What are the 5 rules of database normalization?

A. The 5 rules of database normalization, often referred to as normal forms, are:
a) First Normal Form (1NF): Each column in a table must contain atomic values, and there should be no repeating groups or arrays.
b) Second Normal Form (2NF): The table must be in 1NF, and all non-key attributes must be fully dependent on the primary key.
c) Third Normal Form (3NF): The table must be in 2NF, and there should be no transitive dependencies, meaning no non-key attribute should depend on another non-key attribute.
d) Boyce-Codd Normal Form (BCNF): A stronger version of 3NF where every determinant is a candidate key.

Q4. What is 1NF vs 2NF vs 3NF?

A. 1NF, 2NF, and 3NF refer to different stages of normalization:
a) 1NF (First Normal Form): Ensures that the data is organized without repeating groups and each attribute contains atomic values.
b) 2NF (Second Normal Form): Builds upon 1NF and ensures that non-key attributes are fully dependent on the entire primary key.
c) 3NF (Third Normal Form): Builds upon 2NF and eliminates transitive dependencies by ensuring that non-key attributes are not dependent on other non-key attributes.

The media shown in this article is not owned by Analytics Vidhya and is used at the Author’s discretion.

batch normalizationblogathonnormalizationNormalization Techniques
SATHISH
SATHISH

Hi there, my name is Sathish and I am currently pursuing my final year of B. Tech in the Department of IT from India. I am extremely passionate about the field of data science and machine learning and enjoy studying the latest advancements in deep learning technologies. In addition to my technical skills, I also have a solid foundation in software engineering. I am driven by a desire to learn and grow and am always eager to take on new challenges and expand my knowledge. I am excited about the opportunities that lie ahead in my career and am committed to achieving my goals through hard work and dedication.

Free Courses

Generative AI 4.7

Generative AI - A Way of Life

Explore Generative AI for beginners: create text and images, use top AI tools, learn practical skills, and ethics.
Large Language Models 4.5

Getting Started with Large Language Models

Master Large Language Models (LLMs) with this course, offering clear guidance in NLP and model training made simple.
Prompt Engineering 4.6

Building LLM Applications using Prompt Engineering

This free course guides you on building LLM apps, mastering prompt engineering, and developing chatbots with enterprise data.
LlamaIndex 4.8

Improving Real World RAG Systems: Key Challenges & Practical Solutions

Explore practical solutions, advanced retrieval strategies, and agentic RAG systems to improve context, relevance, and accuracy in AI-driven applications.
RAG systems using LlamaIndex 4.7

Microsoft Excel: Formulas & Functions

Master MS Excel for data analysis with key formulas, functions, and LookUp tools in this comprehensive course.

Recommended Articles

Responses From Readers

Clear

Shingi
Shingi

Thanks brother this was helpful

123

Write for us

Write, captivate, and earn accolades and rewards for your work
  • Reach a Global Audience
  • Get Expert Feedback
  • Build Your Brand & Audience
  • Cash In on Your Knowledge
  • Join a Thriving Community
  • Level Up Your Data Science Game
imag
Congratulations, You Did It!
Well Done on Completing Your Learning Journey. Stay curious and keep exploring!
6 Types of Probability Distribution in Data Science
6 Types of Probability Distribution in Data Science
Different Types of Normalization Techniques
Different Types of Normalization Techniques

We use cookies essential for this site to function well. Please click to help us improve its usefulness with additional cookies. Learn about our use of cookies in our Privacy Policy & Cookies Policy.

Show details