In this section you will learn the concepts of data normalization. The most important
thing in database designing is to make sure that the data get
properly distributed among the tables. Simply we can say that
the designing of the table in proper manner is called Normalization.
Normalization is a process that is used in relational database design to
organize the data for minimizing the duplication. In normalization, we divide
the database in two or more tables and create a relationship between them.
After isolating the data we perform some addition, deletion or modification
on the fields of a table then we propagate and remove the duplicate data from
the related tables.
The main goals of normalization
- To eliminate the redundancy of data
- To make sure that the data
dependencies (relationship) make sense.
By these two goals we reduce the space occupied by the duplicate data in the database
tables and ensure that the data is logically stored there.
Some of the positive points of the
data normalization in database is as under :
- Data integrity
- To make optimized queries on the normalized tables
fast, efficient results.
- To make faster index and also make a perfect sorting.
- To increase the performance of the database.
First normal Form (1NF)
The First Normal Form requires the atomic values in each column. Atomic means
the set of values are not available with in the column. In other words, in
First Normal Form table must have at least one candidate key and make sure
table donít have any duplicate record. In First Normal Form repeating groups
are not allowed, that is no attributes which occur a different number of times
on different records.
Second normal Form (2NF)
The Second Normal Form can
be achieved only when a table is in the 1NF. We can make the 2NF by
eliminating the partial dependencies. As the First Normal Form deals with
only the atomicity of data, but Second Normal Form deals with the relationships of
tables like composite keys and non-key columns. In
Second Normal Form subset of data is removed and is organized in separate
tables. This process is applied to multiple rows of
a table till the duplicity get reduced.
Third Normal Form (3NF)
The Third Normal Form can be achieved only when a table is in the Second Normal Form. We can make the 3NF by
eliminating all transitive dependencies lying among the fields of a record. In Third Normal Form, all columns
should depend on the primary key only i.e. remove the column, which is not
dependent upon the primary key.
Boyce-Codd Normal Form (BCNF)
In the normalization Boyce - Codd Normal Form needs a table
to meet the Third Normal Form. In
Boyce - Codd Normal Form every non-trivial functional dependency must be a
dependent on a superkey.
Fourth Normal Form (4NF)
The Fourth Normal Form can be achieved when a table meets the
Boyce-Codd Normal Form. Fourth Normal Form confirms that the independent multivalued facts are efficiently and
correctly represented in database design.
Fifth Normal Form (5NF)
The Fifth Normal Form is achieved only when a table meets the
Fourth Normal Form. In this normalization it is ensured
that all non-trivial join dependencies get eliminated.