More Related Content
What's hot (20)
Similar to Normalization (20)
Recently uploaded (20)
Normalization
- 1. Normalization of Database Database Normalisation is a technique oforganizing the data in the database.Normalization is a systematic approach of decomposing tables to eliminate data redundancyand undesirable characteristics like Insertion,Update and Deletion Anamolies.Itis a multi-step process that puts data into tabular form by removing duplicated data from the relation tables. Normalization is used for mainlytwo purpose, Eliminating reduntant(useless) data. Ensuring data dependencies make sense i.e data is logicallystored. Problem Without Normalization Without Normalization,itbecomes difficultto handle and update the database,withoutfacing data loss.Insertion, Updation and Deletion Anamolies are very frequent if Database is notNormalized.To understand these anomalies let us take an example of Student table. S_id S_Name S_Address Subject_opted 401 Adam Noida Bio 402 Alex Panipat Maths 403 Stuart Jammu Maths 404 Adam Noida Physics Updation Anamoly : To update address ofa studentwho occurs twice or more than twice in a table,we will have to update S_Address column in all the rows,else data will become inconsistent. Insertion Anamoly : Suppose for a new admission,we have a Studentid(S_id),name and address ofa student but if studenthas not opted for any subjects yet then we have to insert NULL there, leading to Insertion Anamoly. Deletion Anamoly : If (S_id) 401 has only one subjectand temporarilyhe drops it, when we delete that row, entire studentrecord will be deleted along with it.
- 2. Normalization Rule Normalization rule are divided into following normal form. 1. First Normal Form 2. Second Normal Form 3. Third Normal Form 4. BCNF First Normal Form (1NF) As per First Normal Form,no two Rows of data mustcontain repeating group ofinformation i.e each setof column musthave a unique value,such that multiple columns cannotbe used to fetch the same row.Each table should be organized into rows,and each row should have a primary key that distinguishes itas unique. The Primary key is usuallya single column,butsometimes more than one column can be combined to create a single primarykey. For example consider a table which is not in First normal form Student Table : Student Age Subject Adam 15 Biology, Maths Alex 14 Maths Stuart 17 Maths In First Normal Form,any row mustnot have a column in which more than one value is saved, like separated with commas.Rather than that, we mustseparate such data into multiple rows. Student Table following 1NF will be : Student Age Subject Adam 15 Biology Adam 15 Maths
- 3. Alex 14 Maths Stuart 17 Maths Using the First Normal Form,data redundancyincreases,as there will be manycolumns with same data in multiple rows but each row as a whole will be unique. Second Normal Form (2NF) As per the Second Normal Form there mustnot be any partial dependencyof any column on primarykey. It means that for a table that has concatenated primarykey, each column in the table that is not part of the primarykey must depend upon the entire concatenated key for its existence.If any column depends onlyon one part of the concatenated key, then the table fails Second normal form. In example of First Normal Form there are two rows for Adam, to include multiple subjects thathe has opted for. While this is searchable,and follows Firstnormal form,it is an inefficientuse of space.Also in the above Table in First Normal Form,while the candidate key is {Student, Subject}, Age of Student only depends on Studentcolumn, which is incorrectas per Second Normal Form.To achieve second normal form,it would be helpful to splitout the subjects into an independenttable,and match them up using the studentnames as foreign keys. New Student Table following 2NF will be : Student Age Adam 15 Alex 14 Stuart 17 In Student Table the candidate key will be Student column,because all other column i.e Age is dependenton it. New Subject Table introduced for 2NF will be : Student Subject Adam Biology Adam Maths
- 4. Alex Maths Stuart Maths In Subject Table the candidate key will be {Student, Subject} column.Now,both the above tables qualifies for Second Normal Form and will never suffer from Update Anomalies.Although there are a few complexcases in which table in Second Normal Form suffers Update Anomalies,and to handle those scenarios Third Normal Form is there. Third Normal Form (3NF) Third Normal form applies thatevery non-prime attribute of table mustbe dependenton primarykey, or we can say that, there should notbe the case that a non-prime attribute is determined byanother non-prime attribute.So this transitive functional dependency should be removed from the table and also the table mustbe in Second Normal form. For example,consider a table with following fields. Student_Detail Table : Student_id Student_name DOB Street city State Zip In this table Student_id is Primarykey, but street,city and state depends upon Zip.The dependencybetween zip and other fields is called transitive dependency. Hence to apply 3NF, we need to move the street,city and state to new table, with Zip as primarykey. New Student_Detail Table : Student_id Student_name DOB Zip Address Table : Zip Street city state The advantage of removing transtive dependencyis, Amount of data duplication is reduced. Data integrity achieved. Boyce and Codd Normal Form (BCNF)
- 5. Boyce and Codd Normal Form is a higher version of the Third Normal form.This form deals with certain type of anamolythat is not handled by 3NF. A 3NF table which does not have multiple overlapping candidate keys is said to be in BCNF. For a table to be in BCNF, following conditions mustbe satisfied: R mustbe in 3rd Normal Form and, for each functional dependency( X -> Y ), X should be a super Key.