Changes between Version 4 and Version 5 of Normalization

Timestamp:

06/15/26 11:08:56 (41 hours ago)

Author:

213257

Comment:

--

Normalization

@@ -51 +51 @@
 === Identifying the Candidate Key ===
 
+Before we start the aim of a candidate key is to satisfy the following requirements:
+- The candidate key closure must be able to cover every attribute in the relation R.
+- And for the key to be minimal, we only keep ones that will be able to satisfy the above mentioned rule.
+
+The starting point would be finding every attributes that are never found on the right side in any of the functional dependencies.
+
+This means that these attributes don’t get determined by any other. So due to this we need to include them always in the candidate keys.
+
+{{{
+  architect_id  — nothing in Fc produces it
+  inquiry_id    — nothing in Fc produces it
+  appointment_id — appears on the RHS of FD17 (timeslot_id → appointment_id),
+                   but timeslot_id itself appears on the RHS of FD16
+                   (appointment_id → timeslot_id), meaning the two mutually
+                   determine each other. Neither can be derived from anything
+                   outside that pair, so one of them must be in every key.
+}}}
+
+This gives us the base set to test: {architect_id, inquiry_id, appointment_id}.
+
+Testing the coverage of this candidate key results in the complete cover of all attributes in R
+{architect_id, inquiry_id, appointment_id} is a superkey.
+
+If we try to remove each to get a minimal candidate key the following happens:
+
+By removing architect_id we lose the ability to reach architect_name which is only reachable via FD03 and that requires an architect_id.
+
+By removing inquiry_id we lose the ability to reach inquiry_message, inquiry_status, inquiry_created_at which are only reachable via FD15.
+
+By removing appointment_id we lose the ability to reach multiple attributes (appt_status, timeslot_id, ts_date, ts_time_start, ts_time_end, ts_status).
+
+Another viable candidate key is {architect_id, inquiry_id, timeslot_id}:
+
+From the closure test above we know appointment_id is required to reach
+appt_status and the timeslot chain. However, appointment_id and timeslot_id
+mutually determine each other:
+
+{{{
+  FD16: appointment_id → timeslot_id
+  FD17: timeslot_id    → appointment_id
+}}}
+
+This means timeslot_id can substitute for appointment_id. Testing the closure
+of {architect_id, inquiry_id, timeslot_id} results in all attributes being covered.
+
+There arent any other substitutable attributes left, so that leaves us with the 2 candidate keys we tested.
+
 A candidate key must functionally determine all attributes in R and be minimal.