Normalization

Initial de-normalized relation and functional dependencies

Global set of attributes

This represents the initial de-normalized relation before normalization is applied. Attribute names are made unique to avoid ambiguity.

R = {
user_id, username, user_email, user_name, user_surname, password_hash, user_created_at,

admin_user_id,
client_user_id, is_blocked, blocked_at, blocked_reason, blocked_by,
owner_user_id,

notification_id, notification_user_id, notification_type, notification_message, is_read, notification_created_at,

clinic_id, clinic_user_id, clinic_name, clinic_email, clinic_phone, clinic_location, clinic_city, clinic_address,
work_days, start_time, end_time,

application_id, application_clinic_id, app_name, app_email, app_phone, app_city, app_address,
submitted_at, app_status, reviewed_at, reviewed_by, denial_reason,

animal_id, animal_owner_id, animal_name, located_name, species, animal_type, breed, sex, date_of_birth, photo_url,

listing_id, listing_owner_id, listing_animal_id, listing_status, listing_created_at, price, listing_description,

favorite_user_id, favorite_listing_id,

appointment_id, appointment_clinic_id, appointment_animal_id, responsible_owner_id,
date_time, appointment_status, notes,

healthrecord_id, healthrecord_animal_id, healthrecord_appointment_id,
hr_type, hr_description, hr_date,

review_id, reviewer_id, rating, comment, review_created_at, updated_at, is_deleted,
target_user_id, target_clinic_id, review_appointment_id,

slot_id, slot_clinic_id, slot_date_time, slot_reason, slot_created_at
}

Functional dependencies

User / specialization

FD1: user_id -> username, user_email, user_name, user_surname, password_hash, user_created_at
FD2: admin_user_id -> user_id
FD3: client_user_id -> user_id, is_blocked, blocked_at, blocked_reason, blocked_by
FD4: owner_user_id -> client_user_id
FD5: clinic_id -> clinic_user_id, clinic_name, clinic_email, clinic_phone, clinic_location,
                 clinic_city, clinic_address, work_days, start_time, end_time

Notification

FD6: notification_id -> notification_user_id, notification_type, notification_message, is_read, notification_created_at

Vet Clinic application

FD7: application_id -> application_clinic_id, app_name, app_email, app_phone, app_city,
                       app_address, submitted_at, app_status, reviewed_at, reviewed_by, denial_reason

Animal

FD8: animal_id -> animal_owner_id, animal_name, located_name, species, animal_type,
                  breed, sex, date_of_birth, photo_url

Listing

FD9: listing_id -> listing_owner_id, listing_animal_id, listing_status,
                   listing_created_at, price, listing_description

Appointment

FD10: appointment_id -> appointment_clinic_id, appointment_animal_id,
                       responsible_owner_id, date_time, appointment_status, notes

Health Record

FD11: healthrecord_id -> healthrecord_animal_id, healthrecord_appointment_id,
                         hr_type, hr_description, hr_date

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, review_appointment_id

Clinic unavailable slots

FD15: slot_id -> slot_clinic_id, slot_date_time, slot_reason, slot_created_at

VetClinic is included in the User specialization because each clinic has a user account through clinic_user_id. Unlike Admin, Client, and Owner, it also keeps clinic_id as its main entity identifier, because appointments, applications, clinic reviews, and unavailable slots refer to clinics by clinic_id.

Favorite Listing

FavoriteListing has composite key (favorite_user_id, favorite_listing_id)
and no additional non-trivial functional dependencies.

Candidate keys and primary key selection

To determine a candidate key of the universal de-normalized relation, we identify a minimal set of identifiers whose closure determines all attributes in R. Some identifiers determine others through the functional dependencies. For example:

application_id -> application_clinic_id
listing_id -> listing_owner_id, listing_animal_id
healthrecord_id -> healthrecord_appointment_id, healthrecord_animal_id
appointment_id -> appointment_clinic_id, appointment_animal_id, responsible_owner_id
review_id -> reviewer_id and, depending on subtype, target_user_id or target_clinic_id / review_appointment_id
slot_id -> slot_clinic_id

