Original subtitles

[MUSIC PLAYING]

CARTER ZENKE: Well, hello, one and all.

And welcome back to CS50's Introduction to Databases with SQL.

My name is Carter Zenke.

And last we left off, we learned about relating--

that is, how to have multiple tables in our database for people, places,

and things, and how to have them relate to one another in the way

you might do in the real world.

Now, today, we'll take a step forward.

And we'll talk about how to put you in the driver's seat,

designing your very own database schemas to organize your data.

Now, we'll pick up where we left off, which is with this database of books.

So we had this database.

It was full of books that have been longlisted for the International Booker

Prize.

To be longlisted means to be nominated for some prize, let's say.

So we had the past five years of books in this database.

And we worked on improving this database over time from week 0 to week 1.

Now, this week, we'll actually take a look underneath the hood

and see what commands we had used to create these varied databases.

So let's reveal now what we had done all along.

I'll go back to my terminal here.

And if you remember, I could use a command

to open up a database file, which was this command here,

SQL sqlite3, and then the name of the file I want to open.

So let's try this.

I'll go back to week 0, to my week 0 folder like this.

And I'll open up my long list database, week0/longlist.db.

I'll hit Enter.

Now, I'm in my sqlite prompt.

So I could work on typing some SQL commands,

SQL statements or queries inside of this terminal prompt here.

So if I want to get a feel for what's inside this database,

we saw I could use a command called select.

So I'll select now some rows from this table.

I'll say SELECT, let's say, the title and also the author columns

from my longlist table, semicolon, and Enter.

Now, I'll see all the titles and all the authors that

were inside of this long list table.

But I only want to peek here.

I only want to see roughly what kind of data is inside.

So I could probably improve this command here.

I could instead say SELECT the author and title columns from longlist--

from longlist, but limit now to the first five rows we saw before.

Semicolon, and I'll hit Enter on this query.

And now, I'll see only the top five rows.

So I'm able now to see what kind of data is inside my database.

But what I can't yet see is what command was used to create this table

and what kind of data could be stored inside of it.

So let's reveal now what was going on underneath the hood all this time.

I'll say, this new command, a sqlite command-- not a SQL keyword,

but sqlite command called .schema, .schema.

Now, if I hit Enter, I'll see the following--

the command, the statement, the query that

was used to create this longlist table.

And notice how I have many columns inside this table.

I have an ISBN column, a title column, an author column, and so on.

And each column seems to have some kind of data

that could be stored inside of it like text, or integers, or real,

or floating point values, or decimals, if you're familiar.

So this is how we created the very first version of longlist.db.

But let's also see how we created the second.

So I'll type .quit to leave this version of longlist.db.

And now, let me open up the next version we had created,

which is more relational.

It had tables inside of it that could relate to one another.

So I'm going to type sqlite3, then long--

sqlite3, and go to week 1, and then type longlist.db.

I'll hit Enter.

Now, I'm on my next version of longlist.db.

Well, what could I do?

I could type SELECT and look at some of the tables in here

to see what kind of data is inside.

I could perhaps say SELECT maybe the names from the authors table

here, from the authors table, and hit semicolon.

Now, I'll see all the names of authors that are inside of the authors table.

I could do the same thing for books.

I could maybe look at the titles of books.

I could say SELECT title from the books table, semicolon, and Enter.

Now, I see all of the titles that are inside my books table.

But what I haven't seen yet is the schema of this database.

It is the way it is organized and the commands

that were used to create these tables.

So let me work on that now.

I'll clear my terminal using Control-L.

And now, let me type .schema again.

I'll type .schema to see what commands were used to create this database.

Hit Enter, and I can see--

I mean, there are quite a lot of commands here.

Now, if this feels overwhelming, I mean, I'd be right there with you.

This is a lot of commands to parse through and read.

So there's probably a better way to do this.

And one way to try is to type .schema, and then give it some table name.

Let's say, want to understand the schema for just the books table,

like just that for now.

So I'll say .schema, and then the table name--

books, in this case.

Then I can hit Enter.

Now, I'll see the schema, the organization,

the command that we used to create the books table, in this case.

And notice again, we have several columns--

ID, ISBN, title, publisher ID, and so on.

Each one has their own kind of data they could support or take

into this column like integers, text, and so on.

So again, what we'll do today is have you all

learn how to write your very own create table

commands to build your very own databases that represent what you

want to represent in the real world.

So let me exit this prompt here.

And let me propose that we'll have a bit of a design challenge

today to actually try to represent a real world entity with some database

here.

And if you're not already familiar, Boston

is perhaps famous for being among the first cities

to have a subway system in the United States.

So here is a picture from the late 1800s of a subway being

built in Boston's city streets.

Underneath the streets here, there would be

trolley cars that would go and transport people across Boston.

Here's another picture of a trolley actually working

underneath the streets.

So people would go down underneath.

They would hop on a trolley.

They'd be able to different parts of Boston, perhaps from Harvard to MIT

or downtown up to, let's say, Braintree or down to Braintree,

which is more south of Boston, for example.

One of the famous stops is the Park Street stop,

which is right down in the middle of Boston,

one of the central hubs of this subway system.

And now, these photos are all from, let's say, the early 1900s, late 1800s,

and so on.

But the subway has gotten a lot more modern since then.

And actually, now, we have several lines that span the entire city and beyond.

So here, we have the Red Line, of which Harvard and MIT are a part.

We have the green line, which brings you kind of west to east--

the blue line, the orange line, and so on.

So many more lines and stations have been added to this system.

It's a big design challenge to represent all of these stations,

all of these lines, and all of these people who might ride this subway, too.

So the question then becomes, how can we create a schema for this data?

And again, by schema, we mean what kinds of tables should we have,

what kinds of columns might those tables have, and what kind of data

should we put in each of those columns, for instance.

So let me propose this.

Let's say we start just first with names and stations.

So Charlie here, our very first rider on this system,

is going to be at the Kendall at MIT station.

So this is what this table represents now.

But what more could we have?

Well, we might also want to have maybe what Charlie is doing at that station.

Maybe he's entering the station, for instance.

And if you're familiar with the subway system,

you often have to pay to get onto a train or get onto the station itself.

So let's say Charlie pays some fare to enter into the Kendall/MIT station.

Well, back in the mid 1900s, the fare was only about a dime.

It was $0.10.

So we'll say Charlie paid $0.10 to enter the Kendall/MIT station.

And now, this seems pretty good.

But if I am the Transit Authority, the person who runs the subway system,

I probably want to know, does Charlie have enough money to get on the train?

And if so I want to make sure that, OK, well, Charlie actually

could get on this train.

So let's say not only does Charlie pay a fare.

He has some remaining balance afterwards.

So Charlie here has gotten onto the Kendall/MIT stop.

He's paid the fare of $0.10 and has $0.05 left.

OK, so here's a bit of a table.

We probably add more information to it.

