# GATE CSE 2021 Set 1 | Question: 27

0 votes
569 views

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?

1. Employee numbers of only those employees whose age is the maximum
2. Employee numbers of only those employees whose age is more than the age of exactly one other employee
3. Employee numbers of all employees whose age is not the minimum
4. Employee numbers of all employees whose age is the minimum

recategorized

## 2 Answers

2 votes

Best answer

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.

ago
selected ago by
3 votes
Answer : (C) Employees numbers of all employees whose age is not the minimum.
0
What does p mean here
0
I think you are reffering to the symbol named rho.It is called RENAME operator.It allows to rename the output relation.
0
Pls explain this query symbolically..
Answer:

## Related questions

5 votes
2 answers
1
1.1k views
Suppose a database system crashes again while recovering from a previous crash. Assume checkpointing is not done by the database either during the transactions or during recovery. Which of the following statements is/are correct? The same undo and redo list ... any further All the transactions that are already undone and redone will not be recovered again The database will become inconsistent
3 votes
2 answers
2
1.8k views
A relation $r(A, B)$ in a relational database has $1200$ tuples. The attribute $A$ has integer values ranging from $6$ to $20$, and the attribute $B$ has integer values ranging from $1$ to $20$. Assume that the attributes $A$ and $B$ are independently distributed. The estimated number of tuples in the output of $\sigma _{(A>10)\vee(B=18)}(r)$ is ____________.
3 votes
3 answers
3
646 views
Let $r_i(z)$ and $w_i(z)$ denote read and write operations respectively on a data item $z$ by a transaction $T_i$. Consider the following two schedules. $S_1: r_1(x)r_1(y)r_2(x)r_2(y)w_2(y)w_1(x)$ ... $S_2$ is conflict serializable Both $S_1$ and $S_2$ are conflict serializable Niether $S_1$ nor $S_2$ is conflict serializable
1 vote
5 answers
4
996 views
Consider the relation $R(P,Q,S,T,X,Y,Z,W)$ with the following functional dependencies. $PQ\rightarrow X;\quad P\rightarrow YX;\quad Q\rightarrow Y; \quad Y\rightarrow ZW$ Consider the decomposition of the relation $R$ into the constituent relations according to ... , but $D_2$ is a lossless decomposition Both $D_1$ and $D_2$ are lossless decompositions Both $D_1$ and $D_2$ are lossy decompositions