CS50 for Business - Lecture 7 - Deploying Databases

Key Takeaways

[Music] Hello world. My name is David Men and today is all about deploying databases particularly when we have lots and lots of data that we want to store at scale. Now what do we mean by data? Well, really just information and that information can be textual in nature or perhaps binary in nature that is to say files and such. And at the end of the day, we want to store all of this data in a so-called database, which is a collection of data in a computer system. Now, databases specifically tend to be actual products that you can download and install on a computer. For instance, Oracle and SQL Server, MySQL, and Postgress. But there's different types of databases altogether. And along the way today, we'll explore exactly what forms those take. But let's consider then for example what some other terms might be that we encounter in this world of data more broadly. You might hear of a data warehouse which is really a database of databases. All of your data somehow combined in one place. Uh a data mart which is actually just a subset thereof. So this might just be your marketing database, financial database or something else more narrowly defined. And then you have data lakes which are sort of hot messes of data whereby you might just dump all of your data in PDFs and files and CSV files and more generally with the intent of coming back to it later but it's not necessarily as organized as other formats might take but today we'll focus indeed on databases which are organized collections of data and those collections themselves can take different forms. For instance, perhaps the simplest way of storing a lot of data is inside of a so-called flat file database, which literally means just storing your data in a file, maybe a text file, maybe a binary file. Now, that file might be structured, for instance, a very common format for flat file databases are CSV files for commaepparated values, whereby all of the textual data in your file is somehow organized effectively into rows and columns. The rows are straightforward to implement in a so-called CSV file because you just put one line of data per row per row per row just hitting enter at the end of each of those lines. And then within each of those lines or rows, you can indicate the presence of a column or more specifically an individual cell by separating the data on that line with for instance commas. Now there's alternatives to these. You might be familiar with TSV files, tab separated values. Sometimes people use vertical bars in order to delineate data within such a file. The choice of character is really up to you. But it does influence exactly what type of software or how the software reads that file subsequently. Now if we want to take a look at an example, let's do just that. We've seen in the past some some sample data involving like names and numbers. And in fact, I brought such as an example with me already. Let me go over here to VS Code. And as usual, just to simplify the interface, let me go ahead and hide my activity bar, hide my file explorer. And then down here in my terminal, let's go ahead and open up a file that I made in advance called phonebook.csv for again commaepparated values. What do we see? Well, the first line of this file is curiously a little bit different from all of the others. It's the so-called header row in the file. And you'll see that it says name, number. And that effectively indicates that this file effectively has two columns worth of data. The first of which represents names, the second of which represents numbers, phone numbers specifically. The comma therefore indicates exactly where one column begins and ends. Therefore, below that we have those names and numbers, Brian, David, Doug, Eric, and John followed by each of their numbers with commas therein. Now, we got lucky in some sense in this particular data set in that none of the names and none of the numbers naturally have commas themselves. But long suffice it to say that if any of your data in a CSV file actually needed to contain actual commas grammatically, that's fine. There's different ways of escaping the data, for instance, putting all of the data in one cell, so to speak, in for instance, double quotes. It just requires that whatever software you're using to open or process these files knows to treat those quotes in such a way that they're not literal quotes but meant to keep together words or phrases that might otherwise have a comma that could confuse that whole process. But this data, nicely enough, is fairly simplistic and we have ultimately a whole bunch of names and numbers in this file. But what we don't have is the ability right now to actually read this data very in a very user-friendly way. We could certainly write some Python code that reads this file top to bottom, left to right using something like a loop in Python. But if I wanted to look up my number specifically or John's number specifically, we would essentially have to read the entire file searching for searching for searching for my name or John's at which point then we have the actual name. Moreover, if we want to make changes to this file or we want to make additions or deletions, that's a fairly manual process whereby we have to open up the whole file, go in there and make the changes. In other words, in so far as this is just a flat file, a file alone, there's no inherent functionality offered to me. To get more user-friendly functionality, I dare say we need to transition from a flat file database to something like a relational database where a relational database generally refers to software that not only stores your data for you but also provides you with some core functionality. Moreover, that software allows you to relate data within your set of data to each other in different ways. Let's consider for instance a simple example. Here is a data set that we've seen before. three schools Harvard, MIT and Oxford and the cities that respectively each of them is in. It happens to be the case that both Harvard and MIT are in the exact same city of Cambridge but otherwise Oxford is across the pond in Oxford UK. But suppose we add another uh school and city to the mix namely the University of Cambridge also in the UK in the city of Cambridge. Now this city of Cambridge is different from this city of Cambridge. So now we have two problems. We have arguably an inefficiency in so far as for both Harvard and MIT we're storing Cambridge which seems at best redundant to have to say the same word Cambridge in two places per our discussion in the past of just trying to avoid duplication even though this case factually it's true. There's also now a problem of disambiguation whereby this Cambridge is in the UK these are in the US but at first glance it's not obvious that those are actually different. Now we could chip away at some of these problems by just adding for instance Cambridge, UK and Cambridge, Massachusetts, USA to be ever more precise. But that still doesn't eliminate the redundancy and it also doesn't fundamentally ensure long-term that we're not going to run into the same problem again. So let me propose that we take a data set like this which might be stored very simplistically in a CSV file but begin to take steps towards storing it in a relational database instead. For instance let me propose that we explode this single table of data into two tables initially one for the schools one for the names. Then let me propose that we assign a unique identifier to each of those cities. For instance a very simple number like an integer starting with the number one on up. These ids could technically be anything, but by convention, it's often the case that you just use simple integers starting at one, moving up toward eventually infinity. Now, this in and of itself hasn't really done much for us because I haven't yet related those cities to these schools. But suppose now I go over to this table of schools and in addition to their names, I add another column that's going to allow me to keep track of the city in which that school is, but no longer using its name. using that city ID instead. So now I have an indication that both Harvard and MIT are in the same city aka number one. Oxford is in the city aka number two. But Cambridge is in the city of number three thereby disambiguating between the two Camidages. Now I can maybe just for thoroughess and for future proofing in case we encounter more problems like these add another column to this first table for school ID. Now those numbers can and in the real world are very often the same 1 2 3 on up toward infinity but they're not used for the same purpose. It's perfectly fine for multiple tables all to use very simple integers even though those integers might indeed overlap. Context is going to matter and indeed that is why in this table at left which I might call my schools table I have city ID very explicitly stated so that it's not going to be misinterpreted as a school ID. So these 1 2 3 4s are different from these 1 123s, but they were both ids in the sense that I'm now using them to uniquely identify my data. And thus was born the first of our relational tables inside of, if you will, a relational database. This is a database in so far, it's a collection of data. And it's relational in so far as I have used these unique identifiers to relate some of my data to other data. The downside now is that this does not look particularly userfriendly. I took the simplest and most convenient of file formats, that of a CSV for instance, that had all of the names and numbers right there in front of me. Indeed, we saw that a moment ago in tabular form, and now I've created two tables with these arbitrary numbers that somehow create these associations, but I've just created work for myself because now I have to minimally sort of line up the numbers and figure out which school is in which city. So, wouldn't it be nice if the database itself provided me with a bit of functionality that allowed me to just as easily as before get at the answers I want when it comes to this data? And the answer I might want is in what city is Harvard, in what city is Oxford, and so forth. Well, that too is what we typically get in a relational database, which is some inherent functionality that we can leverage to solve problems like those. So, what does this mean? Well, we're going to introduce ourselves now to another programming language. This one called SQL for structured query language or SQL for short. And SQL is typically the language used with relational databases via which to query data and also update your data and delete data and insert more data and do anything you might want with it. It allows you then with code to automate the process of more generally reading and writing data. So what does this allow us to do? Well, really four basic operations and there's this acronym that might help you remember it. CRUD, which a bit crudely stated, which has four letters in it, each of which represents one of the core features of, for instance, SQL. C stands for create, R stands for read, U stands for update, and D stands for delete. So even though we're about to dive into really a whole new programming language that does has some similarities with languages like Python and even JavaScript at the end of the day all it can do is these four fundamental operations. Now these themselves are not necessarily the commands or functions if you will that will run within the SQL database but they're quite similar. In fact, we'll see that in the world of SQL uh reading data that is uh accessing it and using the data in some form actually uses a command called select but create and update and delete are in fact used to do any number of things. Insert also exists when we want to insert new data. And if we want to not only delete data but drop all of our data, that is an entire table thereof, we can use a SQL command known as drop as well. So, even though we're about to take a tour of a brand new programming language, you'll find that SQL is incredibly powerful when it comes to manipulating data, reading and writing it, answering questions about data, doing data analysis, data science more generally. And indeed, SQL is especially powerful in so far as it's a different type of programming language than both Python and JavaScript. Whereas both of those languages were, at least as we saw them, very procedural in nature. If you wanted to use them to solve a problem, you had to tell the computer step by step using Python or JavaScript lines of code exactly what to do, exactly how to iterate, exactly what questions or boolean expressions to ask. SQL is known as a declarative language where it's closer to using English to just declare what question you have without worrying as much how to specify how to get from that question to the answer. you needn't worry so much about loops, so much about variables, so much about conditions. You can instead say they're say a little closer to English, exactly what data you would like to select, update, delete, or insert into your database. So let's take a look. Given that we have these commands in front of us, let's go ahead and explore how we might create one such table. Here for instance is the first of the commands that we might execute when trying to create a SQL table in a relational database for the very first time. You would literally type out create table. Then per the lowercase here, you would type the name for your table. Maybe it's phone book or something else. Then in parenthesis, you would specify the name of a column that you want to store therein. For instance, name or number. And then some specification of type. Is this a number? Is this a string of text or something else? And then you can do that one or more times for additional columns as well. But how do you do this and where do you do this? Well, you can use any number of database systems. Indeed, I mentioned Oracle and SQL Server, MySQL, Postgress, and dozens of more dozens more. But we're going to use a fairly simple version of SQL for this particular class so that we can focus really on the ideas and not necessarily anything proprietary in nature. And we're going to use a variant of SQL called SQL light. And the command that we'll use to interact with this lightweight version of SQL is itself called SQLite 3. Now, this isn't to say that SQLite is a toy version of SQL. In fact, it's very commonly used in web browsers and mobile applications alike, but it doesn't necessarily have as many features as some of those commercial and other open- source tools might have that are particularly well suited for the largest sets of data. Now, we'll still use some fairly large data sets today, but not necessarily in the millions and billions of rows that you might have out there in the real world. So, how can I use SQLite 3 for instance to create my first database and within it some table? Well, let me propose that we first create a database as follows. Let me go back to VS Code here. Let me go ahead and close phone.csv. But in a moment, we'll use that within our own database. I'm going to go ahead and increase the size of my terminal window here in order to see more on the screen. And I'm going to type this command SQLite 3 space phonebook. DB just by convention. I could call this file anything I want, but DB will indicate to me and other people that it's a database. And indeed, I want the data therein to represent that of a phone book. Now, how can I actually get some pre-existing data into this database? Well, at the moment, there's nothing inside of it. I've just created an empty database, but there's a few commands supported by SQLite 3 specifically and Oracle and SQL Server and MySQL and Postgress and others have comparable commands. I'm going to go ahead and temporarily put my SQLite 3 program into CSV mode. I'm then going to import my existing phonebook.csv file into a specific table and then I'm going to go ahead and quit al together just to prove that it's worked. All right, so let's do that. Let me go back here to VS Code. Let me go ahead now and at my SQL light prompt, note my prompt has changed from the usual dollar sign to SQLite and an angle bracket to indicate that the SQLite 3 program is still running. I'm going to go ahead and type mode CSV and nothing seems to happen. But in general, that's a good thing. If I see no errors, I'm then going to go ahead and import the file called phonebook.csv, which recall is in the same folder in which I ran this command a moment ago. And I could call this anything, but naturally I'm going to call the table into which I want to load all of this files contents quite simply phone book. And now you'll note within SQLite's prompt, I'm using these dot commands to indicate that these are specific to SQLite 3. But before long, I'm going to no longer have those dots in front of my commands because they're going to be SQL commands from the programming itself uh moving forward. So I'll hit enter there, too. Nothing seems to happen. So let's do dotquit to exit out. And I'm back at my prompt. At this point in time, I now have a brand new database file called because I chose this name phonebook. DB. And I can proceed now to reopen that for instance and show you what's inside. So let me again run SQLite 3 of phonebook. DB and hit enter. I'm back at my prompt. And nothing seemingly has happened yet. But if I run schema now, this is going to show me the so-called schema of my database. The schema of a database is the design of its columns in particular, the names thereof and the types thereof, as well as potentially some additional constraints. So here at my SQL light prompt, if I do schema, what I'll see is a command that I didn't actually run, but that was automatically run for me when I imported that phonebook.csv. This is not necessarily the typical way that you would create a database out of a CSV file, but it's one way. Though, in the future, when we know in advance what kind of data and how much of it we're going to have, and it's not necessarily a file we're importing from the start, we're going to go ahead and design the schema for our future tables and then load in data. But in this case, I've used that. command specific to SQLite 3 just to get things started more quickly. And you'll see here effectively the line of SQL code that the import command automatically triggered for me. Create table if not exist just in case I might have run this command before. Quote unquote phone book which is the name of the table I wanted to create a column called name the type of which is text and another column called number the type of which is text as well. And all of that as before is inside of those parenthesis. And then lastly, I finished my thought here, it would seem, with a semicolon, which in many programming languages, SQL often among them, is how you would terminate a thought, much like a period after an English sentence. Now, I'm back at my prompt, but there's no data apparent just yet. All we've seen is the schema, so to speak, of my database, but not the data therein. So, how do I get at those names and numbers within this new database? Well, here's the canonical format of a select command via which you can select that is read or access data in one of these tables. The command syntax is to say select then you can specify one or more columns that you want to select from a table and you can specify the name of that table. Now I've convention here done this. I've used all capital letters in uppercase for anything that's SQL specific. So there are keywords in SQL much like we saw in Python and JavaScript among which here are select and from but the columns happen to be my choice of names as is the name of the table. So just for clarity I've used lowercase there. That's not strictly necessary. SQL light and in general SQL the language won't really care what you uppercase with respect to commands but the column names the table names those should be considered case sensitive so that you are consistent with how you name them from the get- go so how can I go about selecting data from this first table well let me go back to VS Code here let me zoom in a bit so the text is a bit bigger and now let me go ahead and clear my SQLite command my SQLite prompt so that I can focus entirely on what's new I'm going to go ahead now and just per that canonical example type select. And now I want to select the columns of interest which will be name, number and I want to select those two columns from the table called phone book and I'll finish my thought with a semicolon. When I now hit enter you'll see using sort of asy art if you will the tabular form of the data that was loaded from that CSV now into this SQLite database. And here we have the column of names and the column of numbers. But notice this perfectly corresponds to what I selected. Now if for some reason I don't care to see the numbers at this point in time, I can do something slightly different. I can just say select name from phone book and indeed SQL will give me just that. Meanwhile, I can do select number from phone book and similarly will the database oblige. So already here I seem to have more expressive capabilities than with something like a flat file database in CSV whereby even though I could open that CSV either in VS code or Microsoft Excel, Google Sheets, Apple Numbers, I wouldn't necessarily have this precision with which I can select subsets of data. I could see it with my human eyes. And to be fair, in those spreadsheet programs, we do have lots of functions, but we'll see we have functions inside of a SQL database as well. Now, it turns out when you want to just select everything in a database table, if only to wrap your mind on what's inside of that table. Turns out there's a common convention, which is to use a wild card, which is something we've seen in the past, albeit in different contexts. I can do select star from phone book. And no matter what the columns in the phone book table are called, this is going to go ahead and give me everything as we saw before. So, it's not strictly necessary for me to enumerate each and every column, especially if there's lots of them. But indeed SQL has supports a whole bunch of functions as well. And particularly when your data is numeric, you can do things like average things together. Find lower uh find maxim uh maxima and minima. So you can if it's textual in nature, you can force things to uppercase or lowerase. You can check for distinct values that are unique and you can even count the amount of data there in. So how might we use some of these? Well, let me go back to VS Code here again. and I'll clear my terminal and propose that if your question is not what are the names herein and what are the numbers but how many of them are there we can do this in a number of ways we can do select count the number of names from phone book and then hit enter there and we get back five equivalently I could do select count number from phone book and I should get back the same result in fact when using this count function in SQL it's quite common to be a little more succinct and say select count of star because you don't really care which columns you're counting because by definition of how SQL tables work, you're always going to have the same number of cells in every row even if some of them might end up being blank. So it suffices to say select count star from phone book then a semicolon and enter and I'll get back indeed the same response. But suppose now I'd like to know what the distinct numbers are in this phone book because we've seen before that some of the staff actually share a main number. To do that I could do initially select number from phone book but that again is going to return all of the rows irrespective of duplicates. And so I can apply one of these functions now and say select distinct number from phone book. And as you might guess, I should only now see two, the two unique numbers that are across that phone book. Now, it's obviously easy for my human eyes just to count two unique numbers. But suppose this were a larger data set and you were interested in how many distinct values there are, but not necessarily what those distinct values are. Well, you could combine these same ideas. And I could say select uh count of distinct number from phone book semicolon enter. and I get back my answer of two. So already I seem to have more declarative abilities with this particular language where I can just select exactly what I want. Now it's not quite as user friendly as using natural language in English. That is your own natural human words. But there's a relatively simple set and finite set certainly of keywords that you can use in SQL. These then are just some of those. Well, what more can we do with SQL? Well, it turns out we can be even more expressive with keywords like these. I can group related data together. I can search for something like a value but not necessarily exactly some value. I can limit the results to just the top 10, top 100 or some subset of the total number of rows. I can order the data that is sorted by a particular column in ascending or descending order. And then I can have a a predicate of sorts whereby I can say only give me the rows where the following boolean expression is true. So within SQL we have some of the same ideas that we saw in a language like Python and then JavaScript, but you'll find that by reading it left to right, it's more akin to just asking the computer system for what you want. So for instance, let's go back here to VS Code and let's select the number for John specifically. Now, if I were to do this in Python or even JavaScript, I ined would probably open the file, read up from top to bottom, left to right, constantly checking with a conditional and a boolean expression. Is the current name John? Is the current name John? Is the current name John? And that's fine. That would be a very procedural approach to the problem. But in SQL, I can declare my intent as follows. I can select the number from the phone book where the corresponding name equals quote unquote John using single quotes here in SQL by convention hitting enter and I get back just the number I care about. Now, conversely, if I want to see all of the names of staff who have the same number in mind, well, I can do select name from phone book where the number in question equals quote unquote +1617495 1000 close quote semicolon and enter. And now I get back Brian, David, Doug, and Eric who all share that specific number. Now, of course, I can just eyeball this and see that four of us have the same number. But if I only care about the count, I could do this. And now to save some keystrokes, I'm going to do a technique much like my general command prompt whereby I can scroll back in time with the up arrow and move down back and forth to go through my history. If I hit up just once, I get my whole command again. I can go ahead now and scroll here and say, you know what, don't just show me the names, show me the count of the names. So, I'm going to insert that function call that we saw before of count. Hit enter. And now I get back quite simply the answer. But what if I want to see in this phone book the number of times that each number appears in total? So ideally four and one respectively. How can I do that? Well, you can express that notion in SQL as well. Let me go ahead and select first a number but also the count thereof using familiar syntax from that phone book. But this time group those numbers together as follows. By saying group by number, you're telling SQL to essentially look through that column of numbers, group all of the like numbers together so that you can perform this aggregate function of count on those grouped by numbers. So if I hit enter now, we'll see that ah that first number which is common across several of us indeed is shared by four names. But the last of those numbers who's just John's only has a count therefore of one. So again using syntax like this we can pretty readily express the questions that we might want to ask about this data set or any other. Well what more can we do with SQL? Well it turns out if we want to not only select data that is read we can also delete it. For instance if I decide that uh you know what I don't want to be in this phone book anymore I can delete my own self. Let me go back to VS Code here. I'll clear my prompt and begin now with a new command. Delete from the phone book where how do I filter myself out? Well, let's see. I don't want to just delete from the phone book entirely because if in fact I were to be a little sloppy and dare say hit a semicolon here, don't hit enter, that would delete everything from the phone book. Therefore, I want to use a more narrowly defined predicate, a wear clause that specifies that I only want to delete rows where some boolean expression is true. And I think if I'm seeking to delete myself, I really want to say where name equals quote unquote David and then hit enter. Nothing seems to have happened. But as we begin, let's do select star from phone book and see what's now in the database. And indeed, I am now gone. We see now Brian, Doug, and Eric with those same numbers, but John is there as well, but no David. All right. Well, suppose I quickly regret that. How can we put myself back? Well, it turns out in SQL, you can not only select and delete, but also insert data therein. So, here is the canonical format for how you might insert a new row or even rows into a table. You literally say insert into and then the name of the table. Then in parenthesis you specify one or more columns into which you want to insert new data. Thereafter you say literally values and then in another set of parenthesis you literally specify one or more values that corresponds to that number of columns. That will have the effect then of inserting into a new row precisely the values you indicate. So let's see how this might work. Let me go back to VS Code now and let me insert into that phone book the name of David as before. Of course, you might realize now I'm forgetting to insert one other value, but that's fine. It's up to me to insert if I so choose just a subset of data. That seems to be successful. Let me scroll back in time two commands and re-execute select star from phone book to now see the result. And indeed, we'll now see that David is back, albeit in a different order, but that's fine because I never seem to care about the order previously. But my phone number is null. Null then is the special sentinel value which is distinct from the empty string. That is the the uh presence of a value that just happens to be blank. Null means we never even put anything there. And it's a so-called sentinel value that's useful because it indicates the deliberate or even accidental but glaring omission of data. It's not just that the user left something blank. It's that your command, your SQL statement didn't insert anything at all. So how can we resolve this? Well, thankfully there's the U in CRUD for update via which we can not only select and delete and insert but also update our table as follows. The syntax here is a little bit different, but it's to say update and then the name of the table, then set followed by a whole bunch of key value pairs as in the past. The name of the column that you want to update and the value that you want to give it and if you so choose a commaepparated list of additional columns and values, but in this case, I'll do just one. But I want to specify where this update should apply. If I were to omit the wear clause and just end my thought with a semicolon, this would update the entire table, setting every value in that column to the same value. So, how might I do this more carefully? Well, let's go ahead and do update phone book set the number to quote unquote + 1 617495 1,000 where and only where the name is quote unquote David. If I had omitted that wear clause here, what would happen? Well, I don't think it would really affect Brian, Doug, or Eric, but it would affect John in so far as his number was different, but would no longer be if I didn't specify that this update should only apply where the name is indeed David. Now, nothing seems to have happened, but indeed, that tends to be good. Let me scroll back in time and select star. And now we see that I'm back in the phone book. Now if you don't like this ordering for some reason or the whole point of this database is to show an alphabet about alphabetical list of your contacts well that too we can address as before. Let me go ahead and do select star from phone book. But let's order this by the name column. Now you'll see that everyone's name is in alphabetical order as before and the numbers still correspond appropriately. If though you wanted to do this not in the default order which happens to be ascending as for short but descending deesc for short you can do the same. And now the order of the names is entirely flipped and you can apply that ordering to any column in some table. All right, let's transition now from our own tiny phone book to an actual real world database that comes to us from IMDb, the Internet Movie Database, which has a whole lot of information on actors and TV shows, movies, and more. Within this database, you can imagine the need to store data in a fairly careful fashion because if you have thousands, maybe even millions of rows, you're going to want to store things as efficiently as possible. So how might you go about storing for instance the names of some TV stars in a database like this? Well, we could for instance for a TV show like the office put the name of the show in the very first column and then thereafter in each subsequent column we could put the name of a star in the show. So we have star star star and star at least for those who receive top billing at the start of this particular series. And then for any other show, we could add another row and another row and another row and then each of their main stars as well. But no matter what show might come to mind, you can imagine the number of stars in it not necessarily being five. So some of those subsequent rows might have fewer columns or more columns simply depending on the number of TV stars therein. And that should rub you the wrong way because if nothing else visually, the data is going to become very ragged whereby none of the edges so to speak will line up. And that in and of itself isn't so much a problem, but it does suggest a sort of sloppiness whereby your data is not all the same. So this tends not to be the best solution. And indeed, in general in relational databases, if you're in the habit of adding more and more columns, you're probably doing something wrong. Unless the schema fundamentally for your data has very deliberately changed, it's much more the common case to add more and more rows to a data set in a relational table. And so we should aspire to do something more like that. Now we could for instance restructure things as follows into a two column table thereby assuaging my concern. But now we have sort of a new problem that we've seen before in other places whereby I've got lots of duplication. The office the office the office the office the office which if nothing else is just wasting space now by storing literally the same name of a show multiple times. And as in the past, it might invite mistakes at some point because if I change the capitalization or spelling of some show's name, I might not remember to make that change in all five places. So, this is just asking for trouble. So, how might we do this a little bit better? Well, let's suppose that we do it as a relational table and introduce as before some of those unique identifiers. Let me propose for instance that we have one table here for the shows themselves. In this case, just one, but you can imagine having more and more rows for more and more shows. In that table, we'll have a title column for the title of the show, but we'll also start to have an ID as well. And rather than start at one, I've consciously chosen 386676, which happens to be the unique identifier in the actual internet movie database for this version of the office. Meanwhile, we'll call this new table shows, which makes perfect sense. Meanwhile, let's have another table for instance over here that has the names of all of those TV stars. But let's associate with each of them a unique identifier as well. And again, rather than start at 1 2 3 4 and so forth, I'm using the actual identifiers from IMDb. For instance, Steve Carell's is 136 797. Arbitrary, but consistently the case throughout their entire data set. Now, unfortunately, at the moment, at a glance, there's nothing linking this data together, nothing relating one table to another. But even here, I think we can solve this not necessarily by adding a new column to these shows or now these people, but in some cases, you can even use a third table that somehow relates one to the other. And in fact, I'll propose here in the middle that we could have a third and final table maybe called stars that creates that relation across these two tables. The first column of which I'll say is show id which corresponds to the ID column in the shows table. The second column of which is person ID which corresponds to the ID column in the people table. Now this is just one convention and different people different companies might have different ways. But it's not uncommon to name your tables in the plural form of these words to then name the unique identifiers in most of the tables quite simply ID in all lowercase and then in a intermediate table like this whose purpose in life is to relate one table to another to use the singular form of the table followed by an underscore followed by ID but again capitalization spelling conventions might differ but this is just one way that I'll use uniformly as we explore this particular data set. Now, unfortunately here too, even though you might now see that oh the stars table links this particular show 386676 with this particular person 136797. It's a lot more work certainly for me as a human with my human eyes to now link these pieces of data together. I can see of course that this ID here corresponds to this show here and this person ID here corresponds to this person here but it was a lot more convenient before when it was just all right together in the same table. So on the one hand we've sort of addressed some of the problems we identified of redundancy perhaps ambiguity and the like but we've introduced new problems and that the data is like all over the place but this too is a problem that SQL can solve for us. Now just to be clear, let's consider exactly what data is in common across these three tables. So highlighted here is the same show ID in the shows table as well as in stars indicating that all of the corresponding person IDs are somehow associated with that show. Meanwhile, between the stars table and people table, do we have the same IDs pair-wise in each of the same indicating that each of those people by way of the stars table is indeed associated with that show. Meanwhile, there could of course be more stars and more shows. So, we'll indicate as much with dot dot dot. But let's take a step back now and look at the entirety of our version of the IMDb database, which we indeed imported for you and for class into these relational tables. Here then is a diagram that represents the data we're about to explore. You'll see some familiar titles here now like shows and people as well as stars. But it turns out there's more tables in this database like writers and ratings and genres. Let's focus for now though on just two of these and consider how we might implement the simplest, if you will, of relations across just two of these tables before we then allow things to escalate into that relationship across three total tables. So pictured here as shows is the table that has we'll soon see an ID, a title, a year, and some episodes. So this isn't quite as simple as our imagined version of IMDb which just had the stars and the names of the shows. But here we have associated with each show a unique ID, the title thereof and the year in which it debuted and the total number of episodes. Meanwhile, over here in another table are the corresponding ratings for that show, which will map back to the shows table by way of a show ID, a rating on a scale of on a numeric scale, as well as the total number of human votes that contributed to that rating. Now, strictly speaking, we could have put all of these ratings inside of the shows table by just adding, for instance, a rating column and a votes column. But in this particular case, the two pieces of data came from two different sources. So it's not unreasonable to claim that this table will relate to this one specifically in a onetoone relationship. That is every row on the left will correspond to one row on the right. And that's something we can ultimately enforce at the database level. So what do we mean by this? Well, consider that the shows tables ID column, the unique identifier for each show, will naturally line up with the show ID column in the ratings table. Now, let's go back to VS Code here and clear my SQLite prompt and quit out of SQLite 3 al together. Now, instead of opening up phonebook db, which was the database we ourselves created using phonebook.csv CSV as an import. Let's go ahead and open up a different database that I brought with us based on IMDb's own data. And to do that, I'm going to run SQLite 3 as before, but this time specify shows db, which is a much larger database that's been made in advance. That brings me back to a SQLite prompt. I'll go ahead and clear the same and now run. Select star from shows. But I don't think I want to see all of the shows at once because there's indeed going to be thousands. So, let's go ahead and limit the result to just the the top 10, the first 10. If I go ahead and hit enter, we'll see here the first 10 shows in the actual internet movie database, including its ID, its title, the year in which it debuted, and the total number of episodes. Meanwhile, let's wrap our mind around the other table called ratings as follows. Select star from ratings, limit 10. And I dare say whenever opening a SQL database for the very first time, just selecting star from one or more tables is a useful if quick and dirty way to wrap your mind around the actual data therein. And just limiting the results again just ensures that your screen is not going to overflow with more data than you care about. It's just going to give me a sense of the format of the data. And indeed here we see three columns in this table. Show ID, rating, and votes. So how can I now maybe see just how large each of these tables is? Well, I could certainly run the entire command and just wait and wait and wait for all the rows to come out. But let me instead clear my screen here and then do select count of star from shows and we'll see in this particular database we have some 245,200 shows. Meanwhile, there's probably nearly as many ratings assuming that all of those shows have been rated. How though can I see how the actual tables themselves have been implemented? Well, for that, recall we can run dots schema. And dots schema is going to show me exactly how each of these tables has been defined. And let me scroll back to the top of this command, and we'll see top to bottom these tables here. We have genres and people, ratings and shows, and down here, stars as well. Let's focus on the few that we've talked about thus far. So, at some point or other, I or someone else ran the command create table shows. Then in parenthesis we specified a comma-epparated list of all of the columns that we want to create and the corresponding types thereof. We'll see now that ID is specifically integer and that lends itself to being a number like 1 2 3 or much larger. We'll see that title is text but moreover it's not null. In fact, this is another feature of a proper database where like whereby unlike in a CSV where you can put anything or nothing if you want in a database you can specify that users should not be able to insert or update values that contain null so that you ensure that every show in this table is in fact going to have a title. Thereafter we see that year is numeric which seems to be another type altogether and then episodes itself makes sense is an integer. Moreover, we then see something mentioning primary key, but we'll come back to that in just a bit. Meanwhile, for people here, not surprisingly, we have ID, that's an integer. Name that is text and not null. Birth, which is presumably their birth year, which is numeric. And then again, some mention of primary key of ID. But lastly, let's focus here now on ratings, which relate somehow to these shows. We have here a show ID, which is an integer that's not unique, that's not null, but is unique. And this indeed ensures that we'll have a onetoone relationship. Ultimately the rating is a real number which means a floating point value something typically with a decimal point that similarly cannot be null. The number of votes is an integer that that too is not null. But notice here some mention of foreign key referencing show I a foreign key called show id referencing the shows tables ID. And we'll come back to to all of these keys in just a bit. So now that we have that in front of us, let's take a quick look at the actual data types available to us. We saw in Python that we had things like integers as well as strings or stirs for short. In SQLite specifically, you have access to these primary types. Now in other databases like Oracle and SQL Server, MySQL and Postgress, you have an even longer list of available types that allow you to impose further constraints and formatting on your data. SQL light though in so far as it's a bit lightweight. Uh allows us these five choices here. Now integer speaks for itself. Text is just text. Real is a floatingoint value. Numeric is something that is like a number typically a year, a date or time or something else that generally falls into some standardized numeric format. And blob or binary large object really just means a binary value of some sort. Maybe even a file in the database. Though files generally belong in the file system stored in a folder somewhere. So that's why we've seen not just text but integer and other types in this file. We can see that there are other constraints we've imposed like keywords like not null which ensures that the database won't even let you omit some value and unique which ensures that the database won't let you insert duplicate values. But let's now revisit these keywords that we saw in that schema for IMDb's database. Namely primary key and foreign key. We've been using the first of these and really the second without calling them by name thus far. A primary key is a column in a table that uniquely identifies each of its rows. Typically with some simple integral value like 1 2 3 or something much larger than that but a unique value that uniquely identifies every row in that table. A foreign key is the presence of a primary key in another table. And it's these foreign keys that enable us to create these relations across tables. For instance, as before, when we had our shows table which had an ID and then we had a stars table which related to that show id in the first of those it was a primary key and the second of those it was a so-called foreign key. Similarly in the people table which had a ID uniquely identifying each person. In the stars table, we had person ID, which in that context was a foreign key referencing the people table's primary key. So the use of these terms is just context dependent, but they refer ultimately to these unique identifiers that not only uniquely identify data, but allow us to cross reference it in other places. And so in the schema here in VS code, we see the ability in the database it spell in in the database itself to specify that for instance within the ratings table in so far as there is a show id column that show id is in fact a foreign key that references this other table's primary key. And we can see this elsewhere as well. If I scroll down to the bottom of the schema and reveal the create syntax for the stars table, we'll see that the stars table, as we saw pictorially, has a show id column and a person ID column, both of which are naturally integers and not null. But there's now two foreign keys in this table. Namely, show ID references the shows tables primary key ID. And the person ID column references the people tables ID primary key as well. So verbose and not necessarily syntax that you might want to memorize. And indeed there's software out there in the world that allows you to click some buttons and generate commands like these which are not necessarily easy to remember. But ultimately reading these things line by line does specify and make clear to you exactly what our columns are and what each of the types therein is. But there's another command in SQL that's even more powerful perhaps and allows us to somehow link back to the original material we care about. That is to say, thus far in trying to impose these relationships across tables, we've created all of these new identifiers, namely these primary and corresponding foreign keys. And to the human eye, now it's a lot more work to try to line up all of those values and figure out what data relates to what. But in SQL, there's indeed an actual keyword called join that allows you to specify how you might take one table here, one table here, and join them together so that you actually have full-fledged access to all of the data you care about as opposed to it