Hello and welcome.
This is Bob Lessick at John Hopkins.
In this lecture, we will examine primary
and
secondary keys and how they operate as
unique identifiers.
We will look into relational databases.
By connecting smaller databases,
information can be divided
without an excessive burden on a single
database.
Most biological databases are set up in
this manner.
I recognize that many of us are coming
from very different backgrounds.
For those very familiar with databases,
this
should serve as a pretty short review.
For those less familiar, I hope to give
you a good introduction.
After viewing this lecture, we should be
able to define some terms
such as field, record, relational
database,
as well as primary and secondary key.
Be able to find a primary key when you
look at a database.
It's usually an identifier.
It's usually a series of numbers or
letters.
It's not data.
Look at the set up of a relational
database, which is a series of linked
databases.
Finally, explain how a secondary key in
one database can serve
as the primary key in another database,
usually a linked database.
Once of the simplest forms of a database
is a spreadsheet.
It's basically a grid with rows and
columns.
There was an old spreadsheet program Lotus
1-2-3 for those who remember.
The most commonly used spreadsheet today
is Microsoft Excel.
Usually the columns represent a particular
type of data.
That is a field, one of our key terms for
this lecture.
The column header usually describes the
field.
Name, Birth date, account balance, a
particular statistic.
Fields are usually specific for certain
types of data.
For instance, a date field might be set up
in a desired format like month slash day
slash year.
We can then call the rows records,
sometimes known as entries.
Essentially, the terms are synonymous for
this purpose.
A record might be an individual person in
the directorate
or it might be a DNA sequence in a
biological database.
Our last definition is of a primary key.
Each record in a database should contain a
unique identifier.
It's often numerical or alphanumerical.
Alphanumerical simply means mixed letters
and numbers.
There should be no duplication when it
comes to a primary key.
One identifier should represent one and
only one record.
We will see examples shortly.
Here is some data from United States
presidential elections.
There are seven columns, cand is an
identifier for a presidential candidate.
Cand/year is an identifier for a candidate
in a particular election.
Candidate is the name of the candidate.
That's data.
That's not an identifier.
Year is also data.
Party represents data.
For which party was that candidate
affiliated?
EV stands for electoral votes.
The candidate with 270 or more is the
winner of that election.
Pop vote stands for popular vote, which
does not decide elections.
The first question to think about is what
if the primary key in this database?
You should only have two choices, only two
columns are identifiers.
Those two columns are marked cand and
cand/year.
How are they different?
Which is the primary key?
The primary key is actually cand/year.
For instance, 1992d1 means the Democratic
candidate
in 1992, so that's William Jefferson
Clinton.
Each record represents a vote total in a
particular year.
So cand/year is unambiguous.
Cand is what we call a secondary key, that
represents one candidate.
That candidate could have run more than
once.
So we can see how it differs from cand in
uniqueness.
The cand identifier represents the
candidate and cand/year
represents the candidate's vote total of
the year.
So it's more specific to the record in
this
case and is therefore the primary key in
this database.
Here is one of the most popular databases
on the internet.
And I'll use it to illustrate in a crude
way how to set up a database.
That's the Internet Movie Database.
Many of you are probably familiar with
IMDB.
But, if not, it's a very commonly used
website.
And it's basically a relational database.
This is the movie page for the 2014 movie
Godzilla.
Now, there are many movies with the title
Godzilla.
So how do I click the right one?
Well, here's where you could use a primary
key.
And when you click the link marked
Godzilla 2014,
you're actually accessing a primary key
without even knowing it.
The primary key is right here on this
page, if you know where to find it.
Where is that primary key?
It's right in the URL.
You see, imdv.com/title/tt.
And then, it's small, but you can find it
yourself on the web.
You see an identifier 0831387.
And essentially, that's a primary key in
the movie title database.
Now a movie database might look something
like this, but I've really over simplified
it.
I'm not going to suggest that IMDB is
based on a simple spreadsheet.
But let's take a look at this data on the
bottom.
Primary key, in this case is ident, which
lists an actors work in a particular
movie.
A secondary key is movie id.
So, you see movie ID, that's the number we
saw on the last slide, 831387.
I left off the preceding 0.
That's a primary key in the movie title
database, but if this is a movie role
database, that would be a secondary key
because
there are several actors in the same
movie.
I only listed three.
You might also notice that actorid is also
a secondary key.
If you notice the 300 identifier appears
twice for Juliette Binoche.
The movieid field essentially in IMDB
become
accessible through links, so that when you
click on Godzilla 2014, you're really
clicking on the URL that has the 831387.
You're actually clicking on a primary key
in the movie title database.
You'll be clicking on a secondary key in
this particular database.
So let's look at that database that we've
already seen.
That's at the top.
And in particular, let's look at the actor
Aaron Taylor-Johnson.
Now, we don't want to put all of this
information,
into this top database, because this is
movie roles.
That might become too cumbersome.
So, we take that secondary key in the top
database and
make it a primary key in the actor
database at the bottom.
So, we see Aaron Taylor-Johnson was born
on June 30th, 1990 in England.
And you could get a whole bunch of other
information into that database, but that's
the basic idea.
We can link databases together by taking a
secondary key in one database or
in one relation and making it a primary
key in a related database or relation.
So basically, IMDB, like the biology
databases, tend to be relational databases
and the idea is to avoid too many fields
in one database.
The goal is to link smaller databases
together.
Each smaller database can be called a
relation.
The concept is that a secondary key in one
database can serve as a primary key in
another database.
So if you're on those movie roles and you
click the
actorid, that might link to the actor
database as a primary key.
We can think of the internet as one
large relational database because every
time we access
a page on the internet, essentially, it's
a
text file, sometimes with a lot of markup.
Whenever you click on a link, you're
clicking on a primary key to
the other database, which is that website
which contains its own set of information.
So, URLs essentially represent primary
keys.
Think about biological databases.
On a DNA sequence record, we might not
want to put
every single thing known about this DNA
sequence into one single record.
So we can link to a protein database to
access
proteins coded for by this DNA sequence or
maybe a
structure database for that protein
structure or a scientific literature
database for papers written about this
particular piece of DNA.
Relational or connected databases come in
very handy.
NCBI is a frequently used website and
database in bioinformatics.
It's one of the most used websites in the
world with millions of hits daily.
It is based at the National Library of
Medicine
at the National Institutes of Health in
Bethesda, Maryland.
What's there?
Well, go to the website and take a look
around.
You will find databases for scientific
literature,
DNA sequences, organism taxonomy, gene
expression data.
Well, quite a bit.
It's a huge relational database.
So basically to summarize, be able
recognize the primary key.
That's the most unique identifier and it's
usually a series of numbers and letters.
It's not data.
So don't look for, for instance, a vote
total, and say that's a primary key.
And relational databases are able to link
related data.
In the case of IMDB, it's a movie database
that can be linked to an actor database.
But at NCBI we can see that a
nucleotide database can be linked to a
protein database
and also linked to a literature database
and
a structure database and a host of other
databases.
I hope this lecture on databases is
helpful
to understanding the concept of databases
and good luck.