Question

Consider the following database schema:

Frequents(kid, store)

Sells(store, candy)

Likes(kid, candy)

Stores(store, city, phone)

Kids(kid, city, age)

Write relational algebra expression(s) to:

a) Find the stores in ’Warrensburg’, for each display only the store name and phone

number.

b) Find the stores in ’Warrensburg’, which sells ’Hersheys’ or ’Mars’. Your

expression must use set union.

c) Repeat the above without using set union.

d) Find the kids who are 10 years or older and like ’M&Ms’.

e) Find the kids and stores in the same city. Display the city, kid, and store names.

f) Find the kids who are frequents at a store which sells at least one candy they like. Display

the kid and store names.

g) Find the kids who like ’Mars’ but not ’M&Ms’. (Hint: set difference)

Answer 1

--a

select store,phone

from Stores

where city = 'Warrenburg';

--explanation:

--select statement retreives the column names store and phone

--where statement defines a condition that city name is Warrenburg

--b

select Sells.store

from Sells,Stores

where Sells.store = Stores.store and candy = "Hersheys" and city = "Warrenburg"

UNION

select Sells.store

from Sells,Stores

where Sells.store = Stores.store and candy = "Mars" and city =  "Warrenburg";

--explanation:

--The first statement before union retrieves the stores selling Hersheys

--The second statement after union retreives the stores selling Mars

--union retireives all the results also if any duplicates found they're neglected

--C

select Sells.store

from Sells,Stores

where Sells.store = Stores.store  and city =  "Warrenburg" and (candy = "Mars" or candy = "Hersheys");

--explanation:

--instead of UNION we are using a OR statement in where conditional statement

--d

select Kids.kid

from Kids,Likes

where Kids.Kid = Likes.kid and candy = "M&Ms" and age >= 10;

--explanation:

--in the conditional statement we are specifying kid who likes M&Ms candies by equalizing the corresponding kid in Kids table

--e

select Kids.city,Kids.kid,Stores.store

from Kids,Stores

where Kids.store = Stores.store and Kids.city = Stores.city

--explanation:

--In where statement we're checking for same city and equalizing the same stores

--f

select Frequents.kid,Frequents.store

from Frequents,Likes,Sells

where Frequents.store = Sells.store and Sells.candy = Likes.candy and Likes.kid = Frequents.kid

group by Sells.candy

HAVING count(candy) >= 1

--explanation:

--first we group by candy so that we can now how much a store is selling

--having count makes sure to include the stores which sells atleast one candy

--remaining where condition says about corresponding kid and candy they like

--g

select kid

from Likes

where candy = "Mars"

MINUS

select kid

from Likes

where candy = "M&Ms";

--first find kids liking Mars And make a set difference using MINUS so that it retrieves only kids which does not like M&Ms but likes mars