Normalization in SQL | Database Normalization Forms - 1NF, 2NF, 3NF, BCNF | Edureka Rewind

Key Takeaways

data in the database is stored in terms of enormous quantity retrieving certain data will be a tedious task if the data is not organized correctly with the help of normalization we can organize this data and also reduce the Redundant data hey guys this is prik from edura and I welcome you all to this interesting session on normalization in SQL in this session I'll explain everything that is related to normalization with simple examples that are easy to remember firstly let's look at the agenda for today's session so we're going to start off with understanding normalization and moving further we shall look at various types of normalization and those are first normal form second normal form third normal form and voice card normal form I hope you guys are clear with the agenda but before moving further if you haven't subscribed to our Channel then do subscribe to never miss out an update with that being said let's get started the first Topic in today session is what is normalization database normalization is a technique of organizing the data in the database it is a systematic approach of decomposing tables to eliminate data redundancy it is a multi-step process that puts data into table of form removing the duplicated data from its relational tables on the screen we just saw that the table is getting decomposed into two smaller table is it really necessary to normalize the table that is present on the database well every table in the database has to be in the normal form so normalization is used mainly for two purpose so the first one is it is used to eliminate repeated data having repeated data in the system not only makes the process slow but will cause trouble during the later part of transactions and the second one is to ensure the data dependencies make some logical sense yes usually the data is stored in database with certain logic huge data sets without any purpose are completely wasted it's like having an abundant resource without any application the data that we have should make some logical sense normalization came into existence because of the problems that occurred on data now let's look at those problems and these are known as data anomalies if a table is not properly normalized and has data redundency then it will not not only eat up the extra memory space but will also make it difficult to handle and update the database let's look at the first anomaly that is insertion anomaly suppose for a new position in a company Mr rakshit is selected but the department has not been allotted for him in that case if we want to update his information to the database we need to set the department information as null similarly if we have to insert data of thousand employees who are in similar situation then the department information will be repeated for all those thousand employees this scenario is a classical example of insertion anomalies the next one is update anomaly what if Mr rakit leaves the company or is it no longer the head of the marketing department in that case all the employee records will have to be updated and if by mistake we miss any record it will lead to data inconsistency this is nothing but updation anomaly and the final one is deletion anomaly in our employee table two different pieces of information are kept together that is employee information and Department information hence at the end of financial year if employee records are deleted we will also lose the department information this is nothing but deletion anomaly so these were some of the problems that occurred while managing the data to eliminate all this anomalies normalization came into existence there are many normal forms which are still under development but let's focus on the very basic and the essential ones only so we will be talking about first normal form second normal form third normal form and finally end the session with Bo Cod normal form so without wasting further time let's proceed to First normal form in first normal form we tackle the problem of atomicity here atomicity means values in the table should not be further divided in simple terms a single cell cannot hold multiple values if a table contains a composite or multivalued attributes it violates the first normal form so the following functions will be performed in first normal form the first one is it removes repeating groups from the table and next it creates a separate table for each set of related data and finally it identifies each set of related data with a primary key to understand this in a better way let's look at the given table in the employee table we have employee ID employee name phone number and salary as columns we can clearly see that the phone number column has two values thus it violates the first normal form now if we apply the first normal form to the above table we get the following result in this table each and every row is distinct that is no cell has multiple values the table has achieved atomicity first normal form is simple and can be easily identified in the table we can clearly see there is no multiple values in each and every column thus the first normal form is achieved now let's move to Second normal form second normal form was originally defined by EF cod in 1971 a table is set to be in second normal form only when it fulfills the following condition the first condition is it has to be in first normal form and the second one is the table also should not contain partial dependency here par IAL dependency means the proper subset of a candidate key determines a nonprime attribute so what is a non-prime attribute let's understand this in a simple way attributes that form a candidate key in a table are called Prime attributes and the rest of the attributes of the relation are non Prime for a table Prime attributes can be like employee ID and Department ID and the non-pr attributes can be like office location to understand second normal form let's consider this table this table has a composite primary key that is employee ID and Department ID makes the primary key the non-key attribute is office location in this case office location only depends on Department ID which is only the part of primary key therefore this table does not satisfy the second normal form so what to do in such scenario the answer is simple split the table accordingly to bring this table to Second normal form we need to break the table into two parts which will give the following tables the first table has employee ID and Department ID as columns the second one has Department ID and office location as columns as you can see we have removed the partial functional dependency that we initially had now in the table the column office location is fully dependent on the primary key of that table which is nothing but Department ID I hope you have understood second normal form now that we have learned first normal form and second normal form let's head to the next part of this normalization next topic is third normal form third normal form is a normal form that is used in normalizing the table to reduce a duplication of data and ensure referential Integrity the following condition has to be met by the table to be in third normal form and the first condition is the table has to be in second normal form and the second condition is no non-prime attribute is transitively dependent on any non-prime attribute which depends on other nonprime attributes I know it's a bit confusing so let me make it simple for you it's like if C is dependent on B and in turn B is dependent on a then transitively C is dependent on a this should not happen in third normal form all the non-prime attributes must depend only on the prime attributes so these are the two necessary condition that needs to be attained so why was the normal form designed firstly to eliminate undesirable data anomalies the next one is to reduce the need for restructuring over time finally to make the data model more informative since we have understood the third normal form let's look at the example table in the above table student ID determines subject ID and subject ID determines subject therefore student ID determines subject via subject ID this implies that we have transitive functional dependency and this table does not satisfy the third normal form now in order to achieve third normal form we need to divide the table as shown below firstly let's divide the table and store student ID student name subject ID and address in it all the columns are referring to the primary key which is student ID let the second table have subject ID and subject column so subject is dependent only on subject ID and not on student ID as you can can see from the above table all the non-key attributes are now fully functionally dependent only on the primary key in the first table columns such as student name subject ID and address are only dependent on student ID in the second table subject is only dependent on subject ID with this being understood now we can proceed further to next normal form that is boy Cod normal form this is also known as 3.5 normal form it is the higher version of third normal form form and was developed by Raymond F boys and Edgar fcod to address certain types of anomalies which were not dealt with third normal form before proceeding to boy Cod normal form the table has to satisfy third normal form in boy Cod normal form if every functional dependency that is a implies B then a has to be the Super Key of that particular table so what is a Super Key a Super Key is a group of single or multiple Keys which identifies rows in a table table let's look at the table to clearly understand voice code normal form in the given table one student can enroll for multiple subjects there can be multiple Professor teaching one subject and for each subject a professor is assigned to the student these are the necessary condition of this table in this table all the normal forms are satisfied except voice code normal form why as you can see that student ID and subject form the primary key which means that the subject column is prime attribute but there is one more dependency that is Professor is depending on subject and while subject is a prime attribute Professor is a non-prime attribute which is not allowed by Bo code normal form now in order to satisfy the boys code normal form we will be dividing the table into two parts the table at the top will hold student ID which already exists and we will create a new column that is Professor ID and in the second table which is below we have the columns Professor ID professor and subject columns why do we need to have a new column that is Professor ID by doing this we are removing the non-prime attributes functional dependency in the second table Professor ID will be the Super Key of that table and remaining column will be functionally dependent on it by doing this we are satisfying boys Cod normal form so this brings us to the end of this session I hope you have clearly understood the normalization and its different types if you have any queries or doubts regarding this session please let me know in the comment section and I'll get back to you with an answer thank you guys for watching this video and have a great day e e e e e e e e e e e e e e