2.3k views

Given the relations

• employee (name, salary, dept-no), and

Which of the following queries cannot be expressed using the basic relational algebra operations $\left(\sigma, \pi,\times ,\Join, \cup, \cap,-\right)$?

1. Department address of every employee
2. Employees whose name is the same as their department name
3. The sum of all employees' salaries
4. All employees of a given department

edited | 2.3k views
Ordering, Outer Join, Aggregation and Group of Data is not possible via Basic Relational Algebra.

possible solutions , relational algebra

(a) join relation using attribute dpart_no.

or

address (σ emp.depart_no.=depart.depart_no. (emp X depat))

(b)     ∏name (σ emp.depart_no.=depart.depart_no. ∧ emp.name = depart.depart_name (emp X depat))

or

∏name (emp ⊠emp.name = depart.depart_name depart)

(d) Let given department number is = 'x'

∏name (σ emp.depart_no.=depart.depart_no. ∧ depart_no. = 'x' (emp X depat))

or

∏name (emp ⊠depart_no.='x' depart)

(c) but we can't generate relational algebra of aggregate function using basic operation , so we need extended operation here , option (c) is false .

reshown
aggregate functions are not supported by relational algebra ie. sum,average,maximum,minimum and count.So c is the answer
aggregate function are supported by RA. It is extended RA Operation.
No,you can implement minimum and maximum using basic operations.
@ prayas ,yes min max can implement but not the sum operation..

SQL Query without aggregation but as said by @reena_kandari can't do sum operation!

SELECT DISTINCT x FROM table WHERE x NOT IN (SELECT table1.c FROM table AS t1 JOIN table AS t2 ON t1.c < t2.c)

@ashutoshaay26  what's the output of this query?

Minimum of table 1 of an attribute c.
(b)

we need to do a self join, for which we need rename operator(row symbol)
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 ?
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