Okay, so in the last lecture we started discussing the relational algebra, right. So, relational algebra is a central component of the relational model and offers certain operatorswhich operate on relational instances and produce relational instance. And then we talked about2 important 2 unary operators, they select and project operators okay, a couple of importantpoints before I move on to other algebra operators. See, one of the common mistakes that youknow people do when they are in the early stages of learning is you know put some in thecondition here in a typical algebraic expression.In the selection condition, they try to put another selection condition because they produce, theythink that that other selection condition produces a value or something like that. So, that will bein a okay. So, for example, let us just consider in this particular data there is only one Mahesh orwhatever is that let us say that this particular result, you know, produces only one name okay.Let us consider the case that this result produces only one name. Even so, even if it produces justone name, it is important to recognize that what it produces is a relation right. Even if it is just asingle value, it is actually a relation, which has 1 column and 1 rho and 1 value. It is still arelation, it is not to be confused with the just the value okay. So, if this confusion is removed,then you will not have the tendency to kind of treat it as though it is a value.And then try and put it in the condition here. So the conditions syntax is very fixed in a verymuch to be. I mean, we discussed this condition syntax for this particular account selectioncondition, it can only involve atomic operators that have attribute names and comparisonoperators with values or other attribute names. So, that is the atomic kind of thing. So, we cannottry and put another selection condition I mean equal to sigma something you cannot right herefor example, okay.So, these are certain. So, this point about even though the algebra expression produces a relationwith single column and a single rho, it is still a relation that has to be clearly understood. It is notto be confused with the value that is present inside that okay. Now with these called points, let usproceed to understand what are the binary operators that are present in relational algebra. One of the first things that comes to our mind is that since relations are sets of tuples, what aboutset operations, the familiar set operations, will they be useful for us with a familiar set operationsare the union, intersection and difference right, these are the set operations, but how can wemake use of these set operations. One issue that we find here in these relations is that each ofthese relations, relations instances has a different set number of attributes okay, one relation mayhave 3 attributes, the other relation may have 5 attributes right.So, in one relationship tuples are of length 3. In the other relationship tuples are of length 5. So itis not obvious how you can take simply union of those 2 things because if you take a such aunion, then the very concept of in producing a relation does not, you know, any more work thanit is the union will be union of 3 tuples and 5 tuples. So it is not a relation instance, is just someunion, right. It is not a use for us.We want our algebra operations to produce relations, take relations and produce relations. So it iskind of required that we must put some kind of condition before we can make use of theseoperators, the union difference and intersection operators. So, that condition is what is calledunion compatibility. We call this condition is it union compatibility. So basically we are justchecking whether 2 relations are you know compatible for applying this union difference andintersection operations.So, what is the compatibility that we are asking for, it is basically that the number of attributes inso let us consider these 2 relations R 1 with A 1 through A k R 2 with B 1 through B mattributes. So, union compatibility basically says that the K should be equal to m. That meansthat they should be equal number of attributes in both relations and not only that, the domains ofcorresponding attributes should be same.So, the domain of A i should be domain of A 1 should be equal to domain of B 1, domain of A 2should be equal to the domain of B 2, etc. So, if you want to combine take a union of tuples fromR1 and R 2 these attribute names may not matter, but the underlying values should come fromthe same set, if you take, so, if you take a tuple and consider its first value suppose this is integerand if this is string, then the union does not work out that well right.So, you want both first tuple to be integer in both this relations and so on. So, you want domainof corresponding attributes to be the same and the number of attributes should itself be same. So,under these 2 circumstance, conditions, if these 2 conditions are satisfied, then we will call thatpair of relations as union compatible and then we can talk about producing unions anddifferences and all that okay.So, set operations union intersection difference, these are applicable to relations, if 2 relations iftheir schemes are union compatible, otherwise we will not be able to apply union intersectionand difference in our context, because our operators are relational algebra operators, we wantthem to take relations and then produce relations. So, another issue that comes up is supposing r3 = r1 union r 2 where r 1 or r 2 are union compatible and we are producing.So, what should be the schema for r 3, or should it be let us say the schema of r1 r should be theschema for r 2 right. So, that question comes up. So, what we do is we just simply follow aconvention saying that the left operates schema is what we take as a scheme of the resultoperation okay. See notice that these schemas are compatible, but the attribute names may bedifferent.So, one set of attribute names we have to take which set is to be decided. So what we do isfollow a convention saying that the first operates schema is what we use for the result okay, isthat point clear. So just as a convention okay, now let us look at some examples of application ofthis union practice okay the definitions are very simple. So, r 1 r 2 relation schemes with capital R 1 capital R 2 and r 2 is union compatible with r 1 thenthe operators are defined like this r 1 union r 2 is the set of all tuples t such that t either belongsto r 1 r belongs to take the tuples that belong to either one of them and then simply take thewhole set together. So, it is possible that some tuple exists both in r 1 as well r 2 in which caseobviously, only one instance because it is a set right it should not contain duplicates.So only one tuple will exist. Now, the intersection is different in a similar way, in the usual wayand so r 1 intersection r 2 is the set of all tuples that belong to both r 1 as well as r 2 and r 1difference r 2 is the set of all tuples that belong to r 1 and do not belong to r 2. So, these are theusual definitions of union intersection. So, and by convention in all these cases, the scheme ofthe result so this is the result relation right. So, is the scheme of the first opera and that is r 1okay. Now, let us look at another. So, I am actually skipping giving a specific examples for this r 1 because they are very familiaroperate as far as the union intersection and difference. We will see that coming up later in otherexamples okay. Now, moving for the we oftentimes actually want to combine information fromone relation with information in other relation irrespective of whether they are actually unioncompatible or otherwise. Sometimes we need to do that.Whereas the union allows us to combine information from one relation to and another relationprovided their union compatible right. But even if when the not incompatible also, we would liketo combine but the different kind of a combination is that actually I want to take a tuple fromhere and concatenate the tuple from the other relation. Right now what we are doing is takingtuple from one relation and taking tuple from the other relation.And then simply adding them together into one long list. But instead, let us say I want to takeone tuple from here, take another tuple from there and then concatenate the tuples and produce anew tuple, sometimes I may need that. So, we will see an operator that actually allows us to dothat. And this is also very familiar operator for us actually, the cross product of sets will recallthe definition of a cross product.So in the context of relations, the let us look at this carefully now, r1 is a relation on the schemaA 1 through A m okay, and it has certain s number of tuples. So, I have written as A 1 1 1 2 1 m.So, all the first tuple has the first subscript as 1, the second tuple has the first subscript as 2 andthen the remaining thing says 1 2 3 up to m and the last tuple is A s 1 A s 2. So, there s numberof tuples in the first relation in a similar way r 2 has B 1 through B m, n number of attributes andit has some t number of tuples.So, s and t so, r 1 has s tuple as r 2 has t tuple and so, we were allowed to find this cross product,the cross product of r 1 cross r 2 r 1 r 2 denoted as r 1 you know with this symbol cross r 2 is thisrelation okay. So what is this relation the scheme of this relation is the concatenation of thescheme of this and the scheme of that. So A 1 through A m and B 1 through B m all attributesare present.And what we do is, you might actually wonder what is this guy doing this is going to producehuge amount of data. Suppose this relation has 1000 tuples, and this original has another 1000tuples, then you are going to produce a million tuple, tuple in the output. Is it really useful yes,we will see that will come to that point a little later. So, what it basically does is to combine thistuple with every tuple here and produce a tuple here.This operator, so a 1 1 a 1 2 a 1 m is combining with b 1 1 b 1 2 b 1 m and also b 2 1 b 2 2 andthen this second tuple and the teeth tuple like that. So this produces some t number of tuples, thisproduces t number of tuples etc, you will get so many number of tuples so. So the operator itselfis clear, but then, you know, we might actually wonder how useful is this operator is going to bebecause it is going to actually produce a huge amount of data okay. So let us look at an example situation where we will need this cross product. But then we alsowill notice how exactly the cross product is going to be used in data retrieval queries okay. Soobtained the list of professors Id and name, along with the name of their respective departmentsokay, here is the data retrieval and a query request or query, applying the list of professors Id andname of the professor.Along with the name of their respective departments okay. So where is this information just Iwanted to look at your schema diagram and then tell me where what are all the relations in whichthis particular information is present. Is it just there in the professor relation itself. In theprofessor relation, do we have the name of the departments, we do not have the name of thedepartment, we have the name of the department inside in a another relation which is thedepartment relation.And there is a foreign key in professor that refers was to department relations. And so, you needto actually you know, combine this information and then obtain the name of the department. Sothe required information is present in 2 relations, the professor and the department with if youwant we can go back to the schema and have a look at it but I suppose you have the schema withyou.But let me try whether this works. And then we will have to apparently go to the pen then andthere we are. So, if you just want to have a look at the relation scheme, we have it here. So, you can see thatprofessor has name and department number alone. So, if you want to know the name of thedepartment, it is present in the department. So that is why we need to combine these 2 things andso and you can see that these are different relations and so there is no question of taking anyunion or anything like that it does not work that way.So how it works is that I should consider a across product of these 2 things. That is the only wayI can combine information from this to that, I can take a tuple from here and then concatenate thetuple from there and then see what does I can do with that. You can see that issue. So that iswhere as the cross product, but you also see that we are doing unnecessary work.Because we are combining a professor with all the 10 departments and 14 departments, whichare actually unnecessary okay. So now, let us proceed. So I will kind of solve this. Let us seehow we can do that. So let me project another thing is that okay. So let me illustrate severalpoints, you know that come up here with this thing notation also. So we are taking the professor,table, I mean relation, sorry.And then projecting employee Id, name and department number because these are the onlythings that were relevant for us right now, in this particular query. We want ID and name anddepartment number of from the professor. So let us, so this particular thing where I on the lefthand side, I given new relation name, and then give us a bunch of exactly the same number ofattributes as I am projecting here is an important construct, you know.This is called the renaming of attributes, we are renaming the attributes like this. So, thisparticular operator the project, the project operator basically takes the employee Id, name,employee Id name and department number of professor relation and projects them out right anddiscard the rest of the attributes. So, it produces a sub tuple for every tuple produces the sub tupleconsisting of only values from these components values from these attributes right.So you get since it contains the employee Id which is a key for professor, you will get as manytuple as there are in the professor relation okay, so you get all of them, but only thing is here, Iam renaming them like this, we will see why exactly we do that, but then I want to illustrate Iwant to exactly you know, define the process of renaming okay. So, we put a new relation namehere and give you even sometimes the same names and then you know use this operator.This operator is can be thought of as assignment operator, assignment operator in my viewshould be always a symmetric operator should not be looking symmetric like equal to and thingslike that. So, I use this arrow like this to say that result will be assigned to this new relation. Andin the process the attributes will be renamed as these entrepreneurs okay, so that is what we areabout.In some textbooks, they also introduce a new, they introduce an operator for doing this. It iscalled a rho operator, okay. And they use that, but instead we will follow this convention in ourslides that whenever we want to remain, we will. And also this kind of this relation you knowstands for some kind of an intermediate result in our formulation of the solution for this query.So, okay, let us proceed and then I will come back to this if necessary. So and in a similar way, Iam producing a new relation called department detail. And they were since I am interested in thename of the departments. So I am projecting department Is and name from the departmentrelation and renaming them as the dId and dname. So, the name is getting renamed as dnamehere, the names of these attributes are important. So, remember that.So, the name is getting replaced by d and then we have a now we can, now that we got the, youknow, relevant information from both professor and department, we can actually combine themand produce a new relation called prof department using the cross product okay. So once we dothis, we have tuple from this and tuple from this you know concatenated. So a tuple from thiswill get concocted with tuple from the other relation.And we have the bunch of us, obviously, we are not interested in all those combinations. We areinterested in meaningful combinations of this particular cross product. What are meaningfulcombinations in this case. In this case, if for a combination to be meaningful a tuple concoctedwith another tuple here for it to be, you know, meaningful in our context, what is the condition itshould satisfy. Both of them should have the should refer to the same department right.If I take a professor from computer science department and then take a mechanical engineeringdepartment tuple and then simply combine, that is not going to be useful for me at all right. So,for this combination of tuple from this to this to be useful, we would like both the tuple to referto the same department and how do we ensure that that simple because we have the departmentnumber here and department Id here.So, we can impose a condition saying that they should be equal to each other. If we impose thatcondition, then we will get meaningful combinations of these tuples. So that is what we will doimmediately. So, desire the professor department tuple or those so, we can now use the selectionoperator to get the appropriate combinations. So department number equals dId. So departmentnumber exists in this particular.So what is the scheme of this prof department schema, the prof department is eId, pname,department number dId, dname, the concatenation of these 2 schemes. And so in that scheme,these are both attributes. And that is why I had to do the renaming. Because if I do not do therenaming, this name attribute is common to both relations, right. So if you combine theinformation from this and this.Then we should ensure that the attribute names are different, right. So you cannot have the sameattribute name occurring 2 times in a relation scheme. That is not allowed. So, we did theappropriate rename, we call it pname here and then we call it dname here. So that when you takethe concatenation of these schemas, we get a proper schema. And then now we are applying theselection operator to get to impose this condition to.So that we get meaningful combinations of tuples from the cross product. So this particularphenomenon, of you know taking the cross product of 2 relations and selecting the requiredcombinations occurs very often occurs almost always, this is the case where we will use the crossproduct. And so there is a strong motivation to actually combine these 2 kind of things into 1operator.So, we will see that actually in a short while now okay. Now finally, to get the result of thisparticular to get the result obtained the list of professors along with the name of their respectivedepartments, all that we have to now do is to take this desired professor department project eId, pname and dname right. So exactly as to how we done the, now notice one thing that if you havethe operator for the let me try okay.First let me take any questions on this one. Is this clear, do you want me to go over this again. Isthis clear. First thing is, we are renaming the we are projecting desired attributes from professorand renaming them and then getting an intermediate relation called professor detail. And then weare taking relevant attributes from department and projecting the relevant attributes from fordepartment and it giving, producing a intermediate relation called department detail with these 2names.And then we are doing a cross product. And since the cross produces a lot of combinations oftuples, which are not really does a meaningful. We are going to impose this condition thatdepartment number should be equal to department Id and only such combinations are meaningfulfor us and we are calling that as desired professor department and then from there we areprojecting what are the attributes that we actually require the eId, pname and dname.That is how we will design. Now, this style of writing a query is kind of desirable because, firstthing is that we are able to organize our thinking and then produce some meaningful names forthe intimate relations. These are the intermediate relations and we are giving some meaningfulnames for those intimate relations. And then finally producing one relational algebra expressionokay.Supposing we adopt that particular operator called the rename operator okay. If you have therename operator how exactly this gets returned I will show you. Let us see, let me try and writethat okay. If so the rename operator is called rho operator okay and then whatever is the new names weshould right here okay, what are those new names eId, pname, and department number. Now sowhat is written inside is basically this entire expression pi employee nameId, name, departmentnumber, professor. So you can imagine that being written by pi the employee Id, name anddepartment number this.And then we can have professor is the way to move this okay. So, you can imagine this kind ofsituation. So, this stands for this relation now that expression that I wrote there it is rho operatorstands for this particular relation prof detail, so, we did not name it as prof detail okay. Insteadwhat we did is to simply produce it as a expression. Now, this is a rename operator.So, what it takes is takes a relation and then renames the attributes like this and then gives a newrelation okay. So, what we did in this slide in the actual the slides that are showing is to kind ofname this entire relation as prop detail, okay. So it is an intermediate thing. In a similar way youcan assume you can see that I will produce this department detail in a similar way by using a rhooperator rho dId, dname of this particular thing.Then that will give you an the intermediate relation, then I can do a cross product and so on. So,I can basically take this and then do a cross product and then give that other thing that we haddId, dname from okay, we have pi here and so on. The point I am trying to illustrate here is thatwe have so inside this there is a pi expression projection expression and here is the renameoperator.And now we can see that this is the cross product okay, the entire cross product. Now after thecross product, we basically lead the selection operator, selection on that cross product okay. Sowe can you can imagine that this entire thing is inside like this, and then put a selection with theselection condition department number equal to dId and then actually as a final step, we aretaking this entire thing and then producing and then finally giving a pi with eId, pname anddname okay.So, this is actually the relational algebra expression that produces the, that gives us the result forthis particular query, obtain the list of professors, along with the name of their respectivedepartments okay. Are you getting the point that this entire thing in fact can actually beexpressed as one single expression like this, which has inside this is the cross product, this entirecross product thing okay, let us not bother about that.So basically pen color I can choose I think right. So this is the entire cross product expression. SoI can put that here and then apply the selection, and then finally do the production. So, I get anexpression, but as you can see this big expression is kind of difficult to understand. So, it is agood style to write the expression in this way, where we give meaningful names for theintermediate relations, okay.And then combine and then use so many steps to kind of finally produce the result of that wewant okay. So I want you to follow this kind of style of writing where we put sub expressions onthe right hand side, assign it to meaningful intermediate relations with appropriate naming for theattributes if necessary. And then combine them and then you know, use one line for each of theoperators that we are producing, we are required to use.And then finally produce the result. So it is kind of easy for you to develop and then, you know,understand the query. And it is also easy for anybody who is looking at this query andunderstand that, that yes, this query is really producing exactly the kind of result we want okay,so even though theoretically, you can express that entire thing as one big relational algebraexpression like this with the help of the rho operator.The rho operator I did not introduce formally, but there is a rho operator which basically does therenaming okay. So is there any questions now in this particular thing. So, we listed several pointshere of renaming and then giving meaningful names for intermediate relations and then makinguse of those intimate relations on the right hand side again and produce new relations and thenfinally produce the results that we want.So, with that, let me see whether I can take you a little further, okay. The other point that wenotice here is that after the cross product in general you know, producer huge number of tuplesand a lot of combinations of tuples from one operator and the other operator and it is generallyseen that it is that all the combinations are not meaningful. And so, we most often follow it up byhaving a selection operator.And since the combination of cross product followed by selection is such an often occurringphenomenon, what we will actually do is to introduce a new operator and that operator isactually you would have heard about it, it is the joint operator. So, we will discuss the jointoperators and the various variations of joint operators in the next lecture.