The Gateway to Computer Science Excellence
First time here? Checkout the FAQ!
x
+24 votes
3.4k 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 (59.6k points)
edited by | 3.4k views
+1
Ordering, Outer Join, Aggregation and Group of Data is not possible via Basic Relational Algebra.

3 Answers

+28 votes
Best answer

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).

answered by Loyal (6.1k points)
edited by
+12 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.4k points)
0
aggregate function are supported by RA. It is extended RA Operation.
0
No,you can implement minimum and maximum using basic operations.
+1
@ prayas ,yes min max can implement but not the sum operation..
0

       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)

0

@ashutoshaay26  what's the output of this query?

0
Minimum of table 1 of an attribute c.
–5 votes
(b)

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

41,011 questions
47,631 answers
147,057 comments
62,353 users