Exercise 11: Adventure Travel

erDiagram

    traveler {
        string name
        string phone
        string address
        int key PK "id_traveler"
    }

    destination {
        string name
        string description
        string country
        int key PK "id_destination"
    }

    trip {
        string title
        datetime start_date
        datetime end_date
        int days
        int hours_per_day
        decimal budget
        int key PK "id_trip"
        int id_traveler FK "id_traveler"
        int id_trip_state FK "id_trip_state"
    }

    trip_traveler {
      decimal cost
      int key PK "id_trip_traveler"
      int id_trip FK "id_trip"
      int id_traveler FK "id_traveler"
    }

    trip_days {
      int day_number
      int nr_of_hours_way
      int key PK "id_trip_days"
      int id_trip FK "id_trip"
    }

    trip_state {
        string name
        string label
        string uuid
        int key PK "id_trip_state"
    }

    trip_destination {
        int key PK "id_trip_destination"
        int id_trip FK "id_trip"
        int id_destination FK "id_destination"
    }

    trip_traveler many--one traveler : has
    trip_traveler many--one trip : has
    trip one--many trip_days : has
    trip_state one--many trip : has
    destination one--many trip_destination : includes
    trip one--many trip_destination : visits

Description #

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

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

• traveler/destination/trip/trip_state/trip_destination/trip_traveler/trip_days: Add, Edit, Delete.

  • When the trip is created, it will automatically become “Planned”.

  • The field “Trip state” from entity “trip” will be read-only in edit view.

• Create a catalog for entities: “trip_state”, “trip”, “traveler”, “destination”, “trip_traveler” and “trip_days”.

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

• Check if a passager is available for going in a new trip. He is available when he is not already in an ongoing trip, if he is not -> validation message.

• Check in entity “travel_days” if the day number is smaller than one or larger than total days of the trip. If it is → validation message.

• Automatically calculate the number of days of a trip.

• Automatically calculate the budget of a trip. The budget is calculated by adding total cost of the travelers.

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

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

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

  • First check if the trip has already been initiated (it has the status “Ongoing”), if it has → validation message;
  • After the initiation of the trip, the state will change fom “Planned” → “Ongoing”;
  • If there is not at least 10 travelers → validation message;
  • A trip cannot be initiated if it is in the “Canceled” or “Completed” state, if it is → validation message.

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

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

• Trip report: traveler situation at trip level (total number of travelers, total hours on the way).

• Traveler report: trip situation at traveler level (total number of trips, total money spent).

• Trip state report: trip state situation at trip level (total number of trips by their state).