EECE 4/6253 Database Engineering
Homework #6
Suppose we have the relations:
frequents( Drinker, Bar)
serves( Bar, Beer )
likes( Drinker, Beer )
The first relation indicates the bars a drinker visits (assume every drinker frequents at
least one bar). The second tells what beers each bar serves, and the last relation indicates
which beers each drinker likes to drink. Write queries in relational algebra for the
following:
a) happy( D) - drinkers who frequent at least one bar that serves a beer he likes.
b) shouldVisit( D, B ) - if a bar B serves a beer drinker D likes.
c) veryHappy( D ) - if every bar that drinker D frequents serves at least one beer he likes.
d) sad( D ) - if drinker D frequents no bar that serves beer he likes.
Let * denote the natural join.
a) Happy = ∏Drinker(frequents * serves * likes)
b) ShouldVisit = ∏Drinker, Bar(serves * likes)
c) Drinker_Bar= ∏Drinker, Bar(frequents * serves * likes)
Comment: <Drinker,Bar> pairs such that the Drinker frequents the bar and the bar
serves a beer the drinker likes.
D_Bar_No_Beer = frequents – Drinker_Bar
Comment: <Drinker,Bar> pairs such that the Drinker frequents the bar and the bar
serves no beer the drinker likes.
VeryHappy = ∏Drinker(frequents) - ∏Drinker(D_Bar_No_Beer)
Comment: Drinkers who only go to bars that serve at least one beer they like.
VeryHappy = ∏Drinker(frequents) - ∏Drinker(frequents - ∏Drinker, Bar(frequents * serves * likes))
d) Sad = ∏Drinker(frequents) - ∏Drinker(frequents * serves * likes)
