GATE CSE 2021 Set 1 | Question: 27

Question

The following relation records the age of $500$ employees of a company, where $empNo$ (indicating the employee number) is the key:

$$empAge(\underline{empNo},age)$$

Consider the following relational algebra expression:

$$\Pi_{empNo}(empAge \Join_{(age>age1)} \rho_{empNo1,age1}(empAge))$$

What does the above expression generate?

• A. Employee numbers of only those employees whose age is the maximum
• B. Employee numbers of only those employees whose age is more than the age of exactly one other employee
• C. Employee numbers of all employees whose age is not the minimum
• D. Employee numbers of all employees whose age is the minimum

Answer 1

Correct Answer: C

Whenever a Database Problem intimidating like the above one(maybe it’s just me) appears, it’s often worth to Dissect the statements for Individual components and build up your arguments from there rather than attempting it head-on by some random example/argument only to get swayed by your hidden biases and choose the wrong answer.

---

Couple of Basic Ideas:

$\rho_{r1(x,y,\ldots)}$ is the rename operation here, it’s used to change the name of the $empAge’s$ attributes $empNo,age$ to $empNo1,age1$ to resolve potential conflicts that can arise while referring the relations’(the table) attributes(column) when using relations that might share a common attribute name.

$\Join_{<cond>}$ is a combination of $\sigma$ and $\times$ where we take the Cross Product at First between the two relations and apply the tuple select condition supplied to $\Join$ by using $\sigma$. So $\Join$ equals $\sigma_{<\text{cond}>}(A\times B)$

$\Pi_{\text{<attr>}}$ is a Column Select Operation in naive words, it’s supplied with attributes that needs to be selected.

A Relation contains only unique tuples unlike in conventional SQL Databases.

---

Now,

1. First the $\rho$ operator renames the $\text{RHS}$ relation to $empNo1,age1$.
    
2. We take the cross product of both the relations, each tuple in $A$(unmodified relation empAge) will be combined with every tuple in $B$(renamed relation empAge).
    
3. We filter the tuples according to the condition $age>age1$ which implies those tuples whose age values in $A$ that are greater than at least one of $B$ are selected. Since $A$ are $B$ are the same here only those values which aren’t the minimum are selected in $A$ are selected($>$).
    
4. We find out the set of unique $empNo$ by using Projection($\Pi$)(Note: $empNo$ derived from $\text{LHS}$ side of $\Join$ the original relation $A$  that we were talking about).

Since the $empNo$ is derived from relation $A(\text{LHS})$ whose age attribute is greater than the relation’s minimum implies employees from $A$ are selected whose age isn’t the minimum hence, Option C is true.

Also, if $empNo1$ was chosen instead of $empNo$ then it would list all the employee numbers whose age isn’t the maximum.

Comments

@Kanwae Kan can u explain a little bit more of point no. 3 and 4 . Especially I m not able to grasp point no.3 of the filtering part.

---

Assume that we’ve a set of numbers, we’ve to pick up numbers that are greater than at least one number in it which then will form a pair(in acc to ques). That pair is formed in 3 and we find out the number which is greater than at least 1 in point 4.

age>age1 in essence is the pair forming operation. and column select is the latter explained in 4

Happy Solving!

Answer 2

Answer: Option C

Let the Relation $empAge(\underline{empNo},age)$ be:-

$\underline{empNo}$	$age$
$1$	$40$
$2$	$40$
$3$	$60$
$4$	$20$

 

Given Query: $\Pi_{empNo}(empAge \Join_{(age>age1)} \rho_{empNo1,age1}(empAge))$

After Rename operation : ($ \rho_{empNo1,age1}(empAge))$) :-

$\underline{empNo1}$	$age1$
$1$	$40$
$2$	$40$
$3$	$60$
$4$	$20$

 

Now,

$empAge \Join_{(age>age1)} \rho_{empNo1,age1}(empAge)$

= $\sigma_{<\text{(age>age1)}>}(empAge\times \rho_{empNo1,age1}(empAge))$

 

$\underline{empNo}$	$age$	$\underline{empNo1}$	$age1$
$1$	$40$	$4$	$20$
$2$	$40$	$4$	$20$
$3$	$60$	$1$	$40$
$3$	$60$	$2$	$40$
$3$	$60$	$4$	$20$

$\Pi_{empNo}(empAge \Join_{(age>age1)} \rho_{empNo1,age1}(empAge))$:-

 

$\underline{empNo}$

$1$

$2$

$3$

 

Hence we get Employee numbers of all employees whose age is not the minimum. Option C.

Comments

Resulting table after join will have one more row for age 60 and age1 20

empNo	age	empNo1	age1
3	60	4	20

 

---

@GateAgg laga Corrected now.

---

@Abhrajyoti00 da, for this type of question what should we do for solving→

1. use small example OR
2. By some thinking or rule applied

Answer 3

Answer : (C) Employees numbers of all employees whose age is not the minimum.

Comments

What does p mean here

---

I think you are reffering to the symbol named rho.It is called RENAME operator.It allows to rename the output relation.

---

Pls explain this query symbolically..

---

It’s a rename operator, mathematically called as ‘rho’. It’s used to rename attributes of a relation for our convenience. 

reference : https://www.geeksforgeeks.org/rename-operation-in-relational-algebra/

 

Answer 4

 

pls upvote if you understand my solution :)