/************************/
/* Final Project Schema */
/************************/

CREATE TABLE tblAttendance
(
    FamilyName  char(30) not null,
    MealDate	date not null,
    MealType    char(50) not null,

    PRIMARY KEY (FamilyName, MealDate, MealType)
);

CREATE TABLE tblCompliments
(
    Recipe1     char(50) not null,
    Recipe2     char(50) not null,

    PRIMARY KEY (Recipe1, Recipe2)
);

CREATE TABLE tblConstituent
(
    RecipeName      char(50) not null,
    IngredientName  char(30) not null,
    Amount	    char(50) not null,
    Unit            char(20),

    PRIMARY KEY (RecipeName, IngredientName)
);

CREATE TABLE tblFamily
(
    FamilyName  char(30) not null,
    Overweight	int not null,
    Diabetic    int not null,
    Vegetarian  int not null,

    PRIMARY KEY (FamilyName)
);

CREATE TABLE tblIngredient
(
    IngredientName  char(30) not null,
    Basic	    int not null,
  
    PRIMARY KEY (IngredientName)
);

CREATE TABLE tblLikesRecipe
(
    FamilyName  char(30) not null,
    RecipeName  char(50) not null,
    Likes	int not null,
  
    PRIMARY KEY (FamilyName, RecipeName)
);

CREATE TABLE tblMeal
(
    Date	date not null,
    Type        char(50) not null,
    Veggie	char(50),
    Entree      char(50),
    Dessert     char(50),
    Restaurant  char(50),
    NumGuests   int not null,
  
    PRIMARY KEY (Date, Type)
);

CREATE TABLE tblRecipe
(
    RecipeName		char(50) not null,
    BasicIngredient	char(50),
    Nationality		char(50),
    Overweight		int not null,
    Diabetic    	int not null,
    Vegetarian  	int not null,
    Quick	   	int not null,
    PrepTime		char(20) not null,
    CookTime		char(20) not null,
    MarinateTime	char(20) not null,
    Instructions        mediumtext,
    Serves		int not null,
      
    PRIMARY KEY (RecipeName)
);

CREATE TABLE tblRestaurant
(
    RestaurantName      char(50) not null,
    FoodAvailable	char(100) not null,
    Phone		char(50) not null,
    Address		text not null,
    DrivingTime    	int not null,
      
    PRIMARY KEY (RestaurantName)
);

CREATE TABLE tblUnit
(
    Unit        char(50) not null,
    Descript	char(50),
      
    PRIMARY KEY (Unit)
);