CREATE TABLE Category (ID SERIAL 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 Category_Item (ID SERIAL NOT NULL, Category_ID int4 NOT NULL, Item_ID int4 NOT NULL, Description varchar(255), PRIMARY KEY (ID), CONSTRAINT UK_Category_Item UNIQUE (Category_ID, Item_ID))

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

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

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

CREATE TABLE Customer (ID SERIAL NOT NULL, City_ID int4 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 bytea NOT NULL, Credit numeric(14, 2), Created_Time timestamp NOT NULL, Last_Login_Time timestamp NOT NULL, Comments varchar(1000), PRIMARY KEY (ID))

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

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

CREATE TABLE Employee (ID SERIAL NOT NULL, Store_ID int4 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 bytea NOT NULL, Created_Date timestamp NOT NULL, Last_Login_Date timestamp, Is_Active varchar(1) NOT NULL, Comments varchar(1000), PRIMARY KEY (ID))

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

CREATE TABLE Hour (ID SERIAL NOT NULL, Name int4 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 SERIAL NOT NULL, Name varchar(50) NOT NULL UNIQUE, Description varchar(255), In_Stock varchar(1) NOT NULL, Calories int4, Ingredients varchar(1000), Recipe varchar(1000), Comments varchar(1000), PRIMARY KEY (ID))

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

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

CREATE TABLE Menu (ID SERIAL NOT NULL, Store_ID int4 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 SERIAL NOT NULL, Menu_ID int4 NOT NULL, Category_ID int4 NOT NULL, Description varchar(255), PRIMARY KEY (ID), CONSTRAINT UK_Menu_Category UNIQUE (Menu_ID, Category_ID))

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

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

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

CREATE TABLE Setting (ID SERIAL NOT NULL, Store_ID int4 NOT NULL UNIQUE, Language_ID int4 NOT NULL, Currency_ID int4 NOT NULL, Average_Preparation_Time int4 NOT NULL, Is_Busy_Mode varchar(1) NOT NULL, Busy_Preparation_Time int4 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 SERIAL NOT NULL, Name varchar(50) NOT NULL UNIQUE, City_ID int4 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 bytea, Bank_Branch varchar(255), Bank_Code varchar(50), Bank_Account varchar(50), Comments varchar(1000), PRIMARY KEY (ID))

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

CREATE TABLE Week_Day (ID SERIAL NOT NULL, 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)