What is Normalization in database design
Want to know what is Normalization? This article would be helpful for you to learn about Normalization and forms of normalization.
The process of Normalization was first developed by E.F. Codd. Normalizing a logical database involves organizing the data into more than one table. Logical structure of the database is designed so that basic operations on the database like insertion, deletion or updation can be performed without any problems. Normalization improves the performance by reducingredundancy in the database tables. The basic objective of Normalization are to reduce redundancy, which means that information to be stored only once in relation. Another serious problems with using the relation as base relation is the problem of modification anomalies..Database Modification Anomalies
Consider the Relation Activity
Deletion Anomaly
Suppose that each activity has a fixed fee that is same for all student. If we delete the tuple (row) for the student 100 , we lose not only the fact that student 100 is cricketer but also lose the fact cricket fee is 5000. This is called deletion anomaly. Insertion Anomaly
Suppose we want to store the fact that the activity swimming fee is 6500. We can't enter this data into the activity relation until a student takes up swimming. This is called insertion anomaly because we can't insert a fact about one entity until we have an additional fact about another entity.Updation Anomaly
Suppose we want to change the fee of football activity 4500 to 4000 for this change we are faced with either the problem of searching the activity relation to find every tuple with football and change the fee from 4500 to 4000 or the possibility of producing an inconsistent result.(The fee for football may be 4500 in one place or 4000 in another).This is called updation Anomaly.Decomposition
One solution of database modification anomalies is decomposition. Decomposition means divide a relation schema into many relation schemas. A decomposition of a relation schema R by two (or more) schemas say R1 and R2 and each consist of the subset of the attribute of R. These would include two properties.Lossless join property
The lossless join property guarantees that if we join R1 and R 2 relation schemas than we get the original relation schemas R. There should be no any spurious tuple result of this join.
Dependency preservation property
This property ensures that each functional dependency is represented in some individual relations resulting after decomposition.
After decomposition Activity relation into stu-act and act-fee relation.
Now, there are no modification anomalies.
No deletion anomaly:
Now, we can delete the tuple for the student 100 from the stu-act relation and we can not lose the fact that cricket fee is 5000.
No Insertion anomaly:
Now we can store the fact that the swimming fee is 6500.
No Updation anomaly:
Now we can change the fee of football activity from 4500 to 4000. In act-fee relation and we never get the problem of inconsistency.Why Normalization is necessary?
Normalization is necessary when creating commercial database application. To provide a formal framework for analyzing relation schemas based on their key and on the functional dependencies among their attribute. And free the collection of relations from undesirable insertion, updation and deletion dependencies.Benefits of normalization are:
1.Reduced redundancy
2.Required less memory space
3.Free from modification anomalies