RelationalDesign: Kreiranje.sql

create table project.Korisnik(
id_korisnik serial primary key, 
ime_prezime varchar(100) not null,
password_korisnik varchar(100) not null,
kreiran_na_datum timestamp default now()
);


create table project.Kupuvac(
id_korisnik serial primary key,
mail varchar(100) not null,
constraint fk_kupuvac foreign key (id_korisnik) references project.Korisnik (id_korisnik)
);

create table project.Admin_korisnik(
id_korisnik serial primary key,
constraint fk_admin foreign key (id_korisnik) references project.Korisnik (id_korisnik)
);



create table project.Dostavuvac(
id_korisnik serial primary key,
constraint fk_dostavuvac foreign key (id_korisnik) references project.Korisnik (id_korisnik)
);



create table project.Magacioner(
id_korisnik serial primary key,
id_magacin serial,
constraint fk_magacioner_magacin foreign key(id_magacin) references project.Magacin (id_magacin),
constraint fk_magacioner foreign key (id_korisnik) references project.Korisnik (id_korisnik)
);


create table project.TelefonskiBroj(
id_korisnik serial references project.Kupuvac(id_korisnik),
telefonski_broj varchar(50),
constraint pk_telefonski_broj_ primary key (id_korisnik,telefonski_broj)
);


create table project.Naracka(
id_naracka serial primary key,
vkupno_suma integer not null,
datum date not null,
status varchar(50) not null,
adresa varchar(100) not null,
id_korisnik serial,
constraint fk_kupuvac foreign key(id_korisnik) references project.Kupuvac (id_korisnik)
);



create table project.Dostava(
id_korisnik serial,
id_naracka serial,
id_dostava serial primary key,
datum date not null,
status varchar(50) not null,
constraint fk_kupuvac foreign key(id_korisnik) references project.Dostavuvac (id_korisnik),
constraint fk_naracka foreign key (id_naracka) references project.Naracka (id_naracka)
);



create table project.Magacin(
id_magacin serial primary key,
lokacija varchar(50) not null
);



create table project.Kategorija(
id_kategorija serial primary key,
ime varchar(50) not null
);


create table project.Produkt(
id_produkt serial primary key,
ime varchar(50) not null,
opis varchar(200),
id_korisnik serial,
constraint fk_produkt_admin foreign key (id_korisnik) references project.Admin_korisnik(id_korisnik)
);


create table project.Cena(
id_produkt serial,
cena_od date not null,
cena_do date,
iznos integer not null,
constraint pk_cena primary key (id_produkt, cena_od),
constraint fk_cena_produkt foreign key (id_produkt) references project.Produkt (id_produkt)
);


create table project.naracka_sodrzi_produkt(
id_produkt serial,
id_naracka serial,
kolicina integer not null,
cena integer not null,
constraint pk_naracka_sodrzi_produkt primary key(id_produkt, id_naracka),
constraint fk_n_s_p_p foreign key (id_produkt) references project.Produkt(id_produkt),
constraint fk_n_s_p_n foreign key (id_naracka) references project.Naracka(id_naracka)
);


create table project.produkt_e_vo_magacin(
id_produkt serial,
id_magacin serial,
kolicina integer not null,
constraint pk_produkt_e_vo_magacin primary key(id_produkt, id_magacin),
constraint fk_p_e_vo_m_p foreign key (id_produkt) references project.Produkt (id_produkt),
constraint fk_p_e_vo_m_m foreign key (id_magacin) references project.Magacin (id_magacin)
);



create table project.produkt_e_od_kategorija(
id_produkt serial, 
id_kategorija serial,
constraint pk_produkt_e_od_kategorija primary key (id_produkt, id_kategorija),
constraint fk_p_e_od_k_p foreign key (id_produkt) references project.Produkt(id_produkt),
constraint fk_p_e_od_k_k foreign key (id_kategorija) references project.Kategorija(id_kategorija)
);


drop table project.Kupuvac;
drop table project.Korisnik;
drop table project.Admin_korisnik;
drop table project.Dostavuvac;
drop table project.Magacioner;
drop table project.Telefonskibroj;
drop table project.Dostava;
drop table project.Magacin;
drop table project.Naracka;
drop table project.Kategorija;
drop table project.Produkt;
drop table project.Cena;
drop table project.naracka_sodrzi_produkt;
drop table project.produkt_e_vo_magacin;
drop table project.produkt_e_od_kategorija;