Normalization in DBMS: 1NF, 2NF, 3NF and BCNF

If a table has data redundancy and is not properly normalized, then it will be difficult to handle and update the database, without facing data loss. It will also eat up extra memory space and Insertion, Update and Deletion Anomalies are very frequent if database is not normalized.

Normalization :

Normalization is the process of minimizing redundancy from a relation or set of relations. Redundancy in relation may cause insertion, deletion, and update anomalies. So, it helps to minimize the redundancy in relations. Normal forms are used to eliminate or reduce redundancy in database tables.

Visit here - https://www.cplusplus.in/normalization-in-dbms-1nf-2nf-3nf-and-bcnf/

There is various level of normalization. These are some of them:

1. First Normal Form (1NF) :

If a relation contain composite or multi-valued attribute, it violates first normal form, or a relation is in first normal form if it does not contain any composite or multi-valued attribute. A relation is in first normal form if every attribute in that relation is singled valued attribute.

A table is in 1 NF iff:

1. There are only Single Valued Attributes.
2. Attribute Domain does not change.
3. There is a Unique name for every Attribute/Column.
4. The order in which data is stored, does not matter.

First Normal Form (1NF) does not eliminate redundancy, but rather, it’s that it eliminates repeating groups.

2. Second Normal Form (2NF) :

Second Normal Form (2NF) is based on the concept of full functional dependency. A relation that is not in 2NF may suffer from the update anomalies. To be in second normal form, a relation must be in first normal form and relation must not contain any partial dependency. A relation is in 2NF if it has No Partial Dependency, i.e., no non-prime attribute (attributes which are not part of any candidate key) is dependent on any proper subset of any candidate key of the table.

In other words, a relation that is in First Normal Form and every non-primary-key attribute is fully functionally dependent on the primary key, then the relation is in Second Normal Form (2NF).

Although Second Normal Form (2NF) relations have less redundancy than those in 1NF, they may still suffer from update anomalies. If we update only one tuple and not the other, the database would be in an inconsistent state. This update anomaly is caused by a transitive dependency. We need to remove such dependencies by progressing to Third Normal Form (3NF).

3. Third Normal Form (3NF) :

A relation is in third normal form, if there is no transitive dependency for non-prime attributes as well as it is in second normal form.

A relation is in 3NF if at least one of the following condition holds in every non-trivial function dependency X –> Y:

1. X is a super key.
2. Y is a prime attribute (each element of Y is part of some candidate key).

In other words, a relation that is in First and Second Normal Form and in which no non-primary-key attribute is transitively dependent on the primary key, then it is in Third Normal Form (3NF).

Although, 3NF is adequate normal form for relational database, still, this (3NF) normal form may not remove 100% redundancy because of X -> Y functional dependency, if X is not a candidate key of given relation. This can be solve by Boyce-Codd Normal Form (BCNF).

4. Boyce-Codd Normal Form (BCNF) :

Boyce–Codd Normal Form (BCNF) is based on functional dependencies that take into account all candidate keys in a relation; however, BCNF also has additional constraints compared with the general definition of 3NF.

A relation is in BCNF iff, X is superkey for every functional dependency (FD) X -> Y in given relation.

In other words, a relation is in BCNF, if and only if, every determinant is a Form (BCNF) candidate key.

Important Points :

1. A database design is considered as bad, if it is not even in the first Normal Form (1NF).
2. If the proper subset of candidate key determines non-prime attribute, it is called partial dependency.
3. If A->B and B->C are two FDs then A->C is called transitive dependency.
4. Third Normal Form (3NF) is considered adequate for normal relational database design because most of the 3NF tables are free of insertion, update, and deletion anomalies. Moreover, 3NF always ensures functional dependency preserving and lossless.
5. To test whether a relation is in BCNF, we identify all the determinants and make sure that they are candidate keys.
6. A prime attribute cannot be transitively dependent on a key in BCNF relation.
7. Redundancies are sometimes still present in a BCNF relation as it is not always possible to eliminate them completely.

P.S. -?I have contributed this article here -?Normalization in DBMS: 1NF, 2NF, 3NF and BCNF