A candidate key for the initial de-normalized relation is:

K = {
notification_id,
application_id,
listing_id,
healthrecord_id,
review_id,
favorite_user_id,
favorite_listing_id,
slot_id
}

Closure proof for K

Start:
K+ = {notification_id, application_id, listing_id, healthrecord_id, review_id,
      favorite_user_id, favorite_listing_id, slot_id}

From FD6:
notification_id -> notification_user_id, notification_type, notification_message, is_read, notification_created_at

From FD1 using notification_user_id:
notification_user_id -> username, user_email, user_name, user_surname, password_hash, user_created_at

From FD7:
application_id -> application_clinic_id, app_name, app_email, app_phone, app_city,
                  app_address, submitted_at, app_status, reviewed_at, reviewed_by, denial_reason

From FD5 using application_clinic_id:
clinic_id -> clinic_user_id, clinic_name, clinic_email, clinic_phone, clinic_location,
             clinic_city, clinic_address, work_days, start_time, end_time

From FD9:
listing_id -> listing_owner_id, listing_animal_id, listing_status,
              listing_created_at, price, listing_description

From FD8 using listing_animal_id:
animal_id -> animal_owner_id, animal_name, located_name, species, animal_type,
             breed, sex, date_of_birth, photo_url

From FD4 using listing_owner_id:
owner_user_id -> client_user_id

From FD3 using client_user_id:
client_user_id -> user_id, is_blocked, blocked_at, blocked_reason, blocked_by

From FD11:
healthrecord_id -> healthrecord_animal_id, healthrecord_appointment_id,
                   hr_type, hr_description, hr_date

From FD10 using healthrecord_appointment_id:
appointment_id -> appointment_clinic_id, appointment_animal_id,
                  responsible_owner_id, date_time, appointment_status, notes

From FD12:
review_id -> reviewer_id, rating, comment, review_created_at, updated_at, is_deleted

From FD13 / FD14:
review_id -> target_user_id, target_clinic_id, review_appointment_id

From FD15:
slot_id -> slot_clinic_id, slot_date_time, slot_reason, slot_created_at

Therefore, K+ determines all attributes of R, so K is a superkey.

Proof that there are no other candidate keys

To promote K from only "a candidate key" to the chosen primary key, we also need to show that no other candidate key exists.

In the given set of functional dependencies, the following identifiers represent independent components of the universal relation:

notification_id,
application_id,
listing_id,
healthrecord_id,
review_id,
favorite_user_id,
favorite_listing_id,
slot_id

None of these attributes is functionally determined by another determinant in the given FD set:

• notification_id does not appear on the right-hand side of any FD, so it cannot be derived.
• application_id does not appear on the right-hand side of any FD, so it cannot be derived.
• listing_id does not appear on the right-hand side of any FD, so it cannot be derived.
• healthrecord_id does not appear on the right-hand side of any FD, so it cannot be derived.
• review_id does not appear on the right-hand side of any FD, so it cannot be derived.
• favorite_user_id and favorite_listing_id identify the saved_by relationship. Since FavoriteListing has no non-trivial FD that derives either component, both are required.
• slot_id does not appear on the right-hand side of any FD, so it cannot be derived.

Therefore every superkey of R must contain all attributes in:

E = {notification_id, application_id, listing_id, healthrecord_id, review_id,
     favorite_user_id, favorite_listing_id, slot_id}

Since K = E and K+ = R, K is a superkey. Since every superkey must contain E, no smaller key can exist. Also, no different candidate key can be formed by replacing one of these attributes with another attribute, because none of the required attributes in E can be derived from any other FD determinant.

Thus K is the only candidate key of the initial de-normalized relation under the stated functional dependencies.

The chosen primary key of the initial de-normalized relation is:

{notification_id, application_id, listing_id, healthrecord_id, review_id,
 favorite_user_id, favorite_listing_id, slot_id}

