Lec 12: Third Normal Form in DBMS | 3NF with example | Normalization in DBMS

so far in the series we have discussed about first normal form and second normal form right how to find out that a particular relation is in first normal form of vertical the given relation is in second normal form or not right all the rules so first NF and all they're also second NF right now still we have some drawback in second normal form so why we need third normal form because of those topics now I will tell you in this video that what are the drawbacks in second normal form and that drawbacks we will remove in third normal form and how to remove those drawbacks right first is obviously redundancy first of all in we have redundancy in the table then we remove the redundancy by first normal form then again we do second in normal form to remove the redundancy right still we have some redundancy if table or relation is in second normal form and because of that redundancy some anomalies may exist right now what kind of anomaly can be there in second in normal form right first of all we'll discuss that thing after that we will discuss with the help of that relation that a particular relation is given functional dependencies are given then how to find out that this relation is in third normal form or not right now see suppose I am taking out this relation or the stable student having these attributes of a student and only primary key is what student ID right I am considering here that frequency only one candidate key that is s ID so that would be primary key right student ID no more candidate he is there now here see this is in second normal form this relation is in second normal form now how can you say that because I haven't given any functional dependency right and in the previous video I have checked using that functional dependency right in the previous video I have told you one more important point about second and F is that if the candidate key right is having only one attribute although lucky knee we have only one candidate key suppose three candidate keys are there and all candidate fees are having only one attribute C candidate key is also having one attribute if candidate is having one attribute than that as you can say simple candidate key if it is having more than one attribute two three four then that is known as composite key right I hope you got now what is composite key now see if the kind of candidate key is having only one attribute then definitely that relation would be in second normal phone now why so because in second normal form there should be no partial dependency right and obviously it is in first normal form it should be in first NF and now by default we consider that given relational day we table would be in first NF so and by looking at this table also you can see this is in first NF now partial dependency is what if proper subset of candidate key determines non-prime attribute then only that is partial dependency right this condition is only of prime sorry partial dependency if this is the case here proper subset of candidate he this side non-prime attribute right it's not like that it is proper subset of candidate here and here we have prime attribute then also partial dependency no left side should be proper subset of candidate K right side must be non-prime attribute then only it is known as partial dependency now if only one attribute is there then obviously no proper subset is possible of this candidate if no proper subset is possible then we will never get this kind of partial dependency here so this is in second NF right but still what can be the problem in this table if your database is in second normal form that is also not considered as a good database why so because see here what you can see here the functional dependency is C using pin code I can determine state and Country right so this is the functional dependency present here and how to check that a particular functional dependency is valid or not that also we have discussed in the previous video you can check out that video sigh button right now it's a fine pin code and the pin code is not any key here no cannot candidate key here right it's if not can you get key that it is non-prime attribute right state and country are also known prime attributes non prime attributes are those which are not a part of any candidate key so here the primary Bute is only s ID because it is only the part of candidate key right now see if you want to change the pin code here right it means what you need to do here also you need to do you need to change the pin code here also you need to change the pin code and suppose because of pin code you need to change the state also so here also you need to state change here also change here also change right I am taking a small table in which I am having only three rows where we have status Ariana and I'm only changing changing the pin code of these three tuples so in all the three tuples you need to change the state as well suppose this through this situation is in five hundred rows so in all the five hundred rows you need to update this change right this is what are the redundancy now here you can see this is one redundancy right so here you can have update anomalies we have already discussed what are three anomalies insert update and delete in the previous one of the previous video right I'll provide you the link of the complete playlist of BBFS in the description box you can check out all the previous videos right so because of this redundancy we have update anomaly here although the table is in second NF still there are some redundancy still we have update anomaly if table is in second NF so to remove this one what you can do you can divide this table right in two tables another table you can make which contains state country and pin code like this this is the second table I have I have taken this dependency right and I have decomposed this table into two parts one table will contain SIDS name DOB pin code and total credit note state and country table will contain state country hand pin code right so now if you will change the pin code here at the three place you need note to change that state at the three three place right only you can change here one at one couple and here only you can change the state right so now here we have removed this redundancy redundant data although in one column obviously we have the spin code we have to link these tables using foreign key that also we'll discuss later I am just giving you a overview here so now we have removed the update anomaly now why this problem comes see now this is very important point why this problem comes obviously because of redundancy but because of redundancy what what functional dependency is there because of that it brought the problem occurs because of this functional dependency because here pin code is determining state and country and pin code as non-prime attribute state and country also known prime attribute non-prime attribute is determining non-prime attribute right so this functional dependency this type of functional dependency caused the problem here you have to remove it remove that and this functional dependency is known as transitive dependency right if transitive dependency is there in the table then that table is not in third normal form so now how can you how can you say that our relation is in third normal form if and only if first one it is in second normal form that is for sure second point is it does not contain any transitive dependency right two points are there let me write so these are two conditions for a relation to beam third normal form first one it should be second normal form you can say a relation here is in third normal form if and only if first one it is in it is in for second order form second one is there is no transitive dependency or specifically you can say there is no transitive dependency for non-prime attribute right now what is transitive dependency see if our relation relation we have a determines B and B determines C right so this is transitive dependency because if this and this is given you can say that a will determine C this is what up obviously you know this is transitive dependency right now here is also known prime attribute and C is also known prime attribute if this is a case then only you can say that in the relation we have transitive dependency present so here how can you write it if obviously the ill they'll ask you in gate or net or they will give you one relation and set of functional dependencies so if in the that set of functional dependency you find out a dependent dependency something like this a non-prime attribute to determining a non-prime attribute then this is transitive dependency it means that relation is not in third order form you have to check this only both side left hand side and right hand side both side must be non-prime attribute if any one is prime attribute then there would be problem see if this side is prime a tribute on this side this side we have known prime attribute then this will create a problem for second normal form because this is what a partial dependency if the side is a part of candidate key right subset of candidate here and this side we have non-prime attribute then this is partial dependency if this is the case then that would not be in second normal form right so obviously if not second normal form then that is not in third normal form so now here suppose here we have candidate key and here we have non-prime attribute then that is there would be no problem this is not you can say a transitive dependency and here suppose maybe you can get like this here we have a non-prime attribute and it is determining a prime a tribute may be sometimes then this is also not transitive dependency for transitive dependency you must check both side must be known prime attributes that's it right now let us take one example now let us consider this example now before that see you can also write down like this other table is in third normal form if and only if for each of its functional dependencies right and these functional dependencies should be known trivial for each of its known trivial functional dependency if any functional dependency is given something like this a B determines a right then will not consider this case for each non-trivial functional dependency at least one of the following conditions hold see there are two condition but at least one condition it's not compulsory that these two conditions must be hold in this functional dependency at least one either left hand side is super key left hand side means this one right that determinant on right hand side is prime attribute means the dependent prime at his prime attribute at least one of these conditions hold you can write down something like this or you can write down something like this for this obviously for finding the non-prime attribute or you can say for finding the super here the prime attributes you must need to find out the candidate key that is very important point that also you have discussed in one of the previous video right how to find out candidate key C ABCD closer you can take all the attribute first of all definitely it will find out all the attributes so you can say that ABCD is super key now find out candidate key try to discard the elements the attributes here CA is determining B so you can discard be a determining BB will determine C so a will determine C so you can discard C because we have a C will determine B so by transitivity property a will determine D we have a so you can discard D so we have only a so a is what super key you can check if the attribute closure of a is having all the attributes of the relation then definitely it is it is a super key you can say a will determine itself right by a flexibility proper property a will determine B we have a so I can determine B using B I can determine C using C I can determine DS all that reboots are there in the tribute closer okay so a is what can it super key now as a candidate key definitely a would be candidate E Y so because no proper subset is possible of a is only single likely having one single attribute right so this is what candidate key now we have got one candidate he so means prime attributes we have board that is one that is a now check out more entities are possible here or not in this relation how to check out this thing if the prime attribute is present at the right hand side of the functional dependencies then definitely there will be more candidate keys and we you have to find those out right but here check is prime attribute is a present on the right hand side no no no so now there is no more candidate key now we have only one candidate key that is a in this relation right and this is prime a tribute now you can check this condition this kind of dependency present here or not if yes then this relation is not in third normal form right check out check the first two dependency a is a non-prime attribute no it is prime attribute so this is what this is not we can say that this is a transitive dependency no right so this is not a transitive dependency now check out this one B yes it is non-prime attribute but only checking B you can't say that this is transitive dependency you have to check the right hand side also because there also there should be non-prime attribute C C is also non-prime attribute so this is transitive dependency see is non-prime attribute B is non-prime attribute so this is also transitive dependency so two transitive dependencies are there so this relation is not in third normal form right this is in second normal form because by default I have taken these type of functional dependencies so that this is in second NF you can check out this also because if candidate is having only one attribute then definitely that relation would be in second normal form so this is also in second hand sorry not in third but only second NF or here if you will not check like this so you can check like this check these two condition first one is left hand side is at least one condition must hold for each non-trivial functional dependency see these are known trivial functional dependencies right trivial I guess you know that if X determine Y then Y is what subset of X this is what trivial functional dependency right now see left-hand side is super he check out for this one left-hand side is super key yes right no need to check the right-hand side only one condition at least one condition must hold for the relation to be in third normal form so check out for second functional dependency left-hand side is it super key no B is not super key now check for right hand side right hand side is prime attribute no so neither this nor this one is present in this for this functional dependency so this is transitive dependency this is also because left hand side check it is super key is that super key no so now right hand side is D primate reviews the primate reboot no so neither this nor this one is holding here right now it is also transitive dependency see here why I am saying that C is not a super key obviously if you find out the C closer then what it contains it will determine C itself by reflexivity property and using the functional dependency C can determine B nothing else so attribute closer is containing only see you do not all the attributes so C cannot be a super key right now let's take a different type of example so let us take this example here we have ABCDE F attributes and these two functional dependencies are given right now first of all obviously find out the candidate key here now candidate he is this closer would contain all the attributes so this is oh definitely a super key now try to discard the attributes from here see the functional dependency a B is determining cdef right so we have a B so using a B I can determine C D F so I can discard from here right can i discard anything else B D is determining if but we don't have we have only B we don't have D right so obviously we can cannot discard now anything so now a B you can find out a be closer would also contain all the attributes so this is what a super key now find out it is a candidate key or not how to find out proper subset would be a and B check out these are super key or not hey closer and be closer a closer will determine itself hey nothing else because here both abies data-mining CD here but we have only a not be right so this is not a super here now be closer will determine B only B so this is also not a super key so you can say that a B is candidate key right now we go to one candidate key that is a B and prime attributes huh here a comma B not yet more candidate keys are there or not how to check prime attributes check out the primary boots are aware of available on the right hand side or not is a available on the right hand side know is be available on the right hand side know it means there is only one candidate key no more candidate keys are present in this relation right and prime attributes are a and B non prime attributes are cdef right now check out for this dependency is this type of type of dependency present here or not see first of all this thing is this a B is non-prime attribute no a be our prime attribute so this is what not a transitive dependency right no need to check the right hand side because if this side is not NP a so no need to check for this side now check for this dependency C B and B now here see this point you need to clear out this point to very carefully both B and B is this non-prime attribute now we will say that B is prime attribute right but B is not a prime attribute so this will consider as non-prime attribute and right hand side is F F is what it's a non-prime attribute so here also non-prime attribute here also non-prime attribute so this is violating the condition so this relation is not in third normal form right I hope you got now if you you find the mix of Prime and non-prime attribute then you will consider it what a non-prime attribute complete because b d complete is not prime attributed d is not in climb attribute so we'll consider it as non-prime attribute right so this is not in third normal form suppose I'm going to modify it a little bit suppose I have I am having a more functional dependency that is a determine D so now check for this one also suppose I am taking this one also so for this functional dependency left-hand side is non Prime attribute no it is what Prime attribute is what crime attribute right so no need to check the right-hand side this is not violating that property this is what we can we cannot say that this is a transitive dependency right here only which dependency is violating the condition because of this dependency this relation is not in third normal form B not because of this dependency right make this thing very clear that both sides should be non-prime attribute non-prime attribute and this a is prime attribute so no need to check this one or you can check this one left hand side is super key guess a is super key so no need to check the right-hand side right so now see if this other case if this is the case then check for the second normal form here because this relation is now in not in second in normal form right C is determining B is what candidate he is a B or you can say primary key is also even a proper subset of candidate he is a and this is determining unknown prime attributed D is what a non-prime attribute so this is a case of partial dependency here this is partial dependency so if this is a case then this relation is not in second in normal form so definitely it cannot be in third normal form right now here obviously I'm not taking this one now check this is partial dependency or not see BD is it a proper subset of candidate key candidate is a B proper subset of candidate key one is a one is B although we have B here but we have des also this complete should be a proper subset of candidate here so this is not a proper subset of candidate he right so this is not a partial dependency right so this is in second a normal form but not in third normal form because this is transitive dependency I hope now you got all the points about this so now how you can say that third and F is better than second NF in two and if we have one anomaly that is update anomaly because of some redundancy there right that is drawback so now third and F will remove all the anomalies insert delete as well as update anomaly so now you can say that this that is why we say that this third normal form is adequate normal form for your database means if your database is in third and if then you can say that it is a good database design although we have more normal forms are there bcnf fourth fifth be KNF that also will discuss one by one later now I hope you got what is third normal form and how to find out that particular relation is in third NF or not now this is question for you this is the relation these are functional dependencies and you need to check out is this relation is in second normal form as well as in third normal form or not right you need to tell me fine now if you want me to discuss more questions on these normal form you can mail me your questions on my mail id or you can just tell me in the comment box I will discuss some more questions also so now how to convert a given relation in into third NF third normal form that thing we will discuss later after discussing the decomposition process means lossless and or say decomposition right in the next video I'll discuss what is bcnf and after that I will discuss about a decomposition process so now I'll see in the next video till then bye-bye take care

Channel: Jenny's Lectures CS IT

Views: 244,811

Keywords: normalization in dbms, gate cs lectures, jennys lectures, dbms tutorials for beginners, third normal form in dbms, third normal form in dbms in hindi, third normal form in dbms with example, normal forms, first normal form, third normal form database, normal forms in database, third normal form example, second normal form, dbms - third normal form (3nf), third normal form (3nf) database normalization dbms, 3rd normal form, third normal form dbms, bcnf normal form in dbms

Id: R2Z-DgZ6QFQ

Length: 23min 45sec (1425 seconds)

Published: Sat Dec 14 2019