Database design task

You are designing a small database for recipes. The analysis has revealed that you need the following data

• dish_code
• dish_name
• material_id
• material_name
• amount_needed  (the amount of material needed for a portion)
• portion_size (for how many persons the portion is intended)
• preparation_time
• easiness
• phase_number_in_preparation
• phase_name
• phase_description

Functional dependencies

• dish_code -> dish_name
• dish_code -> preparation_time    (we assume that there is only one recipe for a dish)
• dish_code ->portion_size
• dish_code -> easiness
• dish_code, phase_number_in_preparation -> phase_name
• dish_code, phase_number_in_preparation -> phase_description
• dish_code, material_id -> amount_needed
• material_id  -> material_name

Tables

create table dish (
     dish_code varchar(20) not null primary key,
     dish_name varchar(120) not null,
     portion_size smallint not null,
     easiness varchar(10)
  );
  
  create table material (
     material_id varchar(20) not null primary key,
     material_name varchar(120) not null
  );
  
  create table phase (
     dish_code varchar(20) not null,
     phase_number_in_preparation smallint not null,
     phase_name  varchar(120) not null,
     phase_description varchar(2000),
     primary key (dish_code,  phase_number_in_preparation),
     foreign key (dish_code) references dish on delete cascade
  );
  
  create table material_use (
     dish_code varchar(20) not null,
     material_id varchar(20) not null,
     amount_needed varchar(40),
     primary key (dish_code, material_id),
     foreign key (dish_code) references dish on delete cascade,
     foreign key (material_id) references material
  );