Exercise 19: Archaeological Expedition

erDiagram

    expedition {
        string title
        string location
        datetime start_date
        datetime end_date
        decimal budget
        decimal total_cost
        int key PK "id_expedition"
        int id_expedition_state FK "id_expedition_state"
    }

    archaeologist {
        string name
        string contact
        string specialization
        int key PK "id_archaeologist"
    }

    expedition_archaeologist {
      decimal cost
      int key PK "id_expedition_archaeologist"
      int id_expedition FK "id_expedition"
      int id_archaeologist FK "id_archaeologist"
    }

    artifact {
        string name
        string description
        datetime discovery_date
        decimal value
        int key PK "id_artifact"
    }

    expedition_state {
        string name
        string label
        string uuid
        int key PK "id_expedition_state"
    }

    expedition_artifact {
        int key PK "id_expedition_artifact"
        int id_expedition FK "id_expedition"
        int id_artifact FK "id_artifact"
    }

    archaeologist one--many expedition_archaeologist : leads
    expedition one--many expedition_archaeologist : has
    expedition one--many expedition_artifact : discovers
    artifact one--many expedition_artifact : found_in
    expedition_state one--many expedition : has

Description #

• The property “name” in the table “expedition_state” can have the values: “Planned”, “Ongoing”, “Completed”, “Canceled”.

• This app manages archaeological expeditions, tracking details like the title, location, dates, budget, and costs. It links archaeologists to expeditions, noting their specialization and costs associated with their involvement.

• Expeditions can discover multiple artifacts, and each artifact is connected to specific expeditions.

• The app also tracks the state of each expedition (e.g., planned, ongoing, completed). It organizes the relationships between archaeologists, expeditions, and artifacts for efficient management of archaeological projects.

• In entity “expedition_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 #

• archaeologist/expedition/artifact/expedition_state/expedition_artifact/expedition_archaeologist: Add, Edit, Delete.

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

• Create a catalog for entities: “expedition_state”, “expedition”, “artifact” and “archaeologist”.

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

• The total cost for the archaeologists should not be grater than the expedition budget, if it is → validation message.

• An archaeologist is available if the selected time interval does not overlap with an existing expedition in which he participates, if it does overlap, that means he is not available → validation message.

• An expedition should have at least one archaeologist, if it doesn’t → validation message.

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

  • After the initiation of the expedition, the state will change from “Planned” → “Ongoing”;
  • An expedition cannot be initiated if it is in the “Completed” or “Canceled” state → validation message;
  • An expedition cannot be initiated if it is already in the “Ongoing” state → validation message.

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

  • It will change the expedition status to “Canceled”;
  • An expedition cannot be canceled if it is in the “Completed” state → validation message;
  • An expedition cannot be canceled if it is alreay in the “Canceled” state → validation message.

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

  • It will change the status from “Ongoing” → “Completed”;
  • It can’t be completed if it is in the “Planned” state → validation message;
  • It can’t be completed if it is in the “Canceled” state → validation message;
  • It can’t be completed if it is already in the “Completed” state → validation message.

• Archaeologist report: expedition situation at archaeologist level (total number of expeditions, artifact value).

• Artifact report: expedition situation at artifact level (total number of expeditions, artifact value).

• Expedition report: archaeologist and artifact situation at expedition level (total number of archeologists, total number of artifacts).