Database 1
Data Manipulation
- Prof. dr. Paul M.E. De Bra
- Dept. of Computer Science
- Eindhoven Univ. of Technology
Beer example 1
- Translate the following query to English (or Dutch)
- S.b( S.b ¹ L.b (V.d ¹ L.d Ú V.k ¹ S.k)(V L S))
Beer example 1
- S.b( S.b ¹ L.b (V.d ¹ L.d Ú V.k ¹ S.k)(V L S))
The V L S construct takes all possible combinations of visits, likes and serves tuples.
The first condition says that someone likes another beer than the served beer.
The second condition is best translated into V.d = L.d Þ V.k ¹ S.k which means that the person liking some other beer goes to a different bar (than the one serving that beer).
Conclusion: List the beers served in a bar such that there is someone who likes some other beer and who visits a different bar.
Beer example 2
Beer example 2
- b(b,k(L V) Ç S))
L V gives drinkers with beers they like and bars they visit.
We project on b,k so these are beers and bars such that someone visits the bar who likes the beer.
We intersect with S, hence we have beer-bar combinations such that someone who likes the beer visits the bar and the bar also serves the beer. (In fact this intersection means the same as the join!)
Conclusion is just the beer: List the beers that are served in a bar where some visitor(s) like that beer.
Beer example 3
- S.b(S V L) Ç L.b(L)
Beer example 3
- S.b(S V L) Ç L.b(L)
The first part selects the beers that are served in a bar where a visitor comes who likes that beer.
The second part lists all the beers liked by anyone, so that's just the list of all the beers.
Since the second part are all the beers the intersection results in just the first part.
Conclusion: List all the beers that are served in a bar where a visitor comes who likes that beer.
Beer example 4
- b(S) - b((V L) - (V S))
Beer example 4
- b(S) - b((V L) - (V S))
The V L are drinker, bar, beer combinations where the beer is liked by the drinker who goes to the bar. The V S are drinker, bar, beer combinations where the beer is served by the bar frequented by the drinker.
The difference and projection gives beers liked by some drinker who visits some bar that does not serve it (i.e. the liked beer).
The whole query is the complement of these beers, thus:
List the beers that are not liked by any drinker who visits a bar that does not serve that beer.
Beer example 5
- k(S) - k(sL.d = V.d Ù L.b ¹ S.b(L ´ (V S)))
Beer example 5
- k(S) - k(sL.d = V.d Ù L.b ¹ S.b(L ´ (V S)))
The join (at the end) is clear. We combine it with the drinkers and all the beers they like.
The selection ensures first of all that we have the same drinker, and then there is a strange part: L.b ¹ S.b. Since the cartesian product combines every L tuple with every (V S) tuple the whole tuple is selected if the drinker likes more than one beer or if the bar serves more than one beer. The net result here is that this subquery (including k) selects all the bars except those that serve only one beer and only have visitors that only like that (same) one beer.
We finally take the complement, so we list all bars that serve only one beer, and that only have visitors who like only that one (served) beer (and nothing else).
Beer example 6
- L.b(S V L) -
L.b(sV.d = L.d Ù V.k = S.k Ù S.b ¹ L.b(S ´ V ´ L))
Beer example 6
- L.b(S V L) -
L.b(sV.d = L.d Ù V.k = S.k Ù S.b ¹ L.b(S ´ V ´ L))
The first part are beers liked by someone who visits a bar that serves that beer.
The second part are beers liked by someone who visits a bar that serves a different beer.
The difference are then the beers that are only liked by people who only go to bars that only serve that one beer.
Beer example 7
- S.b(S V L) -
S.b(sV.d = L.d Ù V.k = S.k Ù S.b ¹ L.b(S ´ V ´ L))
Beer example 7
- S.b(S V L) -
S.b(sV.d = L.d Ù V.k = S.k Ù S.b ¹ L.b(S ´ V ´ L))
The first part are beers served in a bar where there is a visitor who likes that beer.
The second part are beers served in a bar where there is a visitor who also likes a different beer.
The difference are then the beers that are only served in a bar with only visitors who only like that one beer.
Note that this is not the same as:
The difference are then the beers that are only liked by people who only go to bars that only serve that one beer. (example 6)
Beer example 8
- S.b(sS.k = V.k
(S ´ (k(S) - k(V L (S.k,L.b(S ´ L) - S)))))
or
S.b(sS.k = V.k
(S ´ (k(S) - k(V L ((k(S) ´ b(L)) - S)))))
Beer example 8
- S.b(sS.k = V.k
(S ´ (k(S) - k(V L ((k(S) ´ b(L)) - S)))))
The (k(S) ´ b(L)) (or S.k,L.b(S ´ L)) gives all bars combined with all beers. Subtracting S gives each bar with the beers they do not serve.
The large join gives bars with their visitors and the beers they like but that are not served by the visited bar. We then project on the bar. This gives us the bars that have a visitor who likes a beer not served in that bar.
The complement gives bars that only have visitors who can get everything they like in that bar.
The S.b(sS.k = V.k(S ´ (... part is used to find the matching beers.
This yields the answer to beer question (n).
Preparation for colstruction 6
- Study chapter 4 up to and including 4.7
- This deals with SQL, including nested queries, aggregation and null values.
- Homework: by May 26 make the (partial) example exam given in the “huiswerk6” file.
- Note: by the end of the trimester we expect you to need only 2 hours to make this exam (as it is only a part of an old exam!).