Create tables with appropriate primary and foreign keys

Assignment Help Database Management System

Reference no: EM13971655

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

Reference no: EM13971655

Questions Cloud

Which of the following is true of business markets : Julia is worried about the rising pollution levels in her city. She doesn't mind paying extra for goods and services that use sustainable processes to help control pollution.

Give specific evidence : Do the ends justify the means in ethical decision-making for journalists? Or is it more important to follow ethical rules and guidelines? In other words, explain the differences between deontological or teleological philosophies that can guide ethica..

Calculate any passive investment income tax penalty : If this will be the third consecutive year the company may be subject to the penalty tax on excessive passive investment income, what could Sweet water do before the end of year to avoid the tax?

List and explain all the defenses of libel : List and explain all the defenses of libel. Now explain how this would apply to the breaking and entering story from class. The man was listed as being charged with "breaking and entering (burglary)." But he says he was merely trying to kill his girl..

Create tables with appropriate primary and foreign keys : Create tables ICECREAM, INGREDIENT and RECIPE with appropriate primary and foreign keys. What are the names of all ice creams and which ice cream flavored were offered before 1999

What influences have shaped those attitudes and values : Discuss how sexual attitudes and values varied among cultures throughout history. What influences have shaped those attitudes and values

Formula and financial calculator methods : Your father offers you a choice of $105,000 in 12 years or $47,000 today. Use Appendix B as an approximate answer, but calculate your final answer using the formula and financial calculator methods.

Attitudes and values toward sexuality different : Are the influences that have shaped your attitudes and values toward sexuality different than the influences that shaped your parent's or grandparent's generations? Explain

The acquisition of all assets and liabilities of bark : The acquisition of Bark's assets for $8 million of Ark's voting common, the assumption of $2 million of liabilities, and the payment by Ark of $100 in cash.

User Account

All Pages