Normalization in SQL Server

Normalization in SQL Server

Normalization in SQL Server Part - 11

In this post I will explain all about Normalization in SQL Server, although this part is quite short. So let’s start.

Normalization in SQL Server

NORMALIZATION

Normalization is the technique of designing the database with the least redundancy and duplicity of data. 

or

A method to organize the data into a related table, is referred to as normalization of the data.

or

Normalization is the process of organizing data into related data.

or

Normalization is used to reduce the redundancy of the database. The normalization ensures that various types of anomalies and inconsistencies are not introduced within the database. A table structure is normal forms with minimum redundancy. There are 3 forms of normalization –

  • First Normal Form (FNF)
  • Second Normal Form (SNF)
  • Third Normal Form (TNF)

Normalization in SQL Server

1. First Normal Form (FNF)

A table is said to be in the FNF when each cell of the table contains precisely one volume –

Project Table:

E CodeDepartmentProject CodeHours
E001SystemsP2790
P51101
P2060
E005SalesP28109
P2298

The data in the table is not normalized because a cell in Project Code and Hours has more than one values applying the FNF, the table becomes like mentioned below –

E CodeDepartmentProject CodeHours
E001SystemsP2790
E001SystemsP51101
E001SystemsP2060
E005SalesP28109
E005SalesP2298

2. Second Normal Form (SNF)

A table is said to be in Second Normal Form (SNF) when it is in FNF and every attribute in the row is functionally dependent upon the whole key and not just part of the key.

Project Table:

E CodeDepartmentProject CodeHours
E001SystemsP2790
E001SystemsP51101
E001SystemsP2060
E005FinanceP2810
E008AdminP29NULL
E008AdminP3072

The primary key is composite key (E Code + Project Code)

We have to bifurcate it into two tables like mentioned below –

Employee Department Table:

E CodeDepartment
E001Systems
E005Finance
E008Admin

Projects Table:

E CodeProject CodeHours
E001P2790
E001P51101
E001P2060
E005P2810
E008P29NULL
E008P3072

3. Third Normal Form (TNF)

A relation is said to be TNF when it is SNF and every non-key attribute is functionally dependent only on the primary key

Employee:

E CodeDepartmentDepartment Head
E101SystemsE901
E305FinanceE909
E402SalesE906
E508AdminE908
E607OperationsE909
E608ProjectsE904

The primary key in the table (Employee) is E Code for each value of E Code, there is exactly one value of department and department head, hence department and department head is functionally dependent upon whole key E Code, Hence table is in SNF.

However the attribute department is dependent on the attribute department also. As per TNF all non-key attributes have to be functionally department only on the primary key. this table is not TNF.

Since department head is the first department on department which is no primary key –

Employee:

E CodeDepartment
E101Systems
E305Finance
E402Sales
E508Admin
E607Operations
E608Projects

Department:

DepartmentDepartment Head
SystemsE901
SalesE906
AdminE908
FinanceE909

Assuming this tutorial post has well-acquainted aspiring to the complete requirement.

Also requesting, if you have any ideas or suggestions for me or you need some important blogs/Videos or guidelines related to SQL Server, Excel, WordPress, SEO, Website technologies then please feel free to write me on below comment section or you can go through the Contact Page. I will definitely come back to you with your desired requirements.

Hope this tutorial post was helpful for you and you liked very well, I am happy to serve you better. If you liked really this tutorial post, please don’t forget to share on social media like – Facebook, Whatsapp, LinkedIn, Twitter, Instagram, etc.

Leave a Reply