Normalization

Scroll Down to download Lecture in PDF

NORMALIZATION: INTRODUCTION 1. Normalization is a sequential process applied to organizing the data in the database. 2. Normalization is used to minimize the redundancy and to remove Insertion, Update, and Deletion Anomalies. 3. In Normalization the larger table is divided into smaller tables. 4. These tables are linked with each other with the help of keys. TYPES OF NORMAL FORMS Normalization is a step-by-step process in which total 6 stages are used such as: 1. 1NF also known as 1st Normalization Form 2. 2NF also known as 2nd Normalization Form 3. 3NF also known as 3rd Normalization Form 4. BCNF also known as Boyce Codd's Normalization Form 5. 4NF also known as 4th Normalization Form 6. 5NF also known as 5th Normalization Form Each step has its own functioning and importance. 1NF A relation is in 1NF if All the contained values should be atomic values. 2NF A relation will be in 2NF if It is in 1NF and All non-key attributes are fully functional dependent on the key attribute or primary key 3NF A relation will be in 3NF if It is in 2NF and No transition dependency exists between relations BCNF It is a strict version of 3NF 4NF A relation will be in 4NF if It is in Boyce Codd's normal form and Hold no multi-valued attribute dependency 5NF A relation is in 5NF If It is in 4NF and Should not hold any join dependency between relations   1ST NORMALIZATION FORM OR 1NF 1NF A relation is in 1NF if • All the contained values should be atomic values. Means all multivalued and composite attributes are decomposed into atomic attributes. This is a simple database table and not normalized yet. Let’s convert this table in 1NF table. As we can see in the normalized table all the attributes which have multiple vales such as Course Id, Course Name and Contact number for each respective single entity, are now separated in individual lines. So, all the attributes have atomic values. 2nd NORMALIZATION FORM OR 2NF 2NF A relation will be in 2NF if • It is in 1NF and • All non-key attributes are fully functional dependent on the key attribute or primary key • No partial dependencies Find out functional dependencies Let’s take a table which already have 1NF properties means all the attributes have atomic values. Here in this table Course attributes have 2 functional dependencies, such as SID and CID SID Course CID Course So now we should remove this partial dependency, so the resultant table is become as given below: So now we have two tables instead of one but these tables are interconnected with the help of key attributes such as CID is present both the tables. 3rd NORMALIZATION FORM OR 3NF 3NF A relation will be in 3NF if • It is in 2NF and • No transition or transitive dependency exists between relations For any Non-Trivial functional dependency such as X Y X is a super key or Y is a prime attribute, means each element of Y is part of some candidate key What is Trivial dependency? If X Y and Y Z, means X Y Z This is called Trivial dependency. Let’s take a table which already have 2NF properties. Here we have found one trivial dependency such as SID NAME Salutation So, we have to remove this So now the resultants tables are as follows: BOYCE CODD'S NORMALIZATION FORM OR BCNF BCNF is a strict version of 3NF Because in 3NF we have X Y X is a super key or Y is a prime attribute, means each element of Y is part of some candidate key But in BCNF X Y X is a super key. Means all the attributes must dependent on the super key only. Let’s take a table which already have 3NF properties. Here in this table Salutation_Id is dependent on Name. Here Name may be considered as a candidate key, but name may be repeated, so its not unique, so the Salutation_Id should be dependent on SID only, so we have to remove this dependency such as Name Salutation_Id And make it as SID Salutation_Id So now the resultant tables are as follows: 4th NORMALIZATION FORM OR 4NF 4NF A relation will be in 4NF if It is in Boyce Codd's normal form and Hold no multi-valued attribute dependency Let’s take a table which already have BCNF properties. Here CID, Mobile and Class are Multivalued Attributes So, we should separate these attributes to make table simpler. Here we separate the Mobile attribute but we can’t remove the CID attribute because it connects the Course table to this main table such as: 5th NORMALIZATION FORM OR 5NF 5NF A relation is in 5NF If • It is in 4NF and • Should not hold any join dependency between relations Or the Join should be lossless. In simple words in the whole normalization process the complex table is divided into smaller and simpler table, but it does not mean that these all tables are separated from each other, instead these all are interconnected with each other with the help of keys. So, when we want to access the data, the whole data (separated and scattered in separate tables) should be shown as a single unit. For this these all tables perform Join Operation(pre-defined). So, in 5NF we confirmed that the “no data loss” is occurred during the normalization process and when the Join Operation is performed, the whole data is shown as a single unit. Means the Join should be lossless. For example, we take our all tables which we previously used and check that are they fulfill 5NF regulations or not. This is our original table This is Our 1NF table. These are the all resultant tables which we created during normalization process. As we can see that all the tables are interconnected with the help of keys. So, when we want any data of any entity we can retrieve it without any loss. Click here to Download PDF Lecture # 8