GATE CSE 2000 | Question: 1.23, ISRO2016-57

Question

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

• A. Department address of every employee
• B. Employees whose name is the same as their department name
• C. The sum of all employees' salaries
• D. All employees of a given department

Comments

Ordering, Outer Join, Aggregation and Group of Data is not possible via Basic Relational Algebra.

---

Extended relational algebra operations: 

Aggregate function: sum,average,max,min,count,count-distinct

syntax:  ℱ<function-list> (R)

for this : ℱ sum salary (employee)

 

Answer 1

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

Comments

I think no need of join in option D as both employee name and dept_no is in  same table.

This can be used instead:  projection of name(selection on dept_no = x(emp))

 

---

how D option is not correct?
suppose if the name of two employee is same for a given department(x), than we can not get both same name employees, so by this conclusion we can also not get all employee for the given department. please Correct me.

---

2^nd part of Ans(b) is wrong i.e the query written under OR. There should be another condition that is employee.dept-no=department.dept-no. Also in ans(d) if department no is given as x then we can find  all employees of a given department using employee table only by selecting tuples where dept-no = x and then projecting name

Answer 2

aggregate functions are not supported by relational algebra ie. sum,average,maximum,minimum and count.So c is the answer

Comments

@ashutoshaay26  what's the output of this query?

---

Minimum of table 1 of an attribute c.

---

For anyone wondering how min, max can be found out by RA with basic operations.
See https://stackoverflow.com/questions/5493691/how-can-i-find-max-with-relational-algebra.
But sum operation cannot be found out by RA by using just basic operations.

Answer 3

(b)

we need to do a self join, for which we need rename operator(row symbol)

Comments

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