Let's say Charlie then leaves at the Jamaica Plain stop.

And the fare to leave is about a nickel, $0.05.

And now, Charlie has no cents left over.

So again, Charlie paid $0.10 to get on, had $0.05 left, paid $0.05 to get off,

and now has no remaining balance here anymore.

OK.

So that's Charlie's story.

Let's look at Alice, though.

Let's say Alice gets on at the Harvard stop.

They too pay $0.10 to get on at the Harvard stop.

And they have a remaining balance of $0.20.

Alice will go, let's say, to Park Street, get off at Park Street,

pay the nickel to leave.

And now, they'll have a balance of $0.15 at the end.

Let's go to Bob.

Bob enters the Alewife station.

They pay $0.10.

They have remaining balance of $0.30.

And let's say they leave at Park Street and have a fare of $0.10 to leave

because it's a further distance.

Now, they'll have a running balance of $0.20 overall.

So this table is OK, I might admit.

I mean, last time, we learned about having what we called primary keys

and foreign keys.

So it seems like that's missing here.

Let's go ahead and add that here.

I'll give each row a unique ID, so I can know who entered, who exited,

and so on, give that a unique ID here.

But I might even say that this table could really be improved substantially.

And I want to ask you what redundancies or inefficiencies do you see here?

If we're trying to represent riders and stations,

what can we improve about this design?

AUDIENCE: So probably the redundancy will be the names and the stations,

too.

For example, if Charlie will go to the train daily,

then he will become most of the names in the data.

CARTER ZENKE: Yeah, a good point.

If I'm hearing what you're saying, [? Loren, ?]

let me show you some examples that I highlighted here.

One example could be, to your point, about these names.

These names seem to be telling us the name of a person.

But here, we have only three names--

Charlie, Alice, and Bob.

Well, my name is Carter.

And what if somebody else named Carter also

tried to get on and leave at some stop?

Well, I wouldn't be able to know which Carter

was which or which Charlie was which, which Alice was which, and so on.

So we probably need a way to represent people and their names a little better

here, too.

What other ideas do we have for how to improve the design of this table?

AUDIENCE: Yes, I think we can have a singular ID for a singular person.

That way, we'll be better able to track their activities.

CARTER ZENKE: Nice.

So we probably have an ID for each person,

a bit what we learned about last week, putting people in their own table

and giving them their own unique ID, a primary key.

Let's show that here.

I'll go to some slides.

And I'll pick out one that shows us just riders.

So to your point, [? Soqanya, ?] we could try

to have maybe a table for just riders.

And maybe to simplify, this table has only two columns.

It has a column for ID and a column for name.

So here, we have Charlie, Alice, and Bob all in their own table.

Well, let me propose to you, we could do the same thing for stations.

Let's say we have a table of stations now.

And we give each one their very own ID as well,

our own primary key for this table.

We have Harvard, Kendall, and Park Street.

We can differentiate between them using their IDs here.

So a few improvements could be made.

And as we're making these improvements, splitting one table into many,

and debating what kind of data to store in each table,

the process we're going through is one called normalizing.

We're normalizing our data.

To normalize means to reduce redundancies, effectively,

to take a table--

take one table, for instance, split up into multiple,

and have each entity be part of its very own table.

Some academics in the world have named different normal forms, quote unquote.

There's like first normal form, second normal form, third normal form,

this progression of making your day more and more efficient.

You can use those as heuristics.

But end of the day, a few principles might apply.

First, take your entities, like in this case stations and riders,

and put them each in their own table.

And if you add more data, make sure that if I were to add a column, let's say,

to riders, it is only a data point about riders--

not about stations, not about fares, only about riders.

And that process can help us make a data set that

is more dynamic, more easy to reproduce, and more easy to write queries on.

So that is the process here of normalizing.

OK.

So if we have now riders and stations, we want to represent them in our table.

Well, we could use what we learned about relating

last week to ask, how could we actually represent

these riders and these stations?

So let's say here I can have riders and stations.

I want to make sure that I have the right relationship between them.

Well, if you're familiar with subways, we

might say that a rider goes to one station.

And this big T here is the symbol for a station here in Boston,

for the T's that we call it, for the subway.

So a rider might go to one station.

But of course, that might not be the full picture.

A rider also gets off at some station on.

So a rider could be associated with not just one station, but multiple.

And if you're familiar, at least with any subway system or the Boston one,

too, it can often get pretty busy.

And so riders might not just go to, of course, one station or two.

Stations could also have multiple riders that are on a particular station here.

So to recap, one rider might be associated with more than one station.

They might get on at this first one and get off at this later one.

But each station could presumably have more than one rider.

Each station here could have rider A or rider B, the rider

up here or the rider down below, and even many more than that as well.

So to put it in the language of our ER diagrams,

our entity relation diagrams from last week,

we could look at it bit like this, where we have riders and stations.

Riders visit stations, and they're associated like this.

A rider must have at least one station associated with them.

That's what this horizontal line means.

If they aren't at a station, they aren't really a rider, right?

A rider though could have many stations associated with them.

That's what this three prongs down here means.

They could have one, two, three, four, they

could have many stations they get on and get off of.

Now a station could have anywhere between zero riders,

if it's maybe out of commission or isn't very popular, upwards to many.

It could have two, three, four, five, even hundreds of riders associated

with this particular station.

So here is our entity relation diagram for these particular riders

and these stations here.

So let me ask, what questions do we have on these relationships between riders

and stations and how to design this table so far?

AUDIENCE: Then I want to ask that you have

used the same ID for stations and riders so that maybe

give us a problem in coding?

CARTER ZENKE: Yeah, a good observation.

So you might have noticed that in the riders table and in the stations table,

I gave the same kind of ID.

Like I had one, two, three for each of them.

And let me just show you that again here.

I'll come back to some slides, and I'll show you again,

the riders table where we had Charlie, Alice, and Bob, ID 1, 2, 3.

Same for the stations.

We had stations Harvard, Kendall, Park Street, ID 1, 2, 3.

And to your question, isn't that a problem?

Well, I would argue in this case, it's not,

so long as we keep clear that these IDs are for stations

and these IDs are for riders.

And we'll see how to do that using our SQL keywords later on.

But again, so long as we have an ID just for our riders and an ID

just for our stations, we can keep these separate

even if they might have the same values.

But a great question here.

Let's take just one more.

AUDIENCE: Regarding the entity relationship diagram,

how is it possible for a station to have a possibility of zero riders,

but riders must compulsorily have at least one station.

CARTER ZENKE: Yeah, good question.

So this might be up to you and how you formulate it,

but for me, let me show that diagram again.

I'll go back to over here.

In my mind, to be a rider, you have to visit a station.

If you aren't visiting a station, you aren't really a rider, right?

Now presumably, there are stations that were built but aren't really being

used right now or aren't really in service yet.

That could be a station that has no visitors.

So you could argue--

let's make sure every station has at least one rider

and every rider may or may not have to visit a station.

