Basic Concept of Database Normalization - Simple Explanation for Beginners

hello friends and welcome to study tonight in this video we will try to understand the concept of normalization in database we will start with understanding what all problems arise if a table or a database is not normalized and how normalization solves this problem so what is normalization normalization is a technique of organizing the data into multiple related tables to minimize data redundancy now you would ask what is data redundancy and why should we reduce it well data redundancy is nothing but repetition of similar data at multiple places and we want to reduce it not just because repetition of similar data multiple times eats up extra space but it also leads to multiple other issues like insertion deletion and updation anomalies for now let's start with a very basic example and try to understand data redundancy and the problems that we face due to it so here we have a simple student table to store student information in a college we have columns roll number student name the branch or the department name the name of the hid or the head of the department you can call it a teacher name as well and the Department telephone number now let's put data of four computer science students into this table we'll add row number one two three and four put in the students name their branch name the name of the hid and the Department telephone number now in this case the branch name the hid name and the telephone number are same for all the entries that we make whenever a new student entry is created this entire information is repeated and this is what is redundancy now this data redundancy not only increased the size of our DB unnecessarily but it also leads to multiple other issues like we talked about so let's see what these issues are there are three main problems that data redundancy causes these are the insertion anomaly the deletion anomaly and the updation anomaly so let's start with insertion anomaly now we already have four students data in our table if we have to add a fifth student we will have to repeat the same data which holds the branch information like branch name the name of the h.o.t and the branch telephone number again and if we have to enter the data for hundred more students we have to repeat the data 100 more times which leads to insertion anomaly because the repetition of data will only increase as we insert more entries to our table now this repetition arises because in our example we are unknowingly unintentionally saving two pieces of information in every row Student Information and branch information now if we start deleting student information the branch information is also deleted simultaneously and when we delete the last student row from the student table we also unintentionally delete the branch information so now we don't have any student information and we also don't have the branch information because we deleted it which leads to deletion problems and is also known as deletion anomaly now let's see what updation anomaly or modification anomaly is so let's say if the H OD or the head of the department leaves for some reason making the students happy and the department gets a new actually and the system administrator has to update all the student records with the new name of th OD this is modification anomaly because this is hell lot of a world which the administrator has to do to update every single record with the new H OD name and during modification if even a single row is missed out it will lead to inconsistent data hence data redundancy can also lead to inconsistency in data as a result of updation anomaly now you understand that data redundancy is pure evil not only because it eats up extra space but it leads to problem in inserting new data deleting the data and a huge problem if we have to update the data so now the million-dollar question is how normalization will solve this problem so normalization will break the existing student table into two different tables student table and branch table the student table will save the data of student and the branch table will save the branch information now the branch information if we want to save data of computer science department students will be just one row that will be the branch name computer science the name of the Acholi the new actually I must say because the old actually left and the phone number of the department and in the student table will keep all the information of the students enrolled for this particular branch and these tables will be related by the name of the branch in our case which is CS e one can see that the branch name is still getting repeated in the students table so normalization is not about eliminating data redundancy it's about minimizing data redundancy so now earlier where all the branch information was getting repeated with each and every student record now only the branch name is repeated because the branch information is kept in a different table and if we want to update any branch information we just have to update it at one place and it will automatically be updated when we fetch a student record because the student record is connected with the branch record using the branch name and if we update the h.o.t name or the phone number in the branch table it's updated for all the students now as a redundancy is minimized hence the problems which arise due to it are also minimized let's see how the problems are solved now if we have to insert a new student data we only have to enter the roll number the student name and just the branch name because the branch information is stored separately and we don't have to modify or insert it again then we have to delete student information at the end of a session to store information of the students for the new batch we can just delete all the student records and we'll still have the branch information branch details in a separate table safe and if we have to update the name of the h.o.t again or the department's phone number we just have to do it in one place see how normalization solves a lot of problems by just dividing the data into separate independent logical entities and relating them using a common key or you can say a unique name in our case that is the branch name so normalization can be achieved in multiple ways we have three basic normal forms in which we can achieve normalization they are first normal form second normal form and the third normal form and another more advanced normalization technique which is known as B C and F we will learn about all the ordinary forms in our coming videos so stay tuned and do subscribe to the study tonight channel

Channel: Studytonight

Views: 1,133,707

Rating: 4.8986068 out of 5

Keywords: database normalization, normalisation, normalization, database nomalisation, db normalization, basics of normalization, normalization concept, normalization concept beginners, simple explanation normalization, 1NF, 2NF, 3NF, BCNF, First normal form, second normal form, third normal form, 5nf, 4nf, fifth normal form

Id: xoTyrdT9SZI

Length: 8min 10sec (490 seconds)

Published: Sat Oct 07 2017