2.6k views

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.
edited | 2.6k views
–1

(D) is partially true, because, if there are such employees who don't have any dependent, they will also be selected in the final result set.

0

Assume that every employee has at least one associated dependent in the dependent relation.

This is mentioned in the question.

0

0
Why have we used his/her?

(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
0
What's wrong in option B.Its also selecting all dependents value.
+15
if $empId=eID$ this condition won't be there in question, then answer will be B. But in this case if any employee E1 is selected that means E1 age is greater than all of E1's dependent, E1 age might be smaller/greater than E2's dependent.
0
empId=eID means all those who are dependent of same employee
0
@Arjun sir What is the diff between in option B and D is not correct?
+1
Option B means the employees who are senior than all dependents.

Option D means that employees who are senior than only their dependents.      (when empid=eid).
0
sir can you explain with an  example actully i m not able to understand
0
sachin mittal sir

can you explain what is the differnece between b and d.?
+2

employee:

 empid empAge e1 20 e2 50 e3 40

dependent:

 did eid dAge d1 e1 30 d2 e2 35 d3 e2 60 d4 e3 35 d5 e3 30

inner query returns (e1,e2)

subtracting from emp gives e3, whose age is greater than all his/her dependents.

0

@Sachin Mittal 1 sir,

Is this right

set of empIds of employees whose age is greater than that of

1.some dependent.

$\prod$ empId ( employee ⋈ ( empAge > depAge ) dependent )

2.all dependents.

$\prod$empId(employee) − $\prod$ empId ( employee ⋈ (empAge ≤ depAge ) dependent )

3.some of his/her dependents.

$\prod$empId(employee ⋈ ( empId = eID ) ∧ ( empAge > depAge ) dependent )

4.all of his/her dependents.

$\prod$empId(employee) − $\prod$ empId ( employee ⋈ ( empId = eID ) ∧ ( empAge ≤ depAge ) dependent

0
thanks
0

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.

(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"

+3

Procedure is correct. But
"B select all the empID with age which is less than all his dependends"

is wrong. Select statement returns empID even if it is true for at least one of the dependent. So, in B it is "some" and in A-B it will be "All"

0
@Arjun Sir.. Will the given query also return employees who have no dependent?
0
@anchit they explicitly mentioned in the question that each emp has at least one depenedent. right?
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..

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.

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

1
2