Entity-Relationship Model v.01

Diagram

Source file: ERModel_v01.xml​

Data requirements

Entities

Reports

Represents a single communal problem reported by a citizen (damaged road, broken street light, waste...). It is the central entity of the system because photos, status changes, comments and assignments all refer to a report.

Candidate keys: report_id is the only candidate key. No natural attribute uniquely identifies a report, so a generated numeric identifier is used as the primary key.

Attributes:

• report_id - numeric, required, auto-generated
• description - text, required
• location_text - text, optional (manually entered address)
• latitude - numeric, optional, between -90 and 90
• longitude - numeric, optional, between -180 and 180
• status - text, required, one of: submitted, received, in_progress, resolved, rejected
• priority - text, required, one of: low, medium, high, urgent
• created_at - date and time, required

At least one of location_text or latitude/longitude must be entered.

Citizens

...

Relationships

Submits (Citizens - Reports)

Each report is submitted by exactly one citizen, and a citizen can submit many reports. Participation of Reports is total, because a report cannot exist without its author.

Classifies (Categories - Reports)

...

Entity-Relationship Model History

• v01 - Initial version of the ER model.