Banka

create table grad
(
	id_grad integer not null
		constraint pk_grad
			primary key,
	ime_grad varchar(100) not null
);

alter table grad owner to postgres;

grant insert, select, update on grad to student;

create table filijala
(
	id_filijala integer not null
		constraint pk_filijala
			primary key,
	adresa_filijala varchar(255) not null,
	id_grad integer not null
		constraint fk_filijala_e_locirana_vo_grad
			references grad
);

alter table filijala owner to postgres;

grant insert, select, update on filijala to student;

create table chovek
(
	embg char(13) not null
		constraint pk_chovek
			primary key,
	ime varchar(100) not null,
	prezime varchar(100) not null
);

alter table chovek owner to postgres;

grant insert, select, update on chovek to student;

create table klient
(
	embg_klient char(13) not null
		constraint pk_klient
			primary key
		constraint fk_klient_embg_chovek
			references chovek,
	adresa_ziveenje varchar(150),
	id_grad integer not null
		constraint fk_klient_grad_zhivee
			references grad
);

alter table klient owner to postgres;

create table smetka
(
	broj_smetka integer not null
		constraint pk_smetka
			primary key,
	valuta char(3) not null,
	saldo double precision not null,
	dozvolen_minus double precision,
	embg_klient char(13) not null
		constraint fk_smetka_klient
			references klient
);

alter table smetka owner to postgres;

grant insert, select, update on smetka to student;

create table transakcija
(
	broj_transakcija integer not null
		constraint pk_transakcija
			primary key,
	vreme_izvrsuvanje timestamp not null,
	valuta char(3) not null,
	suma double precision not null,
	broj_smetka_uplata integer not null
		constraint fk_transakcija_smetka_uplata
			references smetka,
	broj_smetka_isplata integer not null
		constraint fk_transakcija_smetka_isplata
			references smetka
);

alter table transakcija owner to postgres;

grant insert, select, update on transakcija to student;

create table kredit
(
	id_kredit integer not null,
	broj_smetka integer not null
		constraint fk_kredit_kon_smetka
			references smetka,
	datum_otvoranje date not null,
	rata double precision not null,
	constraint pk_kredit
		primary key (id_kredit, broj_smetka)
);

alter table kredit owner to postgres;

grant insert, select, update on kredit to student;

create table telefonski_broj
(
	embg_klient char(13) not null
		constraint fk_telefonski_broj_embg
			references klient,
	telefonski_broj varchar(15) not null,
	constraint pk_telefon
		primary key (embg_klient, telefonski_broj)
);

alter table telefonski_broj owner to postgres;

grant insert, select, update on telefonski_broj to student;

grant insert, select, update on klient to student;

create table vraboten
(
	embg char(13) not null
		constraint pk_vraboten
			primary key,
	datum_vrabotuvanje date not null,
	id_dogovor integer not null,
	id_filijala integer
		constraint fk_vraboten_vo_filijala
			references filijala,
	rabotno_mesto varchar(100),
	embg_shef char(13) not null
		constraint fk_shef_vraboten
			references vraboten
);

alter table vraboten owner to postgres;

grant insert, select, update on vraboten to student;

create table ovlasten
(
	embg char(13) not null
		constraint fk_ovlasten_klient
			references klient,
	broj_smetka integer not null
		constraint fk_ovlastuvanje_smetka
			references smetka,
	ovlasten_od date not null,
	ovlasten_do date,
	constraint pk_ovlasten
		primary key (embg, broj_smetka)
);

alter table ovlasten owner to postgres;

grant insert, select, update on ovlasten to student;

create table otvorena_vo
(
	broj_smetka integer not null
		constraint fk_otvorena_smetka
			references smetka,
	id_filijala integer not null
		constraint fk_smetka_otvorena_vo_filijala
			references filijala,
	embg_vraboten char(13) not null
		constraint fk_smetka_otvorena_od
			references vraboten,
	constraint pk_otvorena_vo
		primary key (broj_smetka, id_filijala, embg_vraboten)
);

alter table otvorena_vo owner to postgres;

grant insert, select, update on otvorena_vo to student;

create view mesecni_transakcii(ime_uplakjach, prezime_uplakjach, embg_uplakjach, ime_isplaten, prezime_isplaten, embg_isplaten, vreme_izvrsuvanje, suma, valuta) as
	SELECT chu.ime     AS ime_uplakjach,
       chu.prezime AS prezime_uplakjach,
       chu.embg    AS embg_uplakjach,
       chi.ime     AS ime_isplaten,
       chi.prezime AS prezime_isplaten,
       chi.embg    AS embg_isplaten,
       t.vreme_izvrsuvanje,
       t.suma,
       t.valuta
FROM banka.transakcija t
         JOIN banka.smetka su ON t.broj_smetka_uplata = su.broj_smetka
         JOIN banka.klient ku ON su.embg_klient = ku.embg_klient
         JOIN banka.chovek chu ON ku.embg_klient = chu.embg
         JOIN banka.smetka si ON t.broj_smetka_isplata = si.broj_smetka
         JOIN banka.klient ki ON si.embg_klient = ki.embg_klient
         JOIN banka.chovek chi ON ki.embg_klient = chi.embg
WHERE date_part('month'::text, t.vreme_izvrsuvanje) = date_part('month'::text, now())
  AND date_part('year'::text, t.vreme_izvrsuvanje) = date_part('year'::text, now())
ORDER BY t.vreme_izvrsuvanje;

alter table mesecni_transakcii owner to postgres;