Difference between 1NF, 2nd NF, 3rd NF and BCNF? Normalization Example Tutorial

Normalization is one of the essential concept of relational database. It is the process or technique to remove duplicate data from tables and thus reduce the storage size. It also helps to maintain integrity of data. Normalization likewise assists with coordinating the information in the data set. It is a multi-step process that sets the information into even structure and eliminates the copied information from the relational tables. 

Normalization coordinates the segments and tables of a data set to guarantee that data set integrity constraints appropriately execute their conditions. 

It is an orderly method of deteriorating tables to take out information overt repetitiveness (redundant) and unfortunate qualities like Insertion, Update, and Deletion anomalies.

Why Normalization?

The primary justification for normalizing the relations is eliminating these abnormalities. Inability to kill oddities prompts information overt repetitiveness and can cause information uprightness and different issues as the data set develops. 

Normalization comprises of a progression of rules that assists with directing you in making a decent data set structure.

Data alteration anomalies can be sorted into three kinds:

Insertion Anomaly: Insertion Anomaly alludes to when one can't embed a new tuple into a relationship because of absence of information.

Deletion Anomaly: The delete anomaly alludes to the circumstance where the deletion of information brings about the accidental loss of another significant information.

Update Anomaly: The update anomaly is the point at which an update of a solitary information esteem requires various lines of information to be refreshed.

Types of normal forms:

• First normal form(1NF)
• Second normal form(2NF)
• Third normal form(3NF)
• Boyce & Codd normal form (BCNF)

1NF (First Normal Form)

Rules:

As per 1NF rule, Each table cell ought to contain a single value. Each record should be unique.

A relation is supposed to be in 1NF (first normal structure), in the event that it contains no multi-valued characteristic. All in all you can say that a connection is in 1NF on the off chance that each characteristic contains just atomic(single) value as it were.

According to the standard of first normal structure, a attribute (column) of a table can't hold different qualities. It ought to hold just unique values.

Example : Let's say an organization needs to store the names and contact subtleties of its workers. It makes a table in the data set that seems to be this:

Two employees (Jon and Lester) have two versatile numbers that caused the Emp_Mobile field to have different qualities for these two employees.


This table isn't in 1NF as the standard says "each quality of a table unquestionable requirement nuclear (single) esteems", the Emp_Mobile values for employees Jon and Lester disregards that standard.
To make the table conforms to 1NF we want to make separate columns for the every versatile number in such a manner with the goal that none of the qualities contains different qualities.

2NF (Second Normal Form)

Rules:

• Table must be in 1NF (First normal form)
• No non-prime trait is reliant upon the appropriate subset of any candidate key of table.

Candidate Keys: {Teacher_Id, Subject}
Non prime attribute: Teacher_Age


This table is in 1 NF in light of the fact that each attribute has nuclear qualities. In any case, it isn't in 2NF on the grounds that non prime attribute Teacher_Age is subject to Teacher_Id alone which is a legitimate subset of candidate key.

This abuses the standard for 2NF as the standard says "no non-prime attribute is subject to the legitimate subset of any candidate key of the table". To create the table consents to 2NF we can deteriorate it in two tables like this:

Teacher_Details table:

Teacher_Subject table:

Now the tables are in Second normal form (2NF).

3NF (Third Normal Form)

Rules:

• Table should be in 2NF
• Transitive functional dependency of non-prime attribute on any really key ought to be eliminated.

An attribute that isn't important for any candidate key is known as non-prime attribute.

As such 3NF can be made sense of this way: A table is in 3NF in the event that it is in 2NF and for each functional dependency X-> Y no less than one of the accompanying circumstances hold:

• X is a very key of table
• Y is a great attribute of table
• An attribute that is a piece of one of the candidate keys is known as prime attribute.

Example : Let's say an organization needs to store the total location of every worker, they make a table named Employee_Details that seems to be this:

• Super keys: {Emp_Id}, {Emp_Id, Emp_Name}, {Emp_Id, Emp_Name, Emp_Zip}… so on
• Candidate Keys: {Emp_Id}
• Non-prime attributes: all attributes with the exception of Emp_Id are non-prime as they are not piece of any candidate keys.

Here, Emp_State, Emp_City and Emp_District subject to Emp_Zip. Further Emp_zip is subject to Emp_Id that makes non-prime attributes (Emp_State, Emp_City and Emp_District) transitively reliant upon very key (Emp_Id). This disregards the standard of 3NF.
To make this table agrees with 3NF we need to crumble the table into two tables to eliminate the transitive dependency:

