sqldatabasefunctional-dependenciescandidate-key

Are determinants and candidate keys the same?


At https://web.archive.org/web/20130514174856/http://databases.about.com/cs/specificproducts/g/determinant.htm I found this by Mike Chapple:

Definition: A determinant in a database table is any attribute that you can use to determine the values assigned to other attribute(s) in the same row.

Examples: Consider a table with the attributes employee_id, first_name, last_name and date_of_birth. In this case, the field employee_id determines the remaining three fields. The name fields do not determine the employee_id because the firm may have more than one employee with the same first and/or last name. Similarly, the DOB field does not determine the employee_id or the name fields because more than one employee may share the same birthday.

Isn't the definition applicable for candidate keys too?


Solution

  • A determinant is the left side set of attributes of a FD (functional dependency). But it might not be a CK (candidate key). A determinant isn't a CK for

    • a trivial FD that isn't of the form CK -> subset of CK
    • some FD(s) when a table is not in BCNF--because BCNF is when every determinant of a non-trivial FD is a superset of a CK.

    Consider this (obviously non-BCNF) table:

    CREATE TABLE US_Address (
      AddressID int,
      Streetline varchar(80),
      City varchar(80),
      State char(2),
      ZIP char(5),
      StateName varchar(80),
      StateTax DECIMAL(5,2)
    )
    

    {State} is a determinant for {StateName, StateTax}, but it is not a CK.

    Normalization to BCNF would move StateName and StateTax out of the US_Address table into a States table with State.