Get startedGet started for free

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.

This exercise is part of the course

Creating PostgreSQL Databases

View Course

Exercise instructions

  • 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.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

CREATE TABLE ingredient (
  -- Add PRIMARY KEY for table
  ___ ___ ___ ___,
  name VARCHAR(50) NOT NULL
);

CREATE TABLE meal (
    -- Make id a PRIMARY KEY
	id ___ ___ ___,
    name VARCHAR(50) NOT NULL,

	-- Remove the 2 columns (below) that do not satisfy 2NF
  	ingredients VARCHAR(150), -- comma separated list
    avg_student_rating NUMERIC,
    date_served DATE,
    total_calories SMALLINT NOT NULL
);

CREATE TABLE meal_date (
    -- Define a column referencing the meal table
  	meal_id INTEGER ___ ___(___),
    date_served DATE NOT NULL
);

CREATE TABLE meal_ingredient (
  	meal_id INTEGER REFERENCES meal(id),
  
    -- Define a column referencing the ingredient table
    ingredient_id INTEGER ___ ___(___)
);
Edit and Run Code