So now that we've come up with a way that we augment our logical model to make a physical data model, it's time to do some SQL, and actually create these tables, and put them together. Now, if you're going to, you might have to create a whole new database, and you do this with the superuser. Depending on how we're having you do assignments, there might be slightly different instructions for this. But basically, we're going to create a database. And the pg4e account that we made before has a password, and so we'll create that. But you might get slightly different instructions on your assignments. But at some point, we've got to make a database. And you'll always notice that in this thing I'm running as the superuser, the superuser is both the Postgres user. And then that's the one that creates database. And pg4e, that is a user who's not so super. So once you have that database, the music database, we'll make a connection to that database, and then we'll start typing SQL commands. And so you've been doing create tables before, so you sort of know various things here. You're going to create a table, and then there's a series of columns that are in that table. So the first thing we have is Postgres has a really nice way of indicating the column that's going to be the primary key. And so it has this extension, so that's not in all databases. The SERIAL is a thing that basically communicates. I want a column named id, and I want it to be the primary key. Make it a number, give it to me automatically, incement it. It's a number that starts at 1, 2, 3, 4, 5, 6, 7. And this also then added to that is PRIMARY KEY, tells the database that we're going to be looking these things up fast. And please make me an index on this, a very fast index. And then the logical key, we just add this word UNIQUE. And so what UNIQUE basically is saying is that we're only going to allow one particular name, so AC/DC can only have one row. So if you try to insert a row with AC/DC, the first one will work, and the second one will blow up if it's still AC/DC. And so that's also a way to communicate that there's supposed to be an index on this field, because the database has to index that field so that when it is inserting the next AC/DC, it can quickly decide whether or not AC/DC is already there. But the very thing that lets it figure out that AC/DC's already there makes it so it's pretty easy to look up AC/DC, so it has a quick way to find the row that belongs to AC/DC, okay? And so these two lines here are going to be our pattern, the SERIAL and PRIMARY KEY are our pattern for the primary key. And UNIQUE will be our basic pattern for string-based logical keys. So if we look at the album table, we see that we've got a primary key using that same pattern. This is what you want. Once you've got these, you just like copy paste, copy paste, and change a few things, right? And then we add the UNIQUE oh, there's a typo there. Don't worry about that, that should be UNIQUE right there. And so we have a title, which is our logical key. We say UNIQUE, which communicates. And then we're putting in our first foreign key. And remember, foreign key is the thing that points from one to the other. The foreign key is at the beginning of the arrow. So artist_id is a column. It's an integer, REFERENCES says this references the the id field in the artist table, and then ON DELETE CASCADE. So ON DELETE CASCADE is a way that basically says if we have an artist table and an album table, and there's a bunch of rows in the album table, and they all point to one of the artists, if we delete the artist, then all the rows go away. And that's because it knows we deleted one of these artists, it goes and finds all of the corresponding album entries and deletes them. So we cascade a delete from the artist table into the album table, and that's what ON DELETE CASCADE means. Okay, so the genre table, if you remember the picture of the genre table, the genre table just is something we're going to point at. So in a many-to-one, this is a one. And so it just has a primary key, and it has a unique logical key that is the name of the genre. And the track is is actually, it looks complex, but it's really not that complex. We have a primary key, we know how to do one of those things. You make it serial, and then indicate PRIMARY KEY down here. We make a logical key, but we're going to do this one a little bit different, I'll show you in a sec. These are just columns. Length, rating, and count are just columns. And there are two foreign keys, because if you remember in the track table, they point to an album table, and it points to the genre table. And so we have a pointer album_id points to ON DELETE CASCADE, so that looks like normal foreign keys. And then the cool part about this one is the UNIQUE. Now, in all these other ones, we said we're going to make this field unique, but here, you could have a track called Moonlight that could be on lots of different albums. And so actually, you can't make the title be unique. You can't say there's only one track in all of musicdom named Moonlight, because there's too many of them. So we have this UNIQUE clause, and what we're saying is the combination of title and album_id must be unique. And that is okay, on any album, there can only be one Moonlight track. But on a different album, there can also be one. And so those UNIQUE clauses can be kind of constructed to make the most sense, because if you say UNIQUE on here, if you put it on that one, then it's going to be unique. There will be no two Moonlights, and you'll be like, that's not a very useful feature. So this here is a kind of special unique. The combination of title and album is what's unique in this particular situation. Okay. So once we've built all these things, we can describe it, and you can kind of see after you create that track. You see that it's got a integer foreign key. That serial, when you saw that serial thing, it's really not null default nextval, blah, blah, blah, blah. You could probably type all that stuff in, but you could just say it's serial unique key, we're done. So we have a UNIQUE CONSTRAINT, it tells us what kind of an index it's using, it's using a B-tree index. And here we go, the typographical error is not in this, because the code actually works. So this is the foreign key references with our mark of ON DELETE CASCADE. So once you've created that table, you can ask Postgres what did I just make? And that's it's telling you what it just made. So up next, we're going to do some inserting of the data, and you'll see how these foreign keys work together and how we connect those things to each other.
