loading...

Relational Database - How to locate candidate keys in a table

advename profile image Lars ・3 min read

Finding a tables candidate key's during the database design process without having the a lot or any mock up-data can be difficult sometimes. There's, however, one well known trick - but many guides use a simplified version which only works on basic tables. In this article, I want to demonstrate the correct version which works 99% of the times.

Before we start, we'll have a quick look what functional dependencies are.

Functional dependency

If a value of column A is associated with one value of column B, we say:

  • A determines B
  • or, B depends on A
  • or, A -> B

This is called functional dependency.

Find the candidate key of a Songs table

Let's locate the candidate key's of this album - artist - song table.

album_name album_year artist_name artist_genre song_name song_duration

From the table above, we can tell that our dependencies are:

album_name -> album_year
artist_name -> artist_genre
song_name -> song_duration

Next step is to illustrate these dependencies with a drawing.
Locate candidate key relational database

Based on the drawing, we can tell that we need album_name, artist_name and song_name to get all attributes.

Therefore, the candidate key of our table is:
{album_name, artist_name, song_name}

Drawing method explained

By following the arrows, we try to get to every attribute using one or multiple attributes as a starting point.
For the example from above, we have to start from album_name, artist_name and song_name to get to every attribute.

An attribute that is not connected to any other attribute signals that this attribute is already part of the candidate key.

Let's try more examples.

Example 1

Movie(title, year, genre, genre-description, director, director-home-country)

If we would tabulate the table above, it would look like:

Movie

title year genre genre_desc director director_country

From first view, it's impossible to determine which is the candidate key.

But we do know the dependencies:

title -> year
title -> genre
title -> director
genre -> genre-description
director -> director-home-country

So, lets draw the dependencies:
Locate candidate key relational database

We only need the attribute title to determine all other attributes.
Therefore, {title} is the candidate key.

Example 2

Now, lets do an example without obvious names.

R(A, B, C, D, E, F, G)

Dependencies:

A -> B
B  -> {A, C, E}
C -> {B, F, D}
F -> {D, G}

Curly brackets are just a short hand for a group of attributes with a functional dependency to another (group) attribute.

Lets draw once again, the dependencies:
Locate candidate key relational database

By analyzing this drawing, we can get to every attribute starting from A, B and C.

Explanation with starting from C:
C -> {B, F, D}
then, B -> {A, C, E}
then, F -> {D, G}

We have covered all attributes only by using C.

Candidate keys: {A}, {B} or {C}

Example 3

R = (A, B, C, D, E)

Dependencies:

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

Drawing:
Locate candidate key relational database

There's no arrow ever going to D and C. This signals that D and C are already a part of our candidate keys.
However, we need to combine {D, C} with either A, B or E to get all attributes.

Candidate keys: {D, C, A} or {D, C, B} or {D, C, E}

Source:

Posted on by:

Discussion

pic
Editor guide