| 60 | | '''Candidate Key K = {''' {{{review_id}}}, {{{payment_id}}}, {{{pet_id}}}, {{{admin_id}}}, |
| 61 | | ({{{sitter_id}}}, {{{service_id}}}), |
| 62 | | ({{{booking_id}}}, {{{pet_id}}}), |
| 63 | | |
| 64 | | ({{{booking_id}}}, {{{service_id}}}), |
| 65 | | |
| 66 | | |
| 67 | | ({{{admin_id}}}, {{{user_id}}}) '''}''' |
| 68 | | |
| 69 | | '''Closure Proof:''' The closure of K (K+) includes every attribute in R because: |
| 70 | | |
| 71 | | {{{review_id}}} determines review attributes and {{{booking_id}}} (FD9). |
| 72 | | |
| 73 | | {{{booking_id}}} determines booking attributes, {{{owner_id}}}, and {{{sitter_id}}} (FD8). |
| 74 | | |
| 75 | | {{{owner_id}}} and {{{sitter_id}}} determine {{{user_id}}} (FD3, FD4), which determines all user attributes (FD1). |
| 76 | | |
| 77 | | {{{pet_id}}} determines pet attributes and {{{pettype_id}}} (FD6), which determines pet type attributes (FD5). |
| 78 | | |
| 79 | | Removing any attribute from K would result in losing an independent component of the database. Therefore, K is minimal and valid. |
| 80 | | |
| | 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}}} |
