1
00:00:00,340 --> 00:00:04,450
This is the third module
in six about databases.

2
00:00:04,450 --> 00:00:08,880
In this module we will be talking
about relation databases.

3
00:00:08,880 --> 00:00:14,220
You will remember from the previous module
eh, when we talked about data models,

4
00:00:14,220 --> 00:00:19,770
that we talked about the relational
data model, how data can be organized.

5
00:00:19,770 --> 00:00:23,290
In the set of,
tables with rows from columns.

6
00:00:23,290 --> 00:00:28,550
Or relations, attributes and
domains to give them their formal titles.

7
00:00:28,550 --> 00:00:32,802
And a relation database
is a database which uses

8
00:00:32,802 --> 00:00:37,700
the relation model as the means
to structure its data.

9
00:00:40,930 --> 00:00:45,570
There are a number of features of relation
data base that I want to talk of,

10
00:00:45,570 --> 00:00:46,150
first of all.

11
00:00:47,220 --> 00:00:51,850
Things that a relational database
gives you, that are very useful for

12
00:00:51,850 --> 00:00:56,280
the management,
administration of data in a database.

13
00:00:57,560 --> 00:00:59,070
One of the most important.

14
00:01:00,580 --> 00:01:05,170
For the purposes of relational database
is the notion of a transaction.

15
00:01:06,970 --> 00:01:10,800
A transaction is atomic sequence
of actions, whether it's read or

16
00:01:10,800 --> 00:01:12,570
write in the database.

17
00:01:13,800 --> 00:01:21,680
And transactions in the database have to
be executed completely by definition.

18
00:01:21,680 --> 00:01:25,190
And they must leave a database
in a consistent state.

19
00:01:25,190 --> 00:01:29,300
You can't have a transaction which leaves
the database in a dangerous state.

20
00:01:29,300 --> 00:01:30,190
That would not be allowed.

21
00:01:31,360 --> 00:01:34,824
If the transaction should fail or
abort midway,

22
00:01:34,824 --> 00:01:40,610
the database rolls back to
an initial consistent state.

23
00:01:41,700 --> 00:01:44,730
Now you don't need to worry about his
yourself because the database management

24
00:01:44,730 --> 00:01:48,300
system in the relation database
is taking care of this for you.

25
00:01:48,300 --> 00:01:53,530
But it's nice to know that should
a particular transaction fail or crash for

26
00:01:53,530 --> 00:01:56,270
some reason when you're

27
00:01:56,270 --> 00:02:01,260
using a relational database the data is
not going to be compromised in any way.

28
00:02:01,260 --> 00:02:06,050
Because the DBMS is going to
go back to a safe position.

29
00:02:06,050 --> 00:02:11,080
This is one of the advantages
of using a DBMS as opposed to

30
00:02:11,080 --> 00:02:13,460
just working programmatically
with your data.

31
00:02:13,460 --> 00:02:15,540
If you're doing a calculation
on your data and

32
00:02:15,540 --> 00:02:21,080
you suddenly collapsed or crashed, you
may find that your data is, is corrupted.

33
00:02:21,080 --> 00:02:25,350
The hope is that with a re, relational
database, that is less likely to happen.

34
00:02:25,350 --> 00:02:30,840
An example of a transaction is for
example, let me say

35
00:02:30,840 --> 00:02:35,920
I'm going to authorize PayPal to pay
$100 for the eBay purchase I just made.

36
00:02:35,920 --> 00:02:39,980
In terms of the steps of the transaction.

37
00:02:41,450 --> 00:02:42,950
It has to debit my account $100.

38
00:02:42,950 --> 00:02:46,030
And it has to credit
the seller's account $100.

39
00:02:46,030 --> 00:02:52,450
If the transaction fails halfway through,
either my account has

40
00:02:52,450 --> 00:02:57,990
not been debited and the seller's account
may have already been accredited, or

41
00:02:57,990 --> 00:03:03,150
the averse may be true that He has
the money, I don't have the money.

42
00:03:03,150 --> 00:03:07,100
Or, I don't have the money,
and he has the money.

43
00:03:07,100 --> 00:03:11,960
That would not be good,
It would leave us in a dangerous state.

44
00:03:11,960 --> 00:03:17,190
Either I don't have money, or he hasn't
got the money, and so we would need to

45
00:03:17,190 --> 00:03:21,290
roll back to an initial state where
the money was in my account again.

46
00:03:23,340 --> 00:03:26,530
So that's,
an example of a financial transaction, but

47
00:03:26,530 --> 00:03:29,430
you can imagine the same thing happening
with data when you're doing a data

