Changes between Version 5 and Version 6 of Normalization

Timestamp:

05/28/26 17:08:02 (3 weeks ago)

Author:

181201

Comment:

--

Normalization

@@ -171 +171 @@
 '''Validation of 2NF Decomposition:'''
 
-'''1. Loss-less Join Property:''' 
-By definition (Heath's Theorem), a decomposition of relation ''R'' into ''R1'' and ''R2'' is lossless if the common attributes (''R1'' ∩ ''R2'') form a superkey for either ''R1'' or ''R2''. 
-Our 14 relations were not created in a single split, but through '''successive binary decompositions'''. 
- * For example, in the first step, ''R'' was decomposed into ''R_Users'' and ''R_Remainder'' based on FD1. The intersection of these two relations is {{{user_id}}}. Because {{{user_id}}} is the primary key of ''R_Users'', the functional dependency {{{user_id → username, first_name, last_name, password, email}}} is preserved, proving this specific binary split is lossless. 
- * In another step, the remainder was split into ''R_Pets'' and a new remainder based on FD6. The intersection is {{{pet_id}}}. Since {{{pet_id}}} is the primary key of ''R_Pets'', this split is also lossless.
- * By recursively applying this exact binary splitting logic along the boundaries of FD2 through FD14, every single decomposition step shared a primary key with the remainder relation. Therefore, the final 14-table schema is strictly lossless.
+'''1. Loss-less Join Property Validation:'''
+By 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.
+
+(Let '''R_remainder''' denote the remainder relation after each split)
+
+'''Split 1: Extracting Users (FD1)'''
+ * '''Decomposition:''' ''R'' is split into '''Users''' and '''R_remainder1'''.
+ * '''Intersection:''' '''Users''' ∩ '''R_remainder1''' = {{{ {user_id} }}}
+ * '''Proof:''' {{{user_id}}} is the Primary Key of the '''Users''' table. The split is lossless.
+
+'''Split 2: Extracting Admins (FD2)'''
+ * '''Decomposition:''' '''R_remainder1''' is split into '''Admins''' and '''R_remainder2'''.
+ * '''Intersection:''' '''Admins''' ∩ '''R_remainder2''' = {{{ {admin_id} }}}
+ * '''Proof:''' {{{admin_id}}} is the Primary Key of the '''Admins''' table. The split is lossless.
+
+'''Split 3: Extracting Pet Owners (FD3)'''
+ * '''Decomposition:''' '''R_remainder2''' is split into '''PetOwners''' and '''R_remainder3'''.
+ * '''Intersection:''' '''PetOwners''' ∩ '''R_remainder3''' = {{{ {owner_id} }}}
+ * '''Proof:''' {{{owner_id}}} is the Primary Key of the '''PetOwners''' table. The split is lossless.
+
+'''Split 4: Extracting Pet Sitters (FD4)'''
+ * '''Decomposition:''' '''R_remainder3''' is split into '''PetSitters''' and '''R_remainder4'''.
+ * '''Intersection:''' '''PetSitters''' ∩ '''R_remainder4''' = {{{ {sitter_id} }}}
+ * '''Proof:''' {{{sitter_id}}} is the Primary Key of the '''PetSitters''' table. The split is lossless.
+
+'''Split 5: Extracting Pet Types (FD5)'''
+ * '''Decomposition:''' '''R_remainder4''' is split into '''PetTypes''' and '''R_remainder5'''.
+ * '''Intersection:''' '''PetTypes''' ∩ '''R_remainder5''' = {{{ {pettype_id} }}}
+ * '''Proof:''' {{{pettype_id}}} is the Primary Key of the '''PetTypes''' table. The split is lossless.
+
+'''Split 6: Extracting Pets (FD6)'''
+ * '''Decomposition:''' '''R_remainder5''' is split into '''Pets''' and '''R_remainder6'''.
+ * '''Intersection:''' '''Pets''' ∩ '''R_remainder6''' = {{{ {pet_id} }}}
+ * '''Proof:''' {{{pet_id}}} is the Primary Key of the '''Pets''' table. The split is lossless.
+
+'''Split 7: Extracting Services (FD7)'''
+ * '''Decomposition:''' '''R_remainder6''' is split into '''Services''' and '''R_remainder7'''.
+ * '''Intersection:''' '''Services''' ∩ '''R_remainder7''' = {{{ {service_id} }}}
+ * '''Proof:''' {{{service_id}}} is the Primary Key of the '''Services''' table. The split is lossless.
+
+'''Split 8: Extracting Bookings (FD8)'''
+ * '''Decomposition:''' '''R_remainder7''' is split into '''Bookings''' and '''R_remainder8'''.
+ * '''Intersection:''' '''Bookings''' ∩ '''R_remainder8''' = {{{ {booking_id} }}}
+ * '''Proof:''' {{{booking_id}}} is the Primary Key of the '''Bookings''' table. The split is lossless.
+
+'''Split 9: Extracting Reviews (FD9)'''
+ * '''Decomposition:''' '''R_remainder8''' is split into '''Reviews''' and '''R_remainder9'''.
+ * '''Intersection:''' '''Reviews''' ∩ '''R_remainder9''' = {{{ {review_id} }}}
+ * '''Proof:''' {{{review_id}}} is the Primary Key of the '''Reviews''' table. The split is lossless.
+
+'''Split 10: Extracting Payments (FD10)'''
+ * '''Decomposition:''' '''R_remainder9''' is split into '''Payments''' and '''R_remainder10'''.
+ * '''Intersection:''' '''Payments''' ∩ '''R_remainder10''' = {{{ {payment_id} }}}
+ * '''Proof:''' {{{payment_id}}} is the Primary Key of the '''Payments''' table. The split is lossless.
+
+'''Splits 11-13: Extracting M:N Relations/Junctions (FD11-FD14)'''
+
+The final remainder relation ('''R_remainder10''') contains only the composite keys representing the Many-to-Many relationships ('''AdminManagement''', '''BookingPets''', '''SitterServices''', '''BookingServices'''). Because these tables consist ''entirely'' of their composite primary keys, any further separation will trivially satisfy Heath's Theorem.
+
+'''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.
 
 '''2. Dependency Preservation:'''
@@ -201 +255 @@
 
 '''Status:''' All 14 relations naturally satisfy '''3NF'''.
+
 === 3. Decomposition to BCNF ===