Database Normalization

Database Normalization

Database is a collection of data that is organized in an easily way to accessed, managed and update. It is basically a place where the information is stored and operations are performed to store and retrieve the data. Database normalization is a technique that is used to reduce or avoids the redundancy and dependency of data. In normalization big tables are split in small tables and built the relationship between them. It is needed to avoid update and delete anomalies.

The main objective of normalization is to create relations where every dependency is on the key.

Data integrity: data in the database is consistent and satisfy all integrity constraints.

Data redundancy: if data in database is present in two different locations.

If we remove the redundancy it prevent insertion, deletion and update errors since the data is only available in one attribute of one table in the database.

There are many types of normalization

1. First normal form (1NF)

2. Second normal form(2NF)

3. Third normal form(3NF)

4. Boyce codd normal form(BCNF)

5. Fourth normal form(4NF)

6. Fifth normal form(5NF)

First normal form: in first normal form each cell contains the single value and attributes are atomic.

Example:

Normalized to 1NF

Second Normal Form: To understand the 2NF we get to know the about the Prime attribute and the non prime attribute. Prime attribute is the part of the prime key and non prime attribute is not a part of prime key. All non prime attributes are fully dependent on the prime attribute.

Normalized in Second normal form:

Third Normal form: If the 2NF already in the table and the no exists of transitive dependency is there.

Normalized form in 3NF:

Boyce-codd normal form: It is a higher version of 3NF. The problems that are involved in the 3NF are solved in BCNF. In third normal form table the attributes does not have the multiple overlapping candidate keys is said to be in BCNF. More than one candidate is present and some common attributes are there in the relation.

Fourth normal form: A table is in fourth normal form only when it is in BCNF and contains not more than one multi valued. It involves the many to many relations.

Normalized in 4NF:

Fifth normal form: fifth normal form is also known as the project join normal form. If it is in 4NF and it cannot have a lossless decomposition into any number of smaller tables. In 5NF table should have only candidate key and its primary key should consist of only a single column.

Normalized in 5NF