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

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

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

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

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

CREATE TABLE Customer (ID int IDENTITY NOT NULL, City_ID int 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) NULL, Company_Tax_Code varchar(50) NULL, 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) NULL, Created_Time timestamp NOT NULL, Last_Login_Time timestamp NOT NULL, Comments varchar(1000) NULL, PRIMARY KEY (ID))

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

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

CREATE TABLE Employee (ID int IDENTITY NOT NULL, Store_ID int 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) NULL, PRIMARY KEY (ID))

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

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

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

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

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

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

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

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

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

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

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

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

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

CREATE TABLE Week_Day (ID int IDENTITY NOT NULL, Name varchar(50) NOT NULL UNIQUE, Description varchar(255) NULL, 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)