48
00:03:29,430 --> 00:03:31,470
operation in a relation database.

49
00:03:34,790 --> 00:03:41,410
By definition therefore, a database
transaction, is what's called ACID.

50
00:03:42,660 --> 00:03:46,470
Which is an acronym that stands for,
it's atomic, so

51
00:03:46,470 --> 00:03:52,480
either the transaction completes or
nothing happens.

52
00:03:52,480 --> 00:03:54,670
You don't get half
the transaction occurring.

53
00:03:57,310 --> 00:04:00,560
The database transaction is consistent.

54
00:04:00,560 --> 00:04:02,960
There are no integrity
constraints violated.

55
00:04:02,960 --> 00:04:08,140
What that means is that I
may have some constraints on

56
00:04:08,140 --> 00:04:14,832
the type of data or the format of data
that is allowed in one of my data columns.

57
00:04:14,832 --> 00:04:19,820
Or connections between
different data tables.

58
00:04:19,820 --> 00:04:21,270
Different relations.

59
00:04:21,270 --> 00:04:23,160
And none of those are violated.

60
00:04:23,160 --> 00:04:25,940
None of those compromised by
the database transaction.

61
00:04:27,230 --> 00:04:32,370
The transaction is isolated in
the sense that it has no impact on

62
00:04:32,370 --> 00:04:36,420
any other transaction that was going on.

63
00:04:36,420 --> 00:04:41,290
And finally,
the database transaction is durable.

64
00:04:41,290 --> 00:04:44,910
Once a database transaction
has been committed,

65
00:04:44,910 --> 00:04:48,270
the effects of it are permanent and
persistent.

66
00:04:48,270 --> 00:04:52,650
You're not going to fall back to a, a,
a former position at some later stage.

67
00:04:55,330 --> 00:04:59,190
So that is ACID, which you talk
about with database transactions.

68
00:05:03,590 --> 00:05:06,780
One issue with relation databases
is the notion of concurrency.

69
00:05:07,830 --> 00:05:12,030
What happens if I have multiple
transactions going on at

70
00:05:12,030 --> 00:05:14,460
the same time coming
from different clients?

71
00:05:15,750 --> 00:05:21,160
The database management system
ensures that those very nicely.

72
00:05:21,160 --> 00:05:28,970
And that, they don't cause inconsistencies
in the data that they are working with.

73
00:05:28,970 --> 00:05:33,870
What it will do is the DBMS will
analyze the transaction set

74
00:05:33,870 --> 00:05:37,710
that is currently being considered and

75
00:05:37,710 --> 00:05:41,660
it will convert that into a new set
that can be executed sequentially.

76
00:05:43,340 --> 00:05:46,580
Again this is something that
you don't need to do yourself.

77
00:05:46,580 --> 00:05:49,970
It is a benefit of the relational
database system that you're using.

78
00:05:52,646 --> 00:05:57,012
One way that it can do this is that
before reading or writing an object in

79
00:05:57,012 --> 00:06:02,080
the database each transaction waits for
a lock on the object.

80
00:06:02,080 --> 00:06:07,520
And when the transaction is finished,
it releases all its locks.

81
00:06:07,520 --> 00:06:12,680
And in that way a data value that it
may be using, can't be compromised by

82
00:06:12,680 --> 00:06:16,280
another transaction, You isolate
the data that it maybe working with.

83
00:06:16,280 --> 00:06:19,590
And since,
you're only having a set of of logically

84
00:06:21,320 --> 00:06:25,790
Meaningful sequence of transactions
that doesn't cause any inconsistencies.

85
00:06:28,680 --> 00:06:30,880
locks, which was previously mentioned,

86
00:06:32,730 --> 00:06:37,370
the database management system can set and
hold multiple locks simultaneously on

87
00:06:37,370 --> 00:06:40,610
different levels of
the physical data structure.

88
00:06:40,610 --> 00:06:46,700
One of the advantage of a DBMS is
that allows you to control the access

89
00:06:46,700 --> 00:06:50,600
of the operations on different
individual pieces of data or

90
00:06:50,600 --> 00:06:54,550
groups of data in ways that are sensible.

91
00:06:54,550 --> 00:07:01,130
The small full granularity, for example
at a row level in, within a, a table.

92
00:07:01,130 --> 00:07:03,780
We could lock that a basic data block,

93
00:07:03,780 --> 00:07:07,850
a page a whole set of pages,
or even an entire table.

94
00:07:07,850 --> 00:07:10,020
You can have a read lock,
or write lock on,

