Keys

Again what is Primary Key? Unique - Not Null - One PK per table.

Super Key - A super key is a set or one or more columns (attributes) to identify rows in a table uniquely.

Candidate Key - Minimal super keys with no redundant attributes to uniquely identify a row.

Primary Key - Primary key is selected from the sets of candidate keys.

Alternate Key – A candidate key that was not selected as Primary Key.

Natural Key – A key that happens naturally in the table. Example: SSN, TaxID.

Surrogate Key – A system-generated identifier (Meaningless Simple Number).

Unique Key – Unique values one NULL.

Foreign Key – used to relate other tables.

Super Key

Super Key is a set of one or more columns (attributes) to identify rows in a table uniquely.

For the above table, a bag of keys will help us uniquely identify that table's rows.

{ID}
{EmpNum}
{SSN}
{Email}
{ID,EmpNum}
{ID,SSN}
{ID,Email}
{ID,DOB}
{EmpNum,SSN}
{EmpNum,SSN,Email}
{ID, EmpNum, SSN}
{ID, EmpNum, SSN, DOB}
{ID, EmpNum, SSN, DOB, Name}
{ID, EmpNum, SSN, Name, Email,DOB}
...

Now you get the idea.. we can come up with more & more combinations.

Candidate Key

"Minimal super keys" with no redundant attributes to uniquely identify a row.

Here the essential condition is Minimal. Let's see what minimal keys help us identify a row uniquely.

With just ID, we can uniquely identify a row. Similarly, EmpNum, SSN, and Email can uniquely pull a row.

{ID}

{EmpNum}

{SSN}

{Email}

Primary Key

The primary key is selected from the sets of candidate keys. Let's choose the best column as Primary Key through the process of elimination.

SSN: Sensitive
Email: Varchar
EmpNum: Good to use when needed.
ID: Auto Increment. Widely used in Data Warehousing.

Alternate Key

A candidate key should have been selected as Primary Key.

Email is best suited for Alternate Key. Email can be used to search particular employees if the system doesn’t have access to EmpNum. It is used to link to External accounts.

Surrogate Key

Surrogate Key: A system-generated identifier (Meaningless Simple Number)

ID column matches the definition.

Surrogate Keys are very handy and useful in Data Warehousing.

Unique Key

Unique values may have one NULL value.

Under certain circumstances, Email can be a unique key. Assuming email is generated by a different system and it can be null for a brief period.

Natural Key

A natural key is a column or set of columns that already exist in the table (e.g., they are attributes of the entity within the data model) and uniquely identifies a record in the table. Since these columns are entity attributes, they have business meaning.

Example: SSN

Foreign Key

It is used to refer to other tables.