Employee Table:

Employee_Zip table:

Boyce Codd normal form (BCNF)

It is a development variant of 3NF that is the reason it is additionally alluded as 3.5NF. BCNF is stricter than 3NF. A table consents to BCNF on the off chance that it is in 3NF and for each functional dependency X->Y, X ought to be the very key of the table.

Model: Suppose there is an organization wherein employees work in more than one division. They store the information like this:

Functional dependencies in the table above:

• Emp_Id -> Emp_Nationality
• Emp_Dept -> {Dept_Type, Dept_No_Of_Emp}
  
  

Emp_Nationality table:

Candidate key: {Emp_Id, Emp_Dept}

The table is not in BCNF as neither Emp_Id nor Emp_Dept alone are keys.

To make the table comply with BCNF we can break the table in three tables like this:

Functional dependencies:

Emp_Id -> Emp_Nationality

Emp_Dept -> {Dept_Type, Dept_No_Of_Emp}

Candidate keys:

For first table: Emp_Id

For second table: Emp_Dept

For third table: {Emp_Id, Emp_Dept}

This table is now in BCNF as in both the functional dependencies left side part is a key.

Difference between 1NF, 2NF, 3NF and BCNF normal forms?

Now that we have a fair idea of different normal forms, let's try to answer this questions by quickly summarizing what we have learned

1NF ensures that a table has a simple and atomic structure. This means that each column contains only one value, and each record is unique. 

A table is in 1NF if it contains no repeating groups or arrays. Essentially, 1NF removes duplicate columns and ensures that data is organized into rows and columns.

2NF builds on 1NF by addressing partial dependency. A table is in 2NF if it is in 1NF and all non-key attributes are fully dependent on the entire primary key. 

This means that if a table has a composite key, each non-key column must depend on all parts of the key, not just some of them. 2NF eliminates redundant data related to partial keys.

3NF further refines 2NF by removing transitive dependencies. A table is in 3NF if it is in 2NF and all the columns are only dependent on the primary key, and nothing else. 

This means that non-key attributes should not depend on other non-key attributes. 3NF ensures that all non-key attributes are only dependent on the primary key, reducing data redundancy.

BCNF is a stricter version of 3NF. A table is in BCNF if it is in 3NF and for every functional dependency (A → B), A must be a superkey. 

This form handles cases where a table has multiple candidate keys, ensuring that the table is free from certain types of anomalies that 3NF might not cover. BCNF aims to remove any remaining redundancy and dependencies that could lead to data anomalies.

That's all about what is Normalization in database and difference between 1NF, 2NF, 3NF and BCNF. In this tutorial, you have seen Normalization in SQL and figured out the different Normal forms of Normalization. 

Presently, you can put together the information in the data set and eliminate the information overt repetitiveness and advance information uprightness. 

This instructional exercise additionally helps fledglings for their screenings to figure out the idea of Normalization in SQL.

Assuming you have any inquiries or contributions for our article group with respect to this "The Supreme Guide to Normalization in SQL" instructional exercise, do share them in the remarks segment beneath. Our group will survey them and assist with tackling them for you very soon!

Other related SQL queries, Interview questions, and articles:

• 5 Courses to learn Database and SQL Better (courses)
• 50 SQL Server Interview Questions with Answers (SQL Server questions)
• How to find the second highest salary in a table? (solution)
• 50 SQL and Database Phone Interview questions (SQL telephonic questions)
• Write a SQL query to copy or backup a table in MySQL (solution)
• 30 Database Interview Questions with Answers (database questions)
• How to migrate SQL queries from Oracle to SQL Server 2008? (answer)
• 5 Courses to learn Oracle and Microsoft SQL Server database (courses)
• Top 5 Websites to learn SQL online for FREE? (resource)
• Top 5 Courses to learn MySQL Database for Beginners (Courses)
• What is the difference between UNION and UNION ALL in SQL? (answer)
• Difference between clustered and non-clustered indexes in SQL? (answer)
• Difference between the Unique and Primary keys in the table? (answer)
• 5 Free Courses to learn T-SQL and SQL Server for Beginners (Courses)
• Top 5 Books to learn Advanced SQL and Database Design (books)
• 5 Best PostgreSQL Courses for Beginners (online courses)
• Difference between View and Materialized View in Database? (answer)
• 10 Free SQL and Database Courses for Beginners (free courses)

Thanks for reading this article so far. If you like these  common SQL Server Interview questions answers then please share them with your friends and colleagues. If you have any questions or feedback then please drop a note.