For that I would say, we could probably reasonably

disagree there and talk about how we could represent the diagram here, too.

But a great observation and a good other critique of this system here.

All right.

So let's now turn to representing this in our database.

I'll go back to my computer and we'll learn

about this new SQL keyword, SQL statement,

this one called Create Table.

Create table allows us to, as the name suggests, create a brand new table.

So let's do just that in our new database

to represent riders and stations.

I'll go into my terminal, and I want to make a brand new database.

I'll call this one mbta.db, because mbta stands for the Massachusetts Bay

Transportation Authority, the people who run the subway, essentially.

So I'll do sqlite3, mbta.db, hit Enter, and I'll

type Y to say yes, I want to create this brand new database.

Now if I type dot schema, I see nothing.

But that's expected.

I don't have any tables yet.

I have nothing inside this database.

It's up to me to create these tables myself.

So what I'll do is clear my terminal, and let's start first with riders.

I might create a table for riders.

I'll say, create table.

And now I have to give that table some name.

I might call it riders here.

And then in parentheses, like this, I can specify what

columns should be part of this table.

So let's start first.

I'll hit Enter here and continue this query.

Now, all by convention, I'll just indent four spaces--

one, two, three, four, and I'll give an ID to each of these riders

as we saw before.

I'll say ID here is one of my columns.

Now to create a new column, I'll follow this up with a comma and hit Enter.

I'll again, by convention, for style, just indent four spaces.

And what's my next column?

Perhaps a name for these riders.

I'll give this column the name, name, and I'll leave it at that.

Once I'm done adding columns, I no longer need to have a comma.

I could simply close out this query, this statement.

I could hit Enter here, say close in parentheses

to close the top parentheses here, semicolon, hit Enter.

And now nothing seems to happen.

But that's actually a good sign.

So let me type dot schema, hit Enter, and I'll

see the result of that statement before.

Create table, if it doesn't already exist, riders, and riders

has inside of it two columns, ID and name.

OK, let's keep going.

Let's make one for stations, too.

I'll clear my terminal and I'll say, create me

a table called stations and include--

actually, not station.

If you ever want to, let's say fix this kind of table here,

let me try closing the parentheses, hit semicolon, Enter.

I'll get a syntax error.

I can restart.

I'll do Control L. Now I'll do create table, stations, plural.

Open parentheses, Enter.

I'll indent by four spaces.

One, two, three, four.

And now, I'll similarly include an ID for each of these stations.

I'll say ID comma, and then what all should I have?

Well, stations tend to have a name, like the Kendall MIT station,

the Harvard station, the Park Street Station.

So I'll say, I'll give each of these their very own name, comma.

What else do stations have?

Well, they might also have a line that they're on.

Let's say it's the red line, or the blue line, or the green line and so on.

I'll have a space for them to write in their line that they're a part of.

OK, and maybe I'll leave it at that to keep it simple.

I'll say, stations have an ID, a name and a line.

Now I'll close this out.

I'll say end parentheses, semicolon, hit Enter, and nothing seems to happen.

But if I type dot schema, I'll now see my riders and my stations

tables inside of my database.

Now one more step here.

We have riders, we have stations, but we have to relate to them.

We have to relate them using this many to many relationships,

as we saw last week.

So let me try making a table to implement

this many to many relationship.

And if you remember, we might call this kind of table a junction table,

an associative entity, a join table.

It has a lot of names, but it looks a bit like this.

I'll create this new table to represent, let's say, visits.

A rider visits a station.

So I'll call this table visits.

And inside, I'll make sure it has two columns, one for a rider

ID to represent a rider, and one, let's say, for a station ID,

to represent a station.

Now when I see a rider ID next to a station ID in this table,

I'll know the rider with that certain ID visited

the station with that certain ID.

So I'll close this out.

I'll say end, parentheses here, semicolon, Enter, and finally, clear

my terminal, type dot schema.

And I can see, I have riders, stations, and visits between riders and stations

in this associative entity, this junction table or a joined table.

Up to you what you might want to call it in this case.

Now what questions do we have?

AUDIENCE: Why we have not use the primary key

and secondary key in this table?

CARTER ZENKE: Good question.

So we're going to get there in just a minute.

But if I look back at my terminal here, my schema, I'll see,

I really just have column names.

And we saw before and we typed dot schema on our longlist.db,

we had more than just column names.

We had column names, we had perhaps data types,

we had primary keys and foreign keys.

So we'll get to that in just a minute.

But suffice to say for now, we're going to keep improving this over time.

Let's take one more.

AUDIENCE: Is it required to put spaces--

the four spaces indents, or that's just for the visual look?

CARTER ZENKE: Yeah, great question.

Is it required to have these four spaces before each column name.

And in fact, no, it's not, but it makes the code more readable.

So I could put this all in one line--

I shouldn't, but I could.

And if I have instead this new line followed by four spaces,

I can make this more readable for myself and for my colleagues, too.

Good question.

OK, so to our earlier point, there are things

that are missing from this schema.

Like, we have column names, but as we saw before,

we should ideally specify what kind of data

should be able to go into each of these columns.

And for that, we'll need some new ideas to talk about here.

So let's focus now on this idea of data types and storage classes.

Data types and storage classes are two very similar but distinct IDs,

and they're distinct in a way we'll talk about in just a minute.

Now SQLite has five storage classes, five kind of storage--

kind of type, so to speak, of values that can hold.

So let's talk about the first one, null, for instance.

Null in this case means nothing.

There's nothing that actually is inside of this value.

It's kind of a central value to mean nothing is here.

Integer means a whole number, like 1, 2, 3, 4, or 5.

Real talks about decimals like floating points like 1.2 or 2.4 and so on.

Text is used for characters.

And blob, kind of a funny one, blob stands for Binary Large Object,

and it represents the data exactly as I give it to this value.

If I tell it to store 101010, it will store exactly 101010 in binary.

So useful for storing, in this case like images and video files

and audio files, things that have some structure

we don't want to mess around with.

Now let's focus on this idea of a storage class.

These, I'll say, are storage classes and not data types in SQLite.

Now a storage class like integer can comprise, can hold several data types.

Notice how there are seven different data

types that could be stored under this integer storage class.

We have a 6-byte integer, 2-byte integer, a 8 and 0 and so on.

It could be any of these particular types,

but each of these types under the umbrella of this integer storage class.

And SQLite itself will take care of making sure

it uses the appropriate data type.

Like if I give a very large number, like let's say 4 billion or 6 billion

or even bigger than that, it will probably use a longer--

that is, a bigger byte integer to store that kind of value.

If I give it a smaller one, like 1, 2, 3, or 4,

it will probably use a 1-byte or a 2-byte integer for that.

But SQLite's idea is that I, as a programmer,

shouldn't have to care if I use an 8-byte or a 1-byte or a 2-byte integer,

I just care that I'm using integers, whole numbers,

