Sample queries in Relational Calculus

• Join Queries in Relational Calculus

• Query 1:

 Find fname and lname of employees who work for the Research department

Relational Calculus query:

 ``` { e.fname, e.lname | Employee(e) // e is an employee and (∃d) ( Department(d) // d is a department tuple and d.dname = 'Research' // d is the Research department and d.dno = e.dno // e is an employee in R. dept ) } ```

Execution of the Relational Calculus using a concrete example:

• Sample database:

 ``` Employee: Department: fname lname dno salary dnumber dname +------+-----+----+-------+ +-------+----------+ | John | Doe | 1 | 30000 | | 1 | Payroll | +------+-----+----+-------+ +-------+----------+ | Ian | Red | 4 | 40000 | | 4 | Research | +------+-----+----+-------+ +-------+----------+ | Jane | Kit | 1 | 50000 | +------+-----+----+-------+ ```

Execution:

 ``` for ( e := every tuple in the database ) do for ( d := every tuple in the database ) do { if ( Employee(e) and (∃d) ( Department(d) // d is a department tuple and d.dname = 'Research' // d is the Research department and d.dno = e.dno // e is an employee in R. dept ) ) { output e.fname, e.lname; } } ```

Only the following tuple will make the condition true:

 ``` fname lname dno salary dnumber dname +------+-----+----+-------+ +-------+----------+ | John | Doe | 1 | 30000 | | 1 | Payroll | +------+-----+----+-------+ +-------+----------+ e--> | Ian | Red | 4 | 40000 | | 4 | Research | <-- d +------+-----+----+-------+ +-------+----------+ | Jane | Kit | 1 | 50000 | +------+-----+----+-------+ ```

Therefore, the output of the query using the sample database is:

 ``` Ian Red ```

• Alternate Explanation:

• The expression

 ``` (∃d) ( Department(d) // d is a department tuple and d.dname = 'Research' ) // d is the Research department ```

is true for the tuple d belonging to the Research department

• Then the expression:

 ``` (∃d) ( Department(d) // d is a department tuple and d.dname = 'Research' // d is the Research department and d.dno = e.dno // e is an employee in R. dept ) ```

is true only when:

 ``` e.dno = d.dno and d is the tuple of the Research department ```

In other words: e.dno is the department number of the Research department

• Therefore:

 ``` Employee(e) and (∃d) ( Department(d) // d is a department tuple and d.dname = 'Research' // d is the Research department and d.dno = e.dno // e is an employee in R. dept ) ```

are only true only for a tuple e of Employee where the department number of the tuple e is equal to the department number of the Research department !

• Therefore:

 ``` { e.fname, e.lname | Employee(e) // e is an employee and (∃d) ( Department(d) // d is a department tuple and d.dname = 'Research' // d is the Research department and d.dno = e.dno // e is an employee in R. dept ) } Expressed in more "English-like" way: => { e.fname, e.lname | Employee(e) // e is an employee and e.dno = department number of the Research department } ```

is the set of employee (fname, lname) in the Research department

• Query 2:

 Find fname and lname of employees who works on some project controlled by department number 5

Relational Calculus query:

 ``` { e.fname, e.lname | Employee(e) // e is an employee and ( (∃p) (∃w) ( Project(p) // p is a Project tuple and Works_on(w) // w is a Works_on tuple and p.dnum=5 // p is controlled by dept 5 and p.pnumber=w.pnum // join Project and Work_on and w.essn=e.ssn // e works on the project ) ) } ```

How it works:

• Suppose we have the following content in the database:

• Because of these conditions:

 Employee(e) Project(p) Works_on(w)

the entire expression can only be true if:

 e is an Employee tuple p is an Project tuple w is an Works_on tuple

• Let's try the first Employee tuple:

To make the second condition true, we must find tuples such that:

 ``` p is a Project tuple w is a Works_on tuple p.dnum=5 and p.pnumber=w.pnum and w.essn=e.ssn ```

We can use these Project and Works_on tuples:

So e1 is good !!! (And we output his fname, lname)

• Let's try the second Employee tuple:

To make the second condition true, we must find tuples such that:

 ``` p is a Project tuple w is a Works_on tuple p.dnum=5 and p.pnumber=w.pnum and w.essn=e.ssn ```

Here, we have a problem:

We cannot find any combination of tuples that can make the second condition to be true

So: e2 is not outputted !!!

• Finally, e3 is outputted because we can find a combination of tuples that can make the second condition to be true: