Are determinants and candidate keys the same?
Asked Answered
A

3

9

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?

Asante answered 23/5, 2013 at 6:5 Comment(0)
D
21

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.

Defame answered 23/5, 2013 at 6:29 Comment(9)
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]".Reside
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]".Reside
This is not BCNF. your example is about 3NF which is about transitive functional dependencyAllegraallegretto
would you recommend I delete my answer in favor of the bountied one by @Reside ?Defame
I just edited your post more or less minimally to correct & clarify. You can roll it back by clicking on 'edited' then clicking on 'rollback' of an earlier version. PS "normalized", "fully normalized", "non-normal" & "proper normalization" don't mean anything in particular. However BCNF is free of all update anomalies due to FDs & is when CKs do need to be the only determinants that aren't of non-trivial FDs. Also most of your writing was unclear. (Did "may not" mean might not or cannot?) Don't give or quote the bad link you give now.Reside
@AmirAlmian Your comment is not clear. Please rephrase. (However as of now I have edited the non-code of the post.) A table that is not in 3NF is also not in BCNF so what was/is the problem? Anyway the example was indeed an example of a CK that wasn't a determinant, so why would particular NFs matter? Did you think that the text said something wrong about NFs?Reside
@Reside I said your table is not even in 3NF to be BCNF. In the 3NF process you would move the StateName and StateTax out of the US_Address table into a States table. I mean this would be done in 3NF process not BCNF.Allegraallegretto
@AmirAlmian "not even in 3NF to be BCNF" is not clear, it isn't English. PS BCNF implies 3NF. Therefore normalizing to BCNF normalizes to 3NF. So any process that normalizes ot BCNF is also a process that normalizes to 3NF. It is OK to do to get 3NF (plus BCNF). Normalizing to 3NF while avoiding certain kinds of normalizations to 3NF is not "normalizing to 3NF", it is "normalizing to 3NF with some extra requirement". What is it? Notice that for some cases every normalization to 3NF is also BCNF. So when you say normalizing to 3NF that is not what you mean. So--What exactly do you mean?Reside
@AmirAlmian Please just try to write a clear version of what you were trying to say in your first comment & delete your last 2 comments. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. Notice that I edited the post after your 1st comment. The post said unclear & wrong things before that.Reside
R
10

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.

Reside answered 10/12, 2015 at 0:23 Comment(2)
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.Defame
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.Reside
I
6
  • 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.

Inchmeal answered 3/12, 2015 at 6:17 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.