Креирање и пополнување на базата

Опис

Во оваа фаза релациониот модел дефиниран во претходните фази (види AboutProject и RelationalModel) е имплементиран како физичка база на податоци. Базата е креирана во PostgreSQL преку DDL скрипта која ја дефинира структурата (табели, ограничувања, индекси, погледи и програмски објекти), додека податоците се внесуваат преку посебна DML скрипта.

Системот претставува централизиран информациски систем за високообразовни институции, организиран во седум функционални модули, секој означен со специфичен префикс на табелите. Структурата ја следи замислата за единствена точка на вистината (Single Source of Truth):

• Општи и основни податоци (без префикс) — лица, корисници и географски шифрарници
• Организациска структура и инфраструктура (префикс or_)
• Човечки ресурси и кадар (префикс vr_)
• Студентски прашања и академски менаџмент (префикс sm_)
• Финансии, сметководство и плати (префикс fin_)
• Управување со документи (префикс dok_)
• Надворешни ентитети (префикс ent_)

Забелешка за консолидација: DDL скриптата и податоците беа изработувани паралелно на повеќе работни машини во рамки на тимот. Овој документ служи како консолидиран преглед на финалната структура и на постапката за внес на податоци.

Технологија и конвенции

• РСУБП: PostgreSQL.
• Модуларност: секоја табела припаѓа на модул означен со префикс (or_, vr_, sm_, fin_, dok_, ent_); табелите без префикс се заеднички и се референцирани од сите модули.
• Примарни клучеви: секоја табела има суроген клуч id int4 со вредност од секвенца (nextval('<table>_id_seq')).
• Типови на податоци: int4, varchar(255) (и varchar(2)/varchar(3) за кодови), numeric(14,2) за пари, numeric(5,2)/numeric(12,2) за количини и норми, timestamp(6), date, bool, text.
• EAV модел: за динамични атрибути (по тип на организациска единица, студент, начин на наплата, документ темплејт) се користи Entity-Attribute-Value пристап преку парови табели *_atributi / *_atributi_vrednosti, со заеднички шифрарник tip_atribut.
• Шифрарници: табелите со суфикс _tip_*, _status_* и сл. чуваат енумерации (код + име) и се пополнуваат прво.

Содржина на DDL скриптата

Скриптата idontknow_script.sql дефинира:

• 119 табели — со примарни клучеви и unique ограничувања таму каде е потребно (на пр. кодови, кратенки, индекси).
• 11 индекси — секундарни (B-tree) индекси за чести пребарувања (на пр. idx_korisnici_username, idx_studenti_indeks, индекси по user_id за финансиските табели).
• 9 погледи (views) — означени со префикс vw_.
• 9 програмски рутини — 3 функции (fn_), 3 процедури (pr_) и 3 trigger функции (trgf_).

Забелешка за опфатот на скриптата (важно при обнова на системот):

• Скриптата не содржи foreign key ограничувања — релациите помеѓу табелите се изведени преку колоните *_id, но референцијалниот интегритет не е наметнат на ниво на база.
• Секвенците (*_id_seq) се референцирани преку nextval(...), но нивните CREATE SEQUENCE дефиниции не се дел од оваа скрипта (се креираат посебно или од ORM-от).
• Дефинирани се trigger функциите (trgf_), но нема CREATE TRIGGER врзувања во скриптата — врзувањето на функциите за табелите се прави одделно.
• Скриптата не содржи INSERT наредби — внесот на податоци е целосно одвоен во DML скриптата (види подолу).

Модули и табели

1. Општи и основни податоци (без префикс)

Основа на системот, референцирана од сите модули.

• lice — централен регистар на сите физички лица (студенти, кадар, надворешни соработници), спречува дуплирање
• korisnici — кориснички сметки за најава, врзани за lice
• drzavi, gradovi — географски шифрарници
• tip_atribut — заеднички шифрарник на типови атрибути за сите EAV модули

2. Организациска структура и инфраструктура (or_)

