The Tuple Relational Calculus

The Tuple Relational Calculus:--

1. The tuple relational calculus is a nonprocedural language. (The relational algebra was procedural.)
We must provide a formal description of the information desired.
2. A query in the tuple relational calculus is expressed as

i.e. the set of tuples for which predicate is true.
3. We also use the notation
o to indicate the value of tuple on attribute .
o to show that tuple is in relation .



Example Queries
1. For example, to find the branch-name, loan number, customer name and amount for loans over $1200:

This gives us all attributes, but suppose we only want the customer names. (We would use project in the algebra.)
We need to write an expression for a relation on scheme (cname).

In English, we may read this equation as ``the set of all tuples such that there exists a tuple in the relation borrow for which the values of and for the cname attribute are equal, and the value of for the amount attribute is greater than 1200.''
The notation means ``there exists a tuple in relation such that predicate is true''.
How did we get the above expression? We needed tuples on scheme cname such that there were tuples in borrow pertaining to that customer name with amount attribute .
The tuples get the scheme cname implicitly as that is the only attribute is mentioned with.
Let's look at a more complex example.
Find all customers having a loan from the SFU branch, and the cities in which they live:

In English, we might read this as ``the set of all (cname,ccity) tuples for which cname is a borrower at the SFU branch, and ccity is the city of cname''.
Tuple variable ensures that the customer is a borrower at the SFU branch.
Tuple variable is restricted to pertain to the same customer as , and also ensures that ccity is the city of the customer.
The logical connectives (AND) and (OR) are allowed, as well as (negation).
We also use the existential quantifier and the universal quantifier .
Some more examples:
1. Find all customers having a loan, an account, or both at the SFU branch:

Note the use of the connective.
As usual, set operations remove all duplicates.
2. Find all customers who have both a loan and an account at the SFU branch.
Solution: simply change the connective in 1 to .
3. Find customers who have an account, but not a loan at the SFU branch.

4. Find all customers who have an account at all branches located in Brooklyn. (We used division in relational algebra.)
For this example we will use implication, denoted by
The formula means implies , or, if is true, then must be true.

In English: the set of all cname tuples such that for all tuples in the branch relation, if the value of on attribute bcity is Brooklyn, then the customer has an account at the branch whose name appears in the bname attribute of .


Formal Definitions
1. A tuple relational calculus expression is of the form

where is a formula.
Several tuple variables may appear in a formula.
2. A tuple variable is said to be a free variable unless it is quantified by a or a . Then it is said to be a bound variable.
3. A formula is built of atoms. An atom is one of the following forms:
o , where is a tuple variable, and r is a relation ( is not allowed).
o , where and are tuple variables, and and are attributes, and is a comparison operator ( ).
o , where is a constant in the domain of attribute .
4. Formulae are built up from atoms using the following rules:
o An atom is a formula.
o If is a formula, then so are and .
o If and are formulae, then so are , and .
o If is a formula containing a free tuple variable , then

are formulae also.
5. Note some equivalences:
o
o
o


Safety of Expressions
1. A tuple relational calculus expression may generate an infinite expression, e.g.

2. There are an infinite number of tuples that are not in borrow! Most of these tuples contain values that do not appear in the database.
3. Safe Tuple Expressions
We need to restrict the relational calculus a bit.
o The domain of a formula , denoted dom( ), is the set of all values referenced in .
o These include values mentioned in as well as values that appear in a tuple of a relation mentioned in .
o So, the domain of is the set of all values explicitly appearing in or that appear in relations mentioned in .
o is the set of all values appearing in borrow.
o is the set of all values appearing in borrow.
We may say an expression is safe if all values that appear in the result are values from dom( ).
4. A safe expression yields a finite number of tuples as its result. Otherwise, it is called unsafe

Views
1. We have assumed up to now that the relations we are given are the actual relations stored in the database.
2. For security and convenience reasons, we may wish to create a personalized collection of relations for a user.
3. We use the term view to refer to any relation, not part of the conceptual model, that is made visible to the user as a ``virtual relation''.
4. As relations may be modified by deletions, insertions and updates, it is generally not possible to store views. Views must then be recomputed for each query referring to them.

View Definition
1. A view is defined using the create view command:

where is any legal query expression.
The view created is given the name .
2. To create a view all-customer of all branches and their customers:

3. Having defined a view, we can now use it to refer to the virtual relation it creates. View names can appear anywhere a relation name can.
4. We can now find all customers of the SFU branch by writing



Updates Through Views and Null Values
1. Updates, insertions and deletions using views can cause problems. The modifications on a view must be transformed to modifications of the actual relations in the conceptual model of the database.
2. An example will illustrate: consider a clerk who needs to see all information in the borrow relation except amount.
Let the view loan-info be given to the clerk:

3. Since SQL allows a view name to appear anywhere a relation name may appear, the clerk can write:

This insertion is represented by an insertion into the actual relation borrow, from which the view is constructed.
However, we have no value for amount. A suitable response would be
o Reject the insertion and inform the user.
o Insert (``SFU'',3,``Ruth'',null) into the relation.
The symbol null represents a null or place-holder value. It says the value is unknown or does not exist.


Triggers
1. A trigger is a statement that is automatically executed by the system as a side effect of a modification to the database.
2. We need to
o Specify the conditions under which the trigger is executed.
o Specify the actions to be taken by the trigger.
3. For example, suppose that an overdraft is intended to result in the account balance being set to zero, and a loan being created for the overdraft amount.


The trigger actions for tuple t with a negative balance are then
Insert a new tuple s in the borrow relation with
s[bname] = t[bname]

s[amount] = - t[balance]
s[cname] = t[cname]
We need to negate balance to get amount, as balance is negative.
Set t[balance] to 0.









SQL statement to write this trigger
define trigger overdraft
on update of account T
(if new T.balance < 0
then (insert into loan values
(T.bname, T.account#, - new T.balance)
insert into borrower

(select cname, account#
from depositor
where T.coount# = depositor.account#)
update account S
set S.balance = 0
where S.account# = T.account# ))