Changes between Version 2 and Version 3 of P5
- Timestamp:
- 01/14/26 23:25:49 (6 days ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
P5
v2 v3 33 33 === Problems in UNF === 34 34 35 *Massive data redundancy 36 37 38 39 *Repeating groups (events, guests, services) 40 41 42 43 *Update anomalies 44 45 46 *Insertion anomalies 47 48 49 *Deletion anomalies 35 • Data duplication (same user, wedding, venue repeated) 36 • Repeating groups (events and guests) 37 • Update anomalies 38 • Insertion anomalies 39 • Deletion anomalies 50 40 51 41 == First Normal Form (1NF) == 52 42 53 The database is in First Normal Form when: 43 === Theoretical Explanation === 54 44 55 All attributes contain atomic values 45 A relation is in First Normal Form (1NF) if: 46 • all attributes contain atomic values 47 • there are no repeating groups 48 • each record can be uniquely identified 56 49 57 There are no repeating groups 58 59 Each row is uniquely identifiable 60 61 A composite primary key is assumed to uniquely identify each row in the initial relation. 50 In this step, the table structure remains the same, 51 but we define a composite primary key to uniquely identify each row. 62 52 63 53 === 1NF Relation === 54 55 Primary Key: 56 (user_id, wedding_id, event_id, guest_id) 57 58 All values are atomic and no multivalued attributes exist: 59 64 60 65 61 R( … … 78 74 == Second Normal Form (2NF) == 79 75 80 Second Normal Form removes partial dependencies. 81 Attributes that depend only on part of the composite key are separated into new relations. 76 === Theoretical Explanation === 77 78 A relation is in Second Normal Form (2NF) if: 79 • it is already in 1NF 80 • no non-key attribute depends on only part of a composite key 81 82 Partial dependencies are removed by decomposing the table into smaller relations. 82 83 83 84 === Decomposition === … … 117 118 {guest_id} → {first_name, last_name, wedding_id} 118 119 119 === Lossless Join Test(2NF) ===120 === Lossless Join Explanation (2NF) === 120 121 121 Since the intersection of decomposed relations contains primary keys that determine all attributes of the smaller relations, the decomposition is lossless. 122 The decomposition is lossless because: 123 • primary keys are preserved 124 • joining the relations reconstructs the original UNF data without loss 122 125 126 == Third Normal Form (3NF) == 127 128 === Theoretical Explanation === 129 130 A relation is in Third Normal Form (3NF) if: 131 • it is already in 2NF 132 • no transitive dependencies exist 133 134 This means non-key attributes must depend only on the primary key. 123 135 == Third Normal Form (3NF) == 124 136 … … 183 195 184 196 The normalization process eliminates redundancy, prevents anomalies, and produces a clean relational schema fully aligned with the ER diagram and SQL implementation. 197 198 185 199 The final design is lossless, dependency-preserving, and in BCNF, making it suitable for a real-world wedding planner system.
