1
00:00:01,370 --> 00:00:02,220
Hello and welcome.

2
00:00:02,220 --> 00:00:04,800
This is Bob Lessick at John Hopkins.

3
00:00:04,800 --> 00:00:07,120
In this lecture, we will examine primary
and

4
00:00:07,120 --> 00:00:11,090
secondary keys and how they operate as
unique identifiers.

5
00:00:12,150 --> 00:00:13,980
We will look into relational databases.

6
00:00:15,210 --> 00:00:19,070
By connecting smaller databases,
information can be divided

7
00:00:19,070 --> 00:00:21,389
without an excessive burden on a single
database.

8
00:00:22,470 --> 00:00:25,120
Most biological databases are set up in
this manner.

9
00:00:27,060 --> 00:00:31,100
I recognize that many of us are coming
from very different backgrounds.

10
00:00:31,100 --> 00:00:33,700
For those very familiar with databases,
this

11
00:00:33,700 --> 00:00:35,190
should serve as a pretty short review.

12
00:00:36,260 --> 00:00:39,180
For those less familiar, I hope to give
you a good introduction.

13
00:00:42,590 --> 00:00:46,170
After viewing this lecture, we should be
able to define some terms

14
00:00:46,170 --> 00:00:50,060
such as field, record, relational
database,

15
00:00:50,060 --> 00:00:52,080
as well as primary and secondary key.

16
00:00:54,340 --> 00:00:57,359
Be able to find a primary key when you
look at a database.

17
00:00:58,960 --> 00:01:00,780
It's usually an identifier.

18
00:01:00,780 --> 00:01:04,080
It's usually a series of numbers or
letters.

19
00:01:04,080 --> 00:01:04,870
It's not data.

20
00:01:07,260 --> 00:01:09,015
Look at the set up of a relational

21
00:01:09,015 --> 00:01:11,850
database, which is a series of linked
databases.

22
00:01:14,010 --> 00:01:17,985
Finally, explain how a secondary key in
one database can serve

23
00:01:17,985 --> 00:01:22,390
as the primary key in another database,
usually a linked database.

24
00:01:26,170 --> 00:01:29,810
Once of the simplest forms of a database
is a spreadsheet.

25
00:01:29,810 --> 00:01:32,090
It's basically a grid with rows and
columns.

26
00:01:33,380 --> 00:01:36,890
There was an old spreadsheet program Lotus
1-2-3 for those who remember.

27
00:01:36,890 --> 00:01:41,150
The most commonly used spreadsheet today
is Microsoft Excel.

28
00:01:44,260 --> 00:01:48,300
Usually the columns represent a particular
type of data.

29
00:01:48,300 --> 00:01:51,240
That is a field, one of our key terms for
this lecture.

30
00:01:52,910 --> 00:01:55,780
The column header usually describes the
field.

31
00:01:55,780 --> 00:01:58,940
Name, Birth date, account balance, a
particular statistic.

32
00:02:01,020 --> 00:02:03,790
Fields are usually specific for certain
types of data.

33
00:02:04,830 --> 00:02:07,110
For instance, a date field might be set up

34
00:02:07,110 --> 00:02:11,250
in a desired format like month slash day
slash year.

35
00:02:14,040 --> 00:02:17,919
We can then call the rows records,
sometimes known as entries.

36
00:02:18,920 --> 00:02:21,520
Essentially, the terms are synonymous for
this purpose.

37
00:02:23,120 --> 00:02:26,864
A record might be an individual person in
the directorate

38
00:02:26,864 --> 00:02:30,490
or it might be a DNA sequence in a
biological database.

39
00:02:33,110 --> 00:02:35,340
Our last definition is of a primary key.

40
00:02:36,930 --> 00:02:40,069
Each record in a database should contain a
unique identifier.

41
00:02:41,170 --> 00:02:43,500
It's often numerical or alphanumerical.

42
00:02:44,840 --> 00:02:47,450
Alphanumerical simply means mixed letters
and numbers.

43
00:02:49,080 --> 00:02:52,090
There should be no duplication when it
comes to a primary key.

