SQL & Databases - Introduction to Database Relationships | Jon Avis | Skillshare

SQL & Databases - Introduction to Database Relationships

Jon Avis, IT Systems Engineer

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
7 Lessons (20m)
    • 1. Class Introduction

      0:52
    • 2. What Are Database Relationships?

      1:31
    • 3. Primary Keys and Foreign Keys

      4:15
    • 4. One to One Relationships

      3:44
    • 5. One to Many Relationships

      3:53
    • 6. Many to Many Relationships

      4:46
    • 7. Class Project

      1:09

About This Class

In this class you will learn about database relationships. Database relationships are a key component of what makes relational databases so powerful and dynamic. By the end of this class you will know:

  • What database relationships are, why they are so powerful and how they are created.
  • The concepts of primary keys and foreign keys.
  • What a one to one relationship is and how it can be used.
  • What a one to many relationship is and how to create one.
  • What a many to many relationship is and how to create one.

And to finish the class there is a database relationships project to complete which will test your knowledge of database relationships.

This class is the best way to master database relationships!

Transcripts

1. Class Introduction: Hello. My name is John on. I'm in I T Systems Engineer and I work with SQL and Databases on a daily basis. Welcome to my class introduction to database relationships in this class. We're gonna learn what database relationships are, how they used in relational databases on what makes them so powerful. First will understand the concepts off primary keys and foreign keys within tables. Then we'll learn about the three types of database relationships. So the once one relationship the one to many relationship and then finally the many to many relationship. And then we'll finish this class by completing a database relationships project. So let's get started in the next video. 2. What Are Database Relationships?: Welcome to this video on database relationships. In this video, we will quickly go over what database relationships are on the different types off database relationships. Database relationships allow data to be stored in separate tables while still linking the data between tables. For example, in a movie database like I M D B, we would have a movies table containing data on the movies on. We would have a separate directors table with information on the directors. Movies and directors are connected. Movies have a director on directors direct movies, so we would need to create a relationship between these two tables to link the data between the two tables. We create this link or relationship using primary key columns and foreign key columns in the movies table. We would have a foreign key column called Director I D, which is referencing the primary key column in the director's table. So the two tables are linked together by these two columns, and these two tables have a relationship. There are free, different types of data base relationships. We will go through each one in this section. The three relationships are 1 to 1 relationships, one to many relationships on many to many relationships 3. Primary Keys and Foreign Keys: Welcome back in this video, we will look at primary keys on foreign keys. So, firstly, primary keys. So a primary key is a column or a combination of columns, which uniquely identifies a row of data or a record in a table. The values in a primary key column must be unique and cannot be. No. What I mean by cannot be no is that every record must have a value in the primary. Key column. There can't be any missing data in the primary key that can only be one primary key per table. But primary keys are not compulsory, but it is good practice tohave one in every table, and it is highly advice tohave one in every table. So now let's take a look at an example of a primary G column. Here we have the owners table, which contains data on people who own pets in the USA. So the table has five columns. I d first name, last name, city and state. So now we need to decide which column is gonna be our primary key. Remembering the primary key must be unique and cannot be No, we don't need to worry about missing data as we can see that each column has data for each record. There's no missing data in this table, but we do need to think about uniqueness now. It doesn't make sense to use the first name or last thing columns as our primary key because people can have the same names. We could have multiple Samuels or multiple Emma's in our table. And if you look at the Last Names column, you can see that we have to. Smith's already. So The values are not unique in the first name or last name columns, and it's the same for the city and the state columns. Millions of people live in Boston, New York, New York, and millions of people live in each state, so they are not unique to a single person. So that just leaves the I D column, which is unique for each row of data added. The value in the I. D column is just going to increment up with a unique number. So 1234567 etcetera. So in this table, the I D column uniquely identifies each of data in the table, and it's the prime wiki for this table. So now let's take a look at foreign keys. So foreign keys are columns, which are used to link to table together in a relationship. A foreign key is a column where the values match the values off another tables. Primary KEY COLUMN So we have a column in one table, which is referencing the data of a column in another table and forming a relationship between the data in both tables. The table with the primary key is called the reference or parent table, and the table with the foreign key is called the child table. A table can contain multiple foreign key columns, and it is also perfectly normal for a table to not contain any foreign keys at all. So now let's look an example of a foreign key column. Here we have the Pets Table, which contains data on the pets off the owners from the previous slide. Again, we have an I D column, which is this table's pro Mickey, and we also have columns containing the species of the pet. The name of the pet on the age of the pair and in the final column is called Owner I D. This owner i d column is a foreign key column is referencing the i. D column in the owner's table. So this column is linking the owners table on the pets table together in a relationship so we can see that the dog Rex has an owner. I d off one, which, if we go back to the owners table, we can see that is referencing Samuel Smith from Boston, Massachusetts. And we can also see that the dog, Biggles, is also owned by Sam Ing Smith from Boston because he has an owner i d of one as well. So this is how we can relate tables together in a relational database, and we will see how we can use these relationships later in the course. 4. One to One Relationships: in this video, we will go through 1 to 1 relationships, 1 to 1. Relationships are the simplest of three types of database relationships, but they are not that common within databases. A once one relationship is where a primary key value of one table can appear a maximum of once in the foreign key column off another table. So for each row of data in the primary key table, there is either zero or one row of data in the foreign key table. So if we look at the two tables here, we have a primary key column from Table one on the left. On a foreign key column From table to on the rights, you can see the values in the primary key column are 12345 etcetera. And the values in the foreign key column only contained one instance of each value, so we have 13 to 5 and four, so these two tables have a 1 to 1 relationship. An example of a want one relationship from a Movies data database like IMDb is the relationship between the movies table on the movies revenue table. So the movie's table contains data on the movies, such as its name, its length, its language, The release day etcetera on the movie revenues table contains data on the revenue off the movie, so them the money it made at the cinema. You can see that we have a column in the movie revenues table called movie I D, which is a foreign key column referencing the primary key column in the movies table. The movie revenues table contains revenue information for each movie in the movies table, so we only have one row of data in the movie revenues table for each movie. So there is a 1 to 1 relationship between these two tables, So if we look at the movie I D values in the movie revenues table, you can see they go 45 13 23 44 1 and 50 free. So the value of one is referencing movie I. D. One in the movies table. So this row of data is for a Clockwork Orange, and this is the only row of data for a Clockwork Orange so we can see that a Clockwork Orange took $27.1 million we have no information on the international takings once one relationships are quite rare because we could have put the revenues data in the movies table directly rather than creating a separate table. But sometimes you want to separate the data into separate tables if the data is financial or if the data is sensitive, and then we can create a 1 to 1 relationship between the two tables. In a database diagram, a 1 to 1 relationship between two tables is represented. Like the diagram here, Table one has the primary key column. So is the parent table, and Table two has the foreign key column, and it is the child table. So we have a line between the two tables and then two vertical lines beside the parent table with the one This represents that there is one row in the parent table on then by Table two, we have a circle with two vertical lines with a zero dot dot one to represent that there is either zero or one row of data in the child table, so that's it for once. One relationships. Now let's take a look at one to many relationships 5. One to Many Relationships: Welcome back in this video, we will take a look at one to many relationships, which are the most common type of database relationship. A one to many relationship is where a primary key column value can appear multiple times in the foreign. Key column If you look at the diagram here, you can see Table one containing the primary. Key column on Table two with the Foreign Key column. The primary key column contains unique values starting from one. So 12345 etcetera. And then the foreign key column contains 01 or any number of these values. So we have one, then two, then four, then four again and then won again. So we have multiple ones and fours. So these two tables have a one to many relationship. A real world example of a one to many relationship would be a company tracking customer orders. They would have a customer's table containing customer information such as name, email, address on orders, table containing order information. A customer complaints as many orders as they like with the company. But any single order can only be associate ID with one customer, so the customer on orders table contain a one to many relationship. Another example would be instagram users and photos on Instagram User composed many photos , but a photo can only belong to one user. So there is a one to many relationship between the users and the photos table. An example from a movie data database such as IMDb off a one to many relationship would be between the directors table on the movies table so a director can direct any number of movies. But a movie can only be directed by one director. You can see we have the directors table with the director i D. Primary Key column, and then we have the movies table with the director i D. Foreign Key COLUMN The movie stable here has been ordered by the director I D column. If we look at the first movie in the movie stable, let the right one in. We can see the director I d is equal toe one, which, if we then look in the director's table, we can see that this is Thomas Alfredson. So Thomas Alfredson directed Let the right one in. Then the second movie is There will Be Blood with a director. I D off two, which is poo Anderson. Then we have the next three movies. Rushmore, the Dodge Eating Limited and Grand Budapest Hotel all have a director idea free, which is Wes Anderson. So you can see we can have. Multiple primary key column values in the Foreign Key column, meaning that there is a one to many relationship between the directors and movies table. We have multiple values of free on multiple values of five in a director I D column in the movies table in a database diagram, a one to many relationship between two tables is represented like here. So we have Table one, which contains a primary key column and table to which contains the foreign Key column. We have a line connecting the two tables. We have the two vertical lines under one by table, one in a circle and three lines in a fault shape by Table two. This represents that one row of data in Table one can be related to zero or many rows of data in Table two, meaning there is a one to many relationship between two tables. Next, we will look at the final database relationship, which is the many to many relationship 6. Many to Many Relationships: in this video, we will look at the final database relationship type many to many relationships. These relationships are also very common in relational databases. A many to many relationship is where two tables can have multiple instances of each other. Examples off, many to many relationships would be authors on books so an author can write many books and books can have multiple offers. For example, the authors Terry Pratchett and Neil Gaiman wrote the book Good Omens on. Another example would be Terry Pratchett and Stephen Baxter, who co authored the Long Earth Siris of books. So an author's table and the books table would have a many to many relationship, even though most books just have one offer for many to many relationships, we can't just use primary keys and foreign keys because this would violate the uniqueness off the primary key columns you can see in a diagram we have two tables which have a many to many relationship. For many to many relationship, we would need a foreign key column in each table, which references the primary key column off the other table. But putting a foreign key in each table means that we have repeated values in the primary Key columns, which is not allowed as a primary key, can only contain unique values. If we look a table to, you can see that the primary key values three and four, both of a foreign key value off to this means that in Table One, we need two rows of data for primary key value to, so we can't simply use primary key on foreign key columns to link to tables. In a many to many relationship we need to use 1/3 table on this third table is known as a junction table, so let's take a look. That's an example of a many to many relationship from a movie data database such as I M. D. B. In this database, we would have a movie stable containing data on the movies on also an actor's table containing data on the actors. Such is their first name, their last name agenda on their date of birth. These two tables have a many to many relationship because movies can contain many actors in them. On actors can perform in many different movies, but we don't have foreign keys and either off the movies or actors table to link the two tables together. Instead, we have 1/3 table called movies, Actors or movies Underscore actors. This third table is called a Junction Table, as it just contains two foreign key columns. It contains a movie i. D Foreign Key column, which references the primary key in the movies table. And it contains an actor i. D Foreign Key column, which references the primary key column in the actor's table. So the movie's Underscore Actors table creates the relationship between the movies table on the actors table. We can see that the actor idea of one, which is Malin Akerman, is in movie I. D 52. Actor Rieti to is in movie I. D 50 on except for etcetera. And if we look at actor I D Aids, we can see that they were in both movie i. D 15 on movie I. D 40. A Junction table still needed Zane Primary key. If you remember in the primary key video earlier in this class, I said that primary keys can be a combination of columns. So, in this case, the primary key in the movies Actors Table is a combination off the movie I d on the actor I D column. A junction table can also contain 1/3 column, which would be a primary key column. But this is not necessary, as the only real function off this table is to create a many to many relationship between other tables. So this is how we represent a many to many relationship in a database diagram table one and table to have a many to many relationship. And then we create a junctions able between the two tables, and then Table one has a one to many relationship with. The Junction table on Table two has a one to many relationship with the junction table. This way we can keep the primary key columns in Table one and Table two unique, so you can think over many to many relationship as 21 to many relationships with a junction table. So now that we've gone through each type of database relationship, we're ready to complete the class project 7. Class Project: congratulations for completing this class. Now it's time for the project, so the project is to draw a database diagram to represent the relationships in the movie data database. So in this class we've looked at some tables from a movie data database, and now it's time to draw the database diagram representing the relationships between the tables. So the four tables in our movie data database are a director's table on Actors Table A movies table on a movie. Revenues stable on each of these tables has relationships with other tables within the database on its your job or your task to draw the database diagram to represent the relationships between the tables. So you have to think whether two tables have a 1 to 1 relationship, a one to many relationship or many to many relationship with each other on hints. If they have a many to many relationship, then you're gonna have to create an additional table in your diagram. So good luck with your projects