06.10.2022

CREATE DATABASE TRADECOMPANY
USE TRADECOMPANY

ALTER DATABASE CURRENT COLLATE CYRILLIC_GENERAL_CI_AI

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 NULL,

    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  NULL,
    EMAIL VARCHAR(30)  NULL,
    GENDER CHAR(1) NULL 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) NULL,
    MAX_SALARY NUMERIC(6) NULL
)

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) NULL,
    HIRE_DATE DATETIME NOT NULL,
    SALARY NUMERIC(8,2) NOT NULL CHECK(SALARY>0),
    JOB_ID VARCHAR(10) NOT NULL,
    MANAGER_ID INT NULL,
    DEPARTMENT_ID INT NULL,

    CONSTRAINT FK_EMP_JOB FOREIGN KEY (JOB_ID)
        REFERENCES JOBS(JOB_ID),
    CONSTRAINT FK_EMP_MNG FOREIGN KEY (MANAGER_ID)
        REFERENCES EMPLOYEES(EMPLOYEE_ID)
)

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

    CONSTRAINT FK_DEPT_COUNTRY FOREIGN KEY (COUNTRY_ID)
        REFERENCES COUNTRIES(COUNTRY_ID),
    CONSTRAINT FK_DEPT_MNG FOREIGN KEY (MANAGER_ID)
        REFERENCES EMPLOYEES(EMPLOYEE_ID)
)

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

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

CREATE TABLE ORDERS
(
    ORDER_ID INT NOT NULL 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) NULL

    --CONSTRAINT FK_O_CUST FOREIGN KEY (CUSTOMER_ID)
    --REFERENCES CUSTOMERS(CUSTOMER_ID),
    --CONSTRAINT FK_O_EMP FOREIGN KEY (EMPLOYEE_ID)
    --REFERENCES EMPLOYEES  (EMPLOYEE_ID)
)

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

    CONSTRAINT FK_OI_PRODUCTS FOREIGN KEY (PRODUCT_ID)
        REFERENCES PRODUCTS(PRODUCT_ID),
    CONSTRAINT FK_OI_ORDERS FOREIGN KEY (ORDER_ID)
        REFERENCES ORDERS (ORDER_ID)
            ON UPDATE CASCADE ON DELETE CASCADE
)