First page Back Continue Last page Graphics
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.
Notes:
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.