44
00:02:53,520 --> 00:02:57,310
One identifier should represent one and
only one record.

45
00:02:58,660 --> 00:03:00,340
We will see examples shortly.

46
00:03:04,560 --> 00:03:07,170
Here is some data from United States
presidential elections.

47
00:03:08,550 --> 00:03:14,005
There are seven columns, cand is an
identifier for a presidential candidate.

48
00:03:14,005 --> 00:03:20,120
Cand/year is an identifier for a candidate
in a particular election.

49
00:03:22,010 --> 00:03:24,380
Candidate is the name of the candidate.

50
00:03:24,380 --> 00:03:24,930
That's data.

51
00:03:24,930 --> 00:03:25,980
That's not an identifier.

52
00:03:27,770 --> 00:03:28,880
Year is also data.

53
00:03:30,720 --> 00:03:32,060
Party represents data.

54
00:03:32,060 --> 00:03:34,040
For which party was that candidate
affiliated?

55
00:03:36,090 --> 00:03:38,990
EV stands for electoral votes.

56
00:03:38,990 --> 00:03:42,320
The candidate with 270 or more is the
winner of that election.

57
00:03:44,480 --> 00:03:48,480
Pop vote stands for popular vote, which
does not decide elections.

58
00:03:51,250 --> 00:03:55,560
The first question to think about is what
if the primary key in this database?

59
00:03:55,560 --> 00:03:58,359
You should only have two choices, only two
columns are identifiers.

60
00:04:01,190 --> 00:04:05,720
Those two columns are marked cand and
cand/year.

61
00:04:05,720 --> 00:04:07,380
How are they different?

62
00:04:07,380 --> 00:04:08,690
Which is the primary key?

63
00:04:11,930 --> 00:04:14,195
The primary key is actually cand/year.

64
00:04:16,100 --> 00:04:19,568
For instance, 1992d1 means the Democratic
candidate

65
00:04:19,568 --> 00:04:22,770
in 1992, so that's William Jefferson
Clinton.

66
00:04:24,880 --> 00:04:27,560
Each record represents a vote total in a
particular year.

67
00:04:27,560 --> 00:04:29,660
So cand/year is unambiguous.

68
00:04:31,500 --> 00:04:34,399
Cand is what we call a secondary key, that
represents one candidate.

69
00:04:35,540 --> 00:04:38,140
That candidate could have run more than
once.

70
00:04:39,890 --> 00:04:43,860
So we can see how it differs from cand in
uniqueness.

71
00:04:43,860 --> 00:04:46,928
The cand identifier represents the
candidate and cand/year

72
00:04:46,928 --> 00:04:50,150
represents the candidate's vote total of
the year.

73
00:04:50,150 --> 00:04:52,958
So it's more specific to the record in
this

74
00:04:52,958 --> 00:04:56,630
case and is therefore the primary key in
this database.

75
00:05:00,970 --> 00:05:03,860
Here is one of the most popular databases
on the internet.

76
00:05:03,860 --> 00:05:08,000
And I'll use it to illustrate in a crude
way how to set up a database.

77
00:05:09,210 --> 00:05:11,500
That's the Internet Movie Database.

78
00:05:11,500 --> 00:05:14,420
Many of you are probably familiar with
IMDB.

79
00:05:14,420 --> 00:05:16,970
But, if not, it's a very commonly used
website.

80
00:05:16,970 --> 00:05:18,950
And it's basically a relational database.

81
00:05:20,980 --> 00:05:24,040
This is the movie page for the 2014 movie
Godzilla.

82
00:05:25,790 --> 00:05:28,630
Now, there are many movies with the title
Godzilla.

83
00:05:28,630 --> 00:05:30,000
So how do I click the right one?

84
00:05:32,190 --> 00:05:34,119
Well, here's where you could use a primary
key.

85
00:05:35,330 --> 00:05:38,688
And when you click the link marked
Godzilla 2014,

86
00:05:38,688 --> 00:05:42,940
you're actually accessing a primary key
without even knowing it.

