Database 1
Data Manipulation

Prof. dr. Paul M.E. De Bra

Dept. of Computer Science

Eindhoven Univ. of Technology

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.

Reading Queries

{customer-name}(balance>amount(account  depositor  borrower  loan))

{branch-name}(branch)  {branch-name}(branch-city  customer-city(branch  account  depositor  customer))

{X.customer-name}	(X.account-number = Y.account-number  X.customer-name  Y.customer-name	(X(depositor)  Y(depositor)))

Preparation for labsession 2

Study section 3.6 of the database book.

This section deals with the tuple calculus, a declarative query language similar to logic expressions.

In the labsession we are going to try formulating queries in the tuple calculus, and reading them back.

In this colstruction we are going to try to understand to recognize different types of queries by looking at the question formulated in natural language.

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.

First: Give the name of the customers who have an account with a higher balance than the amount of one of the loans of these customers at the same branch.

Second: Give the name of the branches that only have depositors from their own city.

Third: Give the name of customers who have a joint account with someone else.

Databases 1, Colstructie 4

Practice with Relational Algebra

Paul De Bra

2003-04-01T22:52:54

Paul De Bra