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
Exercise instructions
- Complete the definition of the
ingredient
table for storage of ingredients. - Make the
id
column of themeal
table aPRIMARY KEY
and remove theingredients
anddate_served
columns so that themeal
table satisfies 2NF. - Complete the definition of
meal_date
to store dates on which ameal
is served. - Complete the definition of
meal_ingredient
so that ingredients in theingredient
table can be referenced from themeal_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 ___ ___(___)
);