LogicDesign: ddl_final.sql

create table Vraboteni(
id_vraboten serial ,
ime varchar(100) not null,
prezime varchar(100) not null,
embg varchar(13) not null,
datum_ragjanje date not null,
obrazovanie varchar(100) not null,
constraint pk_vraboten primary key (id_vraboten)
);
create table Menadzeri(
id_vraboten integer,
constraint pk_menadzer primary key (id_vraboten),
constraint fk_vraboteni_menadzer foreign key (id_vraboten) 
references vraboteni(id_vraboten)
);
create table Direktor(
id_vraboten integer,
constraint pk_director primary key (id_vraboten),
constraint fk_vraboteni_direktor foreign key (id_vraboten) 
references vraboteni(id_vraboten)
);
create table Magacioneri(
id_vraboten integer,
constraint pk_magacioner primary key (id_vraboten),
constraint fk_vraboteni_magacioner foreign key (id_vraboten) 
references vraboteni(id_vraboten)
);
create table Shefovi(
id_vraboten integer,
constraint pk_shef  primary key (id_vraboten),
constraint fk_vraboteni_shef foreign key (id_vraboten )
references vraboteni(id_vraboten)
);
create table Vraboteni_vo_proizvodstvo(
id_vraboten integer,
constraint pk_vrab_proizvodstvo  primary key (id_vraboten),
constraint fk_vraboten_proizvodstvo foreign key(id_vraboten)
references vraboteni(id_vraboten)
);
create table Korisnik(
id_korisnik serial,
korisnicko_ime varchar(100) not null,
lozinka varchar(300) not null,
constraint pk_korisnik primary key(id_korisnik)
);
create table Delovi(
id_del serial,
vid_na_del varchar(100) not null,
cena double precision not null,
constraint pk_delovi primary key(id_del)
);
create table Materijali(
id_materijal serial,
velicina double precision not null,
naziv varchar(50) not null,
dostapen_broj integer not null,
constraint pk_materijal primary key (id_materijal)
);
create table Mashini(
id_mashina serial,
naziv varchar(50) not null,
constraint pr_mashina primary key (id_mashina)
);
create table Oddelenija(
id_oddelenie serial,
naziv varchar(30) not null,
constraint pk_oddelenie primary key(id_oddelenie)
);
create table Mebel(
id_mebel serial,
tip varchar(100) not null,
naziv varchar(50) not null,
cena double precision not null,
constraint pk_mebel primary key (id_mebel)
);
create table Klienti(
id_klient serial,
korisnicko_ime varchar(50) not null,
lozinka varchar(300) not null ,
prezime varchar(100) not null,
ime varchar(100) not null,
constraint pk_klienti primary key(id_klient)
);
create table Nalozi(
id_nalog serial ,
krajna_data date ,
pocetna_data date not null,
id_vraboten integer not null,
constraint pk_nalog primary key(id_nalog),
constraint fk_nalog_menagzer foreign key (id_vraboten) references Menadzeri(id_vraboten)
);
create table Naracka(
id_naracka serial,
datum_napravena date not null,
datum_isporacana date ,
naslov varchar(100) not null,
opis text not null,
vkupna_cena double precision,
id_klient  integer not null,
constraint pk_naracka primary key (id_naracka),
constraint fk_naracal_klient  foreign key (id_klient) references klienti(id_klient)
);
create table Dogovori(
id_dogovor serial,
pocetok date not null,
plata double precision not null,
kraj date not null,
aktiven smallint not null,
id_vraboten integer,
constraint pk_dogovor primary key(id_dogovor),
constraint fk_vraboten_dogovor foreign key (id_vraboten) references vraboteni(id_vraboten)
);
create table Evidencija_na_naracki(
id_evidencija serial,
datum date not null,
id_naracka integer not null,
constraint pk_evidencija_na_naracki primary key(id_evidencija),
constraint fk_naracka_za_evidencija foreign key (id_naracka) references naracka(id_naracka)
);
create table ima_napraveno_servis(
id_servis serial,
id_mashina integer not null,
datum date not null,
zabeleska text not null,
cena double precision not null,
constraint pk_servis primary key (id_servis,id_mashina),
constraint fk_mashina_servis foreign key(id_mashina) references mashini(id_mashina)
);
--relacii mxn
create table naracka_se_sostoi_od_mebel(
id_mebel integer,
id_naracka integer,
kolicina integer not null,
constraint pk_naracka_se_sostoi_od_mebel primary key (id_mebel,id_naracka),
constraint fk_naracka_se_sostoi_mebel foreign key(id_mebel) references mebel(id_mebel),
constraint fk_naracka_se_sostoi_naracka foreign key(id_naracka) references naracka(id_naracka)
);
create table dopolniteli_delovi_za_naracka(
id_naracka integer,
id_del integer ,
kolicina integer not null,
constraint pk_dopolnistelni_delovi primary key(id_naracka,id_del),
constraint fk_dopolnitelni_delovi_naracka foreign key (id_naracka) references naracka(id_naracka),
constraint fk_dopolnitelni_delovi_delovi foreign key (id_del) references delovi(id_del)
);
create table mebel_se_sostoi_od_delovi(
id_mebel integer ,
id_del integer,
kolicina integer not null,
constraint pk_mebel_se_sostoi_od_delovi primary key(id_mebel,id_del),
constraint fk_mebel_sostoi_od_delov_mebel foreign key (id_mebel) references mebel(id_mebel),
constraint fk_mebel_sostoi_od_delov_del foreign key (id_del) references delovi(id_del)
);
create table evidencija_naracki_izraboteni_delovi(
id_evidencija integer,
id_del integer,
br_na_zavrseni_delovi integer not null,
constraint pk_evidencija_naracki_izraboteni_delovi primary key(id_evidencija,id_del),
constraint fk_evidencija_naracki_izraboteni_delovi_evidencija foreign key (id_evidencija) references evidencija_na_naracki(id_evidencija),
constraint fk_evidencija_naracki_izraboteni_delovi_delovi foreign key(id_del) references delovi(id_del)
);

