Entity Relationship Diagram

ER дијаграмот ја прикажува структурата на податоците во апликацијата Reportium. Преку него се дефинираат ентитетите, нивните атрибути, како и релациите помеѓу нив. Овој дијаграм служи како основна водилка за дизајнирање на базата на податоци.

Податочни побарувања

Ентитети

Strong Entities

1. Person

• Primary Key (PK): person_id - integer
• Attributes:
  • name - text
  • surname - text
  • address - text
  • gender - enum (MALE, FEMALE)
  • date_of_birth - date
  • date_of_death - date
  • contact_phone - text
  • is_alive - boolean
  • is_stub - boolean
  • embg - text

2. Institution

• Primary Key (PK): institution_id - integer
• Attributes:
  • name - text
  • address - text
  • type - enum (PRIMARY SCHOOL, HIGH SCHOOL, UNIVERSITY, ACADEMY)
  • year_established - integer
  • city - text

3. Report

• Primary Key (PK): report_id - integer
• Attributes:
  • report_type - enum (CRIMINAL, MEDICAL, ACADEMIC, EMPLOYMENT)
  • created_at - date
  • summary - text
  • person_id - (Foreign Key to Person Entity) - integer

4. CrimeType

• Primary Key (PK): crime_type_id - integer
• Attributes:
  • label - text
  • severity_level - enum (LOW, MEDIUM, HIGH)

5. Diagnosis

• Primary Key (PK): diagnosis_id - integer
• Attributes:
  • description - text
  • therapy - text
  • severity - enum (LOW, MEDIUM, HIGH)
  • is_chronic - boolean

6. Doctor

• Primary Key (PK): doctor_id - integer
• Attributes:
  • surname - text
  • years_of_experience - integer
  • specialization - text
  • name - text
  • is_active - boolean

7. Role

• Primary Key (PK): role_id - integer
• Attributes:
  • role_name - integer
  • description - text

8. FilterSession

• Primary Key (PK): session_id - integer
• Attributes:
  • filter_description - text
  • searched_at - timestamp
  • user_id - (Foreign Key to ReportiumUser Entity)

9. ReportiumUser

• Primary Key (PK): user_id - integer
• Attributes:
  • password_hash - text
  • is_active - boolean
  • email - text (email)
  • surname - text
  • name - text
  • created_at - timestamp

10. UserProfile

• Primary Key (PK): profile_id - integer
• Attributes:
  • profile_created_at - timestamp
  • user_id - (Foreign Key to ReportiumUser Entity)
  • username - text
  • role_id - (Foreign Key to Role Entity)

Weak Entities

1. Punishment

• Primary Key (PK): punishment_id - integer
• Attributes:
  • value_unit - enum (EUROS, YEARS)
  • punishment_type - enum (FINE, PRISON)
  • fine_to_pay - double
  • release_date - date
  • report_id - (Foreign Key to Report Entity)

2. UserProfileLog

• Primary Key (PK): log_id - integer
• Attributes:
  • change_description - text
  • profile_id - (Foreign Key to UserProfile Entity)
  • changed_at - timestamp

3. ExportLog

• Primary Key (PK): export_id - integer
• Additional Attributes:
  • export_date - timestamp
  • export_format - enum (CSV, PDF)
  • filter_summary - text
  • file_name - text
  • session_id - (Foreign Key to FilterSession Entity)

Report Subtypes

1. AcademicReport

• Primary Key (PK): report_id (inherited)
• Additional Attributes:
  • institution_id - (Foreign Key to Institution Entity)
  • academic_field - text
  • description_of_report - text

2. CriminalReport

• Primary Key (PK): report_id (inherited)
• Additional Attributes:
  • location - text
  • resolved - boolean
  • crime_type_id - (Foreign Key to CrimeType Entity)
  • descriptive_punishment - text

3. MedicalReport

• Primary Key (PK): report_id (inherited)
• Additional Attributes:
  • doctor_id - (Foreign Key to Doctor Entity)
  • next_control_date - date

4. EmploymentReport

• Primary Key (PK): report_id (inherited)
• Additional Attributes:
  • position_description - text
  • start_date - date
  • end_date - date
  • income_per_month - double