DATABASE MANAGEMENT SYSTEMS
COMPUTER PROJECT
Ben & Jerry's, located in Waterbury, Vermont, produces 190,000 pints of ice cream and frozen yogurt daily to ship to over 50,000 grocery stores in the united states and 12 other countries. Every single pint is meticulously tracked, first by being entered into an ORACLE database with the information carefully organized. Ben & Jerry uses a sophisticated data-mining tool set from a company called Business Objects to track customer tastes.
Ben & Jerry's has hired you to track their expenses and Ms. Gifford, the MIS director, has provided you with following information:
They currently have three tables ICECREM, INGREDIENT and RECIPE tables with following attributes:
ICECREAM (Ice_cream_ID, Ice_cream_flavor, price, year_first_offered, sellling _status)
INGREDIENT( Ingredient_ID, Ingredient_name, cost)
RECIPE (Ice_cream_ID, ingredient_ID, quantity_used)
WHERE:
Ice_cream_ID is the internal Id given to an ice cream.
Ingredient_ID is the internal Id given to an ingredient selling_staus is an internal control which keeps track of ice cream sales as high, low, medium or none. If no figures are available this field has no value.
Year_first_offered is the year that ice cream flavor was first offered quantity_used is the amount of ingredient used in one pint of ice cream.
Following sample data is available:
ICECREAM table
|
Ice_cream_ID
|
Ice_cream_Flavor
|
Price per scoop
|
year_first offered
|
Selling_status
|
|
101
|
Berry
|
$4
|
2010
|
|
|
201
|
Vanilla
|
$2
|
1981
|
high
|
|
211
|
Cherry
|
$3
|
1976
|
low
|
|
311
|
Chocolate
|
$3
|
1981
|
high
|
|
321
|
Strawberry
|
$2
|
1961
|
high
|
|
405
|
Pistachio
|
$2.50
|
2012
|
low
|
|
406
|
Almond
|
$3
|
2010
|
|
|
407
|
Blackberry
|
$4
|
2015
|
|
|
510
|
Mint
|
$1.50
|
1998
|
medium
|
|
520
|
Cookie
|
$2
|
2001
|
medium
|
|
525
|
Green Tea
|
$4.50
|
2012
|
low
|
|
540
|
Raspberry
|
$3
|
2012
|
|
|
545
|
Banana
|
$1.50
|
1992
|
medium
|
|
550
|
Pineapple
|
$2
|
2001
|
high
|
|
560
|
coconut
|
$3.25
|
2012
|
medium
|
RECIPE Table
|
Ice_cream_ID
|
Ingredient_ID
|
Quantity used/pint
|
|
101
|
1000
|
30
|
|
540
|
1000
|
75
|
|
545
|
1000
|
55
|
|
101
|
1100
|
40
|
|
201
|
1100
|
45
|
|
407
|
1200
|
25
|
|
101
|
1200
|
20
|
|
201
|
1200
|
45
|
|
311
|
1200
|
40
|
|
321
|
1300
|
35
|
|
101
|
1300
|
80
|
|
540
|
1300
|
50
|
|
545
|
1400
|
15
|
|
405
|
1400
|
10
|
|
406
|
1400
|
27.5
|
|
201
|
1500
|
10
|
|
540
|
1500
|
5.5
|
|
211
|
1500
|
60
|
|
101
|
1700
|
5
|
|
520
|
1700
|
10
|
INGREDIENT
|
Ingredient_ID
|
Ingredient_name
|
Cost/pint
|
|
1000
|
cocoa
|
$125
|
|
1100
|
Vanilla beans
|
$30
|
|
1200
|
Tea leaves
|
$35
|
|
1300
|
Raw almonds
|
$215
|
|
1400
|
Mint Leaves
|
$42
|
|
1500
|
Sugar
|
$5
|
|
1600
|
Cream
|
$25
|
|
1700
|
Pistachio
|
$45
|
|
1710
|
raspberry
|
$12
|
You are to do the following using ORACLE:
PARTS A, B and C
? create tables ICECREAM, INGREDIENT and RECIPE with appropriate primary and foreign keys.
(Submit create statement with appropriate PK, FKs)
? load the data
? Provide table structure and content
PART D:
Answer the following queries in SQL:
1. What are the names of all ice creams?
2. Which ice cream flavored were offered before 1999
3. How many flavored were offered before 1999.
4. Give the count of ingredients used in ice cream 101.
5. What are the ice cream flavors that use ingredient 1100?
6. What are the ingredients used in ice creams 201 or 540?
7. How many flavors were introduced each year?
8. How many ingredients have an ‘a' and an ‘l' in their names in that order?
9. How many ingredients are in each ice cream flavor?
10. Give the names of ice creams that do not have selling status.
11. Give the average price of vanilla, almond and Pistachio ice creams.
12. Give the count of ice creams by their selling status.
13. Add an ice cream peanut butter id number 625 and uses ingredients 1500 and 1600.
PART E
Relationship among entities
You are to submit the following:
1. Part A:
a. Create ORACLE SQL Statements with appropriate PK and FKs for each table (can submit in word: with table create output from ORACLE
2. Part B:
a. Write queries to provide table structure and queries output
3. Part C:
a. Write queries to provide table contents and queries output
4. Part D:
a. Each query/and its output in SQL (1 thru 13) and its result
5. Part E:
a. Draw the relationship (1:1 or 1:m or m:n) between the following:
i. ICECREAM and RECIPE
ii. INGREDIENT and RECIPE
And justify the relationship you select