pctrade

CREATE DATABASE PCTrade

USE PCTrade

create table REGIONS
(
    REGION_ID smallint not null,
    NAME varchar(25) not null,
    constraint PK_REGIONS primary key (REGION_ID)
)

create table COUNTRIES
(
    COUNTRY_ID char(2) not null,
    NAME varchar(40) not null,
    REGION_ID smallint null,
    constraint PK_COUNTRY 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')), -- Ограничение cust_gender за проверка на въвеждани стойности в поле gender. Допустимите стойности са ‘M’ и ‘F’.
    constraint PK_CUSTOMERS primary key (CUSTOMER_ID)
)

-- Промяна на структурата на създадена вече таблица става с ALTER TABLE.
alter table CUSTOMERS
ADD constraint FK_CUSTOMER_COUNTRY foreign key (COUNTRY_ID)
    references COUNTRIES(COUNTRY_ID)


create table PRODUCTS
(
    PRODUCT_ID int not null,
    NAME varchar(50) not null,
    PRICE numeric(8,2) not null,
    DESCR varchar(2000) null,
    constraint PK_PRODUCTS primary key (PRODUCT_ID)
)


create table JOBS
(
    JOB_ID varchar(10) not null,
    JOB_TITLE varchar(35) not null,
    MIN_SALARY numeric(6),
    MAX_SALARY numeric(6),
    constraint PK_JOB primary key (JOB_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),
    STATE varchar(25),
    ADDRESS varchar(40),
    POSTAL_CODE varchar(12) null,
    constraint PK_DEPT primary key (DEPARTMENT_ID)
)

alter table DEPARTMENTS
add constraint FK_DEPT_COUNTR foreign key (COUNTRY_ID)
    references COUNTRIES (COUNTRY_ID)




create table EMPLOYEES
(
    EMPLOYEE_ID int not null,
    FNAME varchar(20) not null,
    LNAME varchar(25) not null,
    EMAIL varchar(25) not null,
    PHONE varchar(20),
    HIRE_DATE datetime not null,
    SALARY numeric(8,2) not null
        constraint EMP_SALARY_MIN check (SALARY > 0),

    JOB_ID varchar(10) not null,
    MANAGER_ID int null,
    DEPARTMENT_ID int null,

    constraint PK_EMP primary key (EMPLOYEE_ID),
    constraint UK_EMP unique (EMAIL),           -- UK_EMP върху EMAIL, гарантиращо въвеждането на уникални стойности в колоната

    constraint EMP_DEPT_FK foreign key (DEPARTMENT_ID) references DEPARTMENTS(DEPARTMENT_ID),
    constraint EMP_JOB_FK foreign key (JOB_ID) references JOBS(JOB_ID),
    constraint EMP_MANAGER_FK foreign key (MANAGER_ID) references EMPLOYEES(EMPLOYEE_ID)
)


alter table DEPARTMENTS
add constraint FK_DEPT_MGR foreign key (MANAGER_ID)
    references EMPLOYEES(EMPLOYEE_ID)

--За информацията за поръчките на клиентите
create table  ORDERS
(
    ORDER_ID int not null,
    ORDER_DATE datetime not null,
    CUSTOMER_ID numeric(6) not null,
    EMPLOYEE_ID int not null,
    SHIP_ADDRESS varchar(150) null,

    constraint PK_ORDER primary key (ORDER_ID),
    constraint FK_ORDERS_CUSTOMER foreign key (CUSTOMER_ID)
        references CUSTOMERS(CUSTOMER_ID),
    constraint FK_ORDERS_REFERENCE_EMPLOYEE foreign key (EMPLOYEE_ID)
        references EMPLOYEES(EMPLOYEE_ID)
)


--За информацията за продуктите, поръчани във всяка една от поръчките
create table ORDER_ITEMS
(
    ORDER_ID int not null,
    PRODUCT_ID integer not null,
    UNIT_PRICE numeric(8,2) not null,
    QUANTITY numeric(8),

    constraint PK_ORDER_ITEMS primary key (ORDER_ID, PRODUCT_ID),
    constraint FK_ORDER_ITEMS_ORDER_ID foreign key (ORDER_ID)
        references ORDERS(ORDER_ID)
             on delete cascade,
    constraint ORDER_ITEMS_PRODUCT_ID_FK foreign key (PRODUCT_ID)
        references PRODUCTS(PRODUCT_ID)
)