= Normalization
== Initial de-normalized relation and functional dependencies
=== Global set of attributes (single de-normalized relation)
This represents the initial de-normalized relation before any normalization is applied.
To avoid duplicate attribute names, role-based user references are renamed.
{{{
R = {
-- User / roles
user_id, username, email, name, surname, full_name, password_hash, user_created_at,
client_id, is_blocked, blocked_at, blocked_reason,
admin_id,
owner_id,

-- Notification
notification_id, notification_type, notification_message, notification_created_at, is_read,

-- Animal
animal_id, animal_name, located_name, species, animal_type, breed, sex, date_of_birth, photo_url,

-- Listing
listing_id, listing_status, listing_created_at, price, listing_description,

-- Favorite listing
(saved relationship has no separate id in the ER, so it is represented by keys) saved_user_id, saved_listing_id,

-- Vet clinic
clinic_id, clinic_name, clinic_email, clinic_phone, clinic_address, clinic_location, clinic_city,

-- Vet clinic application
application_id, app_name, app_email, app_phone, app_city, app_address, submitted_at, app_status, reviewed_at, reviewed_by, denial_reason,

-- Appointment
appointment_id, date_time, appointment_status, notes,

-- Health record
healthrecord_id, hr_type, hr_description, hr_date,

-- Review (and subtypes)
review_id, reviewer_id, rating, comment, review_created_at, updated_at, is_deleted,
target_user_id, target_clinic_id
}
}}}
=== Functional dependencies (initial set)
User / specialization
{{{
FD1: user_id → username, email, name, surname, full_name, password_hash, user_created_at
FD2: client_id → user_id, is_blocked, blocked_at, blocked_reason
FD3: admin_id → user_id
FD4: owner_id → client_id 
}}}
Notification
{{{
FD5: notification_id → notification_type, notification_message, notification_created_at, is_read, user_id
}}}
Vet Clinic / application
{{{
FD6: clinic_id → clinic_name, clinic_email, clinic_phone, clinic_address, clinic_location, clinic_city
FD7: application_id → app_name, app_email, app_phone, app_city, app_address, submitted_at, app_status, reviewed_at, reviewed_by, denial_reason, clinic_id
}}}
Animal
{{{
FD8: animal_id → animal_name, located_name, species, animal_type, breed, sex, date_of_birth, photo_url, owner_id
}}}
Listing
{{{
FD9: listing_id → listing_status, listing_created_at, price, listing_description, animal_id, admin_id
}}}
Appointment
{{{
FD10: appointment_id → date_time, appointment_status, notes, animal_id, clinic_id
}}}
Health Record 
{{{
FD11: healthrecord_id → hr_type, hr_description, hr_date, animal_id, appointment_id
}}}
Review and subtypes
{{{
FD12: review_id → reviewer_id, rating, comment, review_created_at, updated_at, is_deleted
FD13: review_id → target_user_id 
FD14: review_id → target_clinic_id 
}}}
Favorite Listing
{{{
FD15: (saved_user_id, saved_listing_id) → / 
}}}
== Candidate keys and primary key selection
=== Attributes appearing only on the left side
{{{
notification_id, application_id, clinic_id, animal_id, listing_id, 
appointment_id, healthrecord_id, review_id, 
timestamp-like attributes are not keys here; saved is composite.
}}}
[[BR]]
Because these identifiers can’t be derived from other attributes, they must be included in any superkey of the fully de-normalized R.
[[BR]]
A valid superkey for R is:
{{{
K = {notification_id, application_id, clinic_id, animal_id, listing_id,
 appointment_id, healthrecord_id, review_id, saved_user_id, saved_listing_id}
}}}
Its closure includes all descriptive attributes via FD1–FD15, so it determines all attributes of R.
=== Chosen primary key
We select the minimal superkey built from "left-only identifiers":
[[BR]]
Primary key (of the initial de-normalized R):
[[BR]]
{{{
{notification_id, application_id, clinic_id, animal_id, listing_id, 
appointment_id, healthrecord_id, review_id, saved_user_id, saved_listing_id}
}}}
== Normal form checks
=== Check for 1NF
R satisfies 1NF (all attributes are atomic and each row is uniquely identified by the chosen primary key).
=== Check for 2NF
R does not satisfy 2NF, because many non-key attributes depend only on a part of the composite primary key:
* listing_id → price, listing_description, listing_status, …
* animal_id → species, breed, …
* clinic_id → clinic_name, …
* user_id → username, email, …
* review_id → rating, comment, …
* notification_id → message, type, …
So we decompose by grouping attributes by the key they depend on.
== Decomposition to 2NF
=== Grouping by determinants (partial dependencies)
* user_id → username, email, name, surname, full_name, password_hash, user_created_at
* client_id → user_id, is_blocked, blocked_at, blocked_reason
* admin_id → user_id
* owner_id → client_id
* notification_id → notification_type, notification_message, notification_created_at, is_read, user_id
* clinic_id → clinic_name, clinic_email, clinic_phone, clinic_address, clinic_location, clinic_city
* application_id → app_*, submitted_at, app_status, reviewed_at, reviewed_by, denial_reason, clinic_id
* animal_id → animal_*, owner_id
* listing_id → listing_*, animal_id, admin_id
* appointment_id → date_time, appointment_status, notes, animal_id, clinic_id
* healthrecord_id → hr_type, hr_description, hr_date, animal_id, appointment_id
* review_id → reviewer_id, rating, comment, review_created_at, updated_at, is_deleted
* review_id → target_user_id / target_clinic_id (subtype dependent)
* (saved_user_id, saved_listing_id) → /
=== 2NF relations(first decomposition)
* Users(user_id, username, email, name, surname, full_name, password_hash, user_created_at)
* Clients(client_id, user_id, is_blocked, blocked_at, blocked_reason)
* Admins(admin_id, user_id)
* Owners(owner_id, client_id)
* Notifications(notification_id, user_id, notification_type, notification_message, notification_created_at, is_read)
* VetClinics(clinic_id, clinic_name, clinic_email, clinic_phone, clinic_address, clinic_location, clinic_city)
* VetClinicApplications(application_id, clinic_id, app_name, app_email, app_phone, app_city, app_address, submitted_at, app_status, reviewed_at, reviewed_by, denial_reason)
* Animals(animal_id, owner_id, animal_name, located_name, species, animal_type, breed, sex, date_of_birth, photo_url)
* Listings(listing_id, admin_id, animal_id, listing_status, listing_created_at, price, listing_description)
* Appointments(appointment_id, clinic_id, animal_id, date_time, appointment_status, notes)
* HealthRecords(healthrecord_id, appointment_id, animal_id, hr_type, hr_description, hr_date)
* Reviews(review_id, reviewer_id, rating, comment, review_created_at, updated_at, is_deleted)
* FavoriteListings(saved_user_id, saved_listing_id)
{{{Lossless join:}}} each relation shares its determinant key(s) with the original R.
{{{Dependency preservation:}}} FD1–FD15 are preserved because each FD now lives entirely inside one of the new relations.
== Check for 3NF(and decomposition)
Now we look for transitive dependencies inside the produced relations.
=== Typical transitive issues from the ER
* In Clients(client_id, user_id, …) we have client_id → user_id and user_id → (email, username, …) but those user attributes are no longer in Clients, so this is already clean.
* In Listings(listing_id, admin_id, animal_id, …) we have admin_id → user_id (FD3) but user data is in Users, not in Listings, so no transitive dependency remains in Listings.
* In VetClinicApplications(application_id, clinic_id, …) we have clinic_id → clinic attributes, but clinic attributes are not stored there.
=== Review subtypes(User Review and Clinic Review)
The ER has specialization:
[[BR]]
 Review is generalized into UserReview and ClinicReview. If we keep target_user_id and target_clinic_id inside Reviews, we introduce subtype semantics and potential null-heavy design.