and they give me a storage class to use any of these up to their choice

here as well.

Now let's look at a few examples of values in SQLite that we could store.

Well, we have perhaps the red line as some text.

And because this is characters, it's quoted,

we could use the text storage class to represent this particular value here.

We could have maybe an image, and to the earlier point,

we could say, well, this image might be best represented

using a blob, a binary large object, to keep all of these pixels

exactly as they are in this image.

But we do get some choice, some interesting design

challenges when we look at the idea of fares.

So let's say to our point earlier, fares are $0.10 back in the 1950s or so.

Well, $0.10 we could store as an integer, which seems just fine.

But this could get confused.

I'm talking about dollars here or cents?

Maybe it would be better, let's say, if I did this.

A dollar sign, 0.10.

And what might that be stored as?

Well, probably text, right?

I could say this dollar sign isn't really a number,

but now I have to include it, so I'll say this will be quoted, essentially,

as dollar sign 0.10.

Now there's some downsides here, too.

Like let's say I have--

I want to add these up.

Well, I can't add up text.

Like, what does it mean to say dollar sign 0.10 plus dollar sign 0.20.

I can't do math with text.

So maybe it'll be better after all if I used a real or a decimal like this,

0.10.

But I mean even here, you'll run into some problems.

If you are familiar with how the number is represented in binary,

you might know that decimal values or floating point values

can't be perfectly, precisely represented.

And if I talk about 0.10, the computer might store 0.10000056789.

It could get very wonky out to the many, many decimal digits down below here.

So trade offs and challenges overall.

Let's look at these three, though.

I have the first one to store as an integer.

I'm trying to store fares here, second one as text, and the third one

as a floating point or a real in this case.

Let me ask for some opinions here, which one

would you use and why, for trying represent fares in this case?

AUDIENCE: Thank you.

I prefer using integers because of course,

I need to get the calculation very accurately.

That's my point of view.

Well, sometimes I can use float but, you know,

like you said before, it can get very wonky

if I really need that kind of precision, I don't really recommend using floats.

CARTER ZENKE: Yeah, a good point.

So if I go back to some slides here, you might argue for the integer

because you know you can precisely represent integers.

And let's say I want to add up fares over a lot, a lot of riders.

This might be useful for me because I know that each number will

be perfectly, precisely represented.

I can do lots of big kind of math with this number here.

To your point, this decimal might kind of, as you said,

get wonky later on towards the later decimal points.

I might get some unexpected results if we add up these overall.

Let me ask, though, are there any proponents of this floating point

value or a real value?

AUDIENCE: So I think the--

I think the float is the number of, for example,

for each pair, like the answers like truncation

probably suggest by the comments there.

CARTER ZENKE: Yeah, a good point.

So if you talk about using this float value,

I mean, one thing we could say for it is that this decimal could be--

it's more accurate to say, like, where you're working with dollars now,

and we could have maybe $0.10, which is only 0.1 of a dollar.

I totally hear that point as well.

And the point we're making here is that they're really

just trade offs among these data storage classes

to use, whether you're using integers or real values,

it just depends on your use case and what you're designing for.

So be sure to read up on trade offs among these data types,

and determine for yourself which one should you best use.

OK.

So we have now these storage classes to store values,

and it turns out that columns can be made to store certain storage classes

or prioritize certain classes.

And the key distinction here is that columns in SQLite

don't always store one particular type.

Instead, they have type affinities, meaning

that they'll try to convert some value you insert into a given cell or given

row to the type they have the affinity for.

Now there are, let's say five type affinities in SQLite--

text.

Columns can be of the type affinity text,

meaning they store just characters at the end of the day.

There's also numeric, which stores either integer values or real values,

depending on which one seems best to convert to.

You have integer type affinity, which means it can store whole numbers.

Real, to store floating points or decimal values.

And we have blob here again, our old friend

to store binary exactly as we get it.

So whereas before we were talking about storage classes--

those are associated with individual values--

type affinities are now associated with individual columns.

So let's see some example of how this might work in SQLite.

Let's say I have this table of fares, and we've

decided to store fares as integers.

Well, if I said that this column called amount has the affinity for the text

storage class, what would happen is if I insert this integer, 10,

it would look a bit like this later on.

It would be converted to text, it would be quoted in a sense,

to represent-- it's now been converted to some set of characters.

Let's say I insert this value 25.

Well, 25 has a storage class right now of an integer.

It is a whole number.

But if I insert this into a column that has the text affinity,

it will be converted into, in this case, text at the end of the day.

Let's do the opposite.

Let's say I have my fare as a string, some text in this case.

I want to insert it into this column called amount,

but now amount has the integer type affinity.

Well, if I insert 10, quote unquote, into the column amount,

I'll get back not 10, the text, but 10 the integer, because again, amount--

this column here-- has an affinity for the integer storage class.

Let's try this.

25, some text again I'll insert it into this table.

Now I'll have 25 as an integer.

So this is how SQLite allows us to give certain columns

an affinity for certain types, that they'll

try to store values of that type, so long as we insert values it could be

feasibly converted to that type here.

So let's go back to our schema and try to improve it now,

to use not just column names, but also type affinities to store

certain data of a certain type.

Go back to my computer here, and let's improve this once more.

So I'll go over to my table.

And now, I probably want to improve the design here.

And often, if I want to improve this, I might just

need to erase what I've already done.

So let me introduce this new keyword, this new statement called Drop Table.

To drop a table means to delete it, to remove it, effectively.

So let me try doing this for riders, stations, and visits.

I'll type drop table writers, semicolon, Enter.

Nothing seems to happen, but if I type dot schema, well, riders is gone.

I'll try drop table stations, then semicolon, hit Enter, and type,

let's say dot schema again.

No more stations.

I'll try drop table visits, semicolon, Enter,

and then dot schema, and our table--

our database is really gone.

There are no more tables inside of it.

So let me propose that instead of working inside of the SQLite

prompt, like typing out again, and again, and again, create table riders,

create table stations, create table visits,

let me be more efficient about this and create myself

a schema file that I could reuse throughout this lesson

and also later on while I'm working on this database on my own.

To do that, let me quit my SQLite prompt here, and let me

type something like code schema dot sql.

I'm just creating this file called schema.sql.

Now a dot SQL file allows me to type in SQL keywords

and have them be syntax highlighted, so I know

what's going on inside of this file.

So let's just try this once more.

I'll type Create table riders, and inside I'll

say it has the ID column of what type affinity?

Well, IDs are whole numbers, so perhaps integer in this case.

I could say, ID has the integer type affinity.

Now let me say the riders also have a name,

and how could names be best represented?

Maybe text, right?

Characters here.

So I'll say name and text.

Now I'll include a semicolon to say, this

is the end of my create table statement.

And before, remember how I had to kind of air out

or I had to like, backspace and so on to improve the design?

Here I can literally just point and click and edit this file

to improve my schema.

And I'll later on apply this in my database using

