= 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:
[[BR]]
Review rating range (1–5)
[[BR]]
Disjoint review specialization (a Review can be either !UserReview or !ClinicReview, not both) via trigger
[[BR]]
Clinic review constraint: reviewer must have at least one DONE appointment at that clinic (via trigger)
[[BR]]
!HealthRecord consistency: its animal_id must match appointment.animal_id (via trigger)
== DDL
 * [attachment:schema_creation.sql]
== DML
* [attachment:data_load.sql]
== Relational diagram
[[Image(relational_schema.jpg)]]