E.F. Codd's relational model

- [Instructor] Data that's structured following the principles of the relational model make up the very core of our modern lives. Everything from the transactions in your bank account to the status updates that you post to social media are almost certainly managed and stored in relational databases. The birth of the modern relational database can be traced back to an English data scientist named Edgar Frank Codd. E.F. Codd worked for IBM in the 1960s, and in 1970 he published a paper titled A Relational Model of Data for Large Shared Data Banks. In it, Codd defined a new way of structuring data so that the process of retrieving information became independent of the way that it was stored. His relational model did this by defining rules for how traditional data tables should be organized and how data should be split up across multiple tables that are related based off of common values. Let's take a look at the example of our vacation pictures again. Here I've organized information about each photo, where it was taken, and who's in it across three different data tables. Each of these tables can grow vertically as we add new pictures, new locations, and new people to the library of images. But if we add a new picture taken in London, we don't actually need to touch the location's table. We would simply make reference to the location number three in the appropriate column of the pictures table. In the relational model, these values that like the location number, are used to look up additional details in related tables, are called keys. Using the key values we can connect or relate data found in one table to the data stored in another. Now at first glance, traversing these tables through their related keys might feel like an additional step that you need to go through to find the complete details about a specific image. First you need to find the key value, and then you need to look that up in a second table. The beauty of a relational database is that you personally don't need to do that. That's what the computer is for. You simply ask to see pictures taken in London, the computer will quickly translate that request to understand that London is location three, and then it'll search through the pictures table to find the ones that match. As a database user, there's never any real reason you need to know that internally London is stored as location three. All of the work of traversing from one table to the other is handled behind the scenes by the database engine. Structuring data using Codd's relational model brings a number of benefits to users. First, it's efficient. Data values are stored a single time and then referenced from other tables. This not only benefits the size of the database, but it can also help prevent integrity errors. For instance, if a city name were misspelled, the incorrect spelling would be consistently referenced across all of the related records. And it would be easy to fix by updating the single value in a single table. Second, the relational model is highly scalable. Each table only needs to grow vertically when appropriate. In this example, we could easily accommodate pictures with no people, one person, or an entire tour group simply by adding additional rows to the people table when necessary. And third, it's easy to retrieve information from a relational database. You have the flexibility to ask questions of the data through a process called querying. To demonstrate how querying works, consider this exercise. Using just the three tables shown, can you find any pictures taken in Paris featuring James? You can pause the video if you want to work through the question on your own. To find the answer, you can look to the locations table to see that Paris is location number two. Then go to the pictures table and find that pictures zero zero three and zero zero four were both taken in Paris. Then finally you'll move over to the people table to find that of those two images, only zero zero three is a picture of James. So now we know that James only appears in the Eiffel Tower picture. Using the querying power of a relational database, arriving at that same answer is a relatively trivial process. All you need to do is ask the question and the database engine will put the pieces together as needed to find the answer.