Changes between Version 3 and Version 4 of DatabaseCreation


Ignore:
Timestamp:
06/14/26 17:19:43 (6 days ago)
Author:
231025
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • DatabaseCreation

    v3 v4  
    33
    44
    5 
    65[[BR]]
    7 == 1. Опис на фазата ==
    8 Во оваа фаза е реализирана физичка имплементација на релациониот модел дефиниран во Фаза 1. Опфатени се двата клучни чекори:
    9 
    10 * '''DDL (Data Definition Language)''' — креирање на сите табели заедно со примарни клучеви, надворешни клучеви, CHECK ограничувања и DEFAULT вредности.
    11 * '''DML (Data Manipulation Language)''' — пополнување на табелите со реалистични податоци (милионски обем) преку SQL процедурален пристап.
    12 
    13 
    14 [[BR]]
    15 == 2. DDL - Креирање на табели ==
    16 
    17 === 2.1 Преглед на табели ===
    18 ||='''Табела'''=||='''Опис'''=||='''Примарен клуч'''=||
    19 || Gragjanin || Граѓани (потенцијални прекршители, возачи, полицајци) || EMBG ||
    20 || Kategorija || Категории на возила и возачки дозволи || id_kategorija ||
     6== 1. DDL ==
     7[attachment:DDL.sql DDL.sql]
     8=== 1.1 Преглед на табели ===
     9|| Табела || Опис || Примарен клуч ||
     10|| Gragjanin || Основен ентитет за граѓаните; се јавува како прекршител, возач или полицаец || EMBG ||
     11|| Kategorija || Шифрарник на категории возила и возачки дозволи || id_kategorija ||
    2112|| Vozilo || Возила со модел и категорија || broj_na_sasija ||
    2213|| Boja_vozilo || Бои на возило (повеќевредносен атрибут) || (broj_na_sasija, boja) ||
    23 || Vozacka_dozvola || Возачки дозволи || broj_dozvola ||
    24 || Kategorija_Vozacka_dozvola || Категории положени на возачка || (id_kategorija, broj_dozvola) ||
    25 || Registracija || Регистрации на возила || id_registracija ||
     14|| Vozacka_dozvola || Возачки дозволи со датум и рок на важност || broj_dozvola ||
     15|| Kategorija_Vozacka_dozvola || Положени категории по дозвола || (id_kategorija, broj_dozvola) ||
     16|| Registracija || Регистрации на возила по граѓанин, со историја || id_registracija ||
    2617|| Policaec || Полицајци (специјализација на Gragjanin) || EMBG_P ||
    2718|| Sektor || Сектори за внатрешни работи (СВР) || id_sektor ||
    28 || Stanica || Полициски станици || id_stanica ||
     19|| Stanica || Полициски станици во рамки на сектор || id_stanica ||
    2920|| Raboti_vo || Историја на работа на полицаец во станица || (EMBG_P, id_stanica) ||
    3021|| Kazna || Износи и описи на казни || id_kazna ||
    31 || Prekrsok || Типови прекршоци || id_prekrsok ||
    32 || Slucaj || Случаи (обединуваат повеќе записници) || id_slucaj ||
    33 || Zapisnik || Записник за прекршок || id_na_zapisnik ||
    34 || Stavka_Zapisnik || Ставки на записник (поединечни прекршоци) || id_stavka ||
     22|| Prekrsok || Типови прекршоци, секој со припадна казна || id_prekrsok ||
     23|| Slucaj || Случаи што обединуваат повеќе записници || id_slucaj ||
     24|| Zapisnik || Записник за прекршок; поврзува полицаец, граѓанин и возило || id_na_zapisnik ||
     25|| Stavka_Zapisnik || Поединечни прекршоци (ставки) во записник || id_stavka ||
    3526|| Zadaca || Задачи на полицаец во рамки на случај || id_zadaca ||
    36 || Uplata || Уплати за казни || id_uplata ||
    37 
    38 === 2.2 Ограничувања (Constraints) ===
    39 * '''CHECK ограничувања''': валидација на пол ('M'/'F'), должина на ЕМБГ (13 знаци), формат на телефон `(~ '^\+389[0-9]{8}$')`, статус на случај ('Otvoren'/'Zatvoren'/'Vo tek'), статус на уплата ('Plateno'/'Neplateno'), статус на задача ('Aktivna'/'Zavrsena'/'Otkazana'), позитивни износи на казни и уплати, валидни датумски опсези.
    40 * '''FOREIGN KEY''': сите врски се дефинирани со `ON UPDATE CASCADE` и соодветно `ON DELETE RESTRICT` (за чување интегритет) или `ON DELETE CASCADE` (за зависни ставки).
    41 * '''DEFAULT''': предефинирани вредности за ime/prezime ('unknown'), статус на случај ('Otvoren'), статус на уплата ('Neplateno'), статус на задача ('Aktivna'), Potpis (false).
    42 * '''Self-reference''': табелата Policaec има самоповикувачки FK (EMBG_nadreden → EMBG_P) за хиерархија на надредени.
    43 
    44 
    45 
     27|| Uplata || Уплати за казни по записник || id_uplata ||
     28 
     29=== 1.2 Ограничувања ===
     30`CHECK` ограничувањата ја штитат валидноста на податоците:
     31* пол ('M' или 'F'),
     32* должина на ЕМБГ од точно 13 знаци,
     33* формат на телефон (префикс +389 проследен со 8 цифри),
     34* дозволени статуси за случај ('Otvoren', 'Zatvoren', 'Vo tek'),
     35* за уплата ('Plateno', 'Neplateno', 'Sudska_postapka') и за задача ('Aktivna', 'Zavrsena', 'Otkazana'),
     36* позитивни износи на казни и уплати,
     37* како и валидни датумски опсези (на пр. рокот на важност мора да биде по датумот на издавање).
     38 
     39`DEFAULT` вредностите даваат разумни почетни состојби: ime и prezime ('unknown'), статус на случај ('Otvoren'), статус на уплата ('Neplateno'), статус на задача ('Aktivna') и Potpis (false).
     40 
     41Табелата `Policaec` има самоповикувачки надворешен клуч (EMBG_nadreden покажува кон EMBG_P) со кој се моделира хиерархијата на надредени.
     42 
     43=== 1.3 Бришење и ажурирање на податоците ===
     44Сите надворешни клучеви користат ON UPDATE CASCADE: ако се промени вредноста на примарниот клуч кај родителот, промената автоматски се пренесува и кај децата.
     45 
     46`ON DELETE CASCADE` се користи таму каде детето нема смисла без својот родител, па се брише заедно со него:
     47* `Boja_vozilo` кон `Vozilo`: бојата е особина на возилото, па бришење на возилото ги брише и неговите бои.
     48* `Stavka_Zapisnik` кон `Zapisnik`: ставките се составен дел од записникот, па бришење на записникот ги брише и неговите ставки.
     49* `Kategorija_Vozacka_dozvola` кон `Vozacka_dozvola`: положените категории немаат смисла без дозволата.
     50 
     51`ON DELETE RESTRICT` се користи таму каде детето е самостоен или историски/финансиски запис што не смее да исчезне, па бришењето на родителот се спречува додека постојат зависни редови. На пример, не може да се избрише граѓанин што има регистрации, дозволи или записници, ниту казна што е поврзана со прекршок. Така се штити интегритетот на историјата и на финансиските податоци.
     52 
     53 
    4654[[BR]]
    47 == 3. DML - Пополнување на податоци ==
    48 
    49 === 3.1 Стратегија за генерирање ===
    50 За постигнување реалистичен и милионски обем на податоци, користени се следните техники:
     55== 2. DML ==
     56[attachment:DML.sql DML.sql]
     57=== 2.1 Техники на генерирање ===
     58За реалистичен и милионски обем на податоци се користени:
    5159* `generate_series(1, 1000000)` за масовно генерирање редови.
    5260* Помошни (`TEMP`) и import табели за имиња, презимиња, адреси (поделени по пол).
    … …  
    5462* `random()` со тежински распределби за реалистичен микс категории возила, модели, статуси.
    5563* `ON CONFLICT DO NOTHING` за избегнување дупликати на ЕМБГ/број на шасија/број на дозвола.
    56 
    57 === 3.2 Редослед на пополнување ===
    58 1. '''Kategorija''' - фиксна шифра на категории возачки дозволи (AM, A1, A, B, C, D, итн).
    59 2. '''Kazna''' - '''import табела'''. Не може да се генерира на random бидејќи претставува законски дефиниран ценовник на глоби (од 15€ до 500€). Се вчитува од подготвена CSV-листа со 20 фиксни износи.
    60 3. '''Prekrsok''' - '''import табела'''. Не може да се генерира на random бидејќи секој прекршок има точно дефинирано име, опис и припадна казна (FK кон `Kazna`). Се вчитува од подготвена CSV-листа со 100 типови прекршоци, по примерот на реалниот сообраќаен закон (брзина, алкохол, појас, паркинг, мотор, итн.).
    61 4. '''Gragjanin''' - генерирани ~2.000.000 граѓани (1М машки + 1М женски) со валиден ЕМБГ според формула DDMMYYY-RR-G-NN-C.
    62 5. '''Policaec''' - селектирани 500 шефови (над 30 год.) и 9500 обични полицајци (над 21 год.) со хиерархиска поврзаност.
    63 6. '''Sektor (8) и Stanica (20)''' - фиксни СВР сектори и припадни полициски станици.
    64 7. '''Raboti_vo''' - полицаец → станица според градот од адресата на граѓанинот, со fallback на random станица.
    65 8. '''Vozilo''' - 1.000.000 возила со тежинска распределба на категориите (70% автомобили, мото, камиони, трактори, итн).
    66 9. '''Boja_vozilo''' - главна боја + 15% возила со дополнителна боја.
    67 10. '''Vozacka_dozvola''' - за 65% од граѓаните над 18 години.
    68 11. '''Kategorija_Vozacka_dozvola''' - основна категорија (B, A) + дополнителни (C, CE, D, F) според проценти.
    69 12. '''Registracija''' - историски (3 години) и нови регистрации со табличка кодирана според град (SK, BT, KU, TE, OH, ST, VE, PP, KO, GV, GE, SR).
    70 13. '''Slucaj''' - 1.000.000 случаи распределени во последните 5 години со логика за статус.
    71 14. '''Zadaca''' - 3-6 задачи по случај, со статус зависен од статусот на случајот.
    72 15. '''Zapisnik''' - ~10М записници распределени по полицајци (тежинска дистрибуција).
    73 16. '''Stavka_Zapisnik''' - 1-3 прекршоци по записник.
    74 17. '''Uplata''' - за секој записник, со ~70% статус 'Plateno' и автоматска пресметка на износот.
    7564 
    76 
    77 
    78 [[BR]]
    79 == 4. Конечен обем на податоци ==
    80 Сите 18 табели и приближниот број записи во секоја:
    81  
    82 ||='''#'''=||='''Табела'''=||='''Број записи'''=||
     65=== 2.2 Обем на податоци ===
     66|| # || Табела || Број записи ||
    8367|| 1 || Gragjanin || ~2.000.000 ||
    84 || 2 || Kategorija || 16 || 
     68|| 2 || Kategorija || 16 ||
    8569|| 3 || Vozilo || ~1.000.000 ||
    8670|| 4 || Boja_vozilo || ~1.150.000 ||
    87 || 5 || Vozacka_dozvola || ~1.200.000 || 
    88 || 6 || Kategorija_Vozacka_dozvola || ~1.500.000 || 
     71|| 5 || Vozacka_dozvola || ~1.200.000 ||
     72|| 6 || Kategorija_Vozacka_dozvola || ~1.500.000 ||
    8973|| 7 || Registracija || ~5.000.000 ||
    90 || 8 || Policaec || 10.000 || 
    91 || 9 || Sektor || 8 || 
    92 || 10 || Stanica || 20 || 
    93 || 11 || Raboti_vo || ~10.000 || 
    94 || 12 || Kazna || 20 || 
    95 || 13 || Prekrsok || 100 || 
    96 || 14 || Slucaj || 1.000.000 || 
    97 || 15 || Zadaca || ~4.500.000 || 
    98 || 16 || Zapisnik || ~10.000.000 || 
    99 || 17 || Stavka_Zapisnik || ~20.000.000 || 
     74|| 8 || Policaec || 10.000 ||
     75|| 9 || Sektor || 8 ||
     76|| 10 || Stanica || 20 ||
     77|| 11 || Raboti_vo || ~10.000 ||
     78|| 12 || Kazna || 20 ||
     79|| 13 || Prekrsok || 100 ||
     80|| 14 || Slucaj || 1.000.000 ||
     81|| 15 || Zadaca || ~4.500.000 ||
     82|| 16 || Zapisnik || ~10.000.000 ||
     83|| 17 || Stavka_Zapisnik || ~20.000.000 ||
    10084|| 18 || Uplata || ~10.000.000 ||
    101  
    102 [[BR]]
    103 == 5. Прилози ==
    104 * [attachment:DDL.sql DDL скрипта] - креирање на сите табели и ограничувања.
    105 * [attachment:DML.sql DML скрипта] - пополнување на базата.
    106