 So, what we saw before the break was given a set of functional dependencies, what all can you infer using those functional dependencies. And we call that F plus the closure of the set F of functional dependencies. There is another use of the term closure, but this time of attribute sets not of functional dependencies. It is also based on functional dependencies, but the goal is different. The goal is given a set of attributes and a set of functional dependencies. What all function what all other attributes are determined by the given set of attributes. So, if I give you attribute a and say what all is determined by a, you may find that there is a functional dependency a determines b. So, b is also determined by a. Now, trivially a is determined by a. So, the initial set will be a, when you look at this dependency b, a goes to b, I will say a ok b is also included. So, the set is a b. Now, I may have a dependency which says a b goes to d. So, using that I can say a have a b and these two together determine b. So, I will include those in. So, I get a b d and this is the process by which I infer more and more things which are functionally determined by the initial set of attributes which I am given. Final set is called the closure of the given set of attributes. So, that is the procedure which is defined here, but the notion of closure can be defined in a simpler way. The closure of a set of attributes alpha under a set of dependencies f denoted by alpha plus is the set of attributes that are functionally determined by alpha under the given dependencies f. So, this is a very simple thing. So, if you think of it in terms of f plus, if I compute f plus and see take the union of the right hand sides of all the dependencies in f plus which have alpha on the left hand side that is exactly the attribute closure of f or in fact, there is going to be one element of f plus which is the maximal such thing with on the right hand side alpha goes to something which contains all other right hand sides of dependencies with alpha on the left hand side. So, that one corresponds to the closure of alpha under f. Now, we do not necessarily need to compute f plus, f plus computation is very expensive, but alpha plus is actually very cheap. What do I mean by expensive and cheap? Well, given a number of functional dependencies and a number of attributes, the size of f plus is exponential in the number of dependencies and attributes. In contrast, the closure of alpha alpha plus can be done in polynomial time on the same set of dependencies and attributes. So, here is the algorithm result is alpha and now we keep doing this until the result does not change. What is it? This we do for every beta goes to gamma in f, this is not in f plus. If alpha, if beta rather is a subset of result, then we add gamma to the result. So, the result keeps growing and growing until it stops growing. When it stops growing, we exit the loop. That is the basic idea. So, here are some examples. I have this actually the same thing we saw before I think. I have a b c g h i, a goes to b, a goes to c, c g goes to h, c g goes to i and b goes to h. We have five functional dependencies. Supposing I want to compute a g plus the closure of the attribute set a g. So, initially result is a g. Now, is there anything which matches here? There is no a g per say, but I do have a goes to b. It is a subset of a g. Therefore, I can add b to the result. Similarly, I have a goes to c. So, I can add c to the result. So, what do I get now? a b c g and starting with a g, I cannot add anything more. None of the others is contained in a g. These are the only two. But now that I have result equal to a b c g, I can do more. c g is included in result now. So, c g goes to h can be added and similarly, c g goes to i. So, I get a b c g h and a b c g h i. Now, is it anything left here? In fact, this set is a set of all attributes. I cannot go any further. Obviously, everything has been included. So, clearly a g is a super key. The next question is a g a candidate key. How do I know if something is a candidate key? I need to know if it is minimal. How do I check if it is minimal? Well, it is not minimal if there is a subset which is also a super key. But here I can do something very useful. I do not have to consider every subset of the given set. What I will do is remove one attribute at a time. It turns out in this particular case with two attributes. It comes to the same thing. But supposing I had three attributes. If the let us say I have a b c as a super key, then I only need to check three things to see if they are super keys. I remove a, I am left with b c. Is b c a super key? Similarly, is a c a super key? Is a b a super key? So, these are the only three things. There are other subsets. A is a subset, b is a subset. So, they are even smaller. But I only check subsets of size one less and check if any of them is a super key. How do I check if it is a super key? Well, that is easy. Do attribute closure on that set. Now, if any of the subsets is a super key, then I can say no, this is not a candidate key because that is a super key. And then I can in turn take that and repeat this process until I get to a minimal set, which is a candidate key. Of course, there can be many candidate keys. So, finding all candidate keys is actually expensive. Anyway, coming back here is a g a candidate key. Well, we already know it is a super key. Its immediate subsets are a and g. So, is a super key? Does it function in R? In other words, is a plus superset of R? What is a plus here? A plus will be, will include b. It will include c. And then what else does it include? I have a plus, I will start with a, then a b, a b c. And then looking at the last one here, I will get a b c h. I have four things in a plus, but now I am stuck. I do not have g in there. And the remaining two functional dependencies both need g on the left hand side. So, I cannot progress. Therefore, I can show that a plus is not a super key. So, that is eliminated. How about g? Does is g a super key? What is g plus? Well, g plus I do not get anywhere. g plus includes g and that is it. I cannot infer anything more from that because none of the left hand sides are a subset of g. So, that is it. g plus is not a super key. So, what I have just done is shown that a g is a candidate and not only a super key, it is a candidate key. So, attribute closure is actually very easy to do manually. And what we have done in this book is pretty much all the things which we need to do for checking if, you know, for checking if a functional dependency, while it is b c n f and so forth. We have tried to express everything in terms of attribute closure because it is much easier to do manually. Now, here is quiz on attribute closure. I have given a different set of functional dependencies here. Before I continue reading the quiz question, I request center coordinators to make sure clickers are connected first. Software is started up and tell your people when they can press the s t key. So, meanwhile, let us continue with the question. Let us understand what it is. So, I have three functional dependencies. A determines b, b determines c d and d e determines f. So, the question is what is the attribute closure a plus? Is it a b c? Is it a b c d? Is it b c d or is it b c d or is it a b c d f? Those are the options. How do you do this? Well, just compute attribute closure using the algorithm I just showed you. Do it on paper and keep your answer ready. Time is run out. So, let us discuss the answer before we see what the responses have been. So, to compute a plus, I will start with the result being a. So, a is the only thing. Now, what can I add to this? I have a function determines b. So, from that I can add b to the result. So, now the result is a b. Now, I can take the second functional dependency b is contained in a b. So, result will then become a b c d. So, a plus is a b c d at this point. Now, can I do anything more to it? d e goes to f. No, I cannot because e is not there in a b c d. So, at this point a plus the result stops expanding and the final result is a b c d. So, the correct option is option 2. Now, how many people have responded? Numbers have crept up a little bit 187. This time the correct answer has one handsomely. Good. So, people are in sync here. As I said attribute closure is a lot easier to deal with then messing around with Armstrong axioms. So, we are going to see how to do all these things all these tests using attribute closure. Now, moving forward what do we do with attribute closure? There are many useful things. The first is to check if something is a super key we have already seen how to do that. Now, the along with super key we also saw how to do candidate key. The next one is to check if a functional dependency holds. So, again it is fairly straightforward. If I want to check if alpha determines beta simply compute alpha plus and check if beta is a subset of or equal to alpha plus. If it is then I know that alpha goes to beta holds. So, how do I compute f plus using this? What we do is take every subset gamma of r find the closure of gamma plus. Now, this is going to take a while because there are many subsets of r. So, every subset has to be considered. But it is easy to list the subsets systematically. It is not that hard. In contrast, going through Armstrong axioms and using them is very confusing. You do not know if you have taken all cases into account or you forgot something and so forth. So, it is easy to list all gamma. For each gamma it is easy to run the procedure which we just saw to compute gamma plus and then if you really want to come output all of f plus. Now, usually there is no reason to do it, but if you wish to you can take every set s subset of gamma plus and output gamma goes to s or you may just stop at you know output in gamma goes to gamma plus because other ones are just subsets and can be very easily inferred. So, you could do that. So, those are two very straightforward uses of attribute closure. Now, let us come back to normalization and decomposition. So, we saw earlier that certain decompositions are lossy and certain others seem to be fine. So, is there some condition which can show us that a particular decomposition is lossless and the answer to that is the following. Given a schema r which we want to decompose into two schemas r 1 and r 2, we want to check if it is a lossless decomposition. So, what does lossless decomposition mean? It means that for all possible relations r on schema R satisfying whatever constraints we have imposed we want that if you project the relation r on the attributes r 1. Similarly, project them on the attributes r 2 and then do a natural joint you will get back r. So, obviously if your decomposition has no attributes in common what are you going to get? What is the natural joint where the two parts do not have any common attributes? It is in fact a cross product or Cartesian product. One of the questions sent by chat ask this question and the answer is if the decomposition has no attributes in common then it is a cross product and those are usually not interesting. The only reason it would be interesting is if somehow you had joined two relations which have nothing in common and now you break them apart again because they have nothing in common. So, now the condition which ensures lossless joint decomposition is as follows. So, what we want is one of these two functional dependencies must hold in F plus. So, what are the two alternatives? Well both of them have the common attributes r 1 intersection r 2 on the left hand side. So, one option is that the common attributes functionally determines r 1 that is it is a super key for r 1. The other option is the common attributes functionally determine r 2. So, it is a super key for r 2. If one of these is satisfied then the decomposition is can be proved to be lossless joint. So, this is what is called a sufficient condition. If this is satisfied it is lossless joint. Now, is it necessary that for lossless joint that one of these should hold? That answer is a little harder to explain. So, if the only constraints you have are functional dependencies then this is only condition we can impose. But it turns out if we have other kinds of constraints in particular we have what are called multivalued dependencies then we can show that if there is a corresponding multivalued dependency of a similar form then also the decomposition is lossless joint. So, even if the functional dependency does not hold if the other kind of dependency called a multivalued dependency holds then too you can get you can show that it is lossless joint. For now we will stick with functional dependencies. So, here is a relation a b c with two functional dependencies a functionally determines b, b functionally determines c. So, now we can use the functional dependency to decompose while making sure that the decomposition is lossless. As I said earlier if we decompose without making sure it is lossless you are losing information. So, we are not going to allow lossy decomposition they are banned. So, now it turns out in this case we can decompose it in two ways. The first is to decompose into a b and b c what is common between a b and b c? b. Now, it turns out we have already got the functional dependency b determines c. Therefore, the common attribute is a super key for the second relation r 2 b is a super key for b c because b determines c. So, b is a super key for b c therefore, we can show that this particular decomposition is lossless. I will just hold off on dependency preserving for a second. Now, here is the other possible decomposition into a b and a c. Now, what happens? What is the common attribute a? Now, is a a super key for b c because super key for either of those two well if you take a b we have the functional dependency a determines b. So, a is a super key for this one. So, it is actually that is enough to show it is lossless join, but out of curiosity let us see where is the super key for a c is it? It does it is in fact, because given a goes to b b goes to c we can infer a function a determines c. Therefore, a is actually a super key for this one also. So, we are doubly sure here that the decomposition is lossless. So, we have two different lossless join decompositions of a b c. Now, does a b c satisfy b c n f to the decomposition satisfy b c n f we are going to see that in a little bit, but before that let me introduce this terminology of dependency preserving. So, in this case I mean I mentioned it intuitively earlier, but I can explain it again in the context of these two. In this particular decomposition into a b and b c what is the functional dependency that holds on r 1? Well a it goes to b holds what is the functional dependency that holds on r 2 b determines c holds. In this case it turns out these two are exactly the given set of functional dependencies. So, it is dependency preserving. In general that may not be the case the set of functional dependencies that hold may not be exactly what is started off with that is ok as long as they are equivalent. What does equivalent mean? It means that the closures are the same I may have two different sets which look different, but if they imply the same set of dependencies then they are really equivalent there is no difference between them. Therefore, in general I have to check if the individual dependencies that are implied on the individual relations will let us infer the original set of dependencies and that is good enough. Now, how about the second one? I have a b that is checked I also have a function and dependency a c which also I can check. So, let us say both these are satisfied a goes to b a goes to c does that imply a b goes to c it does not because in the first relation I may have two tuples with let us say a 5 and let me write it down here a goes to b holds and a goes to c holds the question is does a function be determined sorry does b determine c is it implied. Now, it is very easy to see if you do closure on b using these two it gets nothing. So, it is fairly easy to see that b function determined c cannot be inferred from these two. So, this decomposition into a b and a c is not dependency preserving this is not implied by these two and if you want to construct an example to see that I can create an example with where I have a with let us say 5 4 and 6 4, but here I have 5 2 and let us say 6 3. If you look at this it is fairly clear that a determines b holds here because the a values are different. Similarly, a determines c holds because a values are different, but if I join these two back to get a b c what do I get I will get 5 4 2 and 6 4 2. Now, it 6 4 3 I am sorry 6 4 3. Now, it is very clear here that this does not satisfy b function which determines c. So, that has shown as basically that that dependency is not preserved. If I only check dependencies locally I may allow the database to reach a state where b goes to c does not hold. In the first one on the other hand dependency preserving decomposition just by checking locally I can ensure all dependencies hold. So, that is an attractive property of the first one. So, if I can choose between these two I would choose the first one. In fact, that is what we will do. Moving forward decomposition is said to be dependency preserving if let f i be the set of dependencies in f plus that include only attributes in r i. So, what are we doing? We are decomposing r into r 1 r 2 r 3 up to some r n. So, generally we do binary decomposition, but in general way you can also decompose multi way. So, this notion is done with the general case. So, I am decomposing r into multiple relations. Now, I can compute f plus and for each r i I find those things in f plus which only contain attributes in r i. They do not contain any other attribute let these be f 1 f 2 up to f n. Now, these are the ones which can be tested locally in r i I can check every one of f i locally. So, now I take this set and take the closure take the union of f 1 f 2 up to f n take the closure is that equal to f plus if so then I will say that the decomposition is dependency preserving if not it is not dependency preserving. So, that is the idea when we just saw an example of this. Now, how do you check if a decomposition is dependency preserving efficiently? There are details in the book I do not have time to cover it in this 3 hour lecture, but you can go read it offline. There is a fairly straight forward algorithm to do it without computing all of this. Now, note computing f plus is very expensive then from f plus computing f 1 through f n is expensive then taking that plus is also expensive all of which will take a lot of time. So, it may seem that this is very difficult to do, but in fact, there is a much simpler cheaper algorithm which is there in the book. So, please go read it. So, now let us come back to decompositions and this is the same dependencies we saw before a goes to b b goes to c and the relation is a b c is r in b c n f that is the first question. Now, if you recall the definition of b c n f what was it the definition was if you have any dependency in f plus then either it is trivial that is it is alpha goes to beta as such that beta is a subset of alpha it is trivial or alpha must be a super key of this thing. Now, take this first dependency a goes to b is it trivial it is not it is not trivial is it a super is a super key yes in this case a is a super key. So, this one is fine it does not cause a violation of b c n f what about the second one b function determines c is it trivial no is b a super key unfortunately no the closure of b here is b c it does not include a therefore, r is not in b c n f. So, the idea is to ensure something is in b c n f I will take all the dependencies in f plus again there are more efficient ways of doing it, but let us start with this take all the dependencies in f plus and check each of them to see if b c n f is violated because of any one of them. If one of the functional dependencies shows that b c n f is violated I am going to use it to decompose. So, in this case I will use b goes to c to decompose r now how do I do that one of the relations will be b c the other will basically be the result of removing c from the original one. So, from a b c if I remove c what do I get a b. So, the two relations are a b and b c are these in b c n f. So, to test for b c n f I could do the following I will check for each functional dependency whether it is trivial or super key, but here is a very useful trick. If you have a binary relation the only meaningful only non-trivial functional dependencies would be that one of the attribute functionally determines the other all other dependencies are trivial I mean either it functionally determines the other or both, but in this case it turns out if a determines b a determines a also. So, it is very obvious that a determines a b therefore, a is a super key similarly if b determines a then b determines b also. So, b is a super key. So, the point I am noting is any binary relation with just two attributes any relation with only two attributes is guaranteed to be in b c n f you do not have to bother testing it by definition it is in b c n f. So, do not even worry about what functional dependencies exist you can just ignore it and move on. Now, if you want to find out what is the super key of the relation yes you have to go back and see what are the dependencies and decide on the super key or the primary key subsequently. However, there is no need to look at it to see if it violates b c n f it will satisfy b c n f. So, in this case both the relations are bindly I am done it is in b c n f. So, what we have here is a decomposition which is lossless join because the common attribute b is a super key for b c it is dependency preserving as we just saw earlier in the previous slide. So, we have a nice decomposition satisfying all the properties the decomposition is in b c n f it is lossless join it is dependency preserving that is our goal. Unfortunately, this would not always happen which is motivates 3 n f. But before getting into 3 n f I want to say a little bit about how to effectively test for b c n f. So, if you are given a schema up front just one relation even and your ask is it in b c n f and that is it the whole schema continues just that one relation there is nothing else and you have given a set of functional dependencies you want to check if this is in b c n f. Now, the b c n f test says compute f plus, but it turns out in this special case I do not have to test f plus. So, it suffices to check each of the given functional dependencies to see if any one of them shows violation of b c n f if it does well we have to decompose r. So, what is more relevant here is if none of them shows that causes a violation of b c n f then we can show that none of the dependencies in f plus will cause a violation of b c n f. This is for the case where I am given one schema and functional dependencies on just that one schema nothing more is there. So, it seems I can get away without computing f plus it seems that everything is very cheap. However, it turns out that if I give you this set f and then I give you two relation schemas or I have a single relation schema it violates b c n f I have split it into two. So, again I have the state where I have two schemas r 1 and r 2 and a set of functional dependencies. It turns out here just using the original set f and checking if r 1 and r 2 are in b c n f is wrong. Now, why does that happen let us consider an example say r is a b c e and the set of functional dependencies is a goes to b and b c goes to b. Now, if I look at this is a a super key what is the closure of a it is a b that is it is a a super key no it is not is a trivial no it is a goes to b trivial no it is not. So, I decompose what do I get after I decompose a b is one relation and a c d e is the other relation so far so good. Now, if we now take these two relations and check for violation of b c n f using these two functional dependencies a b is anyway binary it will be in b c n f we will skip it. What is an element is a c d e is any of these functional dependencies related directly to a c d e well there is no b. So, a goes to b is irrelevant b c goes to d there is no b. So, it is not relevant. So, if I just use these two dependencies it may seem that r 2 is in b c n f, but in fact that is wrong why because from a goes to b and b c goes to d what can we infer I can infer that a c goes to d. How do I do that well I can use augmentation to get a c goes to b c transitivity to get a c goes to d. Now, applying a c goes to d on this one it is obvious that it is not trivial, but a is not a super key because e is not function determined by a. Therefore, a is not a super key. So, a c goes to d shows that r 2 is not in b c n f. So, the moral of this theory is if I just use the given set of functional dependencies f to test after r has been decomposed it is wrong. So, only if there was a single relation I can do this once it is decomposed I cannot use this. So, I have to do it differently I can compute f plus, but again that is very painful. So, here is a slightly easier although it is still time consuming, but from a human perspective it is a lot easier. What we do is you know the straightforward way is to test it by definition we have to test each decomposed relation using all the functional dependencies that hold on that relation. And that is basically the subset of f plus which contains only attributes in that relation that is denoted by f i. So, f i is the restriction of f to r i, but that is very time consuming. Instead we are going to use a slightly different test. What we are going to do is for every set of attributes alpha in r i we will compute alpha plus that is the closure of alpha, but we will compute alpha plus using the entire set of dependencies f. I do not have to compute f plus I will take f compute alpha plus using the given f we have already seen how to do this. And then check if this closure either does not include any attribute of r i minus alpha. That means with respect to r i the restriction will be trivial or it includes all attributes of r i therefore it is a super key in r i. So, this is basically the definition of b c n f either it is trivial or a super key. And we are going to check this for each subset of r i. So, that is basically what we need to do. So, it is easy to see that this test is correct. For every subset of r i we compute alpha every subset alpha of r i we compute alpha plus on the original dependencies and check that either it does not contain any attribute other than alpha from r i or it contains all attributes of r i. So, if it is violated by some alpha beta in f this one we that is for a better way of saying this for some alpha subset of r this condition fails then we can show that this particular dependency will hold alpha functionary determines alpha plus minus alpha intersection r i. So, we can show that this dependency will be part of f plus and furthermore this dependency failed this test and it violates b c n f because of this dependency. And we will in fact use this particular dependency to decompose r i. So, it seems a little confusing here, but let us see how to do it over here given a goes to b b c goes to d and the decomposition of into r 1 a b r 2 a c d e this is the one we saw previously. Now, let us take the second relation the first is binary it is in b c n I forget it take the second relation we have a c d e. So, let us start with just a this is the one which turns out violates it. So, we take a plus under the original dependency. So, what is a plus given this a plus includes b and wait a minute something wrong here. So, from a you get a b it does not include c. So, a plus is not relevant I am sorry this was a typo in this slide. So, a plus is includes only b and b is not even in here. So, this is not relevant what this should have been is we will take a c. If I take a c plus what do I get given a c I can in using a I can get a b. So, I get a b c a b c plus is a b c d. So, what do I get a c plus is. So, a c plus would be a b c d. So, what do I get in the restriction here b is not present. Therefore, what I have found is a c plus let me infer a c d a c goes to a c d is not trivial. Unfortunately, a c is not a super key either because e is not contained in there. So, by using a c and using attribute closure here I have just shown you that this function dependency which I can infer a c goes to a c d or if I remove a c here alpha plus minus alpha. So, if I remove a c from d that comes to the same thing a c goes to d and again intersection r i well in this case d is in r i. So, nothing actually let me go over this slower a c plus is a b c d if I intersect the right hand side with r i b goes away because b is not in r i. So, what is left is after this intersection is a c goes to d that is the result of this particular construction here. So, that shows violation of b c n f and I decompose it again. So, that is the story here. So, please correct this to instead of a plus it should be a c goes to a b c d. So, as I have just demonstrated it is very easy to make silly mistakes when you deal with the functional dependencies. So, it is worth double checking everything which you do and it is worth not doing this on the night before the lecture without having time to cross check it that is the other moral of the story. So, now we have the b c n f decomposition algorithm this algorithm is based on computing f plus, but we do not actually have to do it that way we can also use the previous algorithm. So, what do we do it is actually very simple we start with the given schema we are assuming there is a single relation which we are considering and we keep decomposing it if at any point we find a functional dependency that violates b c n f alpha goes to beta then we decompose into two parts. One of the relations is alpha beta the other relation is r i minus beta we are assuming that alpha intersection beta is 0 if it is not we will do r i minus beta minus alpha that is a minor change. In any case we remove r i from the result and replace r i by these two. So, result is the set of relations in the schema we may start with one relation we decompose again check each of the decomposed relations decompose further and further till all of the relations r i r and b c n f at which point we can stop. So, each r i is in b c n f decomposition is lossless. So, here is an example I have a b c a goes to b b goes to c this is actually very simple example the decomposition is b c and a b. So, now here is a quiz 4. So, remote centers please again check that the clicker stuff is all set up and all of you please press the s t key once now and let us read the question. So, given the relation a b c d and the functional dependency a goes to c which of the four things below is a valid b c n f decomposition a b c a c d a b a c d a b b c d or a b c c d how do you do this well you do not try out each of these instead apply the decomposition algorithm using this dependency and decompose the relation and see what you get and then check if you need to decompose again this particular case you do not actually there is only one dependency. So, nothing more will be required. So, in this particular case is just how to decompose this one relation using the given dependency. So, let us turn on the timer your time starts in a moment. So, this was again a fairly easy question it is a straightforward application of decomposition. So, to decompose using a c d one of the two relations will be exactly a c d the other one contains a the left hand side and all the remaining attributes after removing c d and the only remaining attribute here is b. So, the two relations are a b and a c d. So, the right answer is 2. Now, let us see what people have done many people have not attempted it this time only 126 have attempted and again the correct answer has the largest number of responses. But, if you add up those who gave wrong answers well the wrong answers are together are still in a majority. I know again this is going at lightning pace if you are not familiar with this already and I suspect some of the people attending this have not dealt with function dependencies earlier. But, if you have taught it and have understood it this should have been relatively easy. So, make sure after this course over that you revise it and I am able to solve such things in a straightforward way quickly. So, here is another detailed example of b c and f decomposition this time we have just create a relation which has thrown everything into one relation course I d title department name credits section I d semester year building room number capacity time slot I d what is this relation it is basically starting with course we have combined in section and along with section we have combined in classroom and. So, that is the basically the combination of all of these. So, if we started off with this what are the functional dependencies which we would have on this we know that course I d functionally determines title department name and credits that is we have been using that for a while. In fact, these are all functional dependencies we are know and are familiar with we know that a room a classroom is identified by a building and a room number within the building. So, if I identify a classroom uniquely its capacity is the number of people who can sit in that room is also identified uniquely. So, I have the functional dependency building room number determines capacity. So, that is a second functional dependency now the third one is if I uniquely identify a section then I know which building room it is in I also know which time slot it is in because the section has a unique time slot. So, this can be expressed using the following functional dependency the left hand side is the primary key for section course I d section I d semest the year the right hand side are the things which are uniquely determined building and room number is where the section meets that is unique. Similarly, time slot I d is the time slot in which the section runs that is unique. So, these are the three functional dependencies which I can infer on this set of attributes how did I infer them this no algorithm to magically come up with this I have to understand the domain and figure out what are the functional dependencies on this domain. In this domain I actually got most of these by first identifying entities which were actually course entity the section entity and the classroom entity. So, I actually identified three entities and figured out their primary keys and came up with the functional dependency there may be others in this case there are in, but you have to go through it carefully. So, once you have done that we will have to see what are the decompositions required now if you take the first one course I d title department name credit it is clear that course I d is not a super key because I have joined it with section there are many sections per course. Therefore, we decompose into course I d title department name credits and then class one which is the remaining attributes course I d the left hand side is course I d. So, that will repeat here and all the remaining attributes. Now, I will repeat this step I am going to go over it quickly is course in BCNF this is the course I d title department name this one how do we know it is in BCNF we will have to apply a test either compute F plus or take every subset and compute the closure it turns out that title department name and credits are not on the left hand side of anything here. So, their closure will not give anything new. So, the only useful thing here is course I d, but again course I d on this does not do anything more it is already the super key. So, we can do attribute closure on all subsets and quickly verify that in BCNF. Now, the second relation class one which we got here again we can decide it is not in BCNF because of what because the building and room number uniquely determines capacity. So, it is not a super key, but it is not trivial. So, again we break it into classroom building room number capacity and then the remaining ones and again we have to go through this test I am going to skip the details to show that these two are in BCNF and then we are done. So, I went slowly over BCNF relatively speaking relative compared to what I am going to do for 3 NF which I am going to go over at lightning speed because we have just about 20 minutes left. So, again I do not expect you to fully understand 3 NF in the course of this lecture, but I hope combined with the practice session in the afternoon you will be able to get a good hang of 3 NF also. So, now why 3 NF the motivation comes from few sample things take this particular example and I will actually give you a realistic example matching to this, but for the moment let us just say this relation schema R as attributes Jkl and there are two functional dependencies Jk goes to L and L goes to K. What are these intuitive we will see a good example coming up for the moment stick with this. Now, what are the candidate keys in here Jk is a candidate key because it determines L how about L is it a candidate key it determines K, but not J. So, JL is also a candidate key. Now, let us come back to R is it in BCNF it is not because L goes to K shows that it violates BCNF. So, let us suppose we decompose we can decompose if we decompose using LK what do you get one relation is LK the other one will be LJ that is only decomposition possible LK LJ does it preserve Jk goes to L it would not. In fact, it is easy to see that that is the only decomposition here. So, if you want to bring this into BCNF you can be sure that Jk goes to L will not be preserved in other words this particular example does not have a dependency preserving BCNF decomposition. So, if you are worried about dependency preservation you cannot achieve it using just BCNF that is the story. So, if you decompose using LK and LJ to check if Jk goes to L holds you have to do a joint.