Ben and Jerry is happy with your efforts and wants to extend your contract.
THEY WANT YOU TO INCPRPORATE THE FOLOWING INFORMATION IN YOUR DATABASE
In addition to the current three tables, ICECREAM, RECIPE and INGREDIENT, they have one additional table CUSTOMER that needs to be integrated in the database. Purpose is to keep track of customer's ice cream flavor preferences.
CUSTOMER (Cust_ID, Cust_name, year_born)
They want to link CUSTOMER to their database. Customer flavor preference is shown in table 2.
Following tables are from assignment 1
ICECREAM (Ice_cream_ID, Ice_cream_flavor, price, years_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.
Years_first_offered is the year that ice cream was first offered
quantity_used is the amount of ingredient used in a given ice cream.
Table 1: CUSTOMER
|
CUST_ID
|
CUST_NAME
|
Year_born
|
|
1
|
Harry, T
|
2002
|
|
2
|
Sally, P
|
1992
|
|
3
|
Lio, L
|
1998
|
|
4
|
Patel, P
|
2001
|
|
5
|
Roner,K
|
1978
|
|
6
|
Jackson, O
|
2002
|
|
7
|
Long, P
|
2001
|
|
8
|
Smith, G
|
1992
|
|
9
|
Harry, L
|
2002
|
|
10
|
Paner, K
|
1978
|
|
11
|
Dan, U
|
2010
|
|
12
|
Patel, M
|
2001
|
Table 2: CUSTOMER and their Flavor preference
|
Name
|
Flavor preference
|
|
Harry, T
|
Vanilla, Coconut
|
|
Sally, P
|
Almond, Vanilla, Cookie
|
|
Lio, L
|
Banana, Green Tea, Mint
|
|
Patel, P
|
Cherry, Coconut
|
|
Roner,K
|
|
|
Jackson, O
|
Cherry, Coconut
|
|
Long, P
|
|
|
Smith, G
|
Berry, Vanilla, Mint, Cookie, almond
|
|
Harry, L
|
Mint
|
|
Paner, K
|
|
|
Dan, U
|
Coconut, Vanilla, Cherry
|
|
Patel, M
|
Coconut
|
You are to perform the following using ORACLE available at UB:
PART A: create tables and load data
a) Create CUSTOMER table and create table that will link customer to their ice cream preferences. Make sure to include appropriate primary and foreign keys. You can use customer_ID and Ice_cream_id to link customer to flavors.
Part B Provide Table structure
Part C: Provide table contents
Part D: Develop queries in ORACLE and provide its output
All queries MUST be based SOLELY on the information provided and each question must use a SINGLE query. No views or separate queries, unless otherwise stated.
As always all queries should be data independent.
c) Answer the following queries in SQL:
1. Give the names of customers who are using ice creams that have cocoa as their ingredient.
2. Give the flavor of ice creams that were introduced before customer Roner, K was born
3. Ben and Jerry want to discontinue flavors that none of their current customer like. Give a list of those flavors.
4. Give the count of customers that like exactly two flavors.
5. Ben and Jerry are stating a new flavor, a mix of Vanilla, Cookie and almond. List the names of customers that like either of these flavors.
6. Give the names of flavors that cost more than $500 (total).
7. Give the names of customers that like both Cherry and Vanilla flavors. (note: it is NOT either/Or but AND) (hint: think of UNION, INTERSECT, MINUS)
8. Give the count of employees that do not have any flavor preference.
9. Get the number of customers that have same preference as Harry, L.
10. Give the total cost of each flavor.
BONUS:
BONUS: Related to Q9..Give the names of customers that have EXACTLY same preference as Patel, P. (note if customer Patel has two flavor preference, then we want names of customers who also prefer either or both of those flavors).
PART E:
Draw one complete ERD of all entities from assignment 1 and 2.
Attachment:- Assign 1.pdf