Can any kind soul clarify my doubts with a simple example below and identify the superkey, candidate key and primary key?
I know there are a lot of posts and websites out there explaining the differences between them. But it looks like all are generic definitions.
Example:
Student (StudentNumber, FamilyName, Degree, Major, Grade, PhoneNumber)
So from the above example, I can know StudentNumber is a primary key.
But as for superkey, I’m a bit confused what combination of attributes could be grouped into the superkey?
As for candidate key, I’m confused by the definition given as any candidate key can qualify as a primary key.
Does it mean that attributes such as PhoneNumber are a candidate key and can be a primary key? (Assuming that a PhoneNumber only belongs to one student)
Thanks for any clarification!
Advertisement
Answer
Since you don’t want textbook definitions, loosely speaking, a super key is a set of columns that uniquely defines a row.
This set can have one or more elements, and there can be more than one super key for a table. You usually do this through functional dependencies.
In your example, I’m assuming:
StudentNumber unique FamilyName not unique Degree not unique Major not unique Grade not unique PhoneNumber not unique
In this case, a superkey is any combination that contains the student number.
So the following are superkeys
StudentNumber StudentNumber, FamilyName StudentNumber, FamilyName, Degree StudentNumber, FamilyName, Degree, Major StudentNumber, FamilyName, Degree, Major, Grade StudentNumber, FamilyName, Degree, Major, Grade, PhoneNumber StudentNumber, Degree StudentNumber, Degree, Major StudentNumber, Degree, Major, Grade StudentNumber, Degree, Major, Grade, PhoneNumber StudentNumber, Major StudentNumber, Major, Grade StudentNumber, Major, Grade, PhoneNumber StudentNumber, Grade StudentNumber, Grade, PhoneNumber StudentNumber, PhoneNumber
Now assume, if PhoneNumber is unique (who shares phones these days), then the following are also superkeys (in addition to what I’ve listed above).
PhoneNumber PhoneNumber, Grade, PhoneNumber, Major, Grade PhoneNumber, Degree, Major, Grade PhoneNumber, FamilyName, Degree, Major, Grade PhoneNumber, Major PhoneNumber, Degree, Major PhoneNumber, FamilyName, Degree, Major PhoneNumber, StudentNumber, FamilyName, Degree, Major PhoneNumber, Degree PhoneNumber, FamilyName, Degree PhoneNumber, StudentNumber, FamilyName, Degree PhoneNumber, FamilyName PhoneNumber, StudentNumber, FamilyName
A candidate key is simply the “shortest” superkey. Going back to the 1st list of superkeys (i.e. phone number isn’t unique), the shortest superkey is StudentNumber.
The primary key is usually just the candidate key.