= 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:}}} [[BR]] 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:}}} [[BR]] 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.