CREATE TABLE Category (ID int(10) NOT NULL AUTO_INCREMENT, Name varchar(50) NOT NULL UNIQUE, Description varchar(255), Is_Active varchar(1) NOT NULL, Comments varchar(1000), PRIMARY KEY (ID))

CREATE TABLE Category_Item (ID int(10) NOT NULL AUTO_INCREMENT, Category_ID int(10) NOT NULL, Item_ID int(10) NOT NULL, Description varchar(255), PRIMARY KEY (ID), CONSTRAINT UK_Category_Item UNIQUE (Category_ID, Item_ID))

CREATE TABLE City (ID int(10) NOT NULL AUTO_INCREMENT, Name varchar(50) NOT NULL, Region_ID int(10) NOT NULL, Time_Zone_ID int(10) NOT NULL, Zip_Code int(30), PRIMARY KEY (ID), CONSTRAINT UK_City UNIQUE (Name, Region_ID))

CREATE TABLE Country (ID int(10) NOT NULL AUTO_INCREMENT, Name varchar(50) NOT NULL UNIQUE, PRIMARY KEY (ID))

CREATE TABLE Currency (ID int(10) NOT NULL AUTO_INCREMENT, Name varchar(50) NOT NULL UNIQUE, Description varchar(255), PRIMARY KEY (ID))

CREATE TABLE Customer (ID int(10) NOT NULL AUTO_INCREMENT, City_ID int(10) NOT NULL, Phone varchar(50) NOT NULL UNIQUE, First_Name varchar(50) NOT NULL, Last_Name varchar(50) NOT NULL, Is_Company varchar(1) NOT NULL, Company_Name varchar(255), Company_Tax_Code varchar(50), Billing_Address varchar(255) NOT NULL, Is_Registered_Online varchar(1) NOT NULL, Email varchar(50) NOT NULL UNIQUE, Username varchar(50) NOT NULL UNIQUE, Password binary(255) NOT NULL, Credit decimal(14, 2), Created_Time timestamp NOT NULL, Last_Login_Time timestamp NOT NULL, Comments varchar(1000), PRIMARY KEY (ID))

CREATE TABLE Diet_Item (ID int(10) NOT NULL AUTO_INCREMENT, Diet_Type_ID int(10) NOT NULL, Item_ID int(10) NOT NULL, Description varchar(255), PRIMARY KEY (ID), CONSTRAINT UK_Diet_Item UNIQUE (Diet_Type_ID, Item_ID))

CREATE TABLE Diet_Type (ID int(10) NOT NULL AUTO_INCREMENT, Name varchar(50) NOT NULL UNIQUE, Description varchar(255), Is_Active varchar(1) NOT NULL, PRIMARY KEY (ID))

CREATE TABLE Employee (ID int(10) NOT NULL AUTO_INCREMENT, Store_ID int(10) NOT NULL, First_Name varchar(50) NOT NULL, Last_Name varchar(50) NOT NULL, Phone varchar(50) NOT NULL UNIQUE, Email varchar(50) NOT NULL UNIQUE, Username varchar(50) NOT NULL UNIQUE, Password binary(255) NOT NULL, Created_Date timestamp NOT NULL, Last_Login_Date timestamp NULL, Is_Active varchar(1) NOT NULL, Comments varchar(1000), PRIMARY KEY (ID))

CREATE TABLE Employee_Role (ID int(10) NOT NULL AUTO_INCREMENT, Role_Type_ID int(10) NOT NULL, Employee_ID int(10) NOT NULL, Assigned_Date timestamp NOT NULL, Revoked_Date timestamp NULL, Comments varchar(1000), PRIMARY KEY (ID))

CREATE TABLE Hour (ID int(10) NOT NULL AUTO_INCREMENT, Name int(2) NOT NULL, AM_PM varchar(2) NOT NULL, Description varchar(255), PRIMARY KEY (ID), CONSTRAINT Hour_UK UNIQUE (Name, AM_PM))

