RelationalDesign: kreiranje_v1.sql

drop table if exists user_table cascade;
drop table if exists administrator cascade;
drop table if exists delivery_man cascade;
drop table if exists service_man cascade;
drop table if exists customer cascade;
drop table if exists warehouseman cascade;
drop table if exists warehouse cascade;
drop table if exists vehicle cascade;
drop table if exists delivery cascade;
drop table if exists review cascade;
drop table if exists order_table cascade;
drop table if exists manufacturer cascade;
drop table if exists category cascade;
drop table if exists subcategory cascade;
drop table if exists product cascade;
drop table if exists order_table_contains_product cascade;
drop table if exists product_is_in_category cascade;
drop table if exists product_is_in_stock_in_warehouse cascade;
drop table if exists vehicle_is_used_for_delivery cascade;


create table user_table (
    user_id serial primary key,
    username varchar(100) unique not null,
    name_user varchar(100) not null,  
    email varchar(100) unique not null,
    password varchar(100) not null,
    phone_number varchar(100),
    administrator_id integer
);
   
create table administrator(
    user_id serial primary key,
        
    constraint user_id_fk foreign key(user_id)
        references user_table(user_id)
);

alter table user_table add
        constraint administrator_id_fk foreign key(administrator_id)
        references administrator(user_id);
        
create table delivery_man(
    user_id serial primary key,
       
    constraint user_id_fk foreign key(user_id)
        references user_table(user_id)
);

create table service_man(
    user_id serial primary key,
       
    constraint user_id_fk foreign key(user_id)
        references user_table(user_id)
);

create table customer(
    user_id serial primary key,
        
    constraint user_id_fk foreign key(user_id)
        references user_table(user_id)
);

create table warehouse(
        warehouse_id serial primary key,
        warehouse_location varchar(100) not null
);

create table warehouseman(
    user_id serial primary key,
    warehouse_id integer not null,
       
    constraint user_id_fk foreign key(user_id)
    references user_table(user_id),
    constraint warehouse_id_fk foreign key(warehouse_id)
    references warehouse(warehouse_id)
);


create table order_table (
        order_id serial primary key,
        order_status varchar(100) not null,
        order_date Date not null,
        customer_id integer not null,
        
        constraint customer_id_fk foreign key(customer_id)
        references customer(user_id)
);


create table vehicle (
        vehicle_id serial primary key,
        vehicle_model varchar(100),
        vehicle_service Date,
        delivery_man_id integer not null,
        
        constraint delivery_man_id_fk foreign key(delivery_man_id)
        references delivery_man(user_id)
);

create table delivery (
        delivery_id serial primary key,
        delivery_status varchar(100) not null,
        delivery_address varchar(100) not null,
        delivery_man_id integer not null,
        order_id integer not null,
        
        constraint delivery_man_id_fk foreign key(delivery_man_id)
        references delivery_man(user_id),
        constraint order_id_fk foreign key(order_id)
        references order_table(order_id)
);



create table category (
        category_id serial primary key,
        category_name varchar(100) not null
);


create table subcategory (
        subcategory_id serial primary key,
        subcategory_name varchar(100) not null,
        category_id integer not null,
        
        constraint category_id_fk foreign key(category_id)
        references category(category_id)
);

create table manufacturer (
        manufacturer_id serial primary key,
        manufacturer_name varchar(100)
);

create table product (
        product_id serial primary key,
        product_name varchar(100) not null,
        product_description varchar(100) not null,
        product_price integer not null,
        product_warranty integer not null,
        product_image varchar not null,
        manufacturer_id integer not null,
        service_man_id integer not null,
        
        
        constraint manufacturer_id_fk foreign key(manufacturer_id)
        references manufacturer(manufacturer_id),
        constraint service_man_fk foreign key(service_man_id)
        references service_man(user_id)
);

create table review (
        review_id serial primary key,
        review_rating integer not null,
        review_description varchar(100) not null,
        customer_id integer not null,
        product_id integer not null,
        
        constraint customer_id_fk foreign key(customer_id)
        references customer(user_id),
        constraint product_id_fk foreign key(product_id)
        references product(product_id)
        
);

create table vehicle_is_used_for_delivery (
        vehicle_id integer not null,
        delivery_id integer not null,
        
        constraint vehicle_id_fk foreign key(vehicle_id)
        references vehicle(vehicle_id),
        constraint delivery_id_fk foreign key(delivery_id)
        references delivery(delivery_id)
);

create table product_is_in_stock_in_warehouse (
        product_id integer not null,
        warehouse_id integer not null,
        quantity integer not null,
        
        constraint product_id_fk foreign key(product_id)
        references product(product_id),
        constraint warehouse_id_fk foreign key(warehouse_id)
        references warehouse(warehouse_id)
);

create table order_table_contains_product (
        order_id integer not null,
        product_id integer not null,
        quantity integer not null,
        
        constraint order_id_fk foreign key(order_id)
        references order_table(order_id),
        constraint product_id_fk foreign key(product_id)
        references product(product_id)
);

create table product_is_in_category (
        product_id integer not null,
        category_id integer not null,
        
        constraint product_id_fk foreign key(product_id)
        references product(product_id),
        constraint category_id_fk foreign key(category_id)
        references category(category_id)
);