 Hello friends. Today we will see tuple relational calculus. So, learning outcome for this session is students will be able to write query using tuple relational calculus. Now, before we have seen relational algebra where we have written the sequence of procedures that is used for generating answer. Now, in tuple relational calculus it is non-procedural language. It describes the design information without giving a specific procedure for obtaining that information. Now, how to write a query in tuple relational calculus? So, it is in the form of t where p of t means it will give you set of all the tuples t such that predicate p is true for that tuple t. So, now, we will be using these notations t of a means that denotes a tuple t on attribute a which means that the value of attribute a in that particular tuple. And second is t is element of r means that tuple t exist in the relation r. Now, we will be using these two relations employee relation and department relation m. Now, consider the query that we want to find the information of employee whose salary is greater than 80000. So, we want to find id, name, department and salary. So, it suggests a like a description of say it. So, t where t that tuple exist in employee and the tuple or the value of salary is greater than 80000. So, it will list all the tuples where salary is greater than 80000. Now, here we have taken all the attributes. Now, suppose we want only id then we have to work only on the attribute id. So, in that case we are using one notation that exist. So, this symbol is nothing, but there exist clause. So, there exist t in relation r such that the value or the predicate q is true for that particular tuple. So, let us see with example find employee id for each employee with salary greater than 80000. Now, it is the same query only what we want is that employee id. So, how we will write it is that tuple t such that there exist tuples s in employee the id of this tuple t and this tuple s should be same and obviously the salary should be greater than 80000. Now, consider the next query find the names of all employees where department is in main building. Now, this is the schema that we know. So, pause for a moment and try to write down the query for this. Find names of employees. So, name is available in employee whose department. So, department is also available in employee, but building building is not available in employee here it is in department. So, we require these two tables employee and department. So, we need now the two relations and two there exist. So, what is first exist that is that is required for employee relation and second exist is required for department. So, how will be the query now? Tuple t where there exist some tuple s in employee relations such that the t of name is equal to s of name and for some u that there exist tuple u in department such that department u of department name and is equal to s of department name and u of building is equal to main. So, this will first list some employees such that their department id will be retrieved and that for that department id building should be main. So, obviously this is going to be the result for this query. So, remember that first try to identify the relations how many relations and which attribute we are interested in and then write down this query in using TRC. Now, consider this relation courses taught. Here it is at year class id, course id and teacher id. Now, what is query? Find courses taught by ARD in 2015, 2018 or both. Again pause the video and now try to write down the query in tuple relational calculus. So, we have already seen in the previous lecture that these are the subqueries which we require. Find courses taught by ARD in 2015, find courses taught by ARD in 2018 and then we need to take union. So, you must have written the same query that tuple t where there exist tuple s in courses taught such that t of course id is equal to s of course id and s of teacher id is ARD because we want the teacher ARD and also year is equal to 215. This is one subquery for this first one. The other query is what? Again for some u or tuple u exist in courses taught such that u of course id is equal to t of course id, u of teacher id is equal to ARD and u of year is equal to 2018. So, these two queries that we are finding the same and then we are taking this union operation for this. It means that it will give you all the tuples which is having which has been taught in 2015, in 2018 or both. So, result is the same that course first recorded is DC, second is DC and CN and obviously the result will be DC. Since it is sets, set duplicates are removed. Now again same relation now, but the query is that find courses taught by ARD in both 2015 and 2018. So, we know the set operation that we require intersection. So, queries will be same only or will be replaced by this and that we want to find the courses of 2015 and 2018. So, this is going to be the intersection operation in tuple relational calculus result. Now the intersection will be only DC and this we are getting it as an intersection result. Next query is that find courses taught by ARD in 2018, but not in 2015. Same subqueries. Now here what we are writing is that we are eliminating the records. So, this is going to this notation will tell you that eliminate the records. So, what we are doing first is that for some tuple S in courses taught, T of course ID and S of course ID are same, S of teacher ID is equal to ARD and S of year is equal to 2018. So, these courses will be retrieved. Now there again for some tuple U in courses taught such that U of course ID is equal to T of course ID, U of teacher ID is equal to ARD and U of year is equal to 2015. So, all the courses retrieved from this query should be eliminated from this particular set and this is going to give you the result that that the courses taught only in 2018. So, first these two are taught in 18, second this is taught in 15. So, removing this we are getting only the result as Cf. Now, just remember in mathematical logic P implies Q means what if P is true then Q must be true. Now, consider this schema there are three relation, course, student and text. So, now the query is that find all students who have taken all courses offered in biology department. Now, we have seen the there exists clause. Now, we need here for all clause over here or Q is true for all the tuples T in relation R. Now, let us see how it has been written in tuple relational calculus. Find all the students who have taken all the courses offered in biology department. Now, here three relations are involved. So, for some relation R now some students have taken this particular courses in the biology department. So, some relation for some tuple R in student R of ID is equal to T of ID and for all the courses in that department. So, U of department is equal to biology implies or should imply that for some tuple in text relation that T of ID should be S of ID and S of course ID is equal to U of course ID. See, you can write down the natural giant operation to visualize these things if you know SQL statement that we are taking three relations student, course and text whereas student ID and text ID here it is the common attribute where here department name is the common attribute that we are using over here. So, department name obviously is filtered by biology but the text that student ID should be equal to S of ID which we have already selected from this and course ID should be equal to this S of course ID should be equal to U of course ID. Thus, we can use here implies. Now, what do you mean by the safety expressions? A tuple relational calculus expression may generate infinite relation. Now, what is the example here? Remember that T for eliminate all the tuples where that T is exist in employee what it will generate? So, assume that in employee we are having thousand records. So, all the tuples which is except that thousand records will be eliminated. So, all the tuples which are not present in employee will be the result and that can be infinite. So, this is going to be a this is not a safety expression. So, again we will define the safety expression next. So, first we will see domain. What is domain of P? All the values referenced by P or now that values can be P itself if you are using constant in this particular expression or all the values that are occurring in the tuple which are mentioned in P. Let us say with example. Now, T the T tuple T is in employee and T of salary is greater than 80,000. So, here we are mentioning the value as 80,000 that it will be there in the domain, but employees having four attributes employee ID employee name department ID and salary. So, all the values of these attributes and this 80,000 will be the domain of T. Remember that it is not a single value all the values that are referencing over here will be in the domain. So, let us pause the video and tell me what will be the domain of this eliminate where T is in employee. So, here we are not finding tuples here we are finding domain. So, domain will have all the values appearing in employee remember that we are not retrieving tuples and just eliminating we are adding domains. So, domain will contain all the values appearing in employee. So, employee assume that there are four attributes and there are 10 records or 10 tuples. So, all the 40 values with duplication removal there will be the domain of this particular expression. So, when you call the expression is safe if all the values that appear in the result are from only domain it means that if it is in if it means that this T where we have written this expression which is giving you infinite results is not safe. So, the number of tuples that satisfy unsafe expression could be infinite and the expression which is generating finite result will be safe expressions and in tuple relational calculus we are adding safe expressions. So, again tuple relational calculus restricted to safe expression is equivalent to same relational algebra expression, but using only the basic operations we are not taking the extended operations like generalized projection or aggregation. So, for every relational algebra using basic operations can be converted into tuple relational calculus and vice versa. So, here we have seen this non-procedural language that tuple relational calculus and how to write the query in that tuple relational calculus.