Forums » .NET » .NET »

what is Normalization?types of normalization explain with examples


Posted Date: 13 Mar 2007      Posted By:: moongirl     Member Level: Gold    Member Rank: 0     Points: 2   Responses: 5



what is Normalization?types of normalization explain with examples



Responses

#113890    Author: Dotnet Galaxy      Member Level: Gold      Member Rank: 116     Date: 13/Mar/2007   Rating: 2 out of 52 out of 5     Points: 2

Normalization is the techinque of designing the database with the least redundancy and duplicacy of data. Types of Normalization:
1 NF
2 NF
3 NF
BCNF - Boyce Code Normal Form
5 NF
6NF : Impossible to achieve this level of normalization

Where NF maens Normal Form.

Thanks,
DotNet Galaxy.

*******************************************
Note - Please rate this response [Poor / Fine / Good / Great / Excellent]
*******************************************


 
#113894    Author: Hari Krishna       Member Level: Silver      Member Rank: 0     Date: 13/Mar/2007   Rating: 2 out of 52 out of 5     Points: 2

Normalization is the process of designing a data model to efficiently store data in a database. The end result is that redundant data is eliminated, and only data related to the attribute is stored within the table.
For example, let's say we store City, State and ZipCode data for Customers in the same table as Other Customer data. With this approach, we keep repeating the City, State and ZipCode data for all Customers in the same area. Instead of storing the same data again and again, we could normalize the data and create a related table called City. The "City" table could then store City, State and ZipCode along with IDs that relate back to the Customer table, and we can eliminate those three columns from the Customer table and add the new ID column.
Normalization rules have been broken down into several forms. People often refer to the third normal form (3NF) when talking about database design. This is what most database designers try to achieve: In the conceptual stages, data is segmented and normalized as much as possible, but for practical purposes those segments are changed during the evolution of the data model. Various normal forms may be introduced for different parts of the data model to handle the unique situations you may face.
Whether you have heard about normalization or not, your database most likely follows some of the rules, unless all of your data is stored in one giant table. We will take a look at the first three normal forms and the rules for determining the different forms here.
Rules for First Normal Form (1NF)
Eliminate repeating groups. This table contains repeating groups of data in the Software column.

Computer Software
1 Word
2 Access, Word, Excel
3 Word, Excel
To follow the First Normal Form, we store one type of software for each record.

Computer Software
1 Word
2 Access
2 Word
3 Excel
3 Word
3 Excel
Rules for second Normal Form (2NF)
Eliminate redundant data plus 1NF. This table contains the name of the software which is redundant data.

Computer Software
1 Word
2 Access
2 Word
3 Excel
3 Word
3 Excel
To eliminate the redundant storage of data, we create two tables. The first table stores a reference SoftwareID to our new table that has a unique list of software titles.

Computer SoftwareID
1 1
2 2
2 1
3 3
3 1
3 3

SoftwareID Software
1 Word
2 Access
3 Excel
Rules for Third Normal Form (3NF)
Eliminate columns not dependent on key plus 1NF and 2NF. In this table, we have data that contains both data about the computer and the user.

Computer User Name User Hire Date Purchased
1 Joe 4/1/2000 5/1/2003
2 Mike 9/5/2003 6/15/2004
To eliminate columns not dependent on the key, we would create the following tables. Now the data stored in the computer table is only related to the computer, and the data stored in the user table is only related to the user.

Computer Purchased
1 5/1/2003
2 6/15/2004

User User Name User Hire Date
1 Joe 5/1/2003
2 Mike 6/15/2004

Computer User
1 1
2 1
What does normalization have to do with SQL Server?
To be honest, the answer here is nothing. SQL Server, like any other RDBMS, couldn't care less whether your data model follows any of the normal forms. You could create one table and store all of your data in one table or you can create a lot of little, unrelated tables to store your data. SQL Server will support whatever you decide to do. The only limiting factor you might face is the maximum number of columns SQL Server supports for a table.
SQL Server does not force or enforce any rules that require you to create a database in any of the normal forms. You are able to mix and match any of the rules you need, but it is a good idea to try to normalize your database as much as possible when you are designing it. People tend to spend a lot of time up front creating a normalized data model, but as soon as new columns or tables need to be added, they forget about the initial effort that was devoted to creating a nice clean model.
To assist in the design of your data model, you can use the DaVinci tools that are part of SQL Server Enterprise Manager.
Advantages of normalization
1. Smaller database: By eliminating duplicate data, you will be able to reduce the overall size of the database.
2. Better performance:
a. Narrow tables: Having more fine-tuned tables allows your tables to have less columns and allows you to fit more records per data page.
b. Fewer indexes per table mean faster maintenance tasks such as index rebuilds.
c. Only join tables that you need.
Disadvantages of normalization
1. More tables to join: By spreading out your data into more tables, you increase the need to join tables.
2. Tables contain codes instead of real data: Repeated data is stored as codes rather than meaningful data. Therefore, there is always a need to go to the lookup table for the value.
3. Data model is difficult to query against: The data model is optimized for applications, not for ad hoc querying.


 
#113897    Author: Hari Krishna       Member Level: Silver      Member Rank: 0     Date: 13/Mar/2007   Rating: 2 out of 52 out of 5     Points: 2

if u want still some more material let me know ur mail id ill send it....




 
#113901    Author: moongirl      Member Level: Gold      Member Rank: 0     Date: 13/Mar/2007   Rating: 2 out of 52 out of 5     Points: 2

pls send it more detail s about normalization
pls


 
#348621    Author: RobinHood      Member Level: Gold      Member Rank: 44     Date: 19/Feb/2009   Rating: 2 out of 52 out of 5     Points: 4

Verify this links..
Normalization..
http://en.wikipedia.org/wiki/Database_normalization
http://databases.about.com/od/specificproducts/a/normalization.htm
http://www.rsolutions.net/RSweb/Normalization/
http://www.databasejournal.com/sqletc/article.php/1428511

Denormalization..
http://en.wikipedia.org/wiki/Denormalization
http://www.objectarchitects.de/ObjectArchitects/orpatterns/Performance/Denormalization/
http://www.tdan.com/view-articles/4142
http://www.allinterview.com/showanswers/11455.html

Regards
Sridhar R
Nothing is illegal, Until You Get Caught


 
Post Reply

 This thread is locked for new responses. Please post your comments and questions as a separate thread.
If required, refer to the URL of this page in your new post.



Next : what is ctype . what is that use.other than is there any type conversions are there.
Previous : Create the folder in bin directory in C# windows application in 2.0
Return to Discussion Forum
Post New Message
Category: .NET

Related Messages

Subscribe to Subscribers
Active Members
TodayLast 7 Daysmore...

Awards & Gifts
Talk to Webmaster Tony John

Online Members

Shanmukh joga
More...
Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India