Normal form checks

1NF

R satisfies 1NF because all attributes are atomic and there are no repeating groups.

2NF

R does not satisfy 2NF because it has a composite key and many non-key attributes depend on only part of that key.

Examples:

• notification_id -> notification_user_id, notification_type, notification_message, is_read, notification_created_at
• listing_id -> listing_owner_id, listing_animal_id, listing_status, listing_created_at, price, listing_description
• clinic_id -> clinic_user_id, clinic_name, clinic_email, clinic_phone, clinic_location, clinic_city, clinic_address
• review_id -> reviewer_id, rating, comment, review_created_at, updated_at, is_deleted
• slot_id -> slot_clinic_id, slot_date_time, slot_reason, slot_created_at

These are partial dependencies on components of the composite key, so R violates 2NF.

Decomposition to 2NF

Grouping by determinants

• user_id -> username, user_email, user_name, user_surname, password_hash, user_created_at
• admin_user_id -> user_id
• client_user_id -> user_id, is_blocked, blocked_at, blocked_reason, blocked_by
• owner_user_id -> client_user_id
• clinic_id -> clinic_user_id, clinic_name, clinic_email, clinic_phone, clinic_location, clinic_city, clinic_address, work_days, start_time, end_time
• notification_id -> notification_user_id, notification_type, notification_message, is_read, notification_created_at
• application_id -> application_clinic_id, app_name, app_email, app_phone, app_city, app_address, submitted_at, app_status, reviewed_at, reviewed_by, denial_reason
• animal_id -> animal_owner_id, animal_name, located_name, species, animal_type, breed, sex, date_of_birth, photo_url
• listing_id -> listing_owner_id, listing_animal_id, listing_status, listing_created_at, price, listing_description
• appointment_id -> appointment_clinic_id, appointment_animal_id, responsible_owner_id, date_time, appointment_status, notes
• healthrecord_id -> healthrecord_animal_id, healthrecord_appointment_id, hr_type, hr_description, hr_date
• review_id -> reviewer_id, rating, comment, review_created_at, updated_at, is_deleted
• review_id -> target_user_id / target_clinic_id, review_appointment_id
• slot_id -> slot_clinic_id, slot_date_time, slot_reason, slot_created_at
• FavoriteListing has key (favorite_user_id, favorite_listing_id) only

2NF relations

• Users(user_id, username, email, name, surname, password_hash, created_at)
• Admins(user_id)
• Clients(user_id, is_blocked, blocked_at, blocked_reason, blocked_by)
• Owners(user_id)
• VetClinics(clinic_id, user_id, name, email, phone, location, city, address, work_days, start_time, end_time)
• Notifications(notification_id, user_id, type, message, is_read, created_at)
• VetClinicApplications(application_id, clinic_id, name, email, phone, city, address, submitted_at, status, reviewed_at, reviewed_by, denial_reason)
• Animals(animal_id, owner_id, name, located_name, species, type, breed, sex, date_of_birth, photo_url)
• Listings(listing_id, owner_id, animal_id, status, created_at, price, description)
• FavoriteListings(user_id, listing_id)
• Appointments(appointment_id, clinic_id, animal_id, responsible_owner_id, date_time, status, notes)
• HealthRecords(healthrecord_id, animal_id, appointment_id, type, description, date)
• Reviews(review_id, reviewer_id, rating, comment, created_at, updated_at, is_deleted)
• UserReviews(review_id, target_user_id, interaction_type)
• ClinicReviews(review_id, target_clinic_id, appointment_id)
• ClinicUnavailableSlots(slot_id, clinic_id, date_time, reason, created_at)

Lossless join:
We use the binary lossless decomposition criterion: For a decomposition of relation R into R1 and R2, the decomposition is lossless if:

(R1 ∩ R2) -> R1
or
(R1 ∩ R2) -> R2

