_

CREATE TABLE REGIONS
(
    REGION_ID SMALLINT NOT NULL PRIMARY KEY IDENTITY(1,1),
    NAME VARCHAR(25) NOT NULL UNIQUE
)

CREATE TABLE COUNTRIES
(
    COUNTRY_ID CHAR(2) NOT NULL,
    NAME VARCHAR(40) NOT NULL,
    REGION_ID SMALLINT,

    CONSTRAINT PK_COUNTRIES PRIMARY KEY (COUNTRY_ID),
    CONSTRAINT FK_COUNTRIES_REGIONS FOREIGN KEY (REGION_ID)
        REFERENCES REGIONS (REGION_ID)
)

CREATE TABLE CUSTOMERS
(
    CUSTOMER_ID NUMERIC(6) NOT NULL PRIMARY KEY,
    COUNTRY_ID CHAR(2) NOT NULL,
    FNAME VARCHAR(20) NOT NULL,
    LNAME VARCHAR(20) NOT NULL,
    ADDRESS TEXT,
    EMAIL VARCHAR(30),
    GENDER CHAR(1) DEFAULT 'M'
        CHECK(GENDER IS NULL OR (GENDER IN('M','F')))
)

ALTER TABLE CUSTOMERS
ADD CONSTRAINT FK_CUSTOMERS_COUNTRIES FOREIGN KEY(COUNTRY_ID)
    REFERENCES COUNTRIES(COUNTRY_ID)

CREATE TABLE JOBS
(
    JOB_ID VARCHAR(10) NOT NULL PRIMARY KEY,
    JOB_TITLE VARCHAR(35) NOT NULL,
    MIN_SALARY NUMERIC(6),
    MAX_SALARY NUMERIC(6),
)

CREATE TABLE EMPLOYEES
(
    EMPLOYEE_ID INT NOT NULL PRIMARY KEY,
    FNAME VARCHAR(20) NOT NULL,
    LNAME VARCHAR(25) NOT NULL,
    EMAIL VARCHAR(40) NOT NULL UNIQUE,
    PHONE VARCHAR(20),
    HIRE_DATE DATETIME NOT NULL,
    SALARY NUMERIC(8,2) NOT NULL CHECK(SALARY>0),
    JOB_ID VARCHAR(10) NOT NULL REFERENCES JOBS,
    MANAGER_ID INT,
    DEPARTMENT_ID INT,

    CONSTRAINT FK_EMPLOYEE_MANAGERS FOREIGN KEY(MANAGER_ID)
        REFERENCES EMPLOYEES(EMPLOYEE_ID)
)

CREATE TABLE DEPARTMENTS
(
    DEPARTMENT_ID INT NOT NULL,
    NAME VARCHAR(30) NOT NULL,
    MANAGER_ID INT,
    COUNTRY_ID CHAR(2) NOT NULL,
    CITY VARCHAR(30) NOT NULL,
    STATE VARCHAR(25),
    ADDRESS VARCHAR(40),
    POSTAL_CODE VARCHAR(12),

    CONSTRAINT PK_DEPARTMENTS PRIMARY KEY (DEPARTMENT_ID),
    CONSTRAINT FK_DEPT_COUNTRIES FOREIGN KEY (COUNTRY_ID)REFERENCES COUNTRIES(COUNTRY_ID),
    CONSTRAINT FK_DEPT_MANGR FOREIGN KEY (MANAGER_ID)REFERENCES EMPLOYEES(EMPLOYEE_ID)
)

ALTER TABLE EMPLOYEES
ADD CONSTRAINT FK_EMPL_DEPT FOREIGN KEY (DEPARTMENT_ID)
REFERENCES DEPARTMENTS(DEPARTMENT_ID)

CREATE TABLE ORDERS
(
    ORDER_ID INT NOT NULL PRIMARY KEY,
    ORDER_DATE DATETIME NOT NULL,
    CUSTOMER_ID NUMERIC(6) NOT NULL REFERENCES CUSTOMERS,
    EMPLOYEE_ID INT NOT NULL REFERENCES EMPLOYEES,
    SHIP_ADDRESS VARCHAR(150)
)

CREATE TABLE PRODUCTS
(
    PRODUCT_ID INT NOT NULL PRIMARY KEY,
    NAME VARCHAR(70) NOT NULL,
    PRICE NUMERIC(8,2) NOT NULL,
    DESCR VARCHAR(2000)
)

CREATE TABLE ORDER_ITEMS
(
    ORDER_ID INT NOT NULL,
    PRODUCT_ID INTEGER NOT NULL,
    UNIT_PRICE NUMERIC(8,2) NOT NULL,
    QUANTITY NUMERIC(8) NOT NULL,

    CONSTRAINT PK_OI PRIMARY KEY(ORDER_ID, PRODUCT_ID),

    CONSTRAINT FK_OI_O FOREIGN KEY (ORDER_ID)REFERENCES ORDERS (ORDER_ID)
            ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT FK_OI_P FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCTS(PRODUCT_ID)
)