Database Normalization: 1NF, 2NF, 3NF, BCNF Table Examples
In this blog, we will discover everything about what is normalization including 1NF, 2NF, 3NF, BCNF, database normalization, normalization in the database with example tables, database-design normalization, and the importance of normalization in databases. So before we move further let’s first understand the meaning of database normalization.
Database Normalization
Database Normalization is a method of composing the data in the database. Normalization is a systematic technique of decomposing tables to eradicate data redundancy or repetition and unpleasant characteristics like Update, Insertion, and Deletion Anomalies. It is a multiple-step procedure that settles data into tabular form, eliminating duplicate data from the association tables.
Database Normalization
Normalization is utilized for primarily two purposes which are as follows:
- Removing redundant or useless data.
- Assuring data reliances make sense, that is data is logically saved.
Now let’s look at the database normalization with examples below.
The database normalization example can be effortlessly understood with the help of a trial study. You can think that a video library sustains a database of movies rented out. Devoid of any normalization, complete information is stocked on one table. Now let us look at the rules of 1NF below.
- 1NF (First Normal Form) Rules
There are two rules that you should keep in mind and here are they are given below:
- Each table cell should include a single value.
- Each record is required to be unique.
Before we move on further let’s understand a few things first.
What is the KEY?
A KEY is a significance utilized to recognize a record in a table uniquely. A KEY could be a solitary column or a combination of various columns. You should also note that columns in a table that are not utilized to recognize a record uniquely are known as non-key columns.
What is a Primary Key?
A primary key is a single-column value utilized to identify or recognize a database record uniquely. It consists of the following attributes:
- A primary key cannot be invalid or null
- A primary key value must be unique or different
- The primary key values should barely be altered
- The primary key must be provided a value when a new or fresh record is inserted
What is a Composite Key?
A composite key is a primary key organized of many columns utilized to specify a record uniquely. In our database, if we possess two people with the same name Mark, but they reside in distinct places. Therefore, we need the full name and address of both to know a record uniquely. Hence it is known as a composite key.
So now let’s move into the second normal form i.e., 2NF and its rules.
- 2NF (Second Normal Form) Rules
There are two rules given below:
Rule 1– it should be in 1NF and another rule is
Rule 2– Single Column Primary Key
From the above two mentioned rules, It is clear that we can not move ahead to create our simple and easy database in the 2nd Normalization form unless we divide the table.
- BCNF (Boyce-Codd Normal Form)
Just when a database is in 3rd Normal Form, however, there would be anomalies occurring if it has more than one Candidate Key. Occasionally BCNF is further referred to as 3.5 Normal Form.
- 4NF (Fourth Normal Form) Rules
The rules of 4nf are that if no database table example includes two or more, autonomous and multivalued data describing the related entity, then it is under the 4th Normal Form.
- 5NF (Fifth Normal Form) Rules
A table is in 5th Normal Form barely if it is in 4NF and it can not deteriorate into any number of smaller tables without loss of information.
- 6NF (Sixth Normal Form) Proposed
6th Normal Form is not formalized, nonetheless, it has been examined by database professionals for some time. Hopefully, we will have a detailed and systematic definition for the 6th Normal Form shortly… Now let us have a glimpse of the importance of normalization in the database.
Importance of Normalization in Database
Normalization is a procedure to exclude the drawbacks of a database with poor design. A badly designed database is unpredictable and establishes problems while expanding, deleting, or updating data. Here, are the following points that give rise to database normalization a significant step in the database design process.
By settling the database anomalies
The aspects of Normalization i.e. 1NF, 2NF, 3NF, BCF, 4NF, and 5NF eliminate all the Insert, Update, and Delete anomalies. We will discuss each point one by one so let us look at the insert first.
- Insert
Insertion Anomaly happens when you begin to insert data in a record that does not prevail.
- Delete
Deletion Anomaly is when information is to be deleted due to the bad design of the database, on the other hand, another record is also deleted.
- Eliminate Redundancy of Data
Saving the same data item many times is understood as Data Redundancy. A normalized table does not pose the problem of redundancy of data.
- Data Dependency
The data gets saved in the accurate table and ensures normalization.
- Isolation of Data
A well-designed database asserts that the modifications in one table or field do not impact another. This is accomplished through Normalization.
Data Consistency
While revising or updating, if a record is left, it can lead to inconsistent data. Normalization unravels it and confirms Data Consistency.
The functional dependencies are a very significant element of the normalized data procedure. Extensively database systems are normalized databases up to the third normal form. A primary key uniquely recognized is recorded in a Table and cannot be ineffective or null. Moreover, foreign keyworks connect tables and refer to them as primary keys
Conclusion
Let us conclude by saying that database designing is crucial to the successful execution of a database management system that fulfills the data prerequisites of an enterprise system. Normalization in DBMS enables the production of database systems that are cost-effective and have adequate security models.