Changes between Version 6 and Version 7 of Normalization


Ignore:
Timestamp:
08/22/26 18:28:18 (8 hours ago)
Author:
231035
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • Normalization

    v6 v7  
    44
    55=== Global set of attributes
    6 This represents the initial de-normalized relation before any normalization is applied.
    7 Attribute names are made unique to avoid duplication.
     6This represents the initial de-normalized relation before normalization is applied.
     7Attribute names are made unique to avoid ambiguity.
    88{{{
    99R = {
    10 user_id, username, email, name, surname, full_name, password_hash, user_created_at,
    11 client_id, is_blocked, blocked_at, blocked_reason,
    12 admin_id,
    13 owner_id,
    14 
    15 notification_id, notification_type, notification_message, notification_created_at, is_read,
    16 
    17 animal_id, animal_name, located_name, species, animal_type, breed, sex, date_of_birth, photo_url,
    18 
    19 listing_id, listing_status, listing_created_at, price, listing_description,
    20 
    21 saved_user_id, saved_listing_id,
    22 
    23 clinic_id, clinic_name, clinic_email, clinic_phone, clinic_address, clinic_location, clinic_city,
    24 
    25 application_id, app_name, app_email, app_phone, app_city, app_address, submitted_at, app_status, reviewed_at, reviewed_by, denial_reason,
    26 
    27 appointment_id, date_time, appointment_status, notes,
    28 
    29 healthrecord_id, hr_type, hr_description, hr_date,
     10user_id, username, user_email, user_name, user_surname, password_hash, user_created_at,
     11
     12admin_user_id,
     13client_user_id, is_blocked, blocked_at, blocked_reason, blocked_by,
     14owner_user_id,
     15
     16notification_id, notification_user_id, notification_type, notification_message, is_read, notification_created_at,
     17
     18clinic_id, clinic_user_id, clinic_name, clinic_email, clinic_phone, clinic_location, clinic_city, clinic_address,
     19work_days, start_time, end_time,
     20
     21application_id, application_clinic_id, app_name, app_email, app_phone, app_city, app_address,
     22submitted_at, app_status, reviewed_at, reviewed_by, denial_reason,
     23
     24animal_id, animal_owner_id, animal_name, located_name, species, animal_type, breed, sex, date_of_birth, photo_url,
     25
     26listing_id, listing_owner_id, listing_animal_id, listing_status, listing_created_at, price, listing_description,
     27
     28favorite_user_id, favorite_listing_id,
     29
     30appointment_id, appointment_clinic_id, appointment_animal_id, responsible_owner_id,
     31date_time, appointment_status, notes,
     32
     33healthrecord_id, healthrecord_animal_id, healthrecord_appointment_id,
     34hr_type, hr_description, hr_date,
    3035
    3136review_id, reviewer_id, rating, comment, review_created_at, updated_at, is_deleted,
    32 target_user_id, target_clinic_id
     37target_user_id, target_clinic_id, review_appointment_id,
     38
     39slot_id, slot_clinic_id, slot_date_time, slot_reason, slot_created_at
    3340}
    3441}}}
    3542
    36 === Functional dependencies (initial set)
     43=== Functional dependencies
    3744
    3845User / specialization
    3946{{{
    40 FD1: user_id → username, email, name, surname, full_name, password_hash, user_created_at
    41 FD2: client_id → user_id, is_blocked, blocked_at, blocked_reason
    42 FD3: admin_id → user_id
    43 FD4: owner_id → client_id
     47FD1: user_id -> username, user_email, user_name, user_surname, password_hash, user_created_at
     48FD2: admin_user_id -> user_id
     49FD3: client_user_id -> user_id, is_blocked, blocked_at, blocked_reason, blocked_by
     50FD4: owner_user_id -> client_user_id
     51FD5: clinic_id -> clinic_user_id, clinic_name, clinic_email, clinic_phone, clinic_location,
     52                 clinic_city, clinic_address, work_days, start_time, end_time
    4453}}}
    4554
    4655Notification
    4756{{{
    48 FD5: notification_id → notification_type, notification_message, notification_created_at, is_read, user_id
    49 }}}
    50 
    51 Vet Clinic / application
    52 {{{
    53 FD6: clinic_id → clinic_name, clinic_email, clinic_phone, clinic_address, clinic_location, clinic_city
    54 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
     57FD6: notification_id -> notification_user_id, notification_type, notification_message, is_read, notification_created_at
     58}}}
     59
     60Vet Clinic application
     61{{{
     62FD7: application_id -> application_clinic_id, app_name, app_email, app_phone, app_city,
     63                       app_address, submitted_at, app_status, reviewed_at, reviewed_by, denial_reason
    5564}}}
    5665
    5766Animal
    5867{{{
    59 FD8: animal_id → animal_name, located_name, species, animal_type, breed, sex, date_of_birth, photo_url, owner_id
     68FD8: animal_id -> animal_owner_id, animal_name, located_name, species, animal_type,
     69                  breed, sex, date_of_birth, photo_url
    6070}}}
    6171
    6272Listing
    6373{{{
    64 FD9: listing_id → listing_status, listing_created_at, price, listing_description, animal_id, admin_id
     74FD9: listing_id -> listing_owner_id, listing_animal_id, listing_status,
     75                   listing_created_at, price, listing_description
    6576}}}
    6677
    6778Appointment
    6879{{{
    69 FD10: appointment_id → date_time, appointment_status, notes, animal_id, clinic_id
     80FD10: appointment_id -> appointment_clinic_id, appointment_animal_id,
     81                       responsible_owner_id, date_time, appointment_status, notes
    7082}}}
    7183
    7284Health Record
    7385{{{
    74 FD11: healthrecord_id → hr_type, hr_description, hr_date, animal_id, appointment_id
     86FD11: healthrecord_id -> healthrecord_animal_id, healthrecord_appointment_id,
     87                         hr_type, hr_description, hr_date
    7588}}}
    7689
    7790Review and subtypes
    7891{{{
    79 FD12: review_id → reviewer_id, rating, comment, review_created_at, updated_at, is_deleted
    80 FD13: review_id → target_user_id
    81 FD14: review_id → target_clinic_id
    82 }}}
     92FD12: review_id -> reviewer_id, rating, comment, review_created_at, updated_at, is_deleted
     93FD13: review_id -> target_user_id
     94FD14: review_id -> target_clinic_id, review_appointment_id
     95}}}
     96
     97Clinic unavailable slots
     98{{{
     99FD15: slot_id -> slot_clinic_id, slot_date_time, slot_reason, slot_created_at
     100}}}
     101
     102VetClinic is included in the User specialization because each clinic has a user account through clinic_user_id.
     103Unlike Admin, Client, and Owner, it also keeps clinic_id as its main entity identifier, because appointments,
     104applications, clinic reviews, and unavailable slots refer to clinics by clinic_id.
    83105
    84106Favorite Listing
    85107{{{
    86 The relation FavoriteListings has composite key (saved_user_id, saved_listing_id)
     108FavoriteListing has composite key (favorite_user_id, favorite_listing_id)
    87109and no additional non-trivial functional dependencies.
    88110}}}
    89111
    90112== Candidate keys and primary key selection
    91 === Determining a candidate key
    92 To determine a candidate key of the universal de-normalized relation, we must identify a minimal set of identifiers whose closure determines all attributes in R.
    93 {{{#!comment It is not correct to simply collect all identifiers, because some identifiers are functionally determined by others. For example:
    94 application_id → clinic_id
    95 listing_id → animal_id, admin_id
    96 animal_id → owner_id
    97 owner_id → client_id
    98 healthrecord_id → appointment_id, animal_id
    99 appointment_id → clinic_id, animal_id
    100 
    101 
    102 Therefore, attributes such as clinic_id, animal_id, appointment_id, admin_id, owner_id, and client_id do not need to appear explicitly in the candidate key if they can already be derived from other identifiers.
     113
     114To determine a candidate key of the universal de-normalized relation, we identify a minimal set of identifiers whose closure determines all attributes in R.
     115Some identifiers determine others through the functional dependencies. For example:
     116{{{
     117application_id -> application_clinic_id
     118listing_id -> listing_owner_id, listing_animal_id
     119healthrecord_id -> healthrecord_appointment_id, healthrecord_animal_id
     120appointment_id -> appointment_clinic_id, appointment_animal_id, responsible_owner_id
     121review_id -> reviewer_id and, depending on subtype, target_user_id or target_clinic_id / review_appointment_id
     122slot_id -> slot_clinic_id
    103123}}}
    104124
    105125A candidate key for the initial de-normalized relation is:
    106 
    107 {{{
    108 K = {notification_id, application_id, listing_id, healthrecord_id, review_id, saved_user_id, saved_listing_id}
    109 }}}
    110 
    111 Its closure includes all attributes of R, so it determines the whole relation.
     126{{{
     127K = {
     128notification_id,
     129application_id,
     130listing_id,
     131healthrecord_id,
     132review_id,
     133favorite_user_id,
     134favorite_listing_id,
     135slot_id
     136}
     137}}}
    112138
    113139=== Closure proof for K
    114140{{{
    115 Let K = {notification_id, application_id, listing_id, healthrecord_id, review_id, saved_user_id, saved_listing_id}
    116 
    117 Compute K+:
    118 Start: K+ = K
    119 
    120 From FD5:
    121 notification_id → notification_type, notification_message, notification_created_at, is_read, user_id
    122 Add: notification_type, notification_message, notification_created_at, is_read, user_id
    123 
    124 From FD1:
    125 user_id → username, email, name, surname, full_name, password_hash, user_created_at
    126 Add: username, email, name, surname, full_name, password_hash, user_created_at
     141Start:
     142K+ = {notification_id, application_id, listing_id, healthrecord_id, review_id,
     143      favorite_user_id, favorite_listing_id, slot_id}
     144
     145From FD6:
     146notification_id -> notification_user_id, notification_type, notification_message, is_read, notification_created_at
     147
     148From FD1 using notification_user_id:
     149notification_user_id -> username, user_email, user_name, user_surname, password_hash, user_created_at
    127150
    128151From FD7:
    129 application_id → app_name, app_email, app_phone, app_city, app_address, submitted_at, app_status, reviewed_at, reviewed_by, denial_reason, clinic_id
    130 Add: app_name, app_email, app_phone, app_city, app_address, submitted_at, app_status, reviewed_at, reviewed_by, denial_reason, clinic_id
    131 
    132 From FD6:
    133 clinic_id → clinic_name, clinic_email, clinic_phone, clinic_address, clinic_location, clinic_city
    134 Add: clinic_name, clinic_email, clinic_phone, clinic_address, clinic_location, clinic_city
     152application_id -> application_clinic_id, app_name, app_email, app_phone, app_city,
     153                  app_address, submitted_at, app_status, reviewed_at, reviewed_by, denial_reason
     154
     155From FD5 using application_clinic_id:
     156clinic_id -> clinic_user_id, clinic_name, clinic_email, clinic_phone, clinic_location,
     157             clinic_city, clinic_address, work_days, start_time, end_time
    135158
    136159From FD9:
    137 listing_id → listing_status, listing_created_at, price, listing_description, animal_id, admin_id
    138 Add: listing_status, listing_created_at, price, listing_description, animal_id, admin_id
    139 
    140 From FD3:
    141 admin_id → user_id
    142 user_id already belongs to K+
    143 
    144 From FD8:
    145 animal_id → animal_name, located_name, species, animal_type, breed, sex, date_of_birth, photo_url, owner_id
    146 Add: animal_name, located_name, species, animal_type, breed, sex, date_of_birth, photo_url, owner_id
    147 
    148 From FD4:
    149 owner_id → client_id
    150 Add: client_id
    151 
    152 From FD2:
    153 client_id → user_id, is_blocked, blocked_at, blocked_reason
    154 Add: is_blocked, blocked_at, blocked_reason
    155 user_id already belongs to K+
     160listing_id -> listing_owner_id, listing_animal_id, listing_status,
     161              listing_created_at, price, listing_description
     162
     163From FD8 using listing_animal_id:
     164animal_id -> animal_owner_id, animal_name, located_name, species, animal_type,
     165             breed, sex, date_of_birth, photo_url
     166
     167From FD4 using listing_owner_id:
     168owner_user_id -> client_user_id
     169
     170From FD3 using client_user_id:
     171client_user_id -> user_id, is_blocked, blocked_at, blocked_reason, blocked_by
    156172
    157173From FD11:
    158 healthrecord_id → hr_type, hr_description, hr_date, animal_id, appointment_id
    159 Add: hr_type, hr_description, hr_date, appointment_id
    160 animal_id already belongs to K+
    161 
    162 From FD10:
    163 appointment_id → date_time, appointment_status, notes, animal_id, clinic_id
    164 Add: date_time, appointment_status, notes
    165 animal_id and clinic_id already belong to K+
     174healthrecord_id -> healthrecord_animal_id, healthrecord_appointment_id,
     175                   hr_type, hr_description, hr_date
     176
     177From FD10 using healthrecord_appointment_id:
     178appointment_id -> appointment_clinic_id, appointment_animal_id,
     179                  responsible_owner_id, date_time, appointment_status, notes
    166180
    167181From FD12:
    168 review_id → reviewer_id, rating, comment, review_created_at, updated_at, is_deleted
    169 Add: reviewer_id, rating, comment, review_created_at, updated_at, is_deleted
    170 
    171 From FD13:
    172 review_id → target_user_id
    173 Add: target_user_id
    174 
    175 From FD14:
    176 review_id → target_clinic_id
    177 Add: target_clinic_id
    178 }}}
    179 
    180 Therefore, K+ = R, so K is a superkey.
    181 
    182 K is also minimal, because removing any attribute from K would cause the loss of one independent component of the universal relation
    183 {{{#!comment
    184 * without notification_id, notification attributes cannot be derived
    185 * without application_id, application attributes cannot be derived
    186 * without listing_id, listing attributes cannot be derived
    187 * without healthrecord_id, health record attributes cannot be derived
    188 * without review_id, review attributes cannot be derived
    189 * without saved_user_id or saved_listing_id, the FavoriteListings relationship cannot be identified
    190 }}}
    191 
    192 So K is a candidate key.
    193 
    194 === Chosen primary key
    195 We select the candidate key above as the primary key of the initial de-normalized relation:
    196 
    197 {{{
    198 {notification_id, application_id, listing_id, healthrecord_id, review_id, saved_user_id, saved_listing_id}
     182review_id -> reviewer_id, rating, comment, review_created_at, updated_at, is_deleted
     183
     184From FD13 / FD14:
     185review_id -> target_user_id, target_clinic_id, review_appointment_id
     186
     187From FD15:
     188slot_id -> slot_clinic_id, slot_date_time, slot_reason, slot_created_at
     189}}}
     190
     191Therefore, 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.
     192
     193The chosen primary key of the initial de-normalized relation is:
     194{{{
     195{notification_id, application_id, listing_id, healthrecord_id, review_id,
     196 favorite_user_id, favorite_listing_id, slot_id}
    199197}}}
    200198
    201199== Normal form checks
    202200
    203 === Check for 1NF
     201=== 1NF
    204202R satisfies 1NF because all attributes are atomic and there are no repeating groups.
    205 Each row is uniquely identified by the chosen composite candidate key.
    206 
    207 === Check for 2NF
    208 R does not satisfy 2NF because it has a composite primary key and many non-key attributes depend only on part of that key rather than on the whole key.
     203
     204=== 2NF
     205R does not satisfy 2NF because it has a composite key and many non-key attributes depend on only part of that key.
    209206
    210207Examples:
    211 * listing_id → listing_status, listing_created_at, price, listing_description
    212 * animal_id → animal_name, species, breed, sex, date_of_birth, photo_url, owner_id
    213 * clinic_id → clinic_name, clinic_email, clinic_phone, clinic_address, clinic_location, clinic_city
    214 * review_id → reviewer_id, rating, comment, review_created_at, updated_at, is_deleted
    215 * notification_id → notification_type, notification_message, notification_created_at, is_read, user_id
     208* notification_id -> notification_user_id, notification_type, notification_message, is_read, notification_created_at
     209* listing_id -> listing_owner_id, listing_animal_id, listing_status, listing_created_at, price, listing_description
     210* clinic_id -> clinic_user_id, clinic_name, clinic_email, clinic_phone, clinic_location, clinic_city, clinic_address
     211* review_id -> reviewer_id, rating, comment, review_created_at, updated_at, is_deleted
     212* slot_id -> slot_clinic_id, slot_date_time, slot_reason, slot_created_at
    216213
    217214These are partial dependencies on components of the composite key, so R violates 2NF.
    … …  
    219216== Decomposition to 2NF
    220217
    221 === Grouping by determinants (partial dependencies)
    222 * user_id → username, email, name, surname, full_name, password_hash, user_created_at
    223 * client_id → user_id, is_blocked, blocked_at, blocked_reason
    224 * admin_id → user_id
    225 * owner_id → client_id
    226 * notification_id → notification_type, notification_message, notification_created_at, is_read, user_id
    227 * clinic_id → clinic_name, clinic_email, clinic_phone, clinic_address, clinic_location, clinic_city
    228 * application_id → app_name, app_email, app_phone, app_city, app_address, submitted_at, app_status, reviewed_at, reviewed_by, denial_reason, clinic_id
    229 * animal_id → animal_name, located_name, species, animal_type, breed, sex, date_of_birth, photo_url, owner_id
    230 * listing_id → listing_status, listing_created_at, price, listing_description, animal_id, admin_id
    231 * appointment_id → date_time, appointment_status, notes, animal_id, clinic_id
    232 * healthrecord_id → hr_type, hr_description, hr_date, animal_id, appointment_id
    233 * review_id → reviewer_id, rating, comment, review_created_at, updated_at, is_deleted
    234 * review_id → target_user_id / target_clinic_id
    235 * FavoriteListings has key (saved_user_id, saved_listing_id) only
    236 
    237 === Explanation of grouping by determinants
    238 
    239 The grouping is performed based on the principle that each set of attributes is determined by a single identifier.
    240 This ensures that each relation will have a primary key on which all other attributes fully depend,
    241 thus satisfying 2NF.
    242 
    243 * user_id → username, email, name, surname, full_name, password_hash, user_created_at 
    244 These attributes describe a user and depend only on user_id, so they are grouped into the Users relation.
    245 
    246 * client_id → user_id, is_blocked, blocked_at, blocked_reason 
    247 These attributes describe the client role of a user, so they are grouped into Clients, separated from Users.
    248 
    249 * admin_id → user_id 
    250 This represents the admin role, so it is separated into its own relation to reflect specialization.
    251 
    252 * owner_id → client_id 
    253 Owner is a specialization of Client, so it is grouped separately to preserve this hierarchy.
    254 
    255 * notification_id → notification_type, notification_message, notification_created_at, is_read, user_id 
    256 These attributes describe a notification and depend only on notification_id, so they form the Notifications relation.
    257 
    258 * clinic_id → clinic_name, clinic_email, clinic_phone, clinic_address, clinic_location, clinic_city 
    259 These attributes describe a clinic and depend only on clinic_id, so they are grouped into VetClinics.
    260 
    261 * application_id → app_name, app_email, app_phone, app_city, app_address, submitted_at, app_status, reviewed_at, reviewed_by, denial_reason, clinic_id 
    262 These attributes describe a clinic application and depend only on application_id, so they form VetClinicApplications.
    263 
    264 * animal_id → animal_name, located_name, species, animal_type, breed, sex, date_of_birth, photo_url, owner_id 
    265 These attributes describe an animal and depend only on animal_id, so they form the Animals relation.
    266 
    267 * listing_id → listing_status, listing_created_at, price, listing_description, animal_id, admin_id 
    268 These attributes describe a listing and depend only on listing_id, so they form the Listings relation.
    269 
    270 * appointment_id → date_time, appointment_status, notes, animal_id, clinic_id 
    271 These attributes describe an appointment and depend only on appointment_id, so they form the Appointments relation.
    272 
    273 * healthrecord_id → hr_type, hr_description, hr_date, animal_id, appointment_id 
    274 These attributes describe a health record and depend only on healthrecord_id, so they form the HealthRecords relation.
    275 
    276 * review_id → reviewer_id, rating, comment, review_created_at, updated_at, is_deleted 
    277 These attributes describe a review and depend only on review_id, so they form the Reviews relation.
    278 
    279 * review_id → target_user_id / target_clinic_id 
    280 These attributes represent specialization (UserReview and ClinicReview), so they will be separated later.
    281 
    282 * FavoriteListings has key (saved_user_id, saved_listing_id) only 
    283 This represents a many-to-many relationship between users and listings. Since it has no non-key attributes, it is already correctly grouped and does not require further decomposition.
    284 
    285 === 2NF relations (first decomposition)
    286 * Users(user_id, username, email, name, surname, full_name, password_hash, user_created_at)
    287 * Clients(client_id, user_id, is_blocked, blocked_at, blocked_reason)
    288 * Admins(admin_id, user_id)
    289 * Owners(owner_id, client_id)
    290 * Notifications(notification_id, user_id, notification_type, notification_message, notification_created_at, is_read)
    291 * VetClinics(clinic_id, clinic_name, clinic_email, clinic_phone, clinic_address, clinic_location, clinic_city)
    292 * 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)
    293 * Animals(animal_id, owner_id, animal_name, located_name, species, animal_type, breed, sex, date_of_birth, photo_url)
    294 * Listings(listing_id, admin_id, animal_id, listing_status, listing_created_at, price, listing_description)
    295 * Appointments(appointment_id, clinic_id, animal_id, date_time, appointment_status, notes)
    296 * HealthRecords(healthrecord_id, appointment_id, animal_id, hr_type, hr_description, hr_date)
    297 * Reviews(review_id, reviewer_id, rating, comment, review_created_at, updated_at, is_deleted)
    298 * FavoriteListings(saved_user_id, saved_listing_id)
     218=== Grouping by determinants
     219* user_id -> username, user_email, user_name, user_surname, password_hash, user_created_at
     220* admin_user_id -> user_id
     221* client_user_id -> user_id, is_blocked, blocked_at, blocked_reason, blocked_by
     222* owner_user_id -> client_user_id
     223* clinic_id -> clinic_user_id, clinic_name, clinic_email, clinic_phone, clinic_location, clinic_city, clinic_address, work_days, start_time, end_time
     224* notification_id -> notification_user_id, notification_type, notification_message, is_read, notification_created_at
     225* 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
     226* animal_id -> animal_owner_id, animal_name, located_name, species, animal_type, breed, sex, date_of_birth, photo_url
     227* listing_id -> listing_owner_id, listing_animal_id, listing_status, listing_created_at, price, listing_description
     228* appointment_id -> appointment_clinic_id, appointment_animal_id, responsible_owner_id, date_time, appointment_status, notes
     229* healthrecord_id -> healthrecord_animal_id, healthrecord_appointment_id, hr_type, hr_description, hr_date
     230* review_id -> reviewer_id, rating, comment, review_created_at, updated_at, is_deleted
     231* review_id -> target_user_id / target_clinic_id, review_appointment_id
     232* slot_id -> slot_clinic_id, slot_date_time, slot_reason, slot_created_at
     233* FavoriteListing has key (favorite_user_id, favorite_listing_id) only
     234
     235=== 2NF relations
     236* Users(user_id, username, email, name, surname, password_hash, created_at)
     237* Admins(user_id)
     238* Clients(user_id, is_blocked, blocked_at, blocked_reason, blocked_by)
     239* Owners(user_id)
     240* VetClinics(clinic_id, user_id, name, email, phone, location, city, address, work_days, start_time, end_time)
     241* Notifications(notification_id, user_id, type, message, is_read, created_at)
     242* VetClinicApplications(application_id, clinic_id, name, email, phone, city, address, submitted_at, status, reviewed_at, reviewed_by, denial_reason)
     243* Animals(animal_id, owner_id, name, located_name, species, type, breed, sex, date_of_birth, photo_url)
     244* Listings(listing_id, owner_id, animal_id, status, created_at, price, description)
     245* FavoriteListings(user_id, listing_id)
     246* Appointments(appointment_id, clinic_id, animal_id, responsible_owner_id, date_time, status, notes)
     247* HealthRecords(healthrecord_id, animal_id, appointment_id, type, description, date)
     248* Reviews(review_id, reviewer_id, rating, comment, created_at, updated_at, is_deleted)
     249* UserReviews(review_id, target_user_id, interaction_type)
     250* ClinicReviews(review_id, target_clinic_id, appointment_id)
     251* ClinicUnavailableSlots(slot_id, clinic_id, date_time, reason, created_at)
    299252
    300253{{{Lossless join:}}}
    301254[[BR]]
    302 The decomposition is lossless because each new relation is formed from a determinant that becomes the key of that relation. The common attributes used in the decomposition are keys in at least one of the resulting relations, so no information is lost.
     255The 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.
    303256
    304257{{{Dependency preservation:}}}
    305258[[BR]]
    306 The decomposition is dependency-preserving because each functional dependency FD1–FD14 is represented fully inside one of the resulting relations. FavoriteListings has only its composite key and no additional non-trivial dependencies.
    307 
    308 == Check for 3NF (and decomposition)
    309 Now we look for transitive dependencies inside the produced relations.
    310 
    311 === Typical transitive issues from the ER
    312 * In Clients(client_id, user_id, …), we have client_id → user_id, but user descriptive attributes are stored in Users, so no transitive dependency remains inside Clients.
    313 * In Listings(listing_id, admin_id, animal_id, …), we have listing_id → admin_id and globally admin_id → user_id, but user attributes are stored in Users, not in Listings, so no transitive dependency remains inside Listings.
    314 * In VetClinicApplications(application_id, clinic_id, …), we have application_id → clinic_id and clinic_id determines clinic attributes, but those attributes are stored in VetClinics, not in VetClinicApplications, so no transitive dependency remains there either.
    315 
    316 === Review subtypes (User Review and Clinic Review)
     259The 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.
     260
     261== Check for 3NF
     262
     263The decomposed relations remove the transitive dependencies from the universal relation.
     264
     265Examples:
     266* VetClinicApplications stores clinic_id, but clinic attributes stay in VetClinics.
     267* Listings stores owner_id and animal_id, but owner and animal descriptive attributes stay in Owners and Animals.
     268* Appointments stores clinic_id, animal_id, and responsible_owner_id, but descriptive attributes stay in their own relations.
     269* ClinicReviews stores appointment_id, but appointment details stay in Appointments.
     270
     271=== Review subtypes
    317272The ER model contains a specialization of Review into UserReview and ClinicReview.
    318 If both target_user_id and target_clinic_id are stored in Reviews, the relation mixes subtype-specific semantics and may introduce nulls or invalid combinations.
    319 So we decompose:
    320 {{{
    321 UserReviews(review_id, target_user_id)
    322 ClinicReviews(review_id, target_clinic_id)
    323 }}}
    324 
    325 Now:
    326 * Reviews stores the common review attributes
    327 * UserReviews stores the target user of a user review
    328 * ClinicReviews stores the target clinic of a clinic review
    329 
    330 === Results after 3NF step
    331 * UserReviews(review_id, target_user_id)
    332 * ClinicReviews(review_id, target_clinic_id)
    333 
    334 After separating subtype-specific attributes into UserReviews and ClinicReviews, all resulting relations satisfy 3NF.
     273Subtype-specific attributes are separated:
     274{{{
     275UserReviews(review_id, target_user_id, interaction_type)
     276ClinicReviews(review_id, target_clinic_id, appointment_id)
     277}}}
     278
     279Reviews 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.
     280
     281The 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.
     282
     283After separating subtype-specific attributes, the resulting relations satisfy 3NF.
    335284
    336285== Check for BCNF
    337 Each resulting relation is also in BCNF because, in every non-trivial functional dependency within a relation, the determinant is a superkey of that relation.
    338 
    339 * Users: user_id is key → BCNF
    340 * Clients: client_id is key → BCNF
    341 * Admins: admin_id is key → BCNF
    342 * Owners: owner_id is key → BCNF
    343 * Notifications: notification_id is key → BCNF
    344 * VetClinics: clinic_id is key → BCNF
    345 * VetClinicApplications: application_id is key → BCNF
    346 * Animals: animal_id is key → BCNF
    347 * Listings: listing_id is key → BCNF
    348 * Appointments: appointment_id is key → BCNF
    349 * HealthRecords: healthrecord_id is key → BCNF
    350 * Reviews: review_id is key → BCNF
    351 * UserReviews: review_id is key → BCNF
    352 * ClinicReviews: review_id is key → BCNF
    353 * FavoriteListings: (saved_user_id, saved_listing_id) is key → BCNF
     286
     287Each resulting relation is in BCNF because every non-trivial functional dependency inside a relation has a determinant that is a superkey of that relation.
     288
     289* Users: user_id is key -> BCNF
     290* Admins: user_id is key -> BCNF
     291* Clients: user_id is key -> BCNF
     292* Owners: user_id is key -> BCNF
     293* VetClinics: clinic_id is key, and user_id is unique -> BCNF
     294* Notifications: notification_id is key -> BCNF
     295* VetClinicApplications: application_id is key, and clinic_id is unique -> BCNF
     296* Animals: animal_id is key -> BCNF
     297* Listings: listing_id is key -> BCNF
     298* FavoriteListings: (user_id, listing_id) is key -> BCNF
     299* Appointments: appointment_id is key -> BCNF
     300* HealthRecords: healthrecord_id is key -> BCNF
     301* Reviews: review_id is key -> BCNF
     302* UserReviews: review_id is key -> BCNF
     303* ClinicReviews: review_id is key -> BCNF
     304* ClinicUnavailableSlots: slot_id is key -> BCNF
     305
    354306{{{So the schema satisfies BCNF.}}}
    355307
    356 == Check for 4NF (multivalued dependencies)
     308== Check for 4NF
     309
    357310No additional non-trivial multivalued dependencies are identified in the resulting schema.
    358 The many-to-many relationship FavoriteListing is already decomposed into the separate relation FavoriteListings(saved_user_id, saved_listing_id), so no further decomposition is required for 4NF.
     311The many-to-many relationship FavoriteListing is already decomposed into FavoriteListings(user_id, listing_id), so no further decomposition is required for 4NF.
     312
    359313{{{The schema satisfies 4NF.}}}
    360314
    361315== Final relations
    362 * Users(user_id, username, email, name, surname, full_name, password_hash, user_created_at)
    363 * Clients(client_id, user_id, is_blocked, blocked_at, blocked_reason)
    364 * Admins(admin_id, user_id)
    365 * Owners(owner_id, client_id)
    366 * Notifications(notification_id, user_id, notification_type, notification_message, notification_created_at, is_read)
    367 * VetClinics(clinic_id, clinic_name, clinic_email, clinic_phone, clinic_address, clinic_location, clinic_city)
    368 * 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)
    369 * Animals(animal_id, owner_id, animal_name, located_name, species, animal_type, breed, sex, date_of_birth, photo_url)
    370 * Listings(listing_id, admin_id, animal_id, listing_status, listing_created_at, price, listing_description)
    371 * FavoriteListings(saved_user_id, saved_listing_id)
    372 * Appointments(appointment_id, clinic_id, animal_id, date_time, appointment_status, notes)
    373 * HealthRecords(healthrecord_id, appointment_id, animal_id, hr_type, hr_description, hr_date)
    374 * Reviews(review_id, reviewer_id, rating, comment, review_created_at, updated_at, is_deleted)
    375 * UserReviews(review_id, target_user_id)
    376 * ClinicReviews(review_id, target_clinic_id)
     316
     317* Users(user_id, username, email, name, surname, password_hash, created_at)
     318* Admins(user_id)
     319* Clients(user_id, is_blocked, blocked_at, blocked_reason, blocked_by)
     320* Owners(user_id)
     321* VetClinics(clinic_id, user_id, name, email, phone, location, city, address, work_days, start_time, end_time)
     322* Notifications(notification_id, user_id, type, message, is_read, created_at)
     323* VetClinicApplications(application_id, clinic_id, name, email, phone, city, address, submitted_at, status, reviewed_at, reviewed_by, denial_reason)
     324* Animals(animal_id, owner_id, name, sex, date_of_birth, photo_url, type, species, breed, located_name)
     325* Listings(listing_id, owner_id, animal_id, status, price, description, created_at)
     326* FavoriteListings(user_id, listing_id)
     327* Appointments(appointment_id, clinic_id, animal_id, responsible_owner_id, status, date_time, notes)
     328* HealthRecords(healthrecord_id, animal_id, appointment_id, type, description, date)
     329* Reviews(review_id, reviewer_id, rating, comment, created_at, updated_at, is_deleted)
     330* UserReviews(review_id, target_user_id, interaction_type)
     331* ClinicReviews(review_id, target_clinic_id, appointment_id)
     332* ClinicUnavailableSlots(slot_id, clinic_id, date_time, reason, created_at)
    377333
    378334== Conclusion