 Hello friends. Today we will see the next operations in relational algebra. So, learning outcome is at the end of this session students will be able to write relational algebra expressions using union, intersection, set difference and Cartesian product operations. They will be also able to write the relational algebra expressions for the given skill statement and vice versa. So, first we will start with union operation. So, consider R union S. So, we are taking the union operation of two database relations or temporal relations that are the result of other relational algebra expressions. So, before taking union operation we need to see that whether these two relations are compatible. Compatible means whether they are satisfying the conditions that the relation R and S must have same number of attributes and the second condition is that the domain of ith attribute of R must be same as domain of ith attribute of S. So, now consider one example. Now here there is courses taught relation with the attributes year, class ID, course ID and teacher ID. Find courses taught by ARD in 2015 and 2018. So, at this moment pause video and try to write down the sub queries for this query or skill statements if you know. So, teacher ID is ARD whereas years given are 2015 and 2018. So, for this particular query sub queries will be find courses taught by ARD in 2015, find courses taught by ARD in 2018 and then we can take the union. So, equivalent skill statements will be select course ID from courses taught where teacher ID is equal to ARD and year is equal to 2015. Same for the other selects course ID from courses taught where teacher ID is equal to ARD and year is equal to 2018. Now write down the relational algebra expressions we have already seen the select operation and project operation in relational algebra in previous lecture. So, project course ID which is nothing but select course ID condition is that teacher ID is equal to ARD and year is equal to 2015 and the relation is courses taught same for the other query. So, we have received now we have written two relational algebra expressions for this sub queries. We have to use this union operator for taking the union of the tuples from the first relational algebra expression and second relational algebra expression. Thus we can use union operation. So, result of the first statement first sub query will be this DC course in the second sub query it will be DC and CN and then we will take the union which will give you DC and CN. Duplicates are removed because the resultants are resultant relations are considered to be sets. Consider one more example now there is company X which is running at side two side site A and site B. At site A the schema is a transaction relation whereas at site B there is sales relation. So, we want to find the customers of company X. So, here we will be performing union operation. So, first we have to see that whether these two relations are compatible. So, number of attributes in these two are same also the domain of each attribute respect to attributes are also same. So, here we can perform the union operation. So, again what will be the sub queries for this select customers of company X, select customer name from transaction at site A and select CName from sales at site B. So, equivalent relational algebra expressions will be project customer name from transaction union project CName from sales. Now for example, if we want to use this particular resultant relation then we can also use rename operation before that see the result. So, duplicates are removed and thus we have got all customers of company X. Now we want to say send some greeting cards to all the customers ok. So, we want to use this particular further. So, we can rename it for the relation name cost whereas customer name is the attribute name at one site CName is attribute name at the other site. So, for using that particular relation we are renaming this attribute as name. So, result is the same but the attribute name will be here name and the relation name will be cost. So, here are some more examples now this person and employee are the compatible relation because the number of attributes are same and also the domain of respective attributes are also same. These two are non are not compatible relation because first name and f name domain are same. Last name and l name the domain are same but for address and salary the domains are different. So, though the number of attributes are same as domains are not same these are not compatible relation whereas in these two the number of attribute itself are different. So, these are also not compatible relation. Now sometimes we want the tuples which are common in both the relations. So, we will be performing set intersection operation. So, again for taking the intersection operation or performing the intersection operation these two relations should be compatible. So, consider the query find courses taught by ARD in both 2015 and 2018. So, we have already seen this subqueries and the respective SQL statements and relational algebra expression. So, whatever the set or the relation that we will receive with expression 1 and expression 2 we need to take the intersection of these two which will give you the common tuples or records in these two expressions. So, in that way we can perform set intersection operation. So, here result of the first query is DC, second is DC and CN. So, resulted operation will give you DC as the result. Next is set difference operation. So, sometimes we want the tuples which are present in one relation but not in the other relation in that we can perform set difference operation. So, also for this operation we need to have these two relations compatible. So, consider query find courses taught by ARD in 2018 but not in 2015. So, subqueries are the same find courses taught by ARD in 2018 find courses taught by ARD in 2015 and then take difference. Only here the order is important we want the courses in 2018 but not in 15. So, we have written the query for 18 first and then 15. Equivalent relations for these two queries and then we are going to use this difference operator or the subtraction operator. So, that all the records which are present in 2018 but not in 2015 will be selected. So, result of this query will be the courses taught in 2018 are DC and CN courses taught in 2015 is DC. So, the result will be only CN. Now, consider what we want to find courses taught by ARD in 2015 but not in 2018. So, try to write down the relational algebra expression and find out the result, whether the result is same as the previous query just check it that. So, we want to reverse this sequence. So, first courses taught in 2015 take the difference with the courses taught in 2018. Now, if you look at the result the courses taught in 15 is DC. Courses taught in 2018 is DC and CN. So, there are no courses which are taught in 15 but not in 18. So, result will be null. Now, the next operation is Cartesian product. Suppose we want to combine the information from two relations we can perform Cartesian product. So, consider two relations R and S. Now, what will be the number of attributes in that Cartesian product? So, if R has n 1 attributes and S has n 2 attributes then Cartesian product will have n 1 plus n 2 attributes. Regarding the number of tuples if R has m tuples and S has n tuples then the resultant product or the Cartesian product will have m into n tuples. Now, assume that or there is a condition that there is same name appears in the relation or the attributes name are same then we need to devise a naming schema. Now, consider this example here R has a 1 a 2 a 3 whereas S has a 1 and a 4. So, attribute name a 1 is common in these two relations. So, one method is that use relation as in prefix for every attribute. So, R dot a 1 R dot a 2 R dot a 3 S dot a 1 and S dot a 4 or the other option is that then attributes which are common for them we can use relation name as prefix and remaining attributes can be used as it is. So, here R dot a 1 a 2 a 3 and S dot a 1 a 4 in this way we can use attributes. Now, we will see what one example here. So, there are two relations courses dot and courses and the query is that find course ID and course name in 2016. Now, course ID and year are present in this courses dot, but the name is present in courses relation. So, we need to combine this courses dot and courses relations. So, we will be performing the Cartesian product of these two, but we want the year 2016. So, there will be selection that year is equal to 2016. So, first Cartesian product will be found and then we will be applying this selection criteria. Now, how we will get this result of Cartesian product? Now, here there are four records or tuples in the first relation and three tuples in the second. So, resultant relation will have 12 tuples. So, either you can take first tuple and combine with all the remaining tuples of the second relation or you can take the first tuple of the first relation, first tuple of the second, second of the first relation and second of the second relation any sequence can be followed while combining this information. So, here I have used first record of first relation and all the records of the other relation. So, we are getting here 12 tuples. Now, we want only year 16. So, we have filtered the records with the year 2016. Now, we are interested in course ID and course name. Now, if we look at the result here it is the course ID is AM1, course ID is AM1 of courses and the name is applied mathematics 1. In the second record here the courses dot course ID is AM1, but here it is DC and the name is data communication. So, which is the exact course name? So, wherever there is common attributes the value should be same. So, course ID is a common attribute. So, wherever there is value same that is going to be the correct record. So, here there are only two records. So, AM1 with AM1 and DC with DC. So, what we are done is that on this selection again one more selection is applied. So, courses dot dot course ID is equal to courses dot course ID. And now, we are interested only in course ID and C name. So, we have projected courses dot dot course ID and C name. So, here this is the result AM1 and DC. So, we have combined these two relations then we have applied our selection criteria because we are interested in year equal to 2016. Then we have applied the selection where the courses common attributes are same and then projected the required attributes. Thank you.