Фаза 2: Креирање и пополнување на база на податоци

Членови на тимот

• Матеа Ѓоргоска 231069 (координатор)
• Ана Марија Кукулева 231025

1. Опис на фазата

Во оваа фаза е реализирана физичка имплементација на релациониот модел дефиниран во Фаза 1. Опфатени се двата клучни чекори:

• DDL (Data Definition Language) — креирање на сите табели заедно со примарни клучеви, надворешни клучеви, CHECK ограничувања и DEFAULT вредности.
• DML (Data Manipulation Language) — пополнување на табелите со реалистични податоци (милионски обем) преку SQL процедурален пристап.

2. DDL - Креирање на табели

2.1 Преглед на табели

Табела	Опис	Примарен клуч
Gragjanin	Граѓани (потенцијални прекршители, возачи, полицајци)	EMBG
Kategorija	Категории на возила и возачки дозволи	id_kategorija
Vozilo	Возила со модел и категорија	broj_na_sasija
Boja_vozilo	Бои на возило (повеќевредносен атрибут)	(broj_na_sasija, boja)
Vozacka_dozvola	Возачки дозволи	broj_dozvola
Kategorija_Vozacka_dozvola	Категории положени на возачка	(id_kategorija, broj_dozvola)
Registracija	Регистрации на возила	id_registracija
Policaec	Полицајци (специјализација на Gragjanin)	EMBG_P
Sektor	Сектори за внатрешни работи (СВР)	id_sektor
Stanica	Полициски станици	id_stanica
Raboti_vo	Историја на работа на полицаец во станица	(EMBG_P, id_stanica)
Kazna	Износи и описи на казни	id_kazna
Prekrsok	Типови прекршоци	id_prekrsok
Slucaj	Случаи (обединуваат повеќе записници)	id_slucaj
Zapisnik	Записник за прекршок	id_na_zapisnik
Stavka_Zapisnik	Ставки на записник (поединечни прекршоци)	id_stavka
Zadaca	Задачи на полицаец во рамки на случај	id_zadaca
Uplata	Уплати за казни	id_uplata

2.2 Ограничувања (Constraints)

• CHECK ограничувања: валидација на пол ('M'/'F'), должина на ЕМБГ (13 знаци), формат на телефон (~ '^\+389[0-9]{8}$'), статус на случај ('Otvoren'/'Zatvoren'/'Vo tek'), статус на уплата ('Plateno'/'Neplateno'), статус на задача ('Aktivna'/'Zavrsena'/'Otkazana'), позитивни износи на казни и уплати, валидни датумски опсези.
• FOREIGN KEY: сите врски се дефинирани со ON UPDATE CASCADE и соодветно ON DELETE RESTRICT (за чување интегритет) или ON DELETE CASCADE (за зависни ставки).
• DEFAULT: предефинирани вредности за ime/prezime ('unknown'), статус на случај ('Otvoren'), статус на уплата ('Neplateno'), статус на задача ('Aktivna'), Potpis (false).
• Self-reference: табелата Policaec има самоповикувачки FK (EMBG_nadreden → EMBG_P) за хиерархија на надредени.

3. DML - Пополнување на податоци

3.1 Стратегија за генерирање

За постигнување реалистичен и милионски обем на податоци, користени се следните техники:

• generate_series(1, 1000000) за масовно генерирање редови.
• Помошни (TEMP) и import табели за имиња, презимиња, адреси (поделени по пол).
• Детерминистички row_number() со модуло (%) за рамномерна распределба меѓу понудените вредности.
• random() со тежински распределби за реалистичен микс категории возила, модели, статуси.
• ON CONFLICT DO NOTHING за избегнување дупликати на ЕМБГ/број на шасија/број на дозвола.

3.2 Редослед на пополнување

1. Kategorija - фиксна шифра на категории возачки дозволи (AM, A1, A, B, C, D, итн).
2. Kazna - import табела. Не може да се генерира на random бидејќи претставува законски дефиниран ценовник на глоби (од 15€ до 500€). Се вчитува од подготвена CSV-листа со 20 фиксни износи.
3. Prekrsok - import табела. Не може да се генерира на random бидејќи секој прекршок има точно дефинирано име, опис и припадна казна (FK кон Kazna). Се вчитува од подготвена CSV-листа со 100 типови прекршоци, по примерот на реалниот сообраќаен закон (брзина, алкохол, појас, паркинг, мотор, итн.).
4. Gragjanin - генерирани ~2.000.000 граѓани (1М машки + 1М женски) со валиден ЕМБГ според формула DDMMYYY-RR-G-NN-C.
5. Policaec - селектирани 500 шефови (над 30 год.) и 9500 обични полицајци (над 21 год.) со хиерархиска поврзаност.
6. Sektor (8) и Stanica (20) - фиксни СВР сектори и припадни полициски станици.
7. Raboti_vo - полицаец → станица според градот од адресата на граѓанинот, со fallback на random станица.
8. Vozilo - 1.000.000 возила со тежинска распределба на категориите (70% автомобили, мото, камиони, трактори, итн).
9. Boja_vozilo - главна боја + 15% возила со дополнителна боја.
10. Vozacka_dozvola - за 65% од граѓаните над 18 години.
11. Kategorija_Vozacka_dozvola - основна категорија (B, A) + дополнителни (C, CE, D, F) според проценти.
12. Registracija - историски (3 години) и нови регистрации со табличка кодирана според град (SK, BT, KU, TE, OH, ST, VE, PP, KO, GV, GE, SR).
13. Slucaj - 1.000.000 случаи распределени во последните 5 години со логика за статус.
14. Zadaca - 3-6 задачи по случај, со статус зависен од статусот на случајот.
15. Zapisnik - ~10М записници распределени по полицајци (тежинска дистрибуција).
16. Stavka_Zapisnik - 1-3 прекршоци по записник.
17. Uplata - за секој записник, со ~70% статус 'Plateno' и автоматска пресметка на износот.

4. Конечен обем на податоци

Сите 18 табели и приближниот број записи во секоја:

#	Табела	Број записи
1	Gragjanin	~2.000.000
2	Kategorija	16
3	Vozilo	~1.000.000
4	Boja_vozilo	~1.150.000
5	Vozacka_dozvola	~1.200.000
6	Kategorija_Vozacka_dozvola	~1.500.000
7	Registracija	~5.000.000
8	Policaec	10.000
9	Sektor	8
10	Stanica	20
11	Raboti_vo	~10.000
12	Kazna	20
13	Prekrsok	100
14	Slucaj	1.000.000
15	Zadaca	~4.500.000
16	Zapisnik	~10.000.000
17	Stavka_Zapisnik	~20.000.000
18	Uplata	~10.000.000

5. Прилози

• DDL скрипта​ - креирање на сите табели и ограничувања.
• DML скрипта​ - пополнување на базата.