| 55 | | === Formally identify candidate keys === |
| 56 | | To determine a candidate key of the universal de-normalized relation '''R''', we must find a minimal set of attributes whose closure (K+) determines all other attributes in the relation. |
| 57 | | |
| 58 | | Because there are independent M:N relationships and disjoint entities that do not functionally determine each other, the candidate key must be a composite of the independent determinants. |
| 59 | | |
| 60 | | '''Candidate Key K = {''' {{{admin_id}}}, {{{owner_id}}}, {{{sitter_id}}}, {{{pet_id}}}, {{{service_id}}}, {{{booking_id}}}, {{{review_id}}}, {{{payment_id}}}'''}''' |
| 61 | | |
| 62 | | |
| 63 | | === Closure Proof for K === |
| 64 | | Let K = { admin_id, owner_id, sitter_id, pet_id, service_id, booking_id, review_id, payment_id } |
| 65 | | |
| 66 | | Compute K+: |
| 67 | | * '''Start:''' K+ = K |
| 68 | | * '''From FD2:''' admin_id -> user_id |
| 69 | | * Add: user_id |
| 70 | | * '''From FD1:''' user_id -> username, first_name, last_name, password, email |
| 71 | | * Add: username, first_name, last_name, password, email |
| 72 | | * '''From FD3 & FD4:''' owner_id -> user_id and sitter_id -> user_id |
| 73 | | * (user_id already belongs to K+) |
| 74 | | * '''From FD6:''' pet_id -> pet_name, photo, age, special_needs, pet_description, owner_id, pettype_id |
| 75 | | * Add: pet_name, photo, age, special_needs, pet_description, pettype_id |
| 76 | | * (owner_id already belongs to K+) |
| 77 | | * '''From FD5:''' pettype_id -> species, average_lifespan, needs_outdoor_walk |
| 78 | | * Add: species, average_lifespan, needs_outdoor_walk |
| 79 | | * '''From FD7:''' service_id -> service_type, service_description |
| 80 | | * Add: service_type, service_description |
| 81 | | * '''From FD8:''' booking_id -> booking_status, date_from, date_to, address, owner_id, sitter_id |
| 82 | | * Add: booking_status, date_from, date_to, address |
| 83 | | * (owner_id and sitter_id already belong to K+) |
| 84 | | * '''From FD9:''' review_id -> rating, comment, booking_id |
| 85 | | * Add: rating, comment |
| 86 | | * (booking_id already belongs to K+) |
| 87 | | * '''From FD10:''' payment_id -> amount, payment_type, booking_id |
| 88 | | * Add: amount, payment_type |
| 89 | | * (booking_id already belongs to K+) |
| 90 | | |
| 91 | | Therefore, '''K+ = R''', so K is a valid superkey. |
| 92 | | |
| 93 | | K is '''minimal'''. If we remove any single attribute from K (such as removing service_id or pet_id), we entirely lose our starting point for that independent element, so its specific attributes could never be used to the closure. Which means, K is an official Candidate Key. |
| 94 | | |
| 95 | | === Primary Key Selection === |
| 96 | | We select the single composite candidate key K as the official primary key of the initial de-normalized relation: |
| 97 | | |
| 98 | | {{{admin_id, owner_id, sitter_id, pet_id, service_id, booking_id, review_id, payment_id}}} |
| 99 | | === Primary Key Selection === |
| 100 | | We select the candidate key '''K''' as the primary key of the initial de-normalized relation. |
| | 63 | === Determination of Candidate Keys === |
| | 64 | To find a candidate key, we must find a minimal set of attributes whose closure contains all attributes of the relation. We begin by classifying all attributes based on where they appear in the Functional Dependencies. |
| | 65 | |
| | 66 | === Attribute Classification (Left / Right Side) === |
| | 67 | || '''Attribute''' || '''Left Side''' || '''Right Side''' || '''Classification''' || |
| | 68 | || '''admin_id''' || ✓ (FD2, FD11) || ✗ || '''Left only''' || |
| | 69 | || '''pet_id''' || ✓ (FD6, FD12) || ✗ || '''Left only''' || |
| | 70 | || '''service_id''' || ✓ (FD7, FD13, FD14) || ✗ || '''Left only''' || |
| | 71 | || '''review_id''' || ✓ (FD9) || ✗ || '''Left only''' || |
| | 72 | || '''payment_id''' || ✓ (FD10) || ✗ || '''Left only''' || |
| | 73 | || {{{user_id}}} || ✓ (FD1, FD11) || ✓ (FD2, FD3, FD4) || Both || |
| | 74 | || {{{owner_id}}} || ✓ (FD3) || ✓ (FD6, FD8) || Both || |
| | 75 | || {{{sitter_id}}} || ✓ (FD4, FD13) || ✓ (FD8) || Both || |
| | 76 | || {{{pettype_id}}} || ✓ (FD5) || ✓ (FD6) || Both || |
| | 77 | || {{{booking_id}}} || ✓ (FD8, FD12, FD14) || ✓ (FD9, FD10) || Both || |
| | 78 | || {{{username}}}, {{{first_name}}}, {{{last_name}}}, {{{password}}}, {{{email}}} || ✗ || ✓ (FD1) || Right only || |
| | 79 | || {{{species}}}, {{{average_lifespan}}}, {{{needs_outdoor_walk}}} || ✗ || ✓ (FD5) || Right only || |
| | 80 | || {{{pet_name}}}, {{{photo}}}, {{{age}}}, {{{special_needs}}}, {{{pet_description}}} || ✗ || ✓ (FD6) || Right only || |
| | 81 | || {{{service_type}}}, {{{service_description}}} || ✗ || ✓ (FD7) || Right only || |
| | 82 | || {{{booking_status}}}, {{{date_from}}}, {{{date_to}}}, {{{address}}} || ✗ || ✓ (FD8) || Right only || |
| | 83 | || {{{rating}}}, {{{comment}}} || ✗ || ✓ (FD9) || Right only || |
| | 84 | || {{{amount}}}, {{{payment_type}}} || ✗ || ✓ (FD10) || Right only || |
| | 85 | |
| | 86 | === Attributes That Appear ONLY on the Left Side === |
| | 87 | According to relational theory, attributes that appear ''only'' on the left side of functional dependencies can never be derived from any other attribute. Therefore, they '''must''' be part of every candidate key: |
| | 88 | * '''admin_id''' |
| | 89 | * '''pet_id''' |
| | 90 | * '''service_id''' |
| | 91 | * '''review_id''' |
| | 92 | * '''payment_id''' |
| | 93 | |
| | 94 | === Closure Computation === |
| | 95 | '''Step 1:''' We start with our mandatory attributes {{{ {admin_id, pet_id, service_id, review_id, payment_id} }}} and compute the mathematical closure: |
| | 96 | |
| | 97 | {{{ {admin_id, pet_id, service_id, review_id, payment_id}⁺ }}}: |
| | 98 | * '''From FD2''' ({{{admin_id}}} → {{{user_id}}}): We obtain {{{user_id}}} |
| | 99 | * '''From FD1''' ({{{user_id}}} → {{{username}}}, {{{first_name}}}...): We obtain {{{username, first_name, last_name, password, email}}} |
| | 100 | * '''From FD6''' ({{{pet_id}}} → {{{pet_name}}}, {{{photo}}}... {{{owner_id}}}, {{{pettype_id}}}): We obtain {{{pet_name, photo, age, special_needs, pet_description, owner_id, pettype_id}}} |
| | 101 | * '''From FD5''' ({{{pettype_id}}} → {{{species}}}...): We obtain {{{species, average_lifespan, needs_outdoor_walk}}} |
| | 102 | * '''From FD7''' ({{{service_id}}} → {{{service_type}}}...): We obtain {{{service_type, service_description}}} |
| | 103 | * '''From FD9''' ({{{review_id}}} → {{{rating}}}, {{{comment}}}, {{{booking_id}}}): We obtain {{{rating, comment, booking_id}}} |
| | 104 | * '''From FD8''' ({{{booking_id}}} → {{{booking_status}}}... {{{sitter_id}}}): We obtain {{{booking_status, date_from, date_to, address, sitter_id}}} |
| | 105 | * '''From FD10''' ({{{payment_id}}} → {{{amount}}}, {{{payment_type}}}, {{{booking_id}}}): We obtain {{{amount, payment_type}}} ({{{booking_id}}} is already in closure) |
| | 106 | * '''From FD3 & FD4:''' {{{owner_id}}} and {{{sitter_id}}} map to {{{user_id}}}, which is already in the closure. |
| | 107 | * '''From FD11-FD14:''' All composite pairings (e.g., {{{booking_id}}} and {{{pet_id}}}) are already present in the closure, satisfying the M:N relations. |
| | 108 | |
| | 109 | '''Closure = Universal_Relation ✓''' (all 33 attributes are successfully derived) |
| | 110 | |
| | 111 | === Minimality Check === |
| | 112 | To formally prove this key is minimal, we test proper subsets of '''K = {admin_id, pet_id, service_id, review_id, payment_id}''': |
| | 113 | |
| | 114 | || '''Subset''' || '''Closure Equals Universal_Relation?''' || '''Justification''' || |
| | 115 | || '''K − {admin_id}''' || ✗ NO || Cannot derive {{{admin_id}}}. || |
| | 116 | || '''K − {pet_id}''' || ✗ NO || Cannot derive pet attributes ({{{pet_name, photo, age, special_needs, pet_description}}}). || |
| | 117 | || '''K − {service_id}''' || ✗ NO || Cannot derive service attributes ({{{service_type, service_description}}}). || |
| | 118 | || '''K − {review_id}''' || ✗ NO || Cannot derive review attributes ({{{review_id, rating, comment}}}). || |
| | 119 | || '''K − {payment_id}''' || ✗ NO || Cannot derive payment attributes ({{{payment_id, amount, payment_type}}}). || |
| | 120 | |
| | 121 | '''Conclusion:''' K = {admin_id, pet_id, service_id, review_id, payment_id} is minimal and is the strictly mathematically correct candidate key. |
| | 122 | |
| | 123 | === Choice of Primary Key === |
| | 124 | '''Chosen Primary Key:''' {{{ {admin_id, pet_id, service_id, review_id, payment_id} }}} |
| | 125 | |
| | 126 | '''Justification:''' This composite key is the officially proven minimal Candidate Key. Attempting to include other logical identifiers (such as {{{owner_id}}}, {{{sitter_id}}}, or {{{booking_id}}}) would violate the rule of minimality, because the closure proof demonstrates those attributes are functionally dependent on the core transactional IDs. |