• or_organizaciona_edinica — рекурзивна хиерархија на институцијата (oe_id покажува кон истата табела)
• or_tipovi_organizaciona_edinica, or_tipovi_organizaciona_edinica_atributi — типови ОЕ и нивни EAV дефиниции
• or_organizaciona_edinica_atributi_vrednosti — EAV вредности по ОЕ
• or_organizaciona_edinica_smetki — врска ОЕ ↔ трансакциски сметки
• or_mapa_objekt, or_tip_mapa_objekt — просторни објекти (просторните податоци се чуваат во spatial_tekst од тип text)
• or_mapa_objekt_organizacija, or_tip_objekt_organizacija — мапирање на згради/простории кон ОЕ

3. Човечки ресурси и кадар (vr_)

• vr_vraboten_angazman — централен ангажман: лице ↔ ОЕ ↔ позиција ↔ тип ангажман ↔ FTE (можни повеќе паралелни ангажмани)
• vr_rabota_pozicija — работни позиции
• vr_pozicija_preduslov_grupa, vr_pozicija_preduslov — логички предуслови за позиција (AND/OR)
• vr_lice_kvalifikacija — квалификации/дипломи на лице
• vr_vid_kvalifikacija, vr_pole_kvalifikacija, vr_akademski_stepen — шифрарници за квалификации
• vr_akademsko_zvanje, vr_vraboten_akademsko_zvanje — академски звања и нивна историја
• vr_vraboten_funkcija, vr_tip_funkcija — мандатни функции (Декан, Продекан)
• vr_vraboten_organizaciona_uloga, vr_tip_organizaciona_uloga — организациски улоги
• vr_vraboten_kompensacija — основна (бруто/нето) плата по ангажман
• vr_status_vrabotenje, vr_tip_angazman, vr_tip_uloga_nastaven_kadar — шифрарници

4. Студентски прашања и академски менаџмент (sm_)

• sm_akademska_godina, sm_akademski_semestar, sm_tip_akademski_semestar, sm_period_akademski_semestar — приспособливо академско време (семестри/триместри/квартали)
• sm_akademski_semestar_smer_kvota, sm_tip_kvota — квоти и наплата по семестар/смер
• sm_predmet — апстрактен предмет
• sm_akreditacija, sm_akreditacija_fond, sm_tip_akreditacija, sm_status_akreditacija — законска рамка на предметот (кредити, фонд часови)
• sm_akreditacija_predmet_grupa_preduslov, sm_akreditacija_predmet_preduslov — предуслови за слушање предмет
• sm_akreditacisko_telo, sm_akreditacija_telo_clen, sm_tip_akreditacisko_telo, sm_tip_uloga_akreditacija_telo — акредитациски тела и членови
• sm_smer, sm_smer_verzija, sm_tip_smer — студиски програми (верзионирани)
• sm_smer_verzija_semestar, sm_smer_verzija_semestar_kurs — структура на смер по семестри
• sm_smer_upis_grupa_preduslov, sm_smer_upis_preduslov — предуслови за упис
• sm_smer_diplomiranje_grupa_preduslov, sm_smer_diplomiranje_preduslov — предуслови за дипломирање
• sm_studenti, sm_studenti_smerovi — студенти и нивни смерови
• sm_organizaciona_edinica_upis_pravilo, sm_upis_pravilo_brojac — автоматско генерирање на индекс
• sm_organizaciona_edinica_student_atributi, sm_organizaciona_edinica_student_atributi_vrednosti — EAV за студентски мета-податоци
• sm_kurs_realizacija, sm_kurs_realizacija_paralelka — инстанцирање на акредитација во семестар и поделба на паралелки
• sm_kurs_realizacija_nastaven_kadar, sm_paralelka_nastaven_kadar — алокација на наставен кадар
• sm_student_semestar_upis, sm_student_upisan_kurs — упис на семестар и курсеви
• sm_ispitna_sesija, sm_student_ispit_prijava — испитни сесии и пријави
• sm_sistem_ocenuvanje, sm_sistem_ocenuvanje_vrednost — приспособлив систем на оценување (A–F, бодови, броен)
• sm_student_ocena_zapis — траен запис на оценка
• sm_organizaciona_edinica_dostignuvanja, sm_tip_dostignuvanje, sm_student_dostignuvanje — достигнувања (пракса, волонтерска работа, дипломски)
• sm_organizaciona_edinica_diplomirani — евиденција на дипломирани
• sm_student_paralelka_raspredelba — распределба на студент по паралелка

