Warning: This is a fairly long assignment, but by the end of it you should have fully mastered relational algebra and SQL. Feel free to leave out portions if you believe you have already mastered the material.
SELECT MAX(A) FROM R WHERE B > 5
(a) Can you write this query in relational algebra? If so, show it.
Now consider the following SQL query over the same relation:
SELECT COUNT(*) FROM R WHERE B > 5
(b) Can you write this query in relational algebra? If so, show it.
(a) Do Exercise 4.1.8 in the textbook (page 194), except you only need to give one equivalent expression, not three.
(b) There is another operator called the "antisemijoin," which is similar to the semijoin except it returns the set of all tuples in R that don't agree with any tuple in S on their shared attributes. Write a relational algebra expression equivalent to R antisemijoin S.
from | to | cost | airline |
---|---|---|---|
SF | Denver | 300 | Frontier |
SF | Denver | 350 | United |
Denver | SF | 250 | United |
Denver | SF | 250 | Frontier |
Denver | Chicago | 250 | American |
Chicago | NY | 250 | Delta |
Denver | NY | 500 | American |
Denver | NY | 400 | TWA |
SF | NY | 750 | United |
(a) Give a single SQL query that returns the cost of the cheapest nonstop flight between each pair of cities. For example, the result over the above relation instance should be:
from | to | cost |
---|---|---|
SF | Denver | 300 |
Denver | SF | 250 |
Denver | Chicago | 250 |
Chicago | NY | 250 |
Denver | NY | 400 |
SF | NY | 750 |
(b) Give a single SQL query that returns the cheapest cost of flying between each pair of cities assuming we are willing to stop up to two times en-route. For example, by stopping once (in Denver), we can get from SF to NY for 700 instead of 750. With this example data, we could stop twice (in Denver and Chicago), but that would be more expensive ($300+$250+$250 = $800).
(c) Is it possible to write a single SQL query that returns the cheapest cost of flying between each pair of cities regardless of the number of stops? If so, give the query. If not, briefly explain why.