The Gateway to Computer Science Excellence
First time here? Checkout the FAQ!
x
+21 votes
2.3k views

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)$?

  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

 

asked in Databases by Veteran (68.8k points)
edited by | 2.3k views
Ordering, Outer Join, Aggregation and Group of Data is not possible via Basic Relational Algebra.

3 Answers

+23 votes
Best answer

possible solutions , relational algebra 

(a) join relation using attribute dpart_no.

 ∏address (emp ⊠ depart)

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 .

 

answered by Boss (5.7k points)
reshown by
+11 votes
aggregate functions are not supported by relational algebra ie. sum,average,maximum,minimum and count.So c is the answer
answered by Active (1.3k points)
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.
–4 votes
(b)

we need to do a self join, for which we need rename operator(row symbol)
answered by Loyal (3.4k points)
what about sum?
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


Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true

32,330 questions
39,146 answers
108,244 comments
36,501 users