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

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

Релациониот модел е добиен од ЕР моделот 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 скрипта за креирање на шемата

schema_creation.sql​

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

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

data_load.sql​

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

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