a command that we'll see a bit later.

So let's keep going here.

I'll say create table stations, and inside of the stations table,

I'll make sure it has an ID column of type integer,

a name column that probably stores some text,

and a line column that also stores some text, where name

is the name of the station like Kendell MIT, Harvard,

and line is blue line or green line, what line it's part of in our subway.

Let me try now visits.

I'll say, create table visits.

And then I'll do rider ID, which has what type affinity?

Probably integer.

And then I'll do station ID, which has this same type of affinity.

It's relying on integers, whole numbers here for IDs.

So my colon to finish this statement.

Now this is my schema as a whole.

I have riders, stations, and visits.

But now I want to apply this schema to my database.

So what could I do?

I could reopen it, let's say.

I'll do sqlite3 mbta.db in my terminal.

And now I want to read in this schema.sql file,

just run the commands that are inside of this file.

So what could I do?

I could say dot read schema.sql, where dot read is a command that

says take whatever file you give me, like schema.sql

and read it into this database, running any SQL keywords you come across there.

I'll hit Enter.

And now nothing seems to happen.

But if I type dot schema, I now see my schema improved.

And this is helpful for me, because what I could do now

is I could just edit my schema.sql file and rerun it and rerun

it to make sure I now have these tables being improved over time.

OK.

So this, then, is our new representation of our database.

We have riders, of course, their own entity, and stations.

They have an ID and a name, and stations have an ID, a name, and a line.

We've also now included these type affinities-- integer, text, integer,

text, to tell SQL what kinds of storage classes

could be put inside of each of these columns.

Now before we keep improving this, let me ask,

what questions do we have on these storage classes and type affinities?

AUDIENCE: That you would use--

when you were creating the table, the table was not in line.

So when we search for the authors of books,

so it comes with a perfect table, so how can we make a perfect table in the SQL?

CARTER ZENKE: Yeah, do you mind clarifying what

you mean by like, the perfect table?

AUDIENCE: So I mean that it was arranged in something

like in boxes, block boxes.

CARTER ZENKE: Good question.

So before we able to see the results of our queries

inside some boxes in our terminal, and that is actually a mode of SQLite,

I think type like dot mode table to see your results in that version.

Here though, we have no data inside of our tables,

so I can't really select anything from them.

Like, if I go to my terminal here and I try

to select some data from this riders table,

let me say select star from rider's, semicolon, I won't get anything back.

Next week, though, we'll see how to insert, and add, and update, and delete

data inside of these tables, at which point,

you could write, select star from riders and see some data

you've inserted yourself.

Great question.

Let's take one more here.

AUDIENCE: Yes.

I would like to know if you have a column of the type boolean.

CARTER ZENKE: Yeah, do we have a Boolean type affinity, let's say.

So here, we don't, at least in SQLite.

Some other DBMS', Database Management Systems might have bool or Boolean,

true or false, right?

Let me show you this instead.

If I go to my terminal, I can see-- if I type dot schema,

I haven't used Boolean, there's no need for me in this case,

but I have used integer.

And integer for SQLite can kind of serve the same purpose.

I could have the integer 0 or the integer 1 to be true

or to be false or true, respectively, 0 being false and true being 1,

I believe in this case.

But good question.

OK.

So to the earlier question, like, we've improved our tables.

We now have type affinities for our columns.

But we don't yet have this ID we talked about last week,

which was primary keys and foreign keys.

This idea of trying to uniquely represent each item in our own table

using primary keys and trying to reference

that primary key from some other table using foreign keys.

So let's try to work on that now.

And for this, we'll need this new ID called a table constraint.

In SQLite, you can apply what's called a constraint to your entire table.

A constraint means that some values have to be a certain way.

Like let's say for a primary key, primary keys must be unique.

They can't repeat, and they must be, at least in our case,

is going to be integers, to be able to quickly add on to them over time.

Similarly, for foreign keys, well, a constraint

is that if you have a foreign key, you better

find that value in some other table, otherwise

you violated this constraint to having a foreign key.

So we have two kinds of table constraints, among others,

but two of these are primary key and foreign key.

And we can apply these to our table by applying them

underneath the columns we tend to say will be inside of our table.

Let's try these two here now.

So we come back to my terminal here so we implement our very own primary key

and foreign key constraints.

We'll go back to my SQLite terminal and clear my screen,

and let's then pull up our schema.sql file

so we can keep modifying our schema.

I can now see I have the riders table, stations, and visits,

and I have some columns that could be primary keys or foreign keys,

but I need to declare them as such.

So here in the riders table, what was our primary key?

Well, it was ID.

Every rider should have their own, unique ID that should not

be duplicated across any two riders.

So to ensure that constraint is applied, I could follow this up with a comma

and then say, primary key ID, just like this.

Now ID is the primary key of this rider's table.

I can go down to stations and ask, what was my primary key?

Well, similarly, it was ID, this ID column on line 8.

So I'll type a comma followed up with primary key ID,

and now that ID column is a primary key.

It has that constraint applied in stations.

But now if I get down to visits, we'll have a few more options.

Visits here actually doesn't have its own ID column that I created.

I instead have a rider ID and a station ID column.

So a few options here.

One option is actually to make a joint primary key.

I could have a primary key composed of two columns, both rider ID and station

ID.

If I applied that constraint, that would mean

that if I were to ever insert a row that had the same rider

ID and the same station ID as another row,

I would trigger a constraint violation.

Every row that has a rider ID and a station ID

has to be unique in their combination of those two values.

To write that kind of scenario, I could follow this up

and I could say similarly, the primary key of this table

is not just rider ID like this, but it's also station ID.

Now this is a joint primary key constraint.

But if we think about this logically, I mean,

it kind of stands to reason that somebody would visit a station more

than once, and I don't want to make sure that every combination of rider

and station ID should be unique.

I want people to able to visit a station more than once.

So maybe not the best design for this table, but I could certainly use it.

In other cases or other contexts.

One other option would be to do it before

and to have maybe the ID column here of type integer,

and then down below, make that our primary key, a bit like this.

And now visits has its own ID column.

But actually, SQLite by default will give me its very own primary key,

one called row ID.

It's implicit, I can't see it, but I actually could query it.

I could query it for row ID, all one word,

and get back a unique primary key for this table SQLite has automatically

created for me.

Now we have seen the primary key options,

what are our foreign key options?

Well, it seems like rider ID and station ID

are the foreign keys of this table where rider ID references the ID

column in rider's, and station ID references the ID column in stations.

So to codify that, to make that a reality in our schema,

I could follow this up with the foreign key.

The foreign key of this table is rider ID,

and it references the rider's table and the ID column

inside of it using this syntax here.

Now I could keep going.

I could say I have more than one foreign key.

I also have a foreign key--

foreign key called station ID.

And that references, like we said before,

the stations table and the ID column inside of it.

So now here I have my completed schema.

I have a primary key for the tables I've declared an explicit column

