Entity-Relationship Model v.01

Diagram

Source file: ERModel_v01.xml​

Data requirements

Entities

Admins

Represents the administrators of the system. Administrators have full control over the application: they manage user accounts, configure the categories of problems and assign reports to municipal workers. They are modeled as a separate entity set because they take part in different relationships (Manages, Assigns) than the other types of users.

Candidate keys: admin_id and email. email is unique for every administrator, but it can change over time, so the generated numeric identifier admin_id is chosen as the primary key because it is stable and shorter.

Attributes:

• admin_id - numeric, required, auto-generated
• full_name - text, required, up to 100 characters
• email - text, required, unique, valid e-mail format
• password - text, required, stored as a hash, never as plain text

Workers

Represents the municipal workers who process the reports. Workers receive assignments, update the status of reports and write comments about the progress of the work. They are a separate entity set because only they take part in the Receives, Updates and Writes relationships.

Candidate keys: worker_id and email. worker_id is chosen as the primary key for the same reasons as for Admins.

Attributes:

• worker_id - numeric, required, auto-generated
• full_name - text, required, up to 100 characters
• email - text, required, unique, valid e-mail format
• password - text, required, stored as a hash

Citizens

Represents the citizens who use the application to report communal problems. A citizen can submit many reports and follow their status. Citizens are separate from the other users because only they submit reports, and they have an additional optional contact phone.

Candidate keys: citizen_id and email. citizen_id is chosen as the primary key because it is stable, while a citizen may change their e-mail address.

Attributes:

• citizen_id - numeric, required, auto-generated
• full_name - text, required, up to 100 characters
• email - text, required, unique, valid e-mail format
• phone - text, optional, up to 20 characters, digits and the + sign only
• password - text, required, stored as a hash

Categories

Represents the categories of communal problems (for example roads, street lighting, waste). Categories are modeled as a separate entity set instead of a text attribute of the report, so that administrators can add and change them, and so that all reports use the same consistent list of categories.

Candidate keys: category_id and name. name is unique, but a category may be renamed, so category_id is chosen as the primary key.

Attributes:

• category_id - numeric, required, auto-generated
• name - text, required, unique, up to 80 characters

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.

Photos

Represents a photo attached to a report as visual documentation of the problem. It is modeled as a separate entity set because one report can have several photos, which cannot be stored in a single attribute of the report.

Candidate keys: photo_id is the only candidate key and it is used as the primary key.

Attributes:

• photo_id - numeric, required, auto-generated
• image_url - text, required, path or URL of the stored image, up to 500 characters

StatusLogs

Represents one change of the status of a report. Every change (submitted, received, in progress, resolved, rejected) is recorded as a new entry, so the full history of a report is kept. This provides the transparency and accountability required by the project, because citizens can see when and how their report was processed.

Candidate keys: log_id is the only candidate key and it is used as the primary key.

Attributes:

• log_id - numeric, required, auto-generated
• status - text, required, the new status: submitted, received, in_progress, resolved or rejected
• changed_at - date and time, required
• note - text, optional, explanation of the change

Comments

Represents a comment written by a municipal worker about the progress of solving a report. Comments are a separate entity set because one report can receive many comments over time, each with its own author and time.

Candidate keys: comment_id is the only candidate key and it is used as the primary key.

Attributes:

• comment_id - numeric, required, auto-generated
• content - text, required
• created_at - date and time, required

Assignments

Represents the assignment of a report to a municipal worker, made by an administrator. It is modeled as an entity set because the assignment connects three entity sets (Admins, Workers, Reports) and has its own attributes (time and note).

Candidate keys: assignment_id, and the combination of the assigned report and worker, because the same worker cannot be assigned to the same report twice. assignment_id is chosen as the primary key because it is a single simple value.

Attributes:

• assignment_id - numeric, required, auto-generated
• assigned_at - date and time, required
• note - text, optional, instructions from the administrator

Relationships

Manages (Admins - Categories)

Each category is created and managed by exactly one administrator, and an administrator can manage many categories. Participation of Categories is total, because every category must have an administrator responsible for it. Participation of Admins is partial, because an administrator does not have to manage any category.

Classifies (Categories - Reports)

Each report belongs to exactly one category, and a category can contain many reports. Participation of Reports is total, because a report cannot be submitted without a category. Participation of Categories is partial, because a new category may still have no reports.

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. Participation of Citizens is partial, because a registered citizen does not have to submit any report.

Has (Reports - Photos)

Each photo belongs to exactly one report, and a report can have many photos. Participation of Photos is total, because a photo has no meaning without the report it documents. Participation of Reports is partial, because photos are optional.

Logs (Reports - StatusLogs)

Each status change belongs to exactly one report, and a report can have many status changes over time. Participation of StatusLogs is total, because a status change always refers to a specific report.

Updates (Workers - StatusLogs)

A status change can be made by one worker, and a worker can make many status changes. Participation is partial on both sides, because some status changes are created automatically by the system (for example the initial "submitted" status), so they have no worker.

Comments on (Reports - Comments)

Each comment refers to exactly one report, and a report can have many comments. Participation of Comments is total, because a comment always refers to a report.

Writes (Workers - Comments)

Each comment is written by exactly one worker, and a worker can write many comments. Participation of Comments is total, because every comment must have an author.

Assigns (Admins - Assignments)

Each assignment is made by exactly one administrator, and an administrator can make many assignments. Participation of Assignments is total, because every assignment must be made by an administrator.

Assigned via (Reports - Assignments)

Each assignment refers to exactly one report, and a report can be assigned several times (for example to several workers). Participation of Assignments is total. Participation of Reports is partial, because a new report is not yet assigned.

Receives (Workers - Assignments)

Each assignment is given to exactly one worker, and a worker can receive many assignments. Participation of Assignments is total. Participation of Workers is partial, because a worker may currently have no assignments.

Entity-Relationship Model History

• v01 - Initial version of the ER model.

AI Use

ER Model AI Usage