Ben & Jerrys, 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 toolset from a company called Business Objects to track customer tastes.

- Develop ORACLE SQL Statements with appropriate PK and FKs for each table for ORACLE (SQL)

- Write queries to provide table structure and queries output

- Write queries to provide table contents and queries output

- Develop tables ICECREAM, INGREDIENT, and RECIPE with appropriate primary and foreign keys in word and then create them in ORACLE

- Create a statement with appropriate PK, FKs

- Provide table structure and content

They currently have three tables ICECREAM, INGREDIENT, and RECIPE tables with the following attributes:

ICECREAM (Ice_cream_ID, Ice_cream_flavor, price per scoop, 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 ice cream.

Ingredient_ID is the internal Id given to an ingredient

selling_staus is an internal control that 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

IcecreamID	Ice cream_Flavor	Price per scoop	year_first offered	Selling_status
101	Berry	$6	2012
201	Vanilla	$2	1981
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