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

@BharatKumar, Basic Relational Algebra doesn't have the concept of NULL values. So, using NULL values itself is part of Extended Relational Algebra. See HERE.

Detailed Video Solution: https://www.youtube.com/watch?v=h3pJZbed9M8&t=2082s

---

Aggregate functions are not supported by relational algebra example sum,avg, max,min,count.
(c)

---

Doubt:

in option A,we have to get dept address of EVERY employee but if dept-no(in employee) is null then will not get their "department address" ?? 

dept no in employee is Foriegn key => it can be null

Answer 1

Aggregate functions are not supported by relational algebra example sum,avg, max,min,count.
(c)

Answer 2

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

@Arjun sir In option C, why are we doing a natural join of the EMP. and DEPT. tables? In the EMP table, E.name and D.no are both there, so directly we can use the EMP table only. 

---

@Mani_Raushan I think you meant in option D, also your doubt makes sense i think we should be able to do it without Department relation, i.e.

project employee.name (select employee where employee.dept_no = give_dept_no)

[NOTE : the above query is just a logical description of what a corresponding relational expression look like and not a sql query kind of thing]

correct me if i am wrong 

---

Why are we assuming name as candidate key? We should use projection of all attributes for options b and d cuz employee name is not asked

Answer 3

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 4

in option c we need aggregate function but in relational algebra there is no any aggregate function thats why it is impossible to express

 

Answer 5

(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