Superkey, candidate key & primary key

Question

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!

Hundredth

In the Relational Model, we have Primary Key and Alternate Key, and that is all that is needed. There is no such thing as superkey or candidate key. Those are inventions of people who complicate and thus destroy Relational theory. – Alumina

Answer 1

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.

Unscramble

one question, so if phonenumber is unique, it can be counted as candidate key right ? For any attributes that are unique can be counted as candidate key? Thanks – Hundredth

yes, in that case, there are two superkeys composed of only one element: {phone number} and {student number}, and either could be the candidate key. – Unscramble

A CK is not "the" "shortest" superkey. It is a superkey that contains no smaller superkey. There can be more than one. The CKs can have different numbers of attributes.. – Smukler

So, we are considering the minimal superkey (that is unique) as the candidate key, right ? Does that mean a table can't have more than one candidate key @Unscramble – Palimpsest

A table can have any number of CK's and the most eligible CK is made the primary key. A Candidate Key is very much like a Primary key. – Organography

Adding to the JLearner's comment. phonenumber can be unique but column also needs to be not null to be considered as candidate key. – Coverley

Answer 2

A superkey is any set of attributes for which the values are guaranteed to be unique for all possible sets of tuples in a table at all times.

A candidate key is a "minimal" superkey - meaning the smallest subset of superkey attributes which are unique. Removing any attribute from a candidate key would therefore make it non-unique.

A primary key is just a candidate key. There is no difference between a primary key and any other candidate key.

It's not really useful to make assumptions about keys based only on a list of attribute names. You need to know what dependencies are supposed to hold between the attributes. Having said that, my guess is that you are right - StudentNumber is likely a candidate key in your example.

Exscind

Answer 3

Stretching Cambium's answer, if the PhoneNumber is also unique along with StudentNumber then candidate keys would be:- {StudentNumber},{PhoneNumber}.
Here we can't assume {StudentNumber,PhoneNumber} as a single candidate key because if we omit one attribute say StudentNumber we still get a unique attribute{PhoneNumber} thus, violating the definition of candidate key.

Primary key: Choose one candidate key out of all candidate keys. There are 2 candidate keys so we can choose {StudentNumber} as primary key.
Alternate keys: leftover candidate keys, after choosing primary key from candidate keys, are alternate keys i.e. {PhoneNumber}.

compound key: a compound key is a key that consists of two or more attributes that uniquely identify an entity occurrence. A simple key is one that has only one attribute. Compound keys may be composed of other unique simple keys and non-key attributes, but may not include another compound key.

composite key: A composite key contains at least one compound key and one more attribute. Composite keys may also include simple keys and non-key attributes.

Effulgent