Hello friends, today we're going to discuss normalization. Normalization is the Very Important Concept of DBMS So let's start with normalization. It is a technique to remove or Reduce the Redundancy from a table. Many times, in a table we have duplicacy or redundancy of the data To remove that redundancy or duplicacy, there is a method which is normalization. If we can't remove then We can reduce the duplicacy. So let's start with the normalization. There are basically two types of duplicacy in the database One is the row level and another is the column level. so let's start with the row level first, In the row level duplicacy, i have mentioned one example. In the example like there is a student table In the student table, we are having SID, S. Name and Age. We have taken student's ID, student's name and student's age. Now i have put some values over there Like 1,2,1 these are the Values Now if you look at it carefully. The First row We call it first row. First row and the third row. is exactly same means both the rows are exactly same This is what row level duplicacy. And guys this row level duplicacy should not be there in the table. Method to remove it we have allready discuss in my previus videos. But I'll still tell you once again that we use concept of primary key. We set a primary key in our table, any attributes. But How does we set, that is mentioned in My videos. So what did I? Made the SID as the primary key When we made the SID, a primary key. We know the property of the primary key. It is unique plus not null. Means neither value can be repeated in it Nor it can be left empty. So we cannot put this 1 value in it. So it will take care of the row level duplicacy. But guys the important part is the column level duplicacy. I have mentioned one table over there. In this column level duplicacy let's start with the student ID, student name, Course ID, Course name, faculty ID, faculty name and salary You can note that later. I have put some Values over there. I have put some value, put some values of the rows. Now basically if we look at it carefully. No two rows are exactly same here No two rows are exactly same. Why? Because we already removed the row level duplicacy. How do we do? I just told you that Set the primary key. Made SID as a primary key. So there are no two rows cannot be exactly same in it. but can the columns be the same? Yes, let's see like this is the column Course ID, Course name, faculty ID, faculty name and salary See the Third row. & Fourth row. Although I have mention only for values over there but guys Four value is not only there, when we create the database. So in the database we can have more than thousand or in lakhs We can have the rows.. So in all this rows If I have some data, some columns values are exactly same So what will be the problem come? So basically the problem comes in three types. One is a Insertion Anomaly Seond one is Deletion Anomaly And third one is Updation. Anomaly Guys what is anomaly, problem. Anomaly means a kind of problem. But such a problem that occurs on a Special occasion, that we called anomaly. So guys let's start with the insertion. that if i want to insert something in this table will it create a problem? For example, I want to put the date of a new student. Let's say, A new student has taken admission in the university. Let's say, it's a database of university. So if I have put the information of a new student. Like SID is 5 And S name is Like, varun So guys there is no problem. U can easily insert the data over there. But let's take another case. If the University start a new course. Let's say, University start a new course Like C10. And course name is MBBS. Guys, note it down well. The Course ID is C10 & Course Name is MBBS. This Information. you cannnot insert over there. Even Faculty Also, Like If You Want to insert the new Faculty Data Some new joinee has come So if I want to insert their data, then i Cannot Insert the data. The reason being is What you will insert in the SID? You can't insert in the SID How will you insert in the SID? Until I, what i asked in the question that just the new course is introduced. I didn't talk about the student. The student will be registered later on that course. Then I leave it empty. So it is a primary key. Can't leave empty. If i'll put dummy value like, What Kind of Dummy Value? You cannot insert the wrong data in the database. That will create problem to you later on, guys. More over you can put # Or put some special character But Guys this Will Create a huge Problem. Instead of solving a small problem, you will get stuck in a big problem. So we.should not following this kind of thing. So, then how to insert? So we cannot directly insert in it. This is what called the insertion anomaly. Let's check the deletion anomaly. What is Deletion Anomaly? Like If i have given a query. Simple query. Remove the database of roll number 1. I mean if someone would tell me. That's roll number 1. His data has to be removed. so guys how do we write a simple query? Delete Fromm. Student Means Table Name, Delete from Table Name Where. SID =1 Simple, Delete from Table name where SID=1 What This Will Do? It Will Delete the whole row. So, there was no problem. That's why i am saying. It's a anomaly. It occurs on a special..... Occurs in a special case. So lool guys, if I ask you the question again? Like, that you have to delete the Data of roll number 2. So simple, you do "delete from table name where SID=2". So what will happen guys? This row will be Totally deleted. It's been totally Removed from the database. Means you can say that this row is not existing over there. So if i remove this row Can you tell me Who is teaching Roll number 2?. What was the course name of roll number 2. It can happen guys, that there is only one student . who was studying that Particular Course. It may be possible that there was only one student who was studying that particular course. And he had the same faculty, particular faculty was teaching him Guys just removes the student's details but Because of that, all the information was removed. So that means i have lost the Information of the course and. the Information of the faculty as well. So means extra information is getting removed which I cannot recover later. Okay, then the third. Updation anomaly. Actually, why we taking only three insertion, deletion, Updation. Basically, there are three operations in the database. Generally we discuss that update. Before that we do in database designing. Like, Create a table, insert something into the table, etc. But if we talk about the operation. Then these are the three Operation insertion, deletion and updation That's why I'm taking only these value. Let" s Start with updation anomaly In the updation anomaly, like i have written a query. Writes a simple query. Like the student 4. Student ID is 4 His name is Amrit He wants to change from amrit to amritpal. So guys we have already know. What is the query, for that i'm writing over there. Update Student, that is a table name Set attribute Name, What Is Attribute Name? S.Name, Let's take the new value like Amritpal, where Because where condition we have to apply . Where SID=2 So this is the simple query Which will change value from amrit to Amritpal. There will be no problem. But. Now note down other query If you want to change the salary of Faculty F1. From 30,000 to. 40,,000. Means Change Salary of F1 From 30,000 to 40,000, 40k. If i change the salary 40k So guys see what will happen? From 30,000 to 40,000 here Here also 30,000. to 40,000 means as many times as F1 will be repeating. the same number of times Updation query will Run. Guys, when you will run it on Oracle server or on a Microsoft sequel server Or on IDBM 2 So you won't know much about time gap, you will feel that query has run. But guys if you seen it technically, Then there were 30 to 40 here. 30 to 40 here. As many times 30,000 was written, the more times it would be 40,000. But guys if I ask how many faculty of F1? There is only one faculty of F1. Just a faculty whose ID is F1. So if there is only one faculty F1, then his salary should also change once. But due to the column level duplicacy, How many times is it changing? As many times as that data is Written here. So these are three. Insertion deletion updation anomalies And now if it is seen, what normalization says? Normalization, What Does it do to removes redundancy from here? So it has different rules, which we will see later. Basically, if you look at it, if I divide this table. A simple Solution is, If I divide this table. If I divide this table into the multiple tables. into the Multiple tables. Like SID, S. Name becomes One Table. CID, C. Name. becomes One Table. FID, F. name and salary becomes One Table. Means this can be one of the solution. I am not saying this is the only solution. This can be one of the solution. What will happen after that like, if the same question is asked again? that enter a student's information, put it directly. To insert information about a new course Put it directly in this table. There will be no problem, there will be no problem. Insert any faculty's information. Insert it easily. Similarly deletion, You are restigating a student, Removing it, Remove it. It won't effect on the courses or on the faculty. Same in the updation, You Changing the salary of F1, So what will Be the F1 here? Primary key, If His salary is, Like 30,000. if you do 40,000, how many times will it change? only one time because in this table FID will become the primary key. In this table, the CID will become the primary key. in this table. SID will become the primary key. Alright, Guys. Thank you.