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

1. DDL

ddl.sql​

1.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)
Sopstvenost	Сопственост на возило од граѓанин, со период на важење (datum_steknuvanje/datum_kraj)	id_sopstvenost
Registerska_tablica	Регистарски таблици (регион, број, код)	id_tablica
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

1.2 Ограничувања

CHECK ограничувањата ја штитат валидноста на податоците:

• пол ('M' или 'F'),
• должина на ЕМБГ од точно 13 знаци,
• формат на телефон (префикс +389 проследен со 8 цифри),
• дозволени статуси за случај ('Otvoren', 'Zatvoren', 'Vo tek'),
• за уплата ('Plateno', 'Neplateno', 'Sudska_postapka') и за задача ('Aktivna', 'Zavrsena', 'Otkazana'),
• позитивни износи на казни и уплати,
• валиден период на сопственост (datum_kraj мора да биде по datum_steknuvanje, ако постои),
• како и валидни датумски опсези (на пр. рокот на важност мора да биде по датумот на издавање).

Покрај примарните клучеви, UNIQUE ограничувањето на Registerska_tablica (region, broj, kod) спречува две возила да добијат иста регистарска таблица. DEFAULT вредностите даваат разумни почетни состојби: ime и prezime ('unknown'), статус на случај ('Otvoren'), статус на уплата ('Neplateno'), статус на задача ('Aktivna'), Potpis (false) и datum_steknuvanje (тековен датум). Табелата Policaec има самоповикувачки надворешен клуч (EMBG_nadreden покажува кон EMBG_P) со кој се моделира хиерархијата на надредени.

1.3 Бришење и ажурирање на податоците

Сите надворешни клучеви користат ON UPDATE CASCADE: ако се промени вредноста на примарниот клуч кај родителот, промената автоматски се пренесува и кај децата.

За бришење, никаде не користиме ON DELETE CASCADE-сите надворешни клучеви се ON DELETE RESTRICT. Бидејќи базата чува чувствителни, историски и финансиски податоци (записници, ставки, плаќања, сопствености, регистрации, дозволи), не сакаме автоматско бришење на поврзани записи, бидејќи тоа лесно би можело да доведе до случаен губиток на важни податоци. Затоа родител не може да се избрише додека постојат негови деца - на пример, не може да се избрише граѓанин што има сопствености, дозволи или записници, ниту возило што има внесени бои или сопствености, ниту казна што е поврзана со прекршок. Со ова бришењето секогаш мора да биде свесна, експлицитна одлука, со што се штити интегритетот на историјата и на финансиските податоци.

2. DML

dml.sql​

2.1 Техники на генерирање

За реалистичен и милионски обем на податоци се користени:

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

2.2 Обем на податоци

#	Табела	Број записи
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	Sopstvenost	~2.000.000
8	Registerska_tablica	~2.000.000
9	Registracija	~5.000.000
10	Policaec	10.000
11	Sektor	8
12	Stanica	20
13	Raboti_vo	~10.000
14	Kazna	20
15	Prekrsok	100
16	Slucaj	1.000.000
17	Zadaca	~4.500.000
18	Zapisnik	~10.000.000
19	Stavka_Zapisnik	~20.000.000
20	Uplata	~10.000.000