Resources » SQL Server

What is Normalization in database design


Posted Date: 08-Jan-2013  Last Updated:   Category: SQL Server    
Author: Member Level: Gold    Points: 60


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

normalization

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.

after decompostion

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


Did you like this resource? Share it with your friends and show your love!

Responses to "What is Normalization in database design"
Feedbacks      

Post Comment:




  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:   Sign In to fill automatically.
    Email: (Will not be published, but required to validate comment)



    Type the numbers and letters shown on the left.


    Submit Article     Return to Article Index

    Subscribe to Subscribers
    Active Members
    TodayLast 7 Daysmore...

    Awards & Gifts
    Talk to Webmaster Tony John

    Online Members

    Priya jain
    More...
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India