Little DB

The task

Based on the organic food already in the product portfolio, a recipe service with a recipe box adapted to it is to be introduced. In addition, there should be an option to filter recipes based on specific nutritional categories and restrictions based on allergens / intolerances.

The boxes should then later be filled according to the ingredients and quantities used in the recipes.

Expand the existing database to support the features you want.

Note: The development and connection of a graphical user interface is not a mandatory part of this task, but can be done optionally if you have the appropriate prior knowledge.

Customer requirements to be implemented:

Storage of recipes consisting of several ingredients

Storage of nutrition categories

Storage of restrictions/allergens

Composition of ingredients according to a recipe

Selection of recipes according to given nutritional categories

Selection or exclusion of recipes based on restrictions

optional: access control of personal data

The Kraut und Rüben also asks you to develop a concept for the cases

that a customer requests information about his data according to DSGVO or

requested the deletion of his data.

Clarify the legal basis for both cases and implement the technical implementation (e.g. as an SQL script).

Requirements for the queries to be developed by you:

Implement the following requirements using SQL queries:

Selection of all ingredients of a recipe by recipe name

Selection of all recipes of a specific nutrition category

Selection of all recipes that contain a certain ingredient

Calculation of the average nutritional values of all orders from a customer

Selection of all ingredients that have not yet been assigned to a recipe

optional for FiSi and ITSE or mandatory for FIAE and FIDP:

Selection of all recipes that do not exceed a certain amount of calories

Selection of all recipes that contain less than five ingredients

Select all recipes that contain fewer than five ingredients and meet a specific nutritional category

Create at least three more queries

Make sure you create at least one query each with inner join, left join/right join, subselects, and aggregate functions in total

upd: You are free to use whichever DB you want. You can also add your ideas.