5. Финансии, сметководство и плати (fin_)

• fin_finansiska_obvrska, fin_finansiska_obvrska_stavka — студентски обврски (оригинална/отворена сума, рати, ставки)
• fin_status_finansiska_obvrska — статус на обврска (Izdadena, Delumno platena, Platena)
• fin_korisnik_vid_naplati, fin_korisnik_vid_naplati_attributi, fin_korisnik_naplata_atributi_vrednosti, fin_korisnici_naplati — EAV за начини на наплата (Stripe, PayPal, картичка, трансфер)
• fin_naplati, fin_naplata_alokacija — влезни плаќања и нивна алокација кон обврски
• fin_isplatna_obvrska, fin_tip_isplatna_obvrska, fin_status_isplatna_obvrska — излезни обврски (стипендии, рефундации, хонорари)
• fin_korisnici_isplata, fin_isplati, fin_isplata_alokacija — исплати преку трансакциски сметки
• fin_transakciski_smetki, fin_banki — банкарски сметки (IBAN/SWIFT)
• fin_vid_valuta — валути (ISO код, симбол)
• fin_tip_naplati_organizaciona_edinica, fin_tip_naplati_cenovnik — ценовници
• fin_status_transakcija — статус на трансакција (ACCEPTED итн.)
• fin_finansiski_dokument, fin_finansiski_dokument_tip, fin_finansiski_dokument_vrska — финансиски документи
• Потсистем за плати: fin_plata_period, fin_status_plata_period, fin_plata_vlez, fin_tip_platna_komponenta, fin_plata_stavka_vraboten (исплатно ливче), fin_plata_komponenta (атомарни ставки на пресметката)

6. Управување со документи (dok_)

• dok_dokument_templejti, dok_tip_dokumenti_organizaciona_edinica — HTML/URL обрасци за официјални документи
• dok_dokument_templejt_atributi — EAV дефиниции на полиња во образец
• dok_generiran_dokument, dok_generiran_dokument_vrednosti — генерирани документи и пополнети вредности
• dok_dokument_potpis — евиденција на авторизација/потпис (кој ангажман и функција го одобрил)

7. Надворешни ентитети (ent_)

• ent_entiteti — надворешни компании, добавувачи, агенции, партнерски институции
• ent_entiteti_smetki — мапирање на нивните трансакциски сметки кон fin_transakciski_smetki

Помошни/привремени табели

• tmp_salary_truth — видете ја Забелешката во делот за внес на податоци

Програмски објекти

Погледи (views)

• vw_aktivni_upisani_kursevi — капацитет, број запишани и слободни места по реализација на курс
• vw_ispitni_prijavi — преглед на пријавите за испит (сесија, индекс, предмет, статус)
• vw_istorija_naplati — историја на наплати (сума, валута, статус, референца, ОЕ)
• vw_otvoreni_finansiski_obvrski — отворени обврски по корисник (вкупно отворено, број, најстаро доспевање)
• vw_pregled_plati_period — преглед на плати по период (бруто/нето/даноци, број вработени, просечна нето)
• vw_raspredelba_nastaven_kadar — оптовареност на наставен кадар (часови, курсеви, паралелки по семестар)
• vw_studentski_oceni — просек, освоени кредити, положени/паднати по студент
• vw_vraboteni_karieren_pat — кариерен пат (академско звање и тековна функција)
• vw_vraboteni_prekrsoci_kvalifikacii — вработени што не ги исполнуваат предусловите за позицијата

Функции (fn_)

