So, before lunch we developed a logical model, after lunch we developed a physical model. And sort of implemented it into the database. And then we went home for the night, and now it's the next morning. And it is time to insert some data into our tables. Now you gotta remember, all the keys and fields and stuff like that. Just like we're inserting data from the outward simple things to the more central ones. We're gonna do the same thing when we insert data okay. And so we're using insert into and this is a good time to point out that the keywords inside of SQL like insert into, the case doesn't matter. The names of the tables, the names of the columns may or may not matter. And the actual data you put in, that's case sensitive cuz that's your data. So I'm gonna type, Insert into Artist. And the key thing that is important here is we're not specifying the ID field. So if you recall, in the Artist table, in the Artist table we have an id field and a name field but if you look over here, the id field is an auto generated field. It's not really showing up very well because we didn't have the type. So the id field is auto generated. So when I write this SQL, I don't need to specify the id because as part of our contract, we made a contract with the database, that says you assign that. So I'm gonna run this now, insert into artist the name of Led Zeppelin. And if then we browse data and we go look at the artist it's added to number one. Kinda predictable, you can kinda guess what the next one is gonna be. So the next one thing we're gonna do is we're gonna insert another artist in and we're gonna insert AC/DC in and. We insert that. Now we go take a look at date again, we got two. And if we did a new record here, it would automatically provide this for us, right? I mean, it would let us change this, but it would wanna pick the next number three. So, what we've done here is both inserted the record, but we've also established, for within this database, a new number. One means Led Zeppelin, and two means AC/DC. Okay? So, then the next thing we're gonna do is answered in the the genre, kinda working outward in, and insert the value rock and the value metal. Now I'll show you a little trick here. You can actually in this user interface, of most of these things not just this one, you can use usually put more than one command in, as long as you put a semicolon at the end of it. So I'm gonna, if I did that right, semicolon in the end of it. So I'm gonna insert into genre of rock, and an insert into genre of metal, and I'm gonna run the SQL. I wonder if I did it twice. I did. So I'll just delete this record, goodbye, delete that record, goodbye. [LAUGH] So I did two inserts and then hit the button twice, so that's how I ended up with two copies of it. So rock, for the rest of this lecture is the valueone. And metal is the value two. And again, now when we're making the track table we don't have to put rock, rock, rock, rock, rock we can just put one, one, one, one, one because one is not a string but it's a number, that turns out to be okay. So now we're going to take a look at the album. And this is the first situation were we've got to do a foreign key, right. So we got an album table. And the album has a primary key which is gonna be automatically generated for us and a foreign key called artist id. Because the album points to the artist. And we only add data at the starting points of these arrows. And so, that's what we're doing. So this one we have to put in title, but we don't have to put in ID, so we don't have to put that in. But we do have to explicitly put in artist ID. So when we insert a new album, we're gonna have a title, and then we're gonna have an artist ID. This number we have to remember that two was AC-DC, and one was Led Zeppelin. Now when we're doing this by hand, it's a little trick and you find yourself flipping back and forth to remember like, which one was Led Zeppelin? Which one was ACDC? And it seems like in the short term it would be easier just to be the string in but that's the whole thing we're trying to avoid, okay? So, for foreign keys, we have to put the numbers in explicitly because we or the code we write has gotta remember them. So remembering what these numbers are, the two and the one, is a lot easy for a program to remember then it is for you to remember doing this by hand. Now normally you wouldn't insert this stuff, we'll have code that inserts it and it's real easy for it to say, I just insert genre and I insert rock and I got back a two. So actually you can get that. And so, you the human doesn't have to don't have to remember this. So let's run these, this is our first one that's doing. And I'll put a semicolon here so we can do it all at once. And only push the button once okay, and if we browse data and we look at the album. The ID was auto-generated. The Artist ID was something that we chose and title was something that we put in. So now, we're in pretty good shape. Now the last thing that we're gonna do is probably the most complex. But it's not. We have and id is coming for free, title's data. Length, rating and count that's just data. And then we have two arrows, right? Album pointing to album and genre, album and genre and so we have two foreign keys. Now here is where we have replication now. Those two are going to the same album. And these two are going to the same album. Same genre. Same genre. It's okay to have replication as long as their numbers. That's really the essence of what we've done. We just like, went on the big circle. Took replicated strings and turned them into replicated numbers. So these are okay. And so then what you're gonna do is you're gonna take each of these rows that you know. We don't need that. That clear. Take each of these rows and most of the data we're gonna put in the title. We're gonna put in the album id, the length, the rating, the count, put all those things in. Explicitly this comes automatically and we're gonna do that. Title, rating, length, count. Album id, genre id. Then we have the value [SOUND] and then we have these are the two foreign key values. And they point to those rows. And again this is a point in time where you sort of have written down on a piece of paper what these little numbers were if you were doing it by hand. It wont be too bad. I luckily have them cut and pasted so they're all easy for me and if you're following along then you just cut and paste this guys. And I'll be super lazy and I'll copy all four SQL statements and run over here, paste them in and then I am going to put a semicolon carefully not each line but at the end of it the values thing. So I'm gonna put a semicolon on each of these guys. And I'll have to put a semicolon on the last one. And I'm gonna hit the Run. And I will look at the data. And I will look at the track data. And there we are, that's all there. I can kind of move this over. And that's the track data. And again we specified all those things. So if you look at all these tables, we've constructed them outwardly in. We started at the genre and the artist, and we worked our way in. So that we could establish these numbers, so that then we could link to those numbers as we put things in the album. And then we establish numbers. And so the foreign keys are the starting points. The primary keys are the ending points and we worked in. And so we have replication of numbers in this column but we don't have replication of string. So the word rock if you look at all this data, okay that's a bad example. Right there, forget that, pretend that. The word metal [LAUGH] is better. That doesn't count that's just the title that's not really word rock. The word metal as a genre only appears once in the entire database. And the thing about it is what if there were like a million of these right. One million, you say okay what's the difference. The word metal versus whatever versus the number two. If there's millions of them, it's not just that it takes up storage, it's it has to do with how much has to be scanned. So part of what you're trying to do in database is reduce the amount of data that has to be scanned to get to a particular piece of information. And so the difference between metal which might actually be 128 characters and a number which is actually four characters. That turns out to be div. And it's not we can't start on disk drives. Disk drives are big. It's just that we can't scan past it fast enough. And we can build indexes, so we can jump past it even faster, okay? So that's what we have achieved in this section as we've inserted all these data and we've linked things together and we've modeled relationships and connection points rather than replicating data.
