Exercise 8: Scientific Research

erDiagram

    project {
        string title
        string description
        datetime start_date
        datetime end_date
        decimal budget
        decimal total_cost
        int key PK "id_project"
        int id_project_state FK "id_project_state"
    }

    researcher {
        string name
        datetime birth_date
        string field_of_study
        int key PK "id_researcher"
    }

    project_researcher {
      int key PK "id_project_researcher"
      decimal cost
      int id_project FK "id_project"
      int id_researcher FK "id_researcher"
    }

    publication {
        string title
        string journal
        datetime publication_date
        int key PK "id_publication"
    }

    project_state {
        string name
        string label
        string uuid
        int key PK "id_project_state"
    }

    project_publication {
        int key PK "id_project_publication"
        int id_project FK "id_project"
        int id_publication FK "id_publication"
    }

    researcher one--many project_researcher : leads
    project one--many project_researcher : has
    project one--many project_publication : discovers
    publication one--many project_publication : found_in
    project_state one--many project : has

Description #

• The property “name” in the table “project_state” can have the values: “Proposed”, “Active”, “Completed”, “Canceled”.

• In entity “project_state”, name represents the state that will be displayed on the screen, label represents the value that programmer will use to filter the states, and the uuid represents a value to distinguish the states.

• OBS: The value of the field “uuid” is generated automatically.

Tasks #

• researcher/project/publication/project_state/project_publication/project_researcher: Add, Edit, Delete.

  • When the project is created, it will automatically become “Proposed”.
  • The field “Project state” from entity “project” will be read-only in edit view.

• Create a catalog for entities: “project_state”, “project”, “publication” and “researcher”.

• Create event listeners for tables that needs validations (e.g., adding an researcher without a name, project without title, etc., should not be allowed).

• When adding a researcher on a project, it calculates the total cost of the project.

  • The total cost cannot exceed the project budget → validation message.
  • Ensure the researcher is not assigned to another project with overlapping start and end dates before adding them to the new project → validation message.

• Add a new entity action at the project level “Cancel project”:

  • It will change the project status to “Canceled” → validation message;
  • A project cannot be canceled if it is in the “Completed” or “Active” state → validation message;
  • A project cannot be canceled if it is already in the “Canceled” state → validation message.

• Add a new entity action at the project level “Initiate project”:

  • After the initiation of the project, the state will change from “Proposed” → “Active”;
  • If the project is already initiated, it can’t be again initiated → validation message;
  • A project cannot be initiated if it is in the “Completed” or “Canceled” state → validation message;
  • The project must have at least one researcher before it becomes “Active”, if not → validation message.

• Add a new entity action at the project level “Complete project”:

  • It can’t be completed if it is “Proposed” → validation message;
  • It can’t be completed if it is “Canceled” → validation message;
  • It can’t be completed if it is already in “Completed” state → validation message.

• Researcher report: project situation at researcher level (total number of projects, budget value, total cost of researchers).

• Publication report: project situation at publication level (total number of projects, budget value).