Consider a database that has the relation schemas EMP(EmpId, EmpName, DeptId),…
2017
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\) | ∃u ∈ EMP(t[EmpName] = u[EmpName] ∧ ∀v ∈ DEPT(t[DeptId] ≠ v[DeptId]))}
II. {\(t\) | ∃u ∈ EMP(t[EmpName] = u[EmpName] ∧ ∃v ∈ DEPT(t[DeptId] ≠ v[DeptId]))}
III. {\(t\) | ∃u ∈ EMP(t[EmpName] = u[EmpName] ∧ ∃v ∈ DEPT(t[DeptId] = v[DeptId]))}
Which of the above queries are safe?
- A.
I and II only
- B.
I and III only
- C.
II and III only
- D.
I, II and III
Attempted by 100 students.
Show answer & explanation
Correct answer: D
Answer: Only query III is safe.
Reasoning:
Query I: The formula requires the tuple's EmpName to match some existing employee but requires the tuple's DeptId to be different from every DeptId in DEPT. EmpName is therefore drawn from a finite set (values in EMP), but DeptId can be any value not occurring in DEPT. Since there are potentially infinitely many values not in DEPT, the query can produce infinitely many tuples and is unsafe.
Query II: The formula requires the tuple's EmpName to match some existing employee and that there exists some DeptId in DEPT different from the tuple's DeptId. This condition still does not restrict the tuple's DeptId to a finite set (many values will differ from at least one DEPT DeptId), so infinitely many tuples can satisfy the formula. Therefore the query is unsafe.
Query III: The formula requires the tuple's EmpName to match an existing employee and its DeptId to equal some DeptId present in DEPT. Both EmpName and DeptId are thus drawn from finite sets (values present in EMP and DEPT), so the result set is finite and the query is safe.
A video solution is available for this question — log in and enroll to watch it.