ERModel v01

Diagram

Entities

User

This is the core entity that represents every person registered on PetSitter. It acts as a supertype for Admin/PetOwner/PetSitter so we can store the common attributes only in User.

Candidate keys:

• user_id - Primary key; numeric id, most efficient for indexing
• username - unique but not as efficient as user_id
• email - unique but not as efficient as user_id

Attributes:

• user_id - Numeric (Required, Unique, Auto-generated integer).

• username - Text (Required, Unique, 4-20 characters).

• first_name - Text (Required).

• last_name - Text (Required).

• password - Text (Required, Hashed format, Min. 8 characters).

• email - Text (Required, Unique, Standard email format).

---

Admin

Subtype/specialization of User entity. Inherits from User. Has administrator privileges.

Candidate keys:

• user_id - Inherits user_id from User

Attributes:

• Inherits all attributes from User

---

PetOwner

Subtype/specialization of User entity. Inherits from User. Is able to make bookings for their Pet and request a PetSitter.

Candidate keys:

• user_id - Inherits user_id from User

Attributes:

• Inherits all attributes from User

---

PetSitter

Subtype/specialization of User entity. Inherits from User. Provides services to PetOwner. Is able to confirm or deny bookings.

Candidate keys:

• user_id - Inherits user_id from User

Attributes:

• Inherits all attributes from User

---

Pet

The animal that will be taken care of. Track the identity, special needs, age of the pet in the booking. Linked to PetType to categorize animals more efficiently.

Candidate keys:

• pet_id - Primary key; Unique surrogate key.

Attributes:

• pet_id - Numeric (Required, Unique, Auto-generated integer).

• name - Text (Required).

• photo - Text/URL (Optional, URL format).

• age - Numeric (Required, non-negative integer).

• password - Text (Required, Hashed format, Min. 8 characters).

• special_needs - Text (Optional).

• description - Text (Optional).

---

PetType

Entity used to categorize pets (for example Cat, Dog, Rabbit, Bird). Used for storing attributes that would repeat a lot of times in one single Pet entity.

Candidate keys:

• pet_id - Primary key; Unique surrogate key.
• species - Unique but not as efficient as a numeric id key.

Attributes:

• pettype_id - Numeric (Required, Unique, Auto-generated integer).

• species - Text (Required, Unique).

• average_lifespan - Numeric (Optional).

• needs_outdoor_walk - Boolean (Required, 0 or 1).

---

Booking

The entity that connects PetOwner, PetSitter, Pet and the services requested for the Pet

Candidate keys:

• booking_id - Primary key; Unique surrogate key.

Attributes:

• booking_id - Numeric (Required, Unique, Auto-generated integer).

• status - Text (Required, for example 'Pending', 'Confirmed', 'Completed', 'Rejected').

• date_from - Date (Required).

• date_to - Date (Required).

• address - Text (Required, location of the service).