CREATE TABLE Item (ID int(10) NOT NULL AUTO_INCREMENT, Name varchar(50) NOT NULL UNIQUE, Description varchar(255), In_Stock varchar(1) NOT NULL, Calories int(10), Ingredients varchar(1000), Recipe varchar(1000), Comments varchar(1000), PRIMARY KEY (ID))

CREATE TABLE Item_Hour (ID int(10) NOT NULL AUTO_INCREMENT, Item_ID int(10) NOT NULL, Week_Day_ID int(10) NOT NULL, Start_Hour_ID int(10) NOT NULL, End_Hour_ID int(10) NOT NULL, Comments varchar(1000), PRIMARY KEY (ID))

CREATE TABLE Language (ID int(10) NOT NULL AUTO_INCREMENT, Name varchar(50) NOT NULL UNIQUE, Description varchar(255), PRIMARY KEY (ID))

CREATE TABLE Menu (ID int(10) NOT NULL AUTO_INCREMENT, Store_ID int(10) NOT NULL, Name varchar(50) NOT NULL UNIQUE, Description varchar(255), Is_Active varchar(1) NOT NULL, Comments varchar(1000), PRIMARY KEY (ID))

CREATE TABLE Menu_Category (ID int(10) NOT NULL AUTO_INCREMENT, Menu_ID int(10) NOT NULL, Category_ID int(10) NOT NULL, Description varchar(255), PRIMARY KEY (ID), CONSTRAINT UK_Menu_Category UNIQUE (Menu_ID, Category_ID))

CREATE TABLE Menu_Hour (ID int(10) NOT NULL AUTO_INCREMENT, Menu_ID int(10) NOT NULL, Week_Day_ID int(10) NOT NULL, Start_Hour_ID int(10) NOT NULL, End_Hour_ID int(10) NOT NULL, Comments varchar(1000), PRIMARY KEY (ID))

CREATE TABLE Region (ID int(10) NOT NULL AUTO_INCREMENT, Name varchar(50) NOT NULL, Country_ID int(10) NOT NULL, PRIMARY KEY (ID), CONSTRAINT UK_Region UNIQUE (Name, Country_ID))

CREATE TABLE Role_Type (ID int(10) NOT NULL AUTO_INCREMENT, Name varchar(50) NOT NULL UNIQUE, Description varchar(255), PRIMARY KEY (ID))

CREATE TABLE Setting (ID int(10) NOT NULL AUTO_INCREMENT, Store_ID int(10) NOT NULL UNIQUE, Language_ID int(10) NOT NULL, Currency_ID int(10) NOT NULL, Average_Preparation_Time int(3) NOT NULL, Is_Busy_Mode varchar(1) NOT NULL, Busy_Preparation_Time int(3) NOT NULL, Is_Ordering_Pause varchar(1) NOT NULL, Public_Reviews varchar(1) NOT NULL, Comments varchar(1000), PRIMARY KEY (ID))

CREATE TABLE Store (ID int(10) NOT NULL AUTO_INCREMENT, Name varchar(50) NOT NULL UNIQUE, City_ID int(10) NOT NULL, Is_Active varchar(1) NOT NULL, Legal_Entity_Name varchar(255) NOT NULL, Registration_Number varchar(50), Tax_Code varchar(50), Address varchar(255) NOT NULL, GPS_Location varchar(50), Postal_Code varchar(50), Phone varchar(50), Fax varchar(50), Email varchar(50), Website varchar(255), Logo blob, Bank_Branch varchar(255), Bank_Code varchar(50), Bank_Account varchar(50), Comments varchar(1000), PRIMARY KEY (ID))

CREATE TABLE Time_Zone (ID int(10) NOT NULL AUTO_INCREMENT, Name varchar(50) NOT NULL UNIQUE, Description varchar(255), PRIMARY KEY (ID))

CREATE TABLE Week_Day (ID int(10) NOT NULL AUTO_INCREMENT, Name varchar(50) NOT NULL UNIQUE, Description varchar(255), PRIMARY KEY (ID))

ALTER TABLE Category_Item ADD CONSTRAINT FK_Category_Item_Category FOREIGN KEY (Category_ID) REFERENCES Category (ID)

ALTER TABLE Category_Item ADD CONSTRAINT FK_Category_Item_Item FOREIGN KEY (Item_ID) REFERENCES Item (ID)

