43 votes 43 votes Given the relations employee (name, salary, dept-no), and department (dept-no, dept-name,address), Which of the following queries cannot be expressed using the basic relational algebra operations $\left(\sigma, \pi,\times ,\Join, \cup, \cap,-\right)$? Department address of every employee Employees whose name is the same as their department name The sum of all employees' salaries All employees of a given department Databases gatecse-2000 databases relational-algebra easy isro2016 + – Kathleen asked Sep 14, 2014 • edited Jul 3, 2016 by ManojK Kathleen 15.0k views answer comment Share Follow See all 2 Comments See all 2 2 Comments reply Chhotu commented Dec 23, 2017 reply Follow Share Ordering, Outer Join, Aggregation and Group of Data is not possible via Basic Relational Algebra. 4 votes 4 votes satyaAchar commented Jan 29 reply Follow Share Extended relational algebra operations: Aggregate function: sum,average,max,min,count,count-distinct syntax: ℱ<function-list> (R) for this : ℱ sum salary (employee) 0 votes 0 votes Please log in or register to add a comment.
Best answer 50 votes 50 votes Possible solutions, relational algebra: (a) Join relation using attribute dpart_no. $\Pi_{\text{address}} (\text{emp} \bowtie \text{depart})$ OR $\Pi_{\text{address}} (\sigma_{ \text{emp}.\text{depart_no.}=\text{depart}.\text{depart_no}.} (\text{emp} \times \text{depart}))$ (b) $\Pi_{\text{name} } (\sigma_{\text{emp}.\text{depart_no.}=\text{depart}.\text{depart_no.} \wedge \text{emp.name} = \text{depart}.\text{depart_name}} (\text{emp} \times \text{depart}))$ OR $\Pi_{\text{name}} (\text{emp} \bowtie _{ \text{ emp.name} = \text{depart}.\text{depart_name}} \text{depart})$ (d) Let the given department number be $x$ $\Pi_{\text{name}} (\sigma_{ \text{emp}.\text{depart_no.}=\text{depart}.\text{depart_no.} \wedge \text{depart_no.} = x} (\text{emp} \times \text{depart}))$ OR $\Pi_{\text{name}} (\text{emp} \bowtie _{\text{ depart_no}.=x} \text{depart}) $ (c) We cannot generate relational algebra of aggregate functions using basic operations. We need extended operations here. Option (c). Mithlesh Upadhyay answered Apr 24, 2015 • edited Jun 28, 2018 by Arjun Mithlesh Upadhyay comment Share Follow See all 5 Comments See all 5 5 Comments reply Show 2 previous comments sanjaysharmarose commented Oct 18, 2020 reply Follow Share I think no need of join in option D as both employee name and dept_no is in same table. This can be used instead: projection of name(selection on dept_no = x(emp)) 3 votes 3 votes mohit7891 commented Oct 18, 2023 reply Follow Share how D option is not correct? suppose if the name of two employee is same for a given department(x), than we can not get both same name employees, so by this conclusion we can also not get all employee for the given department. please Correct me. 0 votes 0 votes Shukla_ commented Nov 25, 2023 reply Follow Share 2nd part of Ans(b) is wrong i.e the query written under OR. There should be another condition that is employee.dept-no=department.dept-no. Also in ans(d) if department no is given as x then we can find all employees of a given department using employee table only by selecting tuples where dept-no = x and then projecting name 0 votes 0 votes Please log in or register to add a comment.
18 votes 18 votes aggregate functions are not supported by relational algebra ie. sum,average,maximum,minimum and count.So c is the answer shikhar_deep05 answered Aug 26, 2016 shikhar_deep05 comment Share Follow See all 7 Comments See all 7 7 Comments reply Show 4 previous comments suvasish pal commented Sep 12, 2017 reply Follow Share @ashutoshaay26 what's the output of this query? 0 votes 0 votes ashutoshaay26 commented Sep 13, 2017 reply Follow Share Minimum of table 1 of an attribute c. 1 votes 1 votes Venky8 commented May 6, 2021 reply Follow Share For anyone wondering how min, max can be found out by RA with basic operations. See https://stackoverflow.com/questions/5493691/how-can-i-find-max-with-relational-algebra. But sum operation cannot be found out by RA by using just basic operations. 0 votes 0 votes Please log in or register to add a comment.
–5 votes –5 votes (b) we need to do a self join, for which we need rename operator(row symbol) Aravind answered Sep 25, 2014 Aravind comment Share Follow See all 3 Comments See all 3 3 Comments reply Arjun commented Sep 25, 2014 reply Follow Share what about sum? 0 votes 0 votes Aravind commented Sep 26, 2014 i edited by Aravind Sep 26, 2014 reply Follow Share i think with select operator we can calculate sum and sum is not a relation operator Where can i find the keys of all gate paper ? 0 votes 0 votes Arjun commented Sep 26, 2014 reply Follow Share No. sum is an aggregate operator. Simply with select, this cannot be done. Only keys from 2012 are published. Before that many people have given keys, but they may not be authentic. You can do a google search for this. For 2012-14 you can see here: http://gatecse.in/wiki/Previous_Year_GATE_Question_Papers_and_Keys 6 votes 6 votes Please log in or register to add a comment.