The 2NF decomposition can be seen as repeated binary decompositions. At each step, a relation is split off using a determinant X, and X remains in the remaining relation as the join attribute. Since X functionally determines all attributes in the new relation, the join is lossless.

Step-by-step:

1. Split Users(user_id, username, email, name, surname, password_hash, created_at)
   Intersection with remaining relation: user_id
   FD1 gives user_id -> Users
   Therefore the join is lossless.

2. Split Admins(user_id)
   Intersection with remaining relation: user_id
   user_id is the key of Admins
   Therefore the join is lossless.

3. Split Clients(user_id, is_blocked, blocked_at, blocked_reason, blocked_by)
   Intersection with remaining relation: user_id
   FD3 gives client_user_id -> client attributes, and in the final schema client_user_id is represented as user_id
   Therefore the join is lossless.

4. Split Owners(user_id)
   Intersection with remaining relation: user_id
   user_id is the key of Owners
   Therefore the join is lossless.

5. Split VetClinics(clinic_id, user_id, name, email, phone, location, city, address, work_days, start_time, end_time)
   Intersection with remaining relation: clinic_id
   FD5 gives clinic_id -> VetClinics
   Therefore the join is lossless.

6. Split Notifications(notification_id, user_id, type, message, is_read, created_at)
   Intersection with remaining relation: notification_id
   FD6 gives notification_id -> Notifications
   Therefore the join is lossless.

7. Split VetClinicApplications(application_id, clinic_id, name, email, phone, city, address, submitted_at, status, reviewed_at, reviewed_by, denial_reason)
   Intersection with remaining relation: application_id
   FD7 gives application_id -> VetClinicApplications
   Therefore the join is lossless.

8. Split Animals(animal_id, owner_id, name, located_name, species, type, breed, sex, date_of_birth, photo_url)
   Intersection with remaining relation: animal_id
   FD8 gives animal_id -> Animals
   Therefore the join is lossless.

9. Split Listings(listing_id, owner_id, animal_id, status, created_at, price, description)
   Intersection with remaining relation: listing_id
   FD9 gives listing_id -> Listings
   Therefore the join is lossless.

10. Split FavoriteListings(user_id, listing_id)
    Intersection with remaining relation: user_id, listing_id
    (user_id, listing_id) is the composite key of FavoriteListings
    Therefore the join is lossless.

11. Split Appointments(appointment_id, clinic_id, animal_id, responsible_owner_id, date_time, status, notes)
    Intersection with remaining relation: appointment_id
    FD10 gives appointment_id -> Appointments
    Therefore the join is lossless.

12. Split HealthRecords(healthrecord_id, animal_id, appointment_id, type, description, date)
    Intersection with remaining relation: healthrecord_id
    FD11 gives healthrecord_id -> HealthRecords
    Therefore the join is lossless.

13. Split Reviews(review_id, reviewer_id, rating, comment, created_at, updated_at, is_deleted)
    Intersection with remaining relation: review_id
    FD12 gives review_id -> Reviews
    Therefore the join is lossless.

14. Split UserReviews(review_id, target_user_id, interaction_type)
    Intersection with Reviews: review_id
    review_id is the key of UserReviews and also references Reviews(review_id)
    Therefore joining Reviews with UserReviews is lossless for user-review tuples.

15. Split ClinicReviews(review_id, target_clinic_id, appointment_id)
    Intersection with Reviews: review_id
    review_id is the key of ClinicReviews and also references Reviews(review_id)
    Therefore joining Reviews with ClinicReviews is lossless for clinic-review tuples.

16. Split ClinicUnavailableSlots(slot_id, clinic_id, date_time, reason, created_at)
    Intersection with remaining relation: slot_id
    FD15 gives slot_id -> ClinicUnavailableSlots
    Therefore the join is lossless.

Because every decomposition step satisfies the binary lossless decomposition criterion, the whole decomposition is lossless by induction: after each split, the remaining relation can be joined back with the new relation without producing spurious tuples or losing tuples.

