- [Instructor] Some years back, I had the task
to create a web application that was focused around hiking,
and it became quite challenging
from a database point of view
because we had to answer questions like,
which villages are along some trail?
Or, where can I eat when I'm in some village?
Or which trails should I take to get to this village?
Technically, these are relations,
and relational databases like MySQL
were made exactly for that.
They also have a very clear nomenclature
on how relations are expressed.
In relational databases, we have schemas
and they define tables, a table structure,
and also, very important, the relationship between records.
Let's look at a few examples.
Let's think about a system for a college.
So a student usually has one place of birth,
and this is a one-to-one relation.
A college has many students.
This is a so-called one to n relation.
And the subject has many students,
and the student has many subjects.
That's a so-called m to n relation.
For my Trails database,
I used so-called entity relationship diagram, short ERDs,
that express exactly that.
They can be used to better understand
the relations within the system.
So as you see, such relations like m to n
or n to n are expressed graphically.
For instance, a trail can run through many villages.
A village can have many trails.
A trail may come across a lot of points of interest,
and also, next to a point of interest,
there can be many trails.
By creating that, I also came across
a lot of rather philosophical questions.
Like, is a hotel always in the village?
Should a trail be related to a hotel?
Can a point of interest have many trails?
When you design a database system,
you're often confronted with questions like this,
and you basically have to solve them upfront
because you will define this structure directly
in your database.
There's also another problem here
because what you see here as m to n relations
is nothing that can be easily expressed
within a relational database.
To accomplish that,
you need additional tables to achieve that.
And you see that here.
Now, suddenly we have additional tables
like Trail to a Point of Interest or Trail2Village.
Such cross-reference tables are used
to express m to n relationships within relational databases.
There are also many things that you can get wrong
when you create databases.
And to avoid it, there is a set of general principles
around so-called data normalization.
Let's look at this system
that manages teachers and subjects.
Here we store a teacher with address and subject.
Sina Patel teaches two subjects,
and so there are two records for her.
Unfortunately, when adding the second subject for Sina,
the address entered did not match the one
that was already there.
So which address should now be taken
to send the letter to Sina?
Next problem is, this year Tom Smith has no subject.
So his record is deleted.
Unfortunately, this means now
that he won't receive a letter inviting him to teach again,
because the address is gone.
When doing normalization, such inconsistencies are removed
by factoring out the data.
So we will remove the address and we also remove the name,
and we instead create a table, Teacher.
Next, we will create a relationship between those tables.
It's a one to n relationship.
And for that, we declare a foreign key.
And this foreign key now points from Teacher ID in Subject
to the Teacher table to the record with this given ID.
We also create another rule here
that is called on delete: cascade.
And this means that if a teacher is deleted,
all the subject related to this teacher are deleted as well.
And this is how the database query
would most probably look like.
So running this SQL query would give you all teachers
and all subjects for all teachers.
As this relationship is directly managed
by the database system,
this query is way more efficient
than first fetching all subjects, iterating through them,
and fetching the teacher for each subject.
When we are so eager to make sure
that our data is consistent,
the whole system has to be built around consistency.
That's why most relational databases strive
to follow the ACID principle.
That's an acronym and it means atomicity,
so a transaction has to be all or nothing.
Consistency, so a transaction has to transform the data
from one valid state to another,
so the end result of the transaction cannot be
that the data is in some way inconsistent or invalid.
Isolation, this means
that if there were concurrent transactions,
they leave the data in a state
as if they were executed in sequence.
And durability.
So once a transaction is committed, we have to make sure
that the data is really persistently stored in the database.
Here's a little example for such a transaction.
So, Mary wants to send Tim $200 via PayPal.
First we start the transaction,
then we take $200 from Mary's account.
And then we add $200 to Tim's account.
If anything goes wrong here,
so, maybe we cannot take the $200 from Mary's account,
something goes wrong while I'm trying
to store the $200 into Tim's account
or the database crushes at some point,
the database will look like
as if the transaction never took place.
If we catch an error programmatically
during such a transaction, we can even programmatically
roll back the whole transaction.
And again, the database will look like
all these database statements never took place.
If everything went fine, we can commit the transaction.
And now the data is really persisted into the database.
When at the same time, someone else wants to send Tim $300,
these two transactions will be done more or less in sequence
because during the first transaction,
all participating tables will be locked
until the first transaction is over
and then the next transaction can take place.
Now, if you look at which database systems are out there,
we can say that for open source,
MySQL and PostgreSQL are the most popular ones.
They're very powerful.
And they're also around for very long time.
And the most popular commercial ones are Oracle,
IBM Db2 and Microsoft SQL server.
This course, I will use MySQL
for the relational database part.