Are Determinants and Candidate Keys Same or Different Things

Are determinants and candidate keys the same?

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.

What is the difference between a candidate key and a primary key?

Candidate Key – A Candidate Key can be any column or a combination of columns that can qualify as unique key in database. There can be multiple Candidate Keys in one table. Each Candidate Key can qualify as Primary Key.

Primary Key – A Primary Key is a column or a combination of columns that uniquely identify a record. Only one Candidate Key can be Primary Key.

More on this link with example

Is a candidate key determinant good enough for BCNF?

What does the question mean by "part"? Some but not all of? Some or all of? What do you mean by it?

The definition of partial functional dependency uses "partial" to mean some but not all of.

Check definitions of BCNF. The one that requires that no non-prime attribute be partially functionally dependent on any key also requires other things than you wrote. So your "if" is not correct. But if the assignment question's "part" means "part of but not all of" as in "partially dependent" then by the relevant definition of BCNF the relation is not in BCNF.

All candidate keys are superkeys. But the candidate keys are the superkeys that do not contain any smaller superkeys. Superkeys are involved in a different form of the definition of BCNF. But if BCNF is violated according to one definition and what you know, it can't possibly be allowed by another one.

finding largest number of candidate keys that a relation has?

Sets that are not subsets of other sets.

For example {A-B} and {A,B,C} can't be candidates keys simultaneously, because {A,B} is a subset of {A,B,C}.

Combinations of 2 attributes or 3 attributes generates the maximum number of simultaneous candidates keys.

See how the 3 attributes sets are actually complements of the 2 attributes sets, e.g. {C,D,E} is the complement of {A,B}.

         2               3    
     attributes      attributes
       sets            sets

   1.  {A,B}    -     {C,D,E}
   2.  {A,C}    -     {B,D,E}
   3.  {A,D}    -     {B,C,E}
   4.  {A,E}    -     {B,C,D}
                -     
   5.  {B,C}    -     {A,D,E}
   6.  {B,D}    -     {A,C,E}
   7.  {B,E}    -     {A,C,D}
                -     
   8.  {C,D}    -     {A,B,E}
   9.  {C,E}    -     {A,B,D}
                -     
   10. {D,E}    -     {A,B,C}

---

If I would take sets of a single attribute I would have only 4 options

{A},{B},{C},{D}

Any set with more than 1 element will contain one of the above and therefore will not be qualified.

If I would take sets of 4 attributes I would have only 4 options

{A,B,C,D},{A,B,C,E},{A,B,D,E},{B,C,D,E}

Any set with more than 4 element will contain one of the above and therefore will not be qualified.
Any set with less than 4 element will be contained by one of the above and therefore will not be qualified.

etc.

What's the point of a candidate key?

It means that if PhoneNumber was indeed a candidate key you could delete the ID column and use PhoneNumber instead. In other words, it is a candidate for being a unique key.

Wikipedia has a more formal definition that you many want to look at.

BCNF: Looking for example that actually uses superkey instead of candidate key

The point is that, when defining a normal form, we must express it in a general form, as a property of all the functional dependencies holding on a certain relation.

Instead, when we reason about a particular relation schema, we usually have only a subset of all the functional dependencies (since their number can be too large, being possibly exponential with the number of attributes). The particular set of dependencies used, denoted usually by the letter F, has a special property: it is a cover of all the dependencies holding in the relation, that is from it we can derive all the dependencies of the relation by applying, in all the possible ways, a set of axioms, called Armstrong’s axioms.

F, the set of dependencies specified together with the attributes in a relational schema, can be given in different ways: for instance in an exercise they can be given as input to the exercise, in real database design they can describe a set of constraints considered important for modelling a certain real-word domain, etc.

Even if they are extracted from the knowledge about a situation to be modeled through a database, they could contain dependencies implied by others already given, or maybe can contain redundant attributes, etc.

For these reasons, it is considered an important first step in normalization to find the canonical cover of the set of dependencies given, that is a cover constituted by a set of dependencies that: a) have only a single attribute on the rigth part; b) do not have superflous attributes on the left part (i.e. attributes that can be removed maintaing the property of being a cover); c) do not have redundant dependencies (i.e. dependencies that can be derived from others through the Armstrong’s axioms).

Now let’s consider the general definition of BCNF:

A relation schema R<T,F> is in BCNF if and only if for each non-trivial dependency X → Y of F⁺, X is a superkey.

Note that the we are talking about the dependencies in F⁺, which is the closure of F, in other words, which contains all the dependencies holding in R and derived in some way from F. So if the relation R has a candidate key X_K, obviously not only X_K → T holds, for instance, but for all the supersets S of X_K we will have that S → T holds, and so the definition of the normal form must allow those dependencies.

Now, it is possible to prove, from the general definition of BCNF, the following theorem, that in some way simplifies it (and makes efficient the test to check if a relation is already in BCNF):

Theorem: A relation schema R<T,F> is in BCNF if and only if for each non-trivial dependency X → Y of F, X is a superkey.

See the difference? We are now talking about F and not F⁺.

And this theorem has the following corollary:

Corollary: A relation schema R<T,F> in which F is a canonical cover, is in BCNF if and only if for each non-trivial dependency X → A of F, X is a candidate key.

Since the dependencies in a canonical cover do not have superfluous attributes, if the relation is in BCNF every determinant (left hand side of a functional dependency) is obviously a candidate key (not a generic superkey), and this explain the difference between the definition and the examples that sometimes we found on the books.

---

Related Topics