Description
In this assignment, you are assigned to do the job of a data designer working for the fictional
company of S.T.A.R. Manufacturers. This document first outlines new laws of the fictional
country of the Republic of Batmania, as these laws affect the foodrelated data storage
requirements of S.T.A.R. Manufacturers. The instructions for your assignment task are
detailed at the end of this document.
The laws of the Republic of Batmania
The Batmanian government has recently introduced new food labelling laws. All food now
requires a traffic light rating on the front of the packaging, so that consumers can quickly
compare the relative healthiness of foods. The idea is very similar to Australia’s Health Star
Rating System (see http://healthstarrating.gov.au). An example of Batmania’s labeling is
provided in figure 1. A “green light” label indicates healthy food. An “amber light” indicates a
food that is recommended to be consumed in moderation. A “red light” indicates a food that
should be eaten infrequently (i.e. “junk food”). The categorisation of food as “red”, “amber” or
“green” is easily calculated from information available on the rest of the packaging using a
simple formula. This formula is not expected to change in the future.
Next to the traffic light label a summary of important nutritional information must be provided:
1. The energy in kilojoules per 100 grams of food;
2. The grams of Saturated fat per 100 grams of food;
3. The grams of sugars per 100 grams of food;
4. The milligrams of sodium per 100 grams; and
5. The grams of fibre per 100 grams of food.
In order to place this information in an appropriate context for consumers to make healthy
choices the amounts of saturated fat, sugars and fibre are visually indicated as being “high”
or “low”. Nothing is displayed if a food has a “medium” amount of saturated fat, sugars or
fibre. The thresholds for what is needed to be considered “high”/”low” for each of the food
properties is changed by the Batmanian government from time to time.
Figure 1: An example Traffic Light label as required by the government. This label would typically be included on
the bottom left or bottom right hand corner of the front of food packaging.
The government also requires a Nutrition Information Panel on the back of the food packing.
Examples are provided in Figure 2 and Figure 3. The following information must be
displayed in the panel:
1. The official name of the product.
2. The number of servings per package.
3. The serving size.
4. The average kilojoules per serving and average kilojoules per 100g of the product.
Some food packaging also displays the calories per serving and per 100 grams for
customers who prefer this measurement.
5. Protein, per serving and per 100g.
6. Total Fat, per serving and per 100g. Total Fat is the sum of all different types of fats
present in a food. However, most food labels only list the Saturated and Trans Fat
amounts. It is optional to display the amounts of Trans fat, Polyunsaturated fat and
Monounsaturated fat in the Nutrition Information Panel. As such, it is common that
the amounts of Saturated and Trans Fat will not actually sum to the same value as
Total Fat. Examples can be seen in figures 2 and 3.
7. Total carbohydrates (colloquially “carbs”), per serving and per 100g.
8. Total sugars (please note sugar is a type of carbohydrate), per serving and per 100g.
9. Sodium, per serving and per 100mg.
10. Dietary fibre per serving and per 100g. Although displaying this information is
optional.
11. Aspects of food that are commonly grouped under the category of Vitamins and
Minerals (for example, vitamin C, A, calcium, iron). Although displaying this
information is optional.
12. Some packaging includes information on what percentage a recommended serving is
of a recommended average daily food intake for an adult. The recommended daily
food intake for an adult is 8700kJ.
Either a “Use by” or a “Best Before” date must be included somewhere on the packaging. It
is also possible for a product to have both a “Use By” and a “Best Before” printed on it. The
front of the packaging must display the country of origin (“Made in Australia”, “Made proudly
in Batmania”, “Made in the European Union”).
Figure 2: An example Nutrition Information Panel.
Figure 3: Another example of an Nutrition Information Panel.
Basic packaged foods that are typically used as ingredients for cooking do not require food
labelling. For example, packaged flour does not need food labelling under Batmanian law.
Next to the Nutrition Information Panel the ingredients of the food must be listed. Below the
ingredients must be a text description warning customers of potential health warnings (for
example, “May contain traces of nuts”, “Contains gluten”, “Refrigerate after opening” and so
on). There are a few details which must be on the packaging, but are not required in a
particular position on the packaging:
1. The address where the product was manufactured. This may change over time as
companies often change where they manufacture certain foods.
2. Website address related to the product.
3. Total weight of the food.
4. A barcode.
S.T.A.R. Manufacturers
S.T.A.R. Manufacturers are a multinational food manufacturing company (about the same
size as the nonfictional company of Mondelez International). However, most of their
operations are in the country of Batmania. The company is eager to comply with the new
Batmanian laws described above.
Thus S.T.A.R. Manufacturers need to design a database to store all the relevant information
that is needed to generate product labels that comply with the package labelling laws of
Batmania.
The CEO has demanded that MySQL be used for the solution.
The S.T.A.R. Manufacturers CEO has said that they face particular difficulties complying
with the laws because the specific ingredients they use are constantly changing from year to
year. For example, seasonal fruits are used as ingredients and thus the ingredients may be
different between Winter and Summer. Furthermore, natural disasters such as cyclones can
make ingredients such as bananas more expensive. Thus a recipe may temporarily use a
cheaper banana substitute. The CEO wants to record historical data in the database so that
she can make adhoc queries such as: what values would have appeared on a label for
Mitchell’s Healthy Soup two years ago or 11 days ago. Thus the company has useful records
of compliance with the laws. The CEO would prefer the solution to be flexible to
accommodate plausible changes to the laws.
Instructions
Your task for this assignment is to act as a database designer in the case study that has
been provided.
You need to model the system described in the case study by creating a Physical Data
Model using MySQL Workbench. Your diagram must conform to the notation used in this
subject (e.g. showing crow’s feet). If your model includes resolved subtypes you must
explain this in the written part of the submission. You may wish to add labels to your ER
diagram to aid your explanation (e.g. “… as seen next to point 1.1 in the ER diagram…”). You
need to show the name of each relationship.
Write down any assumptions you made when designing your model. (Making an assumption
for no other reason than to simplify the exercise will not be accepted.) Your assumptions
should be about 500 words. Make sure you explain the reasons for your design decisions or
you will receive lower marks. For example, you may decide to denormalise part of your
solution for the purposes of query speeds. However, such answer is indistinguishable from
the answer of a student who simply did not know how to create a fully normalised answer!
The only way we’ll know you’ve made a deliberate decision rather than a mistake is if you tell
us! We can’t read your minds!
