Again, consider the [[[functionalbasics#tab-inventory-example Inventory table above]. Here is what we know. The relation R is Inventory. The set of attributes A is [Part #, Part Name, Supplier, Address, Bin]. Each of the following are a superkey S:
* [Part #, Part Name, Supplier, Address, Bin],
* [Part #, Supplier],
* [Part #, Part Name, Supplier],
Verify for yourself that each of the above are superkeys. You will generally see superkeys designated as a set of sets; for example, you might see [[Part #, Part Name, Supplier, Address, Bin], [Part #, Supplier], [Part #, Part Name, Supplier], …].
The following is the set of all candidate identifiers (again, designated as a set of sets): [[Part #, Supplier]]. This tells us that there is only one candidate identifier, [Part #, Supplier], and that this one candidate identifier is made up of two attributes, Part # and Supplier. How do we know that this is a candidate identifier? Looking at the definition you can see that you need to verify that this pair is a superkey (you did that above) and that it cannot be further reduced to another superkey. This is equivalent to asking if either Part # or Supplier can be a superkey. (Why?) This is equivalent to asking if either can functionally determine all the attributes in Inventory. (Why?) The strategy for doing this is to try to disprove that either single attribute is a superkey. (If we can't disprove it, then we have to assume that it's true. I'm hoping that you understand this.) This means that we will be looking for something to tell us that one of these two attributes does not functionally determine some other attribute in Inventory. And this is equivalent to looking for two separate rows in which one of the attributes is the same but the other attribute is different. (Why is this equivalent?)
- Part #
- What you are looking for is something to tell you that Part # does not functionally determine some other attribute. That is, you're looking for two separate rows in which Part # is the same but the other attribute is different. Consider the two rows where Part # is equal to 4. Now look at the Supplier attribute and its values in those two rows. In one row the Supplier is Dicky Gere while in the other it is Kay Sera. This is our counter example that proves that Part # does not functionally determine Supplier — that is, if we know Part #, we do not know the value of Supplier. This means that Part # does not functionally determine all the attributes in Inventory which means that it is not a superkey for Inventory which means that Part # is not a candidate identifier for Inventory. Which was what we wanted. (Or W<SUP>5</SUP> for you math or logic geeks out there.) Wake up, wake up! Are you still with me?
- The process for Supplier is left as an exercise for you. Do it now.
Okay, so now we know that neither Part # nor Supplier are superkeys. You verified for yourself (above) that [Part #, Supplier] is a superkey. Putting these two facts together we now can say that [Part #, Supplier] is a candidate identifier. Are there others? No. (I'll leave verification of this to you.)
The following is a primary identifier: [Part #, Supplier]. Notice that this is not a set of sets. It is a single item, in this case, a two-attribute primary identifier. In this case, there are no other possible primary identifiers because there is only one candidate identifier. In all instances for all relations there will always be one and only one primary identifier (the one that you have arbitrarily designated as such) though in some cases (but not this one since there is only one candidate identifier) alternatives could have been chosen.