= 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. 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. 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]] 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. {{{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.