Hey guys, in this lesson we're going to be learning all about relational databases and how that applies
to PostgreSQL.
And we're going to be looking at the different types of relationships such as one to many, 1 to 1,
and many to many relationships.
And we're going to see how we can join our tables together that are constructed using these relationships.
To begin, we already spoke about how relationships are a really big aspect of SQL type databases,
and it's really what they are strong at.
They are essentially the relationship goals of all the different databases.
And this is really where the power of SQL databases actually come in.
So let's explore and see how we can create great relationships.
Now the first type of relationship we're going to look at is the simplest.
It's a 1 to 1 relationship.
And if you take a look here you'll see how we've designed this relationship.
Now in this type of crow's feet diagrams, the relationships which are 1 to 1 are represented like this.
On this side we have a table called student.
And on this side we have a table called contact.
And we're linking these two tables together using this 1 to 1 relationship.
And they're linked by the field that is the ID.
When you think of a 1 to 1 relationship, it basically means that there is one entry in the student
that maps to one entry in the contact table, and very often this is a way to modularize your database.
So for example, if you have a table which has many, many columns as the columns kind of extend, as
you end up with more and more of them, your database becomes larger and larger and more difficult to
search.
So a lot of databases are optimized to have many, many records, so many, many rows.
But they can struggle when you have too many fields or too many columns.
One way of splitting a table up is using a 1 to 1 relationship.
Now an example of this is represented here using SQL code.
By now you already understand how create table works.
So here we're creating two tables.
One is a table called student and the other is a table called contact detail.
Now these two tables represent different bits of data about a student in a imaginary school.
In the first table we have the first name and the last name of the student.
And of course we have a primary key to represent the ID of that record in the table.
This is obviously vastly simplified, but you can imagine a student table might contain some pieces
of information such as this.
Now, every so often you might need to contact the student's parents.
And so you would need to look up the student and the contact detail table.
Here we essentially extend the student table and we add some more details that are related to their
contacts, such as their telephone number or their address.
While we could have all of this inside the student's table, it's not very modular, and very often
you'll see tables being more efficient if we can separate them into different tasks or have different
roles, such as here.
But how do we link them up?
Well, in this case we have a 1 to 1 relationship between the IDs.
The ID of the student is the primary key of the student table, and we use that same ID when we create
a record in the contact detail table.
And this is set to be unique.
So it cannot reoccur in this table.
So we can't map to student IDs to two different records for example.
But also we set it as an integer because we know that in this case this ID is an integer as a whole
number.
And we have the references keyword in Postgres which sets this particular field as a foreign key.
So we now have a primary key which is here which uniquely identifies all records in this table.
And we have a foreign key which identifies the relationship between records in the contact detail table
with the records in the student table.
So we're now kind of getting into the realm of database design.
And very often you'll see that people design their databases using drawings such as these, which.
You can create yourself.
If you go to Draw.io and you can create these tables, add different fields, and plan to have different
relationships and links between the tables.
And this is a very easy, at a glance way to see how we have a 1 to 1 relationship between the ID fields
in these two tables.
Now I want you to create this in Postgres yourself.
So we're going to complete these sessions as an exercise so that you can have a go at creating some
of these relationships yourself.
The first thing I want you to do is to go into the resources for this session and download 8.5 Family
Travel Tracker.
This file contains all of the starting files that we'll need to complete the final exercise, but also
in there I've snuck in this file called queries dot SQL, and it's got all of the queries that we go
through in this lesson.
So you don't have to type it out all yourself.
And you might make some typos or spelling errors.
And this is just a way that allows you to simply copy these commands and put them into Postgres.
If you collapse all of your databases, I want you to go ahead and create a new database.
And this database you're going to call school.
Go ahead and click save.
And we now have this school database created.
Click on this query tool so that we connect to this school database.
Here is where you're going to paste in the queries that you just copied over.
Don't worry about these lines.
They're just comments.
And I wanted to separate the different parts of the queries so that you'd be able to see which part
goes into which.
And in SQL, the comments are created using two dashes at the beginning instead of, for example, our
two forward slashes and JavaScript in SQL is just two dashes, so they won't get executed as code.
So now that we've got the code in here, let's go ahead and create these two tables.
So let's run those two commands.
And now if we go into schemas and we hit refresh, you should be able to see that we have our two tables
created.
They don't yet have any data.
So let's populate some data in here by copying the part from the data section and paste it into the
query section here.
And then let's hit run in order to insert those pieces of data into our tables.
So notice the first command inserts into the students table.
And we have well my name Angela, you added as a student.
And then we insert some details into the contact detail table.
And we're inserting the telephone number the address, but also that all important foreign key, the
student ID.
So now I know that because the student table is created with a primary key that is serial, it's going
to start the ID from number one.
And this is the only entry that I've added so far.
So the ID for this student is one.
That's what I'm inserting into the contact detail table.
As the ID of this record.
I'm adding a imaginary telephone number and an imaginary address.
And now using this foreign key, I've linked it to this primary key.
And these two records, even though they exist on different tables, are now linked through this 1 to
1 relationship.
Now, how can we use this 1 to 1 relationship?
What if we wanted to bring up the entire record?
How would we do that using SQL?
Let's analyze this code.
Here we've got select all select asterisks which is going to select everything.
And then we say the table that we want to select from which is the student table.
And then we're using this keyword here which is called join.
And what this is going to perform is what's known in the industry as an inner join.
It's going to join two tables together based on some sort of criteria.
And the criteria is determined by this keyword on.
And the criteria here we're saying look inside the students table, find the ID, which as you remember
is the primary key that's unique in the students table.
And then check for equality.
So find the ID in the contact detail table that matches this ID we've got the foreign key.
We've got the primary key.
And when they match each other we'll find the.
Record that contains all of the details of that student.
And if we run this, it will look something like this.
This is two tables.
This is the first table, the students table that contains an ID as the primary key, the first name
and last name.
And then we've got another table right here.
And this is the contact details table.
And it contains a foreign key the ID and the telephone number and address.
But what we've done here is because we know that there is a 1 to 1 relationship between the ID field
in the contact details table and the ID field in the students table.
We can use this SQL command to join these two tables together.
And that is what we end up with right here.
We end up with one big table with all of the fields that correspond to that particular entry.
And that is how we can use a 1 to 1 relationship.
So what I want you to do is to try this out as an exercise.
Enter this command into your pgadmin.
And if you need to copy that command, you can find it again in queries dot SQL.
And hopefully you end up with that exact same table being generated.
Let's try it together for the first one.
And I'm going to paste in my query here, making sure that I'm in the correct database.
And then if I go ahead and hit run, then this command is going to utilize that 1 to 1 relationship.
And it's going to bring up this giant table that is joined by the hip, but actually joined by the 1
to 1 relationship right here.