p2: kreiranje.sql

set search_path = project;

drop table if exists se_naogja2;
drop table if exists se_naogja1;
drop table if exists ja_ima;
drop table if exists so_koi;
drop table if exists opsluzuva;
drop table if exists od_kade;
drop table if exists sodrzi;
drop table if exists pripagja;
drop table if exists zadolzen_za;

drop table if exists komentari;
drop table if exists naracki;
drop table if exists sliki;
drop table if exists posetiteli;
drop table if exists vraboteni;
drop table if exists korisnici;
drop table if exists restorani;
drop table if exists lokacii;
drop table if exists prodavnici;
drop table if exists recepti;
drop table if exists potekla;
drop table if exists sostojki;
drop table if exists kategorii;

drop schema if exists project;

create schema project;

set search_path = project;

create table kategorii(
        kat_id integer,
        kat_ime varchar(400) unique not null,
        opis varchar(2000),
        
        constraint pk_kategorii primary key (kat_id)
);

create table sostojki(
        s_id integer,
        s_naziv varchar(400) unique not null,
        
        constraint pk_sostojki primary key(s_id)
        );
        
create table potekla(
        pot_id integer,
        pot_naziv varchar(400) unique not null,
        
        constraint pk_potekla primary key(pot_id)
);
--added rec_ime
create table recepti(
        rec_id bigint,
        postapka varchar(4000),
        rec_ime varchar(200),
        
        constraint pk_recepti primary key (rec_id)
);

create table prodavnici(
        pro_id integer,
        pro_naziv varchar(200),
        
        constraint pk_prodavnici primary key (pro_id)
);

create table lokacii(
        l_id integer,
        adresa varchar(300) not null,
        
        constraint pk_lokacii primary key (l_id)
);
        
create table restorani(
        res_id integer,
        res_naziv varchar(200),
        
        constraint pk_restorani primary key (res_id)
);

create table korisnici(
        telefon char(9),
        kor_ime varchar(200) not null,
        prezime varchar(400),
        
        constraint pk_korisnici primary key (telefon)
);

create table vraboteni(
        telefon char(9),
        
        constraint pk_vraboteni primary key (telefon),
        constraint fk_vraboteni foreign key (telefon) references korisnici(telefon)
);

create table posetiteli(
        telefon char(9),
        
        constraint pk_posetiteli primary key (telefon),
        constraint fk_posetiteli foreign key (telefon) references korisnici(telefon)
);

create table sliki(
        reden_broj integer,
        rec_id bigint,
        slika bytea not null,
        
        constraint pk_sliki primary key (reden_broj, rec_id),
        constraint fk_sliki foreign key (rec_id) references recepti(rec_id)
);

--vreme, n_data -> vreme
create table naracki(
        vreme timestamp,
        telefon char(9),
        
        constraint pk_naracki primary key (vreme, telefon),
        constraint fk_naracki foreign key (telefon) references posetiteli(telefon)
);

create table komentari(
        kom_data timestamp,
        telefon char(9),
        rec_id bigint,
        ocena smallint not null,
        text varchar(2000),
        
        constraint pk_komentari primary key(kom_data, telefon, rec_id),
        constraint fk_komentari_telefon foreign key (telefon) references posetiteli(telefon),
        constraint fk_komentari_rec_id foreign key (rec_id) references recepti(rec_id),
        constraint in_range_ocena check(ocena >= 1 and ocena <= 10)
        );

--relacii

create table zadolzen_za(
        v_telefon char(9),
        vreme timestamp,
        p_telefon char(9),
        
        constraint pk_zadolzen_za primary key (v_telefon, vreme, p_telefon),
        constraint fk_zadolzen_za_v_telefon foreign key (v_telefon) references vraboteni(telefon),
        constraint fk_zadlozen_za_n_vreme foreign key (vreme, p_telefon) references naracki(vreme, telefon)
        );

create table pripagja(
        kat_id integer,
        rec_id bigint,
        constraint pk_pripagja primary key (kat_id, rec_id),
        constraint fk_pripagja_kat foreign key (kat_id) references kategorii(kat_id),
        constraint fk_pripagja_rec foreign key (rec_id) references recepti(rec_id)
);

create table sodrzi(
        s_id integer,
        rec_id bigint,
        constraint pk_sodrzi primary key (s_id, rec_id),
        constraint fk_sodrzi_s_id foreign key (s_id) references sostojki(s_id),
        constraint fk_sodrzi_rec_id foreign key (rec_id) references recepti(rec_id)
);


create table od_kade(
        pot_id integer,
        rec_id bigint,
        constraint pk_od_kade primary key (pot_id, rec_id),
        constraint fk_od_kade_pot_id foreign key (pot_id) references potekla(pot_id),
        constraint fk_od_kade_rec_id foreign key (rec_id) references recepti(rec_id)
);


create table opsluzuva(
                res_id integer,
                rec_id bigint,
                constraint pk_opsluzuva primary key (res_id, rec_id),
                constraint rec_id_not_unique unique (rec_id),
                constraint fk_opsluzuva_res_id foreign key (res_id) references restorani(res_id),
                constraint fk_opsluzuva_rec_id foreign key (rec_id) references recepti(rec_id)
        );

create table so_koi(
        vreme timestamp,
        telefon char(9),
        rec_id bigint,
        nacin varchar(20),
        constraint pk_so_koi primary key (vreme, telefon, rec_id),
        constraint fk_so_koi_naracki foreign key (vreme, telefon) references naracki(vreme, telefon),
        constraint fk_opsluzuva_rec_id foreign key (rec_id) references recepti(rec_id)
);

create table ja_ima(
        s_id integer,
        pro_id integer,
        kolicina varchar(50),
        cena integer,
        constraint pk_ja_ima primary key (s_id, pro_id),
        constraint fk_ja_ima_sostojki foreign key (s_id) references sostojki(s_id),
        constraint fk_ja_ima_pro foreign key (pro_id) references prodavnici(pro_id)
);

create table se_naogja1(
        res_id integer,
        l_id integer,
        constraint pk_se_naogja1 primary key (res_id, l_id),
        constraint fk_se_naogja1_l_id foreign key (l_id) references lokacii(l_id),
        constraint fk_se_naogja1_res_id foreign key (res_id) references restorani(res_id)
);

create table se_naogja2(
        pro_id integer,
        l_id integer,
        constraint pk_se_naogja2 primary key (pro_id, l_id),
        constraint fk_se_naogja2_l_id foreign key (l_id) references lokacii(l_id),
        constraint fk_se_naogja2_pro_id foreign key (pro_id) references prodavnici(pro_id)
);