CREATE TABLE Category (ID number(10) GENERATED AS IDENTITY, Name varchar2(50) NOT NULL UNIQUE, Description varchar2(255), Is_Active varchar2(1) NOT NULL, Comments varchar2(1000), PRIMARY KEY (ID))

CREATE TABLE Category_Item (ID number(10) GENERATED AS IDENTITY, Category_ID number(10) NOT NULL, Item_ID number(10) NOT NULL, Description varchar2(255), PRIMARY KEY (ID), CONSTRAINT UK_Category_Item UNIQUE (Category_ID, Item_ID))

CREATE TABLE City (ID number(10) GENERATED AS IDENTITY, Name varchar2(50) NOT NULL, Region_ID number(10) NOT NULL, Time_Zone_ID number(10) NOT NULL, Zip_Code number(30), PRIMARY KEY (ID), CONSTRAINT UK_City UNIQUE (Name, Region_ID))

CREATE TABLE Country (ID number(10) GENERATED AS IDENTITY, Name varchar2(50) NOT NULL UNIQUE, PRIMARY KEY (ID))

CREATE TABLE Currency (ID number(10) GENERATED AS IDENTITY, Name varchar2(50) NOT NULL UNIQUE, Description varchar2(255), PRIMARY KEY (ID))

CREATE TABLE Customer (ID number(10) GENERATED AS IDENTITY, City_ID number(10) NOT NULL, Phone varchar2(50) NOT NULL UNIQUE, First_Name varchar2(50) NOT NULL, Last_Name varchar2(50) NOT NULL, Is_Company varchar2(1) NOT NULL, Company_Name varchar2(255), Company_Tax_Code varchar2(50), Billing_Address varchar2(255) NOT NULL, Is_Registered_Online varchar2(1) NOT NULL, Email varchar2(50) NOT NULL UNIQUE, Username varchar2(50) NOT NULL UNIQUE, Password raw(255) NOT NULL, Credit number(14, 2), Created_Time timestamp(0) NOT NULL, Last_Login_Time timestamp(0) NOT NULL, Comments varchar2(1000), PRIMARY KEY (ID))

CREATE TABLE Diet_Item (ID number(10) GENERATED AS IDENTITY, Diet_Type_ID number(10) NOT NULL, Item_ID number(10) NOT NULL, Description varchar2(255), PRIMARY KEY (ID), CONSTRAINT UK_Diet_Item UNIQUE (Diet_Type_ID, Item_ID))

CREATE TABLE Diet_Type (ID number(10) GENERATED AS IDENTITY, Name varchar2(50) NOT NULL UNIQUE, Description varchar2(255), Is_Active varchar2(1) NOT NULL, PRIMARY KEY (ID))

CREATE TABLE Employee (ID number(10) GENERATED AS IDENTITY, Store_ID number(10) NOT NULL, First_Name varchar2(50) NOT NULL, Last_Name varchar2(50) NOT NULL, Phone varchar2(50) NOT NULL UNIQUE, Email varchar2(50) NOT NULL UNIQUE, Username varchar2(50) NOT NULL UNIQUE, Password raw(255) NOT NULL, Created_Date timestamp(0) NOT NULL, Last_Login_Date timestamp(0), Is_Active varchar2(1) NOT NULL, Comments varchar2(1000), PRIMARY KEY (ID))

CREATE TABLE Employee_Role (ID number(10) GENERATED AS IDENTITY, Role_Type_ID number(10) NOT NULL, Employee_ID number(10) NOT NULL, Assigned_Date timestamp(0) NOT NULL, Revoked_Date timestamp(0), Comments varchar2(1000), PRIMARY KEY (ID))

CREATE TABLE Hour (ID number(10) GENERATED AS IDENTITY, Name number(2) NOT NULL, AM_PM varchar2(2) NOT NULL, Description varchar2(255), PRIMARY KEY (ID), CONSTRAINT Hour_UK UNIQUE (Name, AM_PM))

CREATE TABLE Item (ID number(10) GENERATED AS IDENTITY, Name varchar2(50) NOT NULL UNIQUE, Description varchar2(255), In_Stock varchar2(1) NOT NULL, Calories number(10), Ingredients varchar2(1000), Recipe varchar2(1000), Comments varchar2(1000), PRIMARY KEY (ID))

CREATE TABLE Item_Hour (ID number(10) GENERATED AS IDENTITY, Item_ID number(10) NOT NULL, Week_Day_ID number(10) NOT NULL, Start_Hour_ID number(10) NOT NULL, End_Hour_ID number(10) NOT NULL, Comments varchar2(1000), PRIMARY KEY (ID))

CREATE TABLE Language (ID number(10) GENERATED AS IDENTITY, Name varchar2(50) NOT NULL UNIQUE, Description varchar2(255), PRIMARY KEY (ID))

CREATE TABLE Menu (ID number(10) GENERATED AS IDENTITY, Store_ID number(10) NOT NULL, Name varchar2(50) NOT NULL UNIQUE, Description varchar2(255), Is_Active varchar2(1) NOT NULL, Comments varchar2(1000), PRIMARY KEY (ID))

CREATE TABLE Menu_Category (ID number(10) GENERATED AS IDENTITY, Menu_ID number(10) NOT NULL, Category_ID number(10) NOT NULL, Description varchar2(255), PRIMARY KEY (ID), CONSTRAINT UK_Menu_Category UNIQUE (Menu_ID, Category_ID))

CREATE TABLE Menu_Hour (ID number(10) GENERATED AS IDENTITY, Menu_ID number(10) NOT NULL, Week_Day_ID number(10) NOT NULL, Start_Hour_ID number(10) NOT NULL, End_Hour_ID number(10) NOT NULL, Comments varchar2(1000), PRIMARY KEY (ID))

CREATE TABLE Region (ID number(10) GENERATED AS IDENTITY, Name varchar2(50) NOT NULL, Country_ID number(10) NOT NULL, PRIMARY KEY (ID), CONSTRAINT UK_Region UNIQUE (Name, Country_ID))

CREATE TABLE Role_Type (ID number(10) GENERATED AS IDENTITY, Name varchar2(50) NOT NULL UNIQUE, Description varchar2(255), PRIMARY KEY (ID))

CREATE TABLE Setting (ID number(10) GENERATED AS IDENTITY, Store_ID number(10) NOT NULL UNIQUE, Language_ID number(10) NOT NULL, Currency_ID number(10) NOT NULL, Average_Preparation_Time number(3) NOT NULL, Is_Busy_Mode varchar2(1) NOT NULL, Busy_Preparation_Time number(3) NOT NULL, Is_Ordering_Pause varchar2(1) NOT NULL, Public_Reviews varchar2(1) NOT NULL, Comments varchar2(1000), PRIMARY KEY (ID))

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

CREATE TABLE Time_Zone (ID number(10) GENERATED AS IDENTITY, Name varchar2(50) NOT NULL UNIQUE, Description varchar2(255), PRIMARY KEY (ID))

CREATE TABLE Week_Day (ID number(10) GENERATED AS IDENTITY, Name varchar2(50) NOT NULL UNIQUE, Description varchar2(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)