 Hello friends, today we will see the next operations in relational algebra. So, learning outcome for this session is students will be able to write relational algebra expression using natural join, outer join, assignment operation and extended relational algebra operations. We will start with natural join. So, when we want to combine some certain selections and Cartesian product into one operation using this natural join. So, what are the steps in this natural join? We will first form a Cartesian product of its two arguments or two relations that we have passed. Then we will perform some selection criteria based on the attributes which are common or which are appearing in both the relations and then finally, we will remove duplicates. So, here if r and s are two relations. So, r natural join s is equal to project the attributes of r and s. So, that is nothing, but r union s perform the selection criteria on the attributes which are common in r and s on Cartesian product. Let us see with example. So, again courses taught relation and courses relation. So, look at the attributes here class ID, course ID and teacher ID, whereas in courses it is course ID and course name. Now, find the courses taught by teacher along with the course name. So, we require courses taught and courses. These are the two relations will perform the Cartesian product. Then the we will perform the selection on basis of the equality of the attributes. Now, here the common attribute is course ID and then we will be selecting the attributes removing the duplicates. So, courses taught Cartesian product courses, selection on course ID which is common attribute and then the attributes which we are interested which is equivalent to all these are equivalent to this performing courses taught natural join courses. So, Cartesian product and selection is equivalent to natural join. So, now how it will be performed? So, again we have seen in the previous lecture that there are 10 records here and 10 records in this courses. So, total 100 records will be obtained in Cartesian product. So, first record of this courses taught. Now, the common attribute is course ID. So, DMS is not equal to AM1 then DMS is not equal to C. In that way this record will be combined and will be checked for common attributes or value for the attribute. So, only this DMS and this DMS is same. Second record this will be checked again with against all the attributes. So, only AM1 and AM1 will be equal. So, in that way we are getting this result as in 10 records where we are getting teacher ID course ID which is common. So, either of them can be used as a relation and then course name. This is the result of natural join. Now, consider these two relations here employee and department. So, perform the natural join of employee and department. So, pause the video and write down the result. So, common attribute is department ID. So, I hope you have written the result. So, here if we look at this again departments and employees. Now, here are there are one, two, three, five departments and whereas, the departments in employee all the departments are not having employee remember that. So, mechanical and civil are the departments where there are no employees in employee tables we are not got that departments here. So, sometimes we want the complete information without any loss in that case we can use outer join. So, outer join is the extension of this natural join and which will be used to avoid the loss of information. There are three forms of outer join left outer join right outer join and fully outer join. Now, first we will see left outer join. So, it takes all the tuples of the left relation which are not matching with the tuple in the right relation. Now, it will pair the tuples with null values for all the attributes in the right side relation and add the result to the natural join. So, obviously now consider this department relation and employee relation. So, department left outer join employee. So, natural join will be performed and the remaining record where there is no record on the right hand side it will be padded with the null. So, all the records of the natural join are already written over here which is the result which we have seen in the previous slide. Now, mechanical and civil does not have employee. So, these two values will be written or the padded with null. So, this is the result of left outer join. Right outer join is again the reverse of the left one. So, it takes all the tuples of the right relation that did not match with any tuple in the left relation. So, obviously again it will be padding the value null for all the attributes from the left relation and then add the result to the natural join. Now, consider the example of the loan relation and account relation. There are some accounts out of that some accounts or some customers are having loan also. So, we want to get the information of accounts along with their loan. So, loan right join account. So, obviously it will give you what the result. So, pause the video and try to get the result. So, right join is for account. So, all the records will be account will be obtained. Whereas, the records which are not matching will be padded with null. So, here there are two records or the two accounts which doesn't have loan account. So, these values are null here. The full outer join. So, it does both left outer join and right outer join. So, the it will pad the tuples of the left relation that does not match with the right relation and it will pad the right relation that does not match with the left relation and the result will be added to the join operation obviously. So, consider again the same relations that we have seen employee relation. So, here ENTC is the department for the employee which is not listed in this department relation. Whereas, mechanical and civil are the departments for which there is no employee. So, what will be the result? Write down the result. So, here this ENTC department is not listed in the department relation. So, department name will be null. Whereas, for mechanical and civil there are no employees. So, result of employee ID and employee name will be null and this is the complete result of full outer join. So, if you want only the common records you can perform natural join. If you want the complete information of the left relation or right relation then you can use left outer join or right outer join or if you want the complete information of both the relations along with the matching record then you can go for full outer join. The next operation is assignment operation. Sometimes we want to store the result of this relational algebra expression temporarily. So, then in that case we can use this assignment operation. It's the same as as we use it in programming languages. Now, it is denoted by this backward arrow. So, here consider again the natural join how it is performed. So, first perform the Cartesian product assign the result to the temporary variable. So, here it is ten point for example. Now, the selection should be applied on this particular result of the Cartesian product. So, select with the common attributes which are present in both the relations and then again the result will be stored in ten two. So, if you remember our example this will contain 100 records of Cartesian product. After applying the select criteria it will take only the records matching records that are the ten records and after that we are projecting what we are interested in that many attributes. So, we can use here assignment operation. Now, sometimes we want to write a relational algebra expression which will require some additional or we can say that we are using the relational algebra expression that will provide the facility which is not able to express using basic operations. So, these are generalized projection and aggregation. So, it is an extension of relational algebra operations. So, first see generalized projection operation we have already seen projection where we are projecting attributes. Now, if you want to perform some arithmetic expression or some expression and get the value and then project it we can use generalized projection. Look at this form here F1, F2, Fn are the arithmetic expressions which will calculate some value based on constants or attributes from the schema of E where is any relational algebra expression. Let us look at the example. Now, consider this is a loan relation where there is loan amount and paid amount. For example, we want to see the outstanding amount then what we can do is that project loan ID account ID and loan amount minus paid amount as outstanding from loan relation. So, this is going to be the result of generalized projection. So, loan ID account ID and outstanding or consider that we are having the employee relation where there are salaries. We want to calculate the salary of average salary or monthly salary then what we can do is that we are having the complete salary. So, salary divided by 12 can be the expression used in this project operation. So, we can use arithmetic expressions for the projection in generalized projection. Next operation is aggregation operation. Now, what is aggregate functions? It takes the collection of values and return a single value. So, some average min, max, count are the aggregation functions. How to use it in relational algebra? So, general form of this aggregation function is that G. Now, this F1 of A1, F2 of A2 and so on are all the aggregate functions where on this left hand side G1, G2, Gn are the grouping attributes if these are optional. So, if you want to group that result by some attributes you can use group by as same in skill statements and number of operations that you are interested in on this relational algebra expression or the relation. So, look at this example, select some of the salary from employee. So, if you want to calculate for example, how much salary is paid monthly by the organization in that case we can use some aggregate function. So, equivalent relational algebra is G, sum of salary from employee. So, this is the sum aggregate function this is the attribute G is for generalized aggregation and then relation is employee. So, result is obviously the sum of all the salaries. Now, second we want department wise how many we want to see that how many how much salary is spent department wise. So, what will be the skill statement select department ID comma sum of salary from employee group by a department ID. So, equivalent relational algebra expression will be department ID is a group by attribute group by attribute G then sum of salary from employee and the result will be it will show you department wise salary. Now, this look at this query and pause the video and try to write down the relational algebra expression. Here we are interested in department wise salary and the number of persons or employees in that department. So, select department ID sum of salary count of star from employee group by department ID. So, again department ID G sum of salary is the first function count of star is a second function from employee and what will be the result? This is a salary department wise and number of persons in that particular department this will be the result. So, thus we can use any aggregation function in this relational algebra expression. Thank you.