CS50 VR 2016 - Week 9 - SQL

Key Takeaways

[Music] All right. This is CS50 and this is week nine. You'll recall that last time we took a look back at such things as Scratch because recall that when we introduced Scratch, we introduced a whole number of programming constructs, loops and conditions and functions and variables and more. And then just one week later did we transition from that world of Scratch to C where the syntax was much more cryptic looking certainly at first glance and perhaps a little bit still but the ideas were ultimately the same. And now in week nine, as we transition from the world to C to Python, you'll find that that same that same finding is the case whereby we are using the same ideas. We're leveraging the same concepts, but we have to translate it now to a slightly different domain and a slightly different syntax. But things really start to get interesting this week and beyond, especially as we build on our ability to write Python code, our ability to serve up web applications because now we can begin to leverage so much more functionality functionality than comes with C, then comes with the CS50 library because there's such a large community of software developers who have created some really amazing things that we can try out. So let's look further today at Python and let's look further at the applications for a language like this. You'll recall that perhaps the simplest program we could have written last time was actually just one line, print hello world. But recall as you began to dive into some of this past week's challenges, you might have needed or wanted to actually start wrapping code like that in a method or a function like main and then calling it by default with this magical incantation here. But this was the building block toward which we were starting to develop more interesting programs. But now we're going to really context switch from a command line environment over to a web-based environment. And the world has been writing web-based applications for quite some time. Even I 20 years ago recall made that froch I im freshman interural sports website. And even since then have the languages changed have the paradigms changed and we humans have learned a lot about programming for web- based applications. And this for instance is one uh design uh pattern or one architecture that has arisen whereby MVC refers again to this pattern whereby you put your your intelligence your so-called business logic in your controllers. This is one or more files that has all the conditions a lot of the function calls and then actually does something with your program. Then you have the views which are often templates or files that render information that you might have dynamically generated or input from users. and then model. And today we start to dwell on the M in NVC model because last week we didn't really leverage much if any of a model. But this week we're finally going to demand of ourselves the ability to save data, retrieve data, search data, delete data, and more. And we really haven't had this capability other than very simple CSV files for instance back in the day of C and even last week when we dabbled with those in Python. And you'll recall last time we introduced this. Flask is what's called a micro framework. So, a bunch of files, a bunch of code that's a community of people have written that just make it easier to make web- based applications. It's absolutely not required. And in fact, one of the sample uh programs among last week's distribution code, if you'd like to go back and play, was a program called serve.py that doesn't use any of this. It just used built uses built-in Python functionality. But you'll find that it's pretty cryptic. It's pretty uh heavyweight in order just to do something simple. And so things like Flask have come around that just make it easy and they're say more pleasurable to write web- based applications because people have realized in writing web app after web app after web app that they're just repeating themselves or borrowing code they've written in the past. And so in frameworks you have solutions typically to very common problems. So we'll use this set of solutions to the development of a web-based application. And for instance, the simplest Flask application that's also available from last week's source code might be something like this whereby at the top of your file, you don't import like the CS50 library. You instead import someone else's library specifically from the Flask framework import what's called a class. Recall uh called flask, capital F, and then also a function or a method called render template. And we used both of those as follows. Last week, we instantiated a Flaskbased application by essentially passing in this special reference to the current file, the name of the current file, and then allowing the framework to do its thing and give us back this very special, very powerful object called app, though we could have called it anything that gives us access to some useful functionality. For instance, the most useful functionality initially was just this at app. And this is just syntax for what's called a decorator. And more on that some other time or more on that in Flask's own documentation. But essentially this line here at app route says that hey server anytime you see an HTTP request for slash the default web page uh typically of a website go ahead and call the following function that's immediately below it. That function I've called index mostly by convention but I could have called it anything I want. And all it did last week in this example was render a template. In this case index.html. Now, that could be raw HTML, recall, but oftent times you use something called a templating language. And indeed, we introduced a little bit of Ginga last time, which is a Python uh based uh templating language that we'll see just makes it easier to generate HTML without having to write HTML inside of our actual Python code, which tends to be frowned upon. So, let's take a look back at one of those examples, and which I've renamed from frost im. And recall that we had the following files. We had a templates directory inside of which was failure, index, layout, and success. Kind of a lot of complexity for a pretty simple program, but we'll see what each of those does again. And then application. And this was the so-called controller code that actually did something interesting last week. Now, what was that? Well, it's a pretty small program. As before, I've imported from the Flask framework a whole bunch of symbols here. I'm instantiating my application here. This is copy paste from our simplest of examples whereby if the user just visits slash I want to show him or her index.html. But then it got interesting. This really was the first time we had the capability in a web-based environment to respond dynamically to users inputs based on whatever they typed into a web form. In fact, if you think back a few weeks in week six when we first introduced the web and HTTP and TCP IP and making uh HTML-based web pages, you'll recall that all we did was implement Google's front end and an ugly one at that, but just the HTML form that if you click the submit button submits to slash search on google.com because we pretty much deferred completely to them lacking at the time a backend and lacking at the time even a language in which we could implement our own backend, a web server that actually responds to those requests. But in frost I im zero, we have the ability to have our own route in this case called /register that I've specified isn't even going to respond to HTTP get requests, but rather post requests, which typically mean a form submission is sending one or more fields that you don't necessarily want to end up in the browser's URL or history. I'm calling my function register, and this would be a good convention. Just make sure your function or uh here lines up with what the route is there. And then I'm checking a couple of conditions. If that requests form, that is all of the parameters that were submitted via HTTP post has a name field. So, a text field for instance called name and that equals nothing quote unquote the so-called empty string or that requests form's dorm key has a value of quote unquote which is to say that if the user myself last week did not give my name or my dorm then go ahead and return the template called failure.html and we'll take a look back at that in a moment otherwise render template success.html if all in fact goes well now if we take a look at failure.html html. It didn't do all that much. It extended layout.html. It declared a title of failure and then it declared a body of you must provide your name in dorm is sort of an admonishment to the user. If I look at success, meanwhile, it's pretty similar, but the text is different. But this is the problem we addressed this week. You are registered. Well, not really because recall, and you could have seen a moment ago, we did nothing with the user's name or dorm or any other information. We just pretended to actually register them. And then what did the form itself look like? Well, this page here is mostly HTML. But again, notice that even this page at the top defines a body block and a title block because it's extending layout.html. So this is the ginger stuff that I referred to earlier, the templating language. And if we finally go into layout.html, now you see the basic framework for every page in this web-based application. It's a pretty small application to be fair, but it does have at least three distinct pages. index, failure, success, all of which are identical to this file except that they each have a title and a custom body dynamically embedded thanks to how we're using templates. So, this is a nice way of not having to copy and paste all of that code into every file, thereby making it a pain to update anything to add CSS or JavaScript files or generally any of the overall structure of the page. We can factor that all out to layout.html. But of course, we just threw all this information away. That name, that dorm, every student who's been registering via this app, we're forgetting about. And so, at the tail end of last week, we introduced this solution. Recall whereby in Frocham's one, now I'll call it, or rather today, let's redress that by borrowing an idea from last week that we didn't incorporate into fros as follows. In application.py pi for this next version called frost imams one. What do I seem to be doing differently? The file is almost identical to before. It's a little longer this route because what I've grabbed is some of the code from last time whereby we wrote out to a CSV file, separated value, uh those student structures. We did this way back when in C. We then ported the student struck to a student class last week. And now I've borrowed that same code for saving those students to disk so to speak by embedding it into this route. So what's going on? So if again the name or the dorm are blank, I go ahead and just return the template called failure.html and we bail out. Otherwise, I declare a variable called file. I open all's fop function registrance.csv, which I'm just calling this uh just because it's going to contain my registrance in a commaepparated values file. and then quote unquote A. And you might not recall this or might not have seen this, but quote unquote A is for appending. If I instead and accidentally did W for writing, you might think that's correct. But every time you use quote unquote W, you're going to write out a new file. Which is to say, if a hundred students registered for some intramural sport via this web app and I was using quote unquote W, I would actually keep clobbering or overwriting the file such that only the most recently registered student would appear in this file. So, we want quote unquote a for append. Then I declared a variable called writer. And I'm using this CSV uh module that we get for free from Python. That's going to allow me to create what we'll call a writer passing in that file as input. Then I'm going to call this special method write row whose purpose in life is to take a tpple, which in this case has two elements, the name that was passed in via the form as well as the dorm. And then I'm going to go ahead and close the file. So write row is now the function that takes care of all the complexity. It's not all that much complexity of printing out name, dorm, name, dorm. And just in case anything has a quote in it, uh like a someone oiri or a name that has uh an apostrophe or some other punctuation symbol that might otherwise confuse a text uh might confuse a program reading this text file which itself might contain quotes. This kind of function write row will take care of those kind of details as well as commas that might correctly or incorrectly be in the values like name and dorm that I'm providing and then we print out the success page. But this isn't all that useful of a file format. A CSV file is nice and that you can download it. You can open it in Excel or Apple Numbers or similar programs. You can import it into Google spreadsheets, but it's not really a database. You can store data in it, but if you want to read any of that data or change any of that data, you pretty much have to do what we've been doing in C, which is open it with open or fop or whatever, iterate over the lines, maybe parse them or read them into separate variables or into an array or a list or whatever. And then you can use binary search or linear search or whatever you want to actually find data, maybe to then change data and then save it all back out. But this is just tedious to have to do all that work simply to save data isn't all that much fun for programming. And it also doesn't scale very well. As soon as you have some good success with some web- based application or even some mobile application, it'd be nice if your code were as efficient and as fast as possible. And wouldn't it be nice if we could stand on the shoulders of others who have had similar problems of storing data efficiently so that we could learn from them as well and leverage some of their work. So thus was born SQL structured query language which can be used in any number of environments. So SQL is just a language that we will now introduce and it's a programming language though it's not going to be as uh we're not going to use it as richly as we have C or Python. We're going to use it for a number of fairly basic but nonetheless very powerful operations. And you can store data using this world of SQL in any number of ways. So you can use things like MySQL which is a very popular database server that Facebook started with and still uses for some of its purposes. Uh Postgress SQL uh Microsoft Access or Oracle or any number of thirdparty products that you might have heard of that are super popular for web- based and business applications. And then there's also a format that's even simpler but gives us all of the same capabilities called SQL light. Whereas MySQL and Postgress and Oracle and Microsoft Access and the like typically require that you run some special program, a server that's listening for requests and responding to requests and often has usernames and passwords. If you want something simpler because you're making a fairly small scale website that might only have hundreds or thousands or tens of thousands of users and you're okay with all of your data living on the same server that your code lives on, maybe in the same folder, you can use something called SQL light which allows us to use the language SQL that we're about to see. But it doesn't require the complexity of configuring a whole server or a whole uh set of tools. You can just store your data right there in a DB orSQL light or whatever file right in your same directory. But how are we going to store that data? Well, many of you are probably familiar with tools like this, Google spreadsheets or Excel or numbers which allow you to store data in generally rows and columns. And if you're looking at a spreadsheet like this and you're storing people's names and dorms and email addresses and phone numbers, generally we humans use the top row for headers. And we'll put quote unquote name, quote unquote dorm, quote unquote email or phone or whatever. And then every row below that first row represents in this case a student, a record in our spreadsheet. So if I were to do this in reality, let me go ahead and do just that. Name and dorm and maybe email and phone and any number of other fields. And I'm going to go ahead and really be tidy here. And you can do these silly aesthetics and something like Google spreadsheets and uh Excel and numbers. But here is where I would do something like David and Matthews and Rob in the dot dot dot. We can fill in the rest of these rows and we can just keep growing and growing and growing. And frankly, most of these programs are smart enough that even though I think Google spreadsheets now goes to a default of a thousand, when you export that file or save it, they're only going to save rows presumably that actually have data up until that point. And then you can have any number of columns. You get a through I think Z probably by default. So 26 columns even if you need fewer, but you can create more as well. So this is nice. This itself is structured data. You have metadata like name, dorm, email, phone, keys if you will, and we've used that expression before in various CS contexts and then you have values and those values line up with those keys. And in fact, now if you think back to some of the features we've seen already in Python, a dictionary is a very popular and very useful data structure in Python. A list or an array is another such one. And so really, if you think about what a spreadsheet is, it's kind of really just a list of rows. each of which is a dictionary. And what do I mean by that? Well, within every row, whether it's two or three or a thousand, you have columns. And those columns have keys or names to them. Name, dorm, email, phone. And recall that a dict or dictionary in Python is just a collection of key value pairs. So if this is just a list of values and each of those values is organized horizontally essentially as dictionaries key value pairs where row 3's name is Rob row 3's dorm is there and so forth well it seems that we could map this kind of format very seems that we could map this like Python or C for that matter but Python will be a lot more convenient and sometimes one sheet is not enough. So sometimes you might have gone down here to the bottom corner. You might have created something like sheet two. And maybe here instead of just storing students, you might store professors. So the professor's name and office and course and any number of other pieces of data that you want to store about him or her. And we could store professors. And I'll even rename this from sheet two to professors. And in sheet one, I'm going to go here and rename this to students. And so we can really kind of organize our data cleanly. But that's about it. You know, even though Google spreadsheets and Excel do have some programming functionality built in with macros or similar functions, it's not all that easy to query the data, it's not all that easy to integrate this kind of file into a program you're writing, and it's definitely not necessarily efficient because you've not really told Google spreadsheets anything about how much data you're going to be putting in here, what the type of that data is beyond maybe the formatting thereof. And so, there's some opportunities for better design. And thus came along SQL databases that give us not only we'll see the ability to store things in rows and columns but also the ability to create data or create structures in memory and insert data, select data and update data and delete data. In fact, a SQL database is really a specific instance of what's generally known as a relational database. is a database that allows you to maintain relations among pieces of data generally spanning sheets or as we're going to start calling them tables that its to a silly acronym CRUD the ability to create read update and delete data and those verbs map to such keywords or commands as these as we're about to see. So what does this actually mean? Well, let me go ahead and do this. Let me go ahead and within CS50 IDE, I'm going to open up a special web-based program that's simply going to give me the ability to create databases and create fields therein. I'm going to go ahead and create a database called lecture db. And I'm going to go ahead and open now a web-based tool via which I can administer this database called lecture. And you're going to see that I have a tab called structure, which is where I'm going to be able to define sheets or tables as we'll call them. I can execute raw SQL commands with which we'll now start to get familiar. And later on I can even export or import data as well. But let's focus on these fields down here. So this tool is one of any number of dozens of tools that might exist that allow you to create and administer databases using SQL. I'm going to go ahead here and call my table registrance. So this is like creating a new sheet in Google spreadsheets. And the number of fields for now I'm just going to go ahead and have two name and dorm. And now notice I get a little HTML form here whose purpose in life is to make things a little easier for me. So I'm going to type in name for one field, dorm for another. And you'll notice that SQL databases now allow me to choose any number of data types. So we're sort of going back and forth here in between the world of strongly typed languages, weakly typed or loosely typed languages. Here it can actually matter for performance. And I'm going to indeed tell my database what kind of data I'm generally trying to store in its columns and each of its rows. So my options with SQL light, which is one incarnation of the SQL language, is integer, which means what it says, real, which is like a real or floatingoint number as we've seen that generally can store up to like 15 or so digits of precision. But even then, if it can't, it's going to uh use a text field automatically instead because text is really just a string, otherwise known in some environments as varchchar or variable length characters. Blob is just going to be binary data. And this means we can store things that are just zeros and ones that aren't asy or unicode text. Numeric is a little more flexible. If I want to store a dollar amount with dollars and cents, if I want to store maybe an integer, maybe a real number, I can use numeric and just let the database SQL light in this case figure out what actual uh data type or affinity so to speak to use. Boolean's just going to be a zero or one, but that's really just a convenience. It's actually still going to use a full bite or eight bits just to store a zero or to store a one. Really just an integer. And then date time, which underneath the hood can use any number of formats, whether it's text or integers or reals. I'll defer to the documentation, but that's going to allow me to in a standard way store things like dates and times so that we can actually record when stuff is happening. Uh we when some when someone registered for freshman interal sports and even do useful arithmetic operations on dates and times. So what should name be given all that? There aren't really too many options and indeed my instincts are to go with text. So I'm going to do that for name and for dorm. And then I'm being prompted by this web form for a few different questions. So primary key. It turns out that among the features of a SQL database is the ability to specify that this field, this column shall be my primary key. The field whose values uniquely identify all of the rows in my database. Now it's not going to be relevant just yet for the following reason. Right now it's quite possible that two Davids live in Matthews or two Robs live in the so if I said that name or dorm were my primary key that would mean I can have only one David or one Rob or one Matthews or one the in my database so for now we're going to leave this alone that would seem to be a bad thing to do auto increment isn't going to be applicable because you can't auto increment a string or a text field that's going to be germanine to integers and not null which is my promise that this field cannot or shall not be null. So I will go ahead and do this and just promise that every student in frost am is going to have a name and a dorm. Meanwhile, you can specify a default value. I'm not going to bother doing that here, but you can see from the drop down that you can specify a certain value like null or you could specify the current date or time if that's actually germaine. But I don't want to give people generic names and I don't want to just assume that people live in some default dorm. So I'm going to leave that alone and expect that the user of this database table is always going to give me a name in a dorm. So, let me go ahead and click create. And what you'll see is this. PHP lightaded admin is really just a handy web-based tool for executing SQL statements. So, I don't necessarily have to remember all of the syntax up front. I can kind of learn from this actual tool. And what PHP Lightadmin has really done for me is this. It has in that file called lecture.b DB executed this statement. create table quote unquote registrance name is text not null dorm is text not null and that's it so PHP light admin is just saving me at least for today's purposes from having to remember exactly all that syntax but after practice it becomes pretty familiar and certainly with Google you can fill in any blanks but what does this now mean if I click on return here and go back to not just the lecture where I was but I click now on registrance my actual ual table, you'll see a bunch more tabs. The top one of which is browse that this table is empty because I've not inserted me or Rob or anyone else for that matter. But if I click on structure, you can now see in a web- based environment a little reminder as to what this table looks like underneath the hood. It's more esoteric looking than something like Google spreadsheets where you just have columns and rows. Here we're being more precise. I have two fields, name and dorm. Each is of type text. Either can be null, but neither has a default value and neither is a primary key. But via the edit link here and delete, I can actually change those definitions and I can even add more fields if I think of things later. And so this is allowing me to create really a table of information whereby these are my rows or my fe sorry whereby these are my columns or fields name and dorm. So let's go ahead and insert for instance me in here. So I'm going to go ahead and insert David who was in from Matthews and click insert and notice what PHP light admin did. It simply executed this SQL command insert into registrance name, dorm. So it's specifying what the name what the two fields are that I want to insert into and what values do I want to put David, Matthews, not to be confused with Dave Matthews. So now if I click return and go back to the browse tab, notice that we see David's in Matthews. Meanwhile, if I click insert here and I type something like Rob and the click insert, a very similar SQL statement was executed, but this one customized for Rob. And now return and see browse. Both of us are in there. But now, let me start to take those training wheels off. It's not all that interesting just to click around in a web gooey. Let me actually learn something from this. And no, let me go ahead and type my own SQL now. Insert into registrance. And if I really want to be proper, I can quote it. But so long as you don't have any special characters, quoting uh symbols is not strictly necessary in this case. Uh what fields do I want to insert into name and dorm? And what values do I want to insert in raw values, textual values that are not special keywords or field names? I do need to quote with single quotes here in SQLite. Uh let's go ahead and put uh Zila and courier for dorm or house. There I can put a semicolon. It's okay if I omit it, but if I want to execute multiple SQL statements at once, I will need the semicolon again. Let me go ahead and click go. And nicely enough, notice what happened. One row was affected. It took only 0.01 seconds. So, it's pretty darn fast. This is just a reminder of what it is I executed. And if I go back now to browse, I can actually see three rows in the table. All right. So, now things are getting a little more interesting. What if I want to search for things? I'm not even going to use this training wheel here. I could use the search tab and type in some names or some dorms that I want to search on, but that's not the goal here. The goal here is not to teach PHP light admin or this web-based tool. It's just to use pretty quickly this kind of web-based environment to create the schema for our database, the design of the database, our choices of tables and columns and perhaps some of the initial data and then use that database in actual code. So, that's where we're going. We're going to move away from this web-based environment and use actual Python code. But first, let's see some of those other instructions. Turns out per this little summary here that we have the ability to create, which we already did using create table, to insert, which I just did. But what about select, update, and delete? Let's not use the web gooey per se. Let's just go ahead and start typing some raw SQL. I'm going to go ahead and do exactly this. Select, let's say, star from registrance. And now, as an aside, stylistically, it's not strictly necessary to write select or from or all of the SQL keywords in all caps. Tends to make code, I think, a little more readable, but I could still just say select star from registrance. But I find it nice to distinguish what are built-in keywords from like what are my field names and table names. So, select star. Star meaning everything. Let's click go. And now what I see here is this web-based representation of two columns with three rows that have come back. We're going to start calling this a result set. A result set containing three rows have come back and that's going to map to code pretty nicely. Now let's go ahead and do this. Suppose that uh Zamaya moves to a different dormatory and let's go ahead and type this update registrance set dorm equal to let's say graze where name equals quote unquote Zamila. And this is what's particularly nice about SQL. Even though the syntax is a bit new and some of the keywords are certainly new, like you kind of just say what you mean. So update the registrance table setting the dorm field or column equal to grays. Not for everyone only where the name field or column equals Zyla. So let me go ahead and click go here. All right. One rows affected up here. And notice a reminder of what I just executed. Now if I click on browse just to see what the data looks like. Indeed, Zomaya has relocated to a different dorm or building altogether. If I screwed up though, notice what could quickly happen. Suppose I did this uh update registrance set dorm equal to grays and I left off the so-called predicate, the wear clause that I did a moment ago and click go. Now, three rows were affected as indicated here. And if I click browse now, notice that oops, all three of us now live in grays. So I could fix this manually if I really wanted to without executing another SQL query, but this is just meant to be a userfriendly way of quickly and dirtily uh editing or creating some data. Really, we're going to start using those SQL commands only. What about delete? Suppose that Rob has graduated and he's moving out. Let me go ahead then and do delete from registrance where name equals Rob. Go ahead and click go. One row affected. And if I go and click browse now, only Zamila and I remain. All right, so those are just a few such keywords, but what's nice is that again it's pretty expressive. So to recap here, how did I create the table? I literally said create table registrance, though I didn't type this at the time, but this was just the SQL that that web tool generated for me. Um, spoiler alert, don't look at that just yet. We're going to come back to that in a moment. And I specified that the name field is going to be of type text and dorm is going to be of type text as well. down here. I then inserted something like David from Matthews specifying name and dorm. And notice these lines wrapped onto two lines. But in general, this could just be a really long query as well. Select star from registrance, update registrance, delete from registrance where, again, another little spoiler. And it's a spoiler because there's actually a flaw in my database tables design. My registrance table was not so well thought through. I indeed only care about storing names and dorms, at least right now. Maybe later, emails and phone numbers. But what could go wrong, right? When I deleted Rob, something could have gone wrong a moment ago. When I updated Zamila, uh, something could have gone wrong because what if there are two Zamas at Harvard? What if there are two Robs at Harvard? Well, each of those queries didn't seem to specify which Rob or which Zomaya. It just said where the name is Rob or the name is Zamila. So, how do we distinguish the two? Well, maybe I should have forced undergrads or students to register with their full name, Zamila Chan. Okay, so that would decrease the probability that we're going to have multiple Zamaya Chans at Harvard, but still there could be that probability. Same with Rob Bowden or really any name, whether common or uncommon. So that doesn't feel very robust. We could look for Zamla that specifically lived in Courier, but maybe by in the off chance there's two of them there. That's going to create problems and we might update or delete more rows than we intend. So this is why we humans in the US for instance have unique identifiers for better or for worse things like social security numbers or here at Harvard we have Harvard University IDs HUI ID numbers or at Yale net IDs or wherever you are in the world odds are you have in your wallet or at home or somewhere in your life a unique identifier and you might not even know what those identifiers are. In fact, anytime you've registered for an account on some website like Facebook or Google or any number of others, underneath the hood, even if those companies aren't telling you what your unique ID is, they know you by more than just your name and dorm or email address or phone number, odds are they have assigned you some unique but very simple piece of data like an integer. So that Zamila might be uh user number uh three, Rob might be two, David might be one simply because that's the order in reverse in which I inserted them into the table. So let me go ahead actually and and fix this. Let me go back into my database. And you know what? I'm going to be pretty bold here. I'm going to go ahead and in my table drop the whole thing. So beware executing the drop command because it will literally drop all the data in your table. But I'm going to confirm that and I'm going to start over as follows. I'm going to create a new table called registrance. This time with three fields. And in addition this time to name which will be text and dorm which will be text. This time I'm going to think ahead and you know what? I'm going to give everyone a unique ID. Convention is to call it literally ID and all lowercase. And indeed field names should generally not have spaces, not have funky punctuation. Whether you use so-called camel case with alternating capital letters or snake case with underscores, it's generally up to you. But using underscores tends to be the convention here, although it's not yet Germaine. But I'm going to make this ID field an integer. And I'm also going to make it my primary key. So that there shall be no two registrants in this frost's program that have the same primary key, the same number uniquely identifying them. And you know what? Better yet, and this is one of the features you get for free with many relational databases, auto increment. I, the programmer, I don't care what numbers Amila is. I don't care what number David or Rob is. I just want them to have a number, whatever it is, but I don't want them to be the same. So, let me just let my database auto increment this ID. Give the first user one, the second user two, the third user three, and so forth. And by nature, this cannot be null. They need to be uniquely identified. And default value here doesn't matter because the database is going to take care of that. Let me go ahead now and click create. And now you'll see the create table that I just spoiled a moment ago on the slide. This now creates a table again called registrance because I dropped or deleted the old one. But notice how with how much detail we have specified what ID is. It is not only an integer. It is also a primary key that supports auto increment and it cannot be null. Meanwhile, name dorm. I don't particularly care this time. Though I could specify like before, I don't want those to be null either. So again, that's just going to be a design decision, but let me see how this affects things. Now, let me go into my SQL tab where I can just execute some raw SQL. And soon we're going to be executing SQL from within Python code. Let me go ahead and insert uh into registrance, a name, and a dorm. And notice I don't care about specifying the ID. That's not interesting to me. All I care about is the values like David and Matthews. And if I now click go, notice that's all that the database executes. It doesn't go in and secretly change my query, but it does notice that I've omitted explicit mention of that ID field. And if I click browse now, notice David from Matthews has an ID of one. Let's go ahead and do two other inserts again via raw SQL. Let me go ahead here and do uh insert into registrance. And you'll notice there's a nice little history in this web tool of all the previous queries I've executed. So in case you want to click and save yourself some typing, you can do that. But for now, I'm just going to go ahead and insert Rob as before from there. And for good measure at the same time, registrance name dorm uh values Zila from courier. And notice the semicolon. So that hopefully both of these should go through as separate queries. Indeed, they did. One row affected, one row affected. And if I click browse now, Rob is number two, Zila is number three. And this is just going to continue on until we maybe run out of integers, but uh PHP uh but uh SQL light will support as many as uh eight byte integers or 64-bit values. So those are uh that's not going to happen for quite some time. So now notice what I'm protected against. There might be a whole bunch of robs at Harvard is fair to say. But now if I do a delete from delete from registrance where name equals Rob and ID equals two and I don't have to quote the two because it is indeed a number. This is actually more information than I need indeed because ID is a primary key. I don't need to mention Rob's name if I already know his ID. I can just say delete from registrance where ID equals two. Click go. one row affected and goodbye to Rob. And what's nice now about PH about SQL light is suppose I do one other insert. Let me go ahead and insert into uh registrance uh name and a dorm uh like with values of Jason and Kirkland and click go. Is Jason going to now be the new number two or is he going to be number four? Well, let's take a look. If we click browse here, he's indeed four. So, one of the features you get by using the auto increment field is that the database is going to ensure that just in case you have remnants of Rob somewhere else or really his ID number, maybe in some other table or in your logs or some piece of data you care about, we're going to make sure that Jason has not only a unique ID now, but a unique ID in perpetuity. If you don't use unique ID or rather auto increment, uh SQL light will actually try to reuse numbers just for efficiency which can actually break things down the road later. All right, so things are getting interesting. We have the ability now to insert data, delete data, update data, and then select it. What more can we actually do? Well, it turns out that SQL tends to come with not only various data types, but also various functions. It turns out to save ourselves trouble there are functions like date functions and times and date f times. So for instance I want to execute a query like select star from registrance but no I don't want to select all the registrants. Maybe I want to select all of the registrants this year. I could say something like select star from registrance where date of a particular field equals or is greater than or equal to January 1st 2016 or 2017 or whatever the year happens to be. So these are functions that can do that kind of thinking for me. But SQL also supports a number of even more compelling features. We saw a moment ago primary key which again specifies that this one and only field shall uniquely identify all of the rows in my table. I I I guarantee it. There's other types of indexes you can use. So this is the special a special type of index. And essentially what you're getting is underneath the hood the database whether it's SQLite or MySQL or Postgress or Oracle or whatever is generally leveraging some of that week five week uh four week six material from CS50 in the fancier data structures especially the tree data structures. And if we tell the database in advance, hey, this field is going to be my primary key or hey, this field is going to be an index more generally, whoever implemented the database software itself probably built into it the ability to store that field using a fancy tree or some other data structure that ultimately is going to expedite selecting and updating and inserting and deleting data. And indeed, we can say exactly that if you know in advance that you're going to be searching on a certain field all the time, not just the ID field. Maybe your table has an email field or maybe a zip code field or some other field that you know users might want to search on rather than just store it in a column by defining a field in your database and then really just letting the database use linear search to find everyone in 02138 or 90210 or whatever zip code. You can actually tell your database in advance, you know what, index this zip code field, index this email field because I know I'm going to be searching on that specifically using that special where keyword in SQL and let the database give you better performance than something like linear search alone. Meanwhile, you can also specify, I'm not going to just search on this field. I don't care just about its performance. You know what? I can promise you right now, this field shall be unique. It might be an integer like the primary key, but if there's some other field in a database that you know in advance is going to be unique by design, you can tell the database as much and the database too will optimize queries involving that field as well. So among the fields we've discussed today, name, dorm, phone, email, zip code, and you can probably think of bunches of others. Which of those might be candidate to be unique and to be indexed as unique by your database? Name already feels like a bad idea. We want to be able to have multiple Zamayas, multiple Robs, multiple of any name in the world. Probably want multiple people to live in the same dorm so that there can't be just one person from Courier or Matthews or or the or wherever. What about email? Email's kind of an interesting candidate, right? like assuming you're okay with banning users, maybe a couple of people in your life who happen to use the same email address uh for whatever reason. So long as you're comfortable assuming that every human in the world is going to have or must have his or her own email address, you could proactively say to your database, okay, the email field or the email column shall be unique. I don't want a user with the same email address to accidentally register twice, let alone three times or more. And even if two people think they have the same email address, maybe because of a typo, I don't want him or her to be able to register if someone with that address has already be registered. Phone number maybe works okay in the world of mobiles, but if people still have landline phones, you probably don't want to enforce uniqueness there. But what else might you want to impose? Not null. If you want to ensure that everyone in your database has a name, there's nothing stopping them innately inherently from typing like asdf or whatever into your database. So you might have to protect against bogus data but you can at least ensure that the user has to give you some kind of uh value and then foreign key and we'll come back to this but there's a way of specifying that the data in one spreadsheet or in our world now one table is somehow related to or identical to the data in another spreadsheet or again table. But let's come back to some of these features in a moment. What else do we get from SQL? We also get the ability to join tables together. Now, what does this mean? Well, let's go ahead and let's go back to this spreadsheet for just a moment and let's not worry about students and professors anymore, but let's go ahead and think of this as, for instance, users. I'm going to rename my quick and dirty spreadsheet here, users. And what do I want every user in my world to have? Well, I want everyone to have some unique ID, and I'll let my database ultimately assign that. I want everyone to have a name, uh maybe a uh let's say a mailing address, a phone number, an email address, and there's could be bunches of other fields as well. What else might someone have? That's probably enough for a customer database. And let's go ahead now and consider what these values might look like. So, my very first customer who buys my first widget or whatever might be someone like Alice and she lives at 1 Oxford Street, Cambridge, Mass 02138 USA. Let's go ahead and make this column wider just so we can see it. Her phone number shall be 617495 uh 5000 and her email address shall be aliceample.com. All right, let me make this table this field a little wider as well. Then Bob comes along and he buys something too. He happens to live down the street at 33 Oxford Street, Cambridge, Mass 02138 USA. And his number is 6174959000. And he is just Bob atacample.com. Then there's someone new altogether, Charlie. Uh he lives at uh 51 Prospect Street in New Haven, Connecticut. And his zip code is 06511 USA. and his number will be uh he doesn't want to cooperate so he's just going to give us some bogus number there but he'll be also charlie@acample.com and this table now if you think of the spreadsheet really is just a database table of users could be dozens or hundreds or even thousands of rows long so let's now consider what data types I should be using if I want to migrate this spreadsheet into an actual SQL database so the ID field which frankly you usually usually get sort of for free in a spreadsheet program because it just numbers all the rows for you. But if you resort your data, those numbers do not follow the original rows and so giving our data ultimately will see their own numbers is good because those should remain with the data. Is should always now be one. Bob two and Charlie 3. So name let me go ahead and consider what should the fields here be. Well just for the sake of discussion let me go ahead and annotate right on the screen here. Uh, it stands to reason that this should be an integer. Name should probably be text. Address should probably be text. Phone, I mean, it could be an integer. It's definitely not a real numeric is not right because it could then become real somehow. But is integer right? I am a little worried here. Like especially if it's an international customer. I'm kind of biased at the moment toward uh US users. But suppose someone typed in their actual uh calling code or country code and they did something like 011 uh or O1 for the US and then 617. If I call this field an integer and then let the user type in their actual number like this. If it's an integer, those leading zeros mean nothing mathematically. So my database is probably going to throw them away. And I might not want that to happen. We get lucky and that this still could work as a phone number, but I really shouldn't be throwing away users data. You know what? I'm going to go ahead and just call this text. Whatever the user gives me, I shall store from him or her. And then finally, email can be text. And now let's consider how we might index these columns. We'll come back to ID in a moment because SQL is going to give us that uh automatically in our database. We'll see by using auto increment again. But it will be an integer. Name. I could I don't want to make it unique, right? Because I want to have multiple Alicees and multiple Bobs. I could make it an index. If for whatever reason the website or the application I'm making wants to make it easy for users to search on names, I could proactively say, okay, database index the name column because it will speed up my searches to be something better than linear search. Address, same thing. If I want to use like autocomplete or some kind of search feature so I can search over address, maybe I want to do that. Phone number, maybe that could be unique if I'm expecting only mobile, but that could be risky. Email is the only one I might claim. You know what? A u