CSC 545 Group Project Schema

schema.sql

/*
 BEGIN ENTITY TABLES
 */
DROP TABLE menu;
CREATE TABLE menu (
  time_of_day NVARCHAR2(10) NOT NULL CHECK (time_of_day IN ('breakfast', 'lunch', 'dinner')),
  "date"      DATE          NOT NULL, -- Are we gonna enforce date format here?
  CONSTRAINT pk PRIMARY KEY (time_of_day, "date")
);

DROP TABLE recipes;
CREATE TABLE recipes (
  rec_id       INT PRIMARY KEY,
  rec_name     NVARCHAR2(50) NOT NULL,
  instructions CLOB, -- character blob
  -- not sure on these enums
  category     NVARCHAR2(10) DEFAULT 'entree' CHECK (category IN ('entree', 'appetizer', 'dessert'))
);

DROP TABLE food;
CREATE TABLE food (
  food_id     INT PRIMARY KEY,
  in_fridge   CHAR CHECK (in_fridge IN (0, 1)), -- not sure what this means
  food_name   NVARCHAR2(50) NOT NULL,
  fk_nfact_id INT REFERENCES nutritional_fact (nfact_id)
);

DROP TABLE nutritional_fact;
CREATE TABLE nutritional_fact (
  nfact_id   INT PRIMARY KEY,
  -- Assuming that we'll need precision in terms of 123.45
  sodium     NUMBER(6, 2) DEFAULT 0.00,
  fat        NUMBER(6, 2) DEFAULT 0.00,
  calories   NUMBER(6, 2) DEFAULT 0.00,
  sugar      NUMBER(6, 2) DEFAULT 0.00,
  protein    NUMBER(6, 2) DEFAULT 0.00,
  food_group NVARCHAR2(15) CHECK (food_group IN ('grain', 'meat', 'veggies')), -- Not sure what enums you need
  amount     NUMBER(6, 2) DEFAULT 0.00,
);

/*
 * BEGIN PIVOT TABLES FOR MANY TO MANY RELATIONS
 */

DROP TABLE serves;
CREATE TABLE serves (
  menu_pk REFERENCES menu (time_of_day, "date"),
  recipe_id INT REFERENCES recipes (rec_id)
);

DROP TABLE ingredients;
CREATE TABLE ingredients (
  recipe_id INT REFERENCES recipes (rec_id),
  food_id   INT REFERENCES food (food_id)
);