Logical and Physical Design, DB Creation (DDL)

Notation

• PK marked with [PK]
• FK marked with [FK→Table(column)]
• UQ = unique constraint

Relation schema

USERS + SUBTYPES

• Users(user_id [PK], username [UQ], email [UQ], name, surname, created_at)
• Admin(user_id [PK, FK→Users(user_id)])
• Client(user_id [PK, FK→Users(user_id)])
• Owner(user_id [PK, FK→Client(user_id)]) (Owner is a subtype of Client, as in the diagram)

NOTIFICATIONS

• Notification(notification_id [PK], user_id [FK→Users(user_id)], type, message, is_read, created_at)

VET CLINICS

• VetClinic(clinic_id [PK], name, email, phone, city, address, approved_by [FK→Admin(user_id)])

ANIMALS

• Animal(animal_id [PK], owner_id [FK→Owner(user_id)], name, sex, date_of_birth, photo_url, type, species, breed, located_name)

LISTINGS

• Listing(listing_id [PK], owner_id [FK→Owner(user_id)], animal_id [FK→Animal(animal_id)], status, price, description, created_at)

APPOINTMENTS

• Appointment(appointment_id [PK], clinic_id [FK→VetClinic(clinic_id)], animal_id [FK→Animal(animal_id)], responsible_owner_id [FK→Owner(user_id)], status, date_time, notes)

REVIEWS (SUPERTYPE + SUBTYPES)

• Review(review_id [PK], reviewer_id [FK→Client(user_id)], rating, comment, created_at)
• UserReview(review_id [PK, FK→Review(review_id)], target_user_id [FK→Users(user_id)])
• ClinicReview(review_id [PK, FK→Review(review_id)], target_clinic_id [FK→VetClinic(clinic_id)])

HEALTH RECORDS

• HealthRecord(healthrecord_id [PK], animal_id [FK→Animal(animal_id)], appointment_id [FK→Appointment(appointment_id)], type, description, date)

---

Business rules enforced in DDL:
Review rating range (1–5)
Disjoint review specialization (a Review can be either UserReview or ClinicReview, not both) via trigger
Clinic review constraint: reviewer must have at least one DONE appointment at that clinic (via trigger)
HealthRecord consistency: its animal_id must match appointment.animal_id (via trigger)

DDL

• schema_creation.sql​

DML