Normalization in DBMS

Normalisation is a process by which we can decompose or divide any relation into more than one relation to remove the anomalies in the relational database.

Update anomalies: An update anomaly is a data inconsistency that results from data redundancy and a partial update

Deletion anomalies: A delete anomaly occurs when you delete a record that may contain attributes that shouldn’t be deleted

Insert anomalies: We tried to insert data in a record that does not exist at all.

Normalization rules are divided into the following normal forms:

1. First Normal Form
2. Second Normal Form
3. Third Normal Form
4. BCNF

First Normal Form: A relation is in the first normal form if the domain of each value contains an only atomic or single value, it means atomicity must be present in the relation.

ID   Name       Courses
--------------------
1    Akshay     c1,c2
2    Abhishek   c3
3    Aditya     c2,c3
ID   Name       Courses
------------------
1    Akshay      c1
1    Akshay      c2
2    Abhishek    c3
3    Aditya      c2
3    Aditya      c3

Second Normal Form: A relation is in the second normal form if it is in the first normal form and there are no partial dependencies. Partial Dependency occurs when a non-prime attribute is functionally dependent on part of a candidate key.

Third Normal Form: A relation is in the third normal form if it is in the second normal form and the is no transitive dependency.

Boyce–Codd Normal Form: A relation is in the BCNF if and only if every non-trivial function dependency X → Y is with X as superkey.

A trivial functional dependency is a database dependency that occurs when you describe a functional dependency of an attribute or a collection of attributes that include the original attribute. If a functional dependency X →Y holds true where Y is not a subset of X, this dependency is called non-trivial Functional dependency.

Fourth Normal Normal: It is a level of database normalization where there are no non-trivial multivalued dependencies other than a candidate key.

It builds on the first three normal forms (1NF, 2NF and 3NF) and the Boyce-Codd Normal Form (BCNF). It states that, in addition to a database meeting the requirements of BCNF, it must not contain more than one multivalued dependency.

Fifth Normal Form: The Fifth Normal Form is also known as the project-join normal form. A relation is in Fifth Normal Form (5NF), if it is in 4NF, and won’t have lossless decomposition into smaller tables.

You can also consider that a relation is in 5NF if the candidate key implies every join dependency in it.