95
00:07:10,020 --> 00:07:13,610
or, or something like that,
There are different types of locks.

96
00:07:13,610 --> 00:07:18,640
Say exclusive locks versus shared locks
that that the access to the data is

97
00:07:18,640 --> 00:07:21,770
limited to one person, when it could be
shared between a group of individuals or

98
00:07:21,770 --> 00:07:22,810
named actors.

99
00:07:24,560 --> 00:07:28,370
You can have optimistic
versus pessimistic locks,

100
00:07:29,430 --> 00:07:34,320
which behave in different ways depending
on the situation being considered.

101
00:07:35,870 --> 00:07:40,830
So the variety of locks that the DMBS is
working with that is worth being aware of

102
00:07:40,830 --> 00:07:46,050
those because sometimes you may
require low level access to try and

103
00:07:46,050 --> 00:07:49,040
understand a problem you may be
having with your database and

104
00:07:49,040 --> 00:07:52,250
it may be related to data access and
to locking.

105
00:07:56,026 --> 00:08:02,694
A relational database will have a set
of logs where it writes down, keeps

106
00:08:02,694 --> 00:08:09,670
track of all the transactions that are
occurring with the relational database.

107
00:08:12,200 --> 00:08:16,329
This ensures the [INAUDIBLE] of
the transactions, the a part of the ACID.

108
00:08:18,050 --> 00:08:23,960
But there is a complete set and
it's useful should your

109
00:08:23,960 --> 00:08:29,370
data base unfortunately crash,
any partially executed

110
00:08:29,370 --> 00:08:33,030
transactions, Can be undone using the Log.

111
00:08:33,030 --> 00:08:37,510
But remember that the atomicity
means that the transaction either is

112
00:08:37,510 --> 00:08:39,850
fully complete or doesn't happen at all.

113
00:08:39,850 --> 00:08:43,610
Honestly if you're half way through
you would violate that but because you

114
00:08:43,610 --> 00:08:49,760
have a log of what operations have been
carried out as part of a transaction.

115
00:08:49,760 --> 00:08:52,780
You can reverse those
actions to take you back to

116
00:08:54,090 --> 00:08:57,690
a clean uncompromised state,
uncorrupted state.

117
00:08:59,140 --> 00:09:02,900
Typically a log record
will have some sort of

118
00:09:02,900 --> 00:09:06,640
header which involves a transaction ID and
a timestamp.

119
00:09:08,010 --> 00:09:12,570
It will have the, An ID of the item
in the relational database that

120
00:09:12,570 --> 00:09:17,450
you're working with and may say what
the type of the transaction is and

121
00:09:17,450 --> 00:09:19,820
then it may be an old and a new value.

122
00:09:19,820 --> 00:09:24,240
So those are the sort of things that you
have in log records should you need to

123
00:09:24,240 --> 00:09:27,792
go through a log record to understand
what's, what's going on possibly.

124
00:09:31,270 --> 00:09:37,710
data, obviously if you have
a lot of data your performance,

125
00:09:37,710 --> 00:09:41,710
you may find, is particularly
slow in a relation database.

126
00:09:41,710 --> 00:09:47,160
And there may be more efficient
ways of breaking your data up.

127
00:09:48,230 --> 00:09:52,390
This may be particularly if you have more
data than can fit on a, on a, single

128
00:09:52,390 --> 00:09:58,670
machine, so you have a cluster of machines
which are your relational database system.

129
00:09:58,670 --> 00:10:03,370
There maybe, ways of partitioning
the data which are meaningful,

130
00:10:06,740 --> 00:10:09,850
The different types of
partitioning schemes.

131
00:10:09,850 --> 00:10:14,220
There's horizontal partitioning
where you have different rows.

132
00:10:14,220 --> 00:10:17,660
And you put different rows
in different tables, posh,

133
00:10:17,660 --> 00:10:19,630
potentially on different machines,
different hosts.

134
00:10:21,150 --> 00:10:28,130
Or you can have one big table
which has very many Data in it.

135
00:10:28,130 --> 00:10:34,130
And you want to do a Vertical partitioning
that you, break it up by, by columns.

136
00:10:34,130 --> 00:10:36,810
And you put different columns
into different tables.

137
00:10:36,810 --> 00:10:41,030
That's part of the thing called
normalization of a relational database.

138
00:10:41,030 --> 00:10:42,070
And we'll talk about that in a minute.

139
00:10:43,360 --> 00:10:45,390
You may partition according to range.

140
00:10:47,466 --> 00:10:53,220
Where those rows which have values in
a particular column are within a side

