= Релационен дизајн =

== Релациона шема ==

Релациониот модел е добиен од ЕР моделот v01 со '''парцијална трансформација'''. Сите релации во ЕР моделот се од тип 1:N, па секоја од нив е претставена со надворешен клуч во табелата од страната N. Нема релации од тип N:M, па не се потребни дополнителни табели за релациите. Assignments е посебно множество ентитети во ЕР моделот и затоа е претставено со своја табела.

'''Нотација:'''
 * '''__примарен клуч__''' - задебелен и подвлечен
 * '''задолжителен атрибут''' (NOT NULL) - задебелен
 * надворешен клуч* - означен со * и табелата кон која покажува
 * незадолжителен атрибут - без ознака

'''admins''' ('''__admin_id__''', '''full_name''', '''email''', '''password''')

'''workers''' ('''__worker_id__''', '''full_name''', '''email''', '''password''')

'''citizens''' ('''__citizen_id__''', '''full_name''', '''email''', phone, '''password''')

'''categories''' ('''__category_id__''', '''name''', '''admin_id*''' (admins))

'''reports''' ('''__report_id__''', '''description''', location_text, latitude, longitude, '''status''', '''priority''', '''created_at''', '''citizen_id*''' (citizens), '''category_id*''' (categories))

'''photos''' ('''__photo_id__''', '''image_url''', '''report_id*''' (reports))

'''status_logs''' ('''__log_id__''', '''status''', '''changed_at''', note, '''report_id*''' (reports), worker_id* (workers))

'''comments''' ('''__comment_id__''', '''content''', '''created_at''', '''report_id*''' (reports), '''worker_id*''' (workers))

'''assignments''' ('''__assignment_id__''', '''assigned_at''', note, '''admin_id*''' (admins), '''report_id*''' (reports), '''worker_id*''' (workers))

'''Дополнителни ограничувања:'''
 * email е единствен во admins, workers и citizens; name е единствен во categories.
 * Комбинацијата (report_id, worker_id) во assignments е единствена, бидејќи ист работник не може двапати да биде доделен на иста пријава.
 * status може да биде: submitted, received, in_progress, resolved, rejected.
 * priority може да биде: low, medium, high, urgent.
 * latitude е помеѓу -90 и 90, longitude помеѓу -180 и 180, и двете се внесуваат заедно.
 * Секоја пријава мора да има барем адреса (location_text) или координати.
 * worker_id во status_logs е незадолжителен, бидејќи почетниот статус „submitted“ го креира системот.

== DDL скрипта за креирање на шемата ==

[attachment:schema_creation.sql]

Скриптата ја брише постоечката шема project (доколку постои) и ги креира одново сите табели со сите ограничувања, па може да се извршува и на празна база и на база каде шемата веќе постои.

== DML скрипта за полнење на табелите ==

[attachment:data_load.sql]

Скриптата ги брише постоечките податоци од сите табели и ги внесува одново. Податоците ги опфаќаат сите ситуации во системот: решени пријави, пријави во тек, одбиени пријави и нови пријави кои сè уште не се доделени на работник.

== Релационен дијаграм ==

[[Image(relational_schema.jpg, width=100%)]]