Transcript for:
Summary of All Normal Forms

Dear students, welcome to Gate Smashers In this video, i am going to explain the summary of All Normal Form. In which we will talk about first, second, third, BCNF, fourth and fifth normal form. So let me tell you that i have already created the details video of all Normal forms separately. Their link is given in the description box. But my main motive to giving this summary to explain you with the real life examples, that what is the scenario in every single Normal form. Because when you are going for the exam, you need summary videos instead of the details, man. I get it quickly, that what happens in every normal form. And i am giving you the guarantee that after listening to this video Whether you want go for the interview, for the competitive exam, College, university exams or preparing for any exam, This video will help you a lot definitely, so guys quickly like the video. Subscribe to my channel, if you haven't yet. And even if you do, you can subscribe to more devices. Even i am giving you the task that those who are watching this video, at least one subscriber You have to bring today and if you do it. then write "done" in the comment section so that I get the acknowledgement. So let's go. Let's start with the first Normal form So what is the condition of the first normal form? No Multi Valued Attribute i.e. There should be Atomic valued or single valued attribute. Many times asked in theory in this way. that means you've been given a table. In that table let's say there is a roll number The roll number has given 1 And the course has given that he is studying the course C, C++ Or roll number 2 is studying C++, JAVA. Means in this way if you have multi-valued in one column That means if that guy is studying more than one course. If you write this way by putting comma Then it will be not in the First Normal Form. So what does the first normal form says? Every attribute must be atomic It should be atomic, You should put only one value in it. Now it will come to your mind that sir, if someone is studying more than one courses, then what we will do? It has multiple ways, either you make different columns. Or you write C in first line and below in the second line write C++, then in the third line, in this way write in the different lines. Or you make two different tables, Roll number and course in one and in the other one, you can keep separately, so what does it mean to say? What is Remedy? I've already told that in the detailed video, but I'm giving you a quick review here. What's the point in every normal form? So, no multi-valued attribute. So there should be only atomic value or single value attributes. So first of all, which normalization we applied? We first apply the first normal form. Then comes second normal form in which maximum students remains confused. So the condition in the second normal form is that, firstly table should be in first normal form. After that there should no partial dependency. What should not be, partial dependency. so that mean, what should be? There should be full dependency, let me tell you the example A real life example, let's say AB together determing some C, What is AB, It is a candidate key, it is unique. so that some C, And what is C here? It is non-unique, so means to say that a unique person determining to a non-unique. So, it will do. Brother, there is a repetition in it, so how will we determine who we are talking about? We'll look at AB and it will tell, it's different and it's different. But if we talk about partial dependency in this, them let's say, It is a company. There are two founders in the company A and B. A and B together approaching to some C, that brother you do finance, we will do this, we will do that, it's our startup. And let's say, If in this situation, B go alone and approach to C, that brother leave A, You give finance to me, I will give you this, and I will do that. So what happened here, it went wrong. Why it went wrong. If AB together determining the C then Only B or only A without their help, could not determine alone. If in your table, dependency is given And if in this the way given then it called called partial dependency. So what does full dependency say? If ab is a candidate key and that is determining the C Then only A or only B couldn't determind alone. If only A or only B is the candidate key then there is no problem. But we have already told that AB together is the candidate key. That's why always remember. Whenever it comes in exam in this way, then remember in the case of partial dependency, that if any proper subset of the left hand side, proper subset, determine some non-unique, then it is a mess. There is a problem in this It's patial dependency. To remove it we uses the full dependency. Then comes the third normal form. What is the condition of the third normal form? It should be in second. plus, there should not be transitive dependency, then what is transitive dependency? X determining Y and Y determining Z Looks like there is no problem in it. But see, what is the scenario? X here is, Let's say, a primary key or the candidate key means unique attribute. That's determining some Y which is a non-prime attribute or you can say a non-unique attribute So there's nothing wrong, there is no problem. It can do. Obviously for non-unique, we will take the help of a unique. It is determinding, there's no problem till here. But where does the problem start? Z is also there which is also a non-prime. What is Z, it is non-prime or non-unique kind of a tribute. Y is determining Z. Now here the mess started. Why? X is determining Z, How? Transitively, X determining Y and Y is determining Z. And because of this, X is also determining Z. Well, there is no problem. X can determine Z but it does not do directly. It is doing the through Y and Y itself is non-unique. What is Y itself? it is non-unique, so because of this, problem is being created of. Transitive dependency. If you want to understand it from real life example then Let's say, there are three persons in the class X is the class topper. And next to his is Y, his friend who is very useless. Okay, so X is saying to Y thay don't take tension, I will get you pass in the exam. So Y says, "Okay, there's no problem, i get tension-free." But the problems came that Y has a friend ahead Z. And Y is showing off the Z that, Don't take tension, brother, I'll get you pass in the exam. So what's the problem here? actully X was Y's friend X is helping Y. But Y is showing off itself and telling Z not to take tension. I will get you pass. So because of it, what is the problem is that the X was the prime attributes, unique. He's just helping Y but now X is helping Z too But through whom? Through Y Yes, if Y was prime, if Y was intelligent too, then there was no problem. But what is Y itself? It's non-unique, there is a problem. Sometiimes it is given that No non prime should determine non Prime. Let's say, if you remove no from in this case, i.e. non-prime defined non-prime, means it is not in the third normal form. But if No non-prime should determine non-prime. Then there is no problem. Then in that case, you can say that it will be in the third Normal form. Then comes to BCNF, What does BCNF says that it should be in the third form, plus Left hand side of every candidate key Means to say that on the left hand side, any dependencies is given, like X goes to Y. So whatever is on the left side will be, either it should be candidate key or it should be super key. Meas to say, The right-hand side's attribute should always be the determined by the candidate key or by the super key. If it is in all the dependencies then it is in BCNF, means it will be in Boyce codd normal form. So till here your story maximally ends, because till here your maximum redundancy gets removed. It gets completely removed. But after that, a special form comes, fourth normal form. What is Fourth Normal form says that multi-valued. firstly they are saying that it should be in BCNF plus. There should not be multi-valued dependency What was here multi-valued attribute, so you see the difference here. We are talking about there should not be multi-valued dependency. Now what does this multi-valued dependency mean? Lets say, Let's say, If I take my own example, Varun. Now Next to Varun, there are three mobile numbers, three phone numbers. and what is next to it, let's say three email IDs. If you have to store this kind of data. So how will you do? let's say Varun, He has three mobile numbers and three emails. If you store it in the table, how will you do it? you will do it in this way, Varun Mobile number 1, Email 1. Then, Varun, Mobile 1, Email 2, then, Varun, Mobile 1, Email 3. In this way again Mobile 2. Email 1, Mobile 2 Email 2, Mobile 2..... So How many rows will be made? Nine rows will made. Person is only one and You have to make so much rows and what's the reason for that? The reason for this is that this name varun here is the multi-valued dependent. on Phone number and email. Number and email have nothing to do with it. So when these two have no relationship between them, mobile and email. So you make different tables In a table, You store varun and mobile number. In another table you store Varun and the email id, just to Reduce the multi-valued dependency. So actually this is the story of multi-valued dependency. When corresponding to one, there are multiple values in multiple attribute. So because of it So much repetition happen So we can reduce it. If you want to create a key Then also you can't make it, because if you make it then there are number of values corresponding to one. so it is difficult to manage them, so that's why decompose them in separate tables. The finally come to the fifth normal form. It says that there should be fourth normal form, plus, Lossless decompostion should be also there means Lossy decomposition should not be there. Joint dependency shouldn't be there. It has to be lossless. What is the meaning of lossless? Let's say There is table, we divided the table into two parts. Okay, you divided that table into two parts some tribute are here. Some tributes are there. Means when you divided them, you divide it vertically, not horizontally. You divided it Vertically means Some of the columns came here and some of column goes there. Rows are as it is, number of rows remained same. When you combine these tables again. There might be a problem of spurious tuples. means extra tuples might be there. To save those extra tuples, that we called as lossy decomposition. If you want to save them or you can say that to create a lossless decomposition. What you do, when you are dividing the big tables into two tables. What's common in that, isn't it? the common attribute there. common attribute must be a candidate key. What should be the common attribute, must be candidate key. Either of this table or of this table or of the main table. If the candidate key has, then there is no problem, no extra tuple will be there. So this is all about the all normal form. I have told you its baisc summary. The rest of the details story, their link is in the description box. You can check it their. Thank you.