All language subtitles for lecture2

[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.