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

3 Answers

+32 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 (6k points)
edited by
0

@Mithlesh Upadhyay sir  @Arjun sir  @srestha mam  plz explain one thing.

In option (b) the following alternative query was used 

Πname(σemp.depart_no.=depart.depart_no.∧emp.name=depart.depart_name(emp×depart))

my question is , USING Selection operator do we need to include depart_no. as well when the sole aim is to find out the equality of

name of empl and dept name. ..???

 

+1

@SomeEarth emp.depart_no.=depart.depart_no. is included because of this statement:

  1. Employees whose name is the same as their department name
+13 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
Answer:

Related questions

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
47,925 questions
52,325 answers
182,361 comments
67,788 users