Assignment 9 – Aggregation (Food Analysis)
Objectives
– To learn to use aggregation functions to analyze data.
– Create a procedure to perform a real database task
Assignment
For this assignment you will use the Food Database ( Case Study – Food Database ) Use the following criteria to break the foods into quartiles (4 groups). Once the food is in 4 quartiles, using ONLY foods in the first quartile rank the top foods in correct order of calorie counts.
Healthy – Your final query should return the top 10 healthiest foods based on (1) being in the first quartile of healthy and (2) low calorie count.
Unhealthy – You final query should return the top 10 unhealthiest foods based on being (1) being in the first quartile of unhealthy and (3) high calorie count
Defining Quartiles
Your stored procedure is going to have to use a specific criteria to define the quartiles. In each case the return set should only consider foods in the top quartile. In essence you are creating a data subset for the remaining query.
Healthy Quartile Criteria
1. Content of grains (first quartile) – a healthy food will have higher value
2. Content of whole grains (first quartile) – a healthy food will have higher value
3. Content of Vegetables (first quartile) – a healthy food will have higher value
Unhealthy Quartile Criteria
1. High Added Sugars – unhealthy food will have higher value
2. High Saturated Fats – unhealthy food will have higher value
3. High Solid fats – unhealthy food will have higher value
Ranking (Order) Criteria
Once the quartile is created you will now rank the food based on a ranking criteria. Any of the quartile criteria can be used as a ranking criteria. For example you could ask for all foods with High Added Sugar (quartile) ranked by Saturated Fat content. In addition to the numeric criteria used to determine the quartile which can also be used in ranking your stored procedure must also support ranking by;
1. Calories
2. Milk
3. Meats
Note that these are neither good nor bad, but do allow for ranking amounts like “What is the highest quantity meat product with also a high amount of added sugar”. Note that when asked these types of questions – words like high and low refer to quantiles (in our case quartiles) and words like highest, lowest, most, least refer to ranking.
You will write a stored procedure that accepts quartile criteria and ranking criteria – and spits out the top 10 foods (Display_Name, Portion_Amount, Portion_Display_Name, and at a minimum the values of the criteria used for quantile and rank.
Information
You will need to view the lectures on aggregation, this is relatively challenging. Lecture – Database and Aggregations
Estimated Completion Time
Good luck and welcome to true data analysis.
Supporting Lectures
Lecture – Database and Aggregations
Lecture – Complex Queries with Registration System
Questions and Answers
Here are the healthy foods using a 3 criteria query of grains (quartile), vegetables(quartile), calories (rank ascending). Note I am asking you to do a 2 criteria query with quartile and rank only.
External Resources
Hint work on creating some working queries first and then work on how to incorporate them into a stored procedure.
Grading Criteria
Partial credit will be awarded based on how well you are able to meet all requirements of the query.