Hey there everyone. Welcome back to topic one in our database class. In this video, part four, I'm going to provide you with a very brief introduction to relational databases. One very interesting property of the relational databases that we will be learning about throughout our time together this semester is that relational databases by their very nature automatically eliminate lots of these problems that can arise when we might otherwise try to store information in the form of the list. So simply by the design of a relational database, we can avoid problems of redundancy. We can avoid the types of insertion, modify, or insertion, update, or delete problems that can occur because we are trying to store information about multiple things in one place. So the nature of a properly designed and implemented relational database is to automatically with no further effort prevent all of those types of problems that we've explored earlier today that can arise when we attempt to store information in the form of a simple list. So let's learn now, we'll start to begin to introduce some of these foundational concepts that are related to relational databases. The first of these is that in a relational database, we store information in the form of tables. So we have tables of data. And a table is just a two dimensional data structure that's going to consist of rows and columns. So maybe I have three columns and I don't know, four or five rows. This is just a table of data, right? And the relational database world, we store information in the form of tables, but, and this is a key thing for you to understand that each theme or business concept information about each theme or business concept is stored in its own separate table. So put differently. Each table contains information about only one business concept. But we may, for example, have a couple of different tables. So just for the sake of simplicity, all. And the basic idea is that each of these tables holds information about just one theme or business concept. So maybe with this table here, we store information about customers and only customers. The only information that we're Survivors in that table is customer information. And then maybe you're here. We store information about products. And this is the only type of information that is stored in this table. We don't put any customer information over here. We don't put any employee information over here. We don't put any supplier information. This table contains just information about products. And this table contains just information about customers. And then we would have additional tables as part of our relational database that contain other information about specific business concepts or themes. So we would have, say, an additional table that holds information about employees, another that holds information about departments, another that holds information about orders. And another that holds information about suppliers. So lots and lots of different tables. But the main idea is that each one of those tables contains information about just one thing. So one way of thinking about this then is that in relational databases, essentially what we're doing is we're taking these big lists of data. And we're breaking them apart into smaller chunks. Previously, for example, we saw an example where we had their information and vehicle information stored in the same list. So what we might do, if we were to try to migrate that information to a relational database is that we would break that list apart into separate tables. We would have one table that stores information about customers and another table that stores information about vehicles. As another example, if we're thinking about the project and project managers scenario that we described earlier, we might take that list of information about projects and break it apart into several tables. I think we might have information about the customer there information about the projects in a separate table and information about our project managers in a third table. The basic idea is that we have several tables, each of which contains information about just one thing, one business concept or a fee. So if you're following along this far, you are well on your way to understanding and appreciating relational databases. If you imagine that we have taken a big list of data that previously contained information about several different things and we've broken it apart into smaller chunks. Tables, each of which contains information about just one thing. And you further imagine that I don't know, maybe we want to generate a report. Times will arise when we will need to join information in those tables back together. For example, using our scenario of customers and cars, if we have stored that information in two separate tables, one of which contains information about customers and the other of which contains information about cars. And we just simply wanted to generate a list, say, give me a list of all of the customers who own Volkswagen's along with the information about their cars. You can imagine we would need to pull information from each of those two tables and link it together in order to accomplish that task. In this case, in order to generate that report. This means in the relational database world, we often need to, we have reasons for wanting to join our tables together, at least temporarily. We join them back together, this task of linking the records together is called a join. And in the relational database world, the way that we keep track of the connections between items in different tables is by using matched pairs of data values. Okay, as an example, if we're doing this example of, say, projects and customers. And we can say that maybe we have a scenario that looks a little something like this. Where we have some columns like a customer ID. Information about customers and I'll keep this very simple. So maybe the customer's name. And that's where we store information about customers and then we have a separate table where we store information about projects. So if we have a project ID and the description. And that'll just for the sake of something interesting like a budget. So imagine that we have these properties that collectively define what a project is for us. And then we would just fill in the data values. So we might have information about customers and it looks like this. I'll just draw some out here. So customer number one is Dan. Sorry, I'm going to use very short names because it's pretty hard to write with this. Looks like a writing with a crayon writing crayons or big markers in kindergarten or something. So we'll have, I don't know. Show will be our other person. And then over here we can store information about projects. So this might be project number 101. Object number 102. Project number 103, etc. And whatever these are. Blah blah blah blah blah blah blah blah. And we set a budget for each one. So maybe we're working in dollars. This one has a $5 budget and this one has a. $20 budget. That's the last one. Is a. $10,000 budget. Okay. So just storing information here about projects and customers. But the question then is how do I keep track if I'm have information in these separate tables. How do I keep track of which customers are associated with which projects and the way that we do that in the relational database world is through matched pairs of values. So in this case, you put a note up here just to remind us that this is a project table. So it's a project. I'm sorry, let me make that yellow so it matches. There we go. And this was our customer table over here. All right. So let's put some labels on there. So what we do in the relational database world is we would create an additional column over here in our project table. Where we would record the ID number. Of the customer. Who is associated. With each of our projects. So in this case, we might say that project number 101 is a Dan project. And 102 is a CHO project. And 103 is another Dan project. So what we have done then is we've created this model where we have matching pairs of values. So if I wanted to ask some questions now and I wanted to say, okay, which projects are associated with the customer Dan, I can look here in the customer table and say, okay, Dan is customer number one. And then all I need to do is look through by project table and say, okay, is this customer number one, yes. Is this customer number one, no. Is this customer number one, yes. Okay, so in that way then I have enabled to figure out that customer Dan. Is associated with projects 101 and 103 because we have these matching values, right, the customer ID here matches the value of the customer ID there. And as well here as well. So this is how we link things together. In the relational database world, it's just through matched pairs of value. If I wanted to ask the same question like which projects are associated with Cho, I would look here and be like, okay, Cho has an ID number of two. And then I would look over here in the customer ID column in the project table. And I would say, oh, hey look, here's a two. So we know that project is a Cho project, right, so Cho is associated with project number 102. So pretty simple, but nevertheless, as we'll learn throughout our time together in this class, this simple idea of just separating information. And as we only store information about one thing in each table. And then linking the information together based on matched pairs of values has extraordinary and enormous benefits for us. So just another illustration, bringing us back to our scenario involving customers and cars from earlier in our class. So if you remember we had customers and cars in one list, in this case we've broken them apart into two separate tables, one of which contains information about customers. And one of which contains information about cars. Now before I proceed, I want to point something out here. One of these columns like car ID. Let's go hybrid thing, right, we couldn't say that a car ID is a property or an attribute of a car. But it's also information about a customer. Because it tells us which. Car a particular customer owns. So we can make an argument that a car ID in this case is something related to a customer and also something related to a car. So a serves as a point of intersection or overlap for us. Now if we wanted to keep track then of which customers had which vehicles. All we would need to do is fill in these values of car ID. So here's 102 that was pennies car Raj has the 2014 BMW so put in 101 out there. Leonard had the 2010 Nissan centra so 103 is Leonard's car. And as we noted previously Sheldon had the same car as penny. So we just record 102 there. Now you'll note that this strategy. Eliminates the need for us to record information about the existence of a 2003 Volkswagen golf more than once. With this model where we store car information in the car table and customer information in a customer table. We only need to record information about each car or each customer one time. There is no redundant information right I don't have two rows here like I did before where I was storing the 2003 Volkswagen golf. So I can have as many new customers as I need. Let's say my next three customers. All the decide that they want to purchase a Volkswagen golf. Well I don't need to add any additional car information I just need to record the fact that those customers have a Volkswagen golf so maybe these ones are 102. I don't know maybe somebody else gets a 2014 BMW so put a one on one in there. And then at any time if we want to figure out who owns what we have to do is follow the connections from one table to the next. So I want to ask a question over here and say hey give me a list of everybody that owns a 2014 BMW. All you would need to do is identify that is car number 101 and then we can scan through our list of customers and say this customer owns a 2014 BMW and so does this one down here. So to leave we can go in the opposite direction, we could say what car does Leonard own. So we find Leonard's car ID 103 and then we can just scan through our car IDs over here until we find a match. So these records are linked together and then we can answer the question Leonard has a 2010 Nissan center. So we separate the information out we no longer have any necessity of recording redundant data on a robot basis. And simply by doing this we have eliminated possibilities of insertion problems update problems or delete problems simply by storing the information in this format rather than as a single list curious. Naturally, if we're going to take a list of data and break it apart into a bunch of different tables and then link all of those tables together based on matching pairs of values. That sounds like more work and indeed it is it is more work relational databases are inherently more complicated than lists. However, the advantages that we gain by storing information in relational database far exceed whatever sort of additional complexity we may need to impose in order to store the information in relational format. So we get extraordinary advantages in terms of efficiency data storage performance when we need to make changes to data etc query performance as much much faster in this format. So lots and lots of advantages. Yes, it is more complex, but there are lots and lots of advantages that we gain as a result of that additional complexity. So here's another example just showing these records linked together, in this case we have projects that we do that involve various hardware products. So maybe we're doing quotes for customers or suppliers are giving us quotes for different items these types of things. And you can just see how we link those things together here. So in this particular example we have four tables we store information about suppliers here and we store information about the various items that we have in our inventory here. And we store information about projects here. And then we can store information about quotes here, where a quote is intersection of a project and an item and a supplier. So we can look at any of these and I get I know it's very small I'm sorry about that. And look at any of these rows of data and we can link things together, I can say okay what is the quoted price for a particular item for a particular project given that they want to purchase, I don't know say 15 of the items. So we can then record that information here. So this is a bigger thing but the concepts are all the same we just link things together based on match pairs of values. Right, so this project ID matches, sorry arrow there this project ID. Here in row one links us up to this project record in the project table. This item number 1300 links us to our say 1100 links us to this item over here in our item table and this supplier ID here links us down to this supplier and our supplier table. So at any time if we're looking through this quote table, we need to know more information about the associated supplier item or project. We can find that information simply by following these connections between the rows in the different tables and those connections are established by using matching pairs of values. In this case supplier IDs item numbers and project IDs will linking these different tables together in a way that supports the operations of this business.