### A division query in Relational Calculus

• Division query in Relational Calculus (horror !!!) --- I will not ask question on division queries in Relational Calulus

• Query:

 Find the fname and lname of employees who work on all the projects controlled by department 5

Solution from txt book (ElMasri & Navathe): horror....

(This is "for your eyes" only - don't need to understand it....)

 ``` { e.fname, e.lname | Employee(e) // e is an Employee tuple and ( (∀p ( not (Project(p)) // p is a Project tuple or not (p.dnum=5) or ( (∃w) ( Works_on(w) and w.essn=e.ssn and p.pnumber=w.pno ) ) ) ) } ```

Re-written:

 ``` { e.fname, e.lname | Employee(e) // e is an Employee tuple and (∀p not ((Project(p)) // p is a Project tuple and (p.dnum=5) and ( NOT (∃w) ( Works_on(w) and w.essn=e.ssn and p.pnumber=w.pno ) ) ) } ```

Note: if you're interested, here is the explanation....

• We first write the solution of the query in a "meta" language:

 ``` { e.fname, e.lname | Employee(e) and "set of projects controlled by dept 5" (set A) ⊆ "set of projects worked on by e.ssn" (set B) } ```

Clearly: when the "set of projects controlled by dept 5" is a subset of "set of projects worked on by the employee e.ssn", then:

 The employee e.ssn works on all projects controlled by dept 5

• From set theory: A ⊆ B <==> A − B = ∅:

 ``` { e.fname, e.lname | Employee(e) and ( "set of projects controlled by dept 5" (set A) − "set of projects worked on by e.ssn" ) (set B) = ∅ } ```

• If a set is empty, then it is not possible to find an element in that set:

 ``` { e.fname, e.lname | Employee(e) and not (∃x) ( x ∈ "set of projects controlled by dept 5" − "set of projects worked on by e.ssn" ) } { e.fname, e.lname | Employee(e) and not (∃x) ( Project(x) and x ∈ { projects controlled by dept 5 } and x ∉ { projects worked on by e.ssn } ) } **** Now apply the inversion rule on the not expression (see below) ! { e.fname, e.lname | Employee(e) and (∀x) not ( Project(x) and x ∈ { projects controlled by dept 5 } and x ∉ { projects worked on by e.ssn } ) } { e.fname, e.lname | Employee(e) and (∀x) not ( Project(x) and x.dnum=5 and x ∉ { projects worked on by e.ssn } ) } x ∉ { projects worked on by e.ssn } means: There is no a single project that exists that is worked on by e.ssn Which is this: { e.fname, e.lname | Employee(e) // e is an Employee tuple and (∀x not ((Project(x)) // x is a Project tuple and (x.dnum=5) and ( NOT (∃w) ( Works_on(w) and w.essn=e.ssn and x.pnumber=w.pno ) ) ) } ```

• Note: the inversion rule in Theory of Logic is as follows:

 ``` not (∃x) ( Condition(x) ) <==> (∀x) ( not Condition(x) ) Example: not (∃x) ( x = dodo ) (in other words: "x is dodo" is FALSE i.e.: there are NO dodo's !!! <==> (∀x) ( not ( x = dodo ) ) i.e.: for every x: x cannot be a dodo !!! ```

• Expressive power of Relational Algebra and Relational Calculus

• Expressive power of a query language:

 Expressive power of a query language = the set of all queries that can be written using that query language

• Comparing different query languages:

• A query language A is more expressive than a query language B if:

 The set of all queries than can be written in A is a superset of the set of all queries than can be written in B

• Comparing Relational Algebra and Relational Calculus:

 Relational Algebra and Relational Calculus are equally expressive (No proof).