10
FebPROMO : GET UP TO 20% OFF ON LIVE CLASSES + 2 SELF-PACED COURSES FREE!! - SCHEDULE CALL
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.
Normalization: Removing Duplicates is called Normalization. In this blog for Database normalization, we will discuss 1nf 2nf 3nf bcnf with example.
Benefits:
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 |
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
Register for a Demo class to practice Database normalization in SQL with examples.
Learn SQL Server in the Easiest Way
In this section, we will discuss example of 1nf 2nf 3nf bcnf in detail
A table is said to me 1NF, if
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.
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.
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
Signup for SQL training classes online now to know more about Database Normalization.
SQL Server Training & Certification
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
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.
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.
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 |
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
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.
AWS
DevOps
Data Science
Hadoop
Salesforce
QA
Business Analyst
MS SQL Server
Python
Artificial Intelligence
Machine Learning
Tableau
Search Posts
Related Posts
Receive Latest Materials and Offers on SQL Server Course
Interviews