Changes between Version 2 and Version 3 of Normalization

Timestamp:

03/02/26 16:38:58 (3 weeks ago)

Author:

231035

Comment:

--

Normalization

@@ -1 +1 @@
 = Normalization
-== Initial De-Normalized Relation and Functional Dependencies
-=== Unified De-Normalized Relation
+== 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.
-{{{
-R(
-user_id, username, user_email, password_hash, fullname, name, surname, user_created_at,
-blocked_reason, blocked_at, is_blocked,
-
-animal_id, animal_name, species, breed, sex, date_of_birth, photo_url, owner_id,
-
-listing_id, listing_status, listing_price, listing_description, listing_created_at,
-
-appointment_id, appointment_date_time, appointment_status, appointment_notes,
-
-clinic_id, clinic_name, clinic_address, clinic_location, clinic_city,
-clinic_email, clinic_phone,
-
-application_id, application_name, application_email, application_phone,
-application_city, application_address, submitted_at, reviewed_at,
-reviewed_by, application_status, denial_reason,
-
-healthrecord_id, health_type, health_description, health_date,
-
-review_id, rating, review_comment, review_created_at,
-review_updated_at, is_deleted, reviewer_id,
-target_user_id, target_clinic_id,
-
-notification_id, notification_type, notification_message,
-notification_created_at, is_read,
-
-favorite_user_id, favorite_listing_id
-)
-}}}
-=== Initial Set of Functional Dependencies
-User dependencies
-{{{
-user_id → username
-user_id → user_email
-user_id → password_hash
-user_id → fullname
-user_id → name
-user_id → surname
-user_id → user_created_at
-user_id → blocked_reason
-user_id → blocked_at
-user_id → is_blocked
-}}}
-Animal dependencies
-{{{
-animal_id → animal_name
-animal_id → species
-animal_id → breed
-animal_id → sex
-animal_id → date_of_birth
-animal_id → photo_url
-animal_id → owner_id
-}}}
-Listing dependencies
-{{{
-listing_id → listing_status
-listing_id → listing_price
-listing_id → listing_description
-listing_id → listing_created_at
-listing_id → animal_id
-}}}
-Appointment dependencies
-{{{
-appointment_id → appointment_date_time
-appointment_id → appointment_status
-appointment_id → appointment_notes
-appointment_id → animal_id
-appointment_id → clinic_id
-}}}
-Vet Clinic dependencies
-{{{
-clinic_id → clinic_name
-clinic_id → clinic_address
-clinic_id → clinic_location
-clinic_id → clinic_city
-clinic_id → clinic_email
-clinic_id → clinic_phone
-}}}
-Vet Clinic Application dependencies
-{{{
-application_id → application_name
-application_id → application_email
-application_id → application_phone
-application_id → application_city
-application_id → application_address
-application_id → submitted_at
-application_id → reviewed_at
-application_id → reviewed_by
-application_id → application_status
-application_id → denial_reason
-}}}
-Health Record dependencies
-{{{
-healthrecord_id → health_type
-healthrecord_id → health_description
-healthrecord_id → health_date
-healthrecord_id → animal_id
-}}}
-Review dependencies
-{{{
-review_id → rating
-review_id → review_comment
-review_id → review_created_at
-review_id → review_updated_at
-review_id → is_deleted
-review_id → reviewer_id
-review_id → target_user_id
-review_id → target_clinic_id
-}}}
-Notification dependencies
-{{{
-notification_id → notification_type
-notification_id → notification_message
-notification_id → notification_created_at
-notification_id → is_read
-notification_id → user_id
-}}}
-Fav listing dependencies
-{{{
-(favorite_user_id, favorite_listing_id) → ∅
-}}}
-== Candidate Keys and Primary Key Selection
+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).