New Members FAQ
What is Normalization in database design
Posted Date: 08-Jan-2013
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
. 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 reducing
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
Database Modification Anomalies
Consider the Relation Activity
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.
Suppose we want to store the fact that the activity swimming fee is 6500. We can’t enter this data into the
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.
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.
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:
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"
No responses found. Be the first to respond...
Notify me by email when others post comments to this article.
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.
to fill automatically.
(Will not be published, but
to validate comment)
Type the numbers and letters shown on the left.
Data Model used in Database
Microsoft SQL Server Versions and associated code names
Return to Resources
Post New Resource
Post resources and
Data Model used in Database
How to restore the backup with existing DB name?
Fun and Games with SQL – Crossword Puzzle 1
SSIS – Conditional Split Transformation
How to sort results in UNION query in SQL Server
How to use Coalesce in Sql server
Tag posting guidelines
Last 7 Days
Asheej T K
Awards & Gifts
Articles Rss Feeds
Forum Rss Feeds
Talk to Webmaster Tony John
Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India