ALTER TABLE City ADD CONSTRAINT FK_City_Region FOREIGN KEY (Region_ID) REFERENCES Region (ID)

ALTER TABLE City ADD CONSTRAINT FK_City_Time_Zone FOREIGN KEY (Time_Zone_ID) REFERENCES Time_Zone (ID)

ALTER TABLE Customer ADD CONSTRAINT FK_Customer_City FOREIGN KEY (City_ID) REFERENCES City (ID)

ALTER TABLE Diet_Item ADD CONSTRAINT FK_Diet_Item_Diet_Type FOREIGN KEY (Diet_Type_ID) REFERENCES Diet_Type (ID)

ALTER TABLE Diet_Item ADD CONSTRAINT FK_Diet_Item_Item FOREIGN KEY (Item_ID) REFERENCES Item (ID)

ALTER TABLE Employee_Role ADD CONSTRAINT FK_Employee_Role_Employee FOREIGN KEY (Employee_ID) REFERENCES Employee (ID)

ALTER TABLE Employee_Role ADD CONSTRAINT FK_Employee_Role_Role_Type FOREIGN KEY (Role_Type_ID) REFERENCES Role_Type (ID)

ALTER TABLE Employee ADD CONSTRAINT FK_Employee_Store FOREIGN KEY (Store_ID) REFERENCES Store (ID)

ALTER TABLE Item_Hour ADD CONSTRAINT FK_Item_Hour_End_Hour FOREIGN KEY (Start_Hour_ID) REFERENCES Hour (ID)

ALTER TABLE Item_Hour ADD CONSTRAINT FK_Item_Hour_Item FOREIGN KEY (Item_ID) REFERENCES Item (ID)

ALTER TABLE Item_Hour ADD CONSTRAINT FK_Item_Hour_Start_Hour FOREIGN KEY (End_Hour_ID) REFERENCES Hour (ID)

ALTER TABLE Item_Hour ADD CONSTRAINT FK_Item_Hour_Week_Day FOREIGN KEY (Week_Day_ID) REFERENCES Week_Day (ID)

ALTER TABLE Menu_Category ADD CONSTRAINT FK_Menu_Category_Category FOREIGN KEY (Category_ID) REFERENCES Category (ID)

ALTER TABLE Menu_Category ADD CONSTRAINT FK_Menu_Category_Menu FOREIGN KEY (Menu_ID) REFERENCES Menu (ID)

ALTER TABLE Menu_Hour ADD CONSTRAINT FK_Menu_Hour_End_Hour FOREIGN KEY (End_Hour_ID) REFERENCES Hour (ID)

ALTER TABLE Menu_Hour ADD CONSTRAINT FK_Menu_Hour_Menu FOREIGN KEY (Menu_ID) REFERENCES Menu (ID)

ALTER TABLE Menu_Hour ADD CONSTRAINT FK_Menu_Hour_Start_Hour FOREIGN KEY (Start_Hour_ID) REFERENCES Hour (ID)

ALTER TABLE Menu_Hour ADD CONSTRAINT FK_Menu_Hour_Week_Day FOREIGN KEY (Week_Day_ID) REFERENCES Week_Day (ID)

ALTER TABLE Menu ADD CONSTRAINT FK_Menu_Store FOREIGN KEY (Store_ID) REFERENCES Store (ID)

ALTER TABLE Region ADD CONSTRAINT FK_Region_Country FOREIGN KEY (Country_ID) REFERENCES Country (ID)

ALTER TABLE Setting ADD CONSTRAINT FK_Setting_Currency FOREIGN KEY (Currency_ID) REFERENCES Currency (ID)

ALTER TABLE Setting ADD CONSTRAINT FK_Setting_Language FOREIGN KEY (Language_ID) REFERENCES Language (ID)

ALTER TABLE Store ADD CONSTRAINT FK_Store_City FOREIGN KEY (City_ID) REFERENCES City (ID)

ALTER TABLE Setting ADD CONSTRAINT FK_Store_Setting FOREIGN KEY (Store_ID) REFERENCES Store (ID)