This preview has intentionally blurred sections. Sign up to view the full version.View Full Document
Unformatted text preview: Handout 3: Sample Problems on Dependencies and Normalisation CS 4604 October 25, 2010 Problem 1 (From the 2005 midterm examination) Consider the relation Inventory(Manufacturer, Brandname, Type, Weight, Store) . This relation stores the items that a grocery store stocks. Each tuple in relation Inventory represents the fact that a store sells an item of a particular type and brand name manufactured by a particular company. The relation also stores the weight of the item. Two tuples that such a relation may contain are: (Kellogg’s Company, Frosted Flakes, Cereal, 14oz., Hokies Holesome Foods) and (Kraft Foods, Philadelphia, Cream Cheese, 8oz., Healthy Hokies Store) . Convert each of the next three sentences in English about Inventory into a functional or a multi-valued dependency. Consider each of these three sentences independently . 1. A manufacturer holds the trademark for a brand name of an item of a particular type, i.e., no two manufacturers can use the same brand name for items of the same type. For example, two different manufacturers cannot use the brand name Philadelphia for the food type Cream Cheese . Solution: Brandname Type → Manufacturer . I gave partial marks for dependencies that came close to this one, as I did in all the other parts of this question. Many students swapped Brandname and Manufacturer ; the dependency Manufacturer Type → Brandname implies that every manufacturer makes only one brand of a particular type, e.g., Kellogg’s Company makes only one cereal. 2. For each type, each store sells only one brand name made by each manufacturer. For example, Hokies Holesome Foods does not sell any Cereal other than Frosted Flakes that is manufac- tured by Kellogg’s Company . Solution: Manufacturer Store Type → Brandname . Some students suggested Brandname Store Type → Manufacturer as a solution. This depen- dency follows from the solution to part (a). However, the English statement in part (b) does not follow from the English statement in part (a). 3. If a particular item (specified by its manufacturer, brand name, and type) is available in a par- ticular weight at a store, then that weight is available at all stores carrying that food item. Solution: Brandname Manufacturer Type Weight and its companion Brandname ManufacturerType Store . The phrase “is available at all stores” suggest that this dependency is multi-valued. If we consider all tuples with a fixed value the manufacturer, brand name, and type of an item, the weight and store appear in all possible combinations in these tuples. Some students listed all five attributes in this multi-valued dependency, forgetting the fact that such a dependency is trivial. Problem continues on the next page....
View Full Document
This note was uploaded on 12/24/2011 for the course CS 4604 taught by Professor Cmmalik during the Fall '08 term at Virginia Tech.
- Fall '08