Changes between Version 3 and Version 4 of Normalization
- Timestamp:
- 03/05/26 16:13:18 (2 weeks ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
Normalization
v3 v4 3 3 === Global set of attributes (single de-normalized relation) 4 4 This represents the initial de-normalized relation before any normalization is applied. 5 To avoid duplicate attribute names, role-based user references are renamed.5 Attribute names are made unique to avoid duplication. 6 6 {{{ 7 7 R = { 8 -- User / roles9 8 user_id, username, email, name, surname, full_name, password_hash, user_created_at, 10 9 client_id, is_blocked, blocked_at, blocked_reason, … … 12 11 owner_id, 13 12 14 -- Notification15 13 notification_id, notification_type, notification_message, notification_created_at, is_read, 16 14 17 -- Animal18 15 animal_id, animal_name, located_name, species, animal_type, breed, sex, date_of_birth, photo_url, 19 16 20 -- Listing21 17 listing_id, listing_status, listing_created_at, price, listing_description, 22 18 23 -- Favorite listing 24 (saved relationship has no separate id in the ER, so it is represented by keys) saved_user_id, saved_listing_id, 25 26 -- Vet clinic 19 saved_user_id, saved_listing_id, 20 27 21 clinic_id, clinic_name, clinic_email, clinic_phone, clinic_address, clinic_location, clinic_city, 28 22 29 -- Vet clinic application30 23 application_id, app_name, app_email, app_phone, app_city, app_address, submitted_at, app_status, reviewed_at, reviewed_by, denial_reason, 31 24 32 -- Appointment33 25 appointment_id, date_time, appointment_status, notes, 34 26 35 -- Health record36 27 healthrecord_id, hr_type, hr_description, hr_date, 37 28 38 -- Review (and subtypes)39 29 review_id, reviewer_id, rating, comment, review_created_at, updated_at, is_deleted, 40 30 target_user_id, target_clinic_id … … 82 72 Favorite Listing 83 73 {{{ 84 FD15: (saved_user_id, saved_listing_id) → /74 FD15: (saved_user_id, saved_listing_id) → ∅ 85 75 }}} 86 76 == Candidate keys and primary key selection 87 77 === Attributes appearing only on the left side 88 78 {{{ 89 notification_id, application_id, clinic_id, animal_id, listing_id, 90 appointment_id, healthrecord_id, review_id, 91 timestamp-like attributes are not keys here; saved is composite. 79 user_id, client_id, admin_id, owner_id, 80 notification_id, clinic_id, application_id, 81 animal_id, listing_id, appointment_id, healthrecord_id, review_id, 82 (saved_user_id, saved_listing_id) 92 83 }}} 93 84 [[BR]] … … 100 91 }}} 101 92 Its closure includes all descriptive attributes via FD1–FD15, so it determines all attributes of R. 93 === Closure proof for K 94 {{{ 95 Let K = {notification_id, application_id, clinic_id, animal_id, listing_id, 96 appointment_id, healthrecord_id, review_id, saved_user_id, saved_listing_id} 97 98 Compute K+: 99 Start: K+ = K 100 101 From FD5: 102 notification_id → notification_type, notification_message, notification_created_at, is_read, user_id 103 Add: notification_type, notification_message, notification_created_at, is_read, user_id 104 105 From FD1: 106 user_id → username, email, name, surname, full_name, password_hash, user_created_at 107 Add: username, email, name, surname, full_name, password_hash, user_created_at 108 109 From FD9: 110 listing_id → listing_status, listing_created_at, price, listing_description, animal_id, admin_id 111 Add: listing_status, listing_created_at, price, listing_description, admin_id 112 (animal_id already in K) 113 114 From FD3: 115 admin_id → user_id (already in K+) 116 117 From FD8: 118 animal_id → animal_name, located_name, species, animal_type, breed, sex, date_of_birth, photo_url, owner_id 119 Add: animal_name, located_name, species, animal_type, breed, sex, date_of_birth, photo_url, owner_id 120 121 From FD4: 122 owner_id → client_id 123 Add: client_id 124 125 From FD2: 126 client_id → user_id, is_blocked, blocked_at, blocked_reason 127 Add: is_blocked, blocked_at, blocked_reason (user_id already in K+) 128 129 From FD6: 130 clinic_id → clinic_name, clinic_email, clinic_phone, clinic_address, clinic_location, clinic_city 131 Add: clinic_name, clinic_email, clinic_phone, clinic_address, clinic_location, clinic_city 132 133 From FD7: 134 application_id → app_name, app_email, app_phone, app_city, app_address, submitted_at, app_status, 135 reviewed_at, reviewed_by, denial_reason, clinic_id 136 Add: app_name, app_email, app_phone, app_city, app_address, submitted_at, app_status, 137 reviewed_at, reviewed_by, denial_reason (clinic_id already in K+) 138 139 From FD10: 140 appointment_id → date_time, appointment_status, notes, animal_id, clinic_id 141 Add: date_time, appointment_status, notes (animal_id, clinic_id already in K+) 142 143 From FD11: 144 healthrecord_id → hr_type, hr_description, hr_date, animal_id, appointment_id 145 Add: hr_type, hr_description, hr_date 146 147 From FD12: 148 review_id → reviewer_id, rating, comment, review_created_at, updated_at, is_deleted 149 Add: reviewer_id, rating, comment, review_created_at, updated_at, is_deleted 150 151 From FD13 and FD14: 152 review_id → target_user_id, target_clinic_id 153 Add: target_user_id, target_clinic_id 154 155 FD15 adds no attributes. 156 157 Therefore K+ = R, so K is a superkey. 158 [[BR]] 159 K is also a minimal and a candidate key. 160 }}} 102 161 === Chosen primary key 103 162 We select the minimal superkey built from "left-only identifiers": … … 151 210 * Reviews(review_id, reviewer_id, rating, comment, review_created_at, updated_at, is_deleted) 152 211 * FavoriteListings(saved_user_id, saved_listing_id) 153 {{{Lossless join:}}} each relation shares its determinant key(s) with the original R. 212 {{{Lossless join:}}} 213 [[BR]] 214 For each decomposition R → R1, R2, the intersection (R1 ∩ R2) is a key of at least one of the relations(user_id is key of Users etc.).So each decomposition step is lossless. 154 215 {{{Dependency preservation:}}} FD1–FD15 are preserved because each FD now lives entirely inside one of the new relations. 155 216 == Check for 3NF(and decomposition) … … 193 254 {{{So the schema satisfies BCNF.}}} 194 255 == Check for 4NF(multivalued dependencies) 195 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: 196 * FavoriteListings(saved_user_id, saved_listing_id) is its own table 197 * Review subtypes are separate 198 * Appointments and HealthRecords are separate entities (no independent repeating groups inside them) 199 {{{So the schema satisfies 4NF.}}} 256 No additional multivalued dependencies are identified beyond the M:N relationship FavoriteListing,which is already represented as its own relation FavoriteListings(saved_user_id, saved_listing_id). So no further 4NF decomposition is required. 257 {{{The schema satisfies 4NF.}}} 200 258 == Final relations 201 259 * Users(user_id, username, email, name, surname, full_name, password_hash, user_created_at) … … 227 285 A ClinicReview is allowed only if the reviewer has at least one Appointment at that clinic with status = 'DONE'. 228 286 == Conclusion 229 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). 287 The final normalized relational design matches the Phase 2 ER-based design: 288 * Each entity becomes a relation. 289 * Specializations are represented as subtype relations. 290 * The M:N relationship FavoriteListing is represented as a junction relation FavoriteListings. 291 Therefore, there are no structural differences from Phase 2.
