in Databases edited by
13,466 views
61 votes
61 votes

Consider the relational schema given below, where eId of the relation dependent is a foreign key referring to empId of the relation employee. Assume that every employee has at least one associated dependent in the dependent relation.

employee (empId, empName, empAge)

dependent (depId, eId, depName, depAge)

Consider the following relational algebra query:

$\Pi_{empId}\:(employee) - \Pi_{empId}\:(employee \bowtie_{(empId=eID) \wedge (empAge \leq depAge)} dependent)$

The above query evaluates to the set of empIds of employees whose age is greater than that of

  1. some dependent.
  2. all dependents.
  3. some of his/her dependents.
  4. all of his/her dependents.
in Databases edited by
13.5k views

4 Comments

Because (empId=eID) is a join condition.

0
0
his/her, empAge is greater than “his/her” dependents only. //Local
non his/her, empAge is greater than all of the dependents’ age. //Global
2
2
God dammn what a word play … Legit gave me goosebumps !!
0
0

3 Answers

63 votes
63 votes
Best answer

(D) all of his/her dependents.

The inner query selects the employees whose age is less than or equal to at least one of his dependents. So, subtracting from the set of employees, gives employees whose age is greater than all of his dependents.

edited by
by

4 Comments

thanks
0
0

@92komal @Brij Mohan Gupta @Shubham Aggarwal "eId of the relation dependent is a foreign key referring to empId of the relation employee" from this line of the question we can distinguish between option (B) and option (D) see how:

We know that in foreign key something can be Missed or Repeated (i.e with respect to primary key obviously) but nothing can be added extra (i.e keeping in mind referential integrity constraint).

So, take the case of repeated values in foreign key. You can easily get option (D) is correct.

Please mention if this way is wrong.

 

0
0

solved by taking an example.
Hope it helps.
 

 

1
1
0 votes
0 votes

(C) some of his/her dependents

consider the qN as A-B

B select all the empID with age which is less than all his dependends

so subtracting above from the universal set give the opposite of which is "empID with age greater than atleast one of his dependents"

4 Comments

@anchit they explicitly mentioned in the question that each emp has at least one depenedent. right?
0
0

manu00x I understand that, but if it were possible that employee may or may not have a dependent then what would be the answer..

0
0

C is wrong because we have already removed all those empid’s from the relation having atleast one dependent of age > employee’s age ..and in the result we are displaying only those empid’s whose age is greater than all of his/her dependents.

1
1
0 votes
0 votes

The below subquery after the subtraction sign produces id's of those employees who have at least one dependent with age greater than or equal the employee's age.

GATECS2014Q40

When the result of above subquery is subtracted from all employees, we get the employees whose age is greater than all dependents.

Answer:

Related questions