87
00:05:45,720 --> 00:05:48,450
The primary key is right here on this
page, if you know where to find it.

88
00:05:49,490 --> 00:05:50,840
Where is that primary key?

89
00:05:54,050 --> 00:05:55,240
It's right in the URL.

90
00:05:56,320 --> 00:05:59,780
You see, imdv.com/title/tt.

91
00:05:59,780 --> 00:06:03,620
And then, it's small, but you can find it
yourself on the web.

92
00:06:03,620 --> 00:06:07,000
You see an identifier 0831387.

93
00:06:07,000 --> 00:06:10,850
And essentially, that's a primary key in
the movie title database.

94
00:06:13,940 --> 00:06:15,620
Now a movie database might look something

95
00:06:15,620 --> 00:06:17,800
like this, but I've really over simplified
it.

96
00:06:17,800 --> 00:06:22,520
I'm not going to suggest that IMDB is
based on a simple spreadsheet.

97
00:06:22,520 --> 00:06:24,670
But let's take a look at this data on the
bottom.

98
00:06:25,870 --> 00:06:28,980
Primary key, in this case is ident, which

99
00:06:28,980 --> 00:06:31,310
lists an actors work in a particular
movie.

100
00:06:34,480 --> 00:06:37,040
A secondary key is movie id.

101
00:06:37,040 --> 00:06:41,510
So, you see movie ID, that's the number we
saw on the last slide, 831387.

102
00:06:41,510 --> 00:06:45,070
I left off the preceding 0.

103
00:06:45,070 --> 00:06:49,998
That's a primary key in the movie title
database, but if this is a movie role

104
00:06:49,998 --> 00:06:53,155
database, that would be a secondary key
because

105
00:06:53,155 --> 00:06:56,099
there are several actors in the same
movie.

106
00:06:57,900 --> 00:06:58,890
I only listed three.

107
00:07:01,350 --> 00:07:06,230
You might also notice that actorid is also
a secondary key.

108
00:07:06,230 --> 00:07:10,840
If you notice the 300 identifier appears
twice for Juliette Binoche.

109
00:07:12,410 --> 00:07:15,108
The movieid field essentially in IMDB
become

110
00:07:15,108 --> 00:07:17,735
accessible through links, so that when you

111
00:07:17,735 --> 00:07:23,330
click on Godzilla 2014, you're really
clicking on the URL that has the 831387.

112
00:07:23,330 --> 00:07:28,130
You're actually clicking on a primary key
in the movie title database.

113
00:07:29,650 --> 00:07:33,650
You'll be clicking on a secondary key in
this particular database.

114
00:07:33,650 --> 00:07:35,920
So let's look at that database that we've
already seen.

115
00:07:35,920 --> 00:07:36,670
That's at the top.

116
00:07:38,060 --> 00:07:41,219
And in particular, let's look at the actor
Aaron Taylor-Johnson.

117
00:07:42,640 --> 00:07:45,091
Now, we don't want to put all of this
information,

118
00:07:45,091 --> 00:07:48,380
into this top database, because this is
movie roles.

119
00:07:48,380 --> 00:07:50,000
That might become too cumbersome.

120
00:07:51,470 --> 00:07:55,385
So, we take that secondary key in the top
database and

121
00:07:55,385 --> 00:07:59,639
make it a primary key in the actor
database at the bottom.

122
00:08:01,700 --> 00:08:05,150
So, we see Aaron Taylor-Johnson was born
on June 30th, 1990 in England.

123
00:08:05,150 --> 00:08:07,262
And you could get a whole bunch of other

124
00:08:07,262 --> 00:08:10,780
information into that database, but that's
the basic idea.

125
00:08:12,090 --> 00:08:16,616
We can link databases together by taking a
secondary key in one database or

126
00:08:16,616 --> 00:08:21,480
in one relation and making it a primary
key in a related database or relation.

127
00:08:23,230 --> 00:08:28,555
So basically, IMDB, like the biology
databases, tend to be relational databases

128
00:08:28,555 --> 00:08:32,190
and the idea is to avoid too many fields
in one database.

