What is 1NF , 2NF, 3NF ??

Hi,

Normalization helps in reducing data redundancy

1NF: This type of normalization states that there must not be any duplicates in the tables that we use. In other words, all the tables used must have a primary key defined.  
2NF: This type of normalization states that data redundancy can be reduced if attributes those are dependent on one of the keys of a composite primary key are isolated to a separate table. Not only does this reduces data redundancy but also helps in increasing data retention when a delete is done. For example, consider a table that has the following columns: Part Id, State, City, and Country. Here, assume Part Id & Country form the composite primary key. The attributes state & city depend only on the country. 2NF states that if such is the case then split the table into 2 tables. One with Part Id & country as the columns. Other with Country, state & city as the columns. In the 1^st table if a delete is made to all the rows with Part Id = 'X' then we would lose country related data too. But in the 2^nd case this would not happen.   
 3NF: This type of normalization states that if a dependency exists on certain attributes other than the primary key then the table split depending on the dependency has to be done. Consider the same example above. In the present case consider that Part Id is the only primary key. Now state, city depend only on country & not on Part Id. This table is already in 1NF & 2NF. But to achieve 3NF we would do the same split as above.


thanks.


If this post is useful then mark it as "Accepted Answer"

Hi Faisal,


In creating a database, normalization is the process of organizing it into tables in such a way that the results of using the database are always unambiguous and as intended. Normalization may have the effect of duplicating data within the database and often results in the creation of additional tables. (While normalization tends to increase the duplication of data, it does not introduce redundancy, which is unnecessary duplication.) Normalization is typically a refinement process after the initial exercise of identifying the data objects that should be in the database, identifying their relationships, and defining the tables required and the columns within each table.

A simple example of normalizing data might consist of a table showing:

Customer    Item purchased    Purchase price
Thomas    Shirt    $40
Maria    Tennis shoes    $35
Evelyn    Shirt    $40
Pajaro    Trousers    $25

If this table is used for the purpose of keeping track of the price of items and you want to delete one of the customers, you will also delete a price. Normalizing the data would mean understanding this and solving the problem by dividing this table into two tables, one with information about each customer and a product they bought and the second about each product and its price. Making additions or deletions to either table would not affect the other.

Normalization degrees of relational database tables have been defined and include:

First normal form (1NF). This is the "basic" level of normalization and generally corresponds to the definition of any database, namely:

    * It contains two-dimensional tables with rows and columns.
    * Each column corresponds to a sub-object or an attribute of the object represented by the entire table.
    * Each row represents a unique instance of that sub-object or attribute and must be different in some way from any other row (that is, no duplicate rows are possible).
    * All entries in any column must be of the same kind. For example, in the column labeled "Customer," only customer names or numbers are permitted.

Second normal form (2NF). At this level of normalization, each column in a table that is not a determiner of the contents of another column must itself be a function of the other columns in the table. For example, in a table with three columns containing customer ID, product sold, and price of the product when sold, the price would be a function of the customer ID (entitled to a discount) and the specific product.

Third normal form (3NF). At the second normal form, modifications are still possible because a change to one row in a table may affect data that refers to this information from another table. For example, using the customer table just cited, removing a row describing a customer purchase (because of a return perhaps) will also remove the fact that the product has a certain price. In the third normal form, these tables would be divided into two tables so that product pricing would be tracked separately.


Please refer the below links

http://searchsqlserver.techtarget.com/definition/normalization

http://techmunchy.wordpress.com/2008/06/07/normalization-basics-1nf-2nf-and-3nf/

Thanks