DatabaseCreation: DDL.sql

File DDL.sql, 18.9 KB (added by 233088, 12 days ago)
Line 
1-- =========================
2-- BANKA
3-- =========================
4CREATE TABLE banka (
5 banka_id SERIAL PRIMARY KEY,
6 ime_na_banka VARCHAR(100) NOT NULL DEFAULT 'unknown_bank',
7 edb VARCHAR(13) NOT NULL UNIQUE,
8 datum_na_osnovanje DATE,
9
10 CONSTRAINT chk_banka_edb_len CHECK (length(edb) = 13)
11);
12
13-- =========================
14-- VALUTA
15-- =========================
16CREATE TABLE valuta (
17 valuta_id SERIAL PRIMARY KEY,
18 kod CHAR(3) NOT NULL UNIQUE,
19 ime VARCHAR(100) NOT NULL,
20 simbol VARCHAR(5)
21);
22
23-- =========================
24-- KURSNA_LISTA
25-- =========================
26CREATE TABLE kursna_lista (
27 kurs_id SERIAL PRIMARY KEY,
28 datum DATE NOT NULL DEFAULT CURRENT_DATE,
29 kupoven_kurs NUMERIC(10,4) NOT NULL,
30 sreden_kurs NUMERIC(10,4) NOT NULL,
31 prodazen_kurs NUMERIC(10,4) NOT NULL,
32 valuta_od_id INT NOT NULL,
33 valuta_do_id INT NOT NULL,
34
35 CONSTRAINT fk_kurs_valuta_od FOREIGN KEY (valuta_od_id)
36 REFERENCES valuta(valuta_id) ON DELETE RESTRICT,
37 CONSTRAINT fk_kurs_valuta_do FOREIGN KEY (valuta_do_id)
38 REFERENCES valuta(valuta_id) ON DELETE RESTRICT,
39
40 CONSTRAINT uq_kurs UNIQUE (datum, valuta_od_id, valuta_do_id),
41 CONSTRAINT chk_kurs_positive CHECK (kupoven_kurs > 0 AND sreden_kurs > 0 AND prodazen_kurs > 0),
42 CONSTRAINT chk_kurs_order CHECK (kupoven_kurs <= sreden_kurs AND sreden_kurs <= prodazen_kurs),
43 CONSTRAINT chk_kurs_diff_valuti CHECK (valuta_od_id <> valuta_do_id)
44);
45
46-- =========================
47-- BANK_USER / ROLE / PRIVILEGII
48-- =========================
49CREATE TABLE bank_user (
50 user_id SERIAL PRIMARY KEY,
51 username VARCHAR(100) NOT NULL UNIQUE,
52 password_hash VARCHAR(255) NOT NULL,
53 status VARCHAR(20) NOT NULL DEFAULT 'AKTIVEN',
54
55 CONSTRAINT chk_bank_user_status CHECK (status IN ('AKTIVEN', 'NEAKTIVEN'))
56);
57
58CREATE TABLE role (
59 role_id SERIAL PRIMARY KEY,
60 ime VARCHAR(100) NOT NULL UNIQUE
61);
62
63CREATE TABLE privilegii (
64 privilegija_id SERIAL PRIMARY KEY,
65 privilegija VARCHAR(100) NOT NULL UNIQUE
66);
67
68CREATE TABLE role_privilegii (
69 role_id INT NOT NULL,
70 privilegija_id INT NOT NULL,
71 PRIMARY KEY (role_id, privilegija_id),
72
73 CONSTRAINT fk_role_privilegii_role FOREIGN KEY (role_id)
74 REFERENCES role(role_id) ON DELETE CASCADE,
75 CONSTRAINT fk_role_privilegii_privilegii FOREIGN KEY (privilegija_id)
76 REFERENCES privilegii(privilegija_id) ON DELETE CASCADE
77);
78
79CREATE TABLE role_user (
80 role_id INT NOT NULL,
81 user_id INT NOT NULL,
82 PRIMARY KEY (role_id, user_id),
83
84 CONSTRAINT fk_role_user_role FOREIGN KEY (role_id)
85 REFERENCES role(role_id) ON DELETE CASCADE,
86 CONSTRAINT fk_role_user_user FOREIGN KEY (user_id)
87 REFERENCES bank_user(user_id) ON DELETE CASCADE
88);
89
90-- =========================
91-- FILIJALA
92-- =========================
93CREATE TABLE filijala (
94 filijala_id SERIAL PRIMARY KEY,
95 ime VARCHAR(100) NOT NULL,
96 banka_id INT NOT NULL,
97
98 CONSTRAINT fk_filijala_banka FOREIGN KEY (banka_id)
99 REFERENCES banka(banka_id) ON DELETE RESTRICT
100);
101
102-- =========================
103-- VRABOTEN
104-- =========================
105CREATE TABLE vraboten (
106 vraboten_id SERIAL PRIMARY KEY,
107 user_id INT NOT NULL UNIQUE,
108 ime VARCHAR(100) NOT NULL,
109 prezime VARCHAR(100) NOT NULL,
110 tatkovo_ime VARCHAR(100),
111 datum_ragjanje DATE,
112 embg CHAR(13) NOT NULL UNIQUE,
113
114 CONSTRAINT fk_vraboten_user FOREIGN KEY (user_id)
115 REFERENCES bank_user(user_id) ON DELETE RESTRICT,
116 CONSTRAINT chk_vraboten_embg_len CHECK (length(embg) = 13)
117);
118
119-- Extra table kept from your model.
120CREATE TABLE raboti_vo (
121 vraboten_id INT NOT NULL,
122 filijala_id INT NOT NULL,
123 raboti_od DATE NOT NULL,
124 raboti_do DATE,
125
126 PRIMARY KEY (vraboten_id, filijala_id, raboti_od),
127
128 CONSTRAINT fk_raboti_vo_vraboten FOREIGN KEY (vraboten_id)
129 REFERENCES vraboten(vraboten_id) ON DELETE RESTRICT,
130 CONSTRAINT fk_raboti_vo_filijala FOREIGN KEY (filijala_id)
131 REFERENCES filijala(filijala_id) ON DELETE RESTRICT,
132 CONSTRAINT chk_raboti_vo_dates CHECK (raboti_do IS NULL OR raboti_do > raboti_od)
133);
134
135-- =========================
136-- KLIENT
137-- =========================
138CREATE TABLE klient (
139 klient_id SERIAL PRIMARY KEY,
140 user_id INT NOT NULL UNIQUE,
141 ime VARCHAR(100) NOT NULL,
142 prezime VARCHAR(100) NOT NULL,
143 datum_ragjanje DATE NOT NULL,
144 tatkovo_ime VARCHAR(100),
145 embg CHAR(13) NOT NULL UNIQUE,
146
147 CONSTRAINT fk_klient_user FOREIGN KEY (user_id)
148 REFERENCES bank_user(user_id) ON DELETE RESTRICT,
149 CONSTRAINT chk_klient_embg_len CHECK (length(embg) = 13)
150);
151
152-- =========================
153-- IZVESTUVANJE
154-- =========================
155CREATE TABLE izvestuvanje (
156 izvestuvanje_id SERIAL PRIMARY KEY,
157 naslov VARCHAR(100) NOT NULL,
158 poraka TEXT NOT NULL,
159 datum_isprakjanje DATE NOT NULL DEFAULT CURRENT_DATE,
160 klient_id INT NOT NULL,
161 banka_id INT NOT NULL,
162
163 CONSTRAINT fk_izvestuvanje_klient FOREIGN KEY (klient_id)
164 REFERENCES klient(klient_id) ON DELETE RESTRICT,
165 CONSTRAINT fk_izvestuvanje_banka FOREIGN KEY (banka_id)
166 REFERENCES banka(banka_id) ON DELETE RESTRICT
167);
168
169-- =========================
170-- CONTACT TABLES FROM ER
171-- Each row belongs to exactly one owner: klient OR vraboten OR banka OR filijala.
172-- =========================
173CREATE TABLE telefon (
174 telefon_id SERIAL PRIMARY KEY,
175 telefonski_broj VARCHAR(20) NOT NULL UNIQUE,
176 tip_telefon VARCHAR(50),
177 klient_id INT,
178 vraboten_id INT,
179 banka_id INT,
180 filijala_id INT,
181
182 CONSTRAINT fk_telefon_klient FOREIGN KEY (klient_id)
183 REFERENCES klient(klient_id) ON DELETE CASCADE,
184 CONSTRAINT fk_telefon_vraboten FOREIGN KEY (vraboten_id)
185 REFERENCES vraboten(vraboten_id) ON DELETE CASCADE,
186 CONSTRAINT fk_telefon_banka FOREIGN KEY (banka_id)
187 REFERENCES banka(banka_id) ON DELETE CASCADE,
188 CONSTRAINT fk_telefon_filijala FOREIGN KEY (filijala_id)
189 REFERENCES filijala(filijala_id) ON DELETE CASCADE,
190
191 CONSTRAINT chk_telefon_one_owner CHECK (
192 (CASE WHEN klient_id IS NULL THEN 0 ELSE 1 END) +
193 (CASE WHEN vraboten_id IS NULL THEN 0 ELSE 1 END) +
194 (CASE WHEN banka_id IS NULL THEN 0 ELSE 1 END) +
195 (CASE WHEN filijala_id IS NULL THEN 0 ELSE 1 END) = 1
196 )
197);
198
199CREATE TABLE email (
200 email_id SERIAL PRIMARY KEY,
201 email VARCHAR(100) NOT NULL UNIQUE,
202 tip_email VARCHAR(50),
203 klient_id INT,
204 vraboten_id INT,
205 banka_id INT,
206 filijala_id INT,
207
208 CONSTRAINT fk_email_klient FOREIGN KEY (klient_id)
209 REFERENCES klient(klient_id) ON DELETE CASCADE,
210 CONSTRAINT fk_email_vraboten FOREIGN KEY (vraboten_id)
211 REFERENCES vraboten(vraboten_id) ON DELETE CASCADE,
212 CONSTRAINT fk_email_banka FOREIGN KEY (banka_id)
213 REFERENCES banka(banka_id) ON DELETE CASCADE,
214 CONSTRAINT fk_email_filijala FOREIGN KEY (filijala_id)
215 REFERENCES filijala(filijala_id) ON DELETE CASCADE,
216
217 CONSTRAINT chk_email_one_owner CHECK (
218 (CASE WHEN klient_id IS NULL THEN 0 ELSE 1 END) +
219 (CASE WHEN vraboten_id IS NULL THEN 0 ELSE 1 END) +
220 (CASE WHEN banka_id IS NULL THEN 0 ELSE 1 END) +
221 (CASE WHEN filijala_id IS NULL THEN 0 ELSE 1 END) = 1
222 )
223);
224
225CREATE TABLE adresa (
226 adresa_id SERIAL PRIMARY KEY,
227 drzava VARCHAR(100) NOT NULL,
228 grad VARCHAR(100) NOT NULL,
229 opstina VARCHAR(100),
230 naselba VARCHAR(100),
231 ulica VARCHAR(150),
232 broj VARCHAR(20),
233 stanben_broj VARCHAR(20),
234 tip_adresa VARCHAR(50),
235 klient_id INT,
236 vraboten_id INT,
237 banka_id INT,
238 filijala_id INT,
239
240 CONSTRAINT fk_adresa_klient FOREIGN KEY (klient_id)
241 REFERENCES klient(klient_id) ON DELETE CASCADE,
242 CONSTRAINT fk_adresa_vraboten FOREIGN KEY (vraboten_id)
243 REFERENCES vraboten(vraboten_id) ON DELETE CASCADE,
244 CONSTRAINT fk_adresa_banka FOREIGN KEY (banka_id)
245 REFERENCES banka(banka_id) ON DELETE CASCADE,
246 CONSTRAINT fk_adresa_filijala FOREIGN KEY (filijala_id)
247 REFERENCES filijala(filijala_id) ON DELETE CASCADE,
248
249 CONSTRAINT chk_adresa_one_owner CHECK (
250 (CASE WHEN klient_id IS NULL THEN 0 ELSE 1 END) +
251 (CASE WHEN vraboten_id IS NULL THEN 0 ELSE 1 END) +
252 (CASE WHEN banka_id IS NULL THEN 0 ELSE 1 END) +
253 (CASE WHEN filijala_id IS NULL THEN 0 ELSE 1 END) = 1
254 )
255);
256
257-- =========================
258-- USLUGA
259-- =========================
260CREATE TABLE usluga (
261 usluga_id SERIAL PRIMARY KEY,
262 ime VARCHAR(100) NOT NULL,
263 opis VARCHAR(255),
264 datum_od DATE,
265 datum_do DATE,
266 tip_usluga VARCHAR(100),
267 status VARCHAR(30) NOT NULL DEFAULT 'AKTIVNA',
268 banka_id INT NOT NULL,
269 filijala_id INT NOT NULL,
270
271 CONSTRAINT fk_usluga_banka FOREIGN KEY (banka_id)
272 REFERENCES banka(banka_id) ON DELETE RESTRICT,
273 CONSTRAINT fk_usluga_filijala FOREIGN KEY (filijala_id)
274 REFERENCES filijala(filijala_id) ON DELETE RESTRICT,
275
276 CONSTRAINT chk_usluga_dates CHECK (datum_do IS NULL OR datum_od IS NULL OR datum_do > datum_od),
277 CONSTRAINT chk_usluga_status CHECK (status IN ('AKTIVNA', 'NEAKTIVNA'))
278);
279
280-- =========================
281-- TIP_KREDIT / KREDIT
282-- =========================
283CREATE TABLE tip_kredit (
284 tip_kredit_id SERIAL PRIMARY KEY,
285 tip VARCHAR(100) NOT NULL UNIQUE,
286 opis VARCHAR(255)
287);
288
289CREATE TABLE kredit (
290 kredit_id SERIAL PRIMARY KEY,
291 kamatna_stapka NUMERIC(5,2) NOT NULL,
292 rok_otplata INT NOT NULL,
293 iznos_kredit NUMERIC(15,2) NOT NULL,
294 mesecna_rata NUMERIC(15,2) NOT NULL,
295 tip_kredit_id INT NOT NULL,
296 usluga_id INT NOT NULL,
297 valuta_id INT NOT NULL,
298
299 CONSTRAINT fk_kredit_tip FOREIGN KEY (tip_kredit_id)
300 REFERENCES tip_kredit(tip_kredit_id) ON DELETE RESTRICT,
301 CONSTRAINT fk_kredit_usluga FOREIGN KEY (usluga_id)
302 REFERENCES usluga(usluga_id) ON DELETE RESTRICT,
303 CONSTRAINT fk_kredit_valuta FOREIGN KEY (valuta_id)
304 REFERENCES valuta(valuta_id) ON DELETE RESTRICT,
305
306 CONSTRAINT chk_kredit_iznos CHECK (iznos_kredit >= 0),
307 CONSTRAINT chk_kredit_rata CHECK (mesecna_rata >= 0),
308 CONSTRAINT chk_kredit_kamata CHECK (kamatna_stapka >= 0),
309 CONSTRAINT chk_kredit_rok CHECK (rok_otplata > 0)
310);
311
312-- =========================
313-- DOGOVOR / POTPISNIK
314-- =========================
315CREATE TABLE dogovor (
316 dogovor_id SERIAL PRIMARY KEY,
317 naslov VARCHAR(255) NOT NULL,
318 datum_kreiranje DATE NOT NULL DEFAULT CURRENT_DATE,
319 datum_posledna_promena DATE,
320 datum_potpisuvanje DATE,
321 status VARCHAR(30) NOT NULL DEFAULT 'KREIRAN',
322 klient_id INT NOT NULL,
323 banka_id INT NOT NULL,
324 usluga_id INT NOT NULL,
325 filijala_id INT,
326
327 CONSTRAINT fk_dogovor_klient FOREIGN KEY (klient_id)
328 REFERENCES klient(klient_id) ON DELETE RESTRICT,
329 CONSTRAINT fk_dogovor_banka FOREIGN KEY (banka_id)
330 REFERENCES banka(banka_id) ON DELETE RESTRICT,
331 CONSTRAINT fk_dogovor_usluga FOREIGN KEY (usluga_id)
332 REFERENCES usluga(usluga_id) ON DELETE RESTRICT,
333 CONSTRAINT fk_dogovor_filijala FOREIGN KEY (filijala_id)
334 REFERENCES filijala(filijala_id) ON DELETE RESTRICT,
335
336 CONSTRAINT chk_dogovor_status CHECK (status IN ('KREIRAN', 'POTPISAN', 'OTKAZAN', 'ISTECEN')),
337 CONSTRAINT chk_dogovor_dates CHECK (datum_potpisuvanje IS NULL OR datum_potpisuvanje >= datum_kreiranje)
338);
339
340CREATE TABLE potpisnik (
341 potpisnik_id SERIAL PRIMARY KEY,
342 datum_potpisuvanje DATE NOT NULL DEFAULT CURRENT_DATE,
343 klient_id INT NOT NULL,
344 dogovor_id INT NOT NULL,
345
346 CONSTRAINT fk_potpisnik_klient FOREIGN KEY (klient_id)
347 REFERENCES klient(klient_id) ON DELETE RESTRICT,
348 CONSTRAINT fk_potpisnik_dogovor FOREIGN KEY (dogovor_id)
349 REFERENCES dogovor(dogovor_id) ON DELETE RESTRICT,
350
351 CONSTRAINT uq_potpisnik UNIQUE (klient_id, dogovor_id)
352);
353
354-- =========================
355-- SMETKA
356-- =========================
357CREATE TABLE smetka (
358 smetka_id SERIAL PRIMARY KEY,
359 broj_smetka VARCHAR(20) NOT NULL UNIQUE,
360 datum_otvaranje DATE NOT NULL DEFAULT CURRENT_DATE,
361 status VARCHAR(30) NOT NULL DEFAULT 'AKTIVNA',
362 tip_smetka VARCHAR(100) NOT NULL,
363 usluga_id INT NOT NULL,
364 klient_id INT NOT NULL,
365 kredit_id INT,
366 banka_id INT NOT NULL,
367 valuta_id INT NOT NULL,
368 saldo NUMERIC(15,2) DEFAULT 0,
369
370 CONSTRAINT fk_smetka_usluga FOREIGN KEY (usluga_id)
371 REFERENCES usluga(usluga_id) ON DELETE RESTRICT,
372 CONSTRAINT fk_smetka_klient FOREIGN KEY (klient_id)
373 REFERENCES klient(klient_id) ON DELETE RESTRICT,
374 CONSTRAINT fk_smetka_kredit FOREIGN KEY (kredit_id)
375 REFERENCES kredit(kredit_id) ON DELETE RESTRICT,
376 CONSTRAINT fk_smetka_banka FOREIGN KEY (banka_id)
377 REFERENCES banka(banka_id) ON DELETE RESTRICT,
378 CONSTRAINT fk_smetka_valuta FOREIGN KEY (valuta_id)
379 REFERENCES valuta(valuta_id) ON DELETE RESTRICT,
380
381 CONSTRAINT chk_smetka_status CHECK (status IN ('AKTIVNA', 'BLOKIRANA', 'ZATVORENA'))
382);
383
384-- =========================
385-- DEPOZIT
386-- =========================
387CREATE TABLE depozit (
388 depozit_id SERIAL PRIMARY KEY,
389 iznos_depozit NUMERIC(15,2) NOT NULL,
390 rok_depozit INT NOT NULL,
391 kamatna_stapka NUMERIC(5,2) NOT NULL,
392 datum_odobruvanje DATE,
393 datum_aktiviranje DATE,
394 momentalna_sostojba NUMERIC(15,2),
395 tip_depozit VARCHAR(100),
396 usluga_id INT NOT NULL,
397 smetka_id INT NOT NULL,
398 valuta_id INT NOT NULL,
399
400 CONSTRAINT fk_depozit_usluga FOREIGN KEY (usluga_id)
401 REFERENCES usluga(usluga_id) ON DELETE RESTRICT,
402 CONSTRAINT fk_depozit_smetka FOREIGN KEY (smetka_id)
403 REFERENCES smetka(smetka_id) ON DELETE RESTRICT,
404 CONSTRAINT fk_depozit_valuta FOREIGN KEY (valuta_id)
405 REFERENCES valuta(valuta_id) ON DELETE RESTRICT,
406
407 CONSTRAINT chk_depozit_iznos CHECK (iznos_depozit >= 0),
408 CONSTRAINT chk_depozit_sostojba CHECK (momentalna_sostojba IS NULL OR momentalna_sostojba >= 0),
409 CONSTRAINT chk_depozit_rok CHECK (rok_depozit > 0),
410 CONSTRAINT chk_depozit_kamata CHECK (kamatna_stapka >= 0)
411);
412
413-- =========================
414-- TIP_KARTICKA / KARTICKA
415-- =========================
416CREATE TABLE tip_karticka (
417 tip_karticka_id SERIAL PRIMARY KEY,
418 ime VARCHAR(50) NOT NULL UNIQUE,
419 opis VARCHAR(255)
420);
421
422CREATE TABLE karticka (
423 karticka_id SERIAL PRIMARY KEY,
424 broj_karticka VARCHAR(16) NOT NULL UNIQUE,
425 datum_izdavanje DATE NOT NULL DEFAULT CURRENT_DATE,
426 datum_istekuvanje DATE NOT NULL,
427 cvc_kod VARCHAR(3) NOT NULL,
428 status VARCHAR(30) NOT NULL DEFAULT 'AKTIVNA',
429 smetka_id INT NOT NULL,
430 tip_karticka_id INT NOT NULL,
431
432 CONSTRAINT fk_karticka_smetka FOREIGN KEY (smetka_id)
433 REFERENCES smetka(smetka_id) ON DELETE RESTRICT,
434 CONSTRAINT fk_karticka_tip FOREIGN KEY (tip_karticka_id)
435 REFERENCES tip_karticka(tip_karticka_id) ON DELETE RESTRICT,
436
437 CONSTRAINT chk_karticka_broj CHECK (length(broj_karticka) = 16),
438 CONSTRAINT chk_karticka_cvc CHECK (length(cvc_kod) = 3),
439 CONSTRAINT chk_karticka_dates CHECK (datum_istekuvanje > datum_izdavanje),
440 CONSTRAINT chk_karticka_status CHECK (status IN ('AKTIVNA', 'BLOKIRANA', 'ISTECENA'))
441);
442
443-- =========================
444-- NALOG
445-- =========================
446CREATE TABLE nalog (
447 nalog_id SERIAL PRIMARY KEY,
448 datum_na_valuta DATE NOT NULL DEFAULT CURRENT_DATE,
449 povikuvanje_na_broj_odobruvanje VARCHAR(100),
450 iznos NUMERIC(15,2) NOT NULL,
451 danocen_broj_embg VARCHAR(13),
452 svrha_na_plakjanje VARCHAR(255),
453 smetka_primalac_id INT,
454 cel_na_doznaka VARCHAR(255),
455 hitno BOOLEAN NOT NULL DEFAULT FALSE,
456 uplateno_mesto VARCHAR(100),
457 smetka_na_budetski_korisnik_edinka_korisnik VARCHAR(100),
458 prihodna_sifra VARCHAR(50),
459 programa VARCHAR(100),
460 nacin_plakjanje VARCHAR(100),
461 nalogodavac_id INT NOT NULL,
462 danocen_broj_primalac VARCHAR(13),
463 smetka_nalogodavac_id INT NOT NULL,
464 smetka_nalogoprimac_id INT,
465 smetka_nalogoprimac VARCHAR(100),
466 valuta_id INT NOT NULL,
467 potpisnik_id INT,
468
469 CONSTRAINT fk_nalog_klient FOREIGN KEY (nalogodavac_id)
470 REFERENCES klient(klient_id) ON DELETE RESTRICT,
471 CONSTRAINT fk_nalog_smetka_nalogodavac FOREIGN KEY (smetka_nalogodavac_id)
472 REFERENCES smetka(smetka_id) ON DELETE RESTRICT,
473 CONSTRAINT fk_nalog_smetka_primalac FOREIGN KEY (smetka_primalac_id)
474 REFERENCES smetka(smetka_id) ON DELETE RESTRICT,
475 CONSTRAINT fk_nalog_smetka_nalogoprimac FOREIGN KEY (smetka_nalogoprimac_id)
476 REFERENCES smetka(smetka_id) ON DELETE RESTRICT,
477 CONSTRAINT fk_nalog_valuta FOREIGN KEY (valuta_id)
478 REFERENCES valuta(valuta_id) ON DELETE RESTRICT,
479 CONSTRAINT fk_nalog_potpisnik FOREIGN KEY (potpisnik_id)
480 REFERENCES potpisnik(potpisnik_id) ON DELETE SET NULL,
481
482 CONSTRAINT chk_nalog_iznos CHECK (iznos >= 0),
483 CONSTRAINT chk_nalog_different_accounts CHECK (
484 smetka_nalogoprimac_id IS NULL
485 OR smetka_nalogodavac_id <> smetka_nalogoprimac_id
486 )
487);
488
489-- =========================
490-- TRANSAKCIJA
491-- =========================
492CREATE TABLE transakcija (
493 transakcija_id SERIAL PRIMARY KEY,
494 datum_na_valuta DATE NOT NULL DEFAULT CURRENT_DATE,
495 iznos NUMERIC(15,2) NOT NULL,
496 datum_transakcija TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
497 opis VARCHAR(255),
498 smetka_isprakjac_id INT NOT NULL,
499 smetka_primac_id INT NOT NULL,
500 nalog_id INT,
501 valuta_id INT NOT NULL,
502
503 CONSTRAINT fk_transakcija_smetka_isprakjac FOREIGN KEY (smetka_isprakjac_id)
504 REFERENCES smetka(smetka_id) ON DELETE RESTRICT,
505 CONSTRAINT fk_transakcija_smetka_primac FOREIGN KEY (smetka_primac_id)
506 REFERENCES smetka(smetka_id) ON DELETE RESTRICT,
507 CONSTRAINT fk_transakcija_nalog FOREIGN KEY (nalog_id)
508 REFERENCES nalog(nalog_id) ON DELETE SET NULL,
509 CONSTRAINT fk_transakcija_valuta FOREIGN KEY (valuta_id)
510 REFERENCES valuta(valuta_id) ON DELETE RESTRICT,
511
512 CONSTRAINT chk_transakcija_iznos CHECK (iznos >= 0),
513 CONSTRAINT chk_transakcija_diff_accounts CHECK (smetka_isprakjac_id <> smetka_primac_id)
514);
515
516-- =========================
517-- RATA_KREDIT
518-- =========================
519CREATE TABLE rata_kredit (
520 rata_kredit_id SERIAL PRIMARY KEY,
521 datum_na_valuta DATE NOT NULL DEFAULT CURRENT_DATE,
522 status VARCHAR(30) NOT NULL DEFAULT 'NEPLATENA',
523 iznos_rata NUMERIC(15,2) NOT NULL,
524 kredit_id INT NOT NULL,
525 transakcija_id INT,
526
527 CONSTRAINT fk_rata_kredit_kredit FOREIGN KEY (kredit_id)
528 REFERENCES kredit(kredit_id) ON DELETE RESTRICT,
529 CONSTRAINT fk_rata_kredit_transakcija FOREIGN KEY (transakcija_id)
530 REFERENCES transakcija(transakcija_id) ON DELETE SET NULL,
531
532 CONSTRAINT chk_rata_kredit_iznos CHECK (iznos_rata >= 0),
533 CONSTRAINT chk_rata_kredit_status CHECK (status IN ('PLATENA', 'NEPLATENA', 'DOCNI'))
534);
535