Question

Use only the operators discussed in class (select, project, Cartesian product, join, union, intersection, set difference and renaming). Type your answers.

Consider the following database schema:

INGREDIENT(ingredient-id,name,price-ounce)

RECIPE(recipe-id,name,country,time)

USES(rid,iid,quantity)

where INGREDIENT lists ingredient information (id, name, and the price per ounce); RECIPE lists recipe information (id, name, country of origin, and time it takes to cook it); and USES tells us which ingredients (and how much of each) a recipe uses. The primary key of each table is underlined; rid is a foreign key to RECIPE and iid is a foreign key to INGREDIENT.

Write the following queries in Relational Algebra.

(a) Find the names of French recipes that use butter and take longer than 45 minutes to cook.

(b) Find the names of recipes that use butter or lard.

(c) Find the names of recipes that use butter and lard.

(d) Find the name of recipes that do not use butter.

(e) Find the names of recipes that use 2 or more ingredients.

(f) (*) Find the names of recipes where at least 3 oz. (quantity) of every ingredient are used.

Answer 1

(a)

_RECIPE.name( _{INGREDIENT.name = 'butter' and time > 45 and Country = 'France'} RECIPE INGREDIENT )

(b)

_RECIPE.name( _{INGREDIENT.name = 'butter' or INGREDIENT.name = 'lard'} RECIPE INGREDIENT )

(c)

_RECIPE.name( _{INGREDIENT.name = 'butter' and INGREDIENT.name = 'lard'} RECIPE INGREDIENT )

(d)

_RECIPE.name( _{INGREDIENT.name != 'butter'} RECIPE INGREDIENT )

(e)

_RECIPE.name( G count( _{INGREDIENT.name) >=2} RECIPE INGREDIENT )

(f)

_RECIPE.name( _{quantity >=3} RECIPE USES INGREDIENT ) _name( INGREDIENT )

Do ask if any doubt. Please upvote.