Фаза 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)
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'),
• позитивни износи на казни и уплати,
• како и валидни датумски опсези (на пр. рокот на важност мора да биде по датумот на издавање).

DEFAULT вредностите даваат разумни почетни состојби: ime и prezime ('unknown'), статус на случај ('Otvoren'), статус на уплата ('Neplateno'), статус на задача ('Aktivna') и Potpis (false). Табелата 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 за избегнување дупликати на ЕМБГ/број на шасија/број на дозвола.

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	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