SQL Database #1

CREATE DATABASE PCTrade_2a

CREATE TABLE REGIONS
(
    REGION_ID TINYINT PRIMARY KEY,
    NAME VARCHAR(25) not null,

)

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

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

CREATE TABLE COUNTRIES
(
    COUNTRY_ID CHAR(2) PRIMARY KEY,
    NAME VARCHAR(40) NOT NULL,
    REGION_ID TINYINT FOREIGN KEY REFERENCES REGIONS ON DELETE SET NULL,

)

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

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

CREATE TABLE EMPLOYEES
(
    EMPLOYEE_ID INT PRIMARY KEY,
    FNAME VARCHAR(20) NOT NULL,
    LNAME VARCHAR(20) NOT NULL,
    EMAIL VARCHAR(20) 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 FOREIGN KEY REFERENCES JOBS,
    MANAGER_ID INT FOREIGN KEY REFERENCES EMPLOYEES,
    DEPARTMENT_ID INT FOREIGN KEY REFERENCES DEPARTMENTS
)

ALTER TABLE DEPARTMENTS
ADD FOREIGN KEY(MANAGER_ID) REFERENCES EMPLOYEES

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

CREATE TABLE ORDER_ITEMS
(
    ORDER_ID INT NOT NULL FOREIGN KEY REFERENCES ORDERS ON DELETE CASCADE,
    PRODUCT_ID INT NOT NULL FOREIGN KEY REFERENCES PRODUCTS,
    PRIMARY KEY(ORDER_ID, PRODUCT_ID),
    UNIT_PRICE NUMERIC(8,2) NOT NULL,
    QUANTITY NUMERIC(8) NOT NULL

)