NORMALIZATION IN DBMS

Today we are talking about Normalization. Before starting we have to know what it is mean by Normalization.So it simply means minimizing the redundancy or we can say that it is a process of analyzing given relational schema based on there function dependency and primary keys to achieve desired properties of minimizing redundancy(store same data multiple times) and minimizing the insertion, deletion and update anomalies.

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: Insert anomalies We tried to insert data in a record that does not exist at all.

To minimizing this anomalies we follow some Normalization ways.

1. First Normal Form(1NF):- A relation is said to be in “1NF” if the values in the domain of each attribute of the relation are atomic or in other way we can say that only one value is associated with each attribute and the value is not a set of values or list of values.

2. Second Normal Form(2NF):- A relation is said to be “2NF” if it must be in “1NF” and relation must not contain any partial dependency. It is based on the concept of Fully Functional Dependency.we can say that ‘R’ is in “2NF” if every non-prime attribute(Attributes which are not part of Candidate Key) of ‘R’ is fully functional dependent on the key of ‘R’.

STUD_NO            COURSE_NO        COURSE_FEE
1                     C1                  1000
2                     C2                  1500
1                     C4                  2000
4                     C3                  1000
4                     C1                  1000
2                     C5                  2000

Conversion of 2NF

Table 1                                    Table 2
STUD_NO            COURSE_NO          COURSE_NO           COURSE_FEE
1                 C1                  C1                        1000
2                 C2                  C2                        1500
1                 C4                  C3                        1000
4                 C3                  C4                        2000
4                 C1                  C5                        2000

3. Third Normal Form(3NF):- A relation schema “R” is in 3NF if it satisfies 2NF and no non-prime attribute of “R” is transitively dependent on key of “R”. Or we can say that a relation is in 3NF if functional dependency X determined Y satisfied any one of the following condition

a) X determined Y is a trivial functional dependency , that is Y is a subset of X.

b) If X determined Y ,then X is a super key.

c)If X determined Y ,then (Y-X) is a prime attribute. Y-X means attribute present in Y but not in X and prime attribute means they take part in Candidate key.

Example :

4. BCNF (Boyce-Codd Normal Form):-Even when a database is in 3rd Normal Form, still there would be possibility of anomalies resulted if it has more than one Candidate Key. A relation is in the BCNF if and only if every non-trivial function dependency X →Y is with X as super key.

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

5. Fourth Normal Form (4NF ):-If no database table instance contains two or more, independent and multi valued data describing the relevant entity, then it is in 4th Normal Form.

5. Fifth Normal Form(5NF):-A table is in 5th Normal Form only if it is in 4NF and it cannot be decomposed into any number of smaller tables without loss of data.

6. Sixth Normal Form(6NF ):-6th Normal Form is not standardized, yet however, it is being discussed by database experts for some time.

Conclusion

This is the reason why storing all the records in one big complex table will be the not right thing to do. and that leads to problems like Insertion, deletion, update anomaly. so we must break the complex table into smaller tables with the help of Normalization.