Proof of Candidate Key

Definition

A set of attributes X is a candidate key if:

X⁺ = R X is minimal

The closure X⁺ is computed by repeatedly applying functional dependencies until no additional attributes can be derived.

Step 1 – Testing Single Attribute Candidate Keys

Attempt 1

K = {attendance_id}

Closure

(attendance_id)⁺ gives:

attendance_status table_number guest_id event_id

Using additional dependencies:

event_id → event_type, wedding_id guest_id → guest_first_name, guest_last_name, rsvp_status, wedding_id wedding_id → date, budget, notes, user_id user_id → first_name, last_name, email, phone_number, gender, birthday

Missing Dependencies

The closure still does NOT determine:

venue booking information photographer booking information band booking information registrar booking information priest and church information

Therefore:

(attendance_id)⁺ ≠ R

Conclusion:

attendance_id is NOT a candidate key.

Step 2 – Testing Two Attributes

K = {attendance_id, priest_id}

Closure

Additional attributes derived:

priest_name priest_contact church_id church_name church_location

Missing Dependencies

Still missing:

venue booking information photographer booking information band booking information registrar booking information

Therefore:

(attendance_id, priest_id)⁺ ≠ R

Conclusion:

K is NOT a candidate key.

Step 3 – Testing Three Attributes

K = { attendance_id, venue_booking_id, photographer_booking_id }

Closure

Additional attributes derived:

venue information photographer information wedding information user information

Missing Dependencies

Still missing:

band booking information registrar booking information priest/church information

Therefore:

K⁺ ≠ R

Conclusion:

K is NOT a candidate key.

Step 4 – Testing Larger Attribute Sets

K = { attendance_id, priest_id, venue_booking_id, band_booking_id }

Missing Dependencies

Still missing:

photographer booking information registrar booking information

Therefore:

K⁺ ≠ R

Conclusion:

K is NOT a candidate key.

Step 5 – Including All Booking Branches

K = { attendance_id, venue_booking_id, photographer_booking_id, band_booking_id, registrar_booking_id }

Missing Dependencies

Still missing:

priest information church information

Therefore:

K⁺ ≠ R

Conclusion:

K is NOT a candidate key.

Step 6 – Final Candidate Key

K = { attendance_id, venue_booking_id, photographer_booking_id, band_booking_id, registrar_booking_id, priest_id }

Closure

Using all functional dependencies:

attendance branch is determined event branch is determined guest branch is determined wedding branch is determined venue booking branch is determined photographer booking branch is determined band booking branch is determined registrar booking branch is determined priest branch is determined church branch is determined

No attributes remain undetermined.

Therefore:

K⁺ = R

Conclusion:

K is a candidate key.

Minimality Proof

A candidate key must be minimal.

Removing any attribute from K causes loss of at least one independent entity branch:

Removed Attribute	Lost Information
attendance_id	attendance branch
venue_booking_id	venue booking branch
photographer_booking_id	photographer booking branch
band_booking_id	band booking branch
registrar_booking_id	registrar booking branch
priest_id	priest/church branch

Therefore every attribute in K is necessary.

Verification of Alternative Candidate Keys

A relation may contain multiple candidate keys. Therefore, after identifying one candidate key, we must verify whether another minimal attribute set can also determine all attributes of R.

Testing Possible Alternative Key

The only realistic possibility is replacing:

priest_id

with:

church_id

because of the dependency:

priest_id → church_id

However, the reverse dependency does NOT hold:

church_id ↛ priest_id

A church may have multiple priests, therefore church_id alone cannot uniquely determine priest information.

Alternative Key Attempt

K₂ = { attendance_id, venue_booking_id, photographer_booking_id, band_booking_id, registrar_booking_id, church_id }

Closure

Using the functional dependencies:

• attendance branch is determined
• venue booking branch is determined
• photographer booking branch is determined
• band booking branch is determined
• registrar booking branch is determined
• church branch is determined

However, the closure cannot derive:

• priest_id
• priest_name
• priest_contact

because:

church_id ↛ priest_id

Therefore:

K₂⁺ ≠ R

Conclusion:

K₂ is NOT a candidate key.

Verification of Other Possible Replacements

No other attribute can replace any component of the identified key because each attribute determines an independent branch of the relation.

Attribute	Determines
attendance_id	attendance/event/guest branch
venue_booking_id	venue booking branch
photographer_booking_id	photographer booking branch
band_booking_id	band booking branch
registrar_booking_id	registrar booking branch
priest_id	priest/church branch

Removing or replacing any attribute causes loss of information that cannot be derived from the remaining attributes.

Why church_id Is Not Included

Suppose we define:

K = { attendance_id, venue_booking_id, photographer_booking_id, band_booking_id, registrar_booking_id, priest_id, church_id }

This violates minimality because:

priest_id → church_id

church_id is already derivable from priest_id.

Therefore church_id is redundant.

The resulting set would be a superkey, NOT a candidate key.

Final Candidate Key

K = { attendance_id, venue_booking_id, photographer_booking_id, band_booking_id, registrar_booking_id, priest_id }

This key:

Determines all attributes of R Is minimal Satisfies the formal definition of a candidate key

No second candidate key exists because no alternative minimal attribute combination functionally determines all attributes of R.