Changes between Version 1 and Version 2 of Proof of Candidate Key

Timestamp:

05/08/26 20:13:11 (3 weeks ago)

Author:

211171

Comment:

--

Proof of Candidate Key

@@ -1 @@
-== Candidate Key Determination ==
-
-As mentioned before, based on business rules of the Wedding Planner system, the following functional dependencies hold:
-
-|| ID || Functional Dependency || Justification ||
-|| FD1 || user_id → user_first_name, user_last_name, user_email || User attributes depend only on user_id 
-|| FD2 || wedding_id → wedding_date, wedding_budget, user_id || A wedding uniquely defines its properties 
-|| FD3 || venue_id → venue_name, venue_type, venue_capacity || Venue attributes depend only on venue_id 
-|| FD4 || booking_id → venue_id, wedding_id, booking_date, start_time, end_time || A booking uniquely defines a venue reservation ||
-=== FD1: user_id → user_first_name, user_last_name, user_email ===
-
-Proof:
-
-|| Condition || Reason ||
-|| user_id is unique || Each user has exactly one system account 
-|| Name and email belong to the user || They do not depend on weddings or venues 
-|| Same user_id always gives same values || No variation allowed by system rules 
-
-Conclusion:
-
-|| Functional Dependency ||
-|| user_id → user_first_name, user_last_name, user_email ||
-
-This dependency is non-transitive and deterministic.
-
-=== FD2: wedding_id → wedding_date, wedding_budget, user_id ===
-
-Proof:
-
-|| Condition || Reason ||
-|| wedding_id is unique || Each wedding is registered once 
-|| Wedding date is fixed || A wedding cannot have multiple dates 
-|| Budget belongs to the wedding || Independent of venue choice 
-|| Each wedding has one owner || user_id identifies the organizer
-
-Conclusion:
-
-|| Functional Dependency ||
-|| wedding_id → wedding_date, wedding_budget, user_id ||
-
-This shows entity integrity for weddings.
-
-=== FD3: venue_id → venue_name, venue_type, venue_capacity ===
-
-Proof:
-
-|| Condition || Reason ||
-|| venue_id is unique || One ID per physical venue 
-|| Name and type are static || Do not change per booking 
-|| Capacity is venue-specific || Independent of weddings 
-
-Conclusion:
-
-|| Functional Dependency ||
-|| venue_id → venue_name, venue_type, venue_capacity ||
-
-Venue attributes are fully functionally dependent on venue_id.
-
-=== FD4: booking_id → venue_id, wedding_id, booking_date, start_time, end_time ===
-
-Proof:
-
-|| Condition || Reason ||
-|| booking_id is unique || One booking per reservation 
-|| Booking ties venue and wedding || Both are required 
-|| Date and time define reservation || Cannot vary per booking ||
-
-Conclusion:
-
-|| Functional Dependency ||
-|| booking_id → venue_id, wedding_id, booking_date, start_time, end_time ||
-
-This models a relationship entity (association table).
-
-== Step 3: Attribute Closure Calculation ==
-
-To find a candidate key, we compute attribute closures.
-
-=== Attempt 1: user_id⁺ ===
-
-|| Derived Attributes ||
-|| user_first_name, user_last_name, user_email ||
-
-❌ Does NOT determine wedding, venue, or booking data
-→ Not a key
-
-=== Attempt 2: wedding_id⁺ ===
-
-|| Derived Attributes ||
-|| wedding_date, wedding_budget, user_id, user_first_name, user_last_name, user_email ||
-
-❌ Missing venue and booking data
-→ Not a key
-
-=== Attempt 3: venue_id⁺ ===
-
-|| Derived Attributes ||
-|| venue_name, venue_type, venue_capacity ||
-
-❌ Missing user, wedding, booking
-→ Not a key
-
-=== Attempt 4: booking_id⁺ ===
-
-|| Derived Attributes ||
-|| venue_id, wedding_id, booking_date, start_time, end_time ||
-|| venue_name, venue_type, venue_capacity ||
-|| wedding_date, wedding_budget, user_id ||
-|| user_first_name, user_last_name, user_email ||
-
-✅ booking_id⁺ = ALL ATTRIBUTES
-
-Thus:
-
-|| Candidate Key ||
-|| booking_id ||
-
-To uniquely identify a tuple in R, the following composite key is required:
-
-|| Candidate Key ||
-|| (user_id, wedding_id, venue_id, booking_date) ||
-
-This confirms that the initial relation has a composite primary key, which leads to partial dependencies.
+= 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.
+
+== 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