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)
• Vet_clinic(user_id [PK, FK→vet_clinic(user_id)])

NOTIFICATIONS

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

VET CLINIC APPLICATIONS

• vet_clinic_applications(application_id[PK],name,email,phone,city,address,submitted_at,status,reviewd_at,reviewd_by[FK→Admin(user_id)],denial_reason)

VET CLINICS

• VetClinic(clinic_id [PK], name, email, phone, city, address, user_if(FK→vet_clinic(user_id)),application_id[FK→vet_clinic_applications(application_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)

FAVORITE LISTINGS

• favorite_listings(client_id[FK→User(user_id)],listing_id[FK→Listing(listing_id)])→The whole thing is the PK

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)

CLINIC UNAVAILABLE SLOTS

• clinic_unavailable_slots(slot_id[PK],clinic_id[FK→vet_clinics(clinic_id)],date_time,reason,created_at)

---

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

• data_load.sql​

Relational diagram