DBMS Module 4 Important Questions | ONE SHOT VIDEO FOR EXAM | VTU 5th sem| DBMS |PYQS #21cs53 #dbms

in this video I'll be discussing all the important topics of fourth module uh if you have watched my previous video I have uploaded all the module wise important questions so as an extension to that I'll be discussing answers of fourth module here so first topic we have is functional dependencies and interference rules so functional dependency is a constraint between two sets of attributes from a given database so let's consider a relation r a set of attributes X in relation is set to be functionally determine another attribute Y which is also in the same relation if and only if each value of x is associated with atmost one value of y so which means that if x is the determinant and Y is the dependent atmost one value of y should be associated with x the determinant X determines y so this is what functional dependency basically means so it is a property of sematics and meaning of attributes so by using functional dependencies we can get to know the meaning of that particular attributes so this would be helpful in the normalization topic which we would be discussing in the further further so discussing about inference rules there are six inference rules majorly so we need to write the inference rules in same order so i1 inference rule one states that it is that it is reflexive rule it states if x is a subset of Y or X covers y then we can say that X also determines y then we have argumentation rule which says if x determines Y and let's take an attribute Z such that x z should determine y z so group of attributes here let's take take a determines B so group of attributes in a are X Z should determine group of attributes on b y z so Y and Zed are um associated with a or are associated with X and Zed so that's what it means then we have a transition rule if x determines Y and Y determine Z then we can write X determine Z directly then we have decomposition rule X determines y z that means X can determine Y and X determine Zed also then we have Union if we Union X determine Y and X determine Zed in order to you need to have a unique or sorry you need to have a a common attribute here here the common attribute is X so you can you can combine them in such a way that X determines y z then the sixth rule we have here is pseudo transitivity which says if x determines Y and Y Z determines y w y determines Zed then we can write w and X determines Zed so this is about Soo transitivity these inference rules would be used in order to calculate the closure find the prime attributes and uh in the further questions we'll discuss here we have the the problems based on functional dependencies closure and identifying the candidate key Prime attributes and non- Prime attributes so starting from the first question we have a relation a b c and the functional dependencies U are given as follows so a determines b b determines C and C determines Z so our first step would be to find closure of all attributes so here attributes we have a BC right so let's start finding closure closure is depicted as a with plus notation on the top so firstly a any closure closure of any attribute would have itself it will consider itself first so we'll write a here followed by that we need to check if a determines any other attribute so here a determines B right so we can include B also next check if uh B determines any attribute B determines C check if C determines any attribute C determines D so a closure is a b c and d now moving on to B closure so first we need to write B and then B determines anything check in the functional dependencies we have B determines C so I'll write C next after we have C here so now check if C determines anything C determines D so I'll write D also so after that D determines anything no it's not there in the functional dependencies next we'll write C closure C closure determines C firstly and then C determines D so I'll write D here then de closure I'll write de closure determines D first and then check if D determines any um any other attribute no there's no functional dependency for D here so yeah these are the closures of all the attributes our first step is done our second step is to check which attribute or which closure determines all the attributes comprises of all the attributes so here if you check here D is also there because D is included here right a b CD so uh if you check at A+ a closure we can state that a closure as a closure determines all attributes we can say that it is the candidate key a closure we'll write a here candidate key is a so understanding the definition of candidate key so it is it could be one attribute or a set of attributes within which within a relation that uniquely identify each tle or the row here so a plus a closure uniquely identifies a b c d right so a is a determines b b determines C and C determines D so candidate key here is a so next we have Prime attributes so all those um Keys which are um which help to which help to uniquely identify the other attributes like candidate key here right so all those which come under candidate key are considered as Prime attributes so we have a here and non-prime attributes are those which do not fall under the accepting the prime attributes whichever is left out write it as non- Prime attributes so b c and d so this was about our first problem looking at the second problem relation has a b c d four attributes and functional dependencies are given here we need to find the closure Prime attribute and non- Prime attribute so our first step is to find closure of all attributes so let's start from a closure a closure here I have initially I'll write a and check if a determines anything a determines B now I'll check if B determines anything B determines c c determines anything d d determines a so now that because I have written a here I can't write it again so it's the same so a b c d is the closure for a next writing closure for B firstly I'll write B and check if B determines anything B determines C so I'll write C here C determines D and D determines a right so I should write a also C closure I'll write C first and check if C determines anything C determines d d determines a a determines B and B determines C so I need not write C again next I'll find D closure D determines first D I'll write and check D determines anything D determines a a determines b b determines C and C determines D so I need not write D again so I have found closure of all the attributes now I'll check if I'll check which attribute determines all other attributes so a a attribute determines all other attributes B also determines the same C also determines the same and D also determines all the attributes present in the relation so I can say candidate key here are a b c and d so Prime attributes are the same write it as it is and non- Prime attributes are those attributes um excluding the prime attributes so excluding a BC D do you have anything in the relation no so it's null so this was about second question um third question are the relation as a b c d e f i j and then the functional dependencies here are a determines C A determines D B determines f f determines GH and D determines IG so here um in the previous questions what we did we found closure of all the attributes right because um it was easy here there were only four attributes and it was easy to find closure but then now here we have a to J it'll become lengthy so the one more step or a shortcut for that is we'll WR we look into the rhs part so firstly we look into the look into rhs part of functional dependency write down as it is what is being determined so on the rhs part what do we have C followed by that we have d and e then we have F here and then we have GH here and then here I have I and G so in order to obtain a in order to obtain a candidate key it has to cover all the attributes right it has to determine all the attributes so if you check I have CDE E F G H I so what are missing a is missing B is missing right so I'll write whatever is missing here initially okay now I'll I'll take closure of a and check if it determines all these so if I take a closure firstly I need to write a and then I'll check what if a determines anything B determines anything and combination of A and B determines anything so first a determines a determines d and e next I have b b determines anything B determines F so a done B done now I'll check the combination a a determines c c I have written now these two are done now let's move to d d determines anything D determines I and J okay and then de determines e determines anything no combination of D no F determines anything F determines GH so I'll write GH here C determines anything no I determines anything no J also no G no h no so I have written um all the possible closures for a all the possible closures for ab so I can say that as AB closure gives or determines all attributes it can be considered as candidate key so whatever is remaining so Prime attributes would also be the same a b and whatever is remaining non- Prime attributes would be those so a is done so c d e f g h i and G would be the non-prime attributes so this was about the third question now let's look into the fourth question the relations are given here A B CDE e and then the functional dependencies are also specified here so let's use the shortcut method so shortcut is look into the rhs part and write down whatever is being determined so here we have b d c and a so a b c d is covered e is missing so I'll write e here and then I'll try taking e closure and check if e closure determines all these so like how we did in previous question let's check e closure e closure e e e closure determines e first then C first then check if EC combination is there no check if C determines anything no so e closure is not possible now let's start with a b c d e now let's Club e closure with a b c and d and check now I'll write a e closure so for a e closure first I'll write a and e as it is then I'll check for a for a determines anything a determines B and then e determines anything e determines C and then AE determines anything no check if B determines anything no C determines anything no combination of BC BC determines D so I have got a b c d e all the attributes in the relation so this is one candidate key e closure now I'll replace it with b and e closure I'll check for b e closure so first I'll write b as it is and then b b determines anything no e determines C right now I can take combination of B and C so BC determines D I can write that now C determines anything no D determines anything a d determines a so I've got a b c d and e all the attributes in the relation so I can consider this also as a candidate key next I'll take C closure okay I'll take C closure and check so I should write c and e as it is e determines C and C combination is there no so this can't be considered as you can't write anything further right so you can't consider this now I'll take de closure so for de closure let's write d and e as it is next e determines c d determines a and a determines B so this is also candidate key so I can write that uh candidate key is equal to AE be and c and e so the prime attributes here would be a b c and e the ones which are not a part of prime attributes are considered as non-prime attributes so that is only C now let's look into the last question that is um the relations are given a b CDE e and the functional dependencies are specified here so you can solve this either by the first method or the shortcut which I've told so you have five variable five attributes here so instead of writing uh you'll write a b CDE along with that you need to consider AB U pair and then BC pair and write it further so instead of that we can look into the shortcut so that is looking to rhs write down whatever is being derived so AB derives C and then BC derives d d derives E and F derives B so what is missing here a is missing here and F is missing here so let me write a here and next I'll try finding closure for a f so a closure is first a I'll write F I'll write and then a derives anything I'll check no F derives anything F derives B so when a b if I take combination of a b it derives C right next if I take combination of B and C it derives D next if I take D closure D derives e so I've got a b c d e and f also so I'm done with AF AF is one candidate key now I'll check for BF so let's see BF determines uh all the attributes or not so BF I'll write first B and f as it is and then I'll check if B determines anything B determines b b does not determine anything F determines anything F determines B itself so this can't be considered next I have CF I'll take CF so CF if I write C determines anything no F determines anything B so I can't consider this also next I'll take DF closure I'll write d and f d determines anything e f determines anything B try taking combination of any these any of these and check if any of them would determine anything in the functional dependencies no so I can say that finally only the candidate key which I have obtained is AF so I have checked for all others also so I'm I'm not able to find um the combinational candidate Keys except AF so a AF are the candidate keys so Prime attributes here would be a and F and non-prime attributes would be except these I have b c d and e so I'm done with solving questions on finding closure and finding closure primary Prime attributes non- Prime attributes and candidate so these questions were taken from previous year paper so I hope this concept is clear moving on to the next topic we have to write so there'll be a that there there'll be a question on finding this closure and this problem along with that they can ask the the algorithm for closure also so understanding the algorithm in pretty simpler terms so we are determining X+ X+ is closure so closure of X under F so input is a set of functional dependencies on relational schema R so R was given here and then functional dependencies were specified here so firstly any closure closure of X would be X itself that's what we did right first e closure means first we wrote e and then we checked if um it e D anything else further or not so this needs to be repeated until and unless we obtained X itself so you started with this and you started finding if it derives anything and you kept on going until and unless until you reach this point you need to keep on repeating this next here we have for each functional dependency y determining Zed in F do so if x covers or X is a subset of Y then I can write exclosure is equal to exclosure Union Zed because Z can also be included so here let's take an example a determines b b determines C and C determines okay a determines b b determines C so I can write if I write a closure I'll write a first and then um b b is being determined by a right so I'll write B also and here the condition says that there's a new variable there's there's a new attribute C such that I can write a closure Union C because C is being derived or determined by B and B is being determined by a so I can write a b and c for a closure that's what is explained here until you keep you keep on repeating this until you encounter the a itself at the end so that was a pretty simple explanation of determining the closure algorithm so you can write it in your own words next moving on to the minimal cover so before moving on to the minimal cover algorithm let's solve the problems first so once you solve the problems you'll get to know how to uh you you'll get to know the steps and it'll be easy to understand the algorithm so here um they have asked to find the minimal cover for the following functional dependency here the functional dependency is given my first step would be to look into the rhs part and decomposition rule needs to be applied here that is split so I'll look into the functional dependency first um I have a determines C so rhs you can't split it right C you can't split it further D also you can't split it further but then a DF can be split further decomposed further such that e e determines a e determines d and e determines F we have seen that in the inference rules here right so here initially I need to remove a determine C assume that we have removed a determine C now write a closure if you write a closure you'll get a first and then check if a derives anything except this if you check there is no other combination or no other possibility of functional dependency which would which would derive C right so we can say that this particular functional dependency is not redundant so we need to keep it our aim in the second step is to remove the Redundant dependencies simple if if you try understanding this you might get confused but then in simpler terms I'm saying that you should find closure of all these by assuming that you have removed if you obtain the rhs part then it is redundant remove it off if not keep it so for AC plus AC closure if I write right I can get a c and then close this AC C determines D close it a closure you have anything here no a closure is c a determines c c is already written so you can't consider it C closure anything no so you have obtained only a and C so um a determines D is unique one it is not redundant not redundant so keep it now let's look into e determines a so assume that you have removed e Min C now write e closure so if you write e closure you'll get e and then D and then F now D closure if you see no F closure there's nothing F determines nothing D determines nothing e e and any of these combinations no so you can state that e determines a is unique it is not redundant so keep it as it is next e determine say is R this is keep it keep it keep it now e determines a or D sorry e closure close this find e closure we have e and then e determines a a determines F check a determines anything a determines c and d and if F determines anything no any of these combinations you have AC AC determines d d I have written already so here e determines D was the functional dependency which I took and which I assumed to be uh which I initially removed it and checked if e CL osure I would get D or not and when I checked I got D here that means this is not unique one there are other ways of deriving uh D that means this is redundant so remove it remove this now I have e determines F finding e closure I have e close this check e determines anything A and D let's write a and d there's there's no attribute which would determine F which I wanted on the rhs part so it is not redundant keep it so I'm done with all the functional dependencies right so I'll write the updated list now so it would be a determines C AC determines d e determines a e determines F so next I'm done with the second of KN the Redundant dependencies now my third step is to look look into the LHS part remove redundant dependencies the basic idea of minimal cover is that you're eliminating all the Redundant ones redundant dependencies okay so looking at the LHS part I have AC so firstly I'll decompose it and then remove the red redundant dependency so AC could be written as it can be written as a determines d c determines d right so again I'll find a closure and C closure I assume I I'll close this and check now a determines C A firstly and then C and then AC determines D and and D determines anything C determines anything no so here a determines D and when I found a closure assuming that I've removed this I got D itself which means that this is redundant so I need to remove this C closure find close this C closure first write c c determines anything no so this is unique one so keep it so again I'll write the updated functional dependency so I have a determines C and out of AC determines D I need to keep only C determines D and then e determines a and then e determines F now this is the functional dependency final minimal cover or you can Club the common ones as we have two e here right so you can club that and a determines c c determines D so a determines d a determines c d sorry and E determines a and F so this was about finding minimal cover so looking at the next example we have consider a relation R um which has attributes a c d e and H stating satisfy the following functional dependency the functional dependency is given here so find the canonical cover for this set of functional dependency my first step is to look into rhs part decompose it if necessary so rhs part I have ad here so I'll write I'll decompose it and write a determines c as it is AC determines d e determines A and E determines d e determines H okay next step is removing the Redundant dependencies so firstly I'll consider a determines C and write a closure close this and write a closure you get a a determines anything further no so this is unique I not obtained C here so this is unique one or it is not redundant so I'll keep this next I have AC determines D so I'll write AC closure so I'll write a and C first a determines c c is there already and U no that's it C determines anything no a determines anything no so this is also again unique because I have not obtained the D here so it is unique or not redundant th keep it next coming to e determines a unique unique unique I I'll check this e closure I write e a a determines c e closure is a and a determines C so a determines C for u e determines a I have e closure close this and check um e closure I have e determines e firstly and then e determines D I have and E determines H I have I've not obtained e determines a right so this is unique or not redundant keep it as it is this is also fine next check for Eed determines D so e closure again close this write e determines anything A and H E A and H now check if uh e determines anything further I've written a determines anything C A determines C right and check if H determines anything no h doesn't determine anything now check if any of these combinations determine anything so AC is there here AC combination I get D here and d e determines D here so that means it is not this particular functional dependency is not redundant because without this also I can obtain D so I I need to remove it next I have e determines H so e closure assuming that I've removed this I get e a d and a determines C AC determines D so I'll write e a d c a d c and d same so I have not obtained H here so this is unique or not redundant keep it so I have removed only e determines D so this is the Redundant one so I'll write functional dependency updated one that is a determines C AC determines d e determines A and E determines F so e determines H sorry this is similar to the previous question I had found it in I had found this question in the previous year paper so I wrote it so third step look into LHS part decompose it and then remove the Redundant ones so here I have AC determines D so I can write for this I can split it as a determines D and C determines D now a closure I'll write C closure I'll write a closure without this would be um a determines C and AC determines D so without this also I have obtained D so this is redundant I need to remove this this is not if if this is redundant this won't be redundant right so I'll keep this so finally I can write fds a determines c c determines d e determines a e determines H so this is the final answer so now that we have understood how to solve the questions for minimal cover or canonical cover we look into the algorithm so understanding the algorithm so finding minimal cover f for a set F function for a set of functional dependencies e so e is the functional dependency here input is a set of functional dependencies e first set f is equal to e First Take minimal cover is e itself the same functional dependency the entire thing okay next we'll replace each functional dependency X in which X determines A1 A2 A3 and so on in F by n functional dependency such that X determines A1 X determines A2 and so on so I I'll check if without this without X determines a if I determine a itself that's what we did here right without D without this I'm checking if e determines D is obtained here and if it is obtained here it is not redundant remove it so removal of extra attributes B from the particular the temporary functional dependency which I've taken the set which I've taken next I'll check in F also Same by removing that if I'm getting the equivalent one or not if I obtain the equivalent one then fine if not if I obtain the equivalent one then remove it if not then keep it because it is unique one remove of redundant dependency x x determines a from F star so this was about minimal cover algorithm you can write this in simpler terms also so we are done with closure minimal minimal cover now we'll move on to equivalence of functional dependency equivalence topic is pretty easy here again they can ask the algorithm and give us a numerical or a problem on um equivalence so the algorithm says that two functional dependencies are taken um X and Y okay two functional dependencies are given X and Y we need to take U we need to check if x covers y if x covers y then we need to consider functional dependency of Y and find closure using X functional dependencies if it determines then X covers y opposite to that y covers X consider each functional dependency of X and find closure using Y functional dependencies if it determines then y covers X if these two are true then um X and Y are said to be equalent so let's let's look into the problem you'll understand it better so firstly I'll consider X CS y so if x cover Y is given then first I'll write consider Y is equal to a a detes b b detes c and a determines C so now I'll take um I have a determines B and B determines C right so A and B A and B are there in X so I'll write closure here by considering this okay so a closure here I'll get a a determines b b determines C and C determines no C doesn't determine anything so a b c and on the rhs of X I have B and C B and C is obtained here so X is X covers Y is true looking into B closure I have B closure is B firstly and then C right B closure is B and C here B determines c b determines C so this is again X covers y these two are true now I'll check for y covers X so for this I'll consider X so I'll write x a determines B and then B determines C I'll take take a closure here also A and B is there right so I'll take a closure now a closure is a B and B determines C so this is accepted a determines B was required and I've got a determines B right so this is fine next I have B closure so for that b would be there first and B determines C is there so this is also accepted here I can write y determines X so now that X determines y oh X is a x covers Y and Y covers X I can write X and Y are equivalent looking into the next question here we have so given below our two sets of functional dependencies for the relation R so these are the attributes we need to check if they are equivalent so I'll take this as X and I'll take this as y these functional dependencies as y so first condition I should take X covers Y and check if this is true or not so for this find y using X so I'll write y for y I have a determines BC and D determines a e now I have a a d and d here right so I'll write um find why I need to find a and d closures using X so a closure and D closure I want these from X so I'll check if I for a closure if I get BC from X I'll consider I'll see so a closure is a I have written and from X if I consider a determines b and a combination determines C so B and C I wanted BC I have got BC fine next D closure I should get AE so I'll write D closure as D first and D determines AC and a determines B and D determines C here I have d right so D determines e so here I wanted a e and here I have obtained A and E along with other attributes so fine this is also true so I I can say this is true X covers y next I'll take y covers X that is find X using Y so I'll write x a determines B A determines c d determines AC and D determines e now I'll check here I have a and d right so a a closure D and D so I'll write a closure first I'll get a I want B b b here so a closure is a a derives BC I wanted B I've obtained B so this is correct next I'll take AB B closure a I should get C now let's check if I get C A derives BC a I'll write next a deres a determines BC B is there C right next I'll check um D determines AE so here I wanted C here C is there this is also fine so I can say that why okay this is done this is done now I'll check for d d closure if I right I should get AC e let's check if I get AC from um from y so D closure is D first for D I get a e and a determines B and C I wanted a c e i I've got a e c so considering this entire thing I can say y covers X so considering these two I'll say that X and Y are equivalent so here we are done with the equ Ence topic informal guidelines which is a very important question so in this only I've explained the anomalies different types of anomalies so the first guideline says that sematics in a relational attribute in relational attributes must be clear that is the guideline states that whenever uh we group attributes to form a relation each attribute should represent some relationship instance or should have a proper meaning so attributes of different schema must not be mixed in the same relation and entity and relation must be kept separate understanding this first guideline with an example here I have look at the example I have employee so in employee entity I have eame SSN B date address and D number as the attributes which is fine all these are related to employe and they have some sort of meaning right next coming to employee details I have e name SSN B date address D number so far it was fine but then d project number the department project number is not necessary here so it is not property of employee details so this is a wrong design that's what the first guideline says so we need to avoid such situations so this was about first guideline moving on to the second guideline redundant redundant info in tles must be reduced so info stored redundantly leads to wastage of storage leads to update anomalies what are update anomalies those situations which need to be avoided while designing u a database that is uh we have three types of anomalies here insert anomaly modify and delete so guideline 2 States that designing a schema design a schema that does does not suffer insert modified delete anomalies if there are any then note them so that applications can be made to take them in account so firstly I we'll understand what insert modification and deletion anomalies are so insertion anomaly states that to enter details of employee here so details of his Department also must be entered so if you want to enter an employee name you should also enter the department um details but it is difficult to enter details of dep Department with no employee in it so if you want to enter enter the department details you but then you don't know the employee name it'll become difficult right so it results in null values at that time for employee it be null for Department there will be some value so that that will lead to insertion anomaly in modification anomaly if one value of attribute suppose um manager assist of department is changed then value should um then that value should be updated for all employe T in same department so if this is changed then uh the employees who work under this particular manager in those rows also the values need to be changed or modified that's what modification anomally States then we have deletion if you delete the last employee of Department then the details of that employee also no longer exist in that database it's fine so far it's fine but then in those details we have department number Department name also so in other tables those values will will get deleted so that again leads to a problem right so that's what um deletion anomally States so we while designing a schema we need to make sure that all these three are avoided so yeah this is the table this table is a result of join of employe and department so joints joints of Base relations lead to update anomalies so this was about second guideline we have third guideline here uh which says that uh we need to reduce null values in the tles so null values can depict that at present the value is unknown but may be known in future or maybe not uh may not be applicable to the tle at moment or the value is known known to exist but unavailable so now value leads to problems like confusion in count and sum operation so the guideline states that relations should be designed in such that their tles will not have um will have fewer null values as as far as POS possible so attributes with null frequently could be placed in separate relations with primary again to link that next the last anomaly we have here is generation of spous tles spous tles are um unwanted tles which are generated right so um they they should be avoided at any moment so the guideline says that no spous stle should be generated by doing natural join so relation should be designed to S satisfy lossless join condition we'll discuss what Los lossless join is there lossless loin lossless join is in the further questions so spia stles occur when join keys are not primary key super key combination we'll also look into this so this was about the four informal guidelines so you need to write all these four and explain it with a proper example example now we look we'll look into normalization topic so normalization is the process of organizing data in database so we try to reduce the data redundancy so normalization is necessary to remove uh it is necessary to remove the redundancy else it can cause anomalies if anomalies Ur then it makes difficult for the database administrator to maintain it and even for the users to execute the queries it takes a longer duration or it becomes complex so we'll start with uh one NF uh we'll start with 1 NF 1 NF stands for first normal form so it states that domain of an attribute must contain only single indivisible values and that and the value of any attribute in Tuple must be single value from domain of that attribute so it disallows relation with relation within relation or relations as attribute values within relations so only attribute values must must be permitted and they need to be Atomic one NF means you should remember that it is atomic so consider this example product ID color and price so color there are three attributes here two and one values two values three values two values and one value in the color so to this is one NF redundancy right in order to reduce that you'll write 1 R56 1 y 56 1 b56 separately you you'll form this table such a way that there are only Atomic values in all the attributes once you have obtained this um the same values are being repeated right 56 56 56 in order to avoid this what you can do is you can CL product ID product ID and color once and then color and and then price and product ID such that 1 2 three prices here and product IDs and colors so here um this would be so here product ID color and price have been reduced or decomposed to two relations R1 such that product ID and color relation to product ID and price so this was the example for 1 NF to normalize the relation in into 1 NF we need to remove nested relation into new relation and propagate primary key into it so these are the primary Keys here product ID and product ID the primary key of new relation will combine with the partially will combine part with the primary key of original relation yes these two primary Keys could be used moving on to the 2nf looking into 2nf definition so 2nf is based on the concept of full functional dependency so before understanding 2nf uh we need to look into the requirements so a relation is set to be in 2nf if the database is in 1 NF firstly and all non-key attributes in the table must be functionally dependent on the primary key that is the complete dependence full dependence fully functionally dependent so every non-prime attribute should be fully functionally dependent on Prime attribute so if x determines a holds then there should not be any proper subset of subset y of X for which y determines a should also hold true so in 2 andf decomposition if data is fully functionally dependent only a part of primary key then um then move that item then move that item or the data item and part of primary key to a new table if other data item are functionally functionally dependent on same part of key then place them in separate table so instead of reading this we can explain this with an example so this is the example so here I have employe project so SSN primary p number r e name p name and P location so here firstly I can say that SSN e name is functionally dependent on SSN so fd3 States fd3 sorry fd1 states that SS p number and rs are functionally dependent on SSN okay F fd2 states that fd2 states that eame is functionally dependent on SSN and then fd3 says that P location P name are functionally dependent on p number so here we can see that these are dependent on non key attributes right in that case we need to split that so when you do 2 andf the one on which they are functionally dependent would be written in Separate Tables so p number and R were dependent on SSN so separate it you'll get one table then e name was dependent on S separate you have written it in a separate table P location P name were dependent on a non key attribute which was p p number is also a key attribute sorry so they have written it here again so these are fully functionally dependent on the uh primary Keys here so this was about 2 NF looking into 3nf we have for 3nf there are two conditions again so it has to be 2nf firstly and no transitive transitive functional dependency so all non primary key attributes in a relation must be functionally dependent on candidate key that is no interdependencies among non- primary key attribute so we'll understand what non-transitive functional dependency is if x x x determines Y and X determines Zed and X determines Y and Zed is a subset of X then X determines z and y determine Z so understanding this with an example we have emplo Department here so e name SSN B date D number D name and D manager are the attributes here so D manager D manager is dependent on SSN so is transitive functional dependency because SS D number is dependent on SSN and D number is dependent on D manager SN here D number is neither a primary key attribute nor a subset of employee department so we have we encounter transitive functional dependency to overcome this we need to do 3nf normalization so for that we decompose it set up relation that includes non primary key attributes that are F functionally dependent that functionally determine other non- primary key attributes so here this was the condition right so on SSN e name and department number and by dependent and on D number D manager and D Name by dependent here D number is not primary key not primary key attribute so that is uh that results to transitive functional dependency so for that we need to decompose it so when we decompose we write e name SSN B and D number in one table and then D number D name and D manager in another table now if you check the second table D number is the primary key here so these two are dependent on the primary attributes so there is no problem of 3 NF here transitive transitive functional dependency problem is avoided here next we have bcnf that is stricter than 3nf so it is Boys called normal form it is Advanced version of 3nf 3.5 NF so the two conditions here are it should be in 3nf form for every functional dependency X determines y x should be a Super Key in the relation so LHS part should be a Super Key always in bcnf condition so looking at the example here student ID is given course is given teacher is given so so student ID course so teacher depends on student ID and course and teacher also depends on the course but here course is a primary key attribute but then teacher is not a primary key attribute it is not super key in that condition what we'll do we'll split it up such that student in student table we have student ID and T ID here we are making use of a third row okay third attribute in order to connect these two okay so when we when you Club these two you need to get the original when you Club these two you need to get the original table back right so in order to avoid any loss of data or the information you make use of T ID here so student ID and T ID student ID T ID are the composite primary Keys T ID is the foreign key which is connected here here T is the super key because it is foreign key in the above table and primary key in this stable so teacher depends on tid and course also depends on T so this was about bcnf I hope you have understood the normalization topic uh I would suggest you to go through uh go through the written part here uh you'll understand it better so the last Topic in fourth module we have is non-additive or lossless join property so it ensures that no spous stles result after the application of project or join operations these are algebraic operations um relational algebra topic so I'll explain it in that particular module so lossy design refers to a design that represents a loss of information so such a des design where the information is lost so if a decomposition does not have lossless join property we might get additional spous stles after the project and natural join operations are applied so these additional tles represent invalid info and not only they represent invalid info they also lead to the redundancy uh the efficiency and the throughput would be reduced in this condition so looking at the algorithm we have this is a complex algorithm I would say so input is universal relation are a decomposition D such that the R is divided into R1 R2 R3 n Parts okay so a set of functional dependenc is f so we need to create a initial Matrix with one row from RI and one column from RJ for each attribute in AJ in R next we need to set the values for all matric we need to find the matrices then we need to check this condition for each row representing for each row I representing relational schema RI for each column J representing attribute AJ if relation RI includes RJ then set s of I comma J as AJ and fourth we need to continue checking this Loop U so like till the result no longer changes for each functional dependency X determines Y and F for all rows in s that have same symbol in column that of X make y columns also same so fifth step is if a row is made entirely of symbols then decomposition has non additive joint property else it does not so here let's consider the example the relation R is given here we'll decompose this relation in two parts such that R1 and R2 so we'll take XY and Y Z as the combinations if I write for XY I'll get a BB and CC for y z if I write ba a BB and CB so um okay I have from R1 I have decomposed it to R1 and R2 now if I have to join R1 and R2 I should make use of Y because Y is the common one common attribute here right so with the help of Y I can join x y and Zed again and I'll get the same relation again so here whenever a query is raised uh which requires values of X and values of Z I make use of Y yeah so let's consider an example so find value of Zed if value of x is a so value of x is a means this one value of Zed I need to find if x is a I should get a in The Zed okay so this is what they are asking so here we'll write select R2 dot Zed from R2 and I'll perform natural join here R1 where R1 do X is equal to a so this is the query for this now I need to perform join right I need to join these two so what is join join is can be simply explained as cross multiplication of the values here so in order to join I'll write first I'll consider ab and multiply it with all these so I'll get b a BB and CB next I'll take BB and multiply it with all these b a BB and CB next I'll take CC and multi multiply it with b a BB and CB so I have multiplied so this was join now here I considered natural joint so natural joint says that only if the Y value is same select or else reject the other values so here y value is B here also B here also B here also B but here it's not the same so reject this here BB consider this here again BB consider this here it's not same reject this CCB reject this CC BB reject this CC so I've obtained few relations let me write it down here so XY Z if I construct uh if I construct the r table here I'll check I'll take this R1 this was R1 this was R2 I'll take this as R3 I'll write a b and a I have obtained a b b b here I have b b and a I have B B and B and here I have c c and b so when uh the decomposed relations are joined I need to obtain the real or relation back right so that that's what um that's what happens when you join but then if you check here R3 is not equal to R because here there were 1 2 three three values three rows but then here there are five rows so there's there's some variation here right so we can say that spous stles have been identified here so ABA was here but then BBB was here and CCB was here these two are the extra ones so these are the SP stles that I've have obtained so in this condition this is not valid right so there's there's error occurring here so so the solution to this is whenever we are decomposing and formulating new relations the common attribute should be a candidate key so it should be a candidate key for either R1 or R2 or for both R1 and R2 so let's take uh let's take the same relation now I'll try decomposing it with R1 as X and Y and R2 as X and Zed so if I write for XY and for x z let me write for XY if I write I get a BB and CC and for y z if I write I get uh for xed if I write I get a a b b and CB so uh find value of Zed if value of x is a here a x value was a here also a so I had to obtain a a now I'll check if I get obtain a a here value of x is a value of Z is a I have obtained it properly so whenever you obtain the same relation when whenever I combine this I'll obtain the same relation back so that's what the lossless join property says so it should obey the following conditions if I add r r 1 or R2 if I do the union of R1 and R2 I should get the r original back so that's what we have done here right so when I combine these two I'm getting the original one back and then if I do intersection of these two I should not get null so if I intersect these two um a and a a so if I inter intersect XY and x z y and z will go X would be remaining so this is not null next R1 should be a candidate key or R2 should be a candidate key or both could be candidate keys so this was about the non-additive or lossless joint property so I hope you have understood I would suggest you to go through the notes the PDF which I've provided in that there are multiple questions so they they usually won't give this format either 1 2 3 or ABC it'll be in um it'll be in like proper attributes like a role number SSN and all so you need to consider each one as XY then it will be easy to solve in this way so I have considered it as a and have solved it further so this was about fourth module I have tried covering all the major topics so I would say that go through the PDF once there are other topics also just glance them once but then be thorough with these topics and fourth module is pretty easy and scoring module because uh they it has algorithms and these numeric and these problems you can easily solve them and do subscribe to my channel I'll be uploading notes and answers of other modules also so stay tuned thank you

Channel: VTU padhai

Views: 18,056

Keywords: 21cs52, 21cs53, 21cs54, dbms important questions, vtu 21scheme dbms important questions, dbms vtu 5th sem, dbms imp qns, 21cs53 important questions vtu, database management system vtu, database management system vtu important questions, 21scheme vtu important questions, 21scheme cse stream vtu important questions, DBMS imp qns, vtu 5th sem, vtu dbms, vtu dbms qns, informal guidelines, normalization 1nf 2nf 3nf, closure, functional dependency, equivalence in dbms, lossless join, vtu

Id: DXDy1brTlrU

Length: 56min 59sec (3419 seconds)

Published: Sun Apr 07 2024