141
00:10:53,220 --> 00:10:57,590
a certain range going into one table,
one machine and

142
00:10:57,590 --> 00:11:01,850
those in another range, those values
on another machine, another machine.

143
00:11:03,802 --> 00:11:04,302
List.

144
00:11:05,728 --> 00:11:10,724
Instead of specifying a range you
may have something which has a,

145
00:11:10,724 --> 00:11:15,349
particular data value which has
a finite number of allowed values and

146
00:11:15,349 --> 00:11:20,272
you could have a list where the values in
the particular column match a subset of

147
00:11:20,272 --> 00:11:23,980
that and you use that for
your partitioning.

148
00:11:23,980 --> 00:11:27,220
Or you may have a,
a more general hash function.

149
00:11:27,220 --> 00:11:28,950
Which returns a particular value,

150
00:11:28,950 --> 00:11:33,120
depending on the value of, of,
particular columns in, in a row.

151
00:11:33,120 --> 00:11:38,030
And the partitioning is
done on the basis of that.

152
00:11:38,030 --> 00:11:39,900
According to the value of the function.

153
00:11:42,150 --> 00:11:46,383
Finally I'll talk about mention
database normalization relation

154
00:11:46,383 --> 00:11:47,890
database normalization.

155
00:11:49,420 --> 00:11:56,153
These were set of rules that Cob who came
up with the relation database model,

156
00:11:56,153 --> 00:11:59,679
devised what are called the normal forms.

157
00:12:01,580 --> 00:12:04,470
I think there are 12 normal forms in all.

158
00:12:04,470 --> 00:12:07,800
But the ones that really only
make sense are the first 3.

159
00:12:07,800 --> 00:12:11,080
Or the ones that are most commonly used.

160
00:12:11,080 --> 00:12:16,230
The idea here is that these ways of
normalizing the relational database.

161
00:12:16,230 --> 00:12:21,560
Make it particularly efficient and
are particularly sensible or, or, or

162
00:12:21,560 --> 00:12:25,110
logical from a relational
data model perspective.

163
00:12:26,310 --> 00:12:27,950
In the first normal form,

164
00:12:27,950 --> 00:12:33,570
you will find that there are no
repeating elements in the database.

165
00:12:34,670 --> 00:12:35,730
Or a group of elements.

166
00:12:35,730 --> 00:12:37,300
Each has a unique key.

167
00:12:37,300 --> 00:12:39,920
And also that you have
no [INAUDIBLE] columns,

168
00:12:39,920 --> 00:12:42,450
that each column actually
has a value in it.

169
00:12:44,450 --> 00:12:46,430
In the second normal form,

170
00:12:46,430 --> 00:12:51,020
no columns in a table are dependent
on only part of the key.

171
00:12:51,020 --> 00:12:59,540
So, for example, if you had a column,
a table of stars.

172
00:12:59,540 --> 00:13:02,710
You may have three columns
which might be the star name,

173
00:13:02,710 --> 00:13:06,240
the constellation that the star is in and
the area of sky.

174
00:13:06,240 --> 00:13:11,500
And obviously there's a dependency there
between one or more of those columns,

175
00:13:11,500 --> 00:13:16,580
which you would remove if you were
normalizing your database according to

176
00:13:16,580 --> 00:13:17,540
second mobile form.

177
00:13:19,130 --> 00:13:25,000
And third normal form, you have no columns
dependent on any other non-key columns.

178
00:13:26,240 --> 00:13:30,450
In that case you would have Star Name,
Magnitude and Flux for example.

179
00:13:30,450 --> 00:13:33,140
But Magnitude and
Flux are related to each other.

180
00:13:33,140 --> 00:13:35,170
One is the log of the other.

181
00:13:35,170 --> 00:13:37,920
And so therefore,
you would remove one of those,

182
00:13:37,920 --> 00:13:41,550
if you were normalizing your database
according to third normal form.

183
00:13:43,250 --> 00:13:49,930
These are, not, mandatory, requirements
when you construct a database,

184
00:13:49,930 --> 00:13:54,060
or a relation [INAUDIBLE] user relation
database, but it is generally,

185
00:13:54,060 --> 00:13:55,840
considered good practice to,.

186
00:13:55,840 --> 00:13:59,720
Consider at least one of
the normal forms to use.

187
00:14:00,860 --> 00:14:04,020
For the purposes of,
of keeping your data organized in

188
00:14:04,020 --> 00:14:08,500
a clean fashion in,
in the relation database.

189
00:14:10,120 --> 00:14:11,770
And, that is the end of this talk.