tradecompany

-- create database tradecompany       -- създава база от данни
-- go                                 -- batch separator
-- use tradecompany                   -- достъпва създадената база данни
-- (но в нашия случай е създадена предварително - индивидуално с факултетен номер)
--  т.е. минаваме дректно към създаване на създаване на таблици
-- execute всяка заявка поотделно
-----------------------------------------------------------------------------------
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_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'))),
     CONSTRAINT PK_CUSTOMERS PRIMARY KEY (CUSTOMER_ID)
)
-----------------------------------------------------------------------------------
ALTER TABLE CUSTOMERS
 ADD CONSTRAINT FK_CUSTOMER_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,
     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_EMPLOYEE_JOBS FOREIGN KEY (JOB_ID) REFERENCES JOBS (JOB_ID),
     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 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 PK_DEPT 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) NULL
)
-----------------------------------------------------------------------------------
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_ORD_ITEM_ORDERS FOREIGN KEY (ORDER_ID)
     REFERENCES ORDERS (ORDER_ID)
     ON UPDATE CASCADE ON DELETE CASCADE,
     CONSTRAINT FK_ORD_ITEM_PRODUCTS FOREIGN KEY (PRODUCT_ID)
     REFERENCES PRODUCTS (PRODUCT_ID)
)