[[BR]]
So we decompose:
[[BR]]
{{{UserReviews}}}(review_id, target_user_id)
{{{ClinicReviews}}}(review_id, target_clinic_id)
Now:
* review_id determines the base review fields in Reviews (FD12)
* review_id determines subtype target in the subtype table (FD13/FD14)
=== Results after 3NF step
* UserReviews(review_id, target_user_id)
* ClinicReviews(review_id, target_clinic_id)
All relations are now in 3NF.
== Check for BCNF
For each relation, every non-trivial FD has a determinant that is a superkey:
* Users: user_id is key → BCNF
* Clients: client_id is key → BCNF
* Admins: admin_id is key → BCNF
* Owners: owner_id is key → BCNF
* Notifications: notification_id is key → BCNF
* VetClinics: clinic_id is key → BCNF
* VetClinicApplications: application_id is key → BCNF
* Animals: animal_id is key → BCNF
* Listings: listing_id is key → BCNF
* Appointments: appointment_id is key → BCNF
* HealthRecords: healthrecord_id is key → BCNF
* Reviews: review_id is key → BCNF
* UserReviews / ClinicReviews: review_id is key → BCNF
* FavoriteListings: (saved_user_id, saved_listing_id) is key → BCNF
{{{So the schema satisfies BCNF.}}}
== Check for 4NF(multivalued dependencies)
Potential 4NF risks usually come from independent multi-valued facts forced into one table. Here, we already separated M:N facts into their own relations:
* FavoriteListings(saved_user_id, saved_listing_id) is its own table
* Review subtypes are separate
* Appointments and HealthRecords are separate entities (no independent repeating groups inside them)
{{{So the schema satisfies 4NF.}}}
== Final relations
* Users(user_id, username, email, name, surname, full_name, password_hash, user_created_at)
* Clients(client_id, user_id, is_blocked, blocked_at, blocked_reason)
* Admins(admin_id, user_id)
* Owners(owner_id, client_id)

* Notifications(notification_id, user_id, notification_type, notification_message, notification_created_at, is_read)

* VetClinics(clinic_id, clinic_name, clinic_email, clinic_phone, clinic_address, clinic_location, clinic_city)

* VetClinicApplications(application_id, clinic_id, app_name, app_email, app_phone, app_city, app_address, submitted_at, app_status, reviewed_at, reviewed_by, denial_reason)

* Animals(animal_id, owner_id, animal_name, located_name, species, animal_type, breed, sex, date_of_birth, photo_url)

* Listings(listing_id, admin_id, animal_id, listing_status, listing_created_at, price, listing_description)

* FavoriteListings(saved_user_id, saved_listing_id)

* Appointments(appointment_id, clinic_id, animal_id, date_time, appointment_status, notes)

* HealthRecords(healthrecord_id, appointment_id, animal_id, hr_type, hr_description, hr_date)

* Reviews(review_id, reviewer_id, rating, comment, review_created_at, updated_at, is_deleted)
* UserReviews(review_id, target_user_id)
* ClinicReviews(review_id, target_clinic_id)

Business constraint:
A ClinicReview is allowed only if the reviewer has at least one Appointment at that clinic with status = 'DONE'.
== Conclusion
By decomposing the initial unified Petify relation using the functional dependencies derived from the ER model, we obtained a set of relations that are dependency-preserving, lossless-join, and satisfy BCNF (and effectively 4NF). This aligns with the fact that the ER model is already structured around proper entities and relationship tables (especially for saved/favorites and review subtypes).