for a primary key, and now I also have foreign key constraints

for those columns that should be foreign keys.

So now let me ask, what questions do we have on the schema

or on primary keys and foreign keys?

AUDIENCE: So, yeah.

I just noticed that whenever before we hadn't

added the affinities and the keys, we were not

applying commas after each column name.

So what is the difference there?

CARTER ZENKE: Yeah, a good catch.

Let me kind of show you what this looks like in my terminal

so you can see it live.

You, I think, had noticed that before we had,

let's say, this primary key ID constraint in riders,

we had done something like this.

Let me just copy paste that.

And we had removed this last column--

or comma from the name column.

Is that right?

And if that's the case, well, it's just convention, just style here.

So if I want to keep adding some constraint

or like a new line to my table, I should include a comma.

Here, this name column was the last portion of my table I had specified.

I have this column called name that has type affinity text, right?

But now, if I add this new constraint, we'll

have to follow it up with-- follow it up after a comma from this new column

here.

Notice now, this constraint, primary key ID, is the last--

let's say attribute of my table I specified.

I no longer need to include a comma at the end of it.

So whatever is the last portion, I should not have a comma after,

but everything else, I should.

Let's take one more question here, too.

AUDIENCE: Would it be OK for the visits table to have an ID column as well?

CARTER ZENKE: Yeah, good question.

Would it be OK for the visits table to have an ID column as well.

It certainly would be.

We could define our very own primary key for this table, too.

So let me go back and show you how that could work.

I'll go to my visits table here.

And I could try to add my own primary key to this table.

I could say ID, make a new column here, make it a type affinity integer,

like this.

Let me scroll up.

And now let me add some new constraint.

I could say, because I've made my very own primary key,

I'll say primary key ID.

Now, this table has a primary key that I've created called ID.

And this will be in place of SQLite, so that it

would have made called row ID itself but hidden from my own view.

OK, let's keep going then.

We've seen table constraints, we've seen type affinities,

but we could probably do more to improve the design

of this table or this database.

So let's introduce one more kind of constraint,

this one called a column constraint.

So whereas a table constraint applies to the table as a whole,

a column constraint applies to a particular column, let's say.

Maybe I want to column to have certain data inside of it.

Maybe I want to make sure it doesn't have null values, and so on.

I could do that with column constraints.

There are four in SQLite--

check, default, not null, and unique, and each one does something different.

Check allows me to make my very own check, like check to be sure

that this amount is greater than 0.

Or I could use default. Default means if I

don't supply a value when I add a new row,

it'll just use a default value instead.

Not null means I can't insert null or empty values into this column.

And in fact, it's required.

Unique means, I want to make sure that every row in this column

is a unique value.

It doesn't appear twice in my data set.

So let's try applying a few of these to our schema here.

Go back to my terminal.

And now let me check out this.

Well, I could try applying the not null constraint

when I know I want a column to be required, effectively.

Now where could I best apply that?

Maybe I could apply that to the name column in stations,

like stations must have a name.

So I'll say, the name column cannot be null.

It cannot be empty in here.

Line also should probably be not null.

A station must be part of some line.

I can't have an empty value for line.

So I'll say, too, this should be not null.

Now I could apply this up at name.

I could say, riders must have a name, too.

Let me try that.

I'll say text not null.

Or I could leave it optional.

I could say, maybe text just on its own and let

riders choose to supply a name or not.

Now the question here is, should I apply not null to my primary key columns,

like ID not null or ID not null here?

You might think that you should, for thoroughness sake.

Well it turns out that when you apply the primary key table constraint down

below here, this already ensures that there are several constraints applied

to this particular column called ID, among them being that ID cannot be

null.

So no need to duplicate this and say that this ID cannot be null when I

already have it specified down below that ID is a primary key.

Let me check others here.

You might also think, could I do it for rider ID and station ID?

Should I include not null here?

Rider ID, not null, station ID, not null.

Well, that would be a good thought, but again, we're

taken care of by our table constraint using our foreign key here.

Again, this constraint will say, if rider ID doesn't already

exist in the ID column of rider's, I can't insert that value.

And we could probably presume that if rider's ID is a primary key,

well, null will not be part of this column,

and therefore, I already can't insert null for rider ID or station ID.

This would be in this case, redundant.

So not null is good when you have columns that are neither

primary keys nor foreign keys and you want

to make sure that they have-- they always have a value, that they

are never null, effectively.

So that is not null.

So we could keep going here.

We also had one called unique that makes sure every value,

every row in this column is unique.

Where could we apply this?

I could try to apply it, let's say, to the name of a station.

Like, station should have unique names.

So I'll say not null and unique.

Now this column has two constraints.

The first, not null.

It should always have a value.

The second, unique.

The value shouldn't repeat throughout this column.

Line I might leave without this constraint.

I could imagine two stations being on the same line, like both on blue,

I'll allow that in this case.

Now again, we could try to apply unique to our primary keys or our foreign keys

as I just did here, but it's already taken

care of for us using this primary key constraint.

A primary key, again, is always going to be unique and never null.

So we'll take advantage of that already using our primary key and foreign key

constraints here.

OK.

So we've seen unique and not null.

And I might argue, we're at the point where this schema seems

to be fairly optimized, at least using our column constraints, our table

constraints, our type affinities and so on.

So let's ask then, what questions do we have on not null and unique, if any?

AUDIENCE: So basically, to recap if I understood correctly, it's

not precisely about not null and unique but about

the concept of the key, labeling key immediately

gives the attribute of not null, unique, and to be referenced, right?

CARTER ZENKE: That is true.

So when you use a primary key or a foreign key constraint,

there are other constraints that go along with that constraint.

A primary key, for instance, must not be null, it must be unique, and so on.

So it would be redundant to apply that again,

to say that this primary key should be unique or not null.

Good clarification there.

OK.

So I think we're at the point where this schema is pretty well set for us,

and we're going to need to think about how we use this in the real world.

Like, if this is our schema here, we have riders and stations, well,

what could we do?

I mean, riders tend to--

I mean, they could register for the subway.

And riders, well, they do visit stations.

But I think if we applied this to the real world,

we'd see that this isn't quite how it's actually done.

Like, riders don't really register that often.

If a rider who's new to the city comes in, they want to ride the subway,

they should be able to ride, too.

So it turns out at least here in Boston, the MBTA doesn't really

track riders, per se, but they do track what we call CharlieCards.

If you want to ride a subway, you get a CharlieCard.

A CharlieCard allows you access to the subway,

keeps track of your fare, your balance and so on,

and allows you to swipe in to certain stations.

So when we come back from a break here, we'll

see how we can actually implement these CharlieCards instead of riders

to make our system more efficient and more in line with what

happens in the real world.

We'll see you all in just a few.

And we're back.

So we saw last time, this challenge of designing

a system for the Massachusetts Bay Transportation Authority

to represent riders and stations-- people

who get on a subway to go around Boston.