Dependency preservation:
The decomposition is dependency-preserving because the functional dependencies FD1-FD15 are represented inside the resulting relations. FavoriteListings has only its composite key and no additional non-trivial dependencies.

Check for 3NF

The decomposed relations remove the transitive dependencies from the universal relation.

Examples:

• VetClinicApplications stores clinic_id, but clinic attributes stay in VetClinics.
• Listings stores owner_id and animal_id, but owner and animal descriptive attributes stay in Owners and Animals.
• Appointments stores clinic_id, animal_id, and responsible_owner_id, but descriptive attributes stay in their own relations.
• ClinicReviews stores appointment_id, but appointment details stay in Appointments.

Review subtypes

The ER model contains a specialization of Review into UserReview and ClinicReview. Subtype-specific attributes are separated:

UserReviews(review_id, target_user_id, interaction_type)
ClinicReviews(review_id, target_clinic_id, appointment_id)

Reviews stores common review attributes. UserReviews stores the reviewed user and interaction type. ClinicReviews stores the reviewed clinic and the appointment on which the clinic review is based.

The business rule "a user may review a clinic only if they have at least one DONE appointment at that clinic" is enforced by a trigger or assertion-like check, not by normalization alone.

After separating subtype-specific attributes, the resulting relations satisfy 3NF.

Check for BCNF

Each resulting relation is in BCNF because every non-trivial functional dependency inside a relation has a determinant that is a superkey of that relation.

• Users: user_id is key -> BCNF
• Admins: user_id is key -> BCNF
• Clients: user_id is key -> BCNF
• Owners: user_id is key -> BCNF
• VetClinics: clinic_id is key, and user_id is unique -> BCNF
• Notifications: notification_id is key -> BCNF
• VetClinicApplications: application_id is key, and clinic_id is unique -> BCNF
• Animals: animal_id is key -> BCNF
• Listings: listing_id is key -> BCNF
• FavoriteListings: (user_id, listing_id) is key -> BCNF
• Appointments: appointment_id is key -> BCNF
• HealthRecords: healthrecord_id is key -> BCNF
• Reviews: review_id is key -> BCNF
• UserReviews: review_id is key -> BCNF
• ClinicReviews: review_id is key -> BCNF
• ClinicUnavailableSlots: slot_id is key -> BCNF

So the schema satisfies BCNF.

Check for 4NF

No additional non-trivial multivalued dependencies are identified in the resulting schema. The many-to-many relationship FavoriteListing is already decomposed into FavoriteListings(user_id, listing_id), so no further decomposition is required for 4NF.

The schema satisfies 4NF.

Final relations

• Users(user_id, username, email, name, surname, password_hash, created_at)
• Admins(user_id)
• Clients(user_id, is_blocked, blocked_at, blocked_reason, blocked_by)
• Owners(user_id)
• VetClinics(clinic_id, user_id, name, email, phone, location, city, address, work_days, start_time, end_time)
• Notifications(notification_id, user_id, type, message, is_read, created_at)
• VetClinicApplications(application_id, clinic_id, name, email, phone, city, address, submitted_at, status, reviewed_at, reviewed_by, denial_reason)
• Animals(animal_id, owner_id, name, sex, date_of_birth, photo_url, type, species, breed, located_name)
• Listings(listing_id, owner_id, animal_id, status, price, description, created_at)
• FavoriteListings(user_id, listing_id)
• Appointments(appointment_id, clinic_id, animal_id, responsible_owner_id, status, date_time, notes)
• HealthRecords(healthrecord_id, animal_id, appointment_id, type, description, date)
• Reviews(review_id, reviewer_id, rating, comment, created_at, updated_at, is_deleted)
• UserReviews(review_id, target_user_id, interaction_type)
• ClinicReviews(review_id, target_clinic_id, appointment_id)
• ClinicUnavailableSlots(slot_id, clinic_id, date_time, reason, created_at)

Conclusion

The normalization process confirms the same structural design obtained from the ER model in phase 2.