Write Create Table commands to create the three tables above in your database and create a primary key for each table. Add any foreign keys as appropriate.

Include in your answer the table creation commands and any command to create keys and foreign keys.

Notes:

• You may create the primary keys and foreign keys either as part of the table creation statement or using Alter Table to add the keys after you create the table

• Assume that the names in all tables can be of arbitrary length

• Assume that the stock symbol can have a maximum length of 6 characters

• Assume that fat and carbs are floating point numbers

• All other fields should be assumed to be integers

Q2. (10 pts) Create csv files for the data shown in the tables above. Load all the data into the tables. You may use either the command line or the phppgadmin gui. Include in your answer a screenshot to show that you have successfully loaded the data.

Q3. (10 pts) Write a query to find the stock symbol of the company that makes Daniela's favorite breakfast food. (You must use the name 'Daniela' in the selection predicate, not Daniela's Id.)

Include your query and the result in your answer.

Q4. (10 pts) Write a SQL query to make a list of consumers and their favorite. Include consumers that do not have a favorite breakfast food.

Include your query and the result in your answer.

Q5: (10 pts) Write a SQL query to find the names and calories of breakfast foods that are *not* Kiran's favorite breakfast food. Use a subquery.

Include your query and the result in your answer.

Q6: (15 pts) Write two SQL queries to find the names of breakfast foods made by Starbucks. One query should use a subquery in the WHERE clause, one query should not use a subquery.

Include your query and the result in your answer.

Q7: (10 pts) Write a query to list all breakfast foods that at least 2 consumers have listed as favorites. List breakfastfood name in your answer.

Include your query and the result in your answer.

Q8: (10 pts) Write two queries: 1) count the number of consumers and 2) count the number of consumers that have favorite breakfasts. Do not use NULL (IS NULL, IS NOT NULL, etc.) in your query.

Include your query and the result in your answer.

Q9: (15 pts) Write a query to find for each breakfast food, the number of consumers who list that food as their favorite. List breakfastfood name and count in your result. Name the count column NumFavorite. Be sure to include breakfast foods that are no one's favorite. Use UNION in your answer. (Using UNION is required.)

Include your query and the result in your answer.

Q10: (15 pts) Write a SQL query to find the name of the breakfast foods that are the favorite of the most consumers. You may ignore nulls - that is, for this question focus only on breakfast foods that are the favorite of at least one consumer.

Include your query and the result in your answer.

Q11: (15 pts) Write two relational algebra expressions to find the name of and the amount of calories in Olivia's favorite breakfast food and the name of the company that makes that food. The two relational algebra expressions should be different, but should be guaranteed to give the same result.

Q12: (10 pts) Give an example of a query that can be expressed in SQL, but which cannot be expressed in pure relational algebra. Write the SQL and explain why the query cannot be expressed in RA.

Q13: (10 pts) Some consumers may have multiple favorite breakfast foods; for example, Miranda likes both Coffee and Nonfat Greek Yogurt. Modify the Breakfast Schema by adding a column called FavBreakArray that uses an Array Type to allow a Consumer to have multiple favorite breakfast foods. Write DDL statements to modify the schema and write an insert or update command to insert a row showing Miranda's favorite breakfast foods.

Include in your answer: your alter table statements and your insert or update statement.

No need to drop the existing FavoriteBreakfast column, you may leave it.

Postgres array documentation: https://www.postgresql.org/docs/14/arrays.html

Q14: (10 pts) First, insert the favorite breakfast information from the FavoriteBreakfast column into the new FavBreakArray column for all consumers other than Miranda. Using this new FavBreakArray column, find all consumers that have Coffee with half and half as one of their favorite breakfast.

Include your query and the result in your answer.

Q15: (10 points). Create an enum type named 'FoodGroupEnum' to categorize foods. Enum values allowed should be: Fats, Dairy, Protein, Vegetables, Grains. Add a column FoodGroup to the Breakfastfood table of type FoodGroupEnum. Update the table to add the FoodGroups for each breakfastfood.

Include in your answer: enum creation command; a sample update statement and your new table.

Note: Count Granola is a Grain; Eggs and Bacon, Sausage & Egg wrap as Proteins; Yogurt and Coffee with half and half as Dairy.

Postgres enum documentation: https://www.postgresql.org/docs/14/datatype-enum.html

Q16: (15 points). Find the name and calories of the breakfast food with the lowest amount of fat in each food group.

Include your query and the result in your answer.

Q17: (10 pts) The table/schema below representing a list of ingredients is not normalized. Please normalize the table/schema.

In your answer, include table names and attribute names as in the first line below. Underline keys and indicate any foreign keys you would create.

Ingredients(id, name, foodgroupid, foodgroup)

id	name	foodgroupid	foodgroup
1	flour	10	grains
2	oil	11	fats
3	oats	12	grains

 

Breakfast Schema: BreakfastFoods Id Name 1 2 3 4 Nonfat Greek Yogurt Coffee with half and half 5 Id 1 2 3 Consumers 4 5 6 Id 1 Bacon, Sausage & Egg Wrap 2 Scrambled Eggs (2 eggs) Peanut Butter Homestyle Granola 3 Companies 4 5 Name Olivia Roman Mikhail Daniela Miranda Kiran Company Bob's Red Mill Fage Starbucks 3 4 5 5 4 Name Hostess Brands, Inc J.M. Smucker SJM Calories Fat 640 33 149 140 SBUX 90 42 FavoriteBreakfast StockSymbol TWNK 11 7 0 3.5 Sodium Carbs 1090 58 145 65 65 19 Notes: 1.6 18 5 1.5 MadeBy 3 4 5 Consumers are people who eat breakfast. Calories, Fat, Sodium and Carbs are per serving. Bob's Red Mill and Fage are privately held and so do not have a stock symbol. Scrambled eggs are homemade, so 'Made by' is null