NORMALIZATION

Database is a collection of meaningful data by which we can easily upload,update,delete and manage data. while creating a database the main problem which encounter is data redundancy. Data redundancy is condition in which same piece of data is located in different places with in same database. In other words whenever data is repeated in database that is called data redundancy. The main problem comes by redundancy is data storage space .

The main problem arises by data redundancy are following:

• Data take extra memory or extra storage space.
• If same data is store in differen places than during data modification we have to modify it from all location ,that process is very timeconsuming and hard to perform.

• During deletion process we have to delete same data from all location.

  so to eliminate redundancy with in database Normalization technique is used.

Normalisation is a technique of removing redundancy. Normalization is a approach in which tables are decompose to eliminate data redundancy. It is a multi-step process that puts data into tabular form by removing duplicated data from the relation tables.

NORMALIZATION RULE

Normalization rule are divided into following normal form.

• First Normal Form
• Second Normal Form
• Third Normal Form

First Normal Form (1NF)

In First Normal Form, their will be no column in which more then two values must be stored Instead of that we should separate such data into multiple rows.

eg :- Employee Table :

Employee Table following 1NF will be :

Second Normal Form (2NF)

In example of First Normal Form there are two rows for suresh, to include its multiple department . While it is an inefficient use of space. Also in the above Table in First Normal Form, while the candidate key is {employee, department}, emp_id of employee only depends on employee column, which is incorrect as per Second Normal Form. To achieve second normal form, it would be helpful to split out the department into an independent table, and match them up using the employee names as foreign keys.

New Employee Table following 2NF will be :

New Employee Table introduced for 2NF will be :

Third Normal Form (3NF)

A table design is said to be in 3NF if following condition get satisfied:

• Table must be in 2NF

• every non-prime attribute of table must be dependent on primary key.

  For example, consider a table with following fields.

Employee_Detail Table :

In this table employee_id is Primary key, but address, city and state depends upon pincode. The dependency between pincode and other fields is called transitive dependency. Hence to apply 3NF, we need to make new table containing address, city and state, with pincode as primary key.

New Employee_Detail Table :

Employee_id Employee_name DOB pincode

001 suresh 1-1-1992 mandakni rishikesh uttrakhand 249204

002 rohit 1-2-1992 doonvihar dehradun uttrakhand 248001

003 pankaj 12-2-1992 neshvilla selaqui uttrakhand 255001

Address Table :