Exercise 3: Pet Clinic

erDiagram

   service {
        string name
        string label
        string uuid
        decimal price
        int key PK "id_service"
    }

    appointment_state {
        string name
        string label
        string uuid
        int key PK "appointment_state"
    }

    appointment {
        string number
        datetime date
        decimal price
        int key PK "id_appointment"
        int id_appointment_state FK "appointment_state"
        int id_service FK "service"
        int id_animal FK "id_animal"
    }

    consultation {
        datetime date
        string results
        decimal weight
        decimal price
        decimal value
        int key PK "id_consultation"
        int id_appointment FK "appointment"
        int id_service FK "service"
        int id_animal FK "id_animal"
    }

    owner {
        string first_name
        string last_name
        string phone
        int key PK "id_owner"
    }

    animal {
        string race
        string sex
        datetime birthday_date
        decimal weight
        int key PK "id_animal"
        int id_owner FK "id_owner"
    }

    service one--many appointment : has
    animal one--many appointment : has
    appointment_state one--many appointment : has
    service one--many consultation : has
    animal one--many consultation : has
    appointment one--many consultation : has
    owner one--many animal : has

Description #

• Create an application for managing services within a pet clinic. The application should keep track of services, registered animals for appointments, and their owners.

• An animal is identified by breed, birth date, weight, sex, and an owner. An owner can have multiple animals.

• The assistant should see all available appointments and prices for each service, as well as the state of each appointment.

• Before finalizing an appointment registration, the application should check availability based on date and time. The appointment is finalized by converting it into a consultation.

• The property “name” in the table “appointment_state” can have the values: “Pending”, “Processed” and “Canceled”.

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

• service/appointment_state/appointment/consultation/owner/animal: Add, Edit, Delete.

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

• Create a catalog for entities: “appointment_state”, “appointment”, “service”, “owner” and “animal”.

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

• Check if a new appointment is available at a specific time. It should not overlap with an existing one, if it does overlap → validation message.

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

  • It will change the appointment state to “Canceled”;
  • An appointment cannot be canceled if it is in the “Processed” state → validation message;
  • An appointment cannot be canceled if it is already in the “Canceled” state → validation message.

• Add a new entity action at the appointment level “Generate consultation”:

  • First check if a consultation has already been generated from the same appointment, if it was → validation message;
  • After creating the consultation, the appointment will have the “Processed” state;
  • It can’t be generated if it is “Canceled” → validation message;

• Owner report: situation of owners (total number of owners, contact details).

• Animal report: situation of animals (total number of animals, details per owner).

• Service report: situation of services (total number of services, service details).