retagged by
6,915 views
16 votes
16 votes

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
retagged by

4 Answers

Best answer
28 votes
28 votes

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.

selected by
6 votes
6 votes

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.

edited by
4 votes
4 votes
Answer : (C) Employees numbers of all employees whose age is not the minimum.
3 votes
3 votes

 

pls upvote if you understand my solution :)

Answer:

Related questions