First page Back Continue Last page Graphics
Recognizing Types of Queries
Identify the type of the following queries, and afterwards also translate them to the algebra (PSJ, union, intersection, difference, division):
- Give the name of customers that have a loan with a branch where they also have an account.
- Give the name of customers who have a loan at a branch where they do not have an account.
- Give the name of customers who have a loan at every branch where they have an account.
- Give the name of customers who have loans only at branches where they have an account.
Notes:
First: PSJ: we search for a match between a customer and a loan and an account. As soon as one match is found we have a tuple for the result. We need to recognize PSJ queries right away by checking that the query works by matching one tuple at a time from each relation that is used.
Second: difference: It is important to realize here what the two subqueries (before and after the difference operator) should be. The word “not” in the query means that a difference operator is needed.
Third. complex difference query. Whenever we see “every” we should consider whether it is a division query or not. Division means that the “every” should refer to a constant set of tuples. In this query the set depends on the custommer (or where he has an account) so it is not a division query. A standard approach to such a question is to rephrase the question to get reach of the “every” (or “each” if that word is used).
Give the names of customers for whom there is no branch where they have an account but no loan. (This is the complement of question 2 with loan and account reversed.)
Fourth: This is again a difference question. These are all the customers except those who have a loan at a branch where they do not have an account. It is thus the complement of question 2.