3.9k 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 | 3.9k views
+1
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.

• $\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).

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

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

1. Employees whose name is the same as their department name
aggregate functions are not supported by relational algebra ie. sum,average,maximum,minimum and count.So c is the answer
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.
(b)

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

1
2