• fn_otvoren_dolg(user_id, vid_valuta_id) → numeric — вкупен отворен долг на корисник во дадена валута
• fn_prosek_student(student_id, sistem_ocenuvanje_id) → numeric — пондериран просек по кредити
• fn_slobodni_mesta(kurs_realizacija_id) → integer — преостанати слободни места на реализација

Процедури (pr_)

• pr_generiraj_obvrski_za_semestar(akademski_semestar_id) — масовно генерирање обврски за школарина за сите запишани студенти (со пакетен COMMIT)
• pr_registriraj_naplata(user_id, suma, valuta, oe_id, referenca, korisnik_naplata_id) — регистрира наплата и ја алоцира по отворени обврски (по доспевање)
• pr_zapisi_student_na_kurs(student_id, kurs_realizacija_id) — запишува студент на курс со проверка на долг и капацитет

Trigger функции (trgf_)

• trgf_azuriraj_otvorena_suma — при алокација ја намалува отворената сума на обврската и го ажурира статусот (Platena/Delumno platena), со заштита од преалокација
• trgf_kapacitet_kurs — спречува упис над капацитетот на курсот (сериализирано со FOR UPDATE)
• trgf_validna_ocena — дозволува оценка само за активен упис, поени 0–100, и датум што не е во иднина

(Овие функции мора да се врзат за соодветните табели преку CREATE TRIGGER, што не е дел од idontknow_script.sql.)

Внес на податоци (DML)

Внесот на податоци (DML) се изведува по DDL скриптата. Бидејќи скриптата не наметнува foreign key ограничувања, податоците технички може да се внесат по кој било редослед, но за зачувување на референцијалниот интегритет и конзистентни идентификатори се препорачува следниов редослед:

1. Базични шифрарници (без зависности) — tip_atribut, drzavi, fin_vid_valuta, vr_akademski_stepen, vr_pole_kvalifikacija, сите *_tip_*, *_status_* табели, sm_period_akademski_semestar, fin_finansiski_dokument_tip, fin_tip_platna_komponenta
2. Географија и идентитети — gradovi → lice → korisnici
3. Организациска структура — or_mapa_objekt → or_organizaciona_edinica (рекурзивно: прво коренот) → EAV дефиниции и вредности
4. Банкарски и финансиски сметки — fin_banki, fin_transakciski_smetki, ent_entiteti, ent_entiteti_smetki, or_organizaciona_edinica_smetki
5. Човечки ресурси — vr_rabota_pozicija, предуслови, vr_vraboten_angazman, звања/функции, vr_lice_kvalifikacija, vr_vraboten_kompensacija
6. Студиска структура — sm_predmet, sm_akreditacisko_telo, sm_akreditacija (+ предуслови), sm_smer → sm_smer_verzija → семестри, sm_akademska_godina → sm_akademski_semestar, sm_sistem_ocenuvanje(_vrednost)
7. Студенти и реализации — sm_studenti, sm_studenti_smerovi, sm_kurs_realizacija (+ паралелки, кадар), sm_student_semestar_upis, sm_student_upisan_kurs
8. Трансакциски/тест податоци — испитни сесии и пријави, sm_student_ocena_zapis, финансиски обврски и наплати, плати (период → влезови → ставки → компоненти), документи

DML скриптата содржи тест податоци со кои се проверува функционалноста на системот, погледите и програмските рутини.

Забелешка: Табелата tmp_salary_truth е помошна (привремена) табела — нема примарен клуч ниту секвенца, само индекс idx_truth_mapping по (plata_period_id, vraboten_id). Во неа се чуваат очекуваните („вистинските") вредности за платите (true_bruto, true_odbivki, true_danok), кои служат за валидација на точноста на пресметката во потсистемот за плати (fin_plata_komponenta / fin_plata_stavka_vraboten) и за тестирање. Не е дел од продукцискиот модел.

Скрипти

Тип	Опис	Линк
DDL	Структура на базата (табели, индекси, погледи, функции, процедури, trigger функции)	DDL скрипта
DML	Податоци за пополнување и тестирање на базата	DML скрипта