This is the third module
in six about databases.
In this module we will be talking
about relation databases.
You will remember from the previous module
eh, when we talked about data models,
that we talked about the relational
data model, how data can be organized.
In the set of,
tables with rows from columns.
Or relations, attributes and
domains to give them their formal titles.
And a relation database
is a database which uses
the relation model as the means
to structure its data.
There are a number of features of relation
data base that I want to talk of,
first of all.
Things that a relational database
gives you, that are very useful for
the management,
administration of data in a database.
One of the most important.
For the purposes of relational database
is the notion of a transaction.
A transaction is atomic sequence
of actions, whether it's read or
write in the database.
And transactions in the database have to
be executed completely by definition.
And they must leave a database
in a consistent state.
You can't have a transaction which leaves
the database in a dangerous state.
That would not be allowed.
If the transaction should fail or
abort midway,
the database rolls back to
an initial consistent state.
Now you don't need to worry about his
yourself because the database management
system in the relation database
is taking care of this for you.
But it's nice to know that should
a particular transaction fail or crash for
some reason when you're
using a relational database the data is
not going to be compromised in any way.
Because the DBMS is going to
go back to a safe position.
This is one of the advantages
of using a DBMS as opposed to
just working programmatically
with your data.
If you're doing a calculation
on your data and
you suddenly collapsed or crashed, you
may find that your data is, is corrupted.
The hope is that with a re, relational
database, that is less likely to happen.
An example of a transaction is for
example, let me say
I'm going to authorize PayPal to pay
$100 for the eBay purchase I just made.
In terms of the steps of the transaction.
It has to debit my account $100.
And it has to credit
the seller's account $100.
If the transaction fails halfway through,
either my account has
not been debited and the seller's account
may have already been accredited, or
the averse may be true that He has
the money, I don't have the money.
Or, I don't have the money,
and he has the money.
That would not be good,
It would leave us in a dangerous state.
Either I don't have money, or he hasn't
got the money, and so we would need to
roll back to an initial state where
the money was in my account again.
So that's,
an example of a financial transaction, but
you can imagine the same thing happening
with data when you're doing a data
operation in a relation database.
By definition therefore, a database
transaction, is what's called ACID.
Which is an acronym that stands for,
it's atomic, so
either the transaction completes or
nothing happens.
You don't get half
the transaction occurring.
The database transaction is consistent.
There are no integrity
constraints violated.
What that means is that I
may have some constraints on
the type of data or the format of data
that is allowed in one of my data columns.
Or connections between
different data tables.
Different relations.
And none of those are violated.
None of those compromised by
the database transaction.
The transaction is isolated in
the sense that it has no impact on
any other transaction that was going on.
And finally,
the database transaction is durable.
Once a database transaction
has been committed,
the effects of it are permanent and
persistent.
You're not going to fall back to a, a,
a former position at some later stage.
So that is ACID, which you talk
about with database transactions.
One issue with relation databases
is the notion of concurrency.
What happens if I have multiple
transactions going on at
the same time coming
from different clients?
The database management system
ensures that those very nicely.
And that, they don't cause inconsistencies
in the data that they are working with.
What it will do is the DBMS will
analyze the transaction set
that is currently being considered and
it will convert that into a new set
that can be executed sequentially.
Again this is something that
you don't need to do yourself.
It is a benefit of the relational
database system that you're using.
One way that it can do this is that
before reading or writing an object in
the database each transaction waits for
a lock on the object.
And when the transaction is finished,
it releases all its locks.
And in that way a data value that it
may be using, can't be compromised by
another transaction, You isolate
the data that it maybe working with.
And since,
you're only having a set of of logically
Meaningful sequence of transactions
that doesn't cause any inconsistencies.
locks, which was previously mentioned,
the database management system can set and
hold multiple locks simultaneously on
different levels of
the physical data structure.
One of the advantage of a DBMS is
that allows you to control the access
of the operations on different
individual pieces of data or
groups of data in ways that are sensible.
The small full granularity, for example
at a row level in, within a, a table.
We could lock that a basic data block,
a page a whole set of pages,
or even an entire table.
You can have a read lock,
or write lock on,
or, or something like that,
There are different types of locks.
Say exclusive locks versus shared locks
that that the access to the data is
limited to one person, when it could be
shared between a group of individuals or
named actors.
You can have optimistic
versus pessimistic locks,
which behave in different ways depending
on the situation being considered.
So the variety of locks that the DMBS is
working with that is worth being aware of
those because sometimes you may
require low level access to try and
understand a problem you may be
having with your database and
it may be related to data access and
to locking.
A relational database will have a set
of logs where it writes down, keeps
track of all the transactions that are
occurring with the relational database.
This ensures the [INAUDIBLE] of
the transactions, the a part of the ACID.
But there is a complete set and
it's useful should your
data base unfortunately crash,
any partially executed
transactions, Can be undone using the Log.
But remember that the atomicity
means that the transaction either is
fully complete or doesn't happen at all.
Honestly if you're half way through
you would violate that but because you
have a log of what operations have been
carried out as part of a transaction.
You can reverse those
actions to take you back to
a clean uncompromised state,
uncorrupted state.
Typically a log record
will have some sort of
header which involves a transaction ID and
a timestamp.
It will have the, An ID of the item
in the relational database that
you're working with and may say what
the type of the transaction is and
then it may be an old and a new value.
So those are the sort of things that you
have in log records should you need to
go through a log record to understand
what's, what's going on possibly.
data, obviously if you have
a lot of data your performance,
you may find, is particularly
slow in a relation database.
And there may be more efficient
ways of breaking your data up.
This may be particularly if you have more
data than can fit on a, on a, single
machine, so you have a cluster of machines
which are your relational database system.
There maybe, ways of partitioning
the data which are meaningful,
The different types of
partitioning schemes.
There's horizontal partitioning
where you have different rows.
And you put different rows
in different tables, posh,
potentially on different machines,
different hosts.
Or you can have one big table
which has very many Data in it.
And you want to do a Vertical partitioning
that you, break it up by, by columns.
And you put different columns
into different tables.
That's part of the thing called
normalization of a relational database.
And we'll talk about that in a minute.
You may partition according to range.
Where those rows which have values in
a particular column are within a side
a certain range going into one table,
one machine and
those in another range, those values
on another machine, another machine.
List.
Instead of specifying a range you
may have something which has a,
particular data value which has
a finite number of allowed values and
you could have a list where the values in
the particular column match a subset of
that and you use that for
your partitioning.
Or you may have a,
a more general hash function.
Which returns a particular value,
depending on the value of, of,
particular columns in, in a row.
And the partitioning is
done on the basis of that.
According to the value of the function.
Finally I'll talk about mention
database normalization relation
database normalization.
These were set of rules that Cob who came
up with the relation database model,
devised what are called the normal forms.
I think there are 12 normal forms in all.
But the ones that really only
make sense are the first 3.
Or the ones that are most commonly used.
The idea here is that these ways of
normalizing the relational database.
Make it particularly efficient and
are particularly sensible or, or, or
logical from a relational
data model perspective.
In the first normal form,
you will find that there are no
repeating elements in the database.
Or a group of elements.
Each has a unique key.
And also that you have
no [INAUDIBLE] columns,
that each column actually
has a value in it.
In the second normal form,
no columns in a table are dependent
on only part of the key.
So, for example, if you had a column,
a table of stars.
You may have three columns
which might be the star name,
the constellation that the star is in and
the area of sky.
And obviously there's a dependency there
between one or more of those columns,
which you would remove if you were
normalizing your database according to
second mobile form.
And third normal form, you have no columns
dependent on any other non-key columns.
In that case you would have Star Name,
Magnitude and Flux for example.
But Magnitude and
Flux are related to each other.
One is the log of the other.
And so therefore,
you would remove one of those,
if you were normalizing your database
according to third normal form.
These are, not, mandatory, requirements
when you construct a database,
or a relation [INAUDIBLE] user relation
database, but it is generally,
considered good practice to,.
Consider at least one of
the normal forms to use.
For the purposes of,
of keeping your data organized in
a clean fashion in,
in the relation database.
And, that is the end of this talk.