1. Learn
    2. /
  2. Courses
    3. /
  3. Creating PostgreSQL Databases

Exercise

Streamlining meal options

The cafeteria staff hears about all of the great work happening at the high school to organize data for important aspects of school operations. This group now wants to join these efforts. In particular, the staff wants to keep track of the different meal options that are available throughout the school year. With the help of the IT staff, the following table is defined for this purpose: 

CREATE TABLE meal (
    id INTEGER,
    name VARCHAR(50) NOT NULL
    ingredients VARCHAR(150), -- comma seperated list
    avg_student_rating NUMERIC,
    date_served DATE,
    total_calories SMALLINT NOT NULL
);

Using your knowledge of database normalization, you will provide a better design for the meal table.

Instructions

100 XP
  • Complete the definition of the ingredient table for storage of ingredients.
  • Make the id column of the meal table a PRIMARY KEY and remove the ingredients and date_served columns so that the meal table satisfies 2NF.
  • Complete the definition of meal_date to store dates on which a meal is served.
  • Complete the definition of meal_ingredient so that ingredients in the ingredient table can be referenced from the meal_ingredient table.