13

--създаваме база данни
CREATE DATABASE TRADE_MASTERS
--достъпваме база данни
USE TRADE_MASTERS

--настройваме колацията на конкретната база да е кирилица
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,
    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'
    CONSTRAINT CUST_GENDER CHECK( GENDER IS NULL OR (GENDER IN('M','F'))),
    CONSTRAINT OK_CUSTOMERS PRIMARY KEY(CUSTOMER_ID)
)

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,
    FNAME VARCHAR(20) NOT NULL,
    LNAME VARCHAR(25) NOT NULL,
    EMAIL VARCHAR(40) NOT NULL,
    PHONE VARCHAR(20) NULL,
    HIRE_DATE DATETIME NOT NULL,
    SALARY NUMERIC(8,2) NOT NULL CONSTRAINT SALARY_CHECK CHECK(SALARY>0),
    JOB_ID VARCHAR(10) NOT NULL,
    MANAGER_ID INT NULL,
    DEPARTMENT_ID INT NULL,
    CONSTRAINT PK_EMPLOYEES PRIMARY KEY(EMPLOYEE_ID),
    CONSTRAINT UK_EMAIL UNIQUE(EMAIL),
    CONSTRAINT FK_EMPLOYEES_JOBS FOREIGN KEY (JOB_ID)
        REFERENCES JOBS(JOB_ID),
    CONSTRAINT FK_EMPLOYEES_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 NULL,
    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_MGR FOREIGN KEY (MANAGER_ID)
        REFERENCES EMPLOYEES(EMPLOYEE_ID),
    CONSTRAINT FK_DEPT_COUNTRIES FOREIGN KEY (COUNTRY_ID)
        REFERENCES COUNTRIES(COUNTRY_ID)
)

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


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)
)

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 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_ORDER_ITEMS PRIMARY KEY(ORDER_ID, PRODUCT_ID),
    CONSTRAINT FK_OI_ORDERS FOREIGN KEY(ORDER_ID)
        REFERENCES ORDERS(ORDER_ID) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT FK_OI_PRODUCTS FOREIGN KEY(PRODUCT_ID)
        REFERENCES PRODUCTS(PRODUCT_ID)
)