RelationalDesign: kreiranje.sql

create table korisnik(
        user_id serial primary key,
        user_ime varchar(50) not null,
        user_prezime varchar(50) not null,
        user_password varchar(300) not null,
        user_admin_id integer not null
        --treba fk da se dodade do sistemski_admin so alter table
);

create table sistemski_admin(
        user_id integer primary key,
        constraint fk_user foreign key (user_id) references korisnik (user_id)
        on delete cascade on update cascade
);

alter table korisnik add constraint fk_korisnik_administriran foreign key (user_admin_id) references sistemski_admin(user_id)
on delete cascade on update cascade;

create table kupuvac(
        user_id serial primary key,
        kupuvac_tel_broj varchar(50) not null,
        constraint fk_user foreign key (user_id) references korisnik (user_id)
        on delete cascade on update cascade
);

create table dostavuvac(
        user_id serial primary key,
        dostavuvac_vraboten_od date not null,
        constraint fk_user foreign key (user_id) references korisnik (user_id)
        on delete cascade on update cascade
);

create table menadzer(
        user_id serial primary key,
        menadzer_vraboten_od date not null,
        constraint fk_user foreign key (user_id) references korisnik (user_id)
        on delete cascade on update cascade
);

create table restoran(
        restoran_id serial primary key,
        restoran_ime varchar(50) not null,
        restoran_rejting integer not null,
        restoran_adresa varchar(50) not NULL,
        user_id integer NOT null,
        constraint fk_restoran_menadzer foreign key(user_id) references menadzer(user_id) on delete cascade on update cascade  
);

create table tip(
        tip_id serial primary key,
        tip_ime varchar(50) not null
);

create table meni(
        meni_id serial primary key,
        tip_id integer not null,
        restoran_id integer not null,
        constraint fk_meni_tip foreign key (tip_id) references tip(tip_id) on delete cascade on update cascade,
        constraint fk_meni_restoran foreign key (restoran_id) references restoran(restoran_id) on delete cascade on update cascade
);

create table obrok(
        obrok_id serial primary key,
        obrok_opis varchar(10000) not null,
        obrok_ime varchar(50) not null,
        meni_id integer not null,
        constraint fk_obrok_meni foreign key(meni_id) references meni(meni_id) on delete cascade on update cascade
);

create table rezervacija(
        rezervacija_id serial primary key,
        rezervacija_vreme timestamp not null,
        br_lugje integer not null,
        rezervacija_status varchar(50) not null,
        rezervacija_opis varchar(10000) not null,
        restoran_id integer not null,
        user_id integer not null,
        constraint fk_rezervacija_restoran foreign key(restoran_id) references restoran(restoran_id) on delete cascade on update cascade,
        constraint fk_rezervacija_kupuvac foreign key (user_id) references kupuvac(user_id) on delete cascade on update cascade
);

create table naracka(
        naracka_id serial primary key,
        naracana_na timestamp not null, 
        naracka_status varchar(50) not null,
        naracka_cena_vkupna integer not null,
        user_id_kupuvac integer not null,
        user_id_dostavuvac integer not null,
        constraint fk_naracka_kupuvac foreign key (user_id_kupuvac) references kupuvac(user_id) on delete cascade on update cascade,
        constraint fk_naracka_dostavuvac foreign key(user_id_dostavuvac) references dostavuvac(user_id)on delete cascade on update cascade
);

create table restoran_tel_broj(
        restoran_id integer not null,
        broj varchar(50),
        constraint pk_restoran_tel_broj primary key (restoran_id,broj),
        constraint fk_restoran_tel_broj_restoran foreign key(restoran_id) references restoran(restoran_id) on delete cascade on update cascade
);

create table cena(
        obrok_id integer not null,
        cena_id integer not null,
        cena_iznos integer not null,
        cena_vazi_od date not null,
        cena_vazi_do date not null,
        constraint pk_cena primary key (obrok_id,cena_id),
        constraint fk_cena_obrok foreign key (obrok_id) references obrok(obrok_id) on delete cascade on update cascade
);

create table rezervira(
        rezervacija_id integer not null,
        obrok_id integer not null,
        constraint pk_rezervira primary key(rezervacija_id,obrok_id),
        constraint fk_rezervira_rezervacija foreign key (rezervacija_id) references rezervacija(rezervacija_id) on delete cascade on update cascade,
        constraint fk_rezervira_obrok foreign key (obrok_id) references obrok(obrok_id) on delete cascade on update cascade     
);

create table se_sostoi_od(
        naracka_id integer not null,
        obrok_id integer not null,
        kolicina integer not null,
        cena integer not null,
        constraint pk_se_sostoi_od primary key (naracka_id,obrok_id),
        constraint fk_se_sostoi_od_naracka foreign key (naracka_id) references naracka(naracka_id) on delete cascade on update cascade,
        constraint fk_se_sostoi_od_obrok foreign key (obrok_id) references obrok(obrok_id) on delete cascade on update cascade
);

-- drop tables
drop table korisnik;
drop table sistemski_admin;
drop table kupuvac;
drop table dostavuvac;
drop table menadzer;
drop table restoran;
drop table tip;
drop table meni;
drop table obrok;
drop table rezervacija;
drop table naracka;
drop table restoran_tel_broj;
drop table cena;
drop table rezervira;
drop table se_sostoi_od;