Are Determinants and Candidate Keys same or different things?

Question

Here I found this:

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 1

From my understanding, a determinant may not be a candidate key if the table is not fully normalized. In fact, the word determinant is used when describing the process of taking non-normal data to a more useful, normalized form.

Consider this (obviously non-normal) 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 and StateTax, but it is not a candidate key for the row. Proper normalization, would therefore move StateName and StateTax out of the US_Address table and into a States table.

See here for more information.

Solution 2

TL;DR No, "determinant" and "candidate key" are not the same concept. A determinant is of a FD. A CK is of a table. We can also reasonably say sloppily that a CK is a determinant (of a FD) of its table since it determines every column & column set in it.

---

All the following terms/concepts are defined in parallel for table values and variables. A table variable has an instance of a FD (functional dependency), determinant, superkey, CK (candidate key) or PK (primary key) (in the variable sense) when every table value that can arise for it in the given business/application has that instance (in the table sense).

For sets of columns X and Y we can write X -> Y. We say that X is the determinant/determining set and Y is the determined set of/in functional dependency (FD) X -> Y.

We say X functionally determines Y and Y is functionally determined by X. We say X is the determinant of X -> Y. In {C} -> Y we say C functionally determines Y. In X -> {C} we say X functionally determines C. When X is a superset of Y we say X -> Y is trivial.

We say X -> Y holds in table T when each subrow value for X only appears with the one particular subrow value for Y. Or we say X -> Y is a FD of/in T. When X is a determinant of some FD in table T we say X is a determinant of/in T. Every trivial FD of a table holds in it.

A superkey of a table T is a set of columns that functionally determines every column. A candidate key (CK) is a superkey that contains no smaller superkey. We can pick one CK as primary key (PK) and then call the other CKs alternate keys (AKs). A column is prime when it is in some CK.

Note that a determinant can be of a FD or, sloppily, of (a FD that holds in) a table. Every CK is a determinant of its table. (But then, in a table every set of columns is a determinant: of itself, trivially. And similarly every column.)

(These definitions do not depend on normalization. FDs and CKs of a table are used in normalizing it. A table is in BCNF when every determinant of a non-trivial FD that holds in it is a superkey.)

SQL tables are not relations and SQL operators are not their relational/mathematical counterparts. Among other things, SQL has duplicate rows, nulls & a kind of 3-valued logic. But although you can borrow terms and give them SQL meanings, you can't just substitute those meanings into other RM definitions or theorems and get something sensible or true. So we must convert an SQL design to a relational design, apply relational notions, then convert back to SQL. There are special cases where we can do certain things directly in SQL because we know what would happen if we did convert, apply & convert back.

Solution 3

• A primary key or any candidate key is also a determinant while the opposite is not true.
• A determinant can uniquely determine one or more attributes in the row.
• A candidate key can uniquely determine the entire row.

---

Taking an example from here, let there be a table with following columns:

Customer #, Name, Address, Credit, Sales Rep #, Sales Rep Name

and let's say that the Sales Rep # can uniquely determine the Sales Rep Name. Thus, Sales Rep # is a determinant for Sales Rep Name but is not a candidate key for this table.

Comments

[philipxy]

Even if a table is in BCNF, every subset of attributes is a determinant. What is correct is "A [non-trivial FD's] determinant may not be [a superset of] a candidate key if the table is not [in BCNF] [but otherwise is]".

[philipxy]

Also, that link unusually defines "determinant" (in a table) as "determinant of a full functional dependency". And its "A relation is in BCNF if, and only if, every determinant [sic] is a candidate key" should be "every non-trivial determinant [sic]".

[Ross Presser]

The definitions do not depend on normalization, but I'll wager they were invented explicitly to make it possible to define normalization. Your definitions are very precise and mathematical. But while they carry very valuable information, reading them is much harder than my answer, incorrect though it was. My answer quickly got the OP to understand that they were different concepts and where the different concepts would be useful. Your answer is more about formal proof.

[philipxy]

The terms appear in the question and your answer but the meanings aren't in the asker's mind. I tried to address that. Hence definitions. They are simple and correct yet not vague. The four boldface sentences for FD, holds, superkey and CK would have sufficed. I added some related terms/notions. My choice reflects my doubt that difference can be appreciated between things without clear understanding of just what they are.