129
00:08:33,550 --> 00:08:36,190
The goal is to link smaller databases
together.

130
00:08:38,470 --> 00:08:40,859
Each smaller database can be called a
relation.

131
00:08:44,350 --> 00:08:46,288
The concept is that a secondary key in one

132
00:08:46,288 --> 00:08:49,900
database can serve as a primary key in
another database.

133
00:08:49,900 --> 00:08:52,420
So if you're on those movie roles and you
click the

134
00:08:52,420 --> 00:08:55,800
actorid, that might link to the actor
database as a primary key.

135
00:08:59,030 --> 00:09:00,738
We can think of the internet as one

136
00:09:00,738 --> 00:09:03,605
large relational database because every
time we access

137
00:09:03,605 --> 00:09:05,984
a page on the internet, essentially, it's
a

138
00:09:05,984 --> 00:09:08,310
text file, sometimes with a lot of markup.

139
00:09:10,250 --> 00:09:13,660
Whenever you click on a link, you're
clicking on a primary key to

140
00:09:13,660 --> 00:09:18,820
the other database, which is that website
which contains its own set of information.

141
00:09:18,820 --> 00:09:21,800
So, URLs essentially represent primary
keys.

142
00:09:23,160 --> 00:09:24,880
Think about biological databases.

143
00:09:26,360 --> 00:09:29,820
On a DNA sequence record, we might not
want to put

144
00:09:29,820 --> 00:09:34,190
every single thing known about this DNA
sequence into one single record.

145
00:09:35,660 --> 00:09:38,509
So we can link to a protein database to
access

146
00:09:38,509 --> 00:09:41,666
proteins coded for by this DNA sequence or
maybe a

147
00:09:41,666 --> 00:09:46,517
structure database for that protein
structure or a scientific literature

148
00:09:46,517 --> 00:09:50,830
database for papers written about this
particular piece of DNA.

149
00:09:52,510 --> 00:09:56,950
Relational or connected databases come in
very handy.

150
00:09:59,870 --> 00:10:04,380
NCBI is a frequently used website and
database in bioinformatics.

151
00:10:04,380 --> 00:10:08,250
It's one of the most used websites in the
world with millions of hits daily.

152
00:10:09,870 --> 00:10:12,060
It is based at the National Library of
Medicine

153
00:10:12,060 --> 00:10:14,810
at the National Institutes of Health in
Bethesda, Maryland.

154
00:10:17,680 --> 00:10:18,240
What's there?

155
00:10:19,370 --> 00:10:22,200
Well, go to the website and take a look
around.

156
00:10:22,200 --> 00:10:25,260
You will find databases for scientific
literature,

157
00:10:25,260 --> 00:10:29,390
DNA sequences, organism taxonomy, gene
expression data.

158
00:10:29,390 --> 00:10:30,280
Well, quite a bit.

159
00:10:31,370 --> 00:10:33,080
It's a huge relational database.

160
00:10:35,440 --> 00:10:40,160
So basically to summarize, be able
recognize the primary key.

161
00:10:40,160 --> 00:10:43,780
That's the most unique identifier and it's
usually a series of numbers and letters.

162
00:10:45,360 --> 00:10:46,360
It's not data.

163
00:10:46,360 --> 00:10:50,060
So don't look for, for instance, a vote
total, and say that's a primary key.

164
00:10:52,140 --> 00:10:55,270
And relational databases are able to link
related data.

165
00:10:58,240 --> 00:11:03,300
In the case of IMDB, it's a movie database
that can be linked to an actor database.

166
00:11:05,390 --> 00:11:07,750
But at NCBI we can see that a

167
00:11:07,750 --> 00:11:10,400
nucleotide database can be linked to a
protein database

168
00:11:10,400 --> 00:11:12,790
and also linked to a literature database
and

169
00:11:12,790 --> 00:11:15,110
a structure database and a host of other
databases.

170
00:11:16,970 --> 00:11:19,346
I hope this lecture on databases is
helpful

171
00:11:19,346 --> 00:11:22,590
to understanding the concept of databases
and good luck.