Hey, what's up everybody? So for those of you who do not know me, my
name is Caleb, from Caleb the video maker, too. And kill crate comm so I did a a design
series A while ago is my first video series here on YouTube, about 25 videos long. And
then I went on to make a 67 part video series over MySQL. And I did a couple other ones,
I have over 100 databasing videos, and I wanted to, I wanted to try to tackle this, again,
to try to make you better explain things better, and also bring you to a new level of skill.
So what what level of difficulty is this course going to be? It's going to be for beginners.
So why do you want to use a series you most platforms, programs, applications rely on
a back end database. That's how we store information in this world, everything we do is powered
by a database. So it's important that you know how to properly implement database concepts
designs, to know how to properly structure data. So that way, you get the best optimized
database that will set you apart from the rest of the IT team or the rest of your classmates,
it will give you the skills needed to get that job or whatever you want to do with it.
Also, if you're just a business person, or you have your own project, and you need to
learn how to build a website, or you need a database to store all the information you're
using, this series will be helpful. Basically, database design is the fundamentals of databases,
you should know how to properly structure data, this series is not going to be very
specific on a specimen a certain database management system. So this isn't like MySQL
database design. This isn't like Oracle, this isn't SQL Server, this isn't whatever else
you use, this is going to be general database design. So then you can take these skills
and apply them to any of the databases that you are working on that are relational, which
I mean through this series, you will you'll really understand what that means you don't
get that. So the first part is going to be a lot of the concepts on the chalkboard, and
I'm not going to like write tons of scripts on here. So don't worry, it's not going to
be confusing to where you can't see the code on a screen. Because in reality, a lot of
database design is not done on a computer first, first, you have to think about it.
And then you kind of just draw your plans, whether it be in your head or on a document.
Or if you use a database design program. I use a chalkboard because I can easily draw
concepts, thoughts, way the way data is connected, and all that. So a lot of these databasing
videos, they're going to be on this chalkboard because that's all you really need. We're
not we're not programming a database, we're designing a database. And designing is art,
you draw it, you design it. So a lot of the concepts from this video will be useful no
matter what you will be using for your database. So database design. Before we talk about database
design, let's first talk about a database because we can't design a database if we don't
know what a database is, that doesn't even make sense. So what is a database? And that's
a really good question. I'm glad you asked. Well, I can actually tell you what a database
is. And that's what this video is about. So a database is something that stores data.
Alright, another vague term data. And I want you to guys, I want all of you to think about
data, very broadly, don't think too specific. Think of data as anything we can store. In
a database or anything we can write down. Anything that has a value can be anything.
data can be the list of every single customer of your store since 1995. Or it can be a list
of every single transaction in a online shopping center. Or it can be a list of every user
and their username. Or it can be a list of every single weather event. Since 1927 can
be pretty much anything. It doesn't have to do with specific people data is so broad. So database
is obviously broad too, because the database stores data. So yeah, just think of it like
this. Here we have a database of bases where we store data. Pretty simple. Alright, so now, that's pretty vague, and
I'm not sure if that was very helpful. So now let's break it down to where we see it
from a technical standpoint. Everything in this world, not everything, but almost everything
is ran from a back end database. It's what stores all of our information. So for example,
I like to use websites, very examples, because they're very clear. And they're very easy
to understand the concept of this. When you go to a website, and you sign up, you, you
give them a username, you give them a password, and you give them an email, all of that, when
you sign in the next time, it allows you to sign in, well, how did that website remember
what your username, email and password was? That's because all of this information that
you put in, when you registered, all of these values are given to a database. So here you
can say is our database. Sorry for my craptastic handwriting, but don't don't even worry about
it. This is what's said to be the front end. This is said to be the back end. That's because
we don't necessarily see what's going on back here, we can put in our email, our username
and our password, and they can get stored in this database. That way, when we come back
again, and we log in, we give them a username and a password, it takes that value, it checks
it with the database to allow you to get into the account. That is a perfect example of
a database. So the database can store that information. But it's not limited to only
registration, you can use this to store all of your records of sales in a store. And also
for that just goes on. Don't think of it as a limited thing, because there's so many things
you can do with the database. So many things. Next question you may be asking is how does
it differ from, like a spreadsheet, for example, so a spreadsheet, basically, if you don't really know much about
spreadsheets, it's basically where you have columns. And then we have rows, and we can
store information about like, we can enter the values for each column. spreadsheets are good, if that's what you need for your very
small company sees me. But if you want to get to any complexity, where you can, like,
imagine if we wanted to select, let's say this was a spreadsheet for all of my employees,
I own a huge business with 100 employees. And I want to select every single employee
who has missed less than three days and have been a an employee for at least three years,
and I want to give them a $100 bonus, well, that's something if I had a spreadsheet, I'd
have to manually go through that check every single value, that's not gonna work, because
time is money in business. But anyways, with a database, we can run queries to get those
kind of values to figure out which employees have been a member, a employee for three years
and volumous, three days or less. So that's how it differs from a spreadsheet, basically,
what you can do with it, if you have a spreadsheet with 10 employees, or like I'm sorry, with
10 values, and that's like you can figure out all of your information in a couple of
seconds, well, then you might not need a database, that's up to you. The other thing is with
with spreadsheets, it's it's a it's either like all data or no data. Let's say I just
this stored every single detail about every single employee that I had, and all I wanted
was their name and their address. Well, we can't really do that easily with the spreadsheet.
Unless we take all those columns and like copy and paste and all this extra garbage
or find some algorithm to do things fancy Lee, if you know what you're doing, you can
probably do it with a database, we can selectively choose which color. So we can say only these
two columns Do we want returned, and we can ignore the rest. On top of that, we can allow
different users to access different information. Back to the website example. We have Users
page, and then we had a database. When here you put your username, email and password
that's stored on a database. But that's not necessarily the only thing that's stored on
a database. A user might only get to access that but a database administrator might get
to access when they registered, the transactions they've made on your shopping website or what
they've watched what what they viewed, the comments they've made, how often they visit the web page, it's pretty much
limitless to what you want to do with the database. So we can see that two different
users, this person only gets to view some things at the database. This person can only
can view pretty much anything with a spreadsheet. It's kind of all or nothing. Do you have access
to the spreadsheet? Yes, I do. Well, here it is. If you don't have access, well, then
too bad, you don't get any of it. So that that's awesome. That's a security feature
as well, because you don't want this person to get all of the hidden information that's
stored within this database. So hopefully that makes things pretty darn clear. The next
video, I'll kind of be talking about how data is stored in a database. Because I didn't
I was going to get into that in this video, but I kind of ran out of time. But for the
sake of just your understanding, if you decide not to watch the second video, which you will
watch the second video for I relations, that's a term where we get the term relational database.
So this series is for what's known as a relational database. That is a specific kind of database. And that's
going to be most of the relation that's going to be most of the databases that you work
with. If you're working with database design. We're going to be talking about the relation,
very basic. Don't freak out, but it is math, we're not going to go super in depth with
math, if you want to know more about the math, I have blogs on my website about that. Caleb
curry.com, forward slash blocks, advertising, not blogs, advertising house advertising,
anyways, basically, a relation is just a connection between data. So let's just think of silly
example, if we have the two sets of numbers, two, four, and then the other set was six,
eight, and then we kind of combined these sets. So we've kind of like crossed here,
and then we cross down here. And where are these cross is our new set. So we have the
combined set of six, two, and then we have the combined set of six, four, and then we
have the combined set of eight, two, and then a combined set of a four. And like you're
probably wondering, like, what the heck does that have to do with database design? Well,
somewhat, we kind of do the same thing with database design relation in relational database
comes from this mathematical concept. But rather than combining sets of numbers, we're
combining attributes of real world things. So let's apply this to the database. First, you need to know two terms. The first one is an entity.
The second one is an attribute. So an entity is anything we store data about attribute
or the things that we store. So if an entity is a person, an attribute would be their name,
their username, their password, their hair color, their address, their phone number,
their relationship status, the person they're in a relationship with the orders that they've
made, or whatever, whatever it is, it's depends on what you need to store for that specific
application. So entity is what we store the data about. attribute is what we store. When
we apply this to this, this connection with the the the relation, we have the attribute
types and the attribute values. So let's say we have this person over here. And we'll name
him Caleb, because it's an awesome name. All right. And then we will also give him a username
Caleb curry with no spaces. And we'll give him a password. Pi 123. And then over here,
we have the set of attributes for a table we have name, username, and password. We can
make this connection of data so the name is Caleb the username Caleb curry passwords,
pi 123. That makes sense, although graphically It looks like a mess.
So we store these in tables, which is exactly what we were doing with the relations. So
we have up here in our columns we have we have the name and then we have the username
and then the password. Don't worry if you can't read my handwriting you get it. We store
this in a table. Right? So these are the things we need to connect with this
person over here. We named Caleb and I accidentally erased them, whatever. We'll just leave it at that. So that's Caleb.
And we want to store this information in this table, which is a graphical way to, to illustrate
how we're storing data in a database. So we take his name, Caleb, we take his username,
Caleb curry, we take his password, pi 123. We kind of get this, this table concept, it
almost looks like a spreadsheet that we talked about in the last video. Although it is slightly
different, and we'll be talking about that as time goes on. This guy is the entity that
we're storing information about. These are the attributes that we need to store about
this person, or any other person. So we enter specific values for each one of these attributes
to make the relation between the attributes, and the entity. Hopefully, that makes somewhat
sense. And if it doesn't, don't freak out this all kind of I mean, this is complex thought.
And you don't necessarily need to understand every single detail to design a database.
So I'm just trying to explain things. And we'll get through. These are the attributes
of this person, this entity, an entity is basically just anything we store data about
it's something can be a person in this case, but it can also be an order, it can be a customer
can be a client, it can be a employee, an employer, whatever it is, it doesn't even
have to be a person. So now, the relation idea, you kind of understand that we're just
making the connection between the attributes, and the entity, the value that the entity
attributes, we're entering that in the database. That's, that's kind of a weak explanation.
But honestly, it doesn't really matter that much for getting into basic database design.
So if you want to learn more about that, just look up relational database design, you'll
figure it out. So now, we have the entity over here. And then we have the attributes.
So in table terms, you probably know, rows and columns, you might not know specifically
what they are. And I'm going to tell you this right here is a rough. You see that might
be run out of space here. I think we're good. Yeah, we're good. So this, all of these values
are a row. So a row is all of the attribute values for a specific entity. So this row
would be easier just to go like this, this row, points to that entity, that person. Pretty
simple. A column are all of the values for a specific attribute type. So the attribute
type is username, we can have another person Billy Joe, and his name, his password can
be within you, and his name can be bill. This is another person in our table. So this is
talking about a different person, we have this person over here. pretty chill, or this
row is talking about this entity. The username is a column where we have all of the attribute
types username, So Caleb curry, Billy Joe, and whatever more if we put more values into
the table. Okay, so it's again, a little messy, so I'm just going to clean it up a bit. Erase. So just to summarize a bit, we have very first
thing we have an entity type. That's that's the word you want to know entity type, which
talks about entities I'll just I'll just tell you what an entity type is in just a sec,
entity type. And then we have the attribute type. So basically, the type means it's a
category. So the person was a specific entity, the case the guys named Caleb, that was an
entity, it was the person then we also had the entity, Joe or Billy or whatever his name
was. Those are two separate people to separate entities, although you could tell that they
were somewhat similar. They're both in the same table, they were both talking about the
username, password, and so forth. So the entity type is user, a specific entity would be Caleb,
or bill, or Jamie, or Jake, or whoever it is. So the entity type is basically the category
of the entities that we're storing. The category is a user and all the all the entities within
it should be a user. So my camera stopped recording by itself for some reason, which
is super annoying. But anyways, our entity type is the category of entities such as user,
every single entity should be a user. Caleb was a user, Billy was a user, we could have
Jimmy john, we could have got Katherine, we could have Caitlin, we could have Cassandra,
we could have, you get the point. The attribute type is basically the categories of attributes.
So we had the username, we had the name, we had the password. These are attribute types,
because we don't actually have a specific value. If like, no one's name, his name. When
we actually give these specific values, they no longer become types, and they become specific
examples. So this guy over here, here's our entity, we can store. So basically, here's
our entity, we can store attributes about this entity, the username can be Caleb curry. The name can be Caleb. And the password can
be pi 123. So these are specific attributes about that
guy. Now all these are all like complex terms, and blah, blah, blah, blah, who really cares?
What really matters is can you put this into a table for people to understand who really
cares if you know the terms, but I mean, I'm sure your professor care. So making that anyways. Table, the columns or the attributes, so we could
have, for example, an ID, a password, a user name, and email, we could keep going address,
phone number, and whatever else. And then a row is where we give a value for every single
one of these attributes, all talking about one entity. So like this guy, this guy right
here? Well, his ID, for example, 72, his username is Caleb curry, his email is cool, guy@hotmail.com blah, blah, blah, blah, blah, all of this
row talks about this guy. So basically, just think attributes are columns. entities are
individual rows. Sweet, then this whole thing right here. This is the table, and that is
the entity type. So the entity type would be user because every single row within this
table should be a user. Oh, another thing I would like to mention real quick, another
name in mathematics for a row is a tupple, or tuple. I think it's terrible.
I don't really know. But that is another term you'll hear. So yeah, tupple is a row. Thank
you. See ya, sorry for like yelling and getting all intense, but fun. Alright. This will be about database management systems.
And because this series is over relational database design, it will be specifically relational
database management systems. And we'll talk about the difference. So first, let's just
talk about in general, what is a database management system. So this is often shortened
to B dB, and S for database management system. And relational database management system
is the same thing except it starts with an R. So the our DBMS, so I'm just gonna write
that because it's easier. Alright, so where we left off in the last video, we learned?
Well, actually, like both of the videos, we learned that a database can be used to store
a ton of information. So we have this database, just think of it like a barrel with a ton
of information just piled in there. So like usernames, and like passwords and junk, and
transactions. And all this stuff is listed in here. And as you saw on the last video,
it looked very similar to a spreadsheet. So what makes it different, we have all this
data. Now what do we do? What do we do? Just look at it? No, don't be knew, obviously we
do cool things with it. Obviously, I don't even know what that was about. So what we
can do is we can run what's known as a query. So a query, just sort of like searching our
data, doing something with it, and giving us cool results. So a database management
system, uses our data in manages it, allowing us to view it in a human friendly way, and
do cool things such as search for values, change the appearance of data, changed the
way data is stored, and so forth. So for example, we have 6 billion users, okay, just for just
for an example. And we want to delete anyone who hasn't been active in a year. Well, without
a database management system, all we have is a bunch of data. And we'd still have to
go manually do all that and delete all that. So the database management system, is what
allows us to easily run a query, alright, select everyone who hasn't been online in
a year. And then we can delete everyone who basically, basically just delete everyone
who hasn't been x active in a year. Basically, a relational database management system is
just a sub category of a database management system, a specific kind that is designed to
work with relational databases. So yeah, not much of a difference there. So just some of
the things that these database management systems can do. The first one is obviously
run fancy queries to give us specific results, such as give us all of the transactions that
were processed before 2011, June 10. That's something fancy we can do with a database
management system. It also allows us to change the way our data is presented. That's something
known as a view mechanism. So basically, if you mechanism allows us to
change the surface appearance of our data. So when we store our values in our database,
we might have it to where it's, we're going to store an ID, we're going to store a username,
I'm just going to shorten these up to make it easy. We're going to store username, we're
going to store a password, we're going to store an email. And then we're going to store
street address, state, city, so forth. But let's just say this is how it is for now.
Here is the actual table structure for the user table. So here are all of our attributes
for a user. Get rid of this. So it's clear? Well, with a view mechanism, we're able to
get different views of this data at the surface level. So for example, if we had two people
accessing this data, one, what's his name him, Jim, and the other one? Let's name her,
Jane, ie, Jamie, Jim and Jamie. Let's say Jim, he doesn't really care about the emails, because
all he's working on is to find out information about usernames and passwords. So what he
might do is he might create a new view of just the username and the password. Once again,
I'm just shortening these, just to make things simple for illustration, I probably wouldn't
put them in the database like that, unless you wanted to, but whatever. So this is a specific view, we can select the username
and the password. But the internal structure of the data has not
changed. This is still how it's stored within the database. Jamie, on the other hand, she
cares about the ID and the email. So she creates a new view. Just like that. So this, these are both called views. So the view mechanism
allows us to create different views. This can be used for database administrators, as
well as for other applications, such as when we have a web page, we may want. We may only
want users to be able to access their username, password, and email. But stuff such as the
date they registered, that might be all private on the database. So then we can just create
a view giving them the information that they have access to and that is a layer of security.
So basically, if we have a huge database for a business, well, the database admin administrator
is going to have access to it all. The president of the company will probably have access to
it all. But the people who are advertisers, they probably only need to know information
about advertising, the the people who monitor comments on your website, they are not going
to need access to the sales and the transactions, they're going to need access to the the table
for comments. That's a security feature that a view mechanism allows us to do by giving
different views. So not every single person who uses the database has the privilege of
creating a new view, just because you can access the database does not mean you can
do things such as update data, change the way it's structured, and so forth, that is
probably something only the administrator or the owner of the business is going to be
able to do. That means we can allow users on websites, they can only ask, they can only
access their specific username and password, they can't access everybody's username and
password only theirs. But if this guy, Jim, who is hired at the company, he can access
the username and password of every single user. That's a security difference difference
between the two people, not allowing the user of the website to get too much information.
And at the same time, not limiting Jim too few too little information. So that is a really
good security feature. So what else a relational database management system allows
us to do transactions, which we'll be talking about as the time comes, basically a transaction
is what it sounds like, it's when we do something with the data that for the technical terms,
basically, it is either complete all the way or it doesn't work at all, if we have a multiple
step thing, such as transferring money to an account, deducting that much money from
the other person's account, so forth, updating the data boom saved. If at any time the power
goes out, and the server crashes and it doesn't complete, well, then the transaction is canceled,
nothing is saved. That's something you can do with a relational database management system.
So that's pretty much the basics of relational database management system, just in case you're
not really sure, like the terms and stuff, examples of relational database management
systems or just database management systems. MySQL is one. It allows us to build and run
a database. SQL Server is basically like the Microsoft, if you like Microsoft, it's run
on the server, and then you access it. So you have Microsoft server, we have Oracle
database we have, I don't even know how to pronounce that I post gray SQL or something,
and so forth, the list goes on. But basically all of the big terms that you hear in databasing,
they're referring to the relational database management system, or the database management
system, if it's not relational database, don't really don't really think of the database
and the relational database as two separate things. They are what they're used as one.
But if you do want to be like conceptual, well things, we have the database, which stores
the information, and then the relational database management system, which allows us to manipulate
that data. When we have something such as MySQL. We don't really have a difference between
these two. And everything's just kind of like, okay, we use MySQL to create the data. It's
stored. My SQL is kind of like all we do the actual files, they're stored on hard drives
on a server or your home computer wherever you're running your database from. And the
relational database management takes the data on the hard drive and puts that into presentable
tables for people to like the administrator to view because if we have like a hard drive
disk, let's just say this is a hard drive disk. And some of the data is going to be
stored like right here. Other parts of data that's going to be stored like right here,
and some right here. Well, if this is a table the relational database management system
is going Want to be able to take that and put that in the appropriate location to make
it presentable, so don't really think of it like, Oh, we have a database, and then a relational
database management system, it's kind of think of them as one and all in themselves. So with
stuff like MySQL. The other thing, finally, I know, I'm kind of running long on this video.
The other thing a relational database management system is going to do is it's going to create
consistency. In bigger piece of chalk here, consistently, it's going to make consistency
behind for the front end. So on a website, we could have someone put their full name
in one box, and that can be stored on a database as two separate columns. first name last name.
Well, let's say if we updated this, and we decided to make it one column for some reason,
which I don't recommend, well, the front end didn't change, because the only thing that
changed is the back end. With a database, we can have consistency on our front end to
make our users happy, never having to worry about things changing. On the back end, the
things can change. For example, the way we store dates or our time zones or our character
sets, well, that's generally, that's not going to affect the front end, unless the data on
here is directly affects the front end. For example, if this is a username column, and
we delete the username column, well, that's going to cause issues, because it's not a
column on the database. But basically, when we have a database, the front end is generally
consistent, as well as we have server side scripting languages, such as PHP, or whatever
else. Well, that is going to hide our database and even even step farther. So basically,
rather than saying, oh, there's no username column, it'll say something like error, try
again, later, something very vague, so people don't understand like, Oh, crap, I can pack
this guy because I know his database structure, I can figure out how to mess up his website
or his business or whatever. So yeah, that was a server side scripting language. I'm
not gonna write that out, because it's huge. Yeah, that is the basics of relational database
management system. Hopefully, that was helpful. In this video, we'll be talking about SQL. So
s q, l. Alright, so what is SQL? SQL is a programming language used to communicate to
a database. Basically, we speak English databases do not so SQL is kind of like the mediator
between computer database and human English, think of it like that. It's almost English,
it's very simple. And in this series, we're not actually going to be programming any databases,
although we will be talking about general SQL concepts. So it's important that you know
about SQL. So SQL is not like a specific relational database management system. programming language,
it is a general language used for every single relational database management system. From
vendor to vendor, such as MySQL to SQL server or something else, it might vary a little bit, but the general concepts of SQL stay the same.
So this is this is going to be a general concept that you should know. So SQL first is used
to define the database structure. And then it manipulates the data within. So basically,
we can kind of think of SQL as two categories here. One, it defines the structure and then
to manipulates by that it means we insert data, and then we can search it or delete
it or update it. These are the two subcategories of SQL. We actually for some reason, name
these their own languages. So this is data Definition Language for DDL and this is data
manipulation language, or DML. Some people may say there's more categories,
but I mean this is pretty much the domain to so think of it like this. I'm just going
to erase this here. Now that we have this. We have DDL DML when we have like a table, for example,
I'm just gonna write this down here. And we have the columns. So we have like ID user
name, password. Well, that's data Definition Language. data manipulation language is what
we would use to insert a new value, such as the ID of 72, the username of Caleb curry,
the password of pi 123. That's data manipulation, language. So when you think of a table, and
if, obviously, if you have a big database, you'd have multiple tables. Well, all of this
structure and the connection between tables, that's all data Definition Language. So it
gets all of the main structure of the database. And then the, the data within
the database is data manipulation language. So those are the two main categories. Just I just want to talk about some of the
features of both of these languages. So basically, when we define tables, we have a create statement.
So basically, all SQL I write in all capital letters. That's just a pattern that I've chosen.
So create is an example of an SQL command used to create tables. And we can create a
database this way. That is data Definition Language, because we're defining the structure
of our database, a command such as update, or, yeah, I guess, update if we're updating
the actual values within columns. Well, this is manipulation. language, because we're not
changing the structure, the columns stays the same, just a specific attribute of an
entity changes. we're updating Caleb Curry's username to Caleb curry 123. Well, that's
a data manipulation language. Another thing that SQL can do is what's known as a join.
And joins are very important, very important with a relational database management system.
Because with a relational database management system, we break things up into separate tables,
for simplicity sake, for one, sorry, I was I added the scene there, I think I was whatever.
Rather than having a spreadsheet with like tons of information, we break it off by entity.
So here's a table about the user, here's the table about the orders, here's a table about
the customers, then we can join those values to get a new, larger table, a new view, that
looks like a huge organized table. I mean, basically, it looks, it doesn't look like
we broke it up into multiple tables. So if we have a table here, and then we have a table
here. And let's just say we have, this is a sale table. And this is a user table. Within
the sale table, we have a sale ID, we have a user ID the person who bought the stuff.
And then we have the objects that they bought. And I mean, this is this this silly example
just for you to understand the concept of a join. And then within the users table, we
have the the name, the user ID, and so forth, we can do a join here, we could take the objects
that they bought, the person the objects that the person bought, as well as the name of
the person who bought it, connected by the user ID, which will we'll talk about this
more, then we can get a new view, which has the name and the items that that name, but
it's all talking about the same entity, this user. So a naming convention is just a pattern
that people do or that you do to keep things consistent. So not only will this naming convention
help keep my database consistent across different tables and columns. But this naming convention
is not only mine, other people use this naming convention. That means when I go to a different
person to review my database, they already understand what's going on. Now, obviously,
there's multiple different naming conventions, there's not one way to do everything, and
it's not required. This is just how I decided to do it. And this is because most people
who use my SQL, which is what I learned first use this, but for different relational database
management systems, you may have different systems of naming things. So I'm just going
to get Explain what I'm going to do. Anytime I write SQL, I put it in all capital letters.
So select is an example. Fortunately, we're not going to be doing much SQL. So I'm not
going to have to write in all capitals on a chalkboard because it's, it's quite complicated.
But when we're talking about anything I named myself, I use all lowercase letters, I just
use all lowercase letters, because I just think it makes it more simple. So when we
have a table, such as a user table, I don't capitalize the
EU. And then when I have an ID, I just put user ID or just ID whatever, but I don't capitalize,
Id I just keep it lowercase. And for spaces, I just use an underscore, so I don't use any
spaces. So basically, that's my naming convention that I use, and yours will vary. And we haven't
really got to this yet. So you might not understand this. But if I have a foreign key, basically,
if I have a copy a connection over here, to where we have the same column over here, I
just make sure their name both the same thing. So I know what they're talking about. So yeah, I, some people will do things such as put
like user table, or they capitalized certain words. And if you want to look at other naming
conventions for your specific relational database management system, go right ahead. In fact,
I'd encourage it. But just so you understand what I'm doing here, I will be doing another
thing, when we're talking about stuff in the real world. Sometimes I don't
necessarily use that naming convention. Because if I'm writing everything I need to store
about a user, well, I might just put, we need to store the user's address, I'm not going
to put the underscore users underscore address, I might just put the user's address with space
in English. And then when I convert when I start making this into a database, structure,
schema, whatever you want to call it, then I'm going to convert that to just address
lowercase. We're going to be talking about Okay, what is database design? And when we
talk about what's a database, we never really talked about database design. Why do we have
to design a database? What What's so special about it, it needs to design it's not like
we're building a house or something? Well, in a way, it's it's kind of like that it's
we're building schematics to build the best database. How do you measure whether a database
is good or bad? Well, it has to do with data integrity, which we'll talk about that in
more depth in an upcoming video. So data integrity is basically where all of
your data is correct, it's up to date, there's no disconnected data. For example, if we have
two tables. And for some reason, these tables are connected in some way. Well, if if for
some reason this link breaks, well, then when this table updates, this table gets left behind,
and then the data becomes incorrect. That's a data integrity problem, because the data
is not managed properly. So with a good designed database, we prevent data integrity issues.
So all of our data is up to date. We don't have, we don't have repeating data, we don't
have old data that should not be in there. Which, if for example, if you have a database,
you can store lots of information from like long ago, and that's fine. But you don't want
data that has recently changed, and it hasn't changed in the update. So you want the database
to always be up to date. Unless for some reason you're storing like an archives, let's say
you have a store, and you want to store an archive of every single order you've ever
made or sold or whatever, well, then you can store all that in a database. And that doesn't
really need updated because it's an archive. But if you have, if you have a customer in
your database, and the address is out of date and doesn't update, well then you have a problem.
Another example is if the person up has four so if your database is designed badly and
you have repeating data, well then one example of the data can update and the other ones
left behind. So now you have like two addresses for one person, which should just be one address.
That's another example of a data integrity problem. So we'll be getting into like specific
cases of data integrity problems as we go on. But when we talk about data design, database
design has Whole, people usually break it up into three sections, two or three, it just
kind of depends on, like, what people mean it, it varies from person to person. So okay,
first, we have the conceptual. And then we have the logical. And then we
have the physical. Alright, these are all known as schemas of schemas, whatever you
however you want to pronounce it, but that's this. Alright, so we have the conceptual schema,
the logical schema and the physical schema. A schema or us just think of a schematic,
like when you're building something, it just tells you the way the data is structured.
The conceptual, this up here is when we're just kind of thinking about things, how things
are related. We don't think of limitations as an Oh, we're not going to be able to do
that, because we only have this much availability or something. This is kind of like unlimited
brainstorming phase. And as we move down, this is more specific to where we say, Okay,
this is how we're going to program our database. So up here, this up here is more general.
This down here is more specific. Once you get down here, you have to start worrying
about, alright, what kind of relational database management system Am I going to use. But when
you're up here, you can just kind of think of general concepts how data is related. So
the farther you go down to the physical, the more specific things get. I mean, that's pretty
obvious. So rather, rather than thinking of it like three steps, I kind of think of it
as a continuum, we are taking our general ideas and moving them to specific implementations.
Hopefully that makes sense. But basically, when we talk about these individually, the
conceptual just talks about how data is related. Because in a relational database management
system, we have relationships between tables, if you think of, we have a users table. And
then we have a sales table. And these users can go to this website, and buy products.
That's our example. Here. It can be anything but this is just an example. Well, every single
sale has to be bought by someone, specifically one user. So that means there's a connection
between the user and the sale. So we have this connection here. That is part of the
conceptual design, we say, this table, the sales depends on the user. So that's, that's
because with without a user, we can't have a sale, because there's no one to buy the
stuff. And a user doesn't doesn't necessarily have to have a sale. So it's dependent this
way. That would be the parent, that would be the child. Hopefully that makes sense.
When we get to the logical, we start planning out our table structure. So we have all these relationships between
data, well, then we can realize all right, we have specifically a user's table, and we
have X number of columns, the username, the password, the ID, the address, the phone number,
the credit card information, if that's something you need to store, anything like that. Then
we have another table. And is the the sales so we have the sales table. So we have user,
use a table, and then we have the sale table. And then this, this is connected in some way.
So we have user can have multiple sales, and a sale can only have one user. That's a logical
database design, because we're actually structuring our columns are data types, our relationship
for drawing those out, so we have this logical drawing or schema, the physical, that is when
we start actually implementing that into a database. So when we're talking about just
designing and we're not actually creating the database, the physical design would be
figuring out what relational database management system will work best. What are our table
types? What what server Are we going to store this on? How are people going to access this?
Is it going to be over the internet, it's going to be over computers programs. So usually
database will be on a server, and this will be accessed by multiple different people.
That's because the database is like the core of all data. For example, if we only had this
one person using the data, well, then we wouldn't need this huge database, we can just store
that information on their computer, because they're the only one on there. It doesn't
even make sense. So typically, everything's kind of accessed on a server. So the physical
would be okay, what kind of server we're using? How are people going to access our data? Is
it going to use a webform? Do Is that something we need to program? And then when you physically
implement that is that that's when you actually program the database, build the webform, or
whatever you are doing, installing the server and testing, building the users who who's
going to access it, the views? What are the different views going to be? What data is
going to be returned, and so forth. This is also when you really focus on security. Because
logically, when we're in the logical phase, we're still kind of thinking, Okay, no one's
going to try and break it, no, everyone's going to use the database correctly. But in
the real world, that doesn't work out that way, people, either they try to break things,
or they just don't know what they're doing. Or there's errors or there's incorrect data.
Well, that's when somewhere along this continuum right here is when we need to start worrying
about security. We don't want people breaking into our database. And we don't want our database
to fall apart and have data integrity issues. So hopefully, that clears some things up.
So the database design, something's up real quick. database design, is using skills that you know, to build a
database that is not going to have data integrity issues, update anomalies, we'll talk about
those two, but that's basically when if for some reason you're storing two of the same
data, like if my address is on in the database twice, well, that's a problem. Because when
I update my address, only one may update, for example. And now the address says I live
in two different places. That's, that's wrong. So a database design is a method to separate
information over multiple tables, rather than having one huge table like this, where I store
my address, we store our sales, we store our, our customers, we store our our favorite video
games we store our relationships with. Yeah, you get. So basically, this is a bad design, because it's all in one table.
And when when I have to update it, so we have, let's just say this is a row, right? And I
have the columns are ID, and username. And then we have us again need to be a little
bigger. And then we have username, we have address, let's go phone number. It's easier
phone. And then we have favorite color. When we store like this, well if we need to put
a new favorite color, we have the ID seven. That's me, we have the username Caleb curry,
we have the phone number one 800. Favorite color cammo. And then if I want to put a new
favorite color in, well, we can't because we don't want to we already put one in there.
So we need to create a new route, say seven Caleb curry one 100 dot dot dot. And then
favorite color. Green. Right? Well, now we have all those duplicate data,
we have this in here twice, do this in here twice, we have this in here twice. And we
have, we have to have two rows for favorite color. So your you have to find a solution
to this problem. But now if I decided to update my phone number because
I got a new phone number, well now I'll update it and I'll change it to 1234567 whatever
it is, well now I updated my phone number but for some reason, we have a data integrity
issue, because my phone number is in here, two different ways that that's a bad database
design. And in this series, we're going to be talking about fixing problems like that.
And the best way to do this would be to like separate it into multiple tables. Plus, you
probably don't need to store your favorite color. Unless you're running some kind of
like art website or build a business, so it's possible. But we can do the same thing with,
like orders how many orders if I had to put my orders in that table, which is another
bad idea, we have the same data integrity issues. So this series is going to talk you
through getting rid of those data integrity issues, so you have the best database design,
for best performance. We're going to be talking about data integrity. Alright, so what is
data integrity, we talked about it a little bit in the last video, but this video is going
to be going over in more depth. data integrity is just having correct data in your database.
So when you're when we're databasing, we don't want repeating values, we don't want incorrect
values. And we don't want broken relationships between tables. For example, in the last video,
I gave the example, you have a table here. And this is the user table. This is weird,
right? And like this, and then we have a table over here. And that is the sale table, which
is where you sell products. For example, this is just an example. You can you can implement
other examples. But this is just to illustrate my point. A sale is when a user buys product,
that means a sale needs a user as the buyer. Otherwise there's no sale, we can't have a
sale, if no one bought it. Does that make sense? That means the sale is dependent on
the user. And we have a relationship the user buys stuff, stuff is bought by a user, this
is a known as a Can you see this is known as a relay ship on ship. Now, do keep in mind
that the term relational database does not come from the word relationship. It comes
from relations, which is the mathematical connection of sets, which we talked about
that in the earlier video. But just don't get that confused. But relational database
does have relationships between tables. So this if for some reason this was broken. And
now we have a sale that was bought by a user or no user. So for example, let's say this
sale was bought by user with the ID of seven, okay. And then we have a user of ID of seven.
So there's a connection, the sale was bought by the user by ID of seven, which points back
to the user. Make sense? That's data integrity. But if this relationship is broken, and it
says the sale was bought by the user with the ID of seven, and then this person is removed
from the database. Well, now this sale points to a table or a user that doesn't exist. And
that might be that might be practical, in some sense to say that sale was bought by
that user one he did exist. But for things that need to update consistently, we don't
want that to happen. So that's example of integrity issues. So you want to keep that
relationship to say, Okay, well, we got rid of the user, we need to update the sales to
say, well, either the person is deleted, or also do something with the sales. So yeah,
sorry, if sounds a little confusing. Okay, so the three main types of data integrity.
First one is entity integrity. We talked about entities in like one of the first few videos,
an entity is anything we store data about. So we had the user to begin with. This is
an entity. What it means by entity integrity is basically
unique entities. When we have a user, we often give them IDs This is known as a key. So an
ID is used to enforce uniqueness among the entities. Like for example, if we had a table
for users, and we store the the name of the person and
then we store their address. Now let's just go to phone number. Because when we talk about
addresses, it's best to break it up into like street state, and I don't want to go through
all that. So we could potentially have a person with a repeating name. So let's To say his
name's Caleb, and his phone number is one 800. You wish? All right? Well, if we had another person,
if they both lived in the same house, for example, and this was their house phone, well,
sometimes people name their children the same name as them. So now we have two people with
the same phone number. And you look at this and say, Okay, well, either either we have
two people with the same phone number, or we have two rows talking about the same person.
And we don't know, because we don't have proper data integrity. So a way to solve this is
to either add more columns to where the row would be unique, such as a social security
number, or we could add an ID on. And this would be like six, and seven. So
now we're talking about two separate entities, this is talking about this guy, because his
ID is seven. This one is talking about this guy. Because this ID is six, they're two separate
entities, that's entity integrity, having uniqueness among your entities, we have uniqueness
with the rows, and we also have only one user table, we're not going to have another user
table, because that information should go in the original user table. Onto referential
integrity. This is the one we talked about earlier, is when we reference the ID of a
table and another table. If we had a comments table on the website, right, and we say who
the comment is by, we have a user ID, which points back to this ID over here. That's because
the comment has to be posted by a user. Otherwise, we have a comment that didn't have someone
who posted it, which doesn't make sense in the practical world. So we always have to
have referential integrity, which basically says, Every time there's a comment, there
needs to be a person who posted that comment. And that connection needs to remain it needs
to stay. If for some reason this is disconnected. And then this user is removed for this table
was changed, well, then this could change. I mean, this stays the same. And then there's
a disconnection to where this is changed. And this is old, outdated data, where either
the user no longer exists. And the comment still says he does, or something else like
that. That's referential integrity. And that'll make more sense once we start designing and
stuff. But yeah, domain integrity, that brings up a new word domain. domain is basically
just the acceptable values for a column for say, it's what we are storing the range of
what we are storing within a database. So when we're talking about tables, we have columns.
So these columns up here, let's say we have a phone number. Well, that means we should
have a certain number of digits, we have the area code, and then the three digits and then
the four digits. And that's at least us in my change other places, but that is 10 digits.
So we know that the phone number should be 10 digits, and it should be numbers. So if
for some reason I put the word cake in the phone number column, Well, for one, this is
going to cause an error if we have domain integrity, because it's not a 10 digit number.
It's just a word. That's not the proper domain. It's not a digit is not numbers, and it's
not tenable. So we know that's going to cause an error. So that's the basics of data integrity.
When we don't have data integrity, we have errors. When we have data integrity, we do
something to correct those errors, such as saying, all these all need to be numbers.
That's how we implement data integrity by setting database rules and how you do that
with the database. Well, that kind of varies upon the relational database management system
that you use. Some relational database management systems allow for general rules, saying okay,
this has to be a range of this numbers. Some are more vague where they, where they only
allow you to implement data types, data types would be like, integer 10 digits. So when
we talk about practically implementing data integrity, different relational database management
systems have different things you can do almost every single relational database system that I have, I'm
pretty sure all of them, but who knows. They allow for data types. So a data type
is pretty, pretty self explanatory. It's the type of data. So in database, we classify things as generally,
integer, or text, or date. So we either have numbers, text, or dates, that's like three
general categories, which we'll be talking about that more in a future video. But the
data type can put limits on what we're allowed to store. For example, we can say, okay, we
only want a character column with the maximum of 20 characters. That means we can put a
string in the column that says, I like p 1-234-567-8910 1112 1314 1516 17, that'll fit. But if I wanted to change this pizza to lasagna,
well, then I just added a couple extra characters that might cut it off, I'm not sure 1-234-567-8910
1112 1314 1516 1718 1920. So that's the maximum we could put in that data. It's like, that's
an example of implementing that data integrity for the characters. So this, this will be for the domain integrity,
we can also have referential integrity with what's known as foreign key constraints, which
allows us to connect to tables, because when we drop tables, we could say that they're
connected in some way. But how do we enforce that in a database, we use foreign key constraints.
For example, we can say if this is the parent, let's say this is the users. And this is the
comments. The user places a comment, so this is the parent This is the child, because the
child can't exist without the parent, you can't have a comment without a commenter.
Well, using foreign key constraints, we can say, Alright, if this user gets removed, also
remove all of the comments by this user. That's an example of foreign key constraint. As for
the non repeating data, that is done with just designing your database in the best way.
So yeah, I know that video was kind of jump back and forth from concept to concept and
maybe a little confusing, but I'm sure you will get it as time goes on. I just want to
do a little bit of review, as well as go over the vocabulary that we've learned. So if you
don't really want to know, we're going to go over some new ones too. But if you don't
want to review, then feel free to skip this video in the next one. So this video will
be about data and database design terms. So the very first term we learned was data. Data
is pretty much anything we store in a database, you pretty much know what that is sounds pretty
good database. That is what we store our data in. And then we have a specific kind of database,
which is a relational database. And what's special about a relational database
is that it stores things in tables. We have a database management system, or database
managed DBMS for short. That is how we manage a database using code. That's how we control
our database. We have a specific kind of database management system called a relational database
management system, which is just used to control our tables and the values within our tables
of a relational database. Pretty Simple. Now we're going to introduce a new word to you,
which is no or no, however you want to pronounce it. I've been told I pronounce it wrong, but
I don't really care. Anyways, this is when someone does not enter a value within a column
on a table. So if we have a table, and we have something such as fax number, well, not everyone is going to have a fax number.
So when we have a guy will say we have an ID here, we have an AI guy with the ID of
seven. And we don't have a fax number, this emptiness is said to be no. So basically,
you'll hear empty value, although, by definition, No means no value. So it's kind of a bad definition.
But who cares? No, basically means there is no data in that specific field. And we'll
be getting into more terms like that in a second. So that is what normal means. That's
one you will need to know. Another one is an anomaly, or anomalies. So, anomalies are basically errors within
our data in our data integrity. So when we have an anomaly, it's something that goes
away from what we expect or from the normal. When we update something, for example, and
instead of updating one column, it updates 10. And it wasn't supposed to. That's an anomaly.
So yeah, you'll need to know that as well. The other one is integrity. We implement database
integrity, to protect against anomalies. So we went over three different kinds of integrity.
We went over entity integrity, over referential and we went over domain. Real quick what these
are, entity integrity is saying uniqueness among the table, referential keeps the connections,
the keys, foreign keys, primary keys, they keep them connected across multiple tables.
So we could say these two tables have some kind of connection. And then domain is basically
saying a column within a table has all of the expected values. So we have a phone number,
we would expect numbers, not text or not a date, that is domain integrity. And the word domain is basically the range
of values that are acceptable to store within a column. So these are our data, vocabulary
that you should know. Now we're going to move on into the design term. So designing is what
we're here for. So this is fun. Very first thing you need to know is an entity.
Which is anything we store data about a user a, a mortgage, a transaction, a credit card
information, anything the comments a comment, they're all entities, that's what we're storing.
data about, then we have attributes. attributes are the things we store about the entity a
comment, a, an attribute of a comment would be the date that it was made the link, how
many characters is it? who posted, that's an example of an attribute a relation. We're talking about mathematics, it's a connection between two
sets of data. When we're talking about databasing. Put very simply, it's just a table. So a relation
is another name for a table where we have data within our database. So we could have
a structure of different tables. So these can be connected. Just I don't know however you want to connect them. This can be our
database, where each one of these different sets of different sets of information or data
is known as a relation is a table. next term is a tupple however you want to
pass that to winging it here. That's another thing for a row. It's a, it's all of the attributes
about a specific entity. So if we have this guy, we can say, his name is Caleb, his phone
number is blah, blah, blah, blah, blah, his address, his age, his or his birthday would
probably be better how long he's been a member of this website, or whatever it is, this all
of these values about this entity. That's what that is. That would also be a row on
a table, because we physically represent relations with a table. So when we have a table, we
have the columns, so we have name, phone number, address, social security number. And then
when we enter all of those, for a specific person has the ID of seven, the name Caleb,
the phone number, blah, blah, blah, the address, blah, blah, blah, that is known as a tupple,
or a row. Another term is table, which we just talked about that which is just a physical
represent representation of a relation. Tables are what we store in a database to organize
a my like cutting my head off there. To organize our data. Within a table, we have rows and
we have columns. The rows would be a specific individual entry
within our table. So like that guy we just drew all of his values would be his row, the
columns are that specific, is a specific attribute of that person, such as a name. This is the
column where we would put the value, Caleb. All right. Now we also have another three,
three more terms that we haven't learned yet, which you will possibly hear. They're kind
of older terms, but they they're just talking about the same thing. You may hear the terms, file, record. And then fields. So what are these talking
about? Well file is just another name for a table, a record is another name for a row,
and a field is another term for a call. So other than that we have the words is value.
A value is the information that we put in to a specific column. So here is a value Kayla, or subscribe, or get rid of, or 700 or June 16, whatever it is, those are
all values. All right. Oh, man, it's already 10 minutes. All right, then
we have an entry, you may hear the term entry, which I like to use this for the like the
it's almost like a verb, the when you enter data, you get an entry. So basically, an entry
is just another name for a row. So um, there's obviously a lot of similar names. And we'll
be making a table saying what we're just just getting database design. The process of designing
your table to remove anomalies and have data integrity. game or schema, however you want
to pronounce that, that is just a physical, that's just a drawn out structure of our,
our database, then we have a new term here. It's called normalize, or normalization. normalization is basically
just a bunch of steps that we're going to follow to help us get the best database design.
So that's just that's part of database design, when we go through these normal forms, to
get the best database design, we'll talk about that more in an upcoming video. But just I
want you to know that the term normalization is the process of building the best database
design. The other one and naming conventions. And naming convention is just a consistency
used to make things consistent. So we named tables a certain way we named columns a certain
way. we name our database a certain way We draw our database schema a certain way. So
basically, convention is anything that is done repeatedly to create consistency. And
the last one here is keys, which I'm not sure if I talked about this or not yet, I think
a little bit, but not much. But key is something to make everything unique within a table.
This is how we make connections among tables, we connect IDs, we may say, Oh, this comment
was posted by the user with the ID of seven. Well, that points back to the user table.
We do all that with keys. And we'll learn more about those as time goes on. Alright,
so you see, we have a lot of terms and a lot of them are repeats for this meaning the same
thing for example, we have relation. And then we have table. And then we have file. So relation
table and file are all talking about the physical structure, a table pretty much record with
circle diesel's record would be a row, so we have a row. And then we have a sub, these
are all talking about rows within a table. And then we have, how should I do this? Let's
just cross them out columns. And then we have attributes. And then we have a field, those
are all talking about a column in a table. And also entry, you can kind of think of that
as a, a row, because you put like, you enter data into the table and you get a new row.
The value is just a specific, you can think of it like we read some of this. We have this
table, and we break it up into columns. So we have ID, name, and then phone number, for
example. Those are a column headers, and then we have specific dice, seven, Halo. And then we have another guy eight. Well,
if we separate this into cells, which is another term, we have a specific value that we've
put in here, such as john, that is a value. No entity, that's the only one that's so we
just just went over value entity is what's left that's not circled or crossed out or
squared, or some entity is I don't like to really assign this to like a row or a table
or anything because an entity is just something we're storing data about. So that could be
an individual row, or it could be the table type. So for example, we have a table for
users while the user is the entity, but a specific, a specific entity would be a row
within this table. Because we'd have the entity for this guy over here. And then we'd have one for this guy down here or for
this check over here. Or lady I should say. So these all get their own rows to see. So
an entity that's is something we store data about. This video will be just some more vocabulary,
there's not as many we're broken up into two categories. This one first one is SQL. So
first we learned about SQL, which obviously is program language to use to binary mention
this I'm horrible. Structured Query Language is what SQL means. What does that sort of
stands for, it's used to connect to your database. Hi, sorry, I just got distracted. I was waving
this paper. And like my lens is like flashing for some reason. Anyways, the next part was data Definition Language,
which is used to define it's part of SQL. So SQL has data Definition Language, and D
and L, data manipulation language DDL is used to define the structure of our database. data
manipulation language is used to insert values in our database, as well as update them, delete
values and search values and so forth. Oh, the other thing was, I never really mentioned
this, but I do want to mention it here, SQL keywords. So anytime we have the word keywords in a programming language, it's talking
about something that is reserved that you're not supposed to use for your user defined
data. So for example, Select in SQL, this is a word that your database is going to recognize,
as Okay, we want you to do something, we want you to select data. It's a keyword, because
it's already defined. So I want to I don't want to name my tables or my columns, any
keywords because it can cause errors or confusion, or just make things complicated. I always
put SQL keywords in capital letters. Right. So that's pretty much all for the SQL. Here's my race. Go America, and every other country, oh, bright presentation.
So the very first thing is we have the front end of an application. The so the front end
is what the user sees. The back end is what's going on behind the scene. When we have a
website, we don't just have what the website looks like, we have code that gives us that
look, when we put in data to forms, we have languages that communicate with databases,
to create a easy to follow non confusing front end. But the back end is complicated as heck.
So that's the difference between front end and back end front end is what the end user
at a particular time will see, the back end is what's going on behind the scenes. So often,
when we're working with databases, we will program a front end because we don't want
everyone accessing our server, it's insecure. And it's just not a good idea. So we often
create a front end application to access our database, most commonly a website, although
there's other forms, but whatever. So here's our database. Rather than people signing into
the server and using SQL code to get information, we often program front ends for people to
access this data in a different way. So each one of these front ends would be like a website
page, for example. So here would be your registration page here would be your users page, here would
be your profile, your homepage here would be if you like you
can even have different tier systems. So this could be a page where the website administrator
can add or delete users, they have that extra privilege. This is also the view system, which
we will talk about in a second. Well, the front end doesn't allow us to directly type
in our own SQL, it only allows us to use what the front end gives us, which is then communicated
to the database. So that's what a front end end is, the back end would be the server side
code used to communicate with the database. So when we when we're talking about clients
and servers, we have those two terms, we have client, and then we have server. So very first
thing is client. client is think of it if we have this wall here. Over here, we have
a computer. And then over here, we have the database. This is the client side, because
we're accessing the database over here is the server side. So client, client side and server side, I
don't really have room to write that. But the server serves instances of this database
to the client where they can access the data in some way depends on how we the privileges
we give them. So when we talk about different privileges for different people,
we use what's known as a view. So personally, I finish this client side and server side.
And we communicate from the client side to the server side by a server side scripting or programming language. It's huge.
Well, my hands tired. We can give custom views. So a view is just taking the data from the
database and illustrating in a different way than how it's actually be stored. For example,
a user on a website can visit, let's say, we're on like a social network and I visit
my friends page. It'll tell me their their name, possibly their username, if that's what
they use, their date of registration, their phone number, if that's public, for example,
anything like that their relationship status, although information that is private may only
be accessible to the user who owns it, for example, their password or their email, or
their friends lists, that might be a different view, depending on what page you are on what
page you are on, and the user that you are. So I may be able to access my own password
and email. But that might be private to other people. That's the view. Views often use joins,
which basically Connect data from multiple tables to create a new table. We have two
tables here. users table, comments table. Can you see that? Can you see it? You better
be able to see it? Well, a comment is always given by a user. And we connect it using the
ID. It's called a foreign key connection. Well, this ID, let's say it's the ID of 25.
Right? Well, instead of having, okay, this column, and it was by the person with the
name, with the user with the user ID of 25, we want to say, Caleb curry. So we have a
join, taking the name from the user table, and a plot and connecting it with the comment
of the comments table to get this new generated. Table. This is just a view. It's not actually
stored in the database like this, we're just creating a new illustration of how the data
really is stored. And then we can have the name of the person followed by the comments,
or comments of that person. You may be asking, why don't you just use the name as a reference?
Well, that's because many multiple people can have the same name, for example. So we
always need something that's unique for this connection. Whether if you don't want to use
an ID, you could use something like a natural key. Use your name, for example, but I often
use IDs. And we'll be talking about that more in upcoming videos. So yeah, that is all of
the vocabulary that we have gone over so far, pretty much. If you have any questions about
any of those, just leave a comment, I recommend that you write all those down, I should have
told you that at the beginning. But anyways, I recommend you write those down, as well
as the definitions, study those, learn them. And especially study from the previous video
study all these different terms that mean the same thing. Because after a while, you
get kind of used to those terms. But if you're just learning and you don't know that these
are talking about the same things, well, then you can get confused very easily. So I recommend
you study. Alright. A row is also called a tumble and is also called it whatever, blah,
blah, blah, that will help you that way. When I say whatever word it is, you know what I'm
talking about. And you don't have to like, check your charts or whatever, or go watch
other videos. So that's something I definitely recommend that you memorize, as well. For
the SQL stuff. It's not as important, like the DML DDL. But I do recommend that you memorize
all of these terms as well. We'll be talking about atomic values. So atomic values, basically just mean that means
that the value stores one thing. Everything we store in a database should be
one thing. When we have columns, they are about one thing. All of the values within
that column are one thing. We have a column named. Well, what does a name consist of,
you can have a first name, middle name and last name. Well, this, although we're talking about one thing and
name, it's not really necessarily atomic, because we're storing three names within one
name. So if we wanted to make this atomic, the best thing to do for database design would
be to break this up into first name, middle name, and last name. So now I have Caleb,
Daniel, through three separate names, right? Alright, so when we do store things and database we want
to go, we want to try and make them atomic. Now we don't want to go to the extreme, where
we break everything apart to where it doesn't even make any sense. But I just think of like,
atom, an atom atomic, when this word came about, we never really split an atom. So we
thought of an atom as the smallest, indivisible, into visible, I thinks the word piece of information
in this world. Now we can split it, but whatever, just so sometimes, you may think, Oh, this
isn't really a good term, but everyone will still use this term. So just think of atomic
as the smallest one, one individual thing stored. Now we don't want to go to the extreme.
For example, if we have the word, subscribe. This is a word, it's one word, don't go to
the extreme, where we separate it into like word like individual letters. because it'd
be ridiculous. It's like, Alright, we store in a table. Instead of having first name,
last name, we have first letter of the first name, second letter of this lab, first name,
third letter, the first name for further than first name. That doesn't make sense. We want
to break it down into we can treat it as one thing, for example, an error, a phone number,
it often has an area code. Well, we don't want to break a phone number up into multiple
portions, I often just store phone numbers as one, because you treat it as one thing.
When we come when it comes to addresses, one address consists of like multiple, we have
we have a street address, we have a city. And then we have a state, we have an area
code for a zip code I mean. So addresses, I often
break this up into multiple columns, rather than storing one address with 123. Epic lane.
legit California 73821. Rather than storing all of that, in one column, I break this up
into street address, city state area code. And then that allows us to run more complex
queries on our data because we can select all of the users from the state of California,
or we can select all of the users who live in this in this city, or pretty much anything
like that. So this is part of one of the normal forms, which we will be talking about in future.
But as for now, just think of storing everything as one. So not only do we want the column
to be about one thing, like an address is just an address, or a phone numbers, just
a phone number. But we want the values within that to be singular as well. For example,
we could have a column favorite movies. This isn't going to work, because within that column,
we could store unlimited movies. And that is also improper. So that's not going to work
as well, every single column header should be singular favorite movie. And you may be
asking, Well, how would you store multiple movies? Well, that has to do with more complex
relationships. And that is something we'll be talking about very shortly. But the solution
is not to store multiple things within a column. This video will be about relationships. And
I don't mean relationships as dating advice, because I probably couldn't give any of that. But besides that, we're going over database relationships.
So what is a relationship? Well, in a database, everything is connected. It's Think of it
like a giant web, somehow something is connected to something else rather than storing everything
in a giant table. Sorry, my what my boards kind of wet, it's not really writing very
well, we split that. And then we have two smaller tables for manageable data simplicity,
as well as data integrity reasons. So now, we still have to maintain the connection between
data. So we take one huge table, break it into two tables, and then we have what's known
as a relationship. There are multiple kinds of relationships.
And we'll be talking about that in the next video. But for this video, we'll be talking
about the general concept of relationships. So relationships, talk about entities, an
entity is anything we store data about. So this is an entity. And this is an entity.
And they are related in some way they're connected. There's a relationship between them. This
erasers scrap There we go. Okay, there we go. So now my chalkboards. Alright,
so I have to worry about entities. There's two terms that we talked about earlier. entities
and attributes, entities or anything we store data about. attributes are the things we store
about the data, well, when it comes to entities, when that attributes are what we store about
the entities, so basically, we have entities. And then we have the attributes for the entity,
right, and then we have a different entity so that as part of our database design, we
figure out all of the things we're storing data about, think of like a college, we could
store information about the student, the professor, the classes, the faculty, the whatever else.
So we're gonna have multiple entities, and then multiple attributes about that entity. And then we can have a third entity. So just
think club, student, Professor class. This is all part of our database, our database.
So our database structure consists of three entities and attributes about each one of
those entities. What's an example of an attribute? Well, it can be the student's name, the student's
address, the classes, they're taking, the, what, there, whatever, it doesn't really matter.
They're a major, that's another entity, we could have an entity about the the majors,
such as science, or whatever else. And then we can have attributes for that, like the
classes that are required to get that major, how long it takes, and so forth. That will
become part of our database structure. Well, these are still related in some way. Because we
have a student is a student is going to school for a major. So there's a connection from
this entity down to the major. So let's just replace these with like, here's student. And
then Okay, that looks like crap. I could have put some specific examples in here. We could
have a student we could have a professor sample proof, then we can have class. And then we
can have major. Well, a student goes to school to get a major, a student takes classes. The
classes are taught by a professor, the professor's typically have a specific area that they teach.
So they might be part of the major like the major league to teachers for that major. Whatever.
Students Yeah, you get the point, everything's connected. So when we design relationships,
that tells us how we want to structure our tables. That's going to make a lot more sense
in the next video. So I just wanted to introduce the idea of relationships in this video. The
next video we'll be talking about a specific kind of relationship. So, one to one, that is a type of relationship, there
are two other ones, one, to many, and many to many. Here are three types of relationships.
And we will be talking about each one of these individually, in the next three videos. This
one will be over one to one relationships. So I wonder one relationship basically just means that one entity has a
connection with one other entity. And that's all they can have. I like to think of a marriage.
So we have a husband and wife. This is a one to one relationship, because
a husband can have one wife, and that wife can have one husband, the husband can't have
multiple wives, and the why the wife can't have multiple husbands, it's one to one, because
one entity is limited to one other entity. This entity is also limited to one other entity,
if they want to one relationship. So another example of a one to one relationship would
be a social security number. Alright, so a social security number in America is basically
just a unique number every citizen is given. There are, I think there's occasional repeats,
although generally, you think of a social security number as being unique to one person.
So we define it as a one to one relationship. Even if there's possibilities of being repeated
data. One person only has one social security number.
Generally, that social security number is only supposed to have one person. So we have
a a person. And then we have a social security number. One person has one social security
number one social security number is assigned to one person, if you want to draw out your
relationships, you can say person as one social security number, social security number. assigned. I'm sorry, yeah. Okay, so assigned to one
person. So a person is assigned a social security number, social security number is assigned
to one person, we would not even though there's a possibility that the social security number
might be repeated. We want to design it this way. Because that's like saying, the social
security number is supposed to be assigned assigned to multiple people. It's not supposed
to it's supposed to be unique for the person. So we designed as a one to one relationship.
We're going to be talking about a one to many relationship in the next video and then a
many to many relationship. And after that, we'll be talking about how we design this
within a database. How do we structure our tables. So in the previous video, we talked
about a one to one relationship, that is one of the three kinds of relationships that we
can have. The other one is one too many. What that means is that one entity can have a relationship
with multiple other entities, but a specific entity can only be related back to one other
entity. I know that sounds a little confusing when confusing when we're talking about entities
and all this conceptual, high level thinking. But think of think of it like comment on a
website. When you go on to YouTube or whatever and you go below, you can leave a comment
on my videos. That comment is only owned by you. It has your has your name. That is a
one to many relationship. The reason for that is you can make many comments, but an individual
comment is only owned by one person. So to draw that out, you have I have a user. And
we have multiple comments. You can see that the user is able to make multiple comments.
But each individual comment, like this one, for example, is only owned by one user, that
is a one to many relationship, or one coin, many. Can you see that? Yeah, I think you can. So
this is how a one to many relationship works. If you want to think of, again, like the marriage
thing. Think of a king, what his name him king, Sam. And Sam demands to have multiple
wives, and he's super controlling of his wives. So Sam has multiple wives, right. So I'm just
kind of dry on a chalkboard here. We have Sam the king over here. Give him a crown, give him a big smile. And he is royal and
famous among the lions. And he's really selfish with this woman, he demands to have 500 wives,
or however many wives he desires. So we have all of his wives over here. So he has these two wives, and then he has
this wife over here. She's not so good looking. But anyways, he is married to these three
wives. Although he's very, he's very protective and very selfish with his women. And he does
not want these wives to marry other men. He they're strictly married to the king. So the
king can marry multiple women. But each woman can only marry one person. Oops, I kind of
already have the line drawn, drawn. So they can only marry one person, they're not able
to go marry another guy, that would be a whole nother way that ain't gonna happen off with
their heads. See what I'm saying? It's not going to work. So that is a one to many relationship.
In the last video, we talked about one to many relationships, the video about that we
talked about one to one relationships. Well, now we're talking about the third possibility
of relationships. And that is many to many relationships. So when I think of many to
many relationships, I like to think of a polygamous marriage, where multiple husbands can have
multiple wives. And multiple wives can have multiple husbands. I just draw this out. So
you can kind of understand what this would be like. We have the king right here, right?
So let's say he's a king, and he decides to marry three women. Let's kind of make up real quick. This guy's Why do you need three wives? Come on? There we go. So this guy is married to these three women,
right? And then he is very protective. But then he decides Oh, what the heck, they can
marry different men if they want. who really cares. So this girl marries this other guy,
right? So now she's married to two people. Right? What's wrong, sir? Only the king can do that. Well, not now.
Because it's a one to many relationship. One King can marry multiple women. And one woman
can marry multiple men. So this guy is married to this girl. And this girl is married to
this guy over here. And this guy's married to this girl over here. So Sally is married
to Jim, who is also married to this girl over here. See my hand drawn here. And this girl, surprisingly, is married to
this guy. She's married to Kathy who is also married to Jim. And by the way, Jim is married
to the second wife of the king, who the king is Mary did this girl over here. And you see,
well, it gets very complex very quickly. And that is an example of a many to many relationship. Now, obviously, you're not going to be storing, like, stuff like
this in a database. So let me think of a practical example real quick. I can think of a college,
specifically a class, and an instructor. Alright. So that's it, let's think of a class and a
student. So here's a class. And here is a student. Okay. Now, one class, how many students can be in
a class multiple, you know that. So we can have multiple students in this class. Let's
draw boxes for quicker. So let's say all these boxes are students. A class can have multiple
students. Well, not only that, but a student can take multiple classes. So this student
can take this class. And this class can be taken also by that student, and this student
over here. And this student can take another class, which can have this guy in it, and
this guy, and this guy can be in this class as well. And by the way, he is also a drama
jock. He is also taking another class, if he's in that class, which has that student
and that student, and it just gets very complex. So you can see this might be very, very difficult
to store within a relational database. And there are reasons we actually can't store
this in a relational database. You might be thinking, well, what if you need to store
the classes that a student is taking in this students within a class, we will be talking
about in the up that in the upcoming videos, with solving many to many problems, many to
many relationships do not work in a relational database, the only two that will work is a
one to one and a one to many relationship. But this is just the concept, you think of
a concept of a many to many relationship, think of a class and a student class can take
many can have many students and a student can take many classes. In the last three videos,
we talked about one to one relationships, one to many relationships, and one to many
to many relationships. This video, I wanted to talk about how we design these relationships,
I decided I was going to break them up into three videos. So first, we'll be talking about
designing one to one relationships, and then one to many, and then many to many. So this
video will be about designing one to one relationships in a database. Alright, let's begin. So the
first thing we need to realize is that when we have a one to one relationship, the the
attribute side, so we have an entity. So let's say let's just say we have a connection between
a person and their, their username. Alright. So we have this, we have this guy here, let
me get rid of these boxes. I don't mean this. We have this guy. And he has a username on
a website, we'll just say it's Caleb curry, because that's usually my username for things.
And we'll just name this guy. Right? Well, that username, the only person who's going
to have that username is him. And he is only going to have one username, that account is
only going to have one username. That's a one to one relationship. So we need to realize
that this is exclusive to this person. It's all hits, no one else owns it, no one else
is connected to it. It's only hits. So that means we can often store one to one relationships
as attributes rather than entities. So rather than his username being an entity, it's an
attribute. If you don't really know what that means, don't worry, I'll be explaining in
just a second. But just think of it as it's exclusively his so that is part it describes
him. What is this guy's username? It's Caleb curry. It's an attribute of that person. When
we're talking about one to many relationships, though, are many too many. We think of like
a class. The students of the class are not exclusive to that class. They can take other
classes. Therefore it wouldn't really make sense to store them as attributes because
it's not Really, it just doesn't make much sense. And I know, it'll, it'll be clear,
so just Just wait a second. So we can actually have a table. The relationship between the
account and the username of the account can be stored just as a column within the table.
So we have an ID. And then we have a name, for example. And then a username. This username
is exclusive to this ID of this person. So like six, Caleb, Caleb curry, that is how we would design a
one to one relationship, we would put it within the same table. If we have a new row, within
our table, we now have a new person, this person can't have another username that Caleb
curry, they have to have a different one. So we can make it john 123. This name is exclusive to that person. So
we just store it as a column within our table. Make sense? So here are our individual rows.
This points to this ID, it's an individual entity. So the entity is the the account,
the user name is an attribute to that an account, there will be occasional times when we store
a one to one relationship over multiple tables. And I will explain now when that would be
the case, let's just think of an example of a database for a credit card company, we have
a relationship between the card holder and the card. By card holder, I just mean the
person who who gets the card and can use it to buy things, the person who owns it pretty
much. And then we have the card. All right. Now let's just say this, this company only
allows you to have one credit card. So this card holder can only have one card, and this
card can only be owned by one card holder. That means we have a one to one relationship,
one to one, how would we store this in a database? Well, if we just went with the attribute thing
that we talked about earlier, here's what it would look like. Here's our table. And
I'm just going to list the columns. So we have the cardholder table. First thing is
we'd have like a an ID for the card holder, we would have probably card holders first
name, the card holders last name, and then we would have the card. So we could say, well,
that's their card, we could let's say we could assign every card and ID or a card number.
So we can say card number. If they have a card, we can give it a value if if they don't
have a card, we can leave it No. Well, that can be considered an attribute of the card
holder. Because all these points back to the cardboard. Now, if we want to store more information
about the card, such as the the issue date when the card was given to the person, so
we could say, card issue. date. Well, now we can see that this is relying on the card,
not the cardholder. So if we want to store extra attributes, about the attribute in the
one to one relationship, we can move that to a new table. And we can make it a card
table, give it an ID. And then we can say card number and then issue date. Now we can store as much information about
this card. So if we want to store the maximum out, be a late fee. Pretty much anything we
want to store about this card can be stored in this table. And we can replace this card
number with a reference. So I'm going to card ID and that points back to this ID. You see
what I'm saying? This is a one to one relationship over multiple people, just like this card
has a connection to the card holder. There's a one to one connection over multiple
tables. The reason we did this is so it could store more information about the card. Because
if we stored it all in one table, we would have bad database design. And I'll show you
why. in just a sec. Alright, so here's our table. Can you see that, all right, we have the ID
and then the name of the person. And then we have the card number, and then the max
amount, and so forth. Well, you can see that this table is really about two things, it's
no longer about one thing, we would have to say this is a user and card table, which is
not proper, because we're not following the rule of one where a table should be about
one entity. And a row should be about one entity. Because now we have the user side,
and then we have the cards, it's like trying to store two tables. In one, the only time
it's acceptable to have the card number in the users table is when we don't store more
information about it. Because now all we have is a card number. The card number is about
the user and points to the user. But the max amount of the card has nothing to do with
the user, therefore, it's reliant, dependent upon the card. So I wonder one relationship,
in conclusion, one, a one to one relationship. The way it is stored is either an attribute within the table, or if you need to store
more information about the entity, then we can have another table and then just use foreign
keys to connect them which that's something we'll be talking about in an upcoming video.
Almost always though, you will be seeing a one to one relationship, used as an attribute.
So when you think if you have a one to one relationship between something, you have a
connection, let's say it's a dating website, the user is in a relationship with another
user, that that's a one to one relationship, we could just have that as a column within
our user table. So we could have the user table, we could have the user ID,
and the name, first name, last name, a phone number, whatever else. And then we can say,
relationship. That's a one to one relationship, we could just have that have that be an ID
of another person. And we don't have to worry about having another table for that. This
video, we're talking about designing one to many relationships. First, I want to bring
back an example from the last video into this video. So we can kind of explain the differences
between one to one and one to many. So in the last example, we had a credit card company
where a person is allowed to have only one card and a one card can only be owned by one
person. Let's draw that out. We'll just draw the table and the columns within that table.
So two tables here, we have the user table. And then we have the card table. This is when
we store a one to one relationship over multiple tables. Otherwise, we could just store it
as a column because I mean, we're only storing one thing. We have the user ID. And then we
have a card ID. And then to connect these as a one to one relationship. We have a foreign
key. So we put a connection card ID in the user table, the user ID in the card table.
So Alright, ID, user ID, that means that what that does is it connects these tables. So
user ID to this user ID, card ID to this card ID, that is how we would make a one to one
relationship over to tables. And then we can store other stuff about the user in this table,
other stuff about the card in this table. Now, to convert this to a one to many, we can change this to something very small.
And one to many relationship would be saying, one user can have many cards, but a card specific
card can only be owned by one user. So there's no card can't be owned, or co signed or owned
by two people. Now, the way we do this, is let me just erase these columns real quick. As a start from scratch. So we have a user table, and then we have
a court ID. Once again, we need IDs for both of them. So we have a user ID, and then a
card by the user owns multiple cards. And we don't know how many they own there, they
can have one, they can have two, they could have three, they could have four, they could
have five, so it wouldn't make sense to put the card ID in here as multiple columns. Because
I would put five columns for card ID, well, then what if someone had six cards? What are
they going to do? They're not, they're not going to have a sixth one. So what we do is
we put the user ID in the card table, which points back to the user, I d in this table.
So now, if we made specific examples of this, let's say we had this guy here, giving a talk. Right. So we've got this guy right here. And his
user ID is 63, just a random generated number by the database. Well, we also have cards
over here. So we have this credit card, we have this credit card, we're actually drawing
card cards here. So we had the barcode and the name and the codes and all that crap.
And then we have this card over here. We make a connection, we want all of these to point back to this
guy. So we give a user ID, we could say the user id 6363 63. So now we know that each
card is owned by the user with the ID of 63, which we can figure out is this guy, whatever
his name is, we can join that. So now we store a one to many relationship into tables, where
the many side gets a foreign key to the single the one side. And if you don't know about
foreign keys, that's fine, basically, in simple terms of foreign key is just that that Id
connection is user ID is a foreign key that points to the user ID in the main table, foreign
key points to the primary key and the primary table. This is also said to be a child. I'm
sorry, a parent child relationship. This will be the parent because every single child has
to point back to the parent. That is how we store a one to many relationship in a database.
So how would we know if we had this example? We had comments, the comments section on a
shopping website. So you're allowed to leave a review there. Let's say you can leave a
review. And we had this table these this table setup. We had the users. And then we had the
reviews. And we had a an ID user ID. And then we had a review ID. I just give that to every
single table. Every day I give every table an ID. Then we have the username. And then
we have the actual review. So the user has a username, the review has the actual comment.
And we could also say we could say we have a review title. So we can say like oh This
review is this Product sucks, or this product is really awesome. And then we can have a
paragraph saying, why that products awesome, for example. Right now, this is not properly designed, because there's
no connection between the user and the review. Because if we look at the review table, who
put who posted the review, it doesn't say all we have is the review ID, the review and
the review title. It doesn't say who posted it. So if we put a key foreign key that points
back to the user table, that would say that is the parent, the person who posted that
review. So we can have a user ID, which points back to the user ID in the primary table?
Is this a one to one relationship? Or is this a one to many relationship? Well, this, we
could have multiple reviews, and each one would point back. So we could have a another
review by and we can have the same ID. Let's just think of some examples. Let's say we
have the review ID of six. And then the review is awesome, are good. And it's by the user ID of 62. Well, then
we could have another one with a review ID of seven. And it could say, Great. And then
we can add it from the user, Id 62. Well, these are both from the user id 62. So we
can say this is a one to many relationship, because multiple reviews going back to one
user, if we want to wanted it. So a user can only post one review, well, then we need to
put the review ID in the user table. So now we have this sort of like this double connection thing here. Because this review ID connects it. So it's
saying only one review ID can be from the user. And honestly, this, this can probably
be improved, because the review is not necessarily connected to the user that directly. For example,
if the website is solely forgiving, one review, well, then we could give it a review ID. But
that might not be the best example, simply because the user might buy multiple products.
And each review could have a product ID easy. So I mean, it kind of gets complex and just
kind of have to study. But the review, it would probably be best to make it a one to
many relationship, because it wouldn't really make sense to have it as a one to one relationship.
We want it as a one to many, because multiple reviews for multiple products can be given.
So this points to a product, this points to a product, this points to a product, and they're
all by a specific user. Alright, so yeah, that's just some basic design concepts that
you will need to understand. When you have a one to many relationship, just store over
two tables, and give a foreign key to the many side pointing back to the one side. Before
we start the many to many relationship design, I wanted to talk about Parent Child relationships.
So we have when we design these relationships, a table is always the parent and a table is
always the child. And when we're talking about keys, keys keep things unique. And they always
they're they're used to connect tables that are related. So every t table has a key that
keeps it unique. When we create a foreign key, it references that primary key in a another
table. So the primary key is the parent. The foreign key is the child. The child points
back to the parent inherits the values from the parent. For example, the parents ID is
a six then the foreign key pointing back to The parent is at six, the job. So in a one
in a one to one relationship, where we just store everything in one table, which is the
most practical way to do things, we don't have to worry about primary keys or foreign
keys, we just have one table. And that's all we have, they only have columns within this
table. So we have the user ID, the password, the username, the whatever else. All those
are just columns within a table, we don't have to worry about a parent or child, because
they're not making any relationships across multiple tables. But when we get to one to
many, that's when we have this table pointing. Or has many children. So you would say this
is the one side it's the parents. Then we have each of the parents children. These all
point back to the parent with a foreign key. So if you think of like comments on a YouTube
video, every comment has a poster or a user account of a person who clicked post a comment.
That means that the commenter is the parent, and the comment is the child. Another way
to think about that is if you if you were given a comment on YouTube, you can figure
out who posted that comment. That's because the child always points back to the parent.
Now, if you look at a user account on YouTube, such as Caleb, the video maker to awesome
channel, check it out, and be sure to subscribe. Well, you don't necessarily know every single
comment that that parent has given, there might be a feed in there that says recent
comments. But when we look at the child, we always know the parent because it
inherits the values. But the parent doesn't inherit anything from
the child. Think of like in real life, you can see a married couple, and you don't know if they have children or not.
But if you see a little baby, we think, all right, that baby has a parent or parents.
That's the similar idea with database design. Think of this wall here between the parent
and the children. The children inherit values from the parent such as the
foreign key, a seven, that tells us who the parent is. That is important. I know, it seems
like okay, who really cares, who's the parent or child, just get on with the database design,
you knew, I'm going to unsubscribe? Well, this is important, because when we get to
understanding how to design each relationship, we need to understand where we put the foreign
key, which table does the foreign key go into. that is decided by the child because the child
always has the foreign key that points back to the primary key. So user ID, primary key,
user ID, foreign key points back to the primary key user ID. So foreign keys always go in
the children, primary keys go in the parent. When it comes to many, to many relationships,
we have problems with Parent Child relationships. And that is why we can't store many to many
relationships in a database. And that's what we're going to talk about in the next video
when we talk about many to many relationships, specifically. But just know that when we have
an entity, think I want you to think of whether or not it would have a parent, a lot of the
times it would but other times not so much. But if you think of think of an order for
a store, an order needs a parent, because an order is when you buy product who is buying
it, someone has to buy it. Otherwise it wouldn't. It wouldn't exist, the child would not exist
without the order. So let's let's relook at this. This is the user and this is the orders. We can't
have a child without a parent logically, it just doesn't make sense. So if there's no
parent of the order, the order cannot exist. First, we need to keep that connection. And
order needs someone who bought that order. Hopefully, that's kind of making sense. So
just keep in mind, parent child relationships. I'm not trying to repeat myself, but I'm trying
to repeat myself. So you get destroyed in your brain, foreign key goes on the child,
primary key goes on the parent. And soon we'll be talking more in depth about keys. So just
for now, memorize primary key and foreign key primary is the user ID. The foreign key
is a reference to that user ID. It's in a foreign table, and it points back to the original
table. This video will be about many to many relationships. Oh, and we're talking about
designing many to many relationships. So I said a couple of weeks ago, videos ago that
we have problems when we design many to many relationships. They don't work out, right?
They don't. And I mean, I never really explained why. Well, this video, I'm going to explain
why. Think of, let's think of a good example, a college class can have many students and
a student can have many classes. So we have two entities we have to worry about. We have a class. Let's just pluralize it because it makes more
sense, the classes, and then the students. So let's just think of an example of a way
we would try to treat we would try to design this, and it's not gonna work out, right,
but we'll just we'll just try. Here we have classes table, and we have a specific class
such as math, math, 101, whatever, really. So we have Matt, we can't even read that my
handwriting is so bad. So we have math one on one. And then we list all of the people
within this class. So we have student number one. And then student number two. And then
student number three. All right, well, we can try that. Except, what if a student drops
out? Well, then we have a null value, which we don't want to have those if possible. And
what if we need four students? Well, that's not going to work because we only have three
columns. So how else can we do it? Well, we can try just having students. Well, then every
single column could have like 100 students, and we already learned about the atomic rule,
we only want one column to store one value one student. So what do we do? Well, okay,
that's not gonna work. Let's try over here. Let's try it on the student side. We have
a table for students. So we have Jimmy, and he's taking his first. Alright, let's let's
list the columns. So we have the name. And then we have class number one. And then we
have class number two. And then we have class number three. Well, that's cool. What if he
wants to take four classes, though? Well, then we have to add a new column. And let's
say someone psycho crazy guy takes like 20 classes. So now we have all the way down to
class 20. And then a new person comes to the school. And he only takes one class. So now
his first class is like math. And then there's 19 columns for that guy who are empty, because
we have to half the columns for every single person. We think of it like this, we drew
that out. So where you can see it better, we would have a table. And then we'd have
the ID of the person. And then the classes. So once puts like class one, class two, class
three, class four plus five, six. Let's just leave it at that. And we'll just kind of make
rows for these. We have new guys sign up. So we have a guy with the ID of six. And his
first class is math. Right? And then his second classes, geology and then Spanish, and then
fashion. And then biology and then chemistry. And then we have
a new guy come and he his IDs, eight, and he only takes science right? Well now we have
no No, no empty nothing. We wasted all of this space. And you can assume that's going
to keep happening. So that's wasted space in our database, bad design. So how do we
possibly do? Well, the trick is, because because we think of if we try to think of like parent
children, who's the parent in this situation? Well, a class has multiple students. So that
would make the class the parent and the students, the children, but a student can have multiple
classes. So that would make the student the parent and the classes the children. So how
is it that this is the parent, and this is the parent at the same time, that means this points to a child. And this points to
a child. So this parent is the parent of this parent who is also the child of this child,
which that just like blew my mind, I don't even know what's going on. So the way we do
this, is we break it up into two, one to many relationships. So this many to many, is going
to become a one to many. And then a second, I think, I guess all right. Okay, just for
you guys, as information, these these colon things, I think the correct way to do that
is many two, and four, like the like the end keep going on. Just don't don't worry about
that. We'll talk about that. in upcoming videos, I'll just write that for my sake. So we're
going to break this up into one many. And then another one too many. But the other way.
So that is how we fix that problem. All right. So let's implement that with classes. And
students. What we need is what's known as an intermediary table, or a junction table, they're both the same thing. I'll write that
out. So you can hear me enter mediary or a junction. There's also like 50, other names
for this table, but I mean, just intermediary table will work fine. So I n t e r m e d ay
ay ay ay er y. And what that is, that is how we connect our tables. So we break this up
into a total of three tables. So we have the intermediary table, which is the one I just
talked about. And then we have the students table. And then we have the class table. So
class, student, this is the intermediary table. So we have a one to many relationship going
this way. And then we have a one to many relationship going this way. So one, many, one, to many. I know that seems a little confusing
at first, but trust me, it'll make sense. Once we start explaining things more. So we
have one to many, one to many. And this is the intermediary table. So let's just draw
this connection. So we kind of make can visualize this a little more. And kind of, like split
this to see how it works. So we're storing the many side of both relationships in one
table. That's because we know that a one to many relationship is spread out over two tables.
So one, to one, to the to, it's being shared. So what would we name is, we would say like,
we can name it. class students, for example. And
these are going to have foreign keys that point back to the class and the students.
So now that we kind of understand what it looks like when we draw it, let's give specific
examples to illustrate this. Actually, like what it would work like look like whatever.
Alright, so let's say we have our classes over here. And our students over here, okay,
so first things first. We have metaphor, just keep it simple math.
Science, I mean, obviously, the more in depth like math 101, math, two of five, or 1200, or whatever. And then we have English.
But just for simplicity sake, let's say these are the only three classes in the college.
Alright? Then we have all of our, our students, we have Caleb, we have celeb, we have calib.
And then we have, man, I'm so conceited, all I ever talked about is myself. Alright, let's get rid of my name. Let's go with Johnny, and Jake, and Sally, and Claire, Claire. Alright, so here are all of our students for
our class. classes, students, we connect these in the middle. So first things
first, we give them all an ID, because that will be our primary key. So we'll just give
these random Assigned Numbers, which is pretty much what a primary key is a surrogate primary
key. So we'll get this 163, this is something our database would do for us. This one will
be 75. And this one will be 89. So you guys can see that clearly, we'll do
the same thing over here. So john will have the ID of eight. Jake, the id 17. Sally, the
ID of 16. And Claire, the ID for just getting 666. Because she's 666. I'm just kidding,
we'll just we'll just go with six. Now we use the intermediary table to connect
these IDs. So we have this table here. We have two columns. We have the class ID. And
then we have the user ID. And then if you wanted to title these tables, we can make
it classes, class students and students, the intermediary table, what an intermediary is,
it's something that connects two things, it's like, it allows this table to talk to that
table. So it's a connection between these two tables. We put the ID of the user with
the class that they're taking. So let's say john is taking English in science, we would
have 875 889, right really big, I'm going to run out of room really quickly. And what
this does, is using this intermediary table, we can figure out that all of these user IDs
point back to a specific person. And these IDs point back to a specific class. We are
not repeating data, because we need to know the user have this class ID This is a foreign
key pointing back to one specific value. So that means if john decided to drop out, well,
then we have foreign key constraints to get rid of these. That way, we don't have to worry
about incorrect data or users. I guess, I'm sorry, this should be like students, sorry.
But we don't have to worry about students who we don't have to worry about students being
enrolled who don't exist. So that is the solution to in many to many relationship. This video
is getting really long. So Sally could do the same thing. We could say 16. And then
75. She's taken science, Claire, she's taken a she's taken math. We keep going on if we
really wanted to, like can make this table as big as we wanted. And basically this is
the easiest, best way to use as much databasing resources storage as possible, as well is
now we solved the many, I'm sorry, the parent child relationships. That's because we have
the parent over here. We also have the parent over here. They point to the child table.
So this child table becomes the child of both of these parents. Sort of how in real life
every single person is two parents and the person is this in Tire the row mean, okay?
applying that to this, the parent is the class and the student of the class, and the child
is a row where we have both the class and the student, this right here needs to be unique.
It's an individual child, we can't have 663. Again, because that's saying, Claire is taking
science and Claire, I'm sorry, math, Claire's, taking math, and player is taking
math, that doesn't make sense. So this right here is unique 63 to six, you can have it
in there twice. So the child is an individual connection between the parent and the other
parent. So that is how you properly store many to many relationships. And I know the
all these relationships are kind of complex, and you don't really understand with like
a stupid chalkboard. But like I said, we will be getting into on screen computer videos
and the introduction. I mean, I said that in the introduction, we're not going to do
that introduction, because that wouldn't make sense. So yeah, we will be explaining these
concepts. And then we will be applying these concepts to actual databases. Once we get
through all of the database concepts. This video will be a summary of all of the relationship
garbage that we learned. And basically, this video is going to be like, all of it in one,
and it's going to be awesome. And it's going to make a whole lot more sense after this.
So the very first thing that you need to realize when it comes to relationships, is that there's
not always a defined relationship for every circumstance. And it's sometimes you have,
it's something that you have to decide on your own. So my old database design series,
which if you want to check that out, just go to my YouTube channel. A lot of people
were confused about, okay, is a student to class? Is that a one to one or one to many
or a many to many relationship? Well, that's something that you're actually gonna have
to decide on your own. Sometimes you have to think logically about the application of
the relationship. So let's think of the example of a class and a professor. What kind of relationship
is that? I don't know. Let's try to find out. So we have the class. And then we have the
professor. People would ask me, like, Okay, what kind of relationship is this? Well, it
depends on the application. If it's me, it can literally be all three, it just depends
on what you need for your application. If it's a one to one relationship, that means
a class can be taught by one professor, and a professor can teach one class. If it's a
one to many relationship. That means either a class can be taught by one professor, and
a professor can teach many classes, or the opposite of class can be taught by many professors.
But a professor can teach only one class. Or it can be many to many relationship, a
professor can teach multiple classes that can be taught by multiple professors. So it
depends on the college because some colleges are going to allow the class to be taught
by multiple professors, other colleges are not going to allow that. So that depends on
the application of the database. If I'm building a database for a college that does allow multiple
professors for a class, that means it's a many to many relationship, because many professors
teach many classes. Many classes are taught by many professors. Right? So when it comes
to designing one to one, one, to many, and many to many, I'll show you how to do all
three of those for a class to Professor. So if we have one to one we could have, for example,
we could have the professor right. We're gonna have a professor table only know how
to spell it. But prof is I don't know if there's two F's, who cares, Professor table, right?
If it's a one to one relationship, we can have the professor ID, the name of the professor.
And then we can have a class. That's the class that they teach. And it's the only class that
they teach. That class is only taught by the head professor. That was, that's how you store
one to one relationship. And typically, you're not going to do that with another entity.
When it's when it comes to like a name. You typically will do that because the name is
just assigned to that person. A class can generally I mean, typically professors can
teach multiple classes unless you're some Kind of adjunct teacher, but class, in this
case is a one to one relationship. If we wanted to make it a one to many relationship, or
then we would break off class here, get a new table class, we have a class ID. and here
we can put more information about the class and we'd have the class name. This is the
most common way of storing information on two tables. You rarely very rarely store a
one to one relationship over multiple tables, but it is possible we talked about that earlier,
we'd have class name, and then we can have more information about the class. And then
we have a foreign key class ID, that would make this the parent because class ID points
to class ID, if you wanted this to be the parent, well, then we would switch that we
would make Professor ID, which was probably the most logical way to
do that. But you have to keep that in mind because it can be a one to many directions,
a one to many this direction, or can be a one to many this direction. And obviously,
typically, the class is going to be the many side because a class can be taught by multiple,
I mean a professor teach multiple classes. That means that professors on the one side
that many is the class, which means we need to give the professor ID on this side. So
the proof ID is a foreign key, referencing the proof ID in the professor's table. That
is how you draw a one to many relationship. This direction one to me, parent child. Now
if we wanted to make that in many to many relationship, we'd actually had to break this
up even farther into three tables. So now we have the professor's table. And then we
have the class table. And then we have the probably class professors table, which is
the intermediate intermediary table. So we have this table here. This table here, this table
here, this, these all, this will have an ID, Professor ID, and this will have an ID, class
ID. Then you reference this in this intermediary table, where we'd have Professor ID of like
six with the class of seven, Professor ID of six, with the class of eight, which is
basically saying this professor with the ID of six is teaching two classes. Now, when
it comes to IDs for this table, this whole thing can be an ID in itself. Or if you want,
you can give it a new ID to say class Professor ID. And that's kind of unrelated. So that's
pretty much we'll get into that later. But that is pretty much the song of designing
relationships. One to One is typically in one table, one to many, it's two tables with
the child having a foreign key pointing to the parent, and then a many to many has to
parents, with the intermediary being the child pointing back to both of the parents. Hopefully,
that is all crystal clear. And awesome. Because now you literally know how to design every
binary relationship and database design. And by binary, I mean a relationship between two
tables. So you can have a binary relationship which we talked about, there's one to one
over two tables, there's one to many over two tables, and there's many to many over
two tables logically, but then we have to break those up into the best way to store
those to store those in a database. But this is basically binary because we're talking
about two entities. A class and a professor, a student and a class. You can have relationships
between multiple tables, but that is something I'm sorry multiple entities, but that is something
we will have to address in a future video. Whoa, didn't see you there goodness. Anyways,
In this video, we're going to be talking about key words. So, a key is not something you use to unlock
a door. keys in database are something else. A key keeps everything unique. Basically,
that's that's the easiest way to remember what a key is. So when you think in a database,
we kind of structure things in tables. And we want to kind of do that we do we do that.
We do do that haha. Anyways, if we have a table here, we have the columns, which are
the attributes of each thing. So like, let's make it a user account table, because that's
always the easiest example. So yeah. Okay, user. So that's the title of the table. Now,
each individual row within this table is going to be a new user, each column is going to
be a thing a bout the users. So the columns can be username, first name, last name, password,
and email. As an example, I mean, you can really put whatever you really want in there.
Well, a key is something that is going to separate this row from the rest of the rows.
So in this example, think of the things that can be duplicated, think of all the possible
values that you could put within one of these columns. And think, is it possible to duplicate
it in your application? I mean, yeah, it's possible to duplicate it if you allow it to
be duplicated. But what naturally should not be duplicated. So typically, when you sign
up for a website, or a, a game or something, they'll ask for your email. Right? Now, when
you sign up, you put your email and you typically have to go to your email, and click like confirmation
or something to register your email. So they know that it's a genuine email account. Basically,
just so they can have your email and they can send you emails and stuff. But yeah, that
is an example of what we could use as a key. That's because every single row within this
table should have a separate email, some websites allow you to use the same email more than
once. If that's the case, then this would not be a good key. This kind of key is known
as a natural key. Because it's naturally already in our at our table, we don't have to define
a new column just for the sake of a key. And we'll be getting into more of that in upcoming
videos because doing a whole bunch of videos of Ricky's so you get them like really good.
All, like I'm gonna go over pretty much everything. But anyways, even if you make it to where
only one person can hit us a certain email, and that email can only be used by one person.
Or that could be used as the key. The way that works, is it basically let's say we have
emails 123 at blah, blah, blah, a VC, blah, blah, blah, x, y, z, blah, blah. And these
are three separate rows. Well, this is going to let us know which person we're talking
about. And, like now, if someone has the first name, first name and last name by chance,
let's say we have two Caleb Curry's Caleb curry, Caleb curry, we can't use the name
as a key because two people had and it's, it's going to confuse us because Okay, do
we have two people with a name Caleb curry, or one person Caleb curry in the database
twice? Is that an error on our part or what? So we can't have the key. Be in there more
than once. So it's never there's it's always unique. So the key is always unique. He said, he said, he said, Alright, so what is another example of a key
that we can use in this table? Let's get rid of this email. Let's say we're not using the
email anymore. And let's say the website allows multiple emails from different people. So
like if my mom signed up, and then I wanted to sign up, I can use my mom's email. So we
have two people from the same email. That's an example of some websites that allow you
to do that, but not tons of websites do that, but it is a possibility. It all just depends
on what you want to do. It's up to you because I guess it's up to the if you're making a
database for yourself with it's up to you, but if you're making it for someone else,
then you get your their rules and you follow them. Let's think of some other ones. Alright,
well email, let's just say it's knowing your password. Well that's really a bad one. Because passwords,
they're not necessarily unique because two different accounts can have the same password.
And if if you didn't allow that to where they could have the same password, you try to put
in a password for your new account. And it'd be like, Oh, this password is already in use.
And then you're like, Oh, so then you can try that password on other accounts and try
to hack into people. So that would not be a good database design, obviously. So password,
that ain't gonna work because not every password is unique. First Name, Last Name, it could
possibly work. But it's really bad. Maybe first name, last name, and middle initial
or middle name. But even that there's, there's people with the same first name, last name
and middle name. So that's first name won't work, last name won't work. And the combination
of first name and last name will work. And yes, keys can be a combination of two columns.
We'll be getting into that too. What we have left is user name. Well, user name is generated
word that is used to represent your account. It's typically not a name or an email. It's
something like Caleb 123, or whatever, you know what I'm saying. So then, when people
will talk to you, they're talking to Kayla 123, rather than Caleb curry, or they can
put the name there. But some websites use usernames, or some games use usernames or
programs use username, so you have a username and a password. Well, username is always unique.
Because if you have two people with the same username, when you try to sign in, how is
the database going to know which one you're trying to sign into, you know, they're you
they have to be unique. Some of that kind of makes sense. That can be used as a key
because we could have the username, cave of food. And then we can have doors 123 and then
pop. These are three different people, all the other stuff, first name, last name, password,
email, they point to that key. So we have a first name, and a last name, and password,
and an email for the user with the username, Caleb, see, that's
how the key works. We have a first name, last name, password, and an email for the person
with the with the user, I'm sorry, we have first name, last name, pass email for the
user with the name, dog 123. And then we have a first name, last name, password and email
for the user with the username, pi, that's kind of how the key works. The other thing
is that the key should never be changing. So the second thing, never changing. Now, why is this? Well, that's because when
we create keys, we use them to kind of structure our entire database, everything points towards
the key. And the last example we had, I'll just draw key for representation. Let's say
this is the key and we'll just say it's the username. Right? Well, every other column
is part is points back to that key. So we have the name of the person, we have the password.
And we know that these are pointing to this specific person, if we got rid of this key
and that we have two people with the name Caleb with the password, pi. Well, now, are we talking about the same person? Are
we talking about two separate people? Or what we don't really know? But now if we give it
keys and key let me get get that key back. So this let me see if I draw key right? Probably not. Close enough. All right. Here's our key. Now
we know that this Caleb points to this specific person we can have the the username, or you
might often see numbers like 72 the ID, which we'll talk about that too soon. Now, if we
have a separate key like this, well then that is a different person so we
can have this guy have the username. Caleb is awesome. 72 this got me like sexy chicks.
74. See what I'm saying? So that's kind of how the keys work. We never want them to change
because then it's kind of it's confusing because all of these things objects are part of this
key. Basically, this key is used to define uniqueness. Well, if we can change it, it
questions the integrity of our database. Because between tables, we connect things by keys.
So let's say this is a user table, this is a common table. If we have it to where we
can change the key, well, then this connection is, you're gonna have to update this connection
all the time. Now, if we have 50 tables connected to this user table, I'm just going to draw
out for snakes like well, I won't draw all 50. Well, now when we update this key, this
key connection. So we have a comments by a user, we have sales, we have friends, we have
messages, whatever, it doesn't really matter, well, then we change this key, well, that
means every single one of these connections are going to have to update that requires
a lot of work from the server. Makes sense. So we don't want them to change, because we
don't want to have to have that extra work from our database. So that's one thing, unique,
never changing. The other thing. These are just three general roles, there's some other
suggestions and stuff, but I'll get into that soon. But the other thing is never know. That
means empty VAT or no value. So when we have a table, and we have a key value, let's say
we have a user ID, this guy six, this guy's five, discuss for this guy's 30, it doesn't
really matter the order. And then we have their name, whatever, blah, blah, blah, well,
we don't want it to where we can have a blank ID and still have information. Like, Sally.
This Sally doesn't have a key, that is another thing we do not want to do with our keys.
So here are the three main rules. And we'll get into more in upcoming videos. But the
key should always be unique. Never be changing, and it should never be empty. You should not
allow a row without a key. This video, I wanted to talk a little bit more about introduction
to keys, although I'm going to go out just a bit off topic. And I'm going to talk about
indexes. So I don't think we talked much about these in this series yet. But we are going
to be talking about an index. Alright, so what's an index? I'm not going to be explaining
every single detail of indexes, because we're not really talking about indexes. In this
video, we're talking about keys. So I'm just explaining enough so you understand how this
relates to keys. So think of an index, best example I have is a book. So if I have a really
awesome book, like, like physics or something, what I can do isn't like, Oh, I want to know
about this. I can flip open to the back, right. And I can travel to the end. And I be like, Oh, that looks nice page 7832. And I can read
about that. Well, that's a lot quicker than being like, Oh, I want to know about this.
page one, not on their page two? Nope, not they're not not there. Oh, no, not there.
Huh. Keep going through the entire book until you find it. That will take forever. Another
good example is a phonebook. When you go through a phonebook you have, everything's alphabetic,
and it has the data right there. So when you think of an index in a book, it points you
to the data. Whereas a phone book, it just has the data there, you can be like, Oh, I'm
gonna go to the letter G, find grandma in color. Right? That's kind of how an index
works. And it's very similar for a database. We add a table, and it's like a user table.
And let's say we have 6 billion rows in this great, crazy number. And we want to find everybody
with the name Caleb just came. We want to find everyone with the name Caleb. Well, without
indexes. Basically the database is going to go row one. Does it have the name Caleb? No,
it does. row two doesn't have a name. Caleb no row three. Yes, it does. We'll put that
we'll put that back in the return statement. And then it goes through every single thing
every single row within the entire table, which can take ages. So if we're given an
index and we saw We order that data in a way that the database knows how to find certain,
like it could know exactly where the C's are, well, then it can go straight to the C's,
grab all the Caleb's and boom, it's done. It's like, super fast. That's kind of how
an index works. Well, what's not to do with keys, when when we define a key, it's actually
a type of index. That's because keys and indexes are used for SELECT statements, where clauses
and joins. So I could do something like select everything from users, for example. There's just like a SQL
example. I mean, it'll vary from whatever programming like relational database management
system, you use this as a general example. And then I could say, where now this is where
I specify. Sorry, drop the wait. Hold my books anyways. I'm selecting all of the columns.
And then I want to select every column where first name has the value Caleb, for example.
Well, that's how we would do it. So this right here, this little where thing? Well, it's
best to have an index for that. Another thing we commonly do are joins, which is basically
combining two tables. And I'm not going to go into all the syntax for all that. Basically,
we have a table over here, users, and then a table over here, comments. And we want to
make a generate a new view, where we have all of the comments from a user and all of
the information about that user, we could have a new generated view. But we can join
these statements. And how does it know how to join it, it uses the I uses the key, right?
So if you have a primary key, and let's say we use username, so the key on drala key here. The key is username. Well,
how would we join it, we would say, I want you to take this table, and this table, and
then combine all of the rows, where the username is Caleb curry over here. And the username
is Caleb curry over here. So that way it knows all of these comments are from Caleb curry,
which we can assign to that user with the name Caleb curry, or you might often see it
with like an ID number. So it could be like ID six, take all the comments from the user
with the ID of six, join it with the SEC, take all the rows with the user with the ID
of six, join it with all of the rows with the user of ID six, and it combines them individual
rows for each ID. Within when we make a primary key, or just a key for now, we're defining
an index. So the database can easily access the order of our data and combine things.
So that is how kind of indexes relate to keys. Key is a type of index. There are all kinds
of types of index. Look up tables. So what is a lookup table.
Let me I have to kind of explain something else first before we're going to get into
this. So just kind of relax and take. Take a good look. I'm going to erase this so I
can have some room. So let's say we have a membership table or membership database, and
we have a user table or members table. And within that we have like a membership status.
Okay. And let's say there's like 10 options, I guess. So you could have like, bronze, silver,
gold, platinum, titanium, and then like not a member or like a trial member or partner or special member or member above all members.
I don't know I'm just kind of making stuff up. Anyways, some people will create what's
known as a lookup table, so they'll have a table over here with every single option for
the membership So we could have the memberships. And then within here, we would have, each
individual row is one of the membership statuses, right. So we have like 123456789, I'm just
gonna go at nine because can't fit anymore, nine individual rows. And then we so that's
one column, the ID, for example, which will mean we could use a different key if we needed.
But for this, we'll just use an ID. And then we have a column to say what the membership
status is. So we're gonna have like, gold, silver, bronze, platinum, and so forth. Now, we can have a
members table. And then a column within this member table, can reference the
key of the membership. So remember, like one or two videos ago, I talked about how keys
make connections between tables? Well, this is how that's done. Here's an example of a
connection using a key, what we would do is we would have like the member, the members,
user member name, or their ID, for example. And then we have their first name, last name,
address, phone number, billing type, whatever it is, and then we would have a column membership,
you see. And within here, let's just say for example, we didn't have this table, this was
gone, we just had this member table. Well, when we have 6000 rows within here, the membership
type would get repeated. Lots and lots of times, we would have like gold, gold, gold,
gold, bronze, platinum, platinum, platinum, bronze, glow, platinum, bronze, and go on
go on for all of the rows. And that would work. That's that that is a possibility for
your database. I'm not saying it's going to cause your database to explode, or you'll
get fired or anything. Well, unless you're told not to do that you might get fired. That's
a possibility for the database design. Now let's think what kind of relationship is this?
One member can have one membership, and a membership can have multiple people having
a membership. So it's a one to many relationship. The one side is the membership, you'd have
one of these, and then you have many people using that. So that means if we if you remember
from the relationships, we need to take the key from this one, and put it over into this
table. And now if we add this new relationship in, we don't have all of that repeating data
because all we do is reference one individual row. And there's some other benefits to that,
which I'll explain in a second. So now we can be like rather than gold, gold, gold,
silver, bronze, platinum, we have 111234 for all of our rows. And we still have individual
information like Caleb, Jimmy, Jake, Sally's Sammy, and Sarah. Well, when we do this little
connection thing, we have a new connection between these tables. We're taking the key
of the membership, which is this column right here. So here's our key. And we are taking
that key, and we are moving it over into the member table. So now this column right here,
within the member table, points back to the key of the membership table. This is known
as a foreign key. Now this will help protect the integrity of our database. That's because
there's only nine possibilities for the membership and if someone puts something else we could
flag an error. The other thing is we can have it to where it will not accept an empty value.
And then if for some reason we decide to change our like let's say we wanted to change silver
to like I don't know diamond well Then we can change it here. And because this value
is not over here, it's only a reference, it will automatically figure it out. Because
you see the key never changed. The key is still one. So one still points using one.
So this connection is still here, the value or I'm sorry, I guess it should have been
two. Whatever you get my point up, we can change the name of gold, we can make it epic.
If you want epic membership, well, now, we didn't have to change 6000 rows within our
member table, we only had to change one row within our membership table. And all these
connections stay the same. So the IDS helped create better connections between tables requiring
less maintenance for our database. And it also protects our integrity, because we don't
have to worry about incorrect values within the membership. Because it's all connected
back to the membership table, which we change from here. So we update epic, it updates every
single instance of epic within our entire database, or it gives us an error or something,
we can set it using what's known as foreign key constraints. That key foreign key constraint.
So because we're using what's known as a relational database, which is the type of designing we're
doing right now, we have relations tables, and everything is connected across the database.
It's not all stored in one individual table. But to protect our integrity of our database.
We create connections between keys. That way, we don't have to worry about incorrect data.
So what do keys oops, and don't match? What do keys do for us? Let's write that out. Well,
first thing, they protect our integrity, I made a video over integrity. So if you want
to check that out, be sure to do that. So they protect our integrity. How do they do
that? Well, we don't have to worry about only some values updating. If you remember, we
had the members table. And when we updated gold, and we named epic, it updated every
single row within the member table. So it protects all of our values. So updates, less
maintenance for us less incorrect data. What else is the key deal keeps everything unique.
And you might have saw that in the member table, we use the the key number one, multiple
times, we had three people with the member ID, I'm sorry, the membership ID one. So three
people had gold, for example. Well, it's still unique, because they're still only talking
about one individual gold. Over here in our membership we had gold was one of the options,
we could have three people. Alright, you see up for her arms, these three people can all
have a gold membership. And it's still unique because we're only talking
about one individual value with three references to that value if we got rid of these foreign
keys, and we just had to say what membership status they had. Well, now, this guy's going
to be gold. This guy's going to be gold, this gal is going to be gold. So now I have 123 times that gets rid of the uniqueness from
the key. So it protects our uniqueness improves our speed. So basically, improved functionality
of our database. Function I'm running out of room and it does a whole bunch of other
things. Basically, it makes updating easier, so less work for us. Because we don't have
to go through manually and check every value, we can just update that individual value.
You know. The other thing is that allows for added complexity. Long, hands getting tired.
Okay. Now you can even see all that. Okay, so here's some examples. I'll see, I'll show
you how we could say it allows for added complexity. Let's see. I'm going to redraw the example
we had, but I'm not going to draw super in depth. So don't, don't wait. Don't mean, don't
worry, it won't take that. We had a members, and then we had the membership. All right.
Now let's focus on this membership table. Let's make it a little bigger. Actually. What
we had earlier it was we had an ID, we had 1234, and so forth. And then we had the title
of whatever they are. So if you wanted to draw this better, you could put the column
headers, so we could have, like ID. And then we could say, name of the membership. And
then we could title the table, we could have it membership, membership. Yes. Well, what
we can do, we can have added complexity, because now we can add a new column. And we could say more about the gold membership.
So we could say, price. And we can have a monthly price of $60. For example, if we didn't do this whole lookup table thing.
And we put it all within our members table. Let's see how that would look. Let's try it. All right, let's have the members table. And
let's not even have a membership table. Well, now we have the guy with the ID, 789, and
three, and we'll have their names be Caleb. Tom Tommie trace. So those are the three people I was talking
about with the gold memberships. Well, what what membership to the heck, okay, gold, gold,
and gold. Now, how much does that cost them 6060 and 60. Just with this little example
of three people, we already have four unnecessary values. Because we have repeating data, we
have G and G twice more, we have 6060, twice more that eliminates the functionality of
our database, because we're having tons of repeating data. So you might think the best
way to do is is get rid of this price. Well, now we get rid of the complexity of our database.
And sometimes complexity is a good thing because it allows us to store more information. When
we use the common or when we I'm sorry, when we use the lookup table. We can just have
that as another column. And we can put more things we could have another column saying,
how long does this membership last? We have another column saying? I don't have a special
purpose, or what can use within our gym? Or what can we use? And are we allowed to bring
friends? What's the price for friends, we can do all that kind of stuff within this
table and just reference that within the other table. It's only a set number of options.
So we reduce the repeating data. If we wanted to do at all Well, we'd have to put each individual
comp column and we'd get tons of repeating data. You know what I mean? Yeah, so that
is an example of how because that would take forever. So that's very common. It's common
for things with a set number of options. So like membership type, there's only so many
options. You can have states, sometimes sometimes people do it for sex, male or female. Some
people do it for I can't think of anything else right off the top of my head. But anytime
there's a set number of options, they will often put a lookup table with the ID, the
name of that option or what that option is like gold, silver, bronze, or male female
or California, all the other states, Wyoming, Texas Russia, well, then we only have a set number of options. So that's
that's the, then we can put more information about it, we could say, male, and then, like
what the male is allowed to do on the website, or anything that allows for extra complexity
for our database. In the last couple of videos, we've talked about all of the characteristics
of keys in general, we talked about the word key, and databasing. Keys are the key to success.
Anyway, anyways, this video, we're going to be talking about specific kind of keys, because
all of the last videos were kind of concepts. But now we're going to be talking, how do
we actually implement that into a database, I understand that we want keys and we want
references from table to table, and we want it all to be connected. But how do I do that?
Well, the main two types of keys that you're going to hear are known as primary and foreign. Actually, we're not going to
be talking about these two kinds of keys. In this video surprise, we're going to be
going a little bit broad, more a little bit more broad. So when you're like a beginner and you're
learning database design, you'll probably learn that these are the two kinds of keys,
when, when you're want to learn more advanced database design, you'll learn about different
categories of keys. So before we get into these two, which if you guys haven't heard of those just hold
up, if you ever have those keep holding up. We're going to be learning about what's known
as a super key. Now, for some reason, I think this is one word with no space. And
I don't know why there's no space there. So if someone knows if like, what do you guys
want to know, let me know. Because I really want to know, it's driving
me crazy. The other one that we're going to be learning is candidate key. Alright, so
before, before we learn about the candidate key, let's just focus on the super key. So
I'm just going to erase this. Alright, so what is a super key? Well, a super key is
any number of columns, that forces every row to be unique. Now, if for some reason your
table is structured to where you can't have every single row be unique, you need to consider
adding some kind of column to enforce uniqueness, such as an ID, or some, you need to make sure
that everyone using the database understands how that table works, if it's a special case,
but generally every table, you will be able to make unique. Now, when you work with databases,
you'll often see IDs, so like user ID, this is a type of key, then you also sometimes
find keys such as like username. The difference between these is that this one's kind of just
made up for the sake of the database, user ID of 748. What does that mean? Absolutely
nothing. It's just a number. username actually has some real world connection. We'll be getting
into the difference between these in an upcoming video. And which one will be best to use or
which situations. But as for now, we'll just be using kind of natural practical columns
that are already in the database rather than IDs. But the same thing can apply with just like an ID field or a call. So let's
learn about the super key. Super key, like Superman, but it's a key, just like a giant
key that just goes and breaks through doors doesn't even use the handle. It just breaks
through doors. Anyways, let's learn. Okay, let's say we have a table
here. Big table right. Now I'm going to put some columns in this
table. That's not straight enough for me. Close enough now it just looks worse, whatever.
Let's say we have a user table because I always think that's the absolute easiest thing to
use. And we're going to have a username, email, password. Then we're going to have first name, middle
name, last name and then we'll have birthday so every single row Within this table is going
to need a value for each one of these columns. I know this
is really bad handwriting, but it doesn't really matter how it looks just understand
the concept. There now looks a lot better now. You're welcome. Now you can actually understand what's going
on. Okay? some instances, for databases, you will allow people to put in blank values.
So for example, if someone doesn't have an email, for example, we could make it optional.
That way, some people have no or no value at all other people have values. But for this
this case, I'm going to say everything is required. Because let's say you use your email
to sign in, or to verify your account. How do we make sure that every single row
is talking about one entity, and every single entity only has one row, everything is unique?
If we have a person and draw a little guy over here, right? And
he wants to sign up for this website. So you guys this website, here's his website, you put this information in, and
the information goes into a table. Right? he does, he probably won't actually have access
to the SQL, because it's just some random user on your website has access to your database.
Can I got some security problems, but anyways, what's going to happen is he will be entered
into this and all of his values will be put into these values box. So we have username,
like it, fi, email, DC at blah, blah, blah, calm pass, it's pi. First name is Caleb, real
name Daniel. Last name, curry birthday, oh, to 1390. So this guy, inserted all that information
within this database. If you can't read, it doesn't really matter. Now, when another guy
comes, or let's say gal, so this lady comes, and she's like, Oh, I want to sign up for
this website. So she signs up this website. And she puts her information in. And this
also goes in the database as a new row. So this is just the column headers. This is separate
from the actual data. The rows, though, they have individual values, she can put her values
in, I'm not going to write them all out, because you guys get the point. How do we know that
each individual row is unique? And that talks about one individual entity, in this case,
a user? Typically, whatever the entity is, is what the table was titled. So the entity
will be the user. How do we know that every single row is talking about only one user,
and every user only has one row the rule of one. We don't want Caleb to be able to come
in here, create a new account and make his username, cc and all the same stuff again.
Now, in some instances, we may be able to allow them to make more than one account.
Like I could make an extra YouTube account if I really wanted to. That would be okay.
So I could have the same different username, different email, same password, same first
name, same middle name, same last name, the same birthday, the only thing that changed
is a username and email. Now, the default, like what the table is trying to define has
kind of changed. We're no longer trying to define the person. We're trying to define
the user account. Because now there's two accounts and they're
completely separate entities over that kind of makes sense. But you don't really need
to worry about all the specifics. I'm getting completely off topic. So what were we talking
about 10 minutes ago, I think we're talking about super keys. It's any number of columns
that forces every single row to be unique. If you take all this information and compare
it with another let's let's create a new row. Let's say this lady here actually ended up
putting your information in her username is a her emails B passwords seat. First Name
is Hi, middle name is yo. And her last name is Hello. And she was born. Oh 316 72. That
is her birthday. Well, now, if you compare these values it is in is there they're different.
Not every single individual value has to be different. For example, this girl's last name
could be curry and that'd be perfectly okay. But all of the values as a whole have to be
different. So as for example, if someone came in here and put all of this information up
here, down here, cc ccof, blah, blah, blah, pi, Caleb Daniel Curie Oh to 1390, that is
not a unique row, and it would cause an error. So the super key could be all of these columns
right here. Now, super keys are usually not defined within the database. That's because
they're so broad, you have all this information. Of course, it's going to be unique, simply
because you can only use a username one time, typically the emails only one time. So if
someone comes down here and tries to use an MCC, well, then you already got an error,
it's not going to work. So all this extra columns are here. Kind of unnecessary, because
we can simply do that same thing with just the username. So a super key is any number
of columns, that creates a unique row, unique rows. A candidate key is the least number
of columns. That Can you see how new is a candidate? I'll explain why it's called a
candidate. And second, or in the next video or something. The candidate key is the least
number of columns. So for this example, we're not going to allow two people to have the
same username. We may allow them to have the same email, we may allow them that same password
first name, middle name, last name, and birthday, but not the same username, because then how
do you tell them apart? You can't because all of their information is the same. They're
exact clones to the database. And we do not ever want that within our database. Typically,
I can't really think of any examples where you would want duplicate data. But okay, anyways,
the candidate key is the least number of columns needed to force every row to be unique. In
this example, the username is enough to make it unique. So we can take the candidate key
and have it to be just the username, or I guess we could certainly call because all
throughout the, the rows are going to be unique. So the can the candidate is least, and super
key is any x. Now when it comes to database programming,
let me clear this up. So your brain doesn't explode anymore. It takes forever. I want to tell you guys, stop watch. Alright. And I just got my chalkboard
to listen to me and erase that stuff. When we're talking about database programming,
there's not really a time where we're ever going to tell your database that this is a
super key, because super keys are not practical. They are for designing your database. Only.
When I looked at that column, first thing I do is I think super key. Yes, every single
row can be unique. That's the first check. So you ask, can it can each row the unique? That's the question. That's the Define. That's what super key is
questioning, can each row be unique? And even better word? Can every row be unique, because
every single row within a table has to be unique. That's what the super key is asking.
Once we figured that out, if it can be unique, then we move on to figuring out the candidate
key. So now we ask how many columns are needed? Can you read that? Hopefully? So the first
question, can every row being be unique? Yes, that means we have super key. That's all we
have to worry about. We have to have to figure out which columns or anything like that, can
every row be unique? Always think about all the possible values. Is it possible for a
duplicate? If it is you got to figure something out? If it's not good, so every row can be
unique. Yes, check move on, how many columns are needed. Now you figure
out the least number of columns needed in order to create unlimited uniqueness forever
so no possibility of uniqueness and we are allowed to put some kind of like limitations.
We can put a unique we can use. It might be different from for different relational databases.
But if we use a unique index, for example, every single column, I'm sorry, every single
value within a column has to be unique. And that's, that's a requirement for people to
put in. So if it's not unique, we give it an error. That's how we force values to be
unique. So how many columns are needed? Once they figure that out, we have what's known
as a candidate key. So in the last, the last example, it was just one because we could
use the username. Now, the next thing I like to figure out is, how many candidate keys
do I have? I'm just going to use ck for candidate keys. How many ck do I have?
What does this mean? Well, we can actually have more than one candidate key. And the
last example, we had a username, we also had an email. Okay, well, email could also work
as a candidate key, because you could put a requirement that every account has a unique
email, some websites are cool with people using the same email more than once. Some
websites aren't usually they're not. So there might be a chance where we have more than
one candidate key. Or if rather than using the username, you want to use something else,
you could have it to where we can have, let's say, you can use your email one once, we could
have first name, plus, last name, plus middle name, or middle initial. So that eliminates
most possibilities of uniqueness. But there's still possibility that people have the same
name. So then you add, birthday. So now you have to find someone with the same
first name, last name, middle name, and the birthday. Very rare, still possible. So then we could add a third.
I guess one to a fifth column, we could make it. email, or we could do something such as
a dress. Alright, let's see, let's go with a dress. This could be a candidate key. Because
we have first name, last name, middle name, birthday, and address, they all have to be unique as
a whole. What are the what are the chances of a person living at a certain location born
on a certain day? Also, the UK what are the chances of two people having the same name
same birthday and living at the same place? Unless you had twins, and you named them the
same thing? If that's the case, shame on you. It's gonna confuse people. That could be a
candidate key now isn't the best candidate key? Probably not because we're using five columns. And it's still technically
possible to have a duplicate, but very, very, very rare. Right, so how many candidate keys
do I have? You figure them out, I like to write out the possible candidate keys. And
then we move on to the next step, which is defining the key that we will use for our
table. Once we have the candidate keys, we can choose one of them. They're called candidate. Because there's there's an options like you could have, you
could have your first candidate, you could have candidate key one, candidate key two,
and you've got candidate key three, this could be the username, this could be the email.
This could be that long, first name, last name, password, blah, blah, whatever you want it to be. Now, you have to decide which one will be
your primary key. We're going to be picking up from where we stopped last video, which
we were talking about candidate keys. So this video, we're going to be talking about primary
keys, which is the next step. So what is a primary key? Well, in the last video, we we
define some possibilities for candidate keys, something we could use as the main key it's
a candidate because you have to pick it to be our primary key. So we have a list of possibilities. One of
them was a username. Another was an email and then if you don't have something simple like this, you could do something like your
full name, which that would be like first name, last name, middle name, and then plus
an address. Which address would probably be broken up into like, Street, city. You know.
Like all the parts have an address, and then possibly your birthday. Or just dog. So here are just some possibilities for candidate
keys, we could have username, email, first name and last name. Well, now, once you have
all your possible candidate keys, you want to pick one to be your primary key. So how
do you do that? Well, once again, you got to make sure we look at the the rules or suggestions for keys. This is specifically
talking about primary keys. We want our primary key a unique, never changing
and never know. So username is never going to change, right. So that's pretty good. You
always need a username when you sign up for an account. So it's never know. And it has
to be unique because two people can't have the same username. So this would be a good
primary key, check out email. Well, it's you can make it unique, you can require people
to only have like, you can prevent multiple emails. So when you create an account, you
can only use an email that has not been used, that's an option. So that's possible, never
changing it Well, it depends depends on if it if your use your email, like a user account
username, that you sign in with your email and you have a password to well, then the
chances are, you're not going to be able to change it. Although now, you can probably
update your email and stuff. So it's possible to be never changing if you prevent people
from updating their main email. So possible, possible and never know you can require someone
to have an email. So that's possible, all three of these are possible. So this is a
possible loss for a dash, first name, last name, middle name, address, date of birth,
well, probably the whole group, as a whole will be unique, the chances of finding someone
with your same name, living at the same place as you with the same date of birth is almost
impossible. so unique, practically Yes, but possible, you can possibly have a duplicate.
So possible, never changing. Oh, well, people can get married, so their last name can change.
First Name can even change your middle name can even change, you can move, you can't be
born a different date, you can't be born again, like unless you become a Christian. But anyways,
never know, well, some people might not have an address. Either that, or they might not
have a middle name, or something like that. So this is probably not the best primary key.
So in this example, I'd probably go with the user name. And then you define that as your
primary key. And then you can use that to make connections between tables. So you have
a comment by a username, that's the connection. That is a natural key. And we'll be talking
about surrogate keys in the next video, if you know anything about that. But basically,
you can use your username as an Id rather like as a primary key. So you could have,
let's say, this is my username. This can be the connection for all of my rows by me. So
if I have a table for comments, table for users, a table for sales, will if I buy something,
Caleb curry connection. If I post, if I'm a user will connection, if I post a comment,
connection, it all kind of points back to the user table, because that's going to be
the parent of the relationship here. So this could be a primary key. So that's
a primary key. Now, this will also be indexed, because primary key is a source of is a type
of index. So you can do SELECT statements really easily with that and everything like
that. So that's going to be how you connect most of your data. Now, the other candidate
keys that we did not choose, they're known as alternate keys. An alternate key. It works.
I mean, it can be it can be it could be the primary key. But basically, it's all the candidate
keys that were not selected as the primary key. You do not you're not required to define
all of your alternate keys in your database. In fact, you don't even need any alternate
keys. But you may want to because oftentimes they will be something you're doing searches
for, or connecting tables or whatever it is. Alternate keys might be useful so you can
create an index on the alternate key. So I could create an index on the email For example, because I said that was a possibility.
Well, now, that can, that'll be an index, and it will probably be used because you might
want to do data by the emails like selecting everyone by their email, you know what I mean?
Maybe possible, basically, you can, you can do a select. And then you can choose the columns
you want to choose and then the table. And then you can say, where. And then you could
say, like, email, has the value, legit at awesome calm, because you have an index on
that it's going to work better. So if you have a really good alternate key, I recommend
you index it simply for good design. And it will help your database run faster. But if
you don't really want to index on it, then don't do it. Because that's just another thing
your database is going to have to maintain, it's going to have to update the index as
well as book sort of how if I have a book here, and I change the insides of the book,
well, then the index in the back is gonna change too. So alternate keys, you can set them as an
index, if they are good and useful if you will be searching for that kind of information.
So that is introduction to primary keys and alternate keys. Sir, I think I'm falling sorry, by the way, sir,
good. And then we have a natural. Alright. So we talked about primary keys in the last
video. That's as far as we'll get for defining a key. These are categories of primary keys.
So we define a primary key. And this is kind of like a descriptive term, we don't have
to define in our database surrogate primary key or natural primary key, this is more for
database design. To know like the difference between types of keys, hopefully that makes
clear sense. And if not, just stick with me. Okay, so Up, up to like all these key videos
I've been doing, I've been using natural keys, let's define a natural key first, it's very
natural definition. It's something that's naturally in the table, something you naturally
want to store. When you have a user's table, you naturally want to store the user name
and the email, those can be used as natural keys, because they fit all the requirements
for a primary key. And it's already in there, you don't have to make up something, you don't
have to think of a column to add just for the sake of having a key, we want to be able
to have a key for every single table. But sometimes, the natural keys not very obvious
or not so natural. So we want to try find a natural key when we're using natural keys.
Generally, when you create a database, so let's say this huge box is our database. Within
this database, we have a bunch of tables. And then within each table, we have a bunch
of columns and rows. See, I'm saying these are all connected in some funky way, with
relationships? Well, when we define a database, typically, I mean, maybe not always, we want
to try to either always use natural keys, or always use surrogate keys, which are just
made up keys. We don't want to kind of flip flop switching, I can't think of any other
words. Because we want to try keep it consistent. Otherwise, people using the database, they're
gonna be confused. Is this a natural key? Or is this a surrogate key? Is this something
that actually has real world value? Or is it just a made up number? So let's say we
are using natural keys, there are a couple problems with natural keys. And this video
will be explaining the difference between these two. And the next video, we'll be talking
about pros and cons of each basically. So natural keys are what's naturally already
in the table, something we wanted to store to begin with, right? The thing is they have
real world value. So your database might adapt, and the meaning of your keys kind of adapt
to which, once again, I'll explain that on the next video surrogate keys. They are a
column that's just added to your database, no matter what even if you have a good natural
key because remember, you want to either use all natural or all surrogate usually. So if
I decide to use surrogate keys, I'm going to give an ID to every single row within every
single table. So we have let's say we have a user table and then we have a sale table. Then we have a common table, all we do is we give every single
table, an ID column. So we have a user ID column and the say in the user table, we have
a sale ID column and the sale table, we have a comment ID in the comment table. So now
when we have like, let's look at the comment table in the comments, we're going to have
a column, we're gonna have a column for the comments ID. And then we'll need a reference
to the user that posted the comments. So we'll have the user ID as a foreign key, which points
back to the user table. So that's kind of how that works. Now we just have random numbers,
the ID is just a big number that has a feature known as auto increment. means every single
time you make a new row within this table, it's going to raise the ID by one, then when
you like, delete columns or something and you have gaps within your numbers, it doesn't
really matter because the number has no real world significance. Typically, surrogate keys
are kept completely private. No one knows the surrogate key, except for the people working
with the database, you're not told your surrogate key. So if I created an account on a website,
and then I put my username and my password, and then I go to like, I sign up, register
and do my email junk, and then I sign in and go to my user settings, it's not going to
be like user ID equals 45, or whatever, it's going to be private. The reason for that is,
is because the world doesn't have I mean, the, the number doesn't have real world, meaning,
therefore, it's private to the database only, if I started giving that number out to everybody
who has one of these IDs, or, or something like that, or if I started putting it on sales
reports, well, then I'm giving that number real meaning in the world, and it's starting
to become a natural key. So hopefully, that kind of makes sense. Natural is something
that's already in the database. surrogate is something we just add. The thing that's
kind of cool about surrogate is if you're struggling to find a really good natural key,
you can always use a surrogate key. The problem with that is you always want to try to be
able to naturally make everything unique. Like any user table, we have a username, that's
going to keep things unique. So even if we use a surrogate key, we can still index this
username. And that can be used. Sorta as if we assign it as a natural key. The only thing
is we're making connections between tables using the surrogate key. So for this table,
we could have a user ID, and then we could have a user name. For just an example. The
user ID would be the surrogate key. This would not be the natural key because we can only
have one primary key. And the primary key is going to be the type surrogate. So the
user ID is a surrogate key random number generator number, the username, we can index it and
use it like a natural key with when we search the database and everything. But we're not
going to use it to make connections between tables and references with foreign keys. So
hopefully that makes things pretty clear. In this video, I wanted to talk about all
the pros and cons of surrogate or natural keys. So basically, we're going to compare
and contrast them here. So over here on the left side, we got surrogate and then over
here on the right side, we got natural All right, we're gonna have a little battle here
and we're going to figure out which one's the best. Me as boxes to keep this up. Here. Alright, so what are some good things about natural keys?
Alright, well, first one of the first good things about what's really weak. One of the
first good things about natural keys is that you don't have to define any new data. If
you don't remember from the last video, let me just redefine them here. Natural keys are
used, or columns used as your key that are already defined within your table. They're
natural to what you want to store. surrogate keys are just kind of added like a user ID
or a car ID or a credit card ID or a comment ID, their surrogate. They have no real world
meaning so natural, not natural. Cool. So some good things on naturals, you don't actually
have to define a new data, any new data, I don't know if you could hear me, they're not
kind of stuttered on that. But anyways, you don't really have to define any new data because
it's already within your table. And you can use that as your natural key. So you're required
to store less information in your database. So you get smaller data, you get it, you have
a smaller database. Right? So that's one good thing. So what's the downside to natural keys?
Well, the downside is that sometimes you're not going to be able to find a really good
natural key. Because if you look over here, we want our keys to be unique, never changing
and never empty. Or No. Well, if we have a natural key, we have to find one that fits
all three of these. And it has to be a good one that would make sense to use as a as a
natural key, like, you might not want to use a combination of 20 columns to make natural
key. But that's one of the downsides to natural keys, you have to figure out what natural
key to use, and there's not always a great option. Now, optimally, you should have a
natural key, or at least something that can be used as a natural key within every table,
to best had to have the best design to ensure uniqueness, not just by a surrogate key if
you use one, but by naturally what's being stored. So like this example, if we have a
comments table, we could have a comment by a certain user on a certain web page or whatever.
And what what forces it to be unique? Well, the combination of that as well as the date
it's posted, or the time it's posted, that's going to be unique, because you can't have
two comments from the same user at the same time, on the same web page. It's not possible,
right? I mean, unless you like glitch the system somehow, which shouldn't happen. Well,
that's an example of using natural keys. The downside? Like I said, you might not always
be able to figure out what to use for your natural key. That one was a good example.
Because naturally, we could figure out a natural key, right? Follow me. The other thing is,
natural keys have real world values real world meaning, right. So you have connections to
real world, meaning. So for example, in the comments, the actual comment in combination
with the user who posted it in combination with the time it was posted, and the page
it was posted on, or any of those four columns to be used as a natural key, well, those have
real world meaning. And as your database develops over time, the database application might
change or be updated, or new meaning is given to the database and what it's supposed to
do. That means that your natural keys could change over time and have new, new meaning.
Or the actual values could change in some instances. And we do not want that, obviously,
because it has to be unique, never changing and never know. If for example, we make it
to where we do have to change the natural keys. Well, that would kind of be like crossing
this one out. And that's possible, like we could update the values of all the keys. But
that's going to require us to update all connections between tables. And that's going to require a lot of resources
from our server. And that's just a bad design in general. So those are most of the cons
of natural keys. So yeah, let's move on to surrogate right. So surrogate keys, well,
obviously, the first downside is you have to add a column to your table, no matter what
even if you have a a, for example, if it's naturally unique. Well, you might like if
you have a comment from a username, well, that username is gonna help ensure uniqueness.
And you still add a surrogate key when you're adding unnecessary data, because you're adding
a new column and you have to store all these numbers. One of the pros of surrogate keys
or is that they're typically numbers were oftentimes natural keys can be words or anything
like that. That's good, because typically, numbers are easy to work with, but not always.
Now, a couple other things. Besides the fact that you have to add a new data new column,
which requires you to store more data. It's that it can be confusing sometimes when all
you're working with is just combinations of numbers. And I have like, we have a column
let's say like komentar for a comments table, and we just have an eye we have the value
like 700 407,462. Well, this kind of clear, it's talking about the user with the ID of
747,462. Sometimes columns won't be very descriptive though. And that would be full into designers.
So make sure you name your columns, right. But when working with other people, you can
sometimes get confused exactly what References What, and you have to do a little bit more
thinking and check the actual database structure the way it's programmed, or however else like
that. So that's a couple things that are confusing. Okay, so now you're probably wondering, okay,
so which 1am I supposed to use? Well, actually, it just depends on what you want to do if
you prefer to use natural or surrogate keys. But typically, you'll want to pick one and
use that throughout all of the database. As a database really weird. There's like database,
data base rather than database. So it's like database anyways. So like, set that aside
to use surrogate keys, well, that means every column should get a ID, or at least a combination
of IDs. Natural if I decide to use natural ones, every column should try to be used as
a natural key, because if we mix and match, sometimes we're gonna have a surrogate key,
sometimes we're gonna have a natural key, and it's going to be super confusing, and
we're not going to know what's going on. So typically, you want to kind of keep one or
the other. The other thing is, I don't know what the other thing was. But yeah, typically
one or the other. Now, for my sake, I personally am going to use surrogate keys, although that's
not saying that they're better in any way. I just prefer surrogate keys, because it's
simple for me, because every time I make a table, I all I got to do is make an ID with
it. Like if it's a user table, all I got to do is make a column user ID, and boom, we're
done. That's all I got to worry about. And I don't really have to worry about ever changing
or ever becoming something different or anything, it's just a random numbers 7254. Well, that
has no real world meaning. Now keep in mind, though, that if you do use surrogate keys,
you shouldn't, you shouldn't have them have any real world value. So for example,
a student ID if we give these student IDs out to the students, that's not really a good
idea to use as the surrogate key, because it's actually an actual key, it has real world
meaning. So those are just some things and you can find tons and tons of stuff on the
internet about natural or surrogate keys. It's basically like a war between two database
sides. Were the natural keys. Oh, were the surrogate keys. You know what I mean? So yeah, don't freak out. Just figure out what works
best for you. There's some minor performance differences. But I mean, I don't really know
all of them. So yeah, believe it or not, I don't know something. I'm kidding. But anyways,
try them both out, if you want, look up stuff, look up, which one performs better, or which
one will work best for your application, or your database, because that's really what
matters. So this is a big subject. And there's a lot of important things. So I'm going to
be separating a lot of these concepts up into separate videos. So the next two or three
videos, this video and two others I think will be over foreign keys, and a lot of things
that have to do with foreign keys. Sorry, anyways, this video we'll be talking about
introduction to foreign keys, and exactly what exactly they do. Alright, so foreign
key is a reference. And what does it reference? It references a primary key. So that's it.
That's the most basic definition of a foreign key. Now, this can be a primary key in the
same table or a primary key in a separate table. So let's kind of see how this would
play out. If we do do some drawing, right? Let's, let's think of a class for a college
database. So this database has call has all the information for a specific college or
multiple colleges. And one table is for classes. We also have a table for instructors and for
users and anything else that's obvious. That's an obvious entity. So let's, let's look at
the class table. So we have a class table with this, draw it out here. Within here,
we could have a class ID which, like I said, we're just going to be using surrogate keys
for most of this. You can do the same thing with natural keys. And if you have no idea
what I'm talking about, just don't pretend I never say anything. So yeah, we have a class ID. That's an example of a
surrogate primary key We can also have a an instructor. You see, so we can have an instructor
ID, right. And we could have, let's say a room number,
because we could have, we could have a table for every single room, which talks about more
information about the room, or I don't know if that's a really good example. Or if they
would say we have a bigger building, or a bigger college, and we have a table for each
building. So that's probably that's probably a better example. So we can say building ID
to say which, which building is in. Alright, so here, here are three IDs. So you can assume
that they're all keys because almost all IDs are going to be keys of some sort. Which one
is the primary key? Well, this one right here, the class ID, this is the primary key because
it's the class table, every single row within this table is going to have a class ID, whether
it be 1234 712-800-4364, whatever it is, it's going to have a class ID every single row.
So every single class is going to have its own ID. We also have an instructor ID and
we also have a building ID. Well, since these are kind of unrelated to the class, you can
assume that they're foreign keys in this example, because we're not going to have an instructor
ID for a class necessarily by itself. This is going to be an instructor ID for an instructor. So that means we have another table for instructor.
So this is our instructors table. I think I'm spelling instructor right. And we have
an instructor in here. Instructor ID. Well, this you can tell these are a reference here, we can also have
a building ID. So this references another table. And that would be the building. And then we have a building ID. So I'm taking forever to write and I can't
even write nice. But that says building ID right there. And this references that there.
So you can see there's a connection between these tables. Now we'll get more into drawing
relationships better like rather than just using wines, we'll talk about more of that
in the future. But for now, we can see that these reference these columns, so the columns
point to each other. And also each individual row is going to point to each other. So let
me explain that a little bit more. When we have a row within our class table, let's draw
this table a little bit better. So I can kind of see how it would work. So let's say this
is an actual example of the values. Within this table, we have a class ID. And instructor
I'm just going to put I and then the building Id just for simplicity, heck, I don't know. Because
I don't feel like writing all that out. So we can have a class ID. And just just to make things more, just to
be more accurate to what a normal database would be, we could say like the name the name
of the class like biology. So we can have a class ID, we could have seven for value.
We could have the instructor ID could be 63, we could have the building ID of 16. And then
the name could be biology. Well, every single column is going to have column rules or requirements.
For example, these two columns here. Individually, this one is going to have a rule that says every single
value within this column. So as we go to the next row, the next value, every single one
is going to have to reference something from the instructor table because it's an instructor
ID. So that's one of the column rules. Now same for the building ID, we could say that
every single row has to reference a value within the building ID. All right, so that
means the row right here, let's say this row individually, so right here, the class ID
with seven biology references, the instructor with the ID of 63 and the building with the
ID of 16. So you can see that the column has a rule set to say that every single value
within this column, every single row is going to have to reference an instructor and a building
now Each individual row, we could say, Oh, this specific row references, the building
and 16, and the instructor 63. You see. So that's kind of how foreign keys work. Let me clear this out, kind of draw it out
a different way. Let's take a look at the, the building table. Okay, so let's just draw little squares to represent it. And let's
say within this table, we're not going to structure the columns or anything, we're just
going to say we have the building, 716, and 14. So these are three separate buildings.
And we could have names for the buildings and all kinds of other things. Now, we have
within the class, let's say this is the class table, right? And we have the class ID. So
the class ID would be like 6438 123, it doesn't really matter what number they are. And eight,
right. And now we're going to have a reference to the building ID. So let's say we have 777
14, right. So here's an example. So over here, we got the class ID. I got the building ID. And then we got the building ID. And then
we got more information about the building. Here, if we wanted, well, you can see that
there's a connection between every single value, every single row within this class
table has a connection with a building ID. So that the entire column building ID has the
rules that every single value within that column has to point to a value within the
building ID of the building table. Now, each individual row has a value that points to
a specific row, within the building ID. As you can see, these are all individual rows.
So let's get a little crowded here. We can see that three of these classes in this class
table, reference one building, and that's the building with the ID seven, and that can
be the room legit building 74, or whatever you want to call your building, it doesn't
really matter. So that's, that's an example of a relationship. Using foreign keys. foreign
keys are what keeps things connected. So let's say the name of building seven was
legit, because I really don't know what else to name it. Well, now we have the value legit
as its name. And we connect with the ID. So rather than putting legit, legit legit, and
having repeating data that could be easily messed up, for example, if legit, was changed
to legit with two T's or changes change the name completely? Well, now with this foreign
key, it's connected, so updates automatically. So yeah, that's kind of how foreign keys work.
Just keep in mind that every table has one primary key. Now that primary can primary
key can be a combination of multiple columns if you want, but it only is defined as one
primary key. For example, we can have the combination of first name plus last name,
this group could be the primary key. But we're not going to have the first name primary key.
And we're not going to have also the last name primary key, because that's to my two primary keys. And we can do that
we can have the combination of the two. But with foreign keys, you can have multiple columns
having foreign key relations to different tables. But keep in mind, each column can
only have one reference. So in the class example, we had a teacher ID, well that teacher ID,
here's the class table. And let's say the teacher ID is seven, six and five. Well, this can be a reference and we could also
have another foreign key we could have the building ID which can be 612, and eight. So
although we only have one primary key in a table, we can have multiple foreign keys,
referencing different tables, if we wanted it to be where the the, the instructor, I
think it's supposed to be an AI. So the instructor to where the class can have multiple instructors.
Well, that's a many to many relationship and we're going to have to design our tables with
an intermediary table. So hopefully that's kind of making sense, I just want you to realize
that the primary keys are what sort each individual tables, the foreign keys are what Connect
tables, and that you protect that integrity with foreign key constraints, which we'll
be talking about. In the upcoming next few videos. In this video, I wanted to talk a
little bit more about foreign keys, and a characteristic known as not null. Alright,
so this this keyword here might vary from relational database to relational database,
but it's probably something similar to this not know, which we learned about No, and we
talked about this over here, it's basically saying you, the database will not accept someone
not answering that column, for example. So if it's not know, you have to give that call
and the value every single row. So the rows are each individual entry within the table.
If you have the column characteristic of not know, for every row, it's going to be required
that it has a value. So think of like a fax number. Does everyone have a fax machine?
No, most people don't. So if I set it as not, no, and I didn't give it a fax number, then
it would throw an error and cause problems. That's, that's an example of how not all works.
Now, this is important when it comes to foreign keys. Because it has to do with what's known
as cardinality, which we'll talk about that when we start designing databases a little
more. But it's basically whether or not a relationship is required. So this is going to dictate what kind of values
we can put within our table back to the fax machine, if we have it to where it's not null. That means every single value, I mean, every
single row has to have a value. That also means that if someone doesn't have a value,
they might not be able to put any information in this table. So by putting this restriction,
you're eliminating all rows that aren't going to have a value, you're preventing them from
happening. You see what I mean? Yeah, so if the cases where you do want people who don't
have fax machine numbers to be able to enter data into your table, well, then you would
not want to use not no as a characteristic, because that's going to prevent them from
doing that. Now, this is important when it comes to foreign keys. Because if we require
a relationship, we said as not No. And that means every foreign key value for that column,
every single row is going to have to have a relationship for that. So one of the biggest
differences between primary keys and foreign keys is that primary keys are required all
the time, never changing. Never know. Right? Drop munchak, dang. Well, foreign keys are
a little different. Because we can have them to where they're not required, they can be
empty, because sometimes that relationship is not there. Or they could update like we
could, we could have a new relationship in this table, which we want. We don't we want
to eliminate as much possible updates as possible. But sometime that's required. And that's why
we use foreign keys to keep things connected. So when things are updated, they update across
the platform. Now, we don't want the primary key values to change.
But we could have the foreign key references change. For example, let's say we have a table
over here. And we'll just have it will be will have to be carbs, okay? Or whoops, have
the car, right. And we have two rows and have a car with the ID of six
and a car with the idea with the ID of 10. Right. And we have this guy over here. And
we have a table for him. What is making the user table and let's say for some reason,
we wanted to make a connection of what car he owned. Well, he might currently own six,
right? And we don't ever want the value of six to change and still be talking about the
same car. Let's say this car is this one over here. This is some flames. Right and this
one over here. We got a legit car. All right. So these are two separate cars. 10 talks about
this car six talks about this car. We don't ever want it to happen to where this number
gets up. Unit eight, or we change the primary key, and it's still referring to the same
exact car, because then we have a changing primary key. But with foreign keys, it can
change in the sense that, oh, I no longer own this car. Now, I own this car. So we didn't
have any primary keys updated. But the foreign keys, which would be this connection, right
here, did update. Alright. So that kind of is a little bit of a difference between primary
keys and foreign keys, primary key value should never change, foreign key values can change,
because references change. Hopefully, I didn't just lose my train of thought, I think I think
I'm going but kind of hit a blank here for a sec. Okay, so let me just illustrate this
in a college right. One of the important things between these tables that have a primary key
and a foreign key connection to that primary key is that they have to be the same data
type, which is the type of data whether it be int, or character or date. And basically,
it has to have all of the same characteristics like the collation or the character set or
the storage engine. Now, the only thing that can vary is the nominal characteristic. Because
if we have the primary key over here, well, we know that the primary key is never know.
So we always have not know, for the primary key, I'll just put an N for sure. The foreign
key on the other hand, it's not always required to have a connection. For example, in that
previous example, we had the person the user table and had this guy over here. And then
we have this car table of the cars that the user owned, right? He could have one car,
right? Well, he might not currently own a car, therefore, we don't have a connection
to this table. Therefore, this connection is optional. Or not required. Yeah, that doesn't even look
like English, but close enough. So let's think of a another example. If we
have a college, well, we might be in early in the semester where every single class before
people have registered, every single class doesn't have an instructor yet, and we're
still trying to find an instructor. Although we know that's the class that we want to have.
So that means we have an instructors table, we'll have the instructors over here, and
we have a class table. A class table is going to have a foreign key pointing to the instructor
for the instructor of that class. Unless the case of a many to many where multiple instructors
can teach multiple classes, then we would need an intermediary table. But that is not
the case here. You need to learn more about that check my relationships video. But for
now, we would have a class ID and an instructor ID. Right. And then over
here, we have an instructor at this it says ins instructor close enough right?
Now this is going to be a reference to this over here. Now what kind of values is this
going to accept? Let's give some examples. Let's say we have here's a row, we have the
class ID seven, the instructor ID at three, and we'll add a call and we'll make the name
of the class. We have a call minutes math. Right? And then we can have another value
within here. I think I'm off screen now. Then we can have an instructor over here. His name,
his ID could be seven. And his name could be shake. Right? So this class ID is seven yummy changes in
the gonna get confusing. Let's make this 16. Alright, so this class ID seven. And it's
by an instructor eight, which we don't actually have over here yet. So that means we either
have another row or we have an error which because we never want that to happen. So let's
say we have another class. Got any room. Okay, we have another class over here. And it's
it's numbers eight and it's taught by Caleb right? So now we have an instance of a class.
We got the class ID seven, the instructor ID eight, and the name math. So the instructor
ID points to this Walker, this instructor over here. This is the parent I'm sorry, this
is the parent This is the child because this is a foreign key reference to the parent.
So that means This relationship is a foreign key. Now, with the not normal thing, if we
made it to where it's not know, if we added the characteristic, not know, or that means this row is going to have
to have a value for the instructor ID, that means every single value for instructor ID
is required. What does that get rid of, that means it's impossible to put a class in this
table that does not have an instructor ID, that could cause a problem if you have a class
created, but you don't have an instructor yet, or if the instructor drops out, and you
have to find a new one. So this one might be a bad time use not know, if we got rid
of that. Well, now we could have a class with a primary key, but no foreign key connection.
Now we have a class with the idea seven and it's met. And the the current teacher, we
don't have one. And we still have this guy over here. He's not teaching that class, but
he's still in the system. Jake's still in the system, the class is still on the system.
That is a good example. So for this one, not an old might not be the best idea. Other examples,
having nano would be a good idea because it forces that relationship to be there. You
only think of another quick example. Real quick, quick example real quick. If we had
two, two entities, we had a card for a credit card company. So we had the card. And then
we had the person holding the card. So the person who bought the car or getting the card
from the bank, and he's using it to buy their groceries and their gas, and they're breaking
up 1000s of dollars in debt. Well, that person would have relationship with this card, because
he's the card holder. That's the car. Now, if if we had a table for this, we probably
have a card table, and we'd have a person ID, which would reference the person table,
right? If we set this to not know, that means every single value or every single row within
this card table is going to require a card owner. If we had if we got rid of this, that
means we could have cards in here that were outdated, that don't currently have an owner
because there's no connection, we broke that owner, or new cards that haven't been given
given out yet. So if you want this table to just be for cards that currently have an owner,
so we can have our own cards. Well, then we could have a a nominal column with the same
data type as person ID. Now every single value is going to have a person ID Does that make
sense? So that's kind of the introduction of not knowing how that works with foreign
keys and how sometimes you will want them to be not known. Other times you want them
to be nullable or not required. I got a bunch of writing on the board. But don't look at
it yet. Because I just want to talk to you guys for a minute. I just wrote it. So you
won't have to watch me write like 50 100 words is that to be boring. So instead, I'm just
going to talk instead. So much better, right? Anyways, last video throughout this series,
we've talked about foreign keys. And as we know, they keep connections between tables
in a database. So table one, table two, boom, foreign key, connect from this foreign key
references the primary key of this table over here, that's kind of how foreign keys work,
they protect the integrity of our database. They keep everything consistent. Think about
if if there was no foreign key constraints, which keeps keeps it updated and everything,
then we would have to manually update all connections between tables. And that'd be
a big waste of time. And it just be easier just to store everything in one giant table,
which both solutions. either manually doing it or storing everything in one giant table
is not going to work. So we need foreign key constraints to protect the integrity of our
database, right? It's called referential, referential integrity,
it's big words. Alright, so now this concepts these words might be specific to a certain
relational database management system. So these are from MySQL, but if you're using
a different relational database management system, yours might be a little bit different.
But in general, the concepts are pretty similar. So you're not wasting your time if you don't
use MySQL, because you need to understand how foreign key constraints work. All right,
cool. So first of all foreign key constraints, make sure that if you update the parent, the
children are going to update or cause an error or something like that, it's up to you, you
can make the decision. And it's also going to burn prevent you from creating children,
when there's not a parent that are that with that primary key. For example, if we have
five users with the user's ID, 2468, and 10. And then I make a comments table, and I add
a row that's like, from the user 745, well, that person doesn't even exist, so that's
not going to work. So that's why foreign key constraints are important, because they once
again, protect our database, and make it pretty much self managed, rather than us having to
manually update everything. So let's begin. Alright, let me get a piece of chalk. This
one looks nice, getting pretty low. This is the biggest piece of chalk I have left. Alright,
so we when we create SQL statement, we can add these two key keyword phrases here on
delete and on update. This refers to the foreign key constraint. So when we, but it's talking
about the parent, so when we update the parent, we want the kid we Superfly. When we update
the parent, we want the children to do something. When we delete the parent, we want the children
to do something, that is what these keywords mean. So these keywords, it's talking about
the foreign key constraint. And it refers to the parent. All right, so let's just draw some basic tables
just to kind of keep things I'm not going to give any specific examples yet, I'll just
kind of draw things out. So let's say we have a parent here. And as you learned in previous videos, the
parent is the primary key, right? And then let's reference this primary key with a child.
Let's not draw this line yet. And then we have a foreign key. This, this primary key
is a column within the parent. And every single row has a unique value, the foreign key is
a column within the parent, and it references the primary key. So the say the value 400
in a row could reference the value 400 in this row, and a row of this table, you get
I've explained it enough. Now let's just make another child table. So these are both foreign
keys that reference the same primary key. But they're two separate tables. So yeah,
we can have a user table and then we could have a, a comments table and an Orders table,
for example. They both are bought or commented by a user. So these foreign keys reference
primary keys, so every single value within the table of the children within the foreign
key column needs to be a value that reference that is present in the parent column. Now,
these these values right here, these options refer to the parent. So when we delete the
parent, or when we update the parent, we want the same thing to happen to the child or whatever
the options are, and we'll talk about the options in a second. So that's kind of how
these work now just to kind of clear things up I'm going to erase these words here just
because it's getting a little crowded you know, I want it to be peaceful. Okay, so this
is kind of how it's working foreign keys pulling back to the primary key these options refer
to that what what happens to the parent? Now these these options right here these refer
what happens refer to what happens to the child Okay, now it's getting real confusing.
There's arrows everywhere, like graphs and diagrams and but you'll get it Don't worry
restrict. You might also hear as no action is well at least for my SQL in different database
management systems. These might be different, but anyways, restrict is going to throw an
error. So if we try to like, we can give we can give one of these for each option. So
on delete, we can either give restrict, cascade or set no or No. on on delete, we can have
it restrict cascade or said no to To add more arrows, you know, but let's say we're working
with delete right now. And we wanted to, we want to use restrict. So within our SQL statement,
we could say on delete, restrict the on delete, restrict, and that is going to give us an
error whenever the parent is deleted. So if I tried to delete the user, with the ID, I
don't know I'm writing all that out, let's just say the ID of 504. And then this child
has a row that has 504, within the foreign key column, well, then it's going to throw
an error, and it's not going to happen, and the parent will not be deleted, not gonna
happen. So that's how cascade on Delete works. I'm sorry, restrict on delete, now restrict
on update if we updated that parent and change their primary key. So let's say we change
504. And these, these children both reference 504 with the foreign key, and then we change
this to 508. And it's not going to work. And it will go back to what it was originally.
Because we have it restrict, we're not going to let that happen. You see. All right, what
about the next option, cascade cascade, we'll do whatever we do to the parent to the child.
Let me clear this up a bit is confusing me. smearing chalk all around, you know, totally
helping. Alright, so now if we cascade on delete, that means if we delete the parent,
we're going to delete the child as well. So we delete 504. And both children are deleted,
boom, child deleted. So now 504 doesn't exist at all. So like, for example, if this was
a comments table, and we had a comment from the user with the user ID of 752, and we deleted
752, the user will then obviously, all of his comments by him should be deleted, too,
because we'd have a comment by user that doesn't exist, which does not, which isn't supposed
to happen. So cascade on Delete will do that on update is, if for some reason, we updated
the primary key, which obviously shouldn't happen, because primary key should be never
changing. But you still want that protection on your database. On update, if we have cascade,
if we change like 504, let's say these people exist again. Skinny, right? So it's because, okay, so these people exist, again, 500, for 500
for these references, 504. And if we update this to 508, well, that means all of the children
will update as well. So now, it connects within within different tables. Finally, we have
Sentinel. And what that'll do is it will basically just set the children as normal for that specific
value. So if we deleted the parent, that this is just going to get rid of the value, the
row should still exist. But the actual foreign key reference, it no longer references anything,
it's empty. So there's no it's not referencing anything. So the column for that row. So like
the, let's say, we have a comment. And it's a comment from Billy Bob with the ID of 504.
If we delete that, whenever we're going to have a comment, with no poster, or no comment,
or at all, it just no value. That's all said no one will do for us on delete an update,
same thing, if we change the ID of the parent changed it from 504 to 508. Well, all of the
children are going to be set to no value as well. All right, now there's one important
thing when he was set know that children have to have, they can't have I should say, the
not no characteristic. We talked about that in previous videos. I'm just gonna put an
end for sure. If this is not no, and we update, if we have on update set, no. And we update
504 to 508. Can you even I've ever gotten this off this whole time? reset five or four
or 508? I think you can see that one. You can we change five or four or five away? And
this is not No, it's gonna be like, No, we can't do it because we can't set it to No,
because it has the nominal characteristic, which means every single row has to have a
value for that attribute or that column. So I'll give an error. And I'll go back to how
it was. So that's an introduction of foreign key constraints. Sorry if I talk fast, but
yeah, cool stuff. It's important stuff. So learn it, study and learn it for your specific
relational database management system. In this video, we'll be talking about simple
and composite and compound keys. These are categories of keys. So like when you define
a database, you can say, this is a primary key, or you can say its foreign key. But you
don't have to say, Oh, this is a natural, simple. Primary Key, like those are just categories,
you have to define that within our database. So these these terms that we're learning or
more for design sake, so you know, the best design for your database, but you don't actually
have to define it, right? Like, for example, this piece of chalk right here, you can define
this as a white piece of chalk. Now, what kind of white piece of chalk? Is it? Well,
it's x brand or whatever, you don't have to define that when you're telling someone Oh,
here's a piece of chalk. That was the worst example ever. Moving on. Alright. Simple and
composite. Okay, so simple means that the key consists of one called composite means
it consists of two or more columns. This is most common with natural keys, because natural
keys can be a combination of multiple columns, whereas a surrogate key is just a random number
one column. So surrogate keys are simple composite keys or multiple columns. So for example,
we can have a first name plus last name, plus email. This could be an example of a composite
key because this whole group as a whole is the primary key. So we could have Jim, Jake, and legit are awesome at whatever's, blah, blah, blah.
And then we can have another another one with the same exact name, Jim, Jake. And we can
have a different email, these two are still separate, they're unique. Because the combination
of the three are unique, Jim and Jake, repeat, but the email is different. So these as a
whole, are different values for primary keys. So that's an example of a composite primary
key. The reason is, is because it consists of three columns 123. This is an example of
a composite key, a composite natural key to be specific, because we're just we're using
things that are already naturally being stored within our database. Now let's think of a
simple key, natural one, or username would be a good one is the neck username can be
something that can be used by itself as a key, it's simple, because it's only one column.
Pretty simple. I know. Alright, so I want to talk about another term you might
run into. And that is compound. Okay, now, I want to clarify that some people and some
relational database management systems don't clarify the difference between these two composite
and compound terms. And they just use them interchangeably as meaning a key with multiple
columns, some people go a step farther, and break these up into two separate terms. So
we're going to be defining both of the terms, but you might see them used interchangeably.
So don't be so super technical about the details. And there'll be like correcting people, Oh,
hold up, that ain't a composite key. That's a compound key, the because most people are
just gonna look at you like you're an idiot, and not really care. Alright, so anyways,
moving on. A compound key is basically, when it's a call, it's a key that has multiple
columns, and they're all keys themselves. So this is the most common example. This is
for intermediary tables. So think about like this. We have a table used to store information
about videos, we could say it's a video table. We could also have another table here, we
can have a user table. Actually, here, let's let's do it this way. We're gonna have a comment
state. Okay. And then within this comment state, we're gonna have a column of what video
they're commenting on. You see, well, this is a many to many relationship, right? So
this this might not work out well with database design, because one user can comment on multiple
videos. And then one video can have comments from multiple people. So the best way to do
this rather than having two separate tables like this We would create a video table. And
then we'd have an intermediary table. And this would be video comments, or user comments.
You see what I'm saying. So we get a connection between the user and the video to create an
intermediary table. And it's split up with two keys. So we have the user ID, and then
the video ID and then we could have another column for the message, or the date or whatever
else. Well, you can see, if you don't know what I'm talking about, go see my videos over
designing many to many relationships. But otherwise, if you know I'm talking about moving
on, you can see that the entire key itself, every user and video ID combination has to
be unique. That might, that might be a bad example, because a user could comment on the
same video more than once. So this is probably not the best example. Let me think of a good one. But anyways, this would be a an example of
a, we could add another column in here to make a unique, we could have the date and
the message. So the message has to be unique, and the user ID has to be unique. And the video ID has to be unique.
That'd be a composite key, a compound, a compound key, missing, for example, real quick. So
a good example of this would be for college, we could have user we can have a student,
student table. And we could have a class table, right? So this table holds information about
the student, this information table holds information about the class. Now, how do we
show enrollment, should we make columns for every student here now, because it's a many
to many relationship, that means we need an intermediary table between the two. So this
is a good example, sorry about my bad example in the last one, but that's a good example
of a composite key, because the two columns themselves wouldn't be enough. So we'd have
to add another one, which is not a key in itself. But for this one, we could have a
column, we'll name the table, student classes, or the student class, whatever you want to
call it, student classes. And within here, we could have the IDs, student ID, and class
ID. Now, this combination between student ID and class ID must be unique. So let's say
we have the value seven and four, seven and six, seven, and seven, that would work. But
if we had something like seven and four, again, we'd get an error, because that's already
in there. And that's not going to work out so well. So the combination has to be unique.
And these are also both foreign keys, which point to another table. So both of them are
keys. And the primary key is compounded. Because we have the student ID, and the class ID as
one giant ID or one giant key, I should say, because the combination has to be unique.
We can repeat seven on this side. And we can repeat like six on this side. But we can't
repeat the combination of seven and six, they have to be unique as a whole. So composite
keys. That's when you have at least one is a is not a key in itself. For compound, they
all have to be keys. So for the composite. Let's look back at the the example we gave
in the last one with the videos, the video comments, let's say this is the intermediary
table blown up just so we can see it real big. This is the user ID. And then we have
the video ID. Well, this isn't sufficient to make uniqueness because a user could comment
on the same video more than once. Thank you for replying to comments or something like
that. So we need another column, we could add the timestamp or we can just say the time
column. And this is going to keep a unique time because you can't comment on the same
video at exactly the same time twice. Right. So that would ensure uniqueness so we can
ensure these three as a whole are unique. That's an example of a composite key, because
at least one column is not a key in itself. This is a key because it points to a user's
table. This is a key because it points to a videos table. This is not a key because
it's just a column for That intermediary table. So that those are the three terms I want to
talk about in this video. One other thing I want to talk about real quick, I'm not going
to say what the best technique is. For this, I'm just going to say what some people do
with an intermediary table, some people will give it a surrogate key on top of the compound
key. So for example, with the college, keep switching back and forth between examples,
I'm probably confusing you, they might add something, such as enrollment ID. And then we have the student ID. And then
we have the class ID. These are both foreign keys. And they might use this enrollment ID
as a, just a surrogate key for this table. Although it's not a requirement. For certain
relational database management systems, some relational database management systems, or
frameworks or something, are not going to be able to work well with composite or compound
keys. So you might need to have a simple key which in that case, you could still have the
idea of a compound key. This group right here, the student in the class, didn't have that
index or whatever you want, or you can just enforce uniqueness with that. But then you
can use an enrollment ID, so you can have like seven. And then you could have student
six in the class four, and then you get eight, with the student four in class 12. And then
you can have nine, with the students 16 in class four, and then we could have 10, with
student 16, in class, 12, and so forth. That's just an example of what some people do. This
video is going to be a quick review, I'll actually be using my notes here just to make
sure I get the main everything because I'm not going to try to memorize like 10 videos
or content has this crazy, crazy eye. But anyways, first thing we started with was super
keys. So a super key was just any number of columns that make ensure uniqueness, but then
a table. So we have super key. Which I still don't know why that's only one word, which
just confuses the heck out of me. So someone please explain that, then we had a candidate
key. Now the difference between a super key and a candidate key key is that a candidate
key is the least number of columns used to enforce uniqueness. Now you cannot you can
have multiple candidate keys if you like a certain group. So if that's the case, you
have to pick one as your primary key. The primary key is the one you select as the the
main key for your table, all the other ones you can assign as alternate keys. Which, if
that's the case, they still can be unique, you can still enforce uniqueness upon those,
but they're not used as the main key within your table. What else? Alright, well, then
there's, that's pretty much the all the ideas with finding a primary key. And then when
you reference that in another table, you have what's known as a foreign key. foreign keys
reference, a primary key is a connection. That's how you make connections between tables.
If you have a user user table, and then you have a is a comments table. Well, the comment
is posted by a user, so you can reference that user in that comments table. And we talked
a lot about that. So I'm pretty sure you guys got that pretty well. Now, this is pretty much all you got to worry
about four primary keys and foreign keys, you just got to worry mainly about these two.
These ones are less important about finding and figuring out. These two are essential.
I'm not saying these aren't important, they're important. But really defining a super key
is kind of pointless when you know there's the candidate key you don't have to say, Okay,
well all of these define uniqueness. That's one. That's one super key. All of these define
uniqueness. That's another super key. Oh, finally, we're down to the correct size for
a candidate key. That's our candidate key. And then I'm check marketing By the way, just
so you know what that is. And then oh, okay, we got a total of 10 candidate keys, five
super keys. Now let's find a good primary key. Oh, this one looks nice. We'll use that
one we'll set the rest is alternate keys. That's just complexity that is unnecessary
when it's easier just to say, oh, there's a candidate key. It's already the smallest
that we need. We'll use that as our primary key. And now let's see if we have any other
alternate keys. We don't have any good ones aren't too bad. It's fine. who really cares?
Oh, we do have some good ones. Oh, yeah. Let's set those an index and make sure they're unique.
And we'll use this for some searching later on. That's an example of how you would find
a primary key. You don't have to make this manual labor for like 20 hours to figure out
which primary key to use. Alright? Then you reference that primary key in other tables
foreign key. So that's why these two are the most important foreign and primary, those
are the two you should focus on the most and memorize, okay, the alternate candidate super
key, and they're kind of important, but not as important. Alright, so let's take a look
at primary for. let's just, let's clear this out for a second. Alright, Rob Merritt. Now, there's some categories, dude, I dusted
now it's like I can breathe. There's some categories of keys that we can talk about.
So the first one was surrogate and natural. So surrogate natural keys. a surrogate key
is just a random number that has no real world value, or no real world meaning, just like
an ID, user ID, student ID, caller ID classroom ID, store ID, sale ID, record ID, song ID, picture,
ID, whatever you want it to be, I don't really care. So long as it's an ID, and it's a surrogate
key, then, yeah, yeah. I don't know what I just did there. But anyways,
natural something that's already naturally in the database. And you just define it as
your key. Now, typically, when you want to use keys in your database, which you will,
because it's your relational database, you will pick either surrogate or natural keys,
and you will use them for throughout the rest of the database, you're not going to switch
from surrogate and natural, you're not going to be like, Oh, this one has a natural key,
we'll use natural, but this one doesn't, and we'll make it surrogate. That's a bad design,
you should be able to enforce uniqueness by the columns that are already there. And then
you can add a surrogate key if that's what you want to do. And if by any chance, you
cannot possibly define uniqueness without a surrogate key, you're pretty much required
to use the surrogate key. But that situation should try to be avoided. But there's times
when you literally cannot find a natural key that will be sufficient. So you guys kind
of follow it. I mean, surrogate keys, random numbers, natural keys already in the table.
Cool. Simple. Speaking of simple, we'll talk about the simple key. Alright, so we got simple.
We got a composite compound. Okay, so simple is basically a one column
key, composite and compound, our multiple column keys. We talked about those in the
previous video, I believe. Yeah, so that's pretty much all the categories of keys. The
when you define your primary or foreign keys, you're not gonna have to specify if it's a
surrogate natural, simple composite or compound. This is for our sake, to design the best way,
because it's, for example, do you want to use composite keys? Or do you want to use
simple keys? That's a good question. If you decide you want to use simple keys, which
by the way, surrogate naturals kind of thing you do throughout your entire database, you
can kind of switch back and forth from simple and composite. For example, you could have
a user's table, and we could have a look, I sorry, students table, a class table, and
then we're gonna have a, an enrollment a with the students taking certain classes, we could
use a week, I guess we can use a compound for this table. And then we could use simple
for these tables. That's something you could do. But you should still use either surrogate
or natural, just pick one and use it throughout your database. Hopefully, I'm not talking
too fast, because I'm going pretty quickly. If I am just let me know as I see a column
A couple inches, come down a couple decibels, because kind of hyper, okay. Now, the other
thing we talked about was foreign keys and foreign key constraints. Well, the main kind
of foreign keys, the main kind of foreign key constraints, were on update, and on delete,
these protect our integrity of our database. You define these, whenever you make a table,
when you when you use the CREATE TABLE statement or create or whatever your relational database
management system does to create tables, the chances are, you're going to say that you have a foreign key. I'm just gonna put fk for short, and then you have
a constraint and you can usually name that constraint. So you can be like blah, blah,
blah, and name it whatever. And then we could say on update, or on delete, and the options were the ones
I thought you were do nothing or basically thrown air or we could update the children
or we could basically get rid of the value for the children. So that would be restrict
cascade and set. No. But your relational database management system might use something a little
bit different, which is fine. Yeah, that's, that's about it. Yeah. Yeah. That's pretty
much it. Cool. Cool. So yeah, that's a summary of keys. So yeah,
well, that was a ton of stuff. So there's a couple different names for this.
So basically, it's just a standard. It's a standard for drawing databases. And the couple
terms you might hear, or acronyms I guess, e AR, model, E, AR, D, or E AR model, or just database design, whichever one's easiest.
But basically, these are some terms used to refer to a method used to draw out your entire
database structure. These videos have been drawn tables and lines and drama, drawn tables
and lines and stuff, and all kinds of cool things like that, but actually haven't officially
designed any database. So what we're going to do now that we've learned most of the concepts,
the only thing we haven't really covered is normal forms, which we'll be getting into
those. But anyways, and I'll make them easy, don't worry. So what we're going to do is
we're going to learn the standard for drawing these databases, we're going to practice with
that. And then we're going to learn the normal forms. And then we're going to design databases
for a lot of stuff oriented. So we're going to be getting into the standards of drawing
databases. And obviously, the standards kind of rough. I mean, there's, there's not a specific
way, you have to do everything, because that's annoying and dumb. So what we're going to
do is we're going to be drawing pretty simple things. And I'll just be kind of illustrating
kind of how that works in this video. So what this means is enhanced Entity Relationship
model, enhance relationship diagram, enhanced relationship model. Yeah, I think that's what they stand for.
I don't think it stands for relation. I think it stands for relationship, which there's
a difference, as you should probably know, by now. A relation is a table, a relationship
is a connection between two tables. Yeah, so anyways, yeah, so we're gonna be talking
about these design techniques are probably this call and like, er, D er, er, er? Or,
who knows? But yeah, the way you do it is you define your database structure. So anything
that is DDL pretty much data Definition Language part of SQL, we talked about that a long time
ago. We're not going to define the actual values. For example, if we have a table for
users, we're not going to put in a row. Oh, Caleb curry with the password of sweet pie
is delicious. 24. And the email of Yeah, yeah, yeah, yeah. yeah.com and the telephone number
of 100 you wish or anything like that. We're not going to put anything like that in there.
We're just going to put the columns username, password, email, telephone, three d that is
so cool. Okay, anyways, let's get started. So to draw a table, database table, you just
draw square, it's like super easy, okay. Well, how he would evolve and so easy, right. And
obviously, doing this on a chalkboard is not very convenient, because for one, you're limited
in space. And for two, most of you guys don't have chalkboards. And for three, I don't know
a third reason. But most of this is actually done on a computer. And we will be doing that
hopefully soon. So stick with these concepts we'll be getting into on the computer design
for whatever reasons. And obviously, as I make new videos in the future, that's what
I'll be doing. So for now, we'll learn the concepts. And you can download Entity Relationship
design programs, such as MySQL Workbench, I made other ones like verta bello or something,
I don't remember what it's called. And a couple others, which you can design for any relational
database management system on that one. So go check out my other videos, or my playlist,
I think it's under reviews or something. But I don't know, look up database design on my
channel, see what I got. You can also just look up er, er, er D relationship, or design.
And you can get all kinds of apps and programs to help you design your databases. And then
some of them have what's known as engineering. So you could forward engineer your database
design, and it will basically just program your database for you. And it's super easy.
Yeah, but I'm getting way off topic. So let's get back on topic. Okay. So here's how we
draw a table. What we do is we give it a table name. So you put the table name either right
here in the in a complete column like that. or miss right above it. So let's put it up
above it right now. So this is a user table. And then what we can do within here is we
put the columns this way, vertically, when we write them horizontally, but we list them
this way, we're not putting them across here. Because if we put them across here, we will
assume that we're going to have row value, like Caleb, password, and his awesome email
is legit, or cool, and then the password already set password. But we're not doing that, we're
just going to put the columns. So the first column we can have is user ID, because we're
just going to use a surrogate primary key in this example. So we're gonna have user
ID. Now, when you get into it, you can also put other things like data types in here,
and any other restrictions you have, such as not normal if you wanted. But for now,
and for most of the design for design concepts, I'm not going to do that, because I'm going
to be writing the same thing over and over again. And you kind of only do that when you're
finalizing your database. So you can kind of worry about the data types later on. But
for now, we're just going to get the columns figure that out. Good. We'll go from there. So user ID, username, the password, and so forth. Hopefully, you guys can read
that. Probably not. And then when we do if we want to create another table, guess what
we do? We just draw another box, right? And we connect these with lines. And we can, there
are certain ways to draw lines that represent different linear relationships. Like that's
one way to do it. Or we're gonna have like, yeah, I mean, you get the point. Oh, I found a nice big piece of chalk. Cool. Alright, so that's how you do that. Now, that's
pretty much all the basics of entity relationship diagram drawing, just remember, we put these
in here, the columns. And what we can also do if we wanted, we could put indexes down
here, we could put like index types. And then we could also draw our relationship types
with different lines. And we can draw our foreign keys by either putting them down here
or doing some other kind of thing. Now, there's also other conventions you might have heard
of, or have used, like UML, and other design techniques. And I'm not saying those are bad,
I'm just saying this one super easy for beginners and for experts. We actually talked about this a little bit, but we haven't really designed it or anything,
because now we're talking about actually drawing out databases. cardinality is basically the
relationship type between a row of one table and rows or a row of another table. So I thought
of the the best example I have for this is a credit card database. So like, basically,
a credit card company has a table for credit cards, and then a table
for card holders or the people who order the credit cards and use them to buy things and
basically their cut the credit card companies customers, I guess. So I will draw those tables
out, just so you can see this, I'm not going to draw them or vague or anything. But just
so you see how the connection works. So we have a card holder. And then over here we
have the card. And there's some kind of relationship between here between these two tables. Now,
the only two possibilities for cardinality are one, or many. Obviously, for both sides,
you can have one too many, one, too many, or many, too many, those are the only three
possibilities. Now, another thing to think about when it comes to many to many, that
might even you might even consider that as not a possibility because you'd have to have
an intermediary table, which we talked about that when we talked about designing many to
many relationships, when when we're talking about many to many relationships here. It's
logical, which which What I mean by that is, we're we're drawing it as a just like this,
and we would say many, to many. And then when we actually implement that in
a database, we would have to have a cardholder card holding table and then the cards table
that would be how to draw a many to many relationship. So hopefully that kind of makes sense for
that. So in reality, when you're actually drawing out finalized products of databases,
the only possibilities are one to many, or one to many, because even if you have an intermediary
table this is good. Going to be a one to many relationship and then a one to many relationship.
And there's certain way to draw those out. That's what I'm going to be drawing right
now. So I wonder many, it's pretty simple. The one side, all you have is a straight up
and down line like this, the many side you have this, this thing that we're doing right
now is known as crows foot notation. Because it looks like crows foot, I think that's why
they call it that I don't really know. But this is a one, this isn't many. So when we're
drawing it, one, many, we could also have one to one, of course. So that's another possibility.
I forgot to mention one to one. Now, before we started drawing on both sides,
I think it's easy just to think about one side, like think of a cardholder relationship
to a card, we would have one card holder. Now that we have that side figured out we
got, we just think about the card holder, then we can add in the card, and we have many
cards, so one card holder can have many cards. And that's how you would design the line between
the tables. Like that. Now there's another thing we need to talk about known as modality,
which we'll talk about in the next video. But for this, we're just going to keep it
simple, just drawing these things here. But you might see also like circles, you might
see a circle here, for example. We'll talk about that in the next video. So now you know
how to draw a one to many relationship. So what does this exactly mean? That means one
row can be connected with many rows and this table. It's not necessarily the table but
the rows within the table. So we can think of a specific card holder, we can have a card
holder with the name jack, Id of two. And we can have many rows over here cards, we
can add a card with the ID of ID six, Id eight, Id 12. And you can see this guy has a lot
of credit cards. So all of these rows can be connected to an individual row within the
cardholder table. Now let's look at it the other way. We could have a many to one relationship,
or basically a one to many going the other direction. So we could add this. So now one
card can be connected to multiple people. But multiple people can be connected to one
card, I'm sorry, one card can be connected to multiple people. But one card holder can
only be connected to one card. So that means one person cannot own more than one card.
So now we think of it the other way around, we have a card. With the ID three, we have
a card with the ID for the ID of six doesn't really matter what the numbers are. And then
we have a card holder, we just got got a guy named jack, which ID is three. And then we
got bill ID two, well, we have we can have one card attached
to many people. So this card can be attached to many people, which means these people co
own a car, they're both the owner of one card. But now you have to realize an individual
cardholder can't be connected to multiple cards. So jack cannot own another car, that's
not going to work out. If you wanted that to work out, then you'd have to have an intermediary
table, which we talked about and designing many to many relationships. And this video
is less to be a review of relationships, but more of how to draw them. So you have to realize
the difference between all of these possibilities on one, one to many, or one to many, it just
depends on the actual application modality, it's basically whether or not the child is
a required child the relationship is required. We talked about that when we talked about
the column characteristic. Not No. So, think of it like this, we have a credit
card company, we have a table for the cardholder. And then we have a table for the card. Obviously,
the card holder, the ID for the card holder, the person who owns the card or or buys the
card from the credit card company, they're going to have an ID, and that's going to be
a not null column, meaning it has to have a value. That is obvious because it's going
to be the primary key surrogate primary key, and it has to have a value. So we have like
this guy, and he has an ID of seven, we have another guy with the ID of 12, and then 368,
and so forth. Those are ID cc card, that is going to have a column, which is a
foreign key, referencing the cardholder because the card is the child in this relationship.
So the cardholder has the primary key, you sat down there, if you can, card is going
to have a foreign key, which connects to these. So this is going to be a foreign key. Meaning,
these are the people that own certain cards. So we have a card with the ID of 12, we have
a card, a card with the ID of 48, I'm just making up numbers are here, in a card with
the idea of 98, we have a card of the ID of 112. Well, they each have an owner, you see
this is the owner. And this is a foreign key, which references the primary key. So seven,
this card is going to be owned by that person, seven, this card is going to be owned by that
person. Seven, this card is going to be owned by that person. So all of these three cards
are owned by that person 368, that's going to be owned by somebody else. Now, if we gave
this column right here, the foreign key the basically the cardholder column, because it's
a child, if we gave it the column characteristic, not know, it would be required that every
single row has an owner. That means we can't have a card in here that is either not activated
in doesn't have an owner, or we can't have a card in there that is not being currently
possessed by a cardholder. Because you think if you have a credit card, somebody owns that
credit card. But when you first make a credit card or something or if you like, if if you
get rid of the credit card or something, it might be a credit card that currently does
not have an owner. Does that make sense? So basically just a credit card that's not being
actively used, or is disabled or something like that, and does not have a cardholder?
Well, if we wanted us to if we wanted this database to be able to store cards that don't
have owners, we would not want the nominal characteristic, because that's going to prevent
it. So that's kind of how that works. So if we take that off, then we could add a card
down here with the ID of 101. That doesn't have a card. Well, I know we
already explained this all but now let's talk about designing. Let's just make this simple.
Pay attention to the Say, say one car, well, this obviously is going we're going to talk
about a a one to many relationship, meaning one card holder owns many cards. And we're
also going to be talking about a one to one relationship, meaning one card owner owns
one card for one too many too many. We just need an intermediary table, the same concept
kind of applies. But we don't necessarily have to go through that just break it into
a one to many, two to one to many relationships. Alright, so let's try this. So we have over
here we have the table. That's going to be the card owner. Over here, we have the table
for the cards. And there's a relationship between these two as a really big gap but
whatever. So let's start over here. We can say one card holder because that's what we're
just going to say one card holder for each time. Let me bring this in so they can actually
see. One card holder can have one card. So what we just did is
cardinality. We talked about this in the last video. Now let's do another example was the
same thing and you'll see why in a second. Let's do another example. And then finally,
one more example, run out of room here. So we have the basic relationships. Now we can
add one more thing to say if it's nullable or not nullable, basically, does the child.
This is the child. Which is the card. We're asking, does it does the foreign key column
have the nominal characteristic, that's so we have the possible relationships, one to
one, one to many. Now, what we're going to do is we're able to add the modality in and
the way we do that is we add either a little circle, or a little bash, or you can think
of it as a zero or a one, which actually will make sense. And we'll see why in a second,
zero, or one. So, over here, we have the cardholder, one
cardholder for all these examples. And then over here, we have the card. zero means the
column does not have the not null characteristic, meaning it accepts no value. So this is saying
it can accept no value. So we have a one to one relationship, that's the maximum, the
card holder can have up to one card, but it's not required. So zero or one cards, one or
one, cards, zero, at least zero cards, because you can have up to many, or at least one card.
So this means not No. Not No. Now when we design this, we're talking generally
from table to table. So we would have card holder, and then card. And why do I have to
say that, basically, because if we have, if we have zero here, that means there's not
necessarily going to be relationship between a certain row. So when we're talking about
rows, we might not actually draw a relationship between certain rows. When we draw relationships,
we talk from table to table, and those relationships, explain what rows can have relationships with
different rows. So in this example, let's just focus on the top one, just to make it
easier, we would draw this within our database application,
we would say we have the table cardholder. And then that has some kind of relationship
with the table card. Now we could say one card order can have zero up to one relationships
with one individual card. So we have a card holder with the ID of seven. And then we have
an ID card with the ID of 68 owned by the card holder seven, so that points back and
that's acceptable. We can also have a card we got a card 69. And we could have a person
eight, and we could say oh it doesn't have an owner, that's fine, we could have another
card 70 and it can have the owner eight, which points back to eight. And then we can have
another person which could be a seven, we're gonna have a card, we're gonna have a card
63 which the has the owner 87 and that points back to the owner 87. So that's how those
relationships work. Now, at first, this can be kind of confusing, so just practice. Make
sure you fully understand how each one works. Just review we could have, there's four possibilities
we could have. And if the down up there makes more sense.
One card holder can have zero or one card, one card holder can have one, only one card
because you can have one the least and then up to one and then you have one card holder
zero or more than one card holder one or more. These two are required, meaning every single
child has to have an owner. Or every single card has to have an owner, every single child
has to have a parent. So hopefully that all makes good sense. I just want to introduce
a topic. So in the next three or so videos, when we go over it, it's gonna be like kind
of not all new to you, you'll kind of have an idea of what's going on. So we're going
over what's known as normalization. So, normalization is a process where we go through our, our
database plan that we have, and we start correcting things that may cause data integrity problems,
or just repeating data, etc, etc, whatever you want to say. The normal forms are basically
like a checklist that you're going to follow. And when you get to the end, you will have
a pretty good design database. There's three main forms. And these are named, very conveniently,
I have to admit, first, normal form. Second, normal form. And guess what the third one is? You guessed
it. Third, normal frame. I'm kidding, it's form. That'd be weird, though.
Alright, so God, first of all, form second normal form. third
normal form. These are the three steps of normalization
for databases. Now, if you really, really, really want to get into database design, you
just like, man, I love this stuff a guy get more? Well, there's tons of more steps, you
can add and make things all complicated if you really want. But for our practical purposes,
these are the only three that you're going to really, really need. And I know there's
like a glare right here, because these lights, and I know, I'm just working on it. So give
me a couple videos, and I'll try to work on my lighting a bit. But for now, first of all
form second Normal Form third normal form. Why do we have these? Well, it's because we
want a systematic way to follow to produce a normalized or a good structure database. Alright, there's a couple things you should know when
it comes to normalization, we still have to talk about the idea of things being atomic,
we want everything to be atomic, you also have to kind of think about data depending
on other data. So if you think of like, let's say a primary key, yes, 608, just a number
that could refer to anything that could be a person, an order an event, whatever it is,
is just the primary key. And that also is going to have different columns in the table,
which have different attributes about whatever this thing is. So let's say it's a person.
And we have a column for the name. And let's say this dude's name is Caleb, what a dumb
name, right? This depends upon the primary key. So what
I mean by that is, if we change the primary key, we're going to also change the person,
right, so let's say we have six or nine, well, this is going to be a completely different
person, right? Because they're two separate entities, this one's referring to some guy over here. And this one is referring to
some guy over here, two separate entities. You can also think, oh, by the way, that that
process or that thing, this is called dependency. So the next three videos will be going over
the first normal form, second normal form. And the third normal form. The other main
thing is that these are kind of comprehensive, I guess, you could say. So what that means
is once you have first normal form, now you can go on to the second normal form, but you
can't go jump in right here, you have to have it in first normal form before you can get
into second normal form, same thing for second normal form into the third normal form. So
that means if you have a database that is in third normal form, it is also in second
normal form, and in first normal form. So I know I'm like getting all these technical
terms in junk. So if it's freaking me out, don't worry. It's really simple. Guy, get
my hair out of my face. Alright. But basically, it's, it's a list of steps. list of things
to do. And basically, you can do this step two until you do step one. So this year second
normal form, that's step two. Can't do this until you do that. So you got to go through
the process and then once you're at third normal form, things are good to go. This video
will be over the first normal form, which is the first step in database normalization
this normal form deals with data being atomic or atomicity. Alright, so let's begin. Let's
first look at three examples where there are problems. And then I'm going to explain a
solution. If we take a look at this first example, we have a user ID, first name, last
name, email, and address, I have some values in here. But those aren't really important.
In this example, the main thing you need to pay attention to is the address, because an
address if you think about actually consists of a street address, state, country, and so
forth. So this column is not atomic, we're asking for multiple things within one column.
So that is the problem in this example, the solution for this one would be to break it
up into multiple columns, you know, a state, and so forth. That way, we don't have a bunch
of things within one column as an individual value. This example, you can see, we have
one row with a user ID, first name, last name, and email. But if you look closely, we have
two emails, these emails were entered together as one individual value. So you can think
of maybe on a website or ask you for your email, imagine putting one email in and then
putting your second email in the same box and clicking Submit. Well, it's entered into
the database as a single value, maybe with the comma or something, but it's still entered
in as the email for this user. So two values within one, which is not atomic, in this example,
the actual values given for the email are atomic, because they're only one email, but
we put two entries within our database. So now we have this in here twice, this in here
twice, and it's in here twice, and the primary key should never be in there twice. The only
time you can see the primary key in there twice is if it's a reference as a foreign
key, because the foreign key can be put in there multiple times. But the primary key
of five should only be in there one time. So we have all that duplicate data. Now what
if this Caleb curry goes and gets a name change? Because that's such a dumb name right now.
Just kidding. That's, that's my name. And I actually like that name. But anyways, if he gets a name change, and he updates his
information, what if only this one's updated. So now we have conflicting data. And see that's
the problem with data integrity, we do make sure that doesn't happen. So in this example,
two column is the problem. And this example, the values for the row is the problem. And
in this example, the values for the row is the problem. So these basically have the same
problem. How do we figure out the solution? Let's take over here, let's take a look over
here. And as you can see, there is no solution. So yeah, guys, thanks for watching. I'm just
kidding, let's, let's do this, the best solution for this problem is to have a user table.
And I'm going to write the columns that are within this user table. So we would have a
user ID, then we are going to have a first name. And we're going to have a last name.
If you wanted the address in there, you could have street address, state country, all that
other good stuff, then we're also going to have an email table. So the solution is to
break off the problem column. You can see in this example, the email is the thing causing
the problem, the address that we had earlier, that's fixable, if it's a problem in the column,
we could just break it up into multiple columns. So we wouldn't have to make a new table for
that. But since this one, even though the columns correct, it's the values within it
that's causing the problem, we would need to create a new table for that. So that's
why we have this email table. And now within here, we're going to have an email id. And
then we're going to have a email, which is going to be the actual email address, which
in this case will be these. And then we're going to have a user ID. Now this is going
to be a foreign key. That references this user ID, the primary key of the user table.
Now if we wanted to put two individual emails, we could have rows within this email table,
let's let's spread out the email table and see what it might look like. Here's what the
email table might look like. We would have an email ID which is just a randomly generated number
for each email 600 dates fine. And then the email that's going to be the actual address of the email. So we could have this I try
to make it just as bad as handwriting. Perfect, then the user ID of who owns that,
which in this case would have been five, because five was the ID that was used for this user,
this will be your first row, then we're gonna have another row, and it'll be a new email.
So we'd have a new email id, it'd be a different email, we would use this one this time. And
I'm going to make that beautiful handwriting you guys love. There we go. And then the user
ID for that would also be five. Now this repeating value is okay. This, this repeat right here.
This is okay, because this is a foreign key. And multiple emails are allowed in this situation.
This email id, this is not not okay to repeat, because each individual number should be a
separate email because this is the primary key of the email table. Now, when we look
back at the user table, we don't have any repeating data, because we're only going to
have a user ID such as five first name, Caleb, and the last name, curry. And there's no need
to make a repeating row. Because we have all the information we need without having any
problems. If we change the name, we will just update this keep the same ID change this to
whatever we name our name ourselves, or whatever the person names themselves. So there you
go, everybody, that's first normal format. Now, second, normal form deals, deals with
what's known as a partial dependency, that's when a column only depends on part of the
primary key. So in order for it to depend on only part of it, you have to have a compound
or composite key. So basically, the primary key has to be multiple columns. Because if
you just have one column as the primary key, a column can partially depend on half of it. That kind
of makes sense. I don't know, maybe not. Let's say we just have a random table for people.
So we have you know, the person. So let's go with let's go. Let's just use a person
ID. And we have some attributes about this person, we have their name, you know, maybe
their phone, or their pH level, and maybe their email. Let's first talk about what a
dependency is, before we go and talk about partial dependencies. These columns depend
upon the primary key. So in this case, this is the primary key. And there's a dependency
here. Another way you can think about is if we have a another table about cars, we can
have a car ID, which is also a surrogate key. Now this person's name, it does not have a
dependency here, you can see this name is only dependent on the person ID not upon the
car. So this doesn't make any sense here. Another thing is that the person ID doesn't
have an unrelated entity or an unrelated attribute about the car ID so such as the car color. The car color has a dependency on the car,
not the person, this isn't going to make any sense. That's what a dependency is. Now, what
is a partial dependency. So let's see what a partial dependency looks like using surrogate
keys, you'll see this when you have a many to many relationship, broken up with an intermediary
table, which is the correct way to design a many to many relationship. So let's think
of the example of books and authors because if you think about it, one author can write
many books and one book can be written by many authors. So conceptually, it's a many
to many relationship, we're going to store that in the database is one to many relationship
on one side, and then one to many relationship on the other side. So we get this look at the table, intermediary table, and then a table. So over
here, let's put the author's so this is the author. Then over here we have the book. So
this is the book table. And then in between we have the intermediate intermediary table
of book authors, or book author. So this is the correct way to design it. Now
when it comes to attributes, all the attributes about The author are going to go over here,
all the attributes about the book are going to go over here, and all the attributes that
have to do with both the app, the book, and the author's connected, those will go in this
table. So first, let's give them some surrogate keys will give us one author ID, we'll give this one a book ID. And this one's
going to have two foreign keys of the author ID, and the book ID. And those foreign keys
together will be the key for this table. So that's kind of how you would set this up.
Now things about the author go over here. So you know, their first name, maybe their
last name, maybe their birth date. Whatever you want to put about the author, the book
would have stuff about, you know, the ISDN, which is, the ISDN is the code on the back
of a book, you can look up, it tries to uniquely define that book. So the exact addition and
everything else, so you can just use that to define it. You know, we will make maybe
have the page numbers or the publisher, the publisher could be a primary key or a foreign
key to another publisher table, or you could just have it in that table. If, if that's
how it worked, but likely to go to another table. But we're going off topic. Anyways,
it looks something like this. So author information goes over here, book information goes over
here. This is the correct way to design this table. Because when it comes to dependency,
the first name of the author has only to do with the author. And it has to do all about the author. Now let's look at things that have to do with
both the book and the author, we could have something such as the author position. Now what that is, when when you write books, often there's a
person who has like the first author position, which is what the big name is. And then you
have the second and the third, and the fourth, people often compete to try to get first author,
this has to do with both the book and the author. Because if you think about it, if
we put author position here, that's going to depend on what book we're talking about,
we can't just put author position one, and say he's first on every single book he ever
is going to write, that doesn't make sense. We can't just say author position one on the
book, because it doesn't say which author we're talking about which authors one, it
doesn't make sense. That's why we have to have it in this table, because it has to do
with the book and the author. So we could say book ID is 17. Author ID is 22. And the
author position is one. So that would say the book with the ID of 17. And the author
with the ID of 22 is in the second or the first position on that book. It's a little
complicated, but we combine that with joins to make it make more sense for the actual
viewing of the data. But anyways, this relies on the book and the author ID, that's why
it's in this table, this is correct. Now an incorrect thing would be something like the
ISDN. Because the ISDN has to do with the book only. So when you look at this, it relies
upon the book ID it has a dependency on the book ID, but it doesn't have a dependency
on the author ID. This is an example of a partial dependency. Now the correct way to
fix this, in this case would just to be take isbm and put it in the book table, which we
obviously already did, because I put that in there first when we started. But if we
didn't have this table and you're working with to say like one table, maybe well, then
the correct way to do it is to take the book ID the iasb and drag them to another table,
and then use a foreign key to connect to that table, which we already have it structured correctly because we understood
how to design that many to many relationship, which is why relationships are useful to understand
what if you don't have it already set up correctly, you'll have to take the partial dependency
and move it to another table and reference it with a foreign key for a second normal
form, you want to first be in first normal form. And second, remove all partial dependencies
by moving the columns as we did here we took that is being put in the correct table. Now
another thing you can think about is if you have a table where there's a primary key of
only one column, you're already in second normal form for that, like imagine it for
this. How can birthday be dependent on only part of the primary key which is author ID
because there's only one you can't you can't depend on only part of An individual column.
Thermal form in order to get there, you have to first do first normal form, and second
normal form. So it's kind of like a ladder, you can't just start a third normal form,
you have to work your way up there. So this one deals with what's known as a transitive
dependency, which is when a column depends upon a column, which depends upon the primary
key. So I know that's kind of like confusing. So let me just kind of draw it out on a table,
make a lot more sense. So here we have a review table. As you can see, I'm kidding, you can't
see it's just a square. So we could say, review. So like, this could be on a website, it could
be, you know, like a shopping website where you review a product, or it can be a review
on a video or anything really, it could be review on a book, most likely, it'll be on a shopping website,
where you buy something, whether it be a book or anything, or cars or whatever, it doesn't
matter. But in this, you would have something like a review ID, obviously, which will be
the primary key, you would also likely have what that review is, like in a text form.
So like a comment. So you would have the actual review. And then you would have maybe a star
rating, or multiple star ratings. So you could have a star rating, so maybe one through five,
one being the worst five being awesome. And then for maybe being like really sort of Okay,
you know, and then we might have something that has a star star, meaning. So that would be like a sort of okay, right.
And finally, we might have something like either a user or the poster, I'll just put the user because
likely, you will have some kind of user account on this website where you can post comments.
So this will likely be a user ID. And it will be a foreign key to a user table. But we're
not going to put that in here right now. Because that's unrelated to the actual point. So foreign
key primary key. Now, let's look at right here we have an issue. This is what's known
as a transitive dependency, because the star meaning depends upon the star. And a way you
can kind of try and figure out if it depends on it is, okay, well, if we change the star
is the star meaning going to change? Well, if we take the star, and we change it from
a four to a three, and might go for, it might go from really super okay to really average.
So the star meaning is going to change. So that's a dependency. But then the star actually
depends upon the actual review ID. Because if you change the actual review, that star
is able to change. So this is known as a transitive dependency, and it is bad. Now you can also
have a multiple transitive dependency, which I don't even know what those are called. But
that would be where I call them depends upon a column, which depends upon a column, which
depends upon a column, which depends upon the actual primary key, it can go on forever,
for as long as you want. But you don't want it because it's bad, which lever going to
remove. And so to remove this, you take the columns that are causing a problem, you move
them to a separate table, and then you create a foreign key and replace of them which references
the actual other column. So let's do this. So since these columns right here are the
ones causing the main problem, we take them, we put them in their own table, we have a
star table, within the star table, we're going to have a star I make. And then we're going
to have the actual star, whether it be 12345, or whatever kind of rating system you're using.
And then we're going to have the star meaning. And then over here, we're going to replace
these columns, let me make this a little prettier. And I just made it five times worse. But we're
gonna replace that with a star ID, which is going to reference this table over here. So
now we don't have that problem, because the only column over here is star ID, we don't
have the star meaning, which was the real column that was causing the problem. Now over
here, we could have if we drew out this table, we would have you know, we could have a star
ID of three. And that star could be a three star rating. And it could be meaning it could
mean Average. Now, we're likely to only have five individual rows in this table. So it's
kind of like a reference table. So over here, we can have a store ID of 12345, or 01234,
or 56789, it doesn't really matter what the ID is, because the ID is just a number used
by the database that has no real world value. So we'd have five rows in here, and then we
would reference them in the reviews table. So we could have, okay, this is too ugly,
let's let me just explain it, we can have one review with the star rating ID of five,
which could correlate to a certain rating from this ratings table. That's the main idea
of the third normal form, is to take those transitive dependencies, move them to a new
table, create a foreign key to reference that table. So under view, first normal form is
making everything atomic second normal form is removing any partial dependencies. And
then third normal form is removing any transitive dependencies. This video, we will be discussing indexes,
the best way to think about it is to take a book, look in the back, there is often an
index here, which basically has a huge list of topics. And it says what pages are on.
So you'd be like, oh, wow, that looks fun queries, go to page 2078. Wow, it's right there, I found it so fast, I have
to go search through the entire book to find it. That's an example of an index. It's basically
a list of where certain data points are another kind of indexes, you can think of a phonebook,
you go to a phonebook, and you search through the phonebook, you find someone's name, G,
we go to Grandma, and it'll have the phone number right there. That's another type of
index, it's where the data is sorted in a way that you can easily find it and the data
is right there, versus an index in the back, or it's a list of data pointing to where it's
at. The one we first discussed, is known as a non clustered
index. What that means is that the data is not actually where the index is, the index
is a separate thing. And it basically just sorts the data. And it's basically a point
that tells you how to get to the data. So the way it works in a book is you find the
topic, and it'll tell you where to go 365, for example, go to page 365. That's a non
clustered index. A clustered index is like the phonebook, where it actually reorganizes
the actual data in a way that's easy to use non clustered, points to the data clustered
organizes the actual data. So we can have multiple non clustered indexes. That's because
a non clustered index is basically just a list of references that point into the data.
So we could organize it in different ways. But as for the clustered index, we can only
have one of those. That's because it actually organizes the data that way. So think of a
phone book, it's organized a to z, or however, you go through there and you find it. That's
a clustered index, it organizes the data. Well, what if I wanted to put the data in
by the phone number, so the smallest phone number like 0000001, which would be an awful
way to organize a phonebook at the beginning, and then 99999989 at the end, that's an awful
way to design a phonebook, like I just said, but that is another example of a clustered
index. That's because it actually reorganizes the data. That's why you can only have one
of those because a phonebook can't be listed A to Z, and also, by the size of the phone
number, see what I mean. It's conflicting, you can have both at the same time, you can
only have, okay, you can only have, okay, this is sorted by name, or Oh, this is sorted
by phone number. You can't have both. Now, you could make the phone number size or whatever,
as a non clustered index, where it would be like this, to have the actual phone numbers
in the back, and it would point to where it's located. So at the beginning of the index,
it would be like, okay, the phone number that's 0000000001 would be on page 743. The phone
number with 0000000002 is on page 242. You see that the actual data isn't organized.
By the, the phone numbers, but you can use that non clustered index that sorts it that
way, and has a reference to the actual data where it's located. Now, you guys are probably
sick and tired of me rambling about this. So let me just explain how this makes sense
in databases. Well, when we're working, we're working with a database, often, we will use
a certain column for certain things. And that column will be used frequently. And we want
that to be like super fast. So we want the database to know how to use it the best way.
So we create an index, the database understands the index so that way, when you tell it to
do something, it can do it faster. Rather than having to go through all of the data,
which is called a table scan, when you're working with a database with millions of rows
that can take forever. If you have an index, it'll use an index seek, which will basically
know where to start searching for that data. That's awesome, because it makes our queries
tons faster, and saves a lot of resources. There's downsides to it, though, because when
you create an index, not only does the actual table have to update whenever you update it,
but you also have to update the indexes. Think of like a book, when you add something to
this book, or you change something within this book, not only does the actual book content
in the beginning of the book change, but also when you flip to the back, the index must
also be updated. Otherwise, all this index information is going to be out of date and
useless. So that's a downside to indexes is when you update your information, it might
take a little longer in a database query, we might say something like select the first
name, last name, and the phone number and the email of the user with the user ID of
72. For example, well, I kind of said that in English, but when you're actually working
with a database, it might be more like select first name, last name, email, phone number,
where user ID equals 72. That would be an example of a database query. Now the where
clause is using the primary key. So basically, the database is going to go through the table,
find the person with the user ID of 72, and then display their first name, last name,
phone number, an email. Well, that where is using a column that has an index, the primary
key is usually the one that's going to have the clustered index. So that's the way it
actually sorts the data. So now, it doesn't have to go through the entire entire entire
entire table to find the person with the ID of 72. Because imagine if, if it was just
random data thrown in there with no organization, you have 2 billion rows, right? And the database
has to go through and find the one individual row with the user ID of 72. Go to the first
one, nope, not 72. Second one, nope, not 72. There, nope, not 72. And keep going on, on
non non to finally found the person with the user ID of 72. Basically, that will take ever
with a clustered index, it knows well, 72 is going to be in this part of the database,
it comes after 60 and comes for 80. Because that's the way it's sorted. So it's gonna
be like, boom, got done. That is how indexes kind of work. So clustered indexes are going
to be faster and better. But you can only have one, that's usually the primary key.
But it doesn't always have to be depending on the database, and what your goal for the
databases, the non clustered indexes, which are like the index and the back of a book,
those are still good. And they are recommended, if you're doing something where you're like,
where first name equals john. Now, it has a list of all the John's and where they're
located on the database are on the table, I guess, that's also a good thing. But you
don't want to have an index on something you're not going to be using a lot, because you'll
just have to have another thing updated. Another thing you don't often want to do where clauses,
which is where like where first name is Caleb, without that column that you're searching,
being a indexed column, because it can take forever. Another reason for indexes is to
increase the speed of joins. So the way the join works is it takes data from one table
and another table and combines it together by the primary key and foreign key connection.
So think of a users, users and comments. So a user post a comment. So you'd have a user
table. And you would have a comment table. And let's just draw a rose on here. So we have this guy I'm just gonna draw pictures.
This because it's funner. So we have three people over here, we have four comments on
a website or review, or it doesn't really matter. Well, this one comment is posted by
one person and one person can posts multiple comments, it's a one to many relationship.
Well, when we want to output this to where we can review the data, we want to use a join.
So it's not across multiple tables. Because if you're thinking like, this comment was
posted by the user with the user ID of 72, you're not going to know who that is right
off the top of your head, because that's a foreign key connection. So rather, you want
to replace that 72 with the actual name of that person or the username. That's a join.
And it's all done after the database is created. It's all for the query side. So basically,
this is what is defined in our database. And we want to output with a join a new table
that looks more prettier and easier to read. When we do this, we're going to do something
that says, the user ID of this call, the user ID of this table, is the same as the user
ID in this table. So if the use if a comment has the user ID of 72, we know that it's done
by the user with the user ID of 72. That's the foreign key connection with which we've
talked about like some tire series, so I'm not going to explain that anymore. But now
down here, in this table, it's going to say, Caleb curry, then oh, put the comment, you
know, join that. Well, this is also done with a where clause, in some cases, for different
databases of the terms might be a little different. But basically, that column that you're connecting
by, which in this case would be the primary key needs to be indexed to make it faster.
So whenever you're joining a certain column, the two tables, the column you're joining
should be indexed. You can also have something that's known as a composite index, which is
an index that is an index on two or more columns. So think of like a first name, and a last
name, or last name, first name. Now, the thing about these is when you use the indexes, so
let's say you select where first name equals this and last name equals that you have to
do them both, or it's not going to use that index. Now, there are exceptions to this,
think it for MySQL, actually, if you order them in a certain way, like like this, you
put an index on the last name, and then also the first name, you can, you can do a where
clause for both the last name and first name. So finding everybody with the last name, curry
and the first name, Caleb would be really fast. But the way this is set up, you can
also do the leftmost one, too. So you can say, find the person with the last name, Curry,
that's going to work. But in this case, you couldn't do it with the first name, because
it's not on the left, that's just the way MySQL is set up. So different database systems
might have it set up differently, where you can search with them individually, or you
can. But most likely, when you make a composite index, you should expect to always use that
index by itself, unless you have it set up to where you make a pretty awesome index that
can be optimized to do multiple different queries that you need to do like if I'm going
to make a ton of queries where I put the last name as the where, and then I'm going to make
a ton of queries where I put last name and first name for the where this will be a good
way to set that up. Because I'm making one index index for last name, first name that
can also be used for just last name. So yeah, that's just the basic introduction do indexes.
Yeah, I'm not going to be giving you tons of syntax actually making them because like,
you should know by now, this is a database design course, we're just trying to learn
how to design our indexes on our tables, so that our database works good. So that's something
you may have to learn more once you actually program a database, because you can test the
speed of the actual indexes to see this for speed optimization to make the database as
fast as possible. You can't really do that. If you're only designing a database, you can
only get so far when designing indexes with the database, but you can get pretty far because
I mean, obviously, you'll likely be using the primary key for where clauses and joins.
So that's something that's good do avid clustered index on. What a data type is, is when you
create a column, every single value in that column has to be of a certain data type. So
you need to tell that column when you create the database, what data type it is to be.
Also, when you're designing, you usually put the data types there as Well, so that way,
you have an idea of how your database is going to work. So there's three main categories
of data types. And the naming conventions, or the names of these data types from one
database management system to another database management system might vary a little bit.
But in general, they're organized into three main kinds. That's date, numeric, and string.
So basically, we're going to be going over these in a little bit of depth, we're not
going to go super in depth, because right now you just need to understand what these
are. And just understand that your database application might be slightly different from
others. Let's start from the bottom up just to make things fun string, a string is any
characters or like letters. So basically, you put these within quotes. So hey, this is an example of a string. Even a number
within quotes can be used as a string, in this case, this will be used as a string,
basically, the the three doesn't have any mathematical value unless
it's converted to numeric. Within string, you often have some subcategories, you will
have something that's either char or var char, which does this mean character or variable
character. What that means is, it's just like a list of characters, the character or chart,
usually is stuff that's set in length, so like a phone number, or a zip code, something
that doesn't change, size wise. So basically, with a char, you'll have something like, sharp
eight, and every single value within that char column is going to be stored as a individual
characters. Now even if you don't put eight characters, let's say you put six, then there's
going to be two blank characters in there. So they're all stored as eight. var. char,
on the other hand, is variable, basically, meaning that the size can change, you can
have anywhere from zero up to eight. There's also often the subcategory or sub data type
of text, which is often used for larger things such as comments, messages, and so forth.
That's basic summary of the string data types. As for numeric data types, these are numbers.
That's different than this. This is a string that has a number in it that's used differently
than the numeric data type. The numeric data type doesn't have quotes. It's just the numbers
that you're using. Just like string, there's often some subcategories of decimal, and then
floating point or double decimal works in base 10. Now, I know we haven't really gotten
much into basis. And I'm just gonna put date back up here. So we haven't got there yet. But I think it's really important to know and in fact,
I've made a lot of videos over binary, hexadecimal and stuff, basically different numbering systems
that use different bases. Well, decimal uses base 10 is the number system we're used to
counting, you know, 0123456789, and then it goes back to 10 1112 1314. It's base 10. That's
what deaths it means in decimal. Well, there's also binary. So we have decimal and then we
have binary decimals, base 10, binaries, and base two. So binary, we only have the options
of zero, and one. Now, you don't really need to understand how to like calculate in binary.
But if you want to know that I do have videos for that, but just know when we're working
with decimal is working with a base 10 system. When we're working with floating points. We're
working with binary, which is stored differently. It's stored in binary, and then you can have
it presented as decimal for math or whatever. So what's really the difference? Do you use
decimal or do you use floating points? It just kind of really depends. decimal generally
is more accurate on mathematics to a point. There are problems that can come up with decimal,
but you'll just have to research those. But basically, this one has some errors binary
it has problems with certain mathematical equations or numbers. For example, when we want to store 1/4, it's stored like this, we have 0.01 The
way this kind of works is we have anything after the point that divides the number, basically,
in half, so we would have half, but we don't have, so we have half of a half 1/4 kind of makes sense. So the place values would
be, this one right here would be, we'd have half, this place value would be 1/4. And then
anything after that would be 1/8. And anything after that would be 116. So right now we're
where we have it, we would take one half, multiply it by zero, to get zero, because
we don't have anything there. We take 1/4, multiply it by one, since we have it there,
we'd get 1400. We add this up, we get 1/4. Basic adding and multiplying to understand
how it works. But when we have a number such as point one, and decimal, that is basically impossible
to store in binary, simply because we're working in a base two system. And we can't get to
point one very easily. Because 1/8, that doesn't quite cut it, you have to basically have a
combination of a bunch of different fractions to get as close as possible to point one,
but you can never really get there perfectly. So that's problem with the floating points.
So if you needed to source something like that, you would if it needs to have accuracy,
you would have to have it stored as a decimal, which stores it differently. Sorry, if it
completely overwhelmed you with that number stuff. And if it did, don't even worry about
it. That was a little too fast. You need it slower, please check out my introduction to
binary video. It's a super long video where I basically explain it step by step. So you
understand. And then I have a couple other videos adding and subtracting. And also fractions.
in binary, you will also have an integer type, which will only store numbers that don't have
any decimals after them. So only whole numbers like 512 28, negative 52, so forth with numbers,
we also have to worry about whether they are unsigned or signed. If they are unsigned numbers,
that means that they can't be negative. If they're signed, that means it can be positive
or negative. probably wondering why would you ever do that? Well, if you're working
with something that doesn't go negative, well, then you wouldn't need to have that negative.
When you have a signed number and it can be positive or negative, the range of potential
values stays the same. But since you include negatives, the maximum value is different.
It's not as high think about if you have a range from zero to 1000. But then you include
negatives, now you have from negative 500 to 500. That is going to be told with the
data type. So you might say something like, double, unsigned, that would work as a number.
That's enough for numbers. I don't want to talk about this anymore. Let's talk about
dates, dates, well, there's a couple different things you need to know about dates, there's
date, then there's time, there's date, time, and then there's timestamp. So let's talk
about date time first, because it makes sense the best. It basically just stores a date
such as you know, oh two for February, then you might have a colon you might have 22 for
the date and 22nd and then a colon maybe 1998 for 1998. And the year, then you have the
time, which could be whatever it could be 12 3022, which would be the seconds and you
might even be would have some milliseconds or microseconds. That's how you would store
date time. The way it actually looks might vary from database management system to database
management system. Some might have colons between the numbers, some some might have
hyphens or commas or whatever, doesn't really matter. I don't need to think about that.
Right now, I just want you to think about the date being a date. And the time being a time, date, time being the combination of a date
and a time. That's pretty much that for dates. The only other thing is the timestamp. The
timestamp is an exact moment in time that's recorded to establish when something was created
or done. It can be in milliseconds or it can be in the month, the day and then the second
hour and then the seconds and then the milliseconds. Or it can just be a number of seconds, so
forth. It varies. But anyways, that's going to give you a number when something was done.
It's usually for something like when an account was created. Either when a transaction was
done or when something happened, when you update a column, that timestamp will often
update. And that's going to keep it to like when that row was entered into the database.
So if you enter a new row, and one of the columns is a timestamp, it's going to give
you a value as soon as you submit that, and it's put into the database, and timestamps
created. And it says, Okay, this row was entered at this time. That's the main thing with timestamps.
So basically, we got three types of data types, we got string, snow, numeric, and then date. All of those
classifications have different data types for each one. But in general, as long as you
know, like, the gist of what is what, you'll be fine, you don't have to worry too much.
Because when you get an actually programming a database, you will have more specifics on
what data types to use. So this video, we'll be talking about joins. And in fact, the next
whole group of videos will be about joins. So it's going to be a lot. Now, joins are
a complex subject there, they can be confusing and tricky, and very syntactical, I don't
know if that's a word. But basically, the syntax from relational database management
to another relational database management system can be confusing and complex. But rather
than diving in and just learning all that stuff, we're going to be talking about the
concepts of what the types of joins are. And I know you might not even know what a join
is yet. So you're probably thinking, I don't know what you're talking about. Well, that's
what we're talking about today. So thank you for joining me, let's begin. So a join is
when you take something that's structured in a database in a confusing, non user friendly
way, and you're presenting it in a user friendly way. Now this is done so that way, we can
store our data in the database in a way that's the best structured, to where we protect our
data integrity, and it's normalized and separated across tables with the proper relationships
and data types and keys and indexes and everything. But when we actually want to present that
data, for example, on a webpage, or in a program, or to the analyzer person who goes in analyzes,
and does calculations on our data, or just views those calculations after the database
does all the work with the analyzation. Well, that end result is often in a way, it looks
in a way that is organized and structured, it makes sense to us. That's the purpose of
a join. So it takes a mess, and puts it out in a way that looks beautiful. And this can
be done over multiple tables. So for example, we're gonna have a table here, a table here, and a table here. And these could all be three
different tables, with relationships between the tables, we all get the specifics of what
kind of tables but basically, a join is going to take all of this. And it's going to present us with a generated
table, that is more pretty. It's more beautiful, and more structured. And it makes more sense. Now, the way this is done, is using foreign
key primary key connections. So when we have these relationships between these tables,
where I drew these arrows here, that represents a foreign key connection, that references
a primary key. So one of these tables is the parent and one of them's the child, and we
want the end result and combine them together into one. Now, when you do this, these columns
that are connected are going to be indexed to make it much faster. Now, when you join
tables, often you will replace names of things with more user friendly names. So for example,
we could have you know, let's say we had a comment on a website. And it says, Wow, then and then this is posted by Caleb. Well, you
can see that if you have a username here, it might actually be a user ID in a comment
table. So for example, we can say, This appears the comment table. This is the user table.
And there is a foreign key from the user ID referencing the user ID in the user table.
Well, we when we present that data on a database, we will want to replace that user ID with
the actual username. So rather than having you know we can have it to where it's like
User ID of 4582. And then you're like, Oh, I don't really know what that means. Well,
since this is all within the comments table, now we're going to take information from the
user table, and put that username instead of the user ID. That is how a join works conceptually.
That's another important thing. All this, think of it conceptually, these videos are
going to be helping you understand the concepts of the different kinds of joins. And throughout
it, I'll be giving you some examples of how you would type that in SQL, maybe, but I'm
not going to be getting into the specifics. That's because joins are done differently
from database management system to database management system. So you just need to know
is how the joins work, and the expected results. And then when you start working with MySQL,
or SQL Server, or Oracle database or whatever else, then you can just figure out how to
do their joins, you will already know what the results are going to be because you understand
the joints. And then you just type it out. And boom, you got to join there. Now there's
one other thing to know that's important, is all of this is data manipulation. Most
of what we've been talking about before is data definition. So that was in basically
DDL. But now we're going on to DML. So DDL data Definition Language, and data manipulation,
language, these are two parts of SQL, we all we all talked about this and the older videos,
but we're on like, way farther now. So you guys might have forgot this. But this is important
to know. Because now we already have the definition, the structure of the database, the database
might already be completely designed and structured with the columns and the rows. But now, we're
manipulating that data to look a certain way. So by doing joins, you're not actually changing
the structure of the database, you are changing the presentation of that database, that is
extremely important to know. And you may be wondering, why exactly do I need to know this,
because if I'm a database designer, or whatever, really, all I need to do is design the structure
of the database, and then the database administrator or the software engineer person, or whoever
can go in there and use my database that I designed and created. And they can do all
the data manipulation stuff to fit their application, right? Well, maybe, but not likely. That's
because the joins are also very important to understand. Because when you design your
database, you need to think of it in a user friendly way. And then normalize it, make
it confusing, and then present it in a user friendly way. It's kind of confusing, because
you start with let's say you are really bad at database design. And you're structuring
a table, and you have it to where it's like comments comment table. And within this, you
have a user name. And then you have the comment. And then maybe the blog post or video, whatever,
it doesn't really matter. You can have some IDs in there. Basically, you don't know what
you're doing. And you put the username, what is Caleb See? Comment lame. There got another one from Caleb See? Cool.
Caleb see off. When you look at this, and you show it to your grandma, she understands
it. She's like, okay, she might not understand it, but she gets if you explained it, she might. Caleb c posted the comment lane. Caleb c also posted
the comment cool. Caleb also posted the comment upscales. He also posted the comment, awesome.
But this isn't the best way to do this because we learned about data integrity, and also
repeating data because now we have this in here three times. So instead, we use a user
ID and get rid of Caleb C. And we get rid of the username. We have a user ID. And then in here we have the user ID six.
Let's not use that number 777. And then over here we have a user table with the user ID
column, which has all have our user IDs for each individual person. So we started off
with this friendly design. But in reality, it doesn't work because it's not normalized,
there's repeating data. But it looks pretty. Once we have that, then we can break it up
and normalize it. So that way, we have multiple tables. So we have this table. And then we
have this table over here. But that's not the only thing we do. The next thing we have
to do is you have to take up those pieces of the puzzle, and put them back together
in a join to get the final result. So we basically want to replicate the original table we had
here by using a join of our normalized database, and recreating it. So what we're going to
do is we're going to take the user ID column, and we're going to take the user ID, you guys
see that all all good, it's everything. This This will likely also have a username column,
so I kind of ran out of room here, but username. And that username, user ID seven will be connected
to Caleb See, using a join, we are going to connect these two tables by
basically using this foreign key which points to seven and finding the associated username
with that user ID. And our join will look like this. username, comment. And the name of the table can be
what mean whatever you want, it can be generated table. Or it doesn't even need a name, if
you're just putting it on a website or something. And then under the username column, it'll
be Caleb See, and then Caleb See, and then Caleb see. And then under the comment, you're going to
have lame and cool and awesome. But this wasn't just like this to begin with, we got the username
from this table. And we got the comment from this table. So not only do you have to design
the structure of the database, but you have to structure the joins, to basically put our
database back into simple language or grandma readable tables. Okay? That that's basically
what we want to do. So let's say you get a job, or you're working for a developer or
something, and they say, okay, on my website, I want a table on this Users page, where I
can sign in with my username, password, and I can see their username, their password,
their email, their date, they signed up and everything like that. And if I want, I can
go in there and edit it. That way. They don't have to go in and type SQL, you're you're
going to have that programmed in the in the actual application to send that SQL. But we
need to structure the tables, we need to make a join, that's going to be able to combine
those all to one pretty table, which that's the job of the database designer, likely.
I mean, if the applications or the database is huge, you might have someone who does the,
the designing, and then someone who does the implementation, and then someone who does
the joins and then someone who does this other junk. But I mean, if you're just working on
your own, and you got to do everything, you're also gonna have to design the joints. And I highly, highly, highly recommend you do that before you go in and
programming your application. And then you're like, Oh, crap, I don't want to do this, or
Oh, my database is structured wrong. I want to do this all beforehand. Don't rush into
things because then you're going to be like, dude, dawg, my application ain't working.
Right. And the reason is, because you didn't take the time to structure your database the
right way, and develop your joins. Okay, once you got that, you're good to go.
And yeah, so there are a couple different kinds of joins. There's Inner Joins, outer
joins, cross joins, unions, there's all kinds of different things that we're going to be
talking about. Okay, so, when energyne. The way an inner join works, is it's going to
take one table and another table. And then wherever there is rows from this table and
this table that are connected, it's going to present them in a new generated table.
So let's just break this down and see how it works. Let's go with an example. Because
you gotta use examples. We're gonna have a customer table We're also going to have a
card table. So think of like a bank, for example, you're going to have a customer table where
you're going to put all your customers in there. And then you have a card table where
you're going to put every single card that's been given out, or will be given out to people
who have credit cards, where you could make to where you can even include debit cards
in there, but we'll just worry about credit cards for now. So basically, we're going to
draw this out. And we're not going to have it required that a card has
a customer, a customer has a card. So neither of these columns are going to be labeled as
not null. So basically, the card does not need to have a customer. And a customer does
not need to have a card. So we'd have one table over here. And another table over here. And generally this will be designed as a one
to many relationship, because one person can have many cards. And usually one card is owned
by one person, you could design it as a many to many relationship where you can join a
card or multiple people can own a card. But yeah, you can modify the examples as needed.
So let's say this is the customer, customer. And this is the card table. Now we might have
something like of course, we have an ID for the customer. So we're gonna have the customer
ID, you know, their first name, last name, their address, their date of birth, all their
personal information that we need for their data for the bank. That's where that's going
to go. Over here, we're going to have a card ID. And then we're also going to have a customer
ID. And because this is a one to many relationship, we have the child and the parent. If this
is a one to one relationship, we could have it to where there is also a card ID and the
customer table if we wanted. But I'm not going to do that. And we can get into one to one
relationship design best practices in another video. But for now, the customer ID is going
to reference this customer ID. and here we can also have more information about the car,
we could have the the total amount or the limit max amount, I guess I can say, max amount,
you can have monthly bill. And because this is a really simplified example, we don't really
have to worry about other tables such as, you know, bills and invoices, I mean, and
all that other junk, we're just keeping it simple here. And let's say this is how we
structured our database. And we want to make it more user friendly with a join to where
we join information across the table. Because we don't want to have to have the customer
ID for the card. So we create a join. And we make this new generated table. I don't
know why I made that like circle, but and within here, we could say we could say it's
the card customer. For example, it doesn't really matter what we give the title of the
actual table. What we're going to do in here is what we decide which columns we want to
take. Well, the first thing we we always start with the ID because that's how things are
going to be joined. And we don't necessarily have to return that Id within our table. But
we need to think about it because that's how we connect the tables. So generally the the
ID is just for the databases sake, it's usually not something you use outside of the database,
it doesn't have any real world value. So we usually don't need to record it in the join
for a view the application or whatever we're using this for. Now, if you're the database
administrator and you want some more presentable tables like this, but you want to keep the
IDS in there, that's fine too. But we're not going to we're not going to return the customer
ID. There's no need to it's just a number. But since it's in both tables, if we did join
it down here, it would only be in one column. So we would have you know, cost a customer
ID. And that's going to take the combination of these. We're gonna have the customer id
like let's say 62 and then card. I Be like seven and then customer ID 62 and current
ID eight. So that's kind of how that would work. But we're not going to return those.
So what we're going to do is we're going to return just the first name, last name. And
we're also going to return the Let's add another column amount paid. So basically, it's saying
like how far they paid off their credit card bill, let's say you borrowed 10,000, and you
owe 6000, you've paid back 4000. So far, just to see how far people are getting to paying
off their bills. And like I said, this is very simplified example, you can make it to
where it gives a percentage. And you could have more tables for bills and invoices and
all that junk. But just so you guys understand the joint concept, it works the same for basically
any columns you use. So within here, we could have first name, last name, and the amount
paid. These are our columns within our table. I'm just gonna open this up, so we can write
some. And we could say that we have individual people we could say, okay, okay. And then
his amount paid on one of his cards is 2200. And we also have Caleb curry. And the amount
paid is 720. And, if you wanted, you could go in and edit the join adding maximum out.
So that's basically, or we could have a column on here such as amount owed. And we'd have the hat in here too. And that
would basically say, how much they paid out of how much so look like. Then here, we got amount of, like 3000, we're just basically saying 2200
out of 3000. If you wanted to do that, you could do that. But for simplicity sake, let's
keep it like this. And then we could have Jimmy john. And the amount paid could be 2800,
and so forth. You can make more rows if you wanted. Now you're wondering, okay, what's
an inner join, we talked about that earlier? Well, basically, an inner join is going to
take only the rows that either a card has a customer and a customer has a card where
they intersect. So this new table is joined table is going to eliminate any customers
that do not have a card. And it's also going to eliminate any cards that do not have a customer. So I'm going
to erase this, I'm just going to draw a little illustration to show you how this works. Now, you can find
more pictures like this online, which I'll link to in the description, if I can remember,
think of it like this, here is the left table, which was the customer. And here is the card
table. And this part in the middle. This is kind of like the join table or where they
intersect. So here are the customers that have a card. And here are the cards that have
a customer. So within here, this is going to contain all of the rows that have a customer
that also owns a card or a card and is owned by a customer. So if you have a card with
no customer, it will stay out of the end result. And if you have a customer with no card, it'll
stay out of the end result. So this is what you can think of it as all of these things
within this part of the Venn diagram or whatever, that's going to be returned. Now, there's
different kinds of joins where it'll only where it will only return, for example, this
side or this side. But that's where we're going to be getting in, in future videos.
So if you want to do a left join, if you want to a left join and go on here, right join
and go over here. But an inner join is just the intersection of both of the tables. I
have this habit of just like wiping it off with my hands and then my hands are like dark
green. So I'm trying to be a little more civilized, you know. So how do you do this? Well, it's done within a select statement because
remember we are in we're using data manipulation language which is done through selects. So
it would be something like this. Now to warn you, the syntax is going to vary a lot from
relational database management system from relational database management system, but
the concept stays the same. So you just got to replace a couple things, insert a couple
things, boom, Bada, bang, you got a new join. So we're going to say select. And then whatever
comes after the Select, I'm going to switch over. So everything that comes after the Select
is going to be the columns that we want in the joined, generated table. So in the last
example, we had first name, and then we also had the last name. And we also had, what was
it amount paid? I don't remember. But something like that. So that's going to be the columns
we want return, then we are going to say, what table are we getting these columns from?
So we do from? And then we say, Okay, well, you may be wondering, well, there's multiple
tables. So how do we know which one we're going to use, or we're going to put
the left one first, which, in our drawing, we had the user or the customer, and then
we had the card. So we're going to pull it from customer. And then you're going to do
a join and join the other table to the customer. So we're going to say, enter join. And then
you're going to say the other table, which was card. Now what you're going to do is say
how are these tables connected? So the way we have them connected, or the way we want
to join these tables, is by the foreign key connecting to the primary key. And the way
you signify that in syntax would be with this little statement here on which is basically
saying what are we joining it by. And you're going to say, customer dot customer ID, and
what that what that.is is basically saying the first part of this is the table. The second
part is the column. The reason we have to do that is because both tables have a customer
ID. And then you're just going to connect that to the other table by saying equals card
dot customer. Id. Now that's basically all you have to do for
this, it's going to connect, if you look at the syntax, it's going to connect and get
a different marker here. Colors makes things so useful for us. Okay, we've got the customer joined with the
card. Those are the tables. And then we're going to say what columns we want to return
and the new join. And that's first name, last name and amount paid. And then we have say
how they're related. That's the customer ID of the customer table is equal to the customer
ID of the card table. And that's how you get
your join results. So the end result would of course look like this. And you're going
to have first name, last name, and then mount paid. There you go. So that is how an inner join works. The next
thing we're going to be talking about is left joins and right joins and all the other cool
kinds of joins. Main thing though, don't worry too much about the syntax. If it's overwhelming,
just worry about the concept. Understand that if you do an inner join, it's going to exclude
all of the rows from either the left or the right table that are in both tables, it has
to be in both tables in order for it to work. That's the most important thing to understand
when it comes to these joints. Because if you understand that, all you got to do literally
is look up. Let's say you're using my SQL, just look up my SQL syntax, inner join. And then you're going to get a little syntax
code, replace the columns they gave you with the columns D one and the tables with the
tables that you want. It's really simple. So you don't really need to like yeah, obviously
you should learn this in the long run. But if you're new to this, don't worry about diving
in studying that for 20 years. Worry about the concepts because once you get the concepts,
it's easier to pick up on this junk. So in this video we'll be discussing Inner Joins
crossed multiple tables. So you're probably wondering, okay, what does that mean? Well, when we have a join, we join multiple tables.
But what if we want to join that join a multiple tables with more tables? So basically, we're
going to have a join over three or more tables trying to find the green marker. There it
is. Okay, how does this work? Well, let's just draw some boxes to kind of get an idea
of how this might look. Okay, so you might be like, okay, cool, we figured out the Inner
Joins and how those work. But how do they work when you have three tables? Let's, let's
find out. Here we have three tables. And when we do an inner join, let's say in the last
example, we join these two tables, we had some kind of join condition, which was something
like customer dot, customer ID equals card customer ID, you know, well, it doesn't always
have to be that it just depends on what you're doing with it. But basically, you're going
to have a join condition right here. And you're also going to have a join condition right
here, if you want to join in another table, these tables don't necessarily
have to be connected, like a many to many relationship, because when we design many
to many relationships, it looked like this with like the intermediary table, it's not
always the case, for example, this table could have relationship with this table. And this
table could have relationship with this table. But this table doesn't necessarily have to
have a relationship with this table. Think of it like this, when you do the join on these
two tables, you're going to get a result set. Right, then if you want to know what your
next results are going to be when you have this table and think of joining
these, and you're going to get a final result set. So if you do an inner join across multiple
tables, the end result set is going to be smaller
than this, because you think about it. If you join these two tables, you get a result
set. But now, you have to take all of these rows, and you're only going to display the
ones that meet the requirements of this join condition, which is going to decrease the
size, or the amount of rows in this table. And it's going to be shorter. Most cases.
Now, let's talk about this a little more. Let's go through the example we had before
with the cards and the customer. But we're going to add a new table, right. So we're
just going to draw a style. But this is the customer table. And we had a customer ID,
a first name, last name, you can also have any other junk you want to store about the
customer in there. Then we have a card table or the card, card ID and then the customer
ID and then the like maximum out or whatever else you want to store about the car, and
the bill or whatever the price or the interest rate. Now, let's throw in another table in
this equation, let's throw another table into this equation. It's going to be a card type
table. So you know when you get like a credit card, because all you college students are
like, yeah, I need pizza. Well, there's the card is a type such as Visa, MasterCard, or
whatever else American Express. Well, that's kind of like the card type. Generally, a bank
would just give out like one card type. But we could say that they can, like we could
just say what kind of card type the card in this table is. Now when you structure it that
way, how many different card types are there, four or five main ones, Visa, MasterCard,
American Express, maybe two other ones that are somewhat popular, there's not that many.
So if you were to store that in a column, and this one, you're going to get a lot of
repeating data. And then for example, with MasterCard decides to change their name or
they get bought out by somebody else. And all the cards now are named ultra card, then
that's going to cause problems because all of the you're gonna have, you're gonna have
a MasterCard and then ultra card, it's gonna be confusing. So what you can do is use a
lookup table which we discussed in a long video ago. And that's just going to be basically
a reference to another table with all the possible options. And that way if you need
to add a new option, you can do that easily and then just reference that or if you need
to update an option, or if you want to put extra information about that option. For example,
if MasterCard has a standard fee or something you could put that within the card type table,
rather than within the card table. Because if you have the card table, like, Look, if
you have card, ID, and then you have card type, and then you have something about that
card type. So card type interest or something. Well, this is a transitive dependency, which
is a violation of the normal forms, I believe, second normal form. Don't forget, third normal
form, I'm sorry. Anyways, that's not good. So we want to take these and put them in a
new table. So it lookup table is a defense against problems like that. And it also reduces
integrity problems. And it can also reduce repeating values. So now let's design this,
we're going to have a lookup table, because card type. And this is going to have a card
type ID, and then the card type, which is going to be like the name of it. And then
you could have any other rows about the card type in there. And then in the card, we're going to reference that with the foreign
key. So it says looking up the values through the through the lookup table. So we're going
to have card type ID, that is a foreign key. Now let's draw
these connections. Customer ID is a foreign key to the customer. And the card type ID
is a foreign key to the card type. So you can see we have all this information spread
out over three tables. And we want to add age, we want to join these and create a new
generated table that contains information from all three, we want it to be an inner
join in this case, but you can do the same thing with outer joins and other types of
joins. We'll be discussing those soon. But for this, we want it to look like this, this
is what we want our generated view or a new table to look like, we want to select stuff
about the customer, just add some columns in there just to put them in here, we can
have the first name, last name, email, phone, now paid. And then we could also have the
card type right here, it's more, because we're gonna do some cool while we're completely
off topic just for a second. I always like drew comics as a kid and I always like drew
a bubble around like either a thought or a talk. And then I tried to fit all the words
into that and never worked. And then my master drawing friend was like, dude, draw the words
first and then draw a bubble around, it was like the best idea ever. But I still don't
do it. I mean, I always have to erase the size of my database tables, fit more junk
in there should always write the words first and then draw a square around them. But this
work, these are all from this table. This is from this table, the card type is from
this table. It's a join across three tables. And you could of course add more. Now, the
join conditions, well, it would be for this, the customer ID is the same as the customer
ID. And the card type ID is the same as the card type ID. So this table versus going to
exclude here, I'll just write out what it's going to exclude. This isn't part of the table,
but I'm just putting it here, it's going to exclude customers with no card, or cards have
no customers, that's going to be the first joint condition, because we're going from
here. And then the second join condition, it's going to remove
cards with no card type. And also card types with no cards. And that means basically if
you have MasterCard as a card type, and you haven't created any card rows that also have
the foreign key that references MasterCard, or whatever I said, then that's not going
to be included because there would be nothing to put with it. And just say MasterCard, no
normal normal. That's because no cards have it and no people have cards that have it.
So that's the one INNER JOIN is going to do. It's going to do all this. So the end result
is going to basically get smaller and smaller and smaller and smaller for every single table
you add. Now, a couple other things to think about here. It has to do with things being
no or not No. Okay, let's talk about the way we have a set up is the customers. They can
have a card but they don't have have to have a cart. That's because there's no required
column in here that says you have to have a card ID. So you could have a customer that
has multiple cards, for example. We also have a card table. And well, the way we have set
up the card doesn't necessarily have to have a customer. If you want to change that, you
could take this customer ID right here. And you could make it not not, that is going to
change the end result. Because now, every single card is going to have to have a customer.
That means cards with no customers. Although, functionally, it's removing all the cards
with no customers, every single card has a customer. So the end result doesn't eliminate
cards with no customers. Functionally it does, though, because that's what the inner join
does. But since there's nothing to remove, it doesn't do anything. So you can kind of
think of it as not doing this, even though functionally does remember that it's the difference.
All right. Now, we also have it where the card, I would say should have a card ID or
a card type ID, I should say, because almost every card you're going to get ever is going
to have a card type, right? Unless for some reason they wouldn't, you'd likely have that
as not know. So functionally, this is going to remove cards without card types. But since
it's labeled as not null, every single card is going to have a card type. Therefore, in the end result, it doesn't really remove cards with no card
type. Because every card has a card type. So there's nothing to remove. Now card type, there might be a possibility, you're offering
a new card type such as, let's say, so I can't think of any cool names like legit card type
12. And like a new company comes out in there, you get a legit card type 12 card and all
through your new bank account. And that might you when you first get that there might not
be a card that has been issued that card type yet. So in the end result, you're only really
removing the customers with no cards. And I guess, the card types with no cards, I'm going to keep that. So that's going to
stay so these two are going to be removed. And this is the end result here. We're going to keep card types of no
cards and customers with no cards. That was a big one man with our hearts. We talked about
him last year, but never actually talked about the join conditions very much. So I'm going
to go over a new example in this video. Let's do this. So we're gonna have a user table,
not a customer table user table. And then we're going to have a comment table and a
video table. So think of like a video sharing website like that one called, starts with
the you know, but anyways, we have a table here, we also have a table here. We also have a table, right there. All right, these are all connected,
right? Boom, boom, boom, I'm just gonna write the name of the table. I'm not gonna really
write any comments in here, just low on space. So got the user got the comment. And then
we got the video. So the the only way the user has any connection with the video is
through the comment table because a user post a comment on a video. So it makes sense, hopefully
does. I don't even know ask you still, I'm trying to stop screaming crazy. What are the
joint conditions for this, if we want to do an inner join, let's discuss the conditions
and also the results we should expect. Okay, so let's just define the keys that are going
to connect these tables. So we have a user ID. And then we're gonna have a common ID,
and then a user ID, and then a video ID. And then this video ID will be referenced in this
table by a video ID. Okay, now, if you think of this as a many to many relationship between
user and the video, you could potentially get rid of the comment ID and just use the
combination of user ID and video ID. But that is not the best way to do it. One user could
post multiple comments on one video. So you see, like, if we had this common table, we
could have user ID and then we could have video ID and then we could have a Comment,
we have the user ID with seven video ID was 106. And the comment was lame, and then the
user ID of seven, video ID of 106 could be JK. So this is a bad idea, you wouldn't want
to use those combined. Unless you are working on some kind of thing where you can only leave
like one review on a product to say instead of just use a review product or user review,
whatever. In that case, you could use user ID combined with the product ID or the video
ID to allow that, but I'm going to use the comment ID in this. And this is going to be
a surrogate ID that's going to be used as the primary key. And these are only going
to be foreign keys, the user ID and the video ID, they're only going to be foreign keys,
and they're not going to be part of a compounds are a compound key in any sort of way, they
are just another column, that's a foreign key referencing a table to see you guys understand
the structure of how I set this up. Alright, now there's going to be a join condition right
here, which basically a joint condition is what columns are in both tables, right? You
don't always have to say user ID equals user ID, or customer ID equals customer ID or comment
equals common ID, you can do other stuff with like greater than less than and all those
fancy things, but we're just going to be using equals because it's the most common one and
the easiest to understand the one here, it's going to be the user ID. So we're gonna have
user dot user ID is equal to comment dot user ID. This is the first joint condition. Now
for this, we're gonna have a second join condition between these two. And the only thing we can
use is the video ID because it's the only thing in both tables. So we're basically going
to have comment, dot video ID equals video, dot video ID. We
got to say what table there and because they're in multiple tables, so we have to use the
dot syntax, table dot column equal to table.com. I know you guys probably can't read that I
wrote it so small, but it's okay. As long as you understand this is the john, this is
the joint condition for these two, and this is the joint condition for these two. When
you join these, you're going to join them by those columns. And now what about not knowing
no columns? What are they going to be returned as? Well, the way we have a structured user
can comment on as many videos as they want. So they can be in the comment table multiple
times, so got one too many there. Now, a comment can only be posted by one user, and can only
be posted on one video, because each individual comment is going to be a separate row in the
comment table. So for video ID, we're going to have that set to not know. And also for
a user ID, we're going to have that set to not know that's because there should never
be a comment that doesn't exist on a video, there should never be a comment on a video
that wasn't posted by a user. Right? Now for video. We don't necessarily have to have comments
on it. So there's nothing we need to worry about that. Just like a user, we don't have
to worry about having to have a comment. So the only time we need to put not know is on
these two columns. To make sure the comments exist with everything they need. You see,
the user ID would be not normal. That's because every user is a user and every video is a
video. But we don't have to say every user has to comment that would make it impractical.
So that's the setup for the nominal and the relationship types. So then when we do an
inner join, in this case, what are we going to get? Well, I just want to think about that
for a little so we can kind of understand what our end result is going to be. So what
do we want? Well, we want a table that looks like this up a little bit. We want a table
that looks like this. And that's because we wouldn't want it any other shape, right? Um, Steven, but basically, we're going to take the user with the user ID and likely we'll
have more information about them, such as their username. So if you wanted, you could
also include that in the table, which would likely be done from the beginning, because
you'll likely do your joins and stuff after that. Make sure you put your user name in
there. You don't have to add that in there because then half your users won't have a
username and half of them will which isn't good. So username from the beginning, likely
include your username, and then will likely include the comment. So we will need to include
the actual comment in the comment table. The reason I didn't put these in there earlier
because I just wanted to think about the keys for now. But now we can put those in there
that says we're designing this right. And then we also want the video title. Maybe a
link. But we'll just stick with the title for now. So these are the three columns that
we want to include. And we could have set it up in a table, and each individual row
is going to be a new comment. Well, let's see, let's think about what can be excluded
from this. So then we can decide which ones actually will be excluded? Well, let's just
ask questions, can a user be excluded? Well, if we're using an inner join, we're only going
to return users that posted a comment on a video. So yes, any user that did not post
a comment will not be included. Now, what about videos? Will videos be excluded? or
sensitive INNER JOIN, we're not going to include any videos that do not have a comment from a user. So yes, videos can be excluded. And they likely will.
So videos, where it's going to put x by to say that it can be excluded, users can be
excluded. Now, can comments be excluded? Well, let's think about each individual key, we
have the user or the foreign keys, user ID, which is labeled as not know. So that means
every single comment is going to have a user and also, the video ID is not. And also every
single comment is going to have a video. so in this situation, all of the comments will
be returned. So we can't remove those. So within here, every single comment is going
to be included. So if we had 10 comments, we're going to have them labeled one through
10, we're actually going to label them but we would have all 10 of them there. And then
only some users would show up here. And all their comments, all the comments would show
up in this column. So it'd be like, like, it'd be like, like, black and lame. And then
the video title, only some videos are gonna be included here. It's a scrap chance. That's how it's gonna work.
And let's just see how it goes. So yeah. Let's see this in action. Here I have some snapshots
of some tables I created. And I'll just be illustrating this and I'll be drawing on the
screen as needed, and have one of them walk them tablets. And if I am drawing all over
the place, it's because I haven't installed the software, not because I can't write better
than a kindergartener. Alright, so let's begin. Here we have what would be like a user table
or something. We have the user ID email username and the password, which is like just encrypted,
basically. And this is going to be joined with the comment table over here. I mean,
sorry, that's the video table here. Me. This is the comment table. Alright, so here, we
got each comment, and foreign keys to the user ID and the video ID as well as the actual
comment. And then we have the video table. Video table has the title of the video, and
also the description, which would go like under the video or on the side or appended
to the title, whatever you want to do for your website. And this is just what the video
is about, right. And as you can see, I'm advertising my database design course, which is pretty
awesome, right? And what we want to do is we just want to join these. So we have the
username, the title, and the comment, all from three different tables. So the username
comes from the user table right here. And then the title comes from the video title
right here. And then finally, the comment comes from the common table right here. So
let's go back to the join table. What we want to do is, is if we're using let's say the
comment table, we want to replace the user ID with the username and the video ID with
the title So let's look at the comment table, we want to replace all these numbers in the
user ID column with the username, which means we have to reference the user table. And then
the video ID, we want to replace with the video name, which means we'll have to reference
the video table. And then the comment ID, that's just the primary key for this table.
Alright, so let's just kind of look at how this works. So yeah, this is how it works,
the username from the user table, the title from the video table and the comment from
the comment table. Now let's go through how the join looks. I have all the tables here.
Now, it's kind of small. So sorry, if you can't see well, but we have the join table
right here. This is the joint table. That's the join. Then we have the comment, the user,
and we have the video. And we're going to basically just borrow some columns. So the
title that's going to come from the video, so I'm just going to go over here
to do to do, it's going to come from over here. And then the username is going to come
from appear. And then finally we have the comment. And the comment is going to come
from right over here. You can even see that probably not. Okay. So there's a couple things
though, not all of the columns of values are included. So not every single row. That's
because this is an inner join. Now, if you look at the username, we've got all guide
pizza, let's follow this user. So let's go to the user table. They got all sky pizza,
right there. Then we got if we keep going down this table, we got ha 12 which of course
is he's my mouse now right there. Then we got Pablo is all dot dot, which is Paulo was
awesome. Then we got milk and COO, which is milk and cookies. And then we got yellow swag,
which is yellow swag, hashtag yolo swag. And then we got that I don't even know what
that says cifs which is this person, right? Best. Best speller in the world. Okay, I made
these A while ago. So you can see these two people, though they're not included. That's
because it's an inner join, and they are not part of the end result. Now let's look at
the video. So we got the videos, we got database design 1234567. where's where's eight, though
I don't see an eight on here. Well, you can see eight. On the video side right here, it's
not included. So eight is not included. Basically, we're taking different columns from different
tables, but not every single row is included. That's because it's they don't meet the qualifications
for the inner join. So the syntax or something like this would be like this. Said the Select
and then the username, title and the comment. These are all from different three tables.
But it might vary on how it looks for your database management system. But it looks something
like this. And then from the user, comment in video table and we just do the inner join
inner join the comment by the user ID and the comment user ID and then the inner join
a video by the video video ID being the same as the comment video ID. So what's the difference
between an inner join and an outer join, that's what we will be discussing in this entire
video. It's going to be about 20 videos. So hold on a notch. It won't take too long. Once you understand what
an inner join is picking up outer joins and the other junk is like super easy. Okay, kind
of how like once you learn a one programming language, the second one is only somewhat
less hard. Right? See I have some junk written behind me. Let's look at it. Alright, so we
have what's known as a join condition. That is at the end of the joint statement. Do you
remember doing this? I can't hear you. I'm just kidding. Okay, so we got customer customer
ID and card customer ID. That means there's two tables, the customer table and the card
table. And then both of these have a column customer ID to basically just simple dot syntax
The table dot column. And this is making an association. This is saying that we want to
join two tables. And the rows that need to be connected, are connected by the column
of the customer ID and customer ID. So the two columns here, this is going to
make a connection. And our join is going to build is going to be built upon that so we
know where to connect things. So the way this will work with an inner join me to show Yeah,
we got, let me just redraw those two tables real quick, we have a customer. And then we
have the card. So this is like a bank, and people can get credit cards. And a person
doesn't necessarily have to have a credit card because they can go to that bank without
having a credit card. And also, a card doesn't necessarily have to have a current owner because
it could be a disabled card, or it could be a card that hasn't yet been activated, or
something else could happen to where a person doesn't own that card. So there's no required
columns for this. Now, when we join these, the join table is going to only include customers
who own a card, that's going to be the end result. That is with a inner join. So we have
the customer card, join table of card and the customer that is exclusive, pushing away all of the customers with no cards, and all of
the cards without an associated customer who owns that card. The Outer Join works a little
differently, in that it's still going to return all of the rows of from one of these tables,
you can decide which one whether or not, they have an associated entity on the other side.
So that joint statement comes in here, it's important. So we had it to where it was the
customer ID, I'm just going to put C for short. If we have it, where we have it what's known
as a left join, I'm just going to write it, it's going to basically ignore that statement.
And it's going to return all of the rows from the left table. The right one, though, is
still going to be a Bane that join statement. So the joint statement on the right out was
a cause customer. So with this left join, all of the rows are going to ignore this on
the left side. So it's going to return back every single row. On the right side, though,
it's going to only return. The ones that have a customer ID that has a customer ID have
the same value within the customer table. So for example, we could have a card with
the ID, the customer ID of seven. So that's customer ID not card ID. So that's
referring to the person who actually owns the car. All right, we can have that person
over here. And since if you basically insert this into the join condition, we have seven
equals seven, because this card customer ID has an equal value in this table, which means
this card is owned by that person. It's kind of how it works. But with an outer join, we
are going to ignore that. And basically say, we're going to return all of the rows for
either the left or right side. So there are made that are and there are three main classifications
of joins, outer joins, that are I'm going to write them all and then next video is we're
going to discuss them, there is a left outer join, Right Outer Join. And that seems to
be an outer OUTER JOIN. I'm so dumb. I meant to write. Full OUTER JOIN, excuse me. But
basically, we're going to be discussing these two right off hand and full, that can be a
little more complicated for some database management systems.
So we're gonna probably discuss that a little farther down the road, hopefully Now the right outer joins, we're basically
the same way as the left outer joins. But instead of the left table, it's going to be
the right table that keeps all of the rows. Now, if you don't know what I'm talking about,
please watch the last video or two, that's going to solidify your knowledge. And you'll
be like, ready to tackle this, because I'm not trying to get you to watch my other videos,
I'm trying to teach you something that's a requirement to understand this. And I don't
wanna have to reteach it all, for the sake of one little concept. So let's draw this
out a little bit. We have it to where we have two tables, a user, our customer table, and
a card table. So this would be like for a bank or something. And let's first see how
would a left join would do a left join would take all of these customers. And then it will
take only the cards that have an associated customer and return those. So we returned,
we basically connect the dots. And then we would get a joint view, which is going to
list all the customers, but only the cards that have a customer. So this is going to
be like customer listed card they have. And if they have another card is going to list
that customer again, list the card they have we discussed that in depth in the last video.
Well, for a right join, it's going to work the same way, except now it's going to do
it for the other team. So we're gonna have the same exact setup, we're gonna have a card,
I mean, a customer and card table. And now, if we set it up with a Right Outer Join,
it's going to take all of the cards, put them in the join table, so every single card, and
then it will list the associated owner of that card. So card, and then the customer.
Now there's something to consider with this, if we set it to where within the card table,
we have customer ID and we give that a column characteristic of not No, that's gonna, that
might confuse you a little bit because then every single card here is going to have an
associated customer, which kind of that's the whole point of the Right Outer Join, or
the left outer join for that sake, is to include the rows that don't have that association.
So if it's set to not know, it's going to return them all anyways, whether you use a
Right Outer Join, and so forth, you guys understand, because basically, if we use the Right Outer
Join for this, it's going to return them all. But it's going to return them all anyways,
because every single one has an associated card, we'll discuss that more in the future,
don't worry about it. Now there's something else to consider. This basically works the
same exact way as the left outer join, except is switched around. So when we discussed in
the last video, how you could say which table was the left, right where the froms the basically the form be
like select, maybe and then you'd have like the columns first name, last name, and card
amount, or you can have your card amount of us good. And then you have something that
says like from Well, the column that you put right here, the table you put right there
is considered left when we're talking about this design stuff. So in theory, you could
switch it around to where a left join works the same way as a right join, you're just
switching the direction of the tables. So in practice, most people don't even use right
joins, I'm only teaching you so you understand when you see one. Instead of using a right
join, they'll often use a left join. And then they'll just flip the column the tables I'm
sorry. So like this, you have your two tables. And I'll give you two possibilities that are
going to give the same result. You could have your customer and then the card. And you can
use a Right Outer Join. Or you could have it to where you have over here you have the
card and the customer with the same exact columns in same exact foreign keys. Nothing
changed except in design, you just switch the things and when you make the SQL statement,
you're going to switch the location of the call of the tables within that statement.
But overall the function works exactly the same. And now you're going to use a left outer join. These are going to return the same exact thing.
Because if you do this one it's going to return All these rows and the associated columns
with those rows, if you do this, it's going to return all of the rows, and all the associated
columns, those rows. So either way, you're getting all the cards in part of the customers
z. So in practice, a lot of people will only use left outer joins for consistency, and
clarity. So if someone else sees it, they already know it's the left outer join, they
don't have to worry about left or right, and then draw it out and get all confused. Most
people just use the left outer joins, but you can always use right outer joins, if you
really wish that is up to you. Well, I've been talking a little bit about not no columns,
basically columns that aren't allowed to have no value. And that can cause some confusion
when it comes to joins, because you might get results you don't expect. And I'm just
going to be explaining that just a tiny bit more, this video will be quick. So if you
already understand it, go ahead and skip it if that's what you wish. Alright, so I'm going
to be going over an example of a user comment system. So basically, you're going to have
a table with the users, and then you're going to have something such as a common table,
and you might have a table such as video, or blog, or whatever you're commenting on.
But that's out of the illustration for now, don't worry about that. Well, in this example,
we have some columns that are labeled not know. And what this means is that you're not
allowed to have them to where there's no value in that row for that column. So for example,
if in comment, we had comment ID, and then we had user ID. And we gave it a column characteristic
of nominal, well, what is that going to do? That's going to basically say that every single
row within this comment table has to have a user ID associated with that comment. And
that would be the person who posted that comment. Now, the user, on the other hand, doesn't
have to post a comment. It's a one to many relationship, and the user does not have to
post a comment. But a comment has to be owned by a user. That's important to know. So there's
three real possible options of joins that we can do on this table. The first is an inner
join, I'm gonna put these in caps, because it's probably better to do that. Now the inner
join does not matter which one is left or right, because it's going to take the middle
of both of them. So the positioning of your tables within your query of the select statement
does not really matter. All it matters is that you're using the inner join, what is
the inner join going to give you and the inner join will give you all of the users who have comments. That's
because if a user doesn't have a comment, it's not going to be in his ID won't be in
the comment table. And he'll be left out. There's a comment that doesn't have a user, well, then it would be left out
too. But that's not the case. Because it's not No. All right, the other option is a left
outer join. And I'm going to go into place places in a second left, outer join. And then we could put the comment on
the left. So if you need help position, if you need help thinking about that, in your
head, switch these two tables around. But I'm not going to do that, because that's part
of the illustration already. We're good. Right? The other option will actually let's talk
about what that's going to do first, left outer join is going to give you all of the
comments, and then give you the associated users who
have posted those comments. So every single comment will be included. So the return result will be all comments, and associated
users, okay? Now think about this, though, these things are actually going to give you
the same exact thing. That's because if you do an inner join, it's going to give you all
the users who have comments. Okay? So it's going to take you all of the users who have
a comment and put it in there. And then it's going to take all the comments that have a
user and put it in there, ensure that all the comments that have a user, all the comments
that have a user. So these are going to give you the same result. So just understand that.
The third option is to flip the left join the tables that is so got a left outer join
with the user table and left. Okay, now what is this going to do? It's going to give you
all of the users and the associated comments from those users. Now, let's talk about so
we're going to have it's either it either includes them all or exclude some of them.
So basically, if it includes With all of the users, I'm going to label it with a star.
If it only includes some users, I'm going to label it with an X. Same for the comments,
all the comments, it's going to be a star, some of the comments is going to be an X.
So users do have comments will only some users are going to post comments. So that's going
to be x on the user end. But all of the users who have comments, that's
going to include all of the comments, because all of the comments are labeled as not know.
So every user posts a comment has a row within the comment table. So it's going to return
them all. So it's going to turn them all on the user end. That's a star. Now, left outer join with the comments on
left returns, all comments and associated users. All the comments. So all on the comment
side, it's going to return them all. But the users, not all of them just part of them.
Now, left outer join is going to return all users and associated comments. So the first
one is some users all comments. The second one is some users all comments. The third
one is all users and all comments, this, that's what you kind of need to do when you're trying
to plan out which join you need to use, go through all the possibilities with that join
in is right amount. So you see that these two are actually the same, this one is just
a tiny bit different. And that includes all of the users. So you may be wondering, well,
which of these two should you use? Well, to be honest, it doesn't really matter. If if
you wanted to return all of the comments, don't worry about whether it's not normal
or not just use a left outer join with the comments on the left. And then you're guaranteed
to have that you don't have to worry about whether it's not normal or not. So that's
what I would do if you only want the users and the comments included. And you don't want
to include any comments that don't have a user, which is never going to happen, then
you do a inner join. So what you do is you just think pretend all
the comments, all the columns are knowable, or they don't have to have a value. And then
you plan your joints around that. And it can still work the same way. If that's what you'd
like to do. Otherwise, you just plan out like this. And realize you can use either one of
these, and it's going to work out fine for what you want to do. This video we will be
discussing using outer joins. When there are three or more tables, we will be discussing
the example using three tables. But you can basically apply it to any example as long
as you understand the concepts, I really recommend that you watch the video about using Inner
Joins when there are three or more tables, because the concepts are going to be basically
the same except different results will be returned. So for this, the best way to think
about it is let's say we have three tables, ignore these two right now, we have a user
comment and video table. So basically, it's a system where you can comment on videos,
each video that does get a comment will be in this table. And every user that post a
comment will also be in this table connected with that video, right? What I'm going to
do is I'm going to use a left outer join on this side and a Right Outer Join on this side.
And we will just see what those results are like. Alright, so first thing is first, let's
join the user and comment table. That's the best way to think about it is to imagine these
two tables being joined here to make a new temporary join table. And then imagine this
new temporary table being joined with the third table to give a final result join table.
So if we do a left outer join here, every single user is going to be brought down to
this table in this situation of a left outer join. It will only take the comments that
have users because there has to be an association of the user ID in both tables for that. But
it's likely that every comment is going to have an associated user because that that
user ID column will likely be set to not know that way you don't have comments that aren't
posted by anybody because that would be weird. So this will also return all comments. Great.
Now that we have that new table. You see we basically just took everything from up here.
Let's imagine taking this and joining it with the videos and we're going to use a Right
Outer Join. So this is going to be Right Outer Join for Outer Join, we're going to take all
of the videos and bring them down here. That's basically how right outer joins work, they
will automatically take every single row from the right table. And then we're going to take
any row in this table that has an association with the video table. So that means the comments
will have to have a video, which all of them will. Alright, so what what will be removed
here? Well, some of the users will be removed. The reason that is is because some of the
users aren't going to be in the comment table. And if they're not in the comment table, they
can't have an association with the video table. So think about the join statements here, we'll
likely have something where it's like, user ID over here is equal to the user ID over
here. And then we'll have it to where it's done, we'll have it to where it's a video
ID over here. And that's equal to a video ID over here. So when we grab all of these,
we have all the users and all the comments, because all the users will have a user ID.
And we don't have to have those users in the comment table. Because it's a left outer join,
which automatically takes all of the users, all of the comments will be returned, because
every comment will have an associated user. But once we get over here, it gets a little
different. Now it's about the video, not the user. So we're going to take all of the comments
that have the video ID, and all of the videos that have a video ID. Because it's a Right
Outer Join, we're going to take all the videos whether or not they have a video ID in the
comment table. So the first thing, take all the videos, we're also going to take all of
the comments are only going to take some users. The reason that is is because if a user, let's
give some example data, let's say we have a user over here with the ID of seven. And
he he doesn't leave a comment, but because we do a left outer join. He comes down here
anyway. So now he's in this new generated table. Well, he has a user ID. But does he
have a video ID in the comment table to be associated with the video ID in the video
table? No, he does not. So he wouldn't make it on past this stage. And he wouldn't be
in this final outer table as joined table. So the final result is going to be all videos,
all comments, and some users. That's just an example of using right and left outer joins
when there's three tables. Or you could do the same thing with inner join. So if you
did an inner join, it's only going to take the users you have comments. So I mean, you
can basically apply the same example to a lot of different situations to get the results
you want. As long as you understand how a left join works, how a right join works, and
how an inner join works, you can get essentially everything you really need to get. The only
one we haven't really discussed is the full OUTER JOIN, which you might need sometimes.
And that's a little different. So we'll be talking about that soon. In this video, we'll
be taking a quick break from joins that we've been discussing for the last eight or so videos.
And we will be discussing something that's a little unrelated, but kind of related, if
that makes sense. But anyways, we'll be discussing aliases. So an alias is when you rename something
and you give it kind of like a nickname or more of a user friendly name, or a name that
you are more used to seeing. And you can do this when you're writing SELECT statements
and SQL and you can do that to either make it easier to write or easier to read. So when
you want to give a table an alias, you will use the A S or as keyword. And that's going
to say anything that follows as we which is what we want to call the table. So let's say
we had that user table. And instead of using user you wanted to say a customer, well then
when you use the table and the rest of the query and you use your joins, rather than
using user, you can use the customer. And often you won't take a short name and make
it into something longer. You will do something like replace user with a U and then instead of you doing user dot user
ID, you would be you die user ID. That's an example of an alias. When you do
it on tables. It's often to make the select statement easier. But you can also do it for
columns. Now when you do it on a column, it's going to work a little differently. So when
I select statement, you put all your columns at the
top. So here's just an example of what you might say to a database, you're going to select
the email, first name, and last name. When you use an alias with columns, not only do
you change what the column is called, but it's going to change what it's presented as.
So when you get that new structured view, that generated table, and it looks something
like this, rather than saying email, first name or last name, you can make it say, email,
fn. Whoops, fn, Ln fOr first name, last name, or you can take out that underscore, and you
can replace it with you know, first space name. This works similar in the way that the other
alias works for the tables, except you put it right after the column. So you can say
select Email, as contact, comma, first name, as, and then you put in quotes got a quoted,
that's because you're telling it this is what you want it to be, you want to put it in quotes, so that way, it's understood to be a string.
You can see here, I put that space there. And without the quotes that can just cause
a problem. Comma, and then underscore last last name. Okay, see all that. That's basically how an
alias works, you can take the alias to make things easier to write easier to present.
And also, it can change the way the view looks. So it looks more pretty, right? In the next
video, we're going to be talking about joins, that you reference the same table or a self
join. And basically, you're going to take the table you have and basically tell them
last Do you want to make a copy of it essentially, and join these two tables which are the same.
And the only way that's going to work is if you give it an alias, so this table will be
called T one and this one V T two, for example. So that's an example of when you would use
an alias. Other than renaming your columns or your tables. The way a self join works
is you take a table, and you are essentially joining it with itself. And that's what kind
of makes self joins confusing. And a lot of people can't find practical purposes for them,
I'm going to be giving you a drop my marker, I just got white marker, all sub joints are
complicated. And a lot of people can't find practical purposes for them. In this video,
I'll be giving you at least one good reason for them. So hopefully, this video is helpful.
So when you have a self join, think of basically duplicating the table you have and making
an exact copy. And then joining those two tables together. That's a good way you can
think about it. So for this example, I'm going to be using a user account system on a website.
And you know how some websites have like a referral option. Basically, if you refer somebody
you get like 20% of their money and or their points or 10% of their money, so forth. That's
like referral marketing. And when you do that you are assigned a person who referred you,
right, and it can become a chain like this person or for this person or for this person
or for this person. So within a user table, you might have a user ID. And then on top
of that, you're going to have, you know, email, first name, last name, and the person who
referred you, I'm putting the person who referred you within a column known as referred by,
and this is actually a foreign key to the same table. So within here, you're going to
have numbers such as 12, which is going to point back to the user ID
of 12. So for example, the user ID 11 might be a person who was referred by the user with
the user ID of 12. Now, the only way you can make this really work for a join, is if you
use a self join. Basically, what we want to do is we want to replace referred by with
the ID, we want to use a join to replace this number with that user's email, which I also
forgot to put in here. So you know, just throw an email in there. So we'll take this, and
then our outputted result will look something like this. And this is the table that we're
going to have. And then we're going to insert data into that table. So you might have a
user such as Caleb curry, and his emails is going to be swag@yolo.com. And then it's referred
by that and we're going to replace that number with a user's email such as Howard South Yeah. dot org. So that's just an example of what we can do
with this self join. Now in order for this to work, we have to tell the database that
we're going to be using the same table in basically two different tables. And that can
kind of be confusing. And in order for that to work, you have to use what's known as an
alias. I talked about those in the last video, so be sure to check those on out. So to begin,
we just kind of think of a name of what we could call each table, we can have the first
table, which would also be the user table. And then the second table, which would be
another copy of the user table. Now we're not actually like copying the data. This is
just for illustration point, it's going to use that data that same table to withdraw
the data from, and we could say this one is v one and v two, for example. Those are our
aliases. And in order to define those, we put as after our data after our table and
put v1. So we can say, user as v1, and then join user as v2. This is something kind of
like what you would do. So now we have a user as v1. And then we're joining user as v2.
And it kind of gets the idea that oh, this person wants to use the same table for this
situation. Let's try to figure this out. Let's begin, we're going to put this within a select
statement. And I'm going to give you some specific syntax just so this can kind of make
sense. So we have a select statement. Then right after the select statement, we always put the columns we want
to take our data from. Now this can get kind of confusing, though, because we have two
basically two different tables that we're going to be taking data from. So we have to
think which table we want to take which columns from the way we had it set up before is we
have this table and this table. This was v one and this was v two. Well, that's kind
of in our imaginary brains, which we don't have, we're gonna say this table is for the
user. And this table is for the referrals. So we're going to take from this table, we'll
take the the user ID, maybe for the first name, the last name, and the email. And then
from this table, we're going to take the email of the person who referred you, so we'll take the referred by person's email. So the way this will work
is we're going to have the user IDs such as seven and Caleb curry, and then their email,
and then it'll have an ID of seven or something around eight, for the referred by, and then
that's going to go to the second table to get that person's email, you got to keep this
in mind. And you have to figure out what you're going to name your tables before you start
the Select. Because now that we are going to say the columns, we have to qualify those
columns by putting a dot before them and saying what table they're from. And we can just use
the Alias Name that we've had or made up. So we could say, select v one, period or dot.
And we could say first name, comma, and then we can say V one dot last name, comma v one dot email. Comma. Finally, we're going to
take v two and we're going to take that person's email or you can do their first name or last
name, whatever you want to put in that referred
by thing, if you wanted to say their first name, you can say v2 dot first name, if you
want to be their last name, it can be v2 dot last name. Or if you want a combination of
both, you can use a concatenation function or something. So we're just going to use the
email. So we're going to take the v two dot email. Now that we know what columns and what
tables that we want to take the data from, we actually have to put the from statement
still and say something like from and then put the table we're taking this data from.
This is where we are going to define our alias. So we could say, from user as v one that is going to basically tell the database
that the user table is known as v one. Now we can do a join. Join, this is going to default to an inner join.
If you just use join like that. We could say join user as the two that is going to basically
tell them that user is also going to be known as v2 but it's going to be considered a different
table in this situation. Then you're going to do the on which is going to say where we
are going To join these things. Now this is where it can get really confusing, because
there's so many possibilities that you can put right here. For this situation, we are
going to take v1, and we're going to take the referred by column, and that is going
to be the same as V to u user ID column. So if you can think about this, imagine literally
two tables with the same exact data. Here's the one, here's v2, and we have a user ID.
And then within here, we also have a referred to by or referred by guess that's going to
reference the user ID of the v2 table. So that means we need to basically make this
connection and say that if there's a six over here, and they're referred by table, it needs
to be the same as the user ID over here that has six. If you need more practical data under
to understand this, imagine, we have this guy over here. And his name is Samuel. And
we have this guy over here. And his name is. Yeah, that's his name. And also refer Samuel,
that means it's going to have the user ID of six, within this user row of the user ID
of let's say, 12. So Samuel has the ID of 12, when he was referred by the person with
the user ID of six, which is I've heard of, and then this other table, which is also the
same user table, it's going to refer to the person with the ID of six, which is this guy. Hopefully, that makes sense. But in reality,
you're not going to have two exact copies of the data, it's smart enough to understand
that these are all one table. So it's basically going to take that referred by, and then go
back to the user column, and join that into what would look like taking two tables and
putting them together. That's why I get kind of confusing. But when you take this query
as it is, and you put it in your database, you will get something that looks like a normal
join table, there'll be one really big table, and it will have the columns that we selected,
v1 dot fn. On top of this, we're also going to have the V one class name and the v1 email,
and the v2 email. Then if we were to put example data into this, it would look something like
Caleb, Curry, and email and then the v2 email would be like another guy's email. So such
as so calm, that's what it would look like. And it would
just give you all the rows for that. So yeah, you can mess around, do all kinds of cool
joins and all kinds of stuff. But there's one more thing that I want to tell you. And
that has to do with aliases. Well, you know, this might not be too pretty how you have
right now. And it might be confusing having an email and then right beside it another
email, like what is the what are those mean? Well, you might want to say that one column
is the user, and another is the person who referred them. So you can use an alias for
that, too. So for that you also use as, but instead of putting in the from, you're just
going to put it right after the column that you want to change. So you can put here, here,
here, and here. So if I wanted the table to email, or the person who referred, you could
make it say like, v two dot email, as referred by, and this you're going to want to put in
quotes to say you'll want that string as the title for that column. And then when you get
that new table, join, instead of saying email, it's going to say referred by, and then a
list of data. Yo, what's up my homie homies, this video are the section of the video, I
guess, we will just be discussing this in a little more depth by showing this real example
that I explained on the board. So you can see I have a user table. And within my user
table, you know, I got user ID pass email for first name, last name, and a referred
by this referred by is a foreign key referencing the user ID. It's kind of interesting how
that works. So you can see here is this guy allama swag at YOLO calm is referred by to,
which in this case would be Hi mom at low level calm, which he basically referred everybody
that's why has a zero here. And if you scroll down, we also have some people who are referred
by one. And basically what we want to do is we want to replace this referred by with the
person's email. And to do that we can use a self join. And who knows there might be
a way easier way to do this. So if there is just be sure to let me know. Alright, so here's
a query that would work to do that. So let me just refresh this. And here's what it's
going to do. It's going to select the first name, last name, email. Mail of the person
who was referred, was also going to take the email of the referral person, the person who
referred the other person, the person within this preferred by column. Okay, so you can
see we got first name, last name, email. That's one thing, and then the referred by email.
So how does this work, we can take the columns, and we also replaced the email of the referred
by and put referred by, that's why it shows up right here, which is what we want, that's
good. Then we say what table we're going to take the user table and call it v1, which
you can basically name it whatever, as long as you update right here, then it'll still
work. That's just the name I made up, I couldn't think of something cool. But base, basically,
a version one, you know, this inner join is taken our other version of the table and just
say, no, this inner is optional. So you can take that away, and it's gonna work exactly
the same way. Then we're going to say how it's related. So basically, this referred
by email, that needs to have an ID in that table that refers to a person. So the referred
by is going to have an ID, and that needs to match a user ID. So within the first table,
let's go back to the user table. This two, for example, that needs to reference an actual
person, it needs to be actual data within that second version of the user table. And
it's going to go to the second version of the user table, find that person with the
ID of two. And it's going to take that email and display it right here. So hopefully, that
makes sense, guys, and of course, you can mess this mess up the square, you can just
mess around with it and put your own stuff in it to get your own results. And you can
even change this statement right here to get even more crazy results. So yeah, guys, thanks
for watching peace on earth, and catch you in the next video. And subscribe, as well
as v one dot last name and V Ah, it's a one night now moron. For the aliases of the table.
We're going to tell my SQL ah crap nuggets. It's not my SQL databases.
