Fall Semester, 2002
Homework Assignment 4
The point value (if any) of each step is given in square brackets [ ]. The solutions should be turned in on paper by Friday, Dec 6, 2002, in class, before class starts.
For all of the following questions, consider the following
schema (the key attributes are emphasized in bold, in addition to
being underlined):
FoodProduct(product-id,product-name,price)
Ingredients(product-id,nutrient-id,calories)
Nutrients(nutrient-id,nutrient-name)
HealthyDiet(nutrient-id)
The FoodProduct relation contains information about products: their
identification number (some unique attribute), their
names, and prices (in dollars), such as :
FoodProduct(1,"Kellogg's Corn Flakes",1.67)
FoodProduct(135,"12-pack Coke",4.00)
FoodProduct(497,"Mashed Potatos",3.25)
The Ingredients relation is like the little table "Nutrition
Facts" that you find on every carton. It indicates for each serving
of the product, what are the nutrients it contains, and how many
calories of each nutrient it provides:
Ingredients(1,23,25)
Ingredients(1,14,30)
Ingredients(1,24,10)
You may assume that all tuples listed in the Ingredients relation
will have a non-zero value for calories.
The first tuple indicates that the product with product-id 1 (i.e.,
Kellogg's Corn Flakes), has 25 calories of nutrient number 23 (which
is given by the next table); it has 30 calories of nutrient number
14, and so on.
The Nutrients table contains the mappings from the nutrient number
to the name of the nutrient:
Nutrients(23,"Vitamin A")
Nutrients(14,"Calcium")
Nutrients(15,"Vitamin C")
Nutrients(24,"Iron")
And finally, the HealthyDiet table contains all the nutrients that
are needed to make up a "healthy diet." For example, if the
HealthyDiet table looks like:
HealthyDiet(23)
HealthyDiet(14)
HealthyDiet(24)
meaning, it contains {Vitamin A, Calcium, Iron},
then any product that provides more than zero calories of these three
products is a "healthy diet" product. So, Kellogg's Corn Flakes is
a "healthy diet" product since it contains all of Vitamin A, Calcium,
and Iron with at least one calorie of each. If the HealthyDiet table
had included Vitamin C, then Kellogg's Corn Flakes would no longer be
considered a "healthy diet" product, since it gives zero calories of
this nutrient.
Notice that this is only example data; if the HealthyDiet relation
had contained 10 nutrients, then all of these 10 nutrients have
to be present (in at least one calorie) in a product for that product
to be considered a "healthy diet" product. If it contained 27 nutrients,
then products have to contain all of these 27 nutrients, and so on.
You may assume that if the Ingredients table doesn't
list a particular nutrient with a particular product,
then that product doesn't contain that nutrient
(and hence, will provide zero calories of that nutrient).