Changes between Version 7 and Version 8 of Normalization

Timestamp:

08/22/26 19:11:29 (8 hours ago)

Author:

231035

Comment:

--

Normalization

@@ -189 +189 @@
 }}}
 
-Therefore, K+ determines all attributes of R, so K is a superkey. It is minimal because removing any identifier loses one independent component of the universal relation: notification, application, listing, health record, review, favorite pair, or unavailable slot.
+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}
@@ -253 +288 @@
 {{{Lossless join:}}}
 [[BR]]
-The decomposition is lossless because each decomposed relation is formed around a determinant that becomes the key of that relation. Foreign keys preserve the relationships between the decomposed relations.
+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:}}}