The GATE Grind

GATE 2017 CS (CS1) – Question 51

Databases · Relational Model: Relational Algebra, Tuple Calculus, SQL · 2 marks · Multiple choice

Consider a database that has the relation schemas EMP(EmpId, EmpName, DeptId), and DEPT(DeptName, DeptId). Note that the DeptId can be permitted to be NULL in the relation EMP. Consider the following queries on the database expressed in tuple relational calculus.

(I) $\{t \mid \exists u \in EMP(t[EmpName] = u[EmpName] \wedge \forall v \in DEPT(t[DeptId] \neq v[DeptId]))\}$

(II) $\{t \mid \exists u \in EMP(t[EmpName] = u[EmpName] \wedge \exists v \in DEPT(t[DeptId] \neq v[DeptId]))\}$

(III) $\{t \mid \exists u \in EMP(t[EmpName] = u[EmpName] \wedge \exists v \in DEPT(t[DeptId] = v[DeptId]))\}$

Which of the above queries are safe?

  1. (I) and (II) only
  2. (I) and (III) only
  3. (II) and (III) only
  4. (I), (II) and (III)

Practise this question in The GATE Grind →

Show answer and explanation

Correct answer: (D) (I), (II) and (III)

Explanation

Under ordinary domain-independent tuple relational calculus, tying t[EmpName] to an EMP name does not also bind t[DeptId] to an EMP department value.

I allows department values unequal to every DEPT value; II allows values unequal to at least one DEPT value. Either can return values outside the active domain. For example, with one EMP name and one department numbered 1, both allow arbitrary department values 2,3,4,... .

III instead equates t[DeptId] to a value from DEPT and binds the name to EMP, so its result is finite and domain-independent. Only III is safe under this definition, but no offered option says so.

The retained historical answer D therefore relies on an implicit active-domain restriction. It must not be justified by claiming that every component of t is bound to an EMP tuple.

More Relational Model: Relational Algebra, Tuple Calculus, SQL questions

All Relational Model: Relational Algebra, Tuple Calculus, SQL questions →