50 likes | 104 Views
Explore relational algebra and SQL solutions for queries related to drinkers who frequent pubs serving particular beers they like, including options for handling preferences and restrictions in the database schema.
E N D
Relational Algebra and SQL Given relational schema: Frequent (D, P) Serves (P, B) Likes (D, B) Attributes: P (pub), B (beer), D (drinker) • The pubs that serve a beer that Jefferson likes. • Drinkers that frequent at least one pub that serves “Bud” or “Becks”. • Drinkers that frequent only pubs that serve some beer they like • Drinkers that frequent only pubs that serve no beer they like.
1) Algebra • Solution 1: • Solution 2: • Solution 3: • Solution 4:
2) SQL SELECT p FROM S, L WHERE S.b=L.b AND L.d=‘Jefferson’ • Solution 1: • Solution 2: SELECT d FROM F WHERE F.p in (SELECT p FROM S WHERE b IN (‘Bud”, ‘Becks’))
2) SQL SELECT d FROM F EXCEPT SELECT d FROM (SELECT* FROM F EXCEPT SELECT d,p FROM S, L WHERE S.b = L.b) • Solution 3:
2) SQL SELECT d FROM F EXCEPT SELECT d FROM (SELECT * FROM F INTERSECT SELECT d,p FROM S, L WHERE S.b = L.b) • Solution 4: • Solution 4 (alternative solution): SELECT d FROM F WHERE (SELECT COUNT (DISTINCT p) FROM S, L WHERE F.p=S.p AND S.b=L.b AND L.d=F.d) = 0