--relacii nx1,nx1
create table nalozi_kreira_naracka(
id_nalog integer,
id_naracka integer not null,
constraint pk_nalozi_kreira_naracka primary key (id_nalog),
constraint fk_nalozi_kreira_naracka_nalog foreign key (id_nalog) references nalozi(id_nalog),
constraint fk_nalozi_kreira_naracka_naracka foreign key (id_naracka) references naracka(id_naracka)
);
create table evidencija_se_odnesuva_na_mebel(
id_evidencija integer,
id_mebel integer not null,
procenti_zavrsen double precision,
constraint pk_evidencija_se_odnesuva_na_mebel primary key (id_evidencija),
constraint fk_evidencija_se_odnesuva_na_mebel_evidencija foreign key (id_evidencija) references evidencija_na_naracki(id_evidencija),
constraint fk_evidencija_se_odnesuva_na_mebel_mebel foreign key (id_mebel) references mebel(id_mebel)
);
create table delovi_izraboteni_od_materijal(
id_del integer,
id_materijal integer not null,
constraint pk_delovi_izraboteni_od_materijal  primary key (id_del), 
constraint fk_delovi_izraboteni_od_materijal_delovi foreign key (id_del) references delovi(id_del),
constraint fk_delovi_izraboteni_od_materijal_materijal foreign key (id_materijal) references materijali(id_materijal)
);

create table delovi_izraboteni_od_mashina(
id_del integer,
id_mashina integer not null,
constraint pk_delovi_izraboteni_od_mashina primary key (id_mashina,id_del),
constraint fk_delovi_izraboteni_od_mashina_del foreign key(id_del) references delovi(id_del),
constraint fk_delovi_izraboteni_od_mashina_mashina foreign key (id_mashina) references mashini(id_mashina)
);
create table mashina_pripaga_oddelenie(
id_mashina integer ,
id_oddelenie integer not null,
constraint pk_mashina_pripaga_oddelenie  primary key (id_mashina),
constraint fk_mashina_prigaha_oddelenie_mashina foreign key (id_mashina) references mashini(id_mashina),
constraint fk_mashina_pripaga_oddelenie_oddelenie foreign key (id_oddelenie) references oddelenija(id_oddelenie)
);
create table vraboteni_se_korisnici(
id_vraboten integer,
id_korisnik integer,
constraint pk_korisnici_vraboteni  primary key (id_vraboten),
constraint fk_korisnici_vraboteni_vraboten foreign key (id_vraboten)references vraboteni(id_vraboten),
constraint fk_korisnici_vraboteni_korisnik foreign key (id_korisnik) references korisnik(id_korisnik)
);
create table vraboteni_vo_proizvodstvo_raboti_vo_oddelenija(
id_vraboten integer,
id_oddelenie integer,
constraint pk_vraboteni_vo_proizvodstvo_raboti_vo_oddelenija primary key (id_vraboten),
constraint fk_vraboteni_vo_proizvodstvo_raboti_vo_odd_vraboteni foreign key (id_vraboten) references vraboteni_vo_proizvodstvo(id_vraboten),
constraint fk_vraboteni_vo_proizvodstvo_raboti_vo_odd_oddelenie  foreign key (id_oddelenie)references oddelenija(id_oddelenie)
);

CREATE table if not exists vraboteni_vo_proizvodstvo_raboti_na_mashina (
        id_mashina int4 NOT NULL,
        id_vraboten int4 NULL,
        CONSTRAINT pk_vprm PRIMARY KEY (id_mashina),
        constraint fk_mashina foreign key(id_mashina) references mashini(id_mashina),
        constraint fk_vraboten foreign key(id_mashina) references vraboteni_vo_proizvodstvo(id_vraboten)
);



ALTER TABLE vraboteni ADD column  IF NOT exists "role" varchar(50);

ALTER TABLE korisnik ADD column IF NOT EXISTS "role"  varchar(50);


ALTER TABLE korisnik ADD column IF NOT EXISTS email varchar(50);



ALTER TABLE klienti ADD column IF NOT EXISTS "role"  varchar(50);


ALTER TABLE klienti ADD column IF NOT EXISTS email varchar(50);

CREATE table if not exists kosnicka (
        id_kosnicka serial4 NOT NULL,
        datum_napravena date NOT NULL,
        status varchar(20) NOT NULL,
        id_klient int4 NULL,
        CONSTRAINT pk_kosnicka PRIMARY KEY (id_kosnicka),
        constraint fk_kos_klien foreign key klienti(id_klient)
);
CREATE TABLE if not exists kosnicka_se_sostoi_od_mebel (
        id_kosnicka int4 NOT NULL,
        id_mebel int4 NOT NULL,
        CONSTRAINT pk_kosnicka_se_sostoi_od_mebel PRIMARY KEY (id_kosnicka, id_mebel),
        constraint fk_kossm_kos foreign key(id_kosnicka) references kosnicka(id_kosnicka),
        constraint fk_kossm_meb foreign key(id_mebel) references mebel(id_mebel)
);