Changes between Version 7 and Version 8 of Normalization


Ignore:
Timestamp:
05/28/26 17:22:03 (3 weeks ago)
Author:
181201
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • Normalization

    v7 v8  
    169169'''BookingServices''' ({{{booking_id}}}, {{{service_id}}})
    170170
    171 '''Validation of 2NF Decomposition:'''
    172 
    173171'''1. Loss-less Join Property Validation:'''
    174172By definition (Heath's Theorem), a decomposition of relation ''R'' into ''R1'' and ''R2'' is lossless if the intersection of their attributes (''R1'' ∩ ''R2'') forms a superkey for either ''R1'' or ''R2''. To achieve our final schema without data loss, the universal relation ''R'' was decomposed through '''13 successive binary splits'''. Below is the formal proof of each split, detailing the exact attributes partitioned.
    175173
    176 ''(Let R_remainder denote the remainder relation after each split)''
     174Let '''R_remainder''' be the remainder relation after each split
    177175
    178176'''Initial State:'''
    179 ''R'' = {user_id, username, first_name, last_name, password, email, admin_id, owner_id, sitter_id, pettype_id, species, average_lifespan, needs_outdoor_walk, pet_id, pet_name, photo, age, special_needs, pet_description, service_id, service_type, service_description, booking_id, booking_status, date_from, date_to, address, review_id, rating, comment, payment_id, amount, payment_type}
     177''R'' = {{{ {user_id, username, first_name, last_name, password, email, admin_id, owner_id, sitter_id, pettype_id, species, average_lifespan, needs_outdoor_walk, pet_id, pet_name, photo, age, special_needs, pet_description, service_id, service_type, service_description, booking_id, booking_status, date_from, date_to, address, review_id, rating, comment, payment_id, amount, payment_type} }}}
    180178
    181179'''Split 1: Extracting Users (FD1)'''
    182180 * '''Decomposition:''' ''R'' is split into '''Users''' and '''R_remainder1'''.
    183  * '''Attributes in Users:''' {user_id, username, first_name, last_name, password, email}
     181 * '''Attributes in Users:''' {{{ {user_id, username, first_name, last_name, password, email} }}}
    184182 * '''Attributes in R_remainder1:''' {{{ {user_id, admin_id, owner_id, sitter_id, pettype_id, species, average_lifespan, needs_outdoor_walk, pet_id, pet_name, photo, age, special_needs, pet_description, service_id, service_type, service_description, booking_id, booking_status, date_from, date_to, address, review_id, rating, comment, payment_id, amount, payment_type} }}}
    185183 * '''Intersection:''' '''Users''' ∩ '''R_remainder1''' = {{{ {user_id} }}}
    … …  
    188186'''Split 2: Extracting Admins (FD2)'''
    189187 * '''Decomposition:''' '''R_remainder1''' is split into '''Admins''' and '''R_remainder2'''.
    190  * '''Attributes in Admins:''' {admin_id, user_id}
     188 * '''Attributes in Admins:''' {{{ {admin_id, user_id} }}}
    191189 * '''Attributes in R_remainder2:''' {{{ {admin_id, owner_id, sitter_id, pettype_id, species, average_lifespan, needs_outdoor_walk, pet_id, pet_name, photo, age, special_needs, pet_description, service_id, service_type, service_description, booking_id, booking_status, date_from, date_to, address, review_id, rating, comment, payment_id, amount, payment_type} }}}
    192190 * '''Intersection:''' '''Admins''' ∩ '''R_remainder2''' = {{{ {admin_id} }}}
    … …  
    195193'''Split 3: Extracting Pet Owners (FD3)'''
    196194 * '''Decomposition:''' '''R_remainder2''' is split into '''PetOwners''' and '''R_remainder3'''.
    197  * '''Attributes in PetOwners:''' {owner_id, user_id}
     195 * '''Attributes in PetOwners:''' {{{ {owner_id, user_id} }}}
    198196 * '''Attributes in R_remainder3:''' {{{ {admin_id, owner_id, sitter_id, pettype_id, species, average_lifespan, needs_outdoor_walk, pet_id, pet_name, photo, age, special_needs, pet_description, service_id, service_type, service_description, booking_id, booking_status, date_from, date_to, address, review_id, rating, comment, payment_id, amount, payment_type} }}}
    199197 * '''Intersection:''' '''PetOwners''' ∩ '''R_remainder3''' = {{{ {owner_id} }}}
    … …  
    202200'''Split 4: Extracting Pet Sitters (FD4)'''
    203201 * '''Decomposition:''' '''R_remainder3''' is split into '''PetSitters''' and '''R_remainder4'''.
    204  * '''Attributes in PetSitters:''' {sitter_id, user_id}
     202 * '''Attributes in PetSitters:''' {{{ {sitter_id, user_id} }}}
    205203 * '''Attributes in R_remainder4:''' {{{ {admin_id, owner_id, sitter_id, pettype_id, species, average_lifespan, needs_outdoor_walk, pet_id, pet_name, photo, age, special_needs, pet_description, service_id, service_type, service_description, booking_id, booking_status, date_from, date_to, address, review_id, rating, comment, payment_id, amount, payment_type} }}}
    206204 * '''Intersection:''' '''PetSitters''' ∩ '''R_remainder4''' = {{{ {sitter_id} }}}
    … …  
    209207'''Split 5: Extracting Pet Types (FD5)'''
    210208 * '''Decomposition:''' '''R_remainder4''' is split into '''PetTypes''' and '''R_remainder5'''.
    211  * '''Attributes in PetTypes:''' {pettype_id, species, average_lifespan, needs_outdoor_walk}
     209 * '''Attributes in PetTypes:''' {{{ {pettype_id, species, average_lifespan, needs_outdoor_walk} }}}
    212210 * '''Attributes in R_remainder5:''' {{{ {admin_id, owner_id, sitter_id, pettype_id, pet_id, pet_name, photo, age, special_needs, pet_description, service_id, service_type, service_description, booking_id, booking_status, date_from, date_to, address, review_id, rating, comment, payment_id, amount, payment_type} }}}
    213211 * '''Intersection:''' '''PetTypes''' ∩ '''R_remainder5''' = {{{ {pettype_id} }}}
    … …  
    216214'''Split 6: Extracting Pets (FD6)'''
    217215 * '''Decomposition:''' '''R_remainder5''' is split into '''Pets''' and '''R_remainder6'''.
    218  * '''Attributes in Pets:''' {pet_id, pet_name, photo, age, special_needs, pet_description, owner_id, pettype_id}
     216 * '''Attributes in Pets:''' {{{ {pet_id, pet_name, photo, age, special_needs, pet_description, owner_id, pettype_id} }}}
    219217 * '''Attributes in R_remainder6:''' {{{ {admin_id, owner_id, sitter_id, pet_id, service_id, service_type, service_description, booking_id, booking_status, date_from, date_to, address, review_id, rating, comment, payment_id, amount, payment_type} }}}
    220218 * '''Intersection:''' '''Pets''' ∩ '''R_remainder6''' = {{{ {pet_id} }}}
    … …  
    223221'''Split 7: Extracting Services (FD7)'''
    224222 * '''Decomposition:''' '''R_remainder6''' is split into '''Services''' and '''R_remainder7'''.
    225  * '''Attributes in Services:''' {service_id, service_type, service_description}
     223 * '''Attributes in Services:''' {{{ {service_id, service_type, service_description} }}}
    226224 * '''Attributes in R_remainder7:''' {{{ {admin_id, owner_id, sitter_id, pet_id, service_id, booking_id, booking_status, date_from, date_to, address, review_id, rating, comment, payment_id, amount, payment_type} }}}
    227225 * '''Intersection:''' '''Services''' ∩ '''R_remainder7''' = {{{ {service_id} }}}
    … …  
    230228'''Split 8: Extracting Bookings (FD8)'''
    231229 * '''Decomposition:''' '''R_remainder7''' is split into '''Bookings''' and '''R_remainder8'''.
    232  * '''Attributes in Bookings:''' {booking_id, booking_status, date_from, date_to, address, owner_id, sitter_id}
     230 * '''Attributes in Bookings:''' {{{ {booking_id, booking_status, date_from, date_to, address, owner_id, sitter_id} }}}
    233231 * '''Attributes in R_remainder8:''' {{{ {admin_id, sitter_id, pet_id, service_id, booking_id, review_id, rating, comment, payment_id, amount, payment_type} }}}
    234232 * '''Intersection:''' '''Bookings''' ∩ '''R_remainder8''' = {{{ {booking_id} }}}
    … …  
    237235'''Split 9: Extracting Reviews (FD9)'''
    238236 * '''Decomposition:''' '''R_remainder8''' is split into '''Reviews''' and '''R_remainder9'''.
    239  * '''Attributes in Reviews:''' {review_id, rating, comment, booking_id}
     237 * '''Attributes in Reviews:''' {{{ {review_id, rating, comment, booking_id} }}}
    240238 * '''Attributes in R_remainder9:''' {{{ {admin_id, sitter_id, pet_id, service_id, booking_id, payment_id, amount, payment_type} }}}
    241239 * '''Intersection:''' '''Reviews''' ∩ '''R_remainder9''' = {{{ {review_id} }}}
    … …  
    244242'''Split 10: Extracting Payments (FD10)'''
    245243 * '''Decomposition:''' '''R_remainder9''' is split into '''Payments''' and '''R_remainder10'''.
    246  * '''Attributes in Payments:''' {payment_id, amount, payment_type, booking_id}
     244 * '''Attributes in Payments:''' {{{ {payment_id, amount, payment_type, booking_id} }}}
    247245 * '''Attributes in R_remainder10:''' {{{ {admin_id, sitter_id, pet_id, service_id, booking_id} }}}
    248246 * '''Intersection:''' '''Payments''' ∩ '''R_remainder10''' = {{{ {payment_id} }}}
    … …  
    251249'''Splits 11-13: Extracting M:N Relations/Junctions (FD11-FD14)'''
    252250The final remainder relation ('''R_remainder10''') contains only the composite keys representing the Many-to-Many relationships:
    253  * '''AdminManagement:''' {admin_id, user_id}
    254  * '''BookingPets:''' {booking_id, pet_id}
    255  * '''SitterServices:''' {sitter_id, service_id}
    256  * '''BookingServices:''' {booking_id, service_id}
    257 
    258 Because these final tables consist ''entirely'' of their composite primary keys, any further binary separation along these boundaries trivially satisfies Heath's Theorem.
     251 * '''AdminManagement:''' {{{ {admin_id, user_id} }}}
     252 * '''BookingPets:''' {{{ {booking_id, pet_id} }}}
     253 * '''SitterServices:''' {{{ {sitter_id, service_id} }}}
     254 * '''BookingServices:''' {{{ {booking_id, service_id} }}}
     255
     256Because these final tables consist only of their composite primary keys, any further binary separation will trivially satisfy Heath's Theorem.
    259257
    260258'''Conclusion:''' Because every single step of the decomposition shared an intersection that was a guaranteed Primary Key, the final 14-table schema is strictly lossless.