CREATE TABLE CUSTOMER (
   ID INTEGER GENERATED BY DEFAULT AS IDENTITY,
   FIRST_NAME VARCHAR(40) NOT NULL,
   LAST_NAME VARCHAR(40) NOT NULL,
   CITY VARCHAR(40),
   COUNTRY VARCHAR(40),
   PHONE VARCHAR(20),
   CONSTRAINT PK_CUSTOMER PRIMARY KEY (ID)
);

CREATE TABLE ORDERS (
   ID INTEGER GENERATED BY DEFAULT AS IDENTITY,
   ORDER_DATE DATE NOT NULL,
   CUSTOMER_ID  INTEGER,
   TOTAL_AMOUNT DECIMAL(12,2),
    ORDER_NUMBER VARCHAR(10),
   CONSTRAINT PK_ORDER PRIMARY KEY (ID)
);  

CREATE TABLE ORDER_ITEM (
   ID INTEGER GENERATED BY DEFAULT AS IDENTITY,
   ORDER_ID INTEGER NOT NULL,
   PRODUCT_ID INTEGER NOT NULL,
   UNIT_PRICE DECIMAL(12,2) NOT NULL,
   QUANTITY INTEGER NOT NULL,
   CONSTRAINT PK_ORDERITEM PRIMARY KEY (ID)
);

CREATE TABLE PRODUCT (
   ID INTEGER GENERATED BY DEFAULT AS IDENTITY,
   PRODUCT_NAME VARCHAR(50) NOT NULL,
   SUPPLIER_ID INTEGER NOT NULL,
   UNIT_PRICE DECIMAL(12,2),
   PACKAGE  VARCHAR(30),
   IS_DISCONTINUED CHAR(1),
   CONSTRAINT PK_PRODUCT PRIMARY KEY (ID)
);

CREATE TABLE SUPPLIER (
   ID INTEGER GENERATED BY DEFAULT AS IDENTITY,
   COMPANY_NAME VARCHAR(40) NOT NULL,
   CONTACT_NAME VARCHAR(50),
   CITY VARCHAR(40),
   COUNTRY VARCHAR(40),
   PHONE VARCHAR(30),
   FAX VARCHAR(30),
   CONSTRAINT PK_SUPPLIER PRIMARY KEY (ID)
);