### The range relation and simple relational calculus expressions

• Range relations

• Range Relation:

• Range relation R(t) = the relation that is the range for a tuple variable t

• The expression R(t) is evaluated as follows:

 R(t) = true if tuple t is a tuple from the relation R R(t) = false if tuple t is not a tuple from the relation R

Example of a Range Relation expression:

 ``` Employee(t) ```

• Example of Relational Calculus with a Range Relation:

• 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 | +------+-----+----+-------+ ```

• Relational Calculus expression:

 ``` { t | Department( t ) } ```

How to evaluate this expression:

 ``` for ( t := every tuple in the database ) do { if ( Department( t ) ) output: t } ```

Output:

 ``` +-------+----------+ | 1 | Payroll | +-------+----------+ | 4 | Research | +-------+----------+ ```

Because: Department(t) is true when tuple t is a tuple in the Department relation !!!

• Simple Queries in Relational Calculus

• Recall that a query in Relational Calculus is as follows:

 ``` { t | CONDITION(t) } Meaning: Retrieve all tuples t for which the condition CONDITION(t) is true ```

• Example 1:

 ``` { t | Employee(t) AND t.salary > 40000 } Meaning: Retrieve all tuples t such that: Employee(t) is true (t is a tuple from Employee) and t.salary > 40000 is true Rephrased: t is a tuple of the relation Employee and t.salary > 40000 In plain English: Retrieve all employees whose salary > 40000 ```

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 | +------+-----+----+-------+ ```

• In order to compute the result of:

 ``` { t | Employee(t) AND t.salary > 40000 } ```

we do this:

 ``` for ( t := every tuple in the database ) do { if ( Employee(t) AND t.salary > 40000 ) output: t; } ```

Output:

 ``` +------+-----+----+-------+ | Jane | Kit | 1 | 50000 | +------+-----+----+-------+ ```

These are employees who earn > 40000

• Example 2:

 ``` { t.fname, t.lname | Employee(t) AND t.salary > 50000 } Meaning: Retrieve fname and lname of employees whose salary > 50000 ```

• Example 3:

 ``` { t.salary | Employee(t) AND t.fname='John' AND t.lname='Smith' } Meaning: Retrieve the salary of the employee "John Smith" ```