Database 1
Data Manipulation

Prof. dr. Paul M.E. De Bra

Dept. of Computer Science

Eindhoven Univ. of Technology

Library Questions

List all the authors of books of which the library has a copy that has never been borrowed.

List all the authors of books that have never been borrowed.

List all the authors of which no book has ever been borrowed.

Exercise 3.5g

Find the names of all employees who earn more than every employee of “SBC”.

The “more than every...” clause cannot be expressed directly in the relational algebra.

For all other employees there is an employee of “SBC” earning more. This can be described using a selection on a cartesian product (of works with works).

We then use the difference to find the correct result.

Exercise 3.5h

Find all companies located in every city in which “SBC” is located.

The “every city...” clause cannot be expressed directly in the relational algebra.

For the other companies there is a city in which “SBC” is located and the other company is not.

The cities where a company is not located are all the cities except the ones where the company is located.

We thus need 2 times a difference in this query.

Preparation for colstruction 2

The preparation is for colstruction 2 instead of labsession 2 because of a holiday.

Study chapter 3 of the database book, up to and including all of section 3.2.

In 3.2.3 additional operators are introduced that make the expression of some queries easier.

Note that we are not going to study sections 3.3, 3.4 and 3.5.

In this colstruction we are going to try some more difficult queries in the relational algebra. We take some of the more difficult queries from the book, and also try some examples from the library database.

In these questions we need to find “borrow” records that correspond to books of an author. The “borrow” records only contain the barcode, not the ISBN. So the “copy” table is needed to make the link between the book and borrowed copies of the book. Note the difference between the three questions. In the first we look at just one copy. In the second we look at all the copies of a book. In the third we look at all the books written by an author. Each time we need the “difference” operator, but on a different subquery.

The query we really answer is: “List the names of all the employees, except those for which there is an employee of “SBC” with a higher or equal salary.”

This is really a question on the division operator, but we have not yet studied it. This is good because it forces us to really understand the question well.

We really would like to answer the question: “List all the companies, except those for which there is a city in which “SBC” is located and the other company is not”, but the difficulty then is in ensuring that the first and last company are the same.

We can perform a trick here: First lets do the following:

“List every company with every city where SBC is located.” So we replace the cities where the companies are located with the cities where SBC is located.

Now “Subtract from this the list of companies with the cities where they are located.”

This yields a list of companies with every city in which SBC is located and they are not.

Now we subtract these companies from the whole list of companies and then we have the companies for which there is no city in which SBC is located and they are not.

We study the remainder of section 3.2 by the next colstruction. We do it by the colstruction because there is no labsession the week before easter.

In section 3.2.3 some additional operators are introduced. Let us briefly mention two of them:

The Join operator is very useful because it lets us use the connection between two tables through a common attribute. We have noticed today that we often need to combine a cartesian product with a selection on the common attribute. The join operator provides us with a more compact way to do this.

The Division operator is a very special one that lets us perform a selection based on sets. Exercise 3.5h can be expressed quite easily using the Division operator.

Databases 1, Colstructie 2

Practice with Relational Algebra

Paul De Bra

2003-04-01T22:52:54

Paul De Bra