But as we also saw, we learned that the MBTA doesn't

keep track of riders themselves, they keep track of CharlieCards, this card

that a rider might carry in their pocket.

And they can scan this card at a station to enter or even to exit in some cases,

to make sure that their fare is counted as they enter that station.

So let's think now how to improve our schema to represent not just riders,

like me, But CharlieCards themselves people might carry around

when they enter the subway station.

Well, we saw before we had riders and cards.

But our goal now is to remove riders from the picture, focus only on cards.

Well cards, as we've seen, might make us swipe at a station.

If I enter Harvard station, I might swipe my CharlieCard

to enter that station.

And we could see that a card would have maybe many swipes involved.

Like if I swipe at Harvard, I might also swipe it MIT or swipe it Park Street,

and so on.

We could see that a swipe can only happen at a single station at a time,

though.

Like if I swipe at Park Street, I'm only swiping at Park Street here,

and similarly, a swipe might only involve one, single card.

So if we think about these entities and how they relate,

we could also think about what kinds of columns you have inside of each entity.

In this case, I would argue we have something a bit like this.

We could say that a card has a ID for its primary key, in this case.

Similarly, this card makes a swipe, and this swipe has, itself, an ID,

as well as a type, some time that that swipe happened,

and an amount or a transaction that is involved.

So for example, let's say I swipe in at the Harvard Square Station.

That type of swipe might be to enter the station at some certain time.

Now associated with that swipe is also some amount

in dollars that happened to be subtracted from my card.

Like let's say the fare nowadays is $2.40, well,

that amount is subtracted from my card from this swipe.

Now, of course, I do all of this at a station, which has our same columns

from before, ID, name, and line.

So a similar idea now, but we're replacing riders with cards

and adding more information to these visits.

There are instead swipes that could be maybe entering the station,

exiting the station or just adding funds to my balance, for instance.

So let's see how we could improve our schema now

using some new SQL keywords to alter our tables and add some new ones, too.

I'll go back to my computer.

And let's see how we could try to alter the table we've already created.

We already have a riders table, a visits table and a stations table,

but we could learn some new statements, some new queries to actually

update and alter these tables as well.

The first one as we saw a little bit before

is this one called drop table, arguably the most dramatic thing

you can do to table, is just drop it, delete it like this.

So let's try just deleting the riders table from our database.

I'll go back to my--

go back to my database here.

I'll type sqlite mbta.db to pull up this database again.

And now if I type dot schema, I'll see I have a riders, stations, and visits

table.

But no longer-- I don't want to have the riders table.

I want to remove that.

So I'll say, drop table riders, semicolon.

Now I'll hit enter.

No more riders table.

If I type dot schema, that's gone for my database.

Well, what could I do now?

I've dropped the table, but I'd still need to maybe update

visits to instead be swipes.

I could probably leave stations as is.

But I want to update a table or alter its schema,

I can use some new commands as well.

I'll use this one called alter table.

Alter table looks a bit like this.

I can use the alter table statement here and give it some table name.

Like let's say alter table visits, and then inside this visits table,

I get to ask, what do I want to do?

Do I want to, let say, rename the table?

I could do that.

I could also decide to add a column, rename a column,

or drop a column altogether.

Let's try first looking at rename to.

I want to rename this visits table to a swipes table,

representing not just a visit to the station, but a swipe of a card.

So let's try this one here.

I'll go back to my computer and I'll go back to SQLite.

And I'll say, I no longer want visits to be called visits.

I ideally want visits to actually be called swipes.

So let me try this.

I'll say alter table, as we saw before, visits

rename to swipes, like this, semicolon.

Now I'll hit enter.

And now if I type dot schema, again, I see--

oops, kind of strange--

I'll hit dot schema again, I'll see swipes,

no longer called visits, but now called swipes.

What we saw-- I'd ideally like to add a type of the swipe,

maybe I'm entering the station, maybe I'm exiting.

Maybe I'm just adding funds or depositing some funds.

So let me try adding, let's say, a new column to swipes.

I'll say alter table, add column--

alter table, swipes, I'm going to name that table, swipes, like this.

And then let's add a column--

add a column called type.

And this will have the type affinity text.

I'll hit semicolon, enter.

And now if I type dot schema, I'll see--

well, I have a column called a ttpe.

So clearly, I made a typo here.

I had rider ID, station ID.

This new column down below called ttpe, I kind of want to fix this, right?

I don't want ttpe, I want type.

So probably good chance to use my rename column.

I'll come back here and I'll try that.

I'll instead do alter table--

alter table, swipes, and I will rename a column now.

I'll say rename the column, ttpe to type, spelled correctly now.

Hit semicolon, clear my terminal, type dot schema.

And now I see over here that type has been added to my table of swipes.

I see rider ID, station ID, and now a new column called type.

So through alter table, we go ahead and add new columns, rename them even,

or if I wanted to just drop the column altogether.

Let's say I add this column type and I change my mind,

I don't want it here anymore.

I go back to my computer and I could try dropping a particular column.

Let me try in this case, alter table, swipes.

And now let me drop column type, semicolon.

Hit enter.

And now if I type dot schema, I'm back to where I began.

So these are the commands we can use to alter our tables, to improve them

if you make a mistake during our first create table command,

or if you want to add more functionality later down the line.

So ideally, I could keep using alter table, add table, create table, drop

table, and so on.

But what I want to do here is just start from scratch.

Like I have stations and swipes and so on.

Why don't I just go back to my schema file and use that instead?

So what I'll do is I'll drop table for stations and--

oops, semicolon.

I will drop table for swipes now, semicolon.

I'll type dot schema, and now I'll see nothing in here.

I'll quit and I'll type code schema.sql.

Let me just start from scratch using this schema.sql.

So we no longer want to have riders, we only want to have cards.

So I could just rename this table here.

I'll call it cards.

Create table, cards.

Now cards don't have a name, they only have some unique ID in this case.

Now I'll leave stations just as it is.

This seems perfectly fine to me.

Stations have an ID, a name and a line with these constraints

applied to the name and line columns.

But now the visits table.

Well, the visits table is no longer a visit to the station per se,

it's more a swipe of the card at that station.

So let's now say visits becomes swipes.

And among these new columns to add are the following.

I want to have not just an ID for each swipe,

not an ID for rider ID and station ID and so on,

but also I want to have a type of swipe.

Am I entering the station, exiting, or depositing some funds?

So I'll say the type of this is going to be a new column,

and the data type, this whole will be text, enter, exit,

or deposit for funds.

Now let me try another column, two.

I'll include a date time.

A date time is like a timestamp.

What time did this swipe actually happen?

And I'll make this type numeric.

Numeric can store all kinds of dates and times for me in this table.

Now let me add one final column.

This one will be an amount.

I'll also use numeric for this kind of column here.

And I'll say that this column called amount

can store, in this case integers or real numbers, like floats and so on.

I'll probably decide on that when I actually add some data to this table.

So here we've updated our schema to represent that diagram we saw before.

I have cards, I have stations, and I have

swipes that have some type associated with them, enter, exit, deposit,

some date, time, and some amount that was charged to me

while I made this swipe.

Either I added some funds, in which case amount is positive,

or I subtracted some funds, entering exiting, in this case from the station.

All right.

So I have these tables now.

And now I want to probably apply some of those same column

constraints we saw before.

Like here, it's fine, but I also want to make sure I'm not adding some data

that I don't want to add to this table.

So I could go back to my old friends, these column constraints.

And we saw before, we had default and not null, unique and check.

We've used not null and unique, but we haven't check or default.

So let's start using more than just not null and unique

and also focus on check and default, what they can do for us here.

I'll go back to my schema.

And let me just make sure that I'm making all the columns that I

want to be required actually required.

I'll go into my swipes table and I'll say that type--

I mean, this should be required.

I should know whether I entered, exited, or deposited some funds.

So I'll say text not null.

Similarly, for the timestamp, the time this swipe happened,

I want that to be not null as well.

I must know what time I swiped the card.

And also, it makes sense for there to always be

some amount associated with this swipe.

Either I added some funds to my balance or I removed some funds overall.

So I'll make this not null as well.

Well, let's see what we could do here with default.

Default gives me some default value, some initial value

to add to this column if I don't specify what kind of value to add,

what be good for date time.

Date time here is, again, the timestamp.

The time I swiped this card to enter, let's say, Harvard station.

Well, if I want this to always have the current time,

I could use default. I could say the default

value for this column is this special value here,

CURRENT TIMESTAMP, in all caps.

CURRENT TIMESTAMP will show me the year, the month, the day, the hour,

the minute, the second, all in one value, and insert that into my table.

So as soon as I add new row, if I don't supply a time for this datetime column,

I'll instead get back the current time exactly as it is represented in SQLite.

Now what can I do further than this?

I could also try to add a-- my very own check.

Maybe I want to make sure that the amounts here are never equal to zero.

Like, nobody should ever be able to make a $0 transaction or $0 swipe.

They're always being charged some money or they're always

depositing some money, in this case.

So I could say, amount here has my very own check on this column.

And inside check, I can actually write my very own expression to check for.

I could say, for example, amount is not equal to zero.

Using those same operators we saw back in week zero,

this will ensure that any value inside amount will not be equal to zero.

Let's try also for type.

I mean, type can only have a few values.

We saw enter, exit, deposit some funds.

I could make sure that only those values are included inside my type column.

I could say check that type is in some list of values.

Going back to week one here, we talk about in.

I could say, maybe the type is in enter exit or is in deposit.

So now when I have this table called swipes,

I'm representing what I'm actually doing when I go to Harvard station.

I have here a visit for myself, my very own ID

here, which will update in second.

I have a station ID where I'm actually going to visit.

I have a type that I'm going to use to enter, exit, or deposit

some funds at this station.

I'm doing it at a certain time, and I have an amount

associated with this transaction.

Now, there's one thing to fix here, which

is that we're still talking about riders inside our swipes table.

So let's fix that here, too.

I'll go back to my computer, and let's try fixing this.

I have a rider ID inside of my swipes table, but no longer do I have riders,

I have cards.

So let me say that this is now a card ID, and down below in my foreign key,

I'll say that this card ID column references the ID column in cards.

And I think this should represent everything

I want to represent about swipes at the station.

So let me ask now what questions we have about this new schema

and the constraints we've applied.

AUDIENCE: I wonder, how could you delete or drop

the table of riders when you use ID as a foreign key?

I try to do that, but I got an error.

CARTER ZENKE: Yeah, so you're getting into some more advanced stuff here.

And suffice to say for now, my foreign key constraints

aren't actually being checked right now, but yours might be.

If you try to drop a table that actually has some data that's

referenced by a foreign key, SQLite will warn you, perhaps to tell you,

you can't do that because this ID is referenced from this table over there.

So in that case, best to delete the value that has that foreign key

and then proceed with dropping that table altogether.

Good question there.

Yeah, let's take one more.

AUDIENCE: How might this syntax send the other SQL languages, same as SQLite

languages, how much is syntax same?

CARTER ZENKE: Yeah, a good question.

So here we're using the SQLite database management system.

It is similar to but distinct in some ways

from others like MySQL or PostgreSQL.

I would say that most of what you're doing here

could also be ported to MySQL and PostgreSQL with a few exceptions

that you might need to treat on a case by case basis.

In fact, the developers of SQLite built things

so that it would be easy to port their database schemas to another schema

like MySQL or PostgreSQL as well.

But good question there.

All right, let's take one more question here.

AUDIENCE: Imagine if we put in the ID, we don't put any data type,

it's going to give us--

in this schema, it's going to give us a text or--

CARTER ZENKE: Yeah, great question.

So you're asking if I didn't tell SQLite what kind of type affinity

a column had, what type of affinity would it actually have?

A great question.

In this case, by default, SQLite gives the numeric type affinity,

where numeric can store integers or real values.

But if you give it like a string of text, like let's say red line,

it will store that for you, too, kind of non-intuitively, but it will.

But the default type affinity is numeric in this case,

if you don't otherwise specify.

Good question.

OK, let's come back then and let's focus on wrapping up on a final few pieces

here.

So we've seen some table constraints, which

we apply primary keys and foreign keys.

We saw column constraints, where we could make sure

that certain values were given to us through not null.

We could also make sure that the--

let's say the value is in some list of values,

they're using check or making sure that it's not some value,

also using check as well.

Default allows us to specify a given value for every new insertion of rows

here.

And this is actually pretty important to have.

Not just schemas that have column names and let's

say type affinities as well, but also constraints,

make sure the data we insert works well for us.

And there's actually a story behind this person

who's on the CharlieCard, this person who's on the subway.

His name is Charlie, and he's perhaps the most famous subway

rider in all of Boston.

Back in the 19-- let's say 1900s, the band

called The Kingston Trio wrote a song about this man named Charlie.

Charlie, supposedly, got on at the Kendall Square station where MIT is,

and he made for Jamaica Plain.

But once he got to Jamaica Plane, the conductor

asked him for one more nickel, and well, he didn't have that nickel.

So he got stuck on the train for years, and years, and years.

He couldn't get off of the subway.

So keep in mind, Charlie, when you're writing your own database constraints,

making sure that if you get on the train,

you're able to get off of it at some point, right?

Don't end up like Charlie in this case.

So with this in mind, you're able to design your very own database

schemas that keep not just certain columns involved,

but also type affinities for those columns, types--

those data types-- types the columns can store.

You're also able to apply constraints to those columns to make sure

that the data you're inserting is that you actually

want to have in that column.

Next time, we'll focus on actually adding data to our columns,

actually write data to a database file to insert,

update, and delete that data altogether.

So with that in mind, we'll see you next time.