Database Design Course - Learn how to design and plan a database for beginners

**Understanding Aliases in SQL**

When working with data, it's common to have multiple tables that are related to each other through foreign keys. However, this can lead to confusion when trying to reference these tables in queries. That's where aliases come in. An alias is a temporary name given to a table or column in a query, allowing you to refer to it by a different name.

In this video, the creator discusses how to use aliases in SQL to make your queries more readable and easier to understand. The first example shows how to create an alias for a table using the AS keyword. For instance, if you have a table called "user_table" with columns "first_name", "last_name", "email", and "referral_by", you can create an alias like this:

`SELECT v1.first_name, v1.last_name, v1.email, v2.email AS referred_by`

This creates two aliases: "v1" for the original table "user_table" and "v2" for the aliased column "referral_by".

**Using Aliases to Replace Complex Column Names**

One common use of aliases is to replace complex column names with simpler ones. For example, let's say you have a table called "user_table" with a column called "referred_by". This column references another table that contains the email addresses of people who referred the original users. To display this information in a more readable format, you can use an alias to replace the complex column name:

`SELECT v1.first_name, v1.last_name, v1.email, v2.email AS referred_by`

In this query, "v2" is the aliased column "referred_by". By using this alias, you can display the email address of the person who referred each user in a more readable format.

**Using Self-Joins to Replace Complex Column Names**

Another use of aliases is to create self-joins, which allow you to join a table with itself. For example, let's say you have two tables: "user_table" and "referred_by_table". The "user_table" contains information about the original users, while the "referred_by_table" contains information about the people who referred those users. To display the email address of the person who referred each user, you can use a self-join like this:

`SELECT v1.first_name, v1.last_name, v1.email, v2.email AS referred_by`

In this query, "v1" is the original table "user_table", and "v2" is the aliased column "referred_by". By using this alias, you can join the two tables together and display the email address of the person who referred each user.

**Example Query**

Here's an example of how you might use aliases in a query:

`SELECT v1.first_name, v1.last_name, v1.email, v2.email AS referred_by`

`FROM user_table v1 INNER JOIN referred_by_table v2 ON v1.referral_by = v2.user_id`

In this query, "v1" is the aliased column "user_table", and "v2" is the aliased column "referred_by". By using these aliases, you can join the two tables together and display the email address of the person who referred each user.

**Tips and Variations**

When working with aliases in SQL, there are a few things to keep in mind. First, make sure to use meaningful names for your aliases, as they will be used by the database engine to refer to the columns. Second, remember that aliases are only valid within the scope of a single query. If you need to use an alias in multiple queries, you may need to reassign it in each query.

In addition, there are many variations on the basic alias syntax. For example, you can use table aliases like "u" or "t", or column aliases like "fn" or "ln". You can also use subqueries to create aliases for more complex data.

**Conclusion**

Aliases are a powerful tool in SQL that allow you to refer to tables and columns by simpler names. By using aliases, you can make your queries more readable and easier to understand. Whether you're replacing complex column names or creating self-joins, aliases can help you achieve your goals and simplify your database interactions.

"WEBVTTKind: captionsLanguage: enHey, what's up everybody?So for those of you who do not know me, myname is Caleb, from Caleb the video maker,too. And kill crate comm so I did a a designseries A while ago is my first video serieshere on YouTube, about 25 videos long. Andthen I went on to make a 67 part video seriesover MySQL. And I did a couple other ones,I have over 100 databasing videos, and I wantedto, 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 coursegoing to be? It's going to be for beginners.So why do you want to use a series you mostplatforms, programs, applications rely ona back end database. That's how we store informationin this world, everything we do is poweredby a database. So it's important that youknow how to properly implement database conceptsdesigns, to know how to properly structuredata. So that way, you get the best optimizeddatabase that will set you apart from therest of the IT team or the rest of your classmates,it will give you the skills needed to getthat job or whatever you want to do with it.Also, if you're just a business person, oryou have your own project, and you need tolearn how to build a website, or you needa database to store all the information you'reusing, this series will be helpful. Basically,database design is the fundamentals of databases,you should know how to properly structuredata, this series is not going to be veryspecific on a specimen a certain databasemanagement system. So this isn't like MySQLdatabase design. This isn't like Oracle, thisisn't SQL Server, this isn't whatever elseyou use, this is going to be general databasedesign. So then you can take these skillsand apply them to any of the databases thatyou are working on that are relational, whichI mean through this series, you will you'llreally understand what that means you don'tget that. So the first part is going to bea lot of the concepts on the chalkboard, andI'm not going to like write tons of scriptson here. So don't worry, it's not going tobe confusing to where you can't see the codeon a screen. Because in reality, a lot ofdatabase design is not done on a computerfirst, 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. Iuse a chalkboard because I can easily drawconcepts, thoughts, way the way data is connected,and all that. So a lot of these databasingvideos, they're going to be on this chalkboardbecause that's all you really need. We'renot we're not programming a database, we'redesigning a database. And designing is art,you draw it, you design it. So a lot of theconcepts from this video will be useful nomatter what you will be using for your database.So database design. Before we talk about databasedesign, let's first talk about a databasebecause we can't design a database if we don'tknow what a database is, that doesn't evenmake sense. So what is a database? And that'sa really good question. I'm glad you asked.Well, I can actually tell you what a databaseis. And that's what this video is about. Soa database is something that stores data.Alright, another vague term data. And I wantyou to guys, I want all of you to think aboutdata, very broadly, don't think too specific.Think of data as anything we can store. Ina database or anything we can write down.Anything that has a value can be anything.data can be the list of every single customerof your store since 1995. Or it can be a listof every single transaction in a online shoppingcenter. Or it can be a list of every userand their username. Or it can be a list ofevery single weather event. Since 1927 canbe pretty much anything. It doesn'thave to do withspecific people data is so broad. So databaseis obviously broad too, because the databasestores data. So yeah, just think of it likethis. Here we have a database of bases wherewe storedata. Pretty simple.Alright, so now, that's pretty vague, andI'm not sure if that was very helpful. Sonow let's break it down to where we see itfrom a technical standpoint. Everything inthis world, not everything, but almost everythingis ran from a back end database. It's whatstores all of our information. So for example,I like to use websites, very examples, becausethey're very clear. And they're very easyto understand the concept of this. When yougo to a website, and you sign up, you, yougive them a username, you give them a password,and you give them an email, all of that, whenyou sign in the next time, it allows you tosign in, well, how did that website rememberwhat your username, email and password was?That's because all of this information thatyou put in, when you registered, all of thesevalues are given to a database. So here youcan say is our database. Sorry for my craptastichandwriting, but don't don't even worry aboutit. This is what's said to be the front end.This is said to be the back end. That's becausewe don't necessarily see what's going on backhere, we can put in our email, our usernameand our password, and they can get storedin this database. That way, when we come backagain, and we log in, we give them a usernameand a password, it takes that value, it checksit with the database to allow you to get intothe account. That is a perfect example ofa database. So the database can store thatinformation. But it's not limited to onlyregistration, you can use this to store allof your records of sales in a store. And alsofor that just goes on. Don't think of it asa limited thing, because there's so many thingsyou can do with the database. So many things.Next question you may be asking is how doesit differ from, like a spreadsheet, for example,so a spreadsheet,basically, if you don't really know much aboutspreadsheets, it's basically where you havecolumns. And then we have rows, and we canstore information about like, we can enterthe values for each column.spreadsheets aregood, if that's what you need for your verysmall company sees me. But if you want toget to any complexity, where you can, like,imagine if we wanted to select, let's saythis was a spreadsheet for all of my employees,I own a huge business with 100 employees.And I want to select every single employeewho has missed less than three days and havebeen 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'dhave to manually go through that check everysingle value, that's not gonna work, becausetime is money in business. But anyways, witha database, we can run queries to get thosekind of values to figure out which employeeshave been a member, a employee for three yearsand volumous, three days or less. So that'show it differs from a spreadsheet, basically,what you can do with it, if you have a spreadsheetwith 10 employees, or like I'm sorry, with10 values, and that's like you can figureout all of your information in a couple ofseconds, well, then you might not need a database,that's up to you. The other thing is withwith spreadsheets, it's it's a it's eitherlike all data or no data. Let's say I justthis stored every single detail about everysingle employee that I had, and all I wantedwas their name and their address. Well, wecan't really do that easily with the spreadsheet.Unless we take all those columns and likecopy and paste and all this extra garbageor find some algorithm to do things fancyLee, if you know what you're doing, you canprobably do it with a database, we can selectivelychoose which color. So we can say only thesetwo columns Do we want returned, and we canignore the rest. On top of that, we can allowdifferent users to access different information.Back to the website example. We have Userspage, and then we had a database. When hereyou put your username, email and passwordthat's stored on a database. But that's notnecessarily the only thing that's stored ona database. A user might only get to accessthat but a database administrator might getto access when they registered, the transactionsthey've made on your shopping website or whatthey've watched what what they viewed, thecomments they've made,how oftenthey visit the web page, it's pretty muchlimitless to what you want to do with thedatabase. So we can see that two differentusers, this person only gets to view somethings at the database. This person can onlycan view pretty much anything with a spreadsheet.It's kind of all or nothing. Do you have accessto the spreadsheet? Yes, I do. Well, hereit is. If you don't have access, well, thentoo bad, you don't get any of it. So thatthat's awesome. That's a security featureas well, because you don't want this personto get all of the hidden information that'sstored within this database. So hopefullythat makes things pretty darn clear. The nextvideo, I'll kind of be talking about how datais stored in a database. Because I didn'tI was going to get into that in this video,but I kind of ran out of time. But for thesake of just your understanding, if you decidenot to watch the second video, which you willwatch the second video for I relations, that'sa term where we get the term relational database.So this series is for what's known as a relationaldatabase.That is a specific kind of database. And that'sgoing to be most of the relation that's goingto be most of the databases that you workwith. 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 withmath, if you want to know more about the math,I have blogs on my website about that. Calebcurry.com, forward slash blocks, advertising,not blogs, advertising house advertising,anyways, basically, a relation is just a connectionbetween data. So let's just think of sillyexample, if we have the two sets of numbers,two, four, and then the other set was six,eight, and then we kind of combined thesesets. So we've kind of like crossed here,and then we cross down here. And where arethese cross is our new set. So we have thecombined set of six, two, and then we havethe combined set of six, four, and then wehave the combined set of eight, two, and thena combined set of a four. And like you'reprobably wondering, like, whatthe heck doesthat have to do with database design? Well,somewhat, we kind of do the same thing withdatabase design relation in relational databasecomes from this mathematical concept. Butrather than combining sets of numbers, we'recombining attributes of real world things.So let's apply this to the database.First, you needto know two terms. The first one is an entity.The second one is an attribute. So an entityis anything we store data about attributeor the things that we store. So if an entityis a person, an attribute would be their name,their username, their password, their haircolor, their address, their phone number,their relationship status, the person they'rein a relationship with the orders that they'vemade, or whatever, whatever it is, it's dependson what you need to store for that specificapplication. So entity is what we store thedata about. attribute is what we store. Whenwe apply this to this, this connection withthe the the relation, we have the attributetypes and the attribute values. So let's saywe have this person over here. And we'll namehim Caleb, because it's an awesome name. Allright. And then we will also give him a usernameCaleb curry with no spaces. And we'll givehim a password. Pi 123. And then over here,we have the set of attributes for a tablewe have name, username, and password. We canmake this connection of data so the name isCaleb 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 exactlywhat we were doing with the relations. Sowe have up here in our columns we have wehave the name and then we have the usernameand then the password. Don't worry if youcan't read my handwriting you get it. We storethis in a table.Right? So theseare the things we need to connect with thisperson over here. We named Caleb and I accidentallyerased them,whatever.We'll just leave it at that. So that's Caleb.And we want to store this information in thistable, which is a graphical way to, to illustratehow we're storing data in a database. So wetake his name, Caleb, we take his username,Caleb curry, we take his password, pi 123.We kind of get this, this table concept, italmost looks like a spreadsheet that we talkedabout in the last video. Although it is slightlydifferent, and we'll be talking about thatas time goes on. This guy is the entity thatwe're storing information about. These arethe attributes that we need to store aboutthis person, or any other person. So we enterspecific values for each one of these attributesto make the relation between the attributes,and the entity. Hopefully, that makes somewhatsense. And if it doesn't, don't freak outthis all kind of I mean, this is complex thought.And you don't necessarily need to understandevery single detail to design a database.So I'm just trying to explain things. Andwe'll get through. These are the attributesof this person, this entity, an entity isbasically just anything we store data aboutit's something can be a person in this case,but it can also be an order, it can be a customercan be a client, it can be a employee, anemployer, whatever it is, it doesn't evenhave to be a person. So now, the relationidea, you kind of understand that we're justmaking the connection between the attributes,and the entity, the value that the entityattributes, we're entering that in the database.That's, that's kind of a weak explanation.But honestly, it doesn't really matter thatmuch for getting into basic database design.So if you want to learn more about that, justlook up relational database design, you'llfigure it out. So now, we have the entityover here. And then we have the attributes.So in table terms, you probably know, rowsand columns, you might not know specificallywhat they are. And I'm going to tell you thisright here is a rough. You see that mightbe run out of space here. I think we're good.Yeah, we're good. So this, all of these valuesare a row. So a row is all of the attributevalues for a specific entity. So this rowwould be easier just to go like this, thisrow, points to that entity, that person. Prettysimple. A column are all of the values fora specific attribute type. So the attributetype is username, we can have another personBilly Joe, and his name, his password canbe within you, and his name can be bill.Thisis another person in our table. So this istalking about a different person, we havethis person over here. pretty chill, or thisrow is talking about this entity. The usernameis a column where we have all of the attributetypes username, So Caleb curry, Billy Joe,and whatever more if we put more values intothe 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 firstthing we have an entity type. That's that'sthe word you want to know entity type, whichtalks about entities I'll just I'll just tellyou what an entity type is in just a sec,entity type. And then we have the attributetype. So basically, the type means it's acategory. So the person was a specific entity,the case the guys named Caleb, that was anentity, it was the person then we also hadthe entity, Joe or Billy or whatever his namewas. Those are two separate people to separateentities, although you could tell that theywere somewhat similar. They're both in thesame table, they were both talking about theusername, password, and so forth. So the entitytype is user, a specific entity would be Caleb,or bill, or Jamie, or Jake, or whoever itis. So the entity type is basically the categoryof the entities that we're storing. The categoryis a user and all the all the entities withinit should be a user. So my camera stoppedrecording by itself for some reason, whichis super annoying. But anyways, our entitytype is the category of entities such as user,every single entity should be a user. Calebwas a user, Billy was a user, we could haveJimmy john, we could have got Katherine, wecould have Caitlin, we could have Cassandra,we could have, you get the point. The attributetype is basically the categories of attributes.So we had the username, we had the name, wehad the password. These are attribute types,because we don't actually have a specificvalue. If like, no one's name, his name. Whenwe actually give these specific values, theyno longer become types, and they become specificexamples. So this guy over here, here's ourentity, we can store. So basically, here'sour entity, we can store attributes aboutthis entity, the username can be Caleb curry.The name can be Caleb. And the password canbepi 123.So these are specific attributes about thatguy. Now all these are all like complex terms,and blah, blah, blah, blah, who really cares?What really matters is can you put this intoa table for people to understand who reallycares if you know the terms, but I mean, I'msure your professor care. So making that anyways.Table,the columns or the attributes, so we couldhave, for example, an ID, a password, a username, and email, we could keep going address,phone number, and whatever else.And then a rowis where we give a value for every singleone of these attributes, all talking aboutone entity. So like this guy, this guy righthere? Well, his ID, for example, 72, his usernameis Caleb curry, his email is cool, guy@hotmail.comblah, blah, blah, blah, blah, all of thisrow talks about this guy. So basically, justthink attributes are columns. entities areindividual rows. Sweet, then this whole thingright here. This is the table, and that isthe entity type. So the entity type wouldbe user because every single row within thistable should be a user. Oh, another thingI would like to mention real quick, anothername in mathematicsfor a rowis a tupple, or tuple. I think it's terrible.I don't really know. But that is another termyou'll hear. So yeah, tupple is a row. Thankyou. See ya, sorry for like yelling and gettingall intense, butfun. Alright.This will be about database management systems.And because this series is over relationaldatabase design, it will be specifically relationaldatabase management systems. And we'll talkabout the difference. So first, let's justtalk about in general, what is a databasemanagement system. So this is often shortenedto B dB, and S for database management system.And relational database management systemis the same thing except it starts with anR. So the our DBMS, so I'm just gonna writethat because it's easier.Alright, sowhere we left off in the last video, we learned?Well, actually, like both of the videos, welearned that a database can be used to storea ton of information. So we have this database,just think of it like a barrel with a tonof information just piled in there. So likeusernames, and like passwords and junk, andtransactions. And all this stuff is listedin here. And as you saw on the last video,it looked very similar to a spreadsheet. Sowhat makes it different, we have all thisdata. Now what do we do? What do we do? Justlook at it? No, don't be knew, obviously wedo cool things with it. Obviously, I don'teven know what that was about. So what wecan do is we can run what's known as a query.So a query, just sort of like searching ourdata, doing something with it, and givingus cool results. So a database managementsystem, uses our data in manages it, allowingus to view it in a human friendly way, anddo cool things such as search for values,change the appearance of data, changed theway data is stored, and so forth. So for example,we have 6 billion users, okay, just for justfor an example. And we want to delete anyonewho hasn't been active in a year. Well, withouta database management system, all we haveis a bunch of data. And we'd still have togo manually do all that and delete all that.So the database management system, is whatallows us to easily run a query, alright,select everyone who hasn't been online ina year. And then we can delete everyone whobasically, basically just delete everyonewho hasn't been x active in a year. Basically,a relational database management system isjust a sub category of a database managementsystem, a specific kind that is designed towork with relational databases. So yeah, notmuch of a difference there. So just some ofthe things that these database managementsystems can do. The first one is obviouslyrun fancy queries to give us specific results,such as give us all of the transactions thatwere processed before 2011, June 10. That'ssomething fancy we can do with a databasemanagement system. It also allows us to changethe way our data is presented. That's somethingknown as a view mechanism.So basically, if you mechanism allows us tochange 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 goingto store an ID, we're going to store a username,I'm just going to shorten these up to makeit easy. We're going to store username, we'regoing to store a password, we're going tostore an email. And then we're going to storestreet address, state, city, so forth. Butlet's just say this is how it is for now.Here is the actual table structurefor theuser table. So here are all of our attributesfor a user. Get rid of this. So it's clear?Well, with a view mechanism, we're able toget different views of this data at the surfacelevel. So for example, if we had two peopleaccessing this data, one, what's his namehim, 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, becauseall he's working on is to find out informationabout usernames and passwords. So what hemight do is he might create a new view ofjust the username and the password. Once again,I'm just shortening these, just to make thingssimple for illustration, I probably wouldn'tput them in the database like that, unlessyou wanted to, but whatever.So thisis a specific view, we can select the usernameand the password.Butthe internal structure of the data has notchanged. This is still how it's stored withinthe database. Jamie, on the other hand, shecares about the ID and the email. So she createsa new view.Just like that. So this,these are both called views. So the view mechanismallows us to create different views. Thiscan be used for database administrators, aswell as for other applications, such as whenwe have a web page, we may want. We may onlywant users to be able to access their username,password, and email. But stuff such as thedate they registered, that might be all privateon the database. So then we can just createa view giving them the information that theyhave access to and that is a layer of security.So basically, if we have a huge database fora business, well, the database admin administratoris going to have access to it all. The presidentof the company will probably have access toit all. But the people who are advertisers,they probably only need to know informationabout advertising, the the people who monitorcomments on your website, they are not goingto need access to the sales and the transactions,they're going to need access to the the tablefor comments. That's a security feature thata view mechanism allows us to do by givingdifferent views. So not every single personwho uses the database has the privilege ofcreating a new view, just because you canaccess the database does not mean you cando things such as update data, change theway it's structured, and so forth, that isprobably something only the administratoror the owner of the business is going to beable to do. That means we can allow userson websites, they can only ask, they can onlyaccess their specific username and password,they can't access everybody's username andpassword only theirs. But if this guy, Jim,who is hired at the company, he can accessthe username and password of every singleuser. That's a security difference differencebetween the two people, not allowing the userof the website to get too much information.And at the same time, not limiting Jim toofew too little information. So that is a reallygood security feature.Sowhat elsea relational database management system allowsus to do transactions, which we'll be talkingabout as the time comes, basically a transactionis what it sounds like, it's when we do somethingwith the data that for the technical terms,basically, it is either complete all the wayor it doesn't work at all, if we have a multiplestep thing, such as transferring money toan account, deducting that much money fromthe other person's account, so forth, updatingthe data boom saved. If at any time the powergoes out, and the server crashes and it doesn'tcomplete, well, then the transaction is canceled,nothing is saved. That's something you cando with a relational database management system.So that's pretty much the basics of relationaldatabase management system, just in case you'renot really sure, like the terms and stuff,examples of relational database managementsystems or just database management systems.MySQL is one. It allows us to build and runa database. SQL Server is basically like theMicrosoft, if you like Microsoft, it's runon the server, and then you access it. Soyou have Microsoft server, we have Oracledatabase we have, I don't even know how topronounce that I post gray SQL or something,and so forth, the list goes on. But basicallyall of the big terms that you hear in databasing,they're referring to the relational databasemanagement system, or the database managementsystem, if it's not relational database, don'treally don't really think of the databaseand the relational database as two separatethings. They are what they're used as one.But if you do want to be like conceptual,well things, we have the database, which storesthe information, and then the relational databasemanagement system, which allows us to manipulatethat data. When we have something such asMySQL. We don't really have a difference betweenthese two. And everything's just kind of like,okay, we use MySQL to create the data. It'sstored. My SQL is kind of like all we do theactual files, they're stored on hard driveson a server or your home computer whereveryou're running your database from. And therelational database management takes the dataon the hard drive and puts that into presentabletables for people to like the administratorto view because if we have like a hard drivedisk, let's just say this is a hard drivedisk. And some of the data is going to bestored like right here. Other parts of datathat's going to be stored like right here,and some right here.Well, if this is a tablethe relational database management systemis going Want to be able to take that andput that in the appropriate location to makeit presentable, so don't really think of itlike, Oh, we have a database, and then a relationaldatabase management system, it's kind of thinkof them as one and all in themselves. So withstuff like MySQL. The other thing, finally,I know, I'm kind of running long on this video.The other thing a relational database managementsystem is going to do is it's going to createconsistency. In bigger piece of chalk here,consistently, it's going to make consistencybehind for the front end. So on a website,we could have someone put their full namein one box, and that can be stored on a databaseas two separate columns. first name last name.Well, let's say if we updated this, and wedecided to make it one column for some reason,which I don't recommend, well, the front enddidn't change, because the only thing thatchanged is the back end. With a database,we can have consistency on our front end tomake our users happy, never having to worryabout things changing. On the back end, thethings can change. For example, the way westore dates or our time zones or our charactersets, well, that's generally, that's not goingto affect the front end, unless the data onhere is directly affects the front end. Forexample, if this is a username column, andwe delete the username column, well, that'sgoing to cause issues, because it's not acolumn on the database. But basically, whenwe have a database, the front end is generallyconsistent, as well as we have server sidescripting languages, such as PHP, or whateverelse. Well, that is going to hide our databaseand even even step farther. So basically,rather than saying, oh, there's no usernamecolumn, it'll say something like error, tryagain, later, something very vague, so peopledon't understand like, Oh, crap, I can packthis guy because I know his database structure,I can figure out how to mess up his websiteor his business or whatever. So yeah, thatwas a server side scripting language. I'mnot gonna write that out, because it's huge.Yeah, that is the basics of relational databasemanagement system.Hopefully, that was helpful. Inthis video, we'll be talking about SQL. Sos q, l. Alright, so what is SQL? SQL is aprogramming language used to communicate toa database. Basically, we speak English databasesdo not so SQL is kind of like the mediatorbetween computer database and human English,think of it like that. It's almost English,it's very simple. And in this series, we'renot actually going to be programming any databases,although we will be talking about generalSQL concepts. So it's important that you knowabout SQL. So SQL is not like a specific relationaldatabase management system. programming language,it is a general language used for every singlerelational database management system. Fromvendor to vendor, such as MySQL to SQL serveror something else, it might vary a littlebit,but the general concepts of SQL stay the same.So this is this is going to be a general conceptthat you should know. So SQL first is usedto define the database structure. And thenit manipulates the data within. So basically,we can kind of think of SQL as two categorieshere. One, it defines the structure and thento manipulates by that it means we insertdata, and then we can search it or deleteit or update it. These are the two subcategoriesof SQL. We actually for some reason, namethese their own languages. So this is dataDefinition Language for DDL and this is datamanipulation language,orDML. Some people may say there's more categories,but I mean this is pretty much the domainto so think of it like this. I'm just goingto erase this here. Now that we have this.We haveDDLDML when we have like a table, for example,I'm just gonna write this down here. And wehave the columns. So we have like ID username, password. Well, that's data DefinitionLanguage. data manipulation language is whatwe would use to insert a new value, such asthe 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, andif, obviously, if you have a big database,you'd have multiple tables. Well, all of thisstructure and the connection between tables,that's all data Definition Language. So itgets all of the mainstructureof the database. And then the, the data withinthe database is data manipulation language.So those are the two main categories.Just I just want to talk about some of thefeatures of both of these languages. So basically,when we define tables, we have a create statement.So basically, all SQL I write in all capitalletters. That's just a pattern that I've chosen.So create is an example of an SQL commandused to create tables. And we can create adatabase this way. That is data DefinitionLanguage, because we're defining the structureof our database, a command such as update,or, yeah, I guess, update if we're updatingthe actual values within columns. Well, thisis manipulation. language, because we're notchanging the structure, the columns staysthe same, just a specific attribute of anentity changes. we're updating Caleb Curry'susername to Caleb curry 123. Well, that'sa data manipulation language. Another thingthat SQL can do is what's known as a join.And joins are very important, very importantwith a relational database management system.Because with a relational database managementsystem, we break things up into separate tables,for simplicity sake, for one, sorry, I wasI added the scene there, I think I was whatever.Rather than having a spreadsheet with liketons of information, we break it off by entity.So here's a table about the user, here's thetable about the orders, here's a table aboutthe customers, then we can join those valuesto get a new, larger table, a new view, thatlooks like a huge organized table. I mean,basically, it looks, it doesn't look likewe broke it up into multiple tables. So ifwe have a table here, and then we have a tablehere. And let's just say we have, this isa sale table. And this is a user table. Withinthe sale table, we have a sale ID, we havea 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 examplejust for you to understand the concept ofa join. And then within the users table, wehave the the name, the user ID, and so forth,we can do a join here, we could take the objectsthat they bought, the person the objects thatthe person bought, as well as the name ofthe person who bought it, connected by theuser ID, which will we'll talk about thismore, then we can get a new view, which hasthe name and the items that that name, butit's all talking about the same entity, thisuser. So a naming convention is just a patternthat people do or that you do to keep thingsconsistent. So not only will this naming conventionhelp keep my database consistent across differenttables and columns. But this naming conventionis not only mine, other people use this namingconvention. That means when I go to a differentperson to review my database, they alreadyunderstand what's going on. Now, obviously,there's multiple different naming conventions,there's not one way to do everything, andit's not required. This is just how I decidedto do it. And this is because most peoplewho use my SQL, which is what I learned firstuse this, but for different relational databasemanagement systems, you may have differentsystems of naming things. So I'm just goingto get Explain what I'm going to do. AnytimeI write SQL, I put it in all capital letters.So select is an example. Fortunately, we'renot going to be doing much SQL. So I'm notgoing to have to write in all capitals ona chalkboard because it's, it's quite complicated.But when we're talking about anything I namedmyself, I use all lowercase letters, I justuse all lowercase letters, because I justthink it makes it more simple. So when wehave a table,such as a user table, I don't capitalize theEU. And then when I have an ID, I just putuser 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 anyspaces. So basically, that's my naming conventionthat I use, and yours will vary. And we haven'treally got to this yet. So you might not understandthis. But if I have a foreign key, basically,if I have a copy a connection over here, towhere we have the same column over here, Ijust 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 putlike user table, or they capitalized certainwords. And if you want to look at other namingconventions for your specific relational databasemanagement system, go right ahead. In fact,I'd encourage it. But just so you understandwhat I'm doing here, I will be doing anotherthing, when we're talkingaboutstuff in the real world. Sometimes I don'tnecessarily use that naming convention. Becauseif I'm writing everything I need to storeabout a user, well, I might just put, we needto store the user's address, I'm not goingto put the underscore users underscore address,I might just put the user's address with spacein English. And then when I convert when Istart making this into a database, structure,schema, whatever you want to call it, thenI'm going to convert that to just addresslowercase. We're going to be talking aboutOkay, what is database design? And when wetalk about what's a database, we never reallytalked about database design. Why do we haveto design a database? What What's so specialabout it, it needs to design it's not likewe're building a house or something? Well,in a way, it's it's kind of like that it'swe're building schematics to build the bestdatabase. How do you measure whether a databaseis good or bad? Well, it has to do with dataintegrity, which we'll talk about that inmore depth in an upcoming video.So data integrity is basically where all ofyour data is correct, it's up to date, there'sno disconnected data. For example, if we havetwo tables. And for some reason, these tablesare connected in some way. Well, if if forsome reason this link breaks, well, then whenthis table updates, this table gets left behind,and then the data becomes incorrect. That'sa data integrity problem, because the datais not managed properly. So with a good designeddatabase, we prevent data integrity issues.So all of our data is up to date. We don'thave, we don't have repeating data, we don'thave old data that should not be in there.Which, if for example, if you have a database,you can store lots of information from likelong ago, and that's fine. But you don't wantdata that has recently changed, and it hasn'tchanged in the update. So you want the databaseto always be up to date. Unless for some reasonyou're storing like an archives, let's sayyou have a store, and you want to store anarchive of every single order you've evermade or sold or whatever, well, then you canstore all that in a database. And that doesn'treally need updated because it's an archive.But if you have, if you have a customer inyour database, and the address is out of dateand doesn't update, well then you have a problem.Another example is if the person up has fourso if your database is designed badly andyou have repeating data, well then one exampleof the data can update and the other onesleft behind. So now you have like two addressesfor one person, which should just be one address.That's another example of a data integrityproblem. So we'll be getting into like specificcases of data integrity problemsas we go on.But when we talk about data design, databasedesign has Whole, people usually break itup into three sections, two or three, it justkind of depends on, like, what people meanit, it varies from person to person. So okay,first, we havethe conceptual.And then we have the logical. And then wehave the physical. Alright, these are allknown as schemas of schemas, whatever youhowever you want to pronounce it, but that'sthis. 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 justtells you the way the data is structured.The conceptual, this up here is when we'rejust kind of thinking about things, how thingsare related. We don't think of limitationsas an Oh, we're not going to be able to dothat, because we only have this much availabilityor something. This is kind of like unlimitedbrainstorming 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 youget down here, you have to start worryingabout, alright, what kind of relational databasemanagement system Am I going to use. But whenyou're up here, you can just kind of thinkof general concepts how data is related. Sothe farther you go down to the physical, themore specific things get. I mean, that's prettyobvious. So rather, rather than thinking ofit like three steps, I kind of think of itas a continuum, we are taking our generalideas and moving them to specific implementations.Hopefully that makes sense. But basically,when we talk about these individually, theconceptual just talks about how data is related.Because in a relational database managementsystem, we have relationships between tables,if you think of, we have a users table. Andthen we have a sales table. And these userscan go to this website, and buy products.That's our example. Here. It can be anythingbut this is just an example. Well, every singlesale has to be bought by someone, specificallyone user. So that means there's a connectionbetween the user and the sale. So we havethis connection here. That is part of theconceptual design, we say, this table, thesales depends on the user. So that's, that'sbecause with without a user, we can't havea sale, because there's no one to buy thestuff. And a user doesn't doesn't necessarilyhave to have a sale. So it's dependent thisway. That would be the parent, that wouldbe the child. Hopefully that makes sense.When we get to the logical, we start planningout our tablestructure.So we have all these relationships betweendata, well, then we can realize all right,we have specifically a user's table, and wehave X number of columns, the username, thepassword, the ID, the address, the phone number,the credit card information, if that's somethingyou need to store, anything like that. Thenwe have another table. And is the the salesso 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, anda sale can only have one user. That's a logicaldatabase design, because we're actually structuringour columns are data types, our relationshipfor drawing those out, so we have this logicaldrawing or schema, the physical, that is whenwe start actually implementing that into adatabase. So when we're talking about justdesigning and we're not actually creatingthe database, the physical design would befiguring out what relational database managementsystem will work best. What are our tabletypes? What what server Are we going to storethis on? How are people going to access this?Is it going to be over the internet, it'sgoing to be over computers programs. So usuallydatabase will be on a server, and this willbe accessed by multiple different people.That's because the database is like the coreof all data. For example, if we only had thisone person using the data, well, then we wouldn'tneed this huge database, we can just storethat information on their computer, becausethey're the only one on there. It doesn'teven make sense. So typically, everything'skind of accessed on a server. So the physicalwould be okay, what kind of server we're using?How are people going to access our data? Isit going to use a webform? Do Is that somethingwe need to program? And then when you physicallyimplement that is that that's when you actuallyprogram the database, build the webform, orwhatever you are doing, installing the serverand testing, building the users who who'sgoing to access it, the views? What are thedifferent views going to be? What data isgoing to be returned, and so forth. This isalso when you really focus on security. Becauselogically, when we're in the logical phase,we're still kind of thinking, Okay, no one'sgoing to try and break it, no, everyone'sgoing to use the database correctly. But inthe real world, that doesn't work out thatway, 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 continuumright here is when we need to start worryingabout security. We don't want people breakinginto our database. And we don't want our databaseto fall apart and have data integrity issues.So hopefully, that clears some things up.So the database design, something's up realquick.database design,is using skills that you know, to build adatabase that is not going to have data integrityissues, update anomalies, we'll talk aboutthose two, but that's basically when if forsome reason you're storing two of the samedata, like if my address is on in the databasetwice, well, that's a problem. Because whenI update my address, only one may update,for example. And now the address says I livein two different places. That's, that's wrong.So a database design is a method to separateinformation over multiple tables, rather thanhaving one huge table like this, where I storemy address, we store our sales, we store our,our customers, we store our our favorite videogames we store our relationships with. Yeah,you get. So basically,this isa 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 Ihave the columns are ID, and username. Andthen we have us again need to be a littlebigger. And then we have username, we haveaddress, let's go phone number. It's easierphone. And then we have favorite color. Whenwe store like this, well if we need to puta new favorite color, we have the ID seven.That's me, we have the username Caleb curry,we have the phone number one 800. Favoritecolor cammo. And then if I want to put a newfavorite color in, well, we can't becausewe don't want to we already put one in there.So we need to create a new route, say sevenCaleb curry one 100 dot dot dot. And thenfavorite color.Green.Right?Well, now we have all those duplicate data,we have this in here twice, do this in heretwice, we have this in here twice. And wehave, we have to have two rows for favoritecolor. So your you have to find a solutionto this problem.But nowif I decided to update my phone number becauseI got a new phone number, well now I'll updateit and I'll change it to 1234567 whateverit is, well now I updated my phone numberbut for some reason, we have a data integrityissue, because my phone number is in here,two different ways that that's a bad databasedesign. And in this series, we're going tobe talking about fixing problems like that.And the best way to do this would be to likeseparate it into multiple tables. Plus, youprobably don't need to store your favoritecolor. Unless you're running some kind oflike art website or build a business, so it'spossible. But we can do the same thing with,like orders how many orders if I had to putmy orders in that table, which is anotherbad idea, we have the same data integrityissues. So this series is going to talk youthrough getting rid of those data integrityissues, so you have the best database design,for best performance. We're going to be talkingabout data integrity. Alright, so what isdata integrity, we talked about it a littlebit in the last video, but this video is goingto be going over in more depth. data integrityis just having correct data in your database.So when you're when we're databasing, we don'twant repeating values, we don't want incorrectvalues. And we don't want broken relationshipsbetween 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 tableover here. And that is the sale table, whichis where you sell products. For example, thisis just an example. You can you can implementother examples. But this is just to illustratemy 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 asale, if no one bought it. Does that makesense? That means the sale is dependent onthe user. And we have a relationship the userbuys stuff, stuff is bought by a user, thisis a known as a Can you see this is knownas a relay ship on ship. Now, do keep in mindthat the term relational database does notcome from the word relationship. It comesfrom relations, which is the mathematicalconnection of sets, which we talked aboutthat in the earlier video. But just don'tget that confused. But relational databasedoes have relationships between tables. Sothis if for some reason this was broken. Andnow we have a sale that was bought by a useror no user. So for example, let's say thissale 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 boughtby the user by ID of seven, which points backto the user. Make sense? That's data integrity.But if this relationship is broken, and itsays the sale was bought by the user withthe ID of seven, and then this person is removedfrom the database. Well, now this sale pointsto a table or a user that doesn't exist. Andthat might be that might be practical, insome sense to say that sale was bought bythat user one he did exist. But for thingsthat need to update consistently, we don'twant that to happen. So that's example ofintegrity issues. So you want to keep thatrelationship to say, Okay, well, we got ridof the user, we need to update the sales tosay, well, either the person is deleted, oralso 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 aboutentities 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 isan entity.What it means by entity integrity is basicallyunique entities. When we have a user, we oftengive them IDs This is known as a key. So anID is used to enforce uniqueness among theentities. Like for example, if we had a tablefor users,and we store the the name of the person andthen we store their address. Now let's justgo to phone number. Because when we talk aboutaddresses, it's best to break it up into likestreet state, and I don't want to go throughall that. So we could potentially have a personwith a repeating name. So let's To say hisname's Caleb, and his phone number is one800.You wish?All right? Well, if we had another person,if they both lived in the same house, forexample, and this was their house phone, well,sometimes people name their children the samename as them. So now we have two people withthe same phone number. And you look at thisand say, Okay, well, either either we havetwo people with the same phone number, orwe have two rows talking about the same person.And we don't know, because we don't have properdata integrity. So a way to solve this isto either add more columns to where the rowwould be unique, such as a social securitynumber, or we could add an IDon.And this would be like six, and seven. Sonow we're talking about two separate entities,this is talking about this guy, because hisID is seven. This one is talking about thisguy. Because this ID is six, they're two separateentities, that's entity integrity, havinguniqueness among your entities, we have uniquenesswith the rows, and we also have only one usertable, we're not going to have another usertable, because that information should goin the original user table. Onto referentialintegrity. This is the one we talked aboutearlier, is when we reference the ID of atable and another table. If we had a commentstable on the website, right, and we say whothe comment is by, we have a user ID, whichpoints back to this ID over here. That's becausethe comment has to be posted by a user. Otherwise,we have a comment that didn't have someonewho posted it, which doesn't make sense inthe practical world. So we always have tohave referential integrity, which basicallysays, Every time there's a comment, thereneeds to be a person who posted that comment.And that connection needs to remain it needsto stay. If for some reason this is disconnected.And then this user is removed for this tablewas changed, well, then this could change.I mean, this stays the same. And then there'sa disconnection to where this is changed.And this is old, outdated data, where eitherthe user no longer exists. And the commentstill says he does, or something else likethat. That's referential integrity. And that'llmake more sense once we start designing andstuff. But yeah, domain integrity, that bringsup a new word domain. domain is basicallyjust the acceptable values for a column forsay, it's what we are storing the range ofwhat we are storing within a database. Sowhen we're talking about tables, we have columns.So these columns up here, let's say we havea phone number. Well, that means we shouldhave a certain number of digits, we have thearea code, and then the three digits and thenthe four digits. And that's at least us inmy change other places, but that is 10 digits.So we know that the phone number should be10 digits, and it should be numbers. So iffor some reason I put the word cake in thephone number column, Well, for one, this isgoing to cause an error if we have domainintegrity, 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'snot tenable. So we know that's going to causean error. So that's the basics of data integrity.When we don't have data integrity, we haveerrors. When we have data integrity, we dosomething to correct those errors, such assaying, all these all need to be numbers.That's how we implement data integrity bysetting database rules and how you do thatwith the database. Well, that kind of variesupon the relational database management systemthat you use. Some relational database managementsystems allow for general rules, saying okay,this has to be a range of this numbers. Someare more vague where they, where they onlyallow you to implement data types, data typeswould be like, integer 10 digits. So whenwe talk about practically implementing dataintegrity, different relational database managementsystems have different things you can do almostevery singlerelational database system that I have, I'mpretty sure all of them, butwho knows.They allow for data types. So a data typeis pretty, pretty self explanatory. It's thetype of data.So in database, we classify things as generally,integer, or text, or date. So we either havenumbers, text, or dates, that's like threegeneral categories, which we'll be talkingabout that more in a future video. But thedata type can put limits on what we're allowedto store. For example, we can say, okay, weonly want a character column with the maximumof 20 characters. That means we can put astring in the column that says,I likep1-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 charactersthat might cut it off, I'm not sure 1-234-567-89101112 1314 1516 1718 1920. So that's the maximumwe could put in that data. It's like, that'san example of implementing that data integrityfor thecharacters.So this, this will be for the domain integrity,we can also have referential integrity withwhat's known as foreign key constraints, whichallows us to connect to tables, because whenwe drop tables, we could say that they'reconnected in some way. But how do we enforcethat 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 thecomments. The user places a comment, so thisis the parent This is the child, because thechild can't exist without the parent, youcan't have a comment without a commenter.Well, using foreign key constraints, we cansay, Alright, if this user gets removed, alsoremove all of the comments by this user. That'san example of foreign key constraint. As forthe non repeating data, that is done withjust designing your database in the best way.So yeah, I know that video was kind of jumpback and forth from concept to concept andmaybe a little confusing, but I'm sure youwill get it as time goes on. I just want todo a little bit of review, as well as go overthe vocabulary that we've learned. So if youdon't really want to know, we're going togo over some new ones too. But if you don'twant to review, then feel free to skip thisvideo in the next one. So this video willbe about data and database design terms. Sothe very first term we learned was data. Datais pretty much anything we store in a database,you pretty much know what that is sounds prettygood database. That is what we store our datain. And then we have a specific kind of database,which is a relational database.And what's special about a relational databaseis that it stores things in tables. We havea database management system, or databasemanaged DBMS for short. That is how we managea database using code. That's how we controlour database. We have a specific kind of databasemanagement system called a relational databasemanagement system, which is just used to controlour tables and the values within our tablesof a relational database. Pretty Simple. Nowwe're going to introduce a new word to you,which is no or no, however you want to pronounceit. I've been told I pronounce it wrong, butI don't really care. Anyways, this is whensomeone does not enter a value within a columnon a table. So if we have a table, and wehave 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 anID here, we have an AI guy with the ID ofseven. And we don't have a fax number, thisemptiness 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 isno data in that specific field. And we'llbe getting into more terms like that in asecond. So that is what normal means. That'sone you will need to know. Another one isan anomaly, or anomalies.So, anomalies are basically errors withinour data in our data integrity. So when wehave an anomaly, it's something that goesaway from what we expect or from the normal.When we update something, for example, andinstead of updating one column, it updates10. 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 databaseintegrity, to protect against anomalies. Sowe went over three different kinds of integrity.We went over entity integrity, over referentialand we went over domain. Real quick what theseare, entity integrity is saying uniquenessamong the table, referential keeps the connections,the keys, foreign keys, primary keys, theykeep them connected across multiple tables.So we could say these two tables have somekind of connection. And then domain is basicallysaying a column within a table has all ofthe expected values. So we have a phone number,we would expect numbers, not text or not adate, that is domainintegrity.And the word domain is basically the rangeof values that are acceptable to store withina column. So these are our data, vocabularythat you should know. Now we're going to moveon into the design term. So designing is whatwe're here for. Sothis is fun.Very first thing you need to know is an entity.Which is anything we store data about a usera, a mortgage, a transaction, a credit cardinformation, anything the comments a comment,they're all entities, that's what we're storing.data about, then we have attributes. attributesare the things we store about the entity acomment, a, an attribute of a comment wouldbe the date that it was made the link, howmany characters is it? who posted, that'san example of an attribute a relation.We're talking aboutmathematics, it's a connection between twosets of data. When we're talking about databasing.Put very simply, it's just a table. So a relationis another name for a tablewhere we havedata within our database. So we could havea structure of different tables. So thesecan be connected.Just I don't know howeveryou want to connect them. This can be ourdatabase, where each one of these differentsets of different sets of information or datais known as a relation is a table.next term is a tupple however you want topass that to winging it here. That's anotherthing for a row. It's a, it's all of the attributesabout a specific entity. So if we have thisguy, we can say, his name is Caleb, his phonenumber is blah, blah, blah, blah, blah, hisaddress, his age, his or his birthday wouldprobably be better how long he's been a memberof this website, or whatever it is, this allof these values about this entity. That'swhat that is. That would also be a row ona table, because we physically represent relationswith a table. So when we have a table, wehave the columns, so we have name, phone number,address, social security number. And thenwhen we enter all of those, for a specificperson 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 wejust talked about that which is just a physicalrepresent representation of a relation. Tablesare what we store in a database to organizea my like cutting my head off there. To organizeour data. Within a table, we have rows andwe have columns.The rows would be a specific individual entrywithin our table. So like that guy we justdrew all of his values would be his row, thecolumns are that specific, is a specific attributeof that person, such as a name. This is thecolumn 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 kindof older terms, but they they're just talkingabout the same thing. You may hear the terms,file,record.And then fields. So what are these talkingabout? Well file is just another name fora table, a record is another name for a row,and a field is another term for a call. Soother than that we have the words is value.A value is the information that we put into a specific column. So here is a valueKayla, orsubscribe, or get rid of,or 700 or June 16, whatever it is, those areall values.All right. Oh,man, it's already 10 minutes. All right, thenwe have an entry, you may hear the term entry,which I like to use this for the like theit's almost like a verb, the when you enterdata, you get an entry. So basically, an entryis just another name for a row. So um, there'sobviously a lot of similar names. And we'llbe making a table saying what we're just justgetting database design. The process of designingyour table to remove anomalies and have dataintegrity. game or schema, however you wantto 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 callednormalize,or normalization. normalization is basicallyjust a bunch of steps that we're going tofollow 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, toget the best database design, we'll talk aboutthat more in an upcoming video. But just Iwant you to know that the term normalizationis the process of building the best databasedesign. The other one and naming conventions.And naming convention is just a consistencyused to make things consistent. So we namedtables a certain way we named columns a certainway. we name our database a certain way Wedraw our database schema a certain way. Sobasically, convention is anything that isdone repeatedly to create consistency. Andthe last one here is keys, which I'm not sureif I talked about this or not yet, I thinka little bit, but not much. But key is somethingto make everything unique within a table.This is how we make connections among tables,we connect IDs, we may say, Oh, this commentwas 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 learnmore about those as time goes on. Alright,so you see, we have a lot of terms and a lotof them are repeats for this meaning the samething for example, we have relation. And thenwe have table. And then we have file. So relationtable and file are all talking about the physicalstructure, a table pretty much record withcircle diesel's record would be a row, sowe have a row. And then we have a sub, theseare all talking about rows within a table.And then we have, how should I do this? Let'sjust cross them out columns. And then we haveattributes. And then we have a field, thoseare all talking about a column in a table.And also entry, you can kind of think of thatas a, a row, because you put like, you enterdata into the table and you get a new row.The value is just a specific, you can thinkof it like we read some of this. We have thistable, and we break it up into columns. Sowe have ID, name, and then phone number, forexample. Those are a column headers, and thenwe have specific dice,seven,Halo.And then we have another guy eight. Well,if we separate this into cells, which is anotherterm, we have a specific value that we'veput in here, such as john, that is a value.No entity, that's the only one that's so wejust just went over value entity is what'sleft that's not circled or crossed out orsquared, or some entity is I don't like toreally assign this to like a row or a tableor anything because an entity is just somethingwe're storing data about. So that could bean individual row, or it could be the tabletype. So for example, we have a table forusers while the user is the entity, but aspecific, a specific entity would be a rowwithin this table. Because we'd have the entityfor thisguy over here. And thenwe'd have one for this guy down here or forthis check over here. Or lady I should say.So these all get their own rows to see. Soan entity that's is something we store dataabout. This video will be just some more vocabulary,there's not as many we're broken up into twocategories. This one first one is SQL. Sofirst we learned about SQL, which obviouslyis program language to use to binary mentionthis I'm horrible. Structured Query Languageis what SQL means. What does that sort ofstands for, it's used to connect to your database.Hi, sorry, I just got distracted. I was wavingthis paper. And like my lens is like flashingfor 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 Dand L, data manipulation language DDL is usedto define the structure of our database. datamanipulation language is used to insert valuesin our database, as well as update them, deletevalues and search values and so forth. Oh,the other thing was, I never really mentionedthis, but I do want to mention it here, SQLkeywords.So anytime we have the wordkeywords in a programming language, it's talkingabout something that is reserved that you'renot supposed to use for your user defineddata. So for example, Select in SQL, thisis a word that your database is going to recognize,as Okay, we want you to do something, we wantyou to select data. It's a keyword, becauseit's already defined. So I want to I don'twant to name my tables or my columns, anykeywords because it can cause errors or confusion,or just make things complicated. I alwaysput 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 frontend of an application. The so the front endis what the user sees. The back end is what'sgoing on behind the scene. When we have awebsite, we don't just have what the websitelooks like, we have code that gives us thatlook, when we put in data to forms, we havelanguages that communicate with databases,to create a easy to follow non confusing frontend. But the back end is complicated as heck.So that's the difference between front endand back end front end is what the end userat a particular time will see, the back endis what's going on behind the scenes. So often,when we're working with databases, we willprogram a front end because we don't wanteveryone accessing our server, it's insecure.And it's just not a good idea. So we oftencreate a front end application to access ourdatabase, most commonly a website, althoughthere's other forms, but whatever. So here'sour database. Rather than people signing intothe server and using SQL code to get information,we often program front ends for people toaccess this data in a different way. So eachone of these front ends would be like a websitepage, for example. So here would be your registrationpage here would be your users page, here wouldbe your profile,your homepage here would be if you like youcan even have different tier systems. So thiscould be a page where the website administratorcan add or delete users, they have that extraprivilege. This is also the view system, whichwe will talk about in a second. Well, thefront end doesn't allow us to directly typein our own SQL, it only allows us to use whatthe front end gives us, which is then communicatedto the database. So that's what a front endend is, the back end would be the server sidecode used to communicate with the database.So when we when we're talking about clientsand servers, we have those two terms, we haveclient, and then we have server. So very firstthing is client. client is think of it ifwe have this wall here. Over here, we havea computer. And then over here, we have thedatabase. This is the client side, becausewe're accessing the database over here isthe server side.So client, client side and server side, Idon't really have room to write that. Butthe server serves instances of this databaseto the client where they can access the datain some way depends on how we the privilegeswegive them.So when we talk aboutdifferent 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 tothe server side by a serversidescripting or programming language. It's huge.Well, my hands tired. We can give custom views.So a view is just taking the data from thedatabase and illustrating in a different waythan 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 visitmy friends page. It'll tell me their theirname, possibly their username, if that's whatthey use, their date of registration, theirphone number, if that's public, for example,anything like that their relationship status,although information that is private may onlybe accessible to the user who owns it, forexample, their password or their email, ortheir friends lists, that might be a differentview, depending on what page you are on whatpage you are on, and the user thatyou are.So I may be able to access my own passwordand email. But that might be private to otherpeople. That's the view. Views often use joins,which basically Connect data from multipletables to create a new table. We have twotables here. users table, comments table.Can you see that? Can you see it? You betterbe able to see it? Well, a comment is alwaysgiven by a user. And we connect it using theID. 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, thiscolumn, and it was by the person with thename, with the user with the user ID of 25,we want to say, Caleb curry. So we have ajoin, taking the name from the user table,and a plot and connecting it with the commentof the comments table to get this new generated.Table. This is just a view. It's not actuallystored in the database like this, we're justcreating a new illustration of how the datareally is stored. And then we can have thename 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 peoplecan have the same name, for example. So wealways need something that's unique for thisconnection. Whether if you don't want to usean ID, you could use something like a naturalkey. Use your name, for example, but I oftenuse IDs. And we'll be talking about that morein upcoming videos. So yeah, that is all ofthe vocabulary that we have gone over so far,pretty much. If you have any questions aboutany of those, just leave a comment, I recommendthat you write all those down, I should havetold you that at the beginning. But anyways,I recommend you write those down, as wellas the definitions, study those, learn them.And especially study from the previous videostudy all these different terms that meanthe same thing. Because after a while, youget kind of used to those terms. But if you'rejust learning and you don't know that theseare talking about the same things, well, thenyou can get confused very easily. So I recommendyou study. Alright. A row is also called atumble and is also called it whatever, blah,blah, blah, that will help you that way. WhenI say whatever word it is, you know what I'mtalking about. And you don't have to like,check your charts or whatever, or go watchother videos. So that's something I definitelyrecommend that you memorize, as well. Forthe SQL stuff. It's not as important, likethe DML DDL. But I do recommend that you memorizeall of these terms as well. We'll be talkingabout atomic values.Soatomic values, basically just mean that meansthat the value stores one thing.Everything we store in a database should beone thing. When we have columns, they areabout one thing. All of the values withinthat column are one thing. We have a columnnamed. Well, what does a name consist of,you can have a first name, middle name andlast name.Well, this,although we're talking about one thing andname, it's not really necessarily atomic,because we're storing three names within onename. So if we wanted to make this atomic,the best thing to do for database design wouldbe to break this up into first name, middlename, and last name. So now I have Caleb,Daniel, through three separate names, right?Alright, sowhen we do store things and database we wantto go, we want to try and make them atomic.Now we don't want to go to the extreme, wherewe break everything apart to where it doesn'teven make any sense. But I just think of like,atom, an atom atomic, when this word cameabout, we never really split an atom. So wethought of an atom as the smallest, indivisible,into visible, I thinks the word piece of informationin this world. Now we can split it, but whatever,just so sometimes, you may think, Oh, thisisn't really a good term, but everyone willstill use this term. So just think of atomicas the smallest one, one individual thingstored. 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 tothe extreme, where we separate it into likeword like individual letters. because it'dbe ridiculous. It's like, Alright, we storein a table. Instead of having first name,last name, we have first letter of the firstname, second letter of this lab, first name,third letter, the first name for further thanfirst name. That doesn't make sense. We wantto break it down into we can treat it as onething, for example, an error, a phone number,it often has an area code. Well, we don'twant to break a phone number up into multipleportions, I often just store phone numbersas one, because you treat it as one thing.When we come when it comes to addresses, oneaddress consists of like multiple, we havewe have a street address,we have a city.And then we have a state, we have an areacodefor a zip code I mean. So addresses, I oftenbreak this up into multiple columns, ratherthan storing one address with 123. Epic lane.legit California 73821. Rather than storingall of that, in one column, I break this upinto street address, city statearea code.And then that allows us to run more complexqueries on our data because we can selectall of the users from the state of California,or we can select all of the users who livein this in this city, or pretty much anythinglike that. So this is part of one of the normalforms, which we will be talking about in future.But as for now, just think of storing everythingas one. So not only do we want the columnto be about one thing, like an address isjust an address, or a phone numbers, justa phone number. But we want the values withinthat to be singular as well. For example,we could have a column favorite movies. Thisisn't going to work, because within that column,we could store unlimited movies. And thatis also improper. So that's not going to workas well, every single column header shouldbe singular favorite movie. And you may beasking, Well, how would you store multiplemovies? Well, that has to do with more complexrelationships. And that is something we'llbe talking about very shortly. But the solutionis not to store multiple things within a column.This video will be about relationships. AndI don't mean relationships as dating advice,because I probably couldn't give any of that.Butbesides that, we're going over database relationships.So what is a relationship? Well, in a database,everything is connected. It's Think of itlike a giant web, somehow something is connectedto something else rather than storing everythingin a giant table. Sorry, my what my boardskind of wet, it's not really writing verywell, we split that. And then we have twosmaller tables for manageable data simplicity,as well as data integrity reasons. So now,we still have to maintain the connection betweendata. So we take one huge table, break itinto two tables, and then we have what's knownas a relationship.There are multiple kinds of relationships.And we'll be talking about that in the nextvideo. But for this video, we'll be talkingabout the general concept of relationships.So relationships, talk about entities, anentity is anything we store data about. Sothis is an entity. And this is an entity.And they are related in some way they're connected.There's a relationship between them. Thiserasers scrapThere we go. Okay,there we go. So now my chalkboards. Alright,so I have to worry about entities. There'stwo terms that we talked about earlier. entitiesand attributes, entities or anything we storedata about. attributes are the things we storeabout the data, well, when it comes to entities,when that attributes are what we store aboutthe entities, so basically, we have entities.And then we have the attributes for the entity,right, and then we have a different entityso that as part of our database design, wefigure out all of the things we're storingdata about, think of like a college, we couldstore information about the student, the professor,the classes, the faculty, the whatever else.So we're gonna have multiple entities, andthen multiple attributesaboutthat entity.And then we can have a third entity. So justthink club, student, Professor class. Thisis all part of our database, our database.So our database structure consists of threeentities and attributes about each one ofthose entities. What's an example of an attribute?Well, it can be the student's name, the student'saddress, the classes, they're taking, the,what, there, whatever, it doesn't really matter.They're a major, that's another entity, wecould have an entity about the the majors,such as science, or whatever else. And thenwe can have attributes for that, like theclasses that are required to get that major,how long it takes, and so forth. That willbecome part of our databasestructure. Well, theseare still related in some way. Because wehave a student is a student is going to schoolfor a major. So there's a connection fromthis entity down to the major. So let's justreplace these with like, here's student. Andthen Okay, that looks like crap. I could haveput some specific examples in here. We couldhave a student we could have a professor sampleproof, then we can have class. And then wecan have major. Well, a student goes to schoolto get a major, a student takes classes. Theclasses are taught by a professor, the professor'stypically have a specific area that they teach.So they might be part of the major like themajor league to teachers for that major. Whatever.Students Yeah, you get the point, everything'sconnected. So when we design relationships,that tells us how we want to structure ourtables. That's going to make a lot more sensein the next video. So I just wanted to introducethe idea of relationships in this video. Thenext video we'll be talking about a specifickind of relationship.So,onetoone, that is a type of relationship, thereare two other ones, one, to many, and manyto many. Here are three types of relationships.And we will be talking about each one of theseindividually, in the next three videos. Thisone will be over one to one relationships.So I wonder one relationshipbasically just means that one entity has aconnection with one other entity. And that'sall they can have. I like to think of a marriage.So we have a husband and wife.This is a one to one relationship, becausea husband can have one wife, and that wifecan have one husband, the husband can't havemultiple wives, and the why the wife can'thave multiple husbands, it's one to one, becauseone entity is limited to one other entity.This entity is also limited to one other entity,if they want to one relationship. So anotherexample of a one to one relationship wouldbe a social security number. Alright, so asocial security number in America is basicallyjust a unique number every citizen is given.There are, I think there's occasional repeats,although generally, you think of a socialsecurity number as being unique to one person.So we define it as a one to one relationship.Even if there's possibilities of being repeateddata.Oneperson only has one social security number.Generally, that social security number isonly supposed to have one person. So we havea a person. And then we have a social securitynumber. One person has one social securitynumber one social security number is assignedto one person, if you want to draw out yourrelationships, you can say person as one socialsecurity number, social security number.assigned.I'm sorry, yeah. Okay, so assigned to oneperson. So a person is assigned a social securitynumber, social security number is assignedto one person, we would not even though there'sa possibility that the social security numbermight be repeated. We want to design it thisway. Because that's like saying, the socialsecurity number is supposed to be assignedassigned to multiple people. It's not supposedto 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 manyrelationship in the next video and then amany to many relationship. And after that,we'll be talking about how we design thiswithin a database. How do we structure ourtables. So in the previous video, we talkedabout a one to one relationship, that is oneof the three kinds of relationships that wecan have. The other one is one too many. Whatthat means is that one entity can have a relationshipwith multiple other entities, but a specificentity can only be related back to one otherentity. I know that sounds a little confusingwhen confusing when we're talking about entitiesand all this conceptual, high level thinking.But think of think of it like comment on awebsite. When you go on to YouTube or whateverand you go below, you can leave a commenton my videos. That comment is only owned byyou. It has your has your name. That is aone to many relationship. The reason for thatis you can make many comments, but an individualcomment is only owned by one person. So todraw that out, you have I have a user. Andwe have multiple comments. You can see thatthe user is able to make multiple comments.But each individual comment, like this one,for example, is only owned by one user, thatis a one to many relationship, orone coin, many.Can you see that? Yeah, I think you can. Sothis is how a one to many relationship works.If you want to think of, again, like the marriagething. Think of a king, what his name himking, Sam. And Sam demands to have multiplewives, and he's super controlling of his wives.So Sam has multiple wives, right. So I'm justkind of dry on a chalkboard here. We haveSam the king over here.Give him a crown,give him a big smile. And he is royal andfamous among the lions. And he's really selfishwith this woman, he demands to have 500 wives,or however many wives he desires. So we haveall of his wives over here.So he has these two wives, and then he hasthis wife over here. She's not so good looking.But anyways, he is married to these threewives. Although he's very, he's very protectiveand very selfish with his women. And he doesnot want these wives to marry other men. Hethey're strictly married to the king. So theking can marry multiple women. But each womancan only marry one person. Oops, I kind ofalready have the line drawn, drawn. So theycan only marry one person, they're not ableto go marry another guy, that would be a wholenother way that ain't gonna happen off withtheir heads. See what I'm saying? It's notgoing to work. So that is a one to many relationship.In the last video, we talked about one tomany relationships, the video about that wetalked about one to one relationships. Well,now we're talking about the third possibilityof relationships. And that is many to manyrelationships. So when I think of many tomany relationships, I like to think of a polygamousmarriage, where multiple husbands can havemultiple wives. And multiple wives can havemultiple husbands. I just draw this out. Soyou can kind of understand what this wouldbe like. We have the king right here, right?So let's say he's a king, and he decides tomarry three women.Let's kind of make up real quick.This guy'sWhy 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. Butthen he decides Oh, what the heck, they canmarry different men if they want. who reallycares. 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. OneKing can marry multiple women. And one womancan marry multiple men. So this guy is marriedto this girl. And this girl is married tothis guy over here. And this guy's marriedto this girl over here. So Sally is marriedto Jim, who is also married to this girlover here.See my hand drawn here.And this girl, surprisingly, is married tothis guy. She's married to Kathy who is alsomarried to Jim. And by the way, Jim is marriedto the second wife of the king, who the kingis Mary did this girl over here. And you see,well, it gets very complex very quickly. Andthat is an example of a many to many relationship.Now, obviously, you'renot going to be storing, like, stuff likethis in a database. So let me think of a practicalexample 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 astudent. So here's a class. And here is astudent.Okay.Now, one class, how many students can be ina class multiple, you know that. So we canhave multiple students in this class. Let'sdraw boxes for quicker. So let's say all theseboxes are students. A class can have multiplestudents. Well, not only that, but a studentcan take multiple classes. So this studentcan take this class. And this class can betaken also by that student, and this studentover here. And this student can take anotherclass, which can have this guy in it, andthis guy, and this guy can be in this classas well. And by the way, he is also a dramajock. He is also taking another class, ifhe's in that class, which has that studentand that student, and it just gets very complex.So you can see this might be very, very difficultto store within a relational database. Andthere are reasons we actually can't storethis in a relational database. You might bethinking, well, what if you need to storethe classes that a student is taking in thisstudents within a class, we will be talkingabout in the up that in the upcoming videos,with solving many to many problems, many tomany relationships do not work in a relationaldatabase, the only two that will work is aone to one and a one to many relationship.But this is just the concept, you think ofa concept of a many to many relationship,think of a class and a student class can takemany can have many students and a studentcan take many classes. In the last three videos,we talked about one to one relationships,one to many relationships, and one to manyto many relationships. This video, I wantedto talk about how we design these relationships,I decided I was going to break them up intothree videos. So first, we'll be talking aboutdesigning one to one relationships, and thenone to many, and then many to many. So thisvideo will be about designing one to one relationshipsin a database. Alright, let's begin. So thefirst thing we need to realize is that whenwe have a one to one relationship, the theattribute side, so we have an entity. So let'ssay let's just say we have a connection betweena person and their, their username. Alright.So we have this, we have this guy here, letme get rid of these boxes. I don't mean this.We have this guy. And he has a username ona 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 goingto have that username is him. And he is onlygoing to have one username, that account isonly going to have one username. That's aone to one relationship. So we need to realizethat this is exclusive to this person. It'sall hits, no one else owns it, no one elseis connected to it. It's only hits. So thatmeans we can often store one to one relationshipsas attributes rather than entities. So ratherthan his username being an entity, it's anattribute. If you don't really know what thatmeans, don't worry, I'll be explaining injust a second. But just think of it as it'sexclusively his so that is part it describeshim. What is this guy's username? It's Calebcurry. It's an attribute of that person. Whenwe're talking about one to many relationships,though, are many too many. We think of likea class. The students of the class are notexclusive to that class. They can take otherclasses. Therefore it wouldn't really makesense to store them as attributes becauseit's not Really, it just doesn't make muchsense. And I know, it'll, it'll be clear,so just Just wait a second. So we can actuallyhave a table. The relationship between theaccount and the username of the account canbe 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 usernameis exclusive to this ID of this person. Solike six,Caleb,Caleb curry, that is how we would design aone to one relationship, we would put it withinthe same table. If we have a new row, withinour table, we now have a new person, thisperson can't have another username that Calebcurry, they have to have a different one.So we can make it john 123.This name is exclusive to that person. Sowe 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 individualentity. So the entity is the the account,the user name is an attribute to that an account,there will be occasional times when we storea one to one relationship over multiple tables.And I will explain now when that would bethe case, let's just think of an example ofa database for a credit card company, we havea relationship between the card holder andthe card. By card holder, I just mean theperson who who gets the card and can use itto buy things, the person who owns it prettymuch. And then we have the card. All right.Now let's just say this, this company onlyallows you to have one credit card. So thiscard holder can only have one card, and thiscard 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 thingthat we talked about earlier, here's whatit would look like. Here's our table. AndI'm just going to list the columns. So wehave the cardholder table. First thing iswe'd have like a an ID for the card holder,we would have probably card holders firstname, the card holders last name, and thenwe would have the card. So we could say, well,that's their card, we could let's say we couldassign every card and ID or a card number.So we can say card number. If they have acard, we can give it a value if if they don'thave a card, we can leave it No. Well, thatcan be considered an attribute of the cardholder. Because all these points back to thecardboard. Now, if we want to store more informationabout the card, such as the the issue datewhen the card was given to the person, sowe could say, card issue. date. Well, nowwe can see that this is relying on the card,not the cardholder. So if we want to storeextra attributes, about the attribute in theone to one relationship, we can move thatto a new table. And we can make it a cardtable, give it an ID. And then we can saycard numberand then issue date.Now we can store as much information aboutthis card. So if we want to store the maximumout, be a late fee. Pretty much anything wewant to store about this card can be storedin this table. And we can replace this cardnumber with a reference. So I'm going to cardID and that points back to this ID. You seewhat I'm saying? This is a one to one relationshipover multiple people, just like this cardhas a connection to the cardholder.There's a one to one connection over multipletables. The reason we did this is so it couldstore more information about the card. Becauseif we stored it all in one table, we wouldhave bad database design. And I'll show youwhy. in just a sec.Alright, sohere's our table.Can you see that, all right, we have the IDand then the name of the person. And thenwe have the card number, and then the maxamount, and so forth. Well, you can see thatthis table is really about two things, it'sno longer about one thing, we would have tosay this is a user and card table, which isnot proper, because we're not following therule of one where a table should be aboutone entity. And a row should be about oneentity. Because now we have the user side,and then we have the cards, it's like tryingto store two tables. In one, the only timeit's acceptable to have the card number inthe users table is when we don't store moreinformation about it. Because now all we haveis a card number. The card number is aboutthe user and points to the user. But the maxamount of the card has nothing to do withthe user, therefore, it's reliant, dependentupon the card. So I wonder one relationship,in conclusion, one, a one to one relationship.The way it is stored is eitheran attributewithin the table, or if you need to storemore information about the entity, then wecan have another table and then just use foreignkeys to connect them which that's somethingwe'll be talking about in an upcoming video.Almost always though, you will be seeing aone to one relationship, used as an attribute.So when you think if you have a one to onerelationship between something, you have aconnection, let's say it's a dating website,the user is in a relationship with anotheruser, that that's a one to one relationship,we could just have that as a column withinour user table.So we could havethe user table, we could have the user ID,and the name, first name, last name, a phonenumber, whatever else. And then we can say,relationship. That's a one to one relationship,we could just have that have that be an IDof another person. And we don't have to worryabout having another table for that. Thisvideo, we're talking about designing one tomany relationships. First, I want to bringback an example from the last video into thisvideo. So we can kind of explain the differencesbetween one to one and one to many. So inthe last example, we had a credit card companywhere a person is allowed to have only onecard and a one card can only be owned by oneperson. Let's draw that out. We'll just drawthe 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 whenwe store a one to one relationship over multipletables. Otherwise, we could just store itas a column because I mean, we're only storingone thing. We have the user ID. And then wehave a card ID. And then to connect theseas a one to one relationship. We have a foreignkey. So we put a connection card ID in theuser table, the user ID in the card table.So Alright, ID, user ID, that means that whatthat does is it connects these tables. Souser ID to this user ID, card ID to this cardID, that is how we would make a one to onerelationship over to tables. And then we canstore 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 specificcard can only be owned by one user. So there'sno card can't be owned, or co signed or ownedby two people. Now, the way we do this, islet me just erase these columns real quick.As a start from scratch.So we have a user table, and then we havea court ID. Once again, we need IDs for bothof them. So we have a user ID, and then acard by the user owns multiple cards. Andwe don't know how many they own there, theycan have one, they can have two, they couldhave three, they could have four, they couldhave five, so it wouldn't make sense to putthe card ID in here as multiple columns. BecauseI would put five columns for card ID, well,then what if someone had six cards? What arethey going to do? They're not, they're notgoing to have a sixth one. So what we do iswe put the user ID in the card table, whichpoints 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 hisuser ID is 63, just a random generated numberby the database. Well, we also have cardsover here. So we have this credit card, wehave this credit card, we're actually drawingcard cards here. So we had the barcode andthe 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 thisguy. So we give a user ID, we could say theuser id 6363 63. So now we know that eachcard is owned by the user with the ID of 63,which we can figure out is this guy, whateverhis name is, we can join that. So now we storea one to many relationship into tables, wherethe many side gets a foreign key to the singlethe one side. And if you don't know aboutforeign keys, that's fine, basically, in simpleterms of foreign key is just that that Idconnection is user ID is a foreign key thatpoints to the user ID in the main table, foreignkey points to the primary key and the primarytable. This is also said to be a child. I'msorry, a parent child relationship. This willbe the parent because every single child hasto point back to the parent. That is how westore 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 ashopping website. So you're allowed to leavea review there. Let's say you can leave areview. And we had this table these this tablesetup. We had the users. And then we had thereviews. And we had a an ID user ID. And thenwe had a review ID. I just give that to everysingle table. Every day I give every tablean ID. Then we have the username. And thenwe have the actual review. So the user hasa username, the review has the actual comment.And we could also say we could say we havea review title. So we can say like oh Thisreview is this Product sucks, or this productis really awesome. And then we can have aparagraph saying, why that products awesome,for example.Right now,this is not properly designed, because there'sno connection between the user and the review.Because if we look at the review table, whoput who posted the review, it doesn't sayall we have is the review ID, the review andthe review title. It doesn't say who postedit. So if we put a key foreign key that pointsback to the user table, that would say thatis the parent, the person who posted thatreview. So we can have a user ID, which pointsback to the user ID in the primary table?Is this a one to one relationship? Or is thisa one to many relationship? Well, this, wecould have multiple reviews, and each onewould point back. So we could have a anotherreview by and we can have the same ID. Let'sjust think of some examples. Let's say wehave the review ID of six. And then the reviewis awesome, aregood.And it's by the user ID of 62. Well, thenwe could have another one with a review IDof seven. And it could say, Great. And thenwe can add it from the user, Id 62. Well,these are both from the user id 62. So wecan say this is a one to many relationship,because multiple reviews going back to oneuser, if we want to wanted it. So a user canonly post one review, well, then we need toput the review ID in the user table. So nowwe have this sort of like this double connectionthing here.Because this review ID connects it. So it'ssaying only one review ID can be from theuser. And honestly, this, this can probablybe improved, because the review is not necessarilyconnected to the user that directly. For example,if the website is solely forgiving, one review,well, then we could give it a review ID. Butthat might not be the best example, simplybecause the user might buy multiple products.And each review could have a product ID easy.So I mean, it kind of gets complex and justkind of have to study. But the review, itwould probably be best to make it a one tomany relationship, because it wouldn't reallymake sense to have it as a one to one relationship.We want it as a one to many, because multiplereviews for multiple products can be given.So this points to a product, this points toa product, this points to a product, and they'reall by a specific user. Alright, so yeah,that's just some basic design concepts thatyou will need to understand. When you havea one to many relationship, just store overtwo tables, and give a foreign key to themany side pointing back to the one side. Beforewe 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 isalways the child. And when we're talking aboutkeys, keys keep things unique. And they alwaysthey're they're used to connect tables thatare related. So every t table has a key thatkeeps it unique. When we create a foreignkey, it references that primary key in a anothertable. So the primary key is the parent. Theforeign key is the child. The child pointsback to the parent inherits the values fromthe parent. For example, the parents ID isa six then the foreign key pointing back toThe parent is at six, the job. So in a onein a one to one relationship, where we juststore everything in one table, which is themost practicalway to do things, we don'thave to worry about primary keys or foreignkeys, we just have one table. And that's allwe have, they only have columns within thistable. So we have the user ID, the password,the username, the whatever else. All thoseare just columns within a table, we don'thave to worry about a parent or child, becausethey're not making any relationships acrossmultiple tables. But when we get to one tomany, that's when we have this table pointing.Or has many children. So you would say thisis the one side it's the parents. Then wehave each of the parents children. These allpoint back to the parent with aforeign key.So if you think of like comments on a YouTubevideo, every comment has a poster or a useraccount of a person who clicked post a comment.That means that the commenter is the parent,and the comment is the child. Another wayto think about that is if you if you weregiven a comment on YouTube, you can figureout who posted that comment. That's becausethe child always points back to the parent.Now, if you look at a user account on YouTube,such as Caleb, the video maker to awesomechannel, check it out, and be sure to subscribe.Well, you don't necessarily know every singlecomment that that parent has given, theremight be a feed in there that says recentcomments.Butwhen we look at thechild, we always know the parent because itinheritsthe values.But the parent doesn't inherit anything fromthe child. Think of like in real life, youcan see a married couple,andyou don't know if they have children or not.But if you see a little baby, we think, allright, that baby has a parent or parents.That's the similar idea with database design.Think of this wall here between the parentand the children.The childreninherit values from the parent such as theforeign key, a seven, that tells us who theparent is. That is important. I know, it seemslike okay, who really cares, who's the parentor child, just get on with the database design,you knew, I'm going to unsubscribe? Well,this is important, because when we get tounderstanding how to design each relationship,we need to understand where we put the foreignkey, which table does the foreign key go into.that is decided by the child because the childalways has the foreign key that points backto the primary key. So user ID, primary key,user ID, foreign key points back to the primarykey user ID. So foreign keys always go inthe 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 manyrelationships in a database. And that's whatwe're going to talk about in the next videowhen we talk about many to many relationships,specifically. But just know that when we havean entity, think I want you to think of whetheror not it would have a parent, a lot of thetimes it would but other times not so much.But if you think of think of an order fora store, an order needs a parent, becausean order is when you buy product who is buyingit, someone has to buy it. Otherwise it wouldn't.It wouldn't exist, the child would not existwithout the order. So let's let's relook atthis.Thisis the user and this is the orders. We can'thave a child without a parent logically, itjust doesn't make sense. So if there's noparent of the order, the order cannot exist.First, we need to keep that connection. Andorder needs someone who bought that order.Hopefully, that's kind of making sense. Sojust keep in mind, parent child relationships.I'm not trying to repeat myself, but I'm tryingto repeat myself. So you get destroyed inyour brain, foreign key goes on the child,primary key goes on the parent. And soon we'llbe talking more in depth about keys. So justfor now, memorize primary key and foreignkey primary is the user ID. The foreign keyis a reference to that user ID. It's in aforeign table, and it points back to the originaltable. This video will be about many to manyrelationships. Oh, and we're talking aboutdesigning many to many relationships. So Isaid a couple of weeks ago, videos ago thatwe have problems when we design many to manyrelationships. They don't work out, right?They don't. And I mean, I never really explainedwhy. Well, this video, I'm going to explainwhy. Think of, let's think of a good example,a college class can have many students anda student can have many classes. So we havetwo entities we have to worry about.We have aclass.Let's just pluralize it because it makes moresense, the classes, and then thestudents.So let's just think of an example of a waywe would try to treat we would try to designthis, and it's not gonna work out, right,but we'll just we'll just try. Here we haveclasses table, and we have a specific classsuch as math, math, 101, whatever, really.So we have Matt, we can't even read that myhandwriting is so bad. So we have math oneon one. And then we list all of the peoplewithin this class. So we have student numberone. And then student number two. And thenstudent number three. All right, well, wecan try that. Except, what if a student dropsout? Well, then we have a null value, whichwe don't want to have those if possible. Andwhat if we need four students? Well, that'snot going to work because we only have threecolumns. So how else can we do it? Well, wecan try just having students. Well, then everysingle column could have like 100 students,and we already learned about the atomic rule,we only want one column to store one valueone 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 havea table for students. So we have Jimmy, andhe's taking his first. Alright, let's let'slist the columns. So we have the name. Andthen we have class number one. And then wehave class number two. And then we have classnumber three. Well, that's cool. What if hewants to take four classes, though? Well,then we have to add a new column. And let'ssay someone psycho crazy guy takes like 20classes. So now we have all the way down toclass 20. And then a new person comes to theschool. And he only takes one class. So nowhis first class is like math. And then there's19 columns for that guy who are empty, becausewe have to half the columns for every singleperson. We think of it like this, we drewthat out. So where you can see it better,we would have a table. And then we'd havethe ID of the person. And then the classes.So once puts like class one, class two, classthree, class four plus five, six. Let's justleave it at that. And we'll just kind of makerows for these. We have new guys sign up.So we have a guy with the ID of six. And hisfirst class is math. Right? And then his secondclasses, geology and then Spanish, and thenfashion.And thenbiology and then chemistry. And then we havea new guy come and he his IDs, eight, andhe only takes science right? Well now we haveno No, no empty nothing. We wasted all ofthis space. And you can assume that's goingto keep happening. So that's wasted spacein our database, bad design. So how do wepossibly do? Well, the trick is, because becausewe think of if we try to think of like parentchildren, who's the parent in this situation?Well, a class has multiple students. So thatwould make the class the parent and the students,the children, but a student can have multipleclasses. So that would make the student theparent and the classes the children. So howis it that this is the parent, and this isthe parent at the same time,that meansthis points to a child. And this points toa child. So this parent is the parent of thisparent who is also the child of this child,which that just like blew my mind, I don'teven know what's going on. So the way we dothis, is we break it up into two, one to manyrelationships. So this many to many, is goingto become a one to many. And then a second,I think, I guess all right. Okay, just foryou guys, as information, these these colonthings, I think the correct way to do thatis many two, and four, like the like the endkeep going on. Just don't don't worry aboutthat. We'll talk about that. in upcoming videos,I'll just write that for my sake. So we'regoing to break this up into one many. Andthen 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. Andstudents. What we need is what's known asan intermediary table, or a junctiontable,they're both the same thing. I'll write thatout. So you can hear me enter mediary or ajunction. There's also like 50, other namesfor this table, but I mean, just intermediarytable will work fine. So I n t e r m e d ayay ay ay er y. And what that is, that is howwe connect our tables. So we break this upinto a total of three tables. So we have theintermediary table, which is the one I justtalked about. And then we have the studentstable. And then we have the class table. Soclass, student, this is the intermediary table.So we have a one to many relationship goingthis way. And then we have a one to many relationshipgoing this way. So one,many,one, to many. I know that seems a little confusingat first, but trust me, it'll make sense.Once we start explaining things more. So wehave one to many, one to many. And this isthe intermediary table. So let's just drawthis connection. So we kind of make can visualizethis a little more. And kind of, like splitthis to see how it works. So we're storingthe many side of both relationships in onetable. That's because we know that a one tomany 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.Andthese are going to have foreign keys thatpoint back to the class and the students.So now that we kind of understand what itlooks like when we draw it, let's give specificexamples to illustrate this. Actually, likewhat it would work like look like whatever.Alright, so let's say we have our classesover here. And our students over here, okay,sofirst things first.We have metaphor, just keep it simple math.Science, I mean, obviously, the more in depthlike math 101, math, two offive, or1200, or whatever. And then we have English.But just for simplicity sake, let's say theseare 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, allI ever talked about is myself. Alright,let's get rid of my name.Let's go withJohnny, and Jake, and Sally,and Claire, Claire.Alright, so here are all of our students forour class.classes,students,we connect these in the middle. So first thingsfirst, we give them all an ID, because thatwill be our primary key. So we'll just givethese random Assigned Numbers, which is prettymuch what a primary key is a surrogate primarykey. So we'll get this 163, this is somethingour database would do for us. This one willbe 75. And this one will be89.So you guys can see that clearly, we'll dothe same thing over here. So john will havethe ID of eight. Jake, the id 17. Sally, theID of 16. And Claire, the ID for just getting666. Because she's 666. I'm just kidding,we'll just we'll just go withsix.Now we use the intermediary table to connectthese IDs. So we have this table here. Wehave two columns. We have the class ID. Andthen we have the user ID. And then if youwanted to title these tables, we can makeit classes, class students and students, theintermediary table, what an intermediary is,it's something that connects two things, it'slike, it allows this table to talk to thattable. So it's a connection between thesetwo tables. We put the ID of the user withthe class that they're taking. So let's sayjohn is taking English in science, we wouldhave 875 889, right really big, I'm goingto run out of room really quickly. And whatthis does, is using this intermediary table,we can figure out that all of these user IDspoint back to a specific person. And theseIDs point back to a specific class. We arenot repeating data, because we need to knowthe user have this class ID This is a foreignkey pointing back to one specific value. Sothat means if john decided to drop out, well,then we have foreign key constraints to getrid of these. That way, we don't have to worryabout incorrect data or users. I guess, I'msorry, this should be like students, sorry.But we don't have to worry about studentswhowe don't have to worry about students beingenrolled who don't exist. So that is the solutionto in many to many relationship. This videois getting really long. So Sally could dothe same thing. We could say 16. And then75. She's taken science, Claire, she's takena she's taken math. We keep going on if wereally wanted to, like can make this tableas big as we wanted. And basically this isthe easiest, best way to use as much databasingresources storage as possible, as well isnow we solved the many, I'm sorry, the parentchild relationships. That's because we havethe parent over here. We also have the parentover here. They point to the child table.So this child table becomes the child of bothof these parents. Sort of how in real lifeevery single person is twoparents andthe person is this in Tire the row mean, okay?applying that to this, the parent is the classand the student of the class, and the childis a row where we have both the class andthe 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 takingscience and Claire,I'm sorry, math,Claire's, taking math, and player is takingmath, that doesn't make sense. So this righthere is unique 63 to six, you can have itin there twice. So the child is an individualconnection between the parent and the otherparent. So that is how you properly storemany to many relationships. And I know theall these relationships are kind of complex,and you don't really understand with likea stupid chalkboard. But like I said, we willbe getting into on screen computer videosand the introduction. I mean, I said thatin the introduction, we're not going to dothat introduction, because that wouldn't makesense. So yeah, we will be explaining theseconcepts. And then we will be applying theseconcepts to actual databases. Once we getthrough all of the database concepts. Thisvideo will be a summary of all of the relationshipgarbage that we learned. And basically, thisvideo is going to be like, all of it in one,and it's going to be awesome. And it's goingto make a whole lot more sense after this.So the very first thing that you need to realizewhen it comes to relationships, is that there'snot always a defined relationship for everycircumstance. And it's sometimes you have,it's something that you have to decide onyour own. So my old database design series,which if you want to check that out, justgo to my YouTube channel. A lot of peoplewere confused about, okay, is a student toclass? Is that a one to one or one to manyor a many to many relationship? Well, that'ssomething that you're actually gonna haveto decide on your own. Sometimes you haveto think logically about the application ofthe relationship. So let's think of the exampleof a class and a professor. What kind of relationshipis that? I don't know. Let's try to find out.So we have the class. And then we have theprofessor. People would ask me, like, Okay,what kind of relationship is this? Well, itdepends on the application. If it's me, itcan literally be all three, it just dependson what you need for your application. Ifit's a one to one relationship, that meansa class can be taught by one professor, anda professor can teach one class. If it's aone to many relationship. That means eithera class can be taught by one professor, anda professor can teach many classes, or theopposite of class can be taught by many professors.But a professor can teach only one class.Or it can be many to many relationship, aprofessor can teach multiple classes thatcan be taught by multiple professors. So itdepends on the college because some collegesare going to allow the class to be taughtby multiple professors, other colleges arenot going to allow that. So that depends onthe application of the database. If I'm buildinga database for a college that does allow multipleprofessors for a class, that means it's amany to many relationship, because many professorsteach many classes. Many classes are taughtby many professors. Right? So when it comesto designing one to one, one, to many, andmany to many, I'll show you how to do allthree of those for a class to Professor. Soif we have one to one we could have, for example,we could have the professorright. We'regonna have a professor table only know howto spell it. But prof is I don't know if there'stwo F's, who cares, Professor table, right?If it's a one to one relationship, we canhave the professor ID, the name of the professor.And then we can have a class. That's the classthat they teach. And it's the only class thatthey teach. That class is only taught by thehead professor. That was, that's how you storeone to one relationship. And typically, you'renot going to do that with another entity.When it's when it comes to like a name. Youtypically will do that because the name isjust assigned to that person. A class cangenerally I mean, typically professors canteach multiple classes unless you're someKind of adjunct teacher, but class, in thiscase is a one to one relationship. If we wantedto make it a one to many relationship, orthen we would break off class here, get anew table class, we have a class ID. and herewe can put more information about the classand we'd have the class name. This is themost common way of storing information ontwo tables. You rarely very rarely store aone to one relationship over multiple tables,but it is possible we talked about that earlier,we'd have class name, and then we can havemore information about the class. And thenwe have a foreign key class ID, that wouldmake this the parent because class ID pointsto class ID, if you wanted this to be theparent, well, then we would switch that wewould makeProfessorID,which was probably the most logical way todo that. But you have to keep that in mindbecause it can be a one to many directions,a one to many this direction, or can be aone to many this direction. And obviously,typically, the class is going to be the manyside because a class can be taught by multiple,I mean a professor teach multiple classes.That means that professors on the one sidethat many is the class, which means we needto give the professor ID on this side. Sothe proof ID is a foreign key, referencingthe proof ID in the professor's table. Thatis how you draw a one to many relationship.This direction one to me, parent child. Nowif we wanted to make that in many to manyrelationship, we'd actually had to break thisup even farther into three tables. So nowwe have the professor's table. And then wehave the class table. And then we have theprobably class professors table, which isthe intermediate intermediary table.So we havethis table here. This table here, this tablehere, this, these all, this will have an ID,Professor ID, and this will have an ID, classID. Then you reference this in this intermediarytable, where we'd have Professor ID of likesix with the class of seven, Professor IDof six, with the class of eight, which isbasically saying this professor with the IDof six is teaching two classes. Now, whenit comes to IDs for this table, this wholething can be an ID in itself. Or if you want,you can give it a new ID to say class ProfessorID. And that's kind of unrelated. So that'spretty much we'll get into that later. Butthat is pretty much the song of designingrelationships. One to One is typically inone table, one to many, it's two tables withthe child having a foreign key pointing tothe parent, and then a many to many has toparents, with the intermediary being the childpointing back to both of the parents. Hopefully,that is all crystal clear. And awesome. Becausenow you literally know how to design everybinary relationship and database design. Andby binary, I mean a relationship between twotables. So you can have a binary relationshipwhich we talked about, there's one to oneover two tables, there's one to many overtwo tables, and there's many to many overtwo tables logically, but then we have tobreak those up into the best way to storethose to store those in a database. But thisis basically binary because we're talkingabout two entities. A class and a professor,a student and a class. You can have relationshipsbetween multiple tables, but that is somethingI'm sorry multiple entities, but that is somethingwe 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 aboutkey words.So, a key is not something you use to unlocka door. keys in database are something else.A key keeps everything unique. Basically,that's that's the easiest way to rememberwhat a key is. So when you think in a database,we kind of structure things in tables. Andwe want to kind of do that we do we do that.We do do that haha. Anyways, if we have atable here, we have the columns, which arethe attributes of each thing. So like, let'smake it a user account table, because that'salways the easiest example. So yeah. Okay,user. So that's the title of the table. Now,each individual row within this table is goingto be a new user, each column is going tobe a thing a bout the users. So the columnscan be username, first name, last name, password,and email. As an example, I mean, you canreally put whatever you really want in there.Well, a key is something that is going toseparate this row from the rest of the rows.So in this example, think of the things thatcan be duplicated, think of all the possiblevalues that you could put within one of thesecolumns. And think, is it possible to duplicateit in your application? I mean, yeah, it'spossible to duplicate it if you allow it tobe duplicated. But what naturally should notbe duplicated. So typically, when you signup for a website, or a, a game or something,they'll ask for your email. Right? Now, whenyou sign up, you put your email and you typicallyhave to go to your email, and click like confirmationor something to register your email. So theyknow that it's a genuine email account. Basically,just so they can have your email and theycan send you emails and stuff. But yeah, thatis an example of what we could use as a key.That's because every single row within thistable should have a separate email, some websitesallow you to use the same email more thanonce. If that's the case, then this wouldnot be a good key. This kind of key is knownas a natural key. Because it's naturally alreadyin our at our table, we don't have to definea new column just for the sake of a key. Andwe'll be getting into more of that in upcomingvideos because doing a whole bunch of videosof 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 whereonly 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 waythat works, is it basically let's say we haveemails 123 at blah, blah, blah, a VC, blah,blah, blah, x, y, z, blah, blah. And theseare three separate rows. Well, this is goingto let us know which person we're talkingabout. And, like now, if someone has the firstname, first name and last name by chance,let's say we have two Caleb Curry's Calebcurry, Caleb curry, we can't use the nameas a key because two people had and it's,it's going to confuse us because Okay, dowe have two people with a name Caleb curry,or one person Caleb curry in the databasetwice? Is that an error on our part or what?So we can't have the key. Be in there morethan once. So it's never there's it's alwaysunique. So the key is always unique. He said,he said, he said,Alright, so what is another example of a keythat we can use in this table? Let's get ridof this email. Let's say we're not using theemail anymore. And let's say the website allowsmultiple emails from different people. Solike if my mom signed up, and then I wantedto sign up, I can use my mom's email. So wehave two people from the same email. That'san example of some websites that allow youto do that, but not tons of websites do that,but it is a possibility. It all just dependson what you want to do. It's up to you becauseI guess it's up to the if you're making adatabase 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 followthem. Let's think of some other ones. Alright,well email, let's just say it's knowing yourpassword.Well that's really a bad one. Because passwords,they're not necessarily unique because twodifferent accounts can have the same password.And if if you didn't allow that to where theycould have the same password, you try to putin a password for your new account. And it'dbe like, Oh, this password is already in use.And then you're like, Oh, so then you cantry that password on other accounts and tryto hack into people. So that would not bea good database design, obviously. So password,that ain't gonna work because not every passwordis unique. First Name, Last Name, it couldpossibly work. But it's really bad. Maybefirst name, last name, and middle initialor middle name. But even that there's, there'speople with the same first name, last nameand middle name. So that's first name won'twork, last name won't work. And the combinationof first name and last name will work. Andyes, keys can be a combination of two columns.We'll be getting into that too. What we haveleft is user name. Well, user name is generatedword that is used to represent your account.It's typically not a name or an email. It'ssomething like Caleb 123, or whatever, youknow what I'm saying. So then, when peoplewill talk to you, they're talking to Kayla123, rather than Caleb curry, or they canput the name there. But some websites useusernames, or some games use usernames orprograms use username, so you have a usernameand a password. Well, username is always unique.Because if you have two people with the sameusername, when you try to sign in, how isthe database going to know which one you'retrying to sign into, you know, they're youthey have to be unique. Some of that kindof makes sense. That can be used as a keybecause we could have the username, cave offood. And then we can have doors 123 and thenpop. These are three different people, allthe other stuff, first name, last name, password,email, they point to that key. So we havea first name, and a last name, and password,and an emailforthe user with the username, Caleb, see, that'show the key works. We have a first name, lastname, password, and an email for the personwith the with the user, I'm sorry, we havefirst name, last name, pass email for theuser with the name, dog 123. And then we havea first name, last name, password and emailfor the user with the username, pi, that'skind of how the key works. The other thingis that the key should never be changing.So the second thing,never changing.Now, why is this? Well, that's because whenwe create keys, we use them to kind of structureour entire database, everything points towardsthe key. And the last example we had, I'lljust draw key for representation. Let's saythis is the key and we'll just say it's theusername. Right? Well, every other columnis part is points back to that key. So wehave the name of the person, we have the password.And we know that these are pointing to thisspecific person, if we got rid of this keyand that we have two people with the nameCaleb with the password, pi.Well, now,are we talking about the same person? Arewe talking about two separate people? Or whatwe don't really know? But now if we give itkeys and key let me get get that key back.So thislet me see if I draw key right?Probably not.Close enough. All right. Here's our key. Nowwe know that this Caleb points to this specificperson we can have the the username, or youmight often see numbers like 72 the ID, whichwe'll talk about that too soon. Now, if wehave a separate keylike this,well then that is a different person so wecan have this guy have the username. Calebis awesome. 72 this got me like sexy chicks.74. See what I'm saying? So that's kind ofhow the keys work. We never want them to changebecause then it's kind of it's confusing becauseall of these things objects are part of thiskey. Basically, this key is used to defineuniqueness. Well, if we can change it, itquestions the integrity of our database. Becausebetween tables, we connect things by keys.So let's say this is a user table, this isa common table. If we have it to where wecan change the key, well, then this connectionis, you're gonna have to update this connectionall the time. Now, if we have 50 tables connectedto this user table, I'm just going to drawout for snakes like well, I won't draw all50. Well, now when we update this key, thiskey connection. So we have a comments by auser, we have sales, we have friends, we havemessages, whatever, it doesn't really matter,well, then we change this key, well, thatmeans every single one of these connectionsare going to have to update that requiresa lot of work from the server. Makes sense.So we don't want them to change, because wedon't want to have to have that extra workfrom our database. So that's one thing, unique,never changing. The other thing. These arejust three general roles, there's some othersuggestions and stuff, but I'll get into thatsoon. But the other thing is never know. Thatmeans empty VAT or no value. So when we havea table, and we have a key value, let's saywe have a user ID, this guy six, this guy'sfive, discuss for this guy's 30, it doesn'treally matter the order. And then we havetheir name, whatever, blah, blah, blah, well,we don't want it to where we can have a blankID and still have information. Like, Sally.This Sally doesn't have a key, that is anotherthing we do not want to do with our keys.So here are the three main rules. And we'llget into more in upcoming videos. But thekey should always be unique. Never be changing,and it should never be empty. You should notallow a row without a key. This video, I wantedto talk a little bit more about introductionto keys, although I'm going to go out justa bit off topic. And I'm going to talk aboutindexes. So I don't think we talked much aboutthese in this series yet. But we are goingto be talking about an index. Alright, sowhat's an index? I'm not going to be explainingevery single detail of indexes, because we'renot really talking about indexes. In thisvideo, we're talking about keys. So I'm justexplaining enough so you understand how thisrelates to keys. So think of an index, bestexample I have is a book. So if I have a reallyawesome book, like, like physics or something,what I can do isn't like, Oh, I want to knowabout 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 readabout that. Well, that's a lot quicker thanbeing like, Oh, I want to know about this.page one, not on their page two? Nope, notthey're not not there. Oh, no, not there.Huh. Keep going through the entire book untilyou find it. That will take forever. Anothergood example is a phonebook. When you go througha phonebook you have, everything's alphabetic,and it has the data right there. So when youthink of an index in a book, it points youto the data. Whereas a phone book, it justhas the data there, you can be like, Oh, I'mgonna go to the letter G, find grandma incolor. Right? That's kind of how an indexworks. 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 thisgreat, crazy number. And we want to find everybodywith the name Caleb just came. We want tofind everyone with the name Caleb. Well, withoutindexes. Basically the database is going togo row one. Does it have the name Caleb? No,it does. row two doesn't have a name. Calebno row three. Yes, it does. We'll put thatwe'll put that back in the return statement.And then it goes through every single thingevery single row within the entire table,which can take ages. So if we're given anindex and we saw We order that data in a waythat 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 howan index works. Well, what's not to do withkeys, when when we define a key, it's actuallya type of index. That's because keys and indexesare used for SELECT statements, where clausesand joins. So I could do something likeselecteverythingfromusers, for example. There's just like a SQLexample. I mean, it'll vary from whateverprogramming like relational database managementsystem, you use this as a general example.And then I could say, where now this is whereI specify. Sorry, drop the wait. Hold my booksanyways. I'm selecting all of the columns.And then I want to select every column wherefirst name has the value Caleb, for example.Well, that's how we would do it. So this righthere, this little where thing? Well, it'sbest to have an index for that. Another thingwe commonly do are joins, which is basicallycombining two tables. And I'm not going togo into all the syntax for all that. Basically,we have a table over here, users, and thena table over here, comments. And we want tomake a generate a new view, where we haveall of the comments from a user and all ofthe information about that user, we couldhave a new generated view. But we can jointhese statements. And how does it know howto join it, it uses the I uses the key, right?So if you have a primary key, and let's saywe use username,so the keyon drala key here. The key is username. Well,how would we join it, we would say, I wantyou to take this table, and this table, andthen combine all of the rows, where the usernameis Caleb curry over here. And the usernameis Caleb curry over here. So that way it knowsall of these comments are from Caleb curry,which we can assign to that user with thename Caleb curry, or you might often see itwith like an ID number. So it could be likeID six, take all the comments from the userwith the ID of six, join it with the SEC,take all the rows with the user with the IDof six, join it with all of the rows withthe user of ID six, and it combines them individualrows for each ID. Within when we make a primarykey, or just a key for now, we're definingan index. So the database can easily accessthe 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 kindsof types of index.Look up tables. So what is a lookup table.Let me I have to kind of explain somethingelse first before we're going to get intothis. So just kind of relax and take. Takea good look. I'm going to erase this so Ican have some room. So let's say we have amembership table or membership database, andwe have a user table or members table. Andwithin 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 nota member or like a trial member orpartner orspecial member or member above all members.I don't know I'm just kind of making stuffup. Anyways, some people will create what'sknown as a lookup table, so they'll have atable over here with every single option forthe membership So we could have the memberships.And then within here, we would have, eachindividual row is one of the membership statuses,right. So we have like 123456789, I'm justgonna go at nine because can't fit anymore,nine individual rows. And then we so that'sone column, the ID, for example, which willmean we could use a different key if we needed.But for this, we'll just use an ID. And thenwe have a column to say what the membershipstatus is. So we're gonna have like, gold,silver,bronze,platinum, and so forth. Now, we can have amembers table.And then a columnwithin this member table, can reference thekey of the membership. So remember, like oneor two videos ago, I talked about how keysmake connections between tables? Well, thisis how that's done. Here's an example of aconnection using a key, what we would do iswe 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, whateverit is, and then we would have a column membership,you see. And within here, let's just say forexample, we didn't have this table, this wasgone, we just had this member table. Well,when we have 6000 rows within here, the membershiptype would get repeated. Lots and lots oftimes, we would have like gold, gold, gold,gold, bronze, platinum, platinum, platinum,bronze, glow, platinum, bronze, and go ongo on for all of the rows. And that wouldwork. That's that that is a possibility foryour database. I'm not saying it's going tocause your database to explode, or you'llget fired or anything. Well, unless you'retold not to do that you might get fired. That'sa possibility for the database design. Nowlet's think what kind of relationship is this?One member can have one membership, and amembership can have multiple people havinga membership. So it's a one to many relationship.The one side is the membership, you'd haveone of these, and then you have many peopleusing that. So that means if we if you rememberfrom the relationships, we need to take thekey from this one, and put it over into thistable. And now if we add this new relationshipin, we don't have all of that repeating databecause all we do is reference one individualrow. And there's some other benefits to that,which I'll explain in a second. So now wecan be like rather than gold, gold, gold,silver, bronze, platinum, we have 111234 forall of our rows. And we still have individualinformation like Caleb, Jimmy, Jake, Sally'sSammy, and Sarah. Well, when we do this littleconnection thing, we have a new connectionbetween these tables. We're taking the keyof the membership, which is this column righthere. So here's our key. And we are takingthat key, and we are moving it over into themember table. So now this column right here,within the member table, points back to thekey of the membership table. This is knownas a foreign key. Now this will help protectthe integrity of our database. That's becausethere's only nine possibilities for the membershipand if someone puts something else we couldflag an error. The other thing is we can haveit to where it will not accept an empty value.And then if for some reason we decide to changeour like let's say we wanted to change silverto like I don't know diamond well Then wecan change it here. And because this valueis not over here, it's only a reference,it will automatically figure it out. Becauseyou see the key never changed. The key isstill one. So one still points using one.So this connection is still here, the valueor I'm sorry, I guess it should have beentwo. Whatever you get my point up, we canchange the name of gold, we can make it epic.If you want epic membership, well, now, wedidn't have to change 6000 rows within ourmember table, we only had to change one rowwithin our membership table. And all theseconnections stay the same. So the IDS helpedcreate better connections between tables requiringless maintenance for our database. And italso protects our integrity, because we don'thave to worry about incorrect values withinthe membership. Because it's all connectedback to the membership table, which we changefrom here. So we update epic, it updates everysingle instance of epic within our entiredatabase, or it gives us an error or something,we can set it using what's known as foreignkey constraints. That key foreign key constraint.So because we're using what's known as a relationaldatabase, which is the type of designing we'redoing 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? Whatdo keys do for us? Let's write that out. Well,first thing, they protect our integrity, Imade a video over integrity. So if you wantto check that out, be sure to do that. Sothey protect our integrity. How do they dothat? Well, we don't have to worry about onlysome values updating. If you remember, wehad the members table. And when we updatedgold, and we named epic, it updated everysingle row within the member table. So itprotects all of our values. So updates, lessmaintenance for us less incorrect data. Whatelse is the key deal keeps everything unique.And you might have saw that in the membertable, we use the the key number one, multipletimes, we had three people with the memberID, I'm sorry, the membership ID one. So threepeople had gold, for example. Well, it's stillunique, because they're still only talkingabout one individual gold. Over here in ourmembership we had gold was one of the options,we could have three people. Alright, you seeup for her arms, these three people can allhavea gold membership.And it's still unique because we're only talkingabout one individual value with three referencesto that value if we got rid of these foreignkeys, and we just had to say what membershipstatus they had. Well, now, this guy's goingto be gold. This guy's going to be gold, thisgal is going to be gold. So now I have 123times that gets rid of the uniqueness fromthe key. So it protects our uniqueness improvesour speed. So basically, improved functionalityof our database. Function I'm running outof room and it does a whole bunch of otherthings. Basically, it makes updating easier,so less work for us. Because we don't haveto go through manually and check every value,we can just update that individual value.You know. The other thing is that allows foradded complexity. Long, hands getting tired.Okay. Now you can even see all that. Okay,so here's some examples. I'll see, I'll showyou how we could say it allows for added complexity.Let's see. I'm going to redraw the examplewe had, but I'm not going to draw super indepth. So don't, don't wait. Don't mean, don'tworry, 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. Whatwe had earlier it was we had an ID, we had1234, and so forth. And then we had the titleof whatever they are. So if you wanted todraw this better, you could put the columnheaders, so we could have, like ID. And thenwe could say, name of the membership. Andthen we could title the table, we could haveit membership, membership. Yes. Well, whatwe can do, we can have added complexity, becausenow 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. Andlet's not even have a membership table. Well,now we have the guy with the ID, 789, andthree, and we'll have their names be Caleb.Tom Tommietrace.So those are the three people I was talkingabout with the gold memberships. Well, whatwhat membership to the heck, okay, gold, gold,and gold. Now, how much does that cost them6060 and 60. Just with this little exampleof three people, we already have four unnecessaryvalues. Because we have repeating data, wehave G and G twice more, we have 6060, twicemore that eliminates the functionality ofour database, because we're having tons ofrepeating data. So you might think the bestway 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 becauseit allows us to store more information. Whenwe use the common or when we I'm sorry, whenwe use the lookup table. We can just havethat as another column. And we can put morethings we could have another column saying,how long does this membership last? We haveanother column saying? I don't have a specialpurpose, or what can use within our gym? Orwhat can we use? And are we allowed to bringfriends? What's the price for friends, wecan do all that kind of stuff within thistable and just reference that within the othertable. It's only a set number of options.So we reduce the repeating data. If we wantedto do at all Well, we'd have to put each individualcomp column and we'd get tons of repeatingdata. You know what I mean? Yeah, so thatis an example of how because that would takeforever. So that's very common. It's commonfor things with a set number of options. Solike membership type, there's only so manyoptions. You can have states, sometimes sometimespeople do it for sex, male or female. Somepeople do it for I can't think of anythingelse right off the top of my head. But anytimethere's a set number of options, they willoften put a lookup table with the ID, thename of that option or what that option islike gold, silver, bronze, or male femaleor California, all the other states, Wyoming,TexasRussia,well, thenwe only have a set number of options. So that'sthat's the, then we can put more informationabout it, we could say, male, and then, likewhat the male is allowed to do on the website,or anything that allows for extra complexityfor our database. In the last couple of videos,we've talked about all of the characteristicsof keys in general, we talked about the wordkey, and databasing. Keys are the key to success.Anyway, anyways, this video, we're going tobe talking about specific kind of keys, becauseall of the last videos were kind of concepts.But now we're going to be talking, how dowe actually implement that into a database,I understand that we want keys and we wantreferences from table to table, and we wantit all to be connected. But how do I do that?Well, the main two types of keys that you'regoing to hear are known asprimaryand foreign. Actually, we're not going tobe talking about these two kinds of keys.In this video surprise, we're going to begoing a little bit broad,more a little bit more broad.So when you're like a beginner and you'relearning database design, you'll probablylearn that these are the two kinds of keys,when, when you're want to learn more advanceddatabase design, you'll learn about differentcategories of keys. So before we get intothese two,whichif you guys haven't heard of those just holdup, if you ever have those keep holding up.We're going to be learning about what's knownas a super key. Now,for some reason,I think this is one word with no space. AndI don't know why there's no space there. Soif someone knows if like, what do you guyswant to know,let me know.Because I really want to know, it's drivingme crazy. The other one that we're going tobe learning is candidate key. Alright, sobefore, before we learn about the candidatekey, let's just focus on the super key. SoI'm just going to erase this. Alright, sowhat is a super key? Well, a super key isany number of columns, that forces every rowto be unique. Now, if for some reason yourtable is structured to where you can't haveevery single row be unique, you need to consideradding some kind of column to enforce uniqueness,such as an ID, or some, you need to make surethat everyone using the database understandshow that table works, if it's a special case,but generally every table, you will be ableto make unique. Now, when you work with databases,you'll often see IDs, so like user ID, thisis a type of key, then you also sometimesfind keys such as like username. The differencebetween these is that this one's kind of justmade up for the sake of the database, userID of 748. What does that mean? Absolutelynothing. It's just a number. username actuallyhas some real world connection. We'll be gettinginto the difference between these in an upcomingvideo. And which one will be best to use orwhich situations. But as for now, we'll justbe using kind of natural practical columnsthat are already in the database rather thanIDs. Butthe same thing can applywith just like an ID field or a call. So let'slearn about the super key. Super key, likeSuperman, but it's a key, just like a giantkey that just goes and breaks through doorsdoesn't even use the handle. It just breaksthrough doors.Anyways,let's learn. Okay, let's say we have a tablehere.Big table right.Now I'm going to put some columns in thistable. 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 alwaysthink that's the absolute easiest thing touse. And we're going to have ausername,email,password.Then we're going to have first name, middlename, last name and then we'll havebirthdayso every single row Within this table is goingto need a valuefor each one of these columns. I know thisis really bad handwriting, but it doesn'treally matter how it looks just understandthe concept. There now looks a lot betternow.You're welcome.Now you can actually understand what's goingon. Okay? some instances, for databases, youwill allow people to put in blank values.So for example, if someone doesn't have anemail, for example, we could make it optional.That way, some people have no or no valueat all other people have values. But for thisthis case, I'm going to say everything isrequired. Because let's say you use your emailto sign in, or to verify your account.How do we make sure that every single rowis talking about one entity, and every singleentity only has one row, everything is unique?If we have a personand draw a little guy over here, right? Andhe wants to sign up for this website. So youguys this website, here's hiswebsite, you put this information in, andthe information goes into a table. Right?he does, he probably won't actually have accessto the SQL, because it's just some randomuser 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 enteredinto this and all of his values will be putinto 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, realname Daniel. Last name, curry birthday, oh,to 1390. So this guy, inserted all that informationwithin this database. If you can't read, itdoesn't really matter. Now, when another guycomes, or let's say gal, so this lady comes,and she's like, Oh, I want to sign up forthis website. So she signs up this website.And she puts her information in. And thisalso goes in the database as a new row. Sothis is just the column headers. This is separatefrom the actual data. The rows, though, theyhave individual values, she can put her valuesin, I'm not going to write them all out, becauseyou guys get the point. How do we know thateach individual row is unique? And that talksabout one individual entity, in this case,a user? Typically, whatever the entity is,is what the table was titled. So the entitywill be the user. How do we know that everysingle row is talking about only one user,and every user only has one row the rule ofone. We don't want Caleb to be able to comein here, create a new account and make hisusername, cc and all the same stuff again.Now, in some instances, we may be able toallow them to make more than one account.Like I could make an extra YouTube accountif I really wanted to. That would be okay.So I could have the same different username,different email, same password, same firstname, same middle name, same last name, thesame birthday, the only thing that changedis a username and email. Now, the default,like what the table is trying to define haskind of changed. We're no longer trying todefine the person. We're trying to definethe useraccount.Because now there's two accounts and they'recompletely separate entities over that kindof makes sense. But you don't really needto worry about all the specifics. I'm gettingcompletely off topic. So what were we talkingabout 10 minutes ago, I think we're talkingabout super keys. It's any number of columnsthat forces every single row to be unique.If you take all this information and compareit with another let's let's create a new row.Let's say this lady here actually ended upputting your information in her username isa her emails B passwords seat. First Nameis Hi, middle name is yo. And her last nameis Hello. And she was born. Oh 316 72. Thatis her birthday. Well, now, if you comparethese values it is in is there they're different.Not every single individual value has to bedifferent. For example, this girl's last namecould be curry and that'd be perfectly okay.But all of the values as a whole have to bedifferent. So as for example, if someone camein here and put all of this information uphere, down here, cc ccof, blah, blah, blah,pi, Caleb Daniel Curie Oh to 1390, that isnot a unique row, and it would cause an error.So the super key could be all of these columnsright here. Now, super keys are usually notdefined within the database. That's becausethey're so broad, you have all this information.Of course, it's going to be unique, simplybecause you can only use a username one time,typically the emails only one time. So ifsomeone comes down here and tries to use anMCC, well, then you already got an error,it's not going to work. So all this extracolumns are here. Kind of unnecessary, becausewe can simply do that same thing with justthe username. So a super key is any numberof columns, that creates a unique row, uniquerows. A candidate key is the least numberof columns. That Can you see how new is acandidate? I'll explain why it's called acandidate. And second, or in the next videoor something. The candidate key is the leastnumber of columns. So for this example, we'renot going to allow two people to have thesame username. We may allow them to have thesame email, we may allow them that same passwordfirst name, middle name, last name, and birthday,but not the same username, because then howdo you tell them apart? You can't becauseall of their information is the same. They'reexact clones to the database. And we do notever want that within our database. Typically,I can't really think of any examples whereyou would want duplicate data. But okay, anyways,the candidate key is the least number of columnsneeded to force every row to be unique. Inthis example, the username is enough to makeit unique. So we can take the candidate keyand have it to be just the username, or Iguess we could certainly call because allthroughout the, the rows are going to be unique.So the can the candidate is least, and superkey is anyx.Now when it comes to database programming,let me clear this up. So your brain doesn'texplode anymore.It takes forever.I want to tell you guys,stop watch. Alright. And I just got my chalkboardto listen to me anderase that stuff.When we're talking about database programming,there's not really a time where we're evergoing to tell your database that this is asuper key, because super keys are not practical.They are for designing your database. Only.When I looked at that column, first thingI do is I think super key. Yes, every singlerow can be unique. That's the first check.So you ask,can itcan each row the unique?That's the question.That's the Define. That's what super key isquestioning, can each row be unique? And evenbetter word? Can every row be unique, becauseevery single row within a table has to beunique. 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 candidatekey. So now we askhow manycolumnsare needed?Can you read that? Hopefully? So the firstquestion, can every row being be unique? Yes,that means we have super key. That's all wehave to worry about. We have to have to figureout which columns or anything like that, canevery row be unique? Always think about allthe possible values. Is it possible for aduplicate? If it is you got to figure somethingout? If it's not good, so every row can beunique.Yes, check move on,how many columns are needed. Now you figureout the least number of columns needed inorder to create unlimited uniqueness foreverso no possibility of uniqueness and we areallowed to put some kind of like limitations.We can put a unique we can use. It might bedifferent from for different relational databases.But if we use a unique index, for example,every single column, I'm sorry, every singlevalue within a column has to be unique. Andthat's, that's a requirement for people toput in. So if it's not unique, we give itan error. That's how we force values to beunique. So how many columns are needed? Oncethey figure that out, we have what's knownas a candidate key. So in the last, the lastexample, it was just one because we coulduse the username. Now, the next thing I liketo figure out is, how many candidate keysdo I have?I'm just going to useck for candidate keys. How many ck do I have?What does this mean? Well, we can actuallyhave more than one candidate key. And thelast example, we had a username, we also hadan email. Okay, well, email could also workas a candidate key, because you could puta requirement that every account has a uniqueemail, some websites are cool with peopleusing the same email more than once. Somewebsites aren't usually they're not. So theremight be a chance where we have more thanone candidate key. Or if rather than usingthe username, you want to use something else,you could have it to where we can have, let'ssay, you can use your email one once, we couldhave first name, plus, last name, plus middlename, or middle initial. So that eliminatesmost possibilities of uniqueness. But there'sstill possibility that people have the samename. So then you add,birthday.So now you have to find someone with the samefirst name, last name, middle name, and thebirthday.Very rare,still possible. So then we could add a third.I guess one to a fifth column, we could makeit. email, or we could do something such asa dress. Alright, let's see, let's go witha dress. This could be a candidate key. Becausewe have first name, last name, middle name,birthday,and address, they all have to be unique asa whole. What are the what are the chancesof a person living at a certain location bornon a certain day? Also, the UK what are thechances of two people having the same namesame birthday and living at the same place?Unless you had twins, and you named them thesame thing? If that's the case, shame on you.It's gonna confuse people. That could be acandidate key now isn't the best candidatekey?Probably not becausewe're using five columns. And it's still technicallypossible to have a duplicate, but very, very,very rare. Right, so how many candidate keysdo I have? You figure them out, I like towrite out the possible candidate keys. Andthen we move on to the next step, which isdefining the key that we will use for ourtable. Once we have the candidate keys, wecan choose one of them. They're called candidate.Because there'sthere's an options like you could have, youcould have your first candidate, you couldhave candidate key one, candidate key two,and you've got candidate key three, this couldbe the username, this could be the email.This could be that long, first name, lastname, password, blah, blah,whatever you want it to be.Now, you have to decide which one will beyour primary key. We're going to be pickingup from where we stopped last video, whichwe were talking about candidate keys. So thisvideo, we're going to be talking about primarykeys, which is the next step. So what is aprimary key? Well, in the last video, we wedefine some possibilities for candidate keys,something we could use as the main key it'sa candidate because you have to pick it tobe our primary key.So we have a list of possibilities. One ofthem was a username. Another was an emailand then if you don't have something simplelike this, you could do something like yourfull name, which that would be like firstname, last name, middle name, and then plusan address. Which address would probably bebroken up into like, Street, city. You know.Like all the parts have an address, and thenpossibly your birthday.Or justdog.So here are just some possibilities for candidatekeys, we could have username, email, firstname and last name. Well, now, once you haveall your possible candidate keys, you wantto pick one to be your primary key. So howdo you do that? Well, once again,you got to make sure we lookat the therules or suggestions for keys. This is specificallytalking aboutprimary keys.We want our primary key a unique, never changingand never know. So username is never goingto change, right. So that's pretty good. Youalways need a username when you sign up foran account. So it's never know. And it hasto be unique because two people can't havethe same username. So this would be a goodprimary key, check out email. Well, it's youcan make it unique, you can require peopleto only have like, you can prevent multipleemails. So when you create an account, youcan only use an email that has not been used,that's an option. So that's possible, neverchanging it Well, it depends depends on ifit if your use your email, like a user accountusername, that you sign in with your emailand you have a password to well, then thechances are, you're not going to be able tochange it. Although now, you can probablyupdate your email and stuff. So it's possibleto be never changing if you prevent peoplefrom updating their main email. So possible,possible and never know you can require someoneto have an email. So that's possible, allthree of these are possible. So this is apossible loss for a dash, first name, lastname, middle name, address, date of birth,well, probably the whole group, as a wholewill be unique, the chances of finding someonewith your same name, living at the same placeas you with the same date of birth is almostimpossible. so unique, practically Yes, butpossible, you can possibly have a duplicate.So possible, never changing. Oh, well, peoplecan get married, so their last name can change.First Name can even change your middle namecan even change, you can move, you can't beborn a different date, you can't be born again,like unless you become a Christian. But anyways,never know, well, some people might not havean address. Either that, or they might nothave 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 theuser name. And then you define that as yourprimary key. And then you can use that tomake connections between tables. So you havea comment by a username, that's the connection.That is a natural key. And we'll be talkingabout surrogate keys in the next video, ifyou know anything about that. But basically,you can use your username as an Id ratherlike as a primary key. So you could have,let's say, this is my username. This can bethe connection for all of my rows by me. Soif I have a table for comments, table forusers, a table for sales, will if I buy something,Caleb curry connection. If I post, if I'ma user will connection, if I post a comment,connection, it all kind of points back tothe user table, because that's going to bethe parent of therelationship here.So this could be a primary key. So that'sa primary key. Now, this will also be indexed,because primary key is a source of is a typeof index. So you can do SELECT statementsreally easily with that and everything likethat. So that's going to be how you connectmost of your data. Now, the other candidatekeys that we did not choose, they're knownas alternate keys. An alternate key. It works.I mean, it can be it can be it could be theprimary key. But basically, it's all the candidatekeys that were not selected as the primarykey. You do not you're not required to defineall of your alternate keys in your database.In fact, you don't even need any alternatekeys. But you may want to because oftentimesthey will be something you're doing searchesfor, or connecting tables or whatever it is.Alternate keys might be useful so you cancreate an index on the alternate key. So Icould create an index on the emailFor 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 mightwant to do data by the emails like selectingeveryone by their email, you know what I mean?Maybe possible, basically, you can, you cando a select. And then you can choose the columnsyou want to choose and then the table. Andthen you can say, where. And then you couldsay, like, email, has the value, legit atawesome calm, because you have an index onthat it's going to work better. So if youhave a really good alternate key, I recommendyou index it simply for good design. And itwill help your database run faster. But ifyou don't really want to index on it, thendon't do it. Because that's just another thingyour database is going to have to maintain,it's going to have to update the index aswell as book sort of how if I have a bookhere, and I change the insides of the book,well, then the index in the back is gonnachange too.So alternate keys, you can set them as anindex, if they are good and useful if youwill be searching for that kind of information.So that is introduction to primary keys andalternate keys.Sir, Ithink I'm falling sorry, by the way, sir,good. And then we have a natural.Alright.So we talked about primary keys in the lastvideo. That's as far as we'll get for defininga key. These are categories of primary keys.So we define a primary key. And this is kindof like a descriptive term, we don't haveto define in our database surrogate primarykey or natural primary key, this is more fordatabase design. To know like the differencebetween types of keys, hopefully that makesclear sense. And if not, just stick with me.Okay, so Up, up to like all these key videosI've been doing, I've been using natural keys,let's define a natural key first, it's verynatural definition. It's something that'snaturally in the table, something you naturallywant to store. When you have a user's table,you naturally want to store the user nameand the email, those can be used as naturalkeys, because they fit all the requirementsfor a primary key. And it's already in there,you don't have to make up something, you don'thave to think of a column to add just forthe sake of having a key, we want to be ableto have a key for every single table.Butsometimes, the natural keys not very obviousor not so natural. So we want to try finda natural key when we're using natural keys.Generally, when you create a database, solet's say this huge box is our database. Withinthis database, we have a bunch of tables.And then within each table, we have a bunchof columns and rows. See, I'm saying theseare all connected in some funky way, withrelationships? Well, when we define a database,typically, I mean, maybe not always, we wantto try to either always use natural keys,or always use surrogate keys, which are justmade up keys. We don't want to kind of flipflop switching, I can't think of any otherwords. Because we want to try keep it consistent.Otherwise, people using the database, they'regonna be confused. Is this a natural key?Or is this a surrogate key? Is this somethingthat actually has real world value? Or isit just a made up number? So let's say weare using natural keys, there are a coupleproblems with natural keys. And this videowill be explaining the difference betweenthese two. And the next video, we'll be talkingabout pros and cons of eachbasically.So natural keys are what's naturally alreadyin the table, something we wanted to storeto begin with, right? The thing is they havereal world value. So your database might adapt,and the meaning of your keys kind of adaptto which, once again, I'll explain that onthe next video surrogate keys. They are acolumn that's just added to your database,no matter what even if you have a good naturalkey because remember, you want to either useall natural or all surrogate usually. So ifI decide to use surrogate keys, I'm goingto give an ID to every single row within everysingle table. So we have let's say we havea user table and then we have a saletable.Then we have acommon table, all we do is we give every singletable, an ID column. So we have a user IDcolumn and the say in the user table, we havea sale ID column and the sale table, we havea comment ID in the comment table. So nowwhen we have like, let's look at the commenttable in the comments, we're going to havea column, we're gonna have a column for thecomments ID. And then we'll need a referenceto the user that posted the comments. So we'llhave the user ID as a foreign key, which pointsback to the user table. So that's kind ofhow that works. Now we just have random numbers,the ID is just a big number that has a featureknown as auto increment. means every singletime you make a new row within this table,it's going to raise the ID by one, then whenyou like, delete columns or something andyou have gaps within your numbers, it doesn'treally matter because the number has no realworld significance. Typically, surrogate keysare kept completely private. No one knowsthe surrogate key, except for the people workingwith the database, you're not told your surrogatekey. 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, registerand do my email junk, and then I sign in andgo to my user settings, it's not going tobe like user ID equals 45, or whatever, it'sgoing 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 everybodywho has one of these IDs, or, or somethinglike that, or if I started putting it on salesreports, well, then I'm giving that numberreal meaning in the world, and it's startingto become a natural key. So hopefully, thatkind of makes sense. Natural is somethingthat's already in the database. surrogateis something we just add. The thing that'skind of cool about surrogate is if you'restruggling to find a really good natural key,you can always use a surrogate key. The problemwith that is you always want to try to beable to naturally make everything unique.Like any user table, we have a username, that'sgoing to keep things unique. So even if weuse a surrogate key, we can still index thisusername. And that can be used. Sorta as ifwe assign it as a natural key. The only thingis we're making connections between tablesusing the surrogate key. So for this table,we could have a user ID, and then we couldhave a user name. For just an example. Theuser ID would be the surrogate key. This wouldnot be the natural key because we can onlyhave one primary key. And the primary keyis going to be the type surrogate. So theuser ID is a surrogate key random number generatornumber, the username, we can index it anduse it like a natural key with when we searchthe database and everything. But we're notgoing to use it to make connections betweentables and references with foreign keys. Sohopefully that makes thingspretty clear.In this video, I wanted to talk about allthe pros and cons of surrogate or naturalkeys. So basically, we're going to compareand contrast them here. So over here on theleft side, we got surrogate and then overhere on the right side, we got natural Allright, we're gonna have a little battle hereand we're going to figure out which one'sthe best. Me as boxes to keep this up.Here. Alright, sowhat are some good things about natural keys?Alright, well, first one of the first goodthings about what's really weak. One of thefirst good things about natural keys is thatyou don't have to define any new data. Ifyou don't remember from the last video, letme just redefine them here. Natural keys areused, or columns used as your key that arealready defined within your table. They'renatural to what you want to store. surrogatekeys are just kind of added like a user IDor a car ID or a credit card ID or a commentID, their surrogate. They have no real worldmeaning so natural, not natural. Cool. Sosome good things on naturals, you don't actuallyhave to define a new data, any new data, Idon't know if you could hear me, they're notkind of stuttered on that. But anyways, youdon't really have to define any new data becauseit's already within your table. And you canuse that as your natural key. So you're requiredto store less information in your database.So you get smaller data, you get it, you havea smaller database. Right? So that's one goodthing. So what's the downside to natural keys?Well, the downside is that sometimes you'renot going to be able to find a really goodnatural key. Because if you look over here,we want our keys to be unique, never changingand never empty. Or No. Well, if we have anatural key, we have to find one that fitsall three of these. And it has to be a goodone that would make sense to use as a as anatural key, like, you might not want to usea combination of 20 columns to make naturalkey. But that's one of the downsides to naturalkeys, you have to figure out what naturalkey to use, and there's not always a greatoption. Now, optimally, you should have anatural key, or at least something that canbe used as a natural key within every table,to best had to have the best design to ensureuniqueness, not just by a surrogate key ifyou use one, but by naturally what's beingstored. So like this example, if we have acomments table, we could have a comment bya 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 dateit's posted, or the time it's posted, that'sgoing to be unique, because you can't havetwo comments from the same user at the sametime, on the same web page. It's not possible,right? I mean, unless you like glitch thesystem somehow, which shouldn't happen. Well,that's an example of using natural keys. Thedownside? Like I said, you might not alwaysbe able to figure out what to use for yournatural key. That one was a good example.Because naturally, we could figure out a naturalkey, right? Follow me. The other thing is,natural keys have real world values real worldmeaning, right. So you have connections toreal world, meaning. So for example, in thecomments, the actual comment in combinationwith the user who posted it in combinationwith the time it was posted, and the pageit was posted on, or any of those four columnsto be used as a natural key, well, those havereal world meaning. And as your database developsover time, the database application mightchange or be updated, or new meaning is givento the database and what it's supposed todo. That means that your natural keys couldchange over time and have new, new meaning.Or the actual values could change in someinstances. And we do not want that, obviously,because it has to be unique, never changingand never know. If for example, we make itto where we do have to change the naturalkeys. Well, that would kind of be like crossingthis one out. And that's possible, like wecould update the values of all the keys. Butthat's going to require us to update all connectionsbetween tables.And that's going to require a lot of resourcesfrom our server. And that's just a bad designin general. So those are most of the consof natural keys. So yeah, let's move on tosurrogate right. So surrogate keys, well,obviously, the first downside is you haveto add a column to your table, no matter whateven if you have a a, for example, if it'snaturally unique. Well, you might like ifyou have a comment from a username, well,that username is gonna help ensure uniqueness.And you still add a surrogate key when you'readding unnecessary data, because you're addinga new column and you have to store all thesenumbers. One of the pros of surrogate keysor is that they're typically numbers wereoftentimes natural keys can be words or anythinglike that. That's good, because typically,numbers are easy to work with, but not always.Now, a couple other things. Besides the factthat you have to add a new data new column,which requires you to store more data. It'sthat it can be confusing sometimes when allyou're working with is just combinations ofnumbers. And I have like, we have a columnlet's say like komentar for a comments table,and we just have an eye we have the valuelike 700 407,462. Well, this kind of clear,it's talking about the user with the ID of747,462. Sometimes columns won't be very descriptivethough. And that would be full into designers.So make sure you name your columns, right.But when working with other people, you cansometimes get confused exactly what ReferencesWhat, and you have to do a little bit morethinking and check the actual database structurethe way it's programmed, or however else likethat. 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 ifyou prefer to use natural or surrogate keys.But typically, you'll want to pick one anduse that throughout all of the database. Asa database really weird. There's like database,data base rather than database. So it's likedatabase anyways. So like, set that asideto use surrogate keys, well, that means everycolumn should get a ID, or at least a combinationof IDs. Natural if I decide to use naturalones, every column should try to be used asa 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, andwe're not going to know what's going on. Sotypically, you want to kind of keep one orthe other. The other thing is, I don't knowwhat the other thing was. But yeah, typicallyone or the other. Now, for my sake, I personallyam going to use surrogate keys, although that'snot saying that they're better in any way.I just prefer surrogate keys, because it'ssimple for me, because every time I make atable, I all I got to do is make an ID withit. Like if it's a user table, all I got todo is make a column user ID, and boom, we'redone. That's all I got to worry about. AndI don't really have to worry about ever changingor ever becoming something different or anything,it's just a random numbers 7254. Well, thathas no real world meaning. Now keep in mind,though, that if you do use surrogate keys,you shouldn't, you shouldn'thave themhave any real world value. So for example,a student ID if we give these student IDsout to the students, that's not really a goodidea to use as the surrogate key, becauseit's actually an actual key, it has real worldmeaning. So those are just some things andyou can find tons and tons of stuff on theinternet about natural or surrogate keys.It's basically like a war between two databasesides. Were the natural keys. Oh, werethe surrogate keys.You know what I mean? So yeah,don't freak out. Just figure out what worksbest for you. There's some minor performancedifferences. But I mean, I don't really knowall of them. So yeah, believe it or not, Idon'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 whichone will work best for your application, oryour database, because that's really whatmatters. So this is a big subject. And there'sa lot of important things. So I'm going tobe separating a lot of these concepts up intoseparate videos. So the next two or threevideos, this video and two others I thinkwill be over foreign keys, and a lot of thingsthat have to do with foreign keys. Sorry,anyways, this video we'll be talking aboutintroduction to foreign keys, and exactlywhat exactly they do. Alright, so foreignkey 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 foreignkey. Now, this can be a primary key in thesame table or a primary key in a separatetable. So let's kind of see how this wouldplay out. If we do do some drawing, right?Let's, let's think of a class for a collegedatabase. So this database has call has allthe information for a specific college ormultiple colleges. And one table is for classes.We also have a table for instructors and forusers and anything else that's obvious. That'san obvious entity. So let's, let's look atthe class table. So we have a class tablewith 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 keysfor most of this. You can do the same thingwith natural keys. And if you have no ideawhat I'm talking about, just don't pretendI never say anything.So yeah,we have a class ID. That's an example of asurrogate primary key We can also have a aninstructor. You see, so we can have an instructorID,right.And we could have, let's say a room number,because we could have, we could have a tablefor every single room, which talks about moreinformation about the room, or I don't knowif that's a really good example. Or if theywould say we have a bigger building, or abigger college, and we have a table for eachbuilding. So that's probably that's probablya better example. So we can say building IDto say which, which building is in. Alright,so here, here are three IDs. So you can assumethat they're all keys because almost all IDsare going to be keys of some sort. Which oneis the primary key? Well, this one right here,the class ID, this is the primary key becauseit's the class table, every single row withinthis table is going to have a class ID, whetherit be 1234 712-800-4364, whatever it is, it'sgoing to have a class ID every single row.So every single class is going to have itsown ID. We also have an instructor ID andwe also have a building ID. Well, since theseare kind of unrelated to the class, you canassume that they're foreign keys in this example,because we're not going to have an instructorID for a class necessarily by itself. Thisis 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 thinkI'm spelling instructor right. And we havean instructor in here. Instructor ID.Well, this you can tellthese are a reference here, we can also havea building ID. So this references anothertable. And that would be the building.And then we have a building ID.So I'm taking forever to write and I can'teven write nice. But that says building IDright there. And this references that there.So you can see there's a connection betweenthese tables. Now we'll get more into drawingrelationships better like rather than justusing wines, we'll talk about more of thatin the future. But for now, we can see thatthese reference these columns, so the columnspoint to each other. And also each individualrow is going to point to each other. So letme explain that a little bit more. When wehave a row within our class table, let's drawthis table a little bit better. So I can kindof see how it would work. So let's say thisis an actual example of the values. Withinthis table, we have a class ID. And instructorI'm just going to put I and then the buildingId justfor simplicity, heck, I don't know. BecauseI don't feel like writing all that out. Sowe can have a class ID.And just just to make things more, just tobe more accurate to what a normal databasewould be, we could say like the name the nameof the class like biology. So we can havea 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 thenthe name could be biology. Well, every singlecolumn is going to have column rules or requirements.For example, these two columns here.Individually, this one isgoing to have a rule that says every singlevalue within this column. So as we go to thenext row, the next value, every single oneis going to have to reference something fromthe instructor table because it's an instructorID. So that's one of the column rules. Nowsame for the building ID, we could say thatevery single row has to reference a valuewithin the building ID. All right, so thatmeans the row right here, let's say this rowindividually, so right here, the class IDwith seven biology references, the instructorwith the ID of 63 and the building with theID of 16. So you can see that the column hasa rule set to say that every single valuewithin this column, every single row is goingto have to reference an instructor and a buildingnow Each individual row, we could say, Oh,this specific row references, the buildingand 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 outa different way.Let's take a look at the, the building table.Okay, so let's justdraw little squares to represent it. And let'ssay within this table, we're not going tostructure the columns or anything, we're justgoing to say we have the building, 716, and14. So these are three separate buildings.And we could have names for the buildingsand all kinds of other things. Now, we havewithin the class, let's say this is the classtable, right? And we have the class ID. Sothe class ID would be like 6438 123, it doesn'treally matter what number they are. And eight,right. And now we're going to have a referenceto the building ID. So let's say we have 77714, right. So here's an example. So over here,we got the class ID.I got thebuilding ID.And then we got the building ID. And thenwe got more information about the building.Here, if we wanted, well, you can see thatthere's a connection between every singlevalue, every single row within this classtable has a connection with a building ID.Sothat the entire column building ID has therules that every single value within thatcolumn has to point to a value within thebuilding ID of the building table. Now, eachindividual row has a value that points toa specific row, within the building ID. Asyou can see, these are all individual rows.So let's get a little crowded here. We cansee that three of these classes in this classtable, reference one building, and that'sthe building with the ID seven, and that canbe the room legit building 74, or whateveryou want to call your building, it doesn'treally matter. So that's, that's an exampleof a relationship. Using foreign keys. foreignkeys are what keeps things connected.So let's say the name of building seven waslegit, because I really don't know what elseto name it. Well, now we have the value legitas its name. And we connect with the ID. Sorather than putting legit, legit legit, andhaving repeating data that could be easilymessed up, for example, if legit, was changedto legit with two T's or changes change thename completely? Well, now with this foreignkey, it's connected, so updates automatically.Soyeah, that's kind of how foreign keys work.Just keep in mind that every table has oneprimary key. Now that primary can primarykey can be a combination of multiple columnsif you want, but it only is defined as oneprimary key. For example, we can have thecombination of first name plus last name,this group could be the primary key. But we'renot going to have the first name primary key.And we're not going to have also the lastname primary key,because that'sto my two primary keys. And we can do thatwe can have the combination of the two. Butwith foreign keys, you can have multiple columnshaving foreign key relations to differenttables. But keep in mind, each column canonly 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 theteacher ID is seven, six and five.Well,this can be a reference and we could alsohave another foreign key we could have thebuilding ID which can be 612, and eight. Soalthough we only have one primary key in atable, we can have multiple foreign keys,referencing different tables, if we wantedit to be where the the, the instructor, Ithink it's supposed to be an AI. So the instructorto where the class can have multiple instructors.Well, that's a many to many relationship andwe're going to have to design our tables withan intermediary table. So hopefully that'skind of making sense, I just want you to realizethat the primary keys are what sort each individualtables, the foreign keys are what Connecttables, and that you protect that integritywith foreign key constraints, which we'llbe talking about. In the upcoming next fewvideos. In this video, I wanted to talk alittle bit more about foreign keys, and acharacteristic known as not null. Alright,so this this keyword here might vary fromrelational database to relational database,but it's probably something similar to thisnot know, which we learned about No, and wetalked about this over here, it's basicallysaying you, the database will not accept someonenot answering that column, for example. Soif it's not know, you have to give that calland the value every single row. So the rowsare each individual entry within the table.If you have the column characteristic of notknow, for every row, it's going to be requiredthat it has a value. So think of like a faxnumber. 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, thenit 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 foreignkeys. Because it has to do with what's knownas cardinality, which we'll talk about thatwhen we start designing databases a littlemore. But it's basically whether or not arelationship is required. Sothis is going to dictate what kind of valueswe can put within our table back to the faxmachine, if we have it to where it'snot null.That means every single value, I mean, everysingle row has to have a value. That alsomeans that if someone doesn't have a value,they might not be able to put any informationin this table. So by putting this restriction,you're eliminating all rows that aren't goingto have a value, you're preventing them fromhappening. You see what I mean? Yeah, so ifthe cases where you do want people who don'thave fax machine numbers to be able to enterdata into your table, well, then you wouldnot want to use not no as a characteristic,because that's going to prevent them fromdoing that. Now, this is important when itcomes to foreign keys. Because if we requirea relationship, we said as not No. And thatmeans every foreign key value for that column,every single row is going to have to havea relationship for that. So one of the biggestdifferences between primary keys and foreignkeys is that primary keys are required allthe time, never changing. Never know. Right?Drop munchak, dang. Well, foreign keys area little different. Because we can have themto where they're not required, they can beempty, because sometimes that relationshipis not there. Or they could update like wecould, we could have a new relationship inthis table, which we want. We don't we wantto eliminate as much possible updates as possible.But sometime that's required. And that's whywe use foreign keys to keep things connected.So when things are updated, they update acrossthe platform.Now,we don't want the primary key values to change.But we could have the foreign key referenceschange. For example, let's say we have a tableover here. And we'll just have it will bewill have to be carbs, okay? Or whoops, havethe car,right. And we havetwo rows and have a car with the ID of sixand a car with the idea with the ID of 10.Right. And we have this guy over here. Andwe have a table for him. What is making theuser table and let's say for some reason,we wanted to make a connection of what carhe owned. Well, he might currently own six,right? And we don't ever want the value ofsix to change and still be talking about thesame car. Let's say this car is this one overhere. This is some flames. Right and thisone over here. We got a legit car. All right.So these are two separate cars. 10 talks aboutthis car six talks about this car. We don'tever want it to happen to where this numbergets up. Unit eight, or we change the primarykey, and it's still referring to the sameexact car, because then we have a changingprimary key. But with foreign keys, it canchange in the sense that, oh, I no longerown this car. Now, I own this car. So we didn'thave any primary keys updated. But the foreignkeys, which would be this connection, righthere, did update. Alright. So that kind ofis a little bit of a difference between primarykeys and foreign keys, primary key value shouldnever change, foreign key values can change,because references change. Hopefully, I didn'tjust lose my train of thought, I think I thinkI'm going but kind of hit a blank here fora sec. Okay, so let me just illustrate thisin a college right. One of the important thingsbetween these tables that have a primary keyand a foreign key connection to that primarykey is that they have to be the same datatype, which is the type of data whether itbe int, or character or date. And basically,it has to have all of the same characteristicslike the collation or the character set orthe storage engine. Now, the only thing thatcan vary is the nominal characteristic. Becauseif 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 primarykey, I'll just put an N for sure. The foreignkey on the other hand, it's not always requiredto have a connection. For example, in thatprevious example, we had the person the usertable and had this guy over here. And thenwe have this car table of the cars that theuser owned, right? He could have one car,right? Well, he might not currently own acar, therefore, we don't have a connectionto this table. Therefore, this connectionis optional.Or not required. Yeah, that doesn't even looklike English, but close enough.So let's think of a another example. If wehave a college, well, we might be in earlyin the semester where every single class beforepeople have registered, every single classdoesn't have an instructor yet, and we'restill trying to find an instructor. Althoughwe 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, andwe have a class table. A class table is goingto have a foreign key pointing to the instructorfor the instructor of that class. Unless thecase of a many to many where multiple instructorscan teach multiple classes, then we wouldneed an intermediary table. But that is notthe case here. You need to learn more aboutthat check my relationships video. But fornow, we would have a class IDand an instructor ID. Right. And then overhere, we have an instructor atthis it says ins instructor close enough right?Now this is going to be a reference to thisover here. Now what kind of values is thisgoing to accept? Let's give some examples.Let's say we have here's a row, we have theclass ID seven, the instructor ID at three,and we'll add a call and we'll make the nameof the class. We have a call minutes math.Right? And then we can have another valuewithin here. I think I'm off screen now. Thenwe can have an instructor over here. His name,his ID could be seven. And his name couldbeshake. Right?So this class ID is seven yummy changes inthe gonna get confusing. Let's make this 16.Alright, so this class ID seven. And it'sby an instructor eight, which we don't actuallyhave over here yet. So that means we eitherhave another row or we have an error whichbecause we never want that to happen. So let'ssay we have another class. Got any room. Okay,we have another class over here. And it'sit's numbers eight and it's taught by Calebright? So now we have an instance of a class.We got the class ID seven, the instructorID eight, and the name math. So the instructorID points to this Walker, this instructorover here. This is the parent I'm sorry, thisis the parent This is the child because thisis a foreign key reference to the parent.So that means This relationship is a foreignkey. Now, with the not normal thing, if wemade it to where it's not know, if we addedthe characteristic,notknow, or that means this row is going to haveto have a value for the instructor ID, thatmeans every single value for instructor IDis required. What does that get rid of, thatmeans it's impossible to put a class in thistable that does not have an instructor ID,that could cause a problem if you have a classcreated, but you don't have an instructoryet, or if the instructor drops out, and youhave to find a new one. So this one mightbe a bad time use not know, if we got ridof that. Well, now we could have a class witha primary key, but no foreign key connection.Now we have a class with the idea seven andit's met. And the the current teacher, wedon't have one. And we still have this guyover here. He's not teaching that class, buthe's still in the system. Jake's still inthe system, the class is still on the system.That is a good example. So for this one, notan old might not be the best idea. Other examples,having nano would be a good idea because itforces that relationship to be there. Youonly think of another quick example. Realquick, quick example real quick. If we hadtwo, two entities, we had a card for a creditcard company. So we had the card. And thenwe had the person holding the card. So theperson who bought the car or getting the cardfrom the bank, and he's using it to buy theirgroceries and their gas, and they're breakingup 1000s of dollars in debt. Well, that personwould have relationship with this card, becausehe's the card holder. That's the car. Now,if if we had a table for this, we probablyhave a card table, and we'd have a personID, which would reference the person table,right? If we set this to not know, that meansevery single value or every single row withinthis card table is going to require a cardowner. If we had if we got rid of this, thatmeans we could have cards in here that wereoutdated, that don't currently have an ownerbecause there's no connection, we broke thatowner, or new cards that haven't been givengiven out yet. So if you want this table tojust be for cards that currently have an owner,so we can have our own cards. Well, then wecould have a a nominal column with the samedata type as person ID. Now every single valueis going to have a person ID Does that makesense? So that's kind of the introductionof not knowing how that works with foreignkeys and how sometimes you will want themto be not known. Other times you want themto be nullable or not required. I got a bunchof writing on the board. But don't look atit yet. Because I just want to talk to youguys for a minute. I just wrote it. So youwon't have to watch me write like 50 100 wordsis that to be boring. So instead, I'm justgoing to talk instead. So much better, right?Anyways, last video throughout this series,we've talked about foreign keys. And as weknow, they keep connections between tablesin a database. So table one, table two, boom,foreign key, connect from this foreign keyreferences the primary key of this table overhere, that's kind of how foreign keys work,they protect the integrity of our database.They keep everything consistent. Think aboutif if there was no foreign key constraints,which keeps keeps it updated and everything,then we would have to manually update allconnections between tables. And that'd bea big waste of time. And it just be easierjust to store everything in one giant table,which both solutions. either manually doingit or storing everything in one giant tableis not going to work. So we need foreign keyconstraints to protect the integrity of ourdatabase, right?It's called referential, referential integrity,it's big words. Alright, so now this conceptsthese words might be specific to a certainrelational database management system. Sothese are from MySQL, but if you're usinga different relational database managementsystem, 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'tuse MySQL, because you need to understandhow foreign key constraints work. All right,cool. So first of all foreign key constraints,make sure that if you update the parent, thechildren are going to update or cause an erroror something like that, it's up to you, youcan make the decision. And it's also goingto burn prevent you from creating children,when there's not a parent that are that withthat primary key. For example, if we havefive users with the user's ID, 2468, and 10.And then I make a comments table, and I adda row that's like, from the user 745, well,that person doesn't even exist, so that'snot going to work. So that's why foreign keyconstraints are important, because they onceagain, protect our database, and make it prettymuch self managed, rather than us having tomanually update everything. So let's begin.Alright, let me get a piece of chalk. Thisone looks nice, getting pretty low. This isthe biggest piece of chalk I have left. Alright,so we when we create SQL statement, we canadd these two key keyword phrases here ondelete and on update. This refers to the foreignkey constraint. So when we, but it's talkingabout the parent, so when we update the parent,we want the kid we Superfly. When we updatethe parent, we want the children to do something.When we delete the parent, we want the childrento do something, that is what these keywordsmean. So these keywords, it's talking aboutthe foreign key constraint. And it refersto the parent.All right, so let's just draw some basic tablesjust to kind of keep things I'm not goingto give any specific examples yet, I'll justkind of draw things out. So let's say we havea parent here.And as you learned in previous videos, theparent is the primary key, right? And thenlet's reference this primary key with a child.Let's not draw this line yet. And then wehave a foreign key. This, this primary keyis a column within the parent. And every singlerow has a unique value, the foreign key isa column within the parent, and it referencesthe primary key. So the say the value 400in a row could reference the value 400 inthis row, and a row of this table, you getI've explained it enough. Now let's just makeanother child table. So these are both foreignkeys that reference the same primary key.But they're two separate tables. So yeah,we can have a user table and then we couldhave a, a comments table and an Orders table,for example. They both are bought or commentedby a user. So these foreign keys referenceprimary keys, so every single value withinthe table of the children within the foreignkey column needs to be a value that referencethat is present in the parent column. Now,these these values right here, these optionsrefer to the parent. So when we delete theparent, or when we update the parent, we wantthe same thing to happen to the child or whateverthe options are, and we'll talk about theoptions in a second. So that's kind of howthese work now just to kind of clear thingsup I'm going to erase these words here justbecause it's getting a little crowded youknow, I want it to be peaceful. Okay, so thisis kind of how it's working foreign keys pullingback to the primary key these options referto that what what happens to the parent? Nowthese these options right here these referwhat happens refer to what happens to thechild Okay, now it's getting real confusing.There's arrows everywhere, like graphs anddiagrams and but you'll get it Don't worryrestrict. You might also hear as no actionis well at least for my SQL in different databasemanagement systems. These might be different,but anyways, restrict is going to throw anerror. So if we try to like, we can give wecan give one of these for each option. Soon delete, we can either give restrict, cascadeor set no or No. on on delete, we can haveit restrict cascade or said no to To add morearrows, you know, but let's say we're workingwith delete right now. And we wanted to, wewant 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 anerror whenever the parent is deleted. So ifI tried to delete the user, with the ID, Idon't know I'm writing all that out, let'sjust say the ID of 504. And then this childhas a row that has 504, within the foreignkey column, well, then it's going to throwan error, and it's not going to happen, andthe parent will not be deleted, not gonnahappen. So that's how cascade on Delete works.I'm sorry, restrict on delete, now restricton update if we updated that parent and changetheir primary key. So let's say we change504. And these, these children both reference504 with the foreign key, and then we changethis to 508. And it's not going to work. Andit will go back to what it was originally.Because we have it restrict, we're not goingto let that happen. You see. All right, whatabout the next option, cascade cascade, we'lldo 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, totallyhelping. Alright, so now if we cascade ondelete, that means if we delete the parent,we're going to delete the child as well. Sowe delete 504. And both children are deleted,boom, child deleted. So now 504 doesn't existat all. So like, for example, if this wasa comments table, and we had a comment fromthe user with the user ID of 752, and we deleted752, the user will then obviously, all ofhis comments by him should be deleted, too,because we'd have a comment by user that doesn'texist, which does not, which isn't supposedto happen. So cascade on Delete will do thaton update is, if for some reason, we updatedthe primary key, which obviously shouldn'thappen, because primary key should be neverchanging. But you still want that protectionon your database. On update, if we have cascade,if we change like 504, let's say these peopleexist again.Skinny, right? So it's because, okay,so these people exist, again, 500, for 500for these references, 504. And if we updatethis to 508, well, that means all of the childrenwill update as well. So now, it connects withinwithin different tables. Finally, we haveSentinel. And what that'll do is it will basicallyjust set the children as normal for that specificvalue. So if we deleted the parent, that thisis just going to get rid of the value, therow should still exist. But the actual foreignkey reference, it no longer references anything,it's empty. So there's no it's not referencinganything. So the column for that row. So likethe, let's say, we have a comment. And it'sa comment from Billy Bob with the ID of 504.If we delete that, whenever we're going tohave a comment, with no poster, or no comment,or at all, it just no value. That's all saidno one will do for us on delete an update,same thing, if we change the ID of the parentchanged it from 504 to 508. Well, all of thechildren are going to be set to no value aswell. All right, now there's one importantthing when he was set know that children haveto have, they can't have I should say, thenot no characteristic. We talked about thatin previous videos. I'm just gonna put anend for sure. If this is not no, and we update,if we have on update set, no. And we update504 to 508. Can you even I've ever gottenthis off this whole time? reset five or fouror 508? I think you can see that one. Youcan we change five or four or five away? Andthis is not No, it's gonna be like, No, wecan'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 avalue for that attribute or that column. SoI'll give an error. And I'll go back to howit was. So that's an introduction of foreignkey constraints. Sorry if I talk fast, butyeah, cool stuff. It's important stuff. Solearn it, study and learn it for your specificrelational database management system. Inthis video, we'll be talking about simpleand composite and compound keys. These arecategories of keys. So like when you definea database, you can say, this is a primarykey, or you can say its foreign key. But youdon'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 ormore for design sake, so you know, the bestdesign for your database, but you don't actuallyhave to define it, right? Like, for example,this piece of chalk right here, you can definethis as a white piece of chalk. Now, whatkind of white piece of chalk? Is it? Well,it's x brand or whatever, you don't have todefine that when you're telling someone Oh,here's a piece of chalk. That was the worstexample ever. Moving on. Alright. Simple andcomposite. Okay, so simple means that thekey consists of one called composite meansit consists of two or more columns. This ismost common with natural keys, because naturalkeys can be a combination of multiple columns,whereas a surrogate key is just a random numberone column. So surrogate keys are simple compositekeys or multiple columns. So for example,we can have a first name plus last name, plusemail. This could be an example of a compositekey because this whole group as a whole isthe primary key. So we could haveJim,Jake, andlegitare awesome at whatever's, blah, blah, blah.And then we can have another another one withthe same exact name, Jim, Jake. And we canhave a different email, these two are stillseparate, they're unique. Because the combinationof the three are unique, Jim and Jake, repeat,but the email is different. So these as awhole, are different values for primary keys.So that's an example of a composite primarykey. The reason is, is because it consistsof three columns 123. This is an example ofa composite key, a composite natural key tobe specific, because we're just we're usingthings that are already naturally being storedwithin our database. Now let's think of asimple key, natural one, or username wouldbe a good one is the neck username can besomething that can be used by itself as akey, it's simple, because it's only one column.Pretty simple. Iknow. Alright,so I want to talk about another term you mightrun into. And that is compound. Okay, now,I want to clarify that some people and somerelational database management systems don'tclarify the difference between these two compositeand compound terms. And they just use theminterchangeably as meaning a key with multiplecolumns, some people go a step farther, andbreak these up into two separate terms. Sowe'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'sa compound key, the because most people arejust gonna look at you like you're an idiot,and not really care. Alright, so anyways,moving on. A compound key is basically,whenit's a call, it's a key that has multiplecolumns, and they're all keys themselves.So this is the most common example. This isfor intermediary tables. So think about likethis. We have a table used to store informationabout videos, we could say it's a video table.We could also have another table here, wecan have a user table. Actually, here, let'slet's do it this way. We're gonna have a commentstate. Okay. And then within this commentstate, we're gonna have a column of what videothey're commenting on. You see, well, thisis a many to many relationship, right? Sothis this might not work out well with databasedesign, because one user can comment on multiplevideos. And then one video can have commentsfrom multiple people. So the best way to dothis rather than having two separate tableslike this We would create a video table. Andthen we'd have an intermediary table. Andthis would be video comments, or user comments.You see what I'm saying. So we get a connectionbetween the user and the video to create anintermediary table. And it's split up withtwo keys. So we have the user ID, and thenthe video ID and then we could have anothercolumn for the message, or the date or whateverelse. Well, you can see, if you don't knowwhat I'm talking about, go see my videos overdesigning many to many relationships. Butotherwise, if you know I'm talking about movingon, you can see that the entire key itself,every user and video ID combination has tobe unique. That might, that might be a badexample, because a user could comment on thesame video more than once. So this is probablynot the best example.Let me think of a good one.But anyways, this would be a an example ofa, we could add another column in here tomake a unique, we could have the date andthe message. So the message has to be unique,and the user ID hasto be unique. And the video ID has to be unique.That'd be a composite key, a compound, a compoundkey, missing, for example, real quick. Soa 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 aboutthe student, this information table holdsinformation about the class. Now, how do weshow enrollment, should we make columns forevery student here now, because it's a manyto many relationship, that means we need anintermediary table between the two. So thisis a good example, sorry about my bad examplein the last one, but that's a good exampleof a composite key, because the two columnsthemselves wouldn't be enough. So we'd haveto add another one, which is not a key initself. But for this one, we could have acolumn, we'll name the table, student classes,or the student class, whatever you want tocall it, student classes. And within here,we could have the IDs, student ID, and classID. Now, this combination between studentID and class ID must be unique. So let's saywe have the value seven and four, seven andsix, seven, and seven, that would work. Butif we had something like seven and four, again,we'd get an error, because that's alreadyin there. And that's not going to work outso well. So the combination has to be unique.And these are also both foreign keys, whichpoint to another table. So both of them arekeys. And the primary key is compounded. Becausewe have the student ID, and the class ID asone 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 canrepeat like six on this side. But we can'trepeat the combination of seven and six, theyhave to be unique as a whole. So compositekeys. That's when you have at least one isa is not a key in itself. For compound, theyall have to be keys. So for the composite.Let's look back at the the example we gavein the last one with the videos, the videocomments, let's say this is the intermediarytable blown up just so we can see it realbig. This is the user ID. And then we havethe video ID. Well, this isn't sufficientto make uniqueness because a user could commenton the same video more than once. Thank youfor replying to comments or something likethat. So we need another column, we couldadd the timestamp or we can just say the timecolumn. And this is going to keep a uniquetime because you can't comment on the samevideo at exactly the same time twice. Right.So that would ensure uniqueness so we canensure these three as a whole are unique.That's an example of a composite key, becauseat least one column is not a key in itself.This is a key because it points to a user'stable. This is a key because it points toa videos table. This is not a key becauseit's just a column for That intermediary table.So that those are the three terms I want totalk about in this video. One other thingI want to talk about real quick, I'm not goingto say what the best technique is. For this,I'm just going to say what some people dowith an intermediary table, some people willgive it a surrogate key on top of the compoundkey. So for example, with the college, keepswitching back and forth between examples,I'm probably confusing you, they might addsomething, such as enrollment ID.And then we have the student ID. And thenwe have the class ID. These are both foreignkeys. And they might use this enrollment IDas a, just a surrogate key for this table.Although it's not a requirement. For certainrelational database management systems, somerelational database management systems, orframeworks or something, are not going tobe able to work well with composite or compoundkeys. So you might need to have a simple keywhich in that case, you could still have theidea of a compound key. This group right here,the student in the class, didn't have thatindex or whatever you want, or you can justenforce uniqueness with that. But then youcan use an enrollment ID, so you can havelike seven. And then you could have studentsix in the class four, and then you get eight,with the student four in class 12. And thenyou can have nine, with the students 16 inclass four, and then we could have 10, withstudent 16, in class, 12, and so forth. That'sjust an example of what some people do. Thisvideo is going to be a quick review, I'llactually be using my notes here just to makesure I get the main everything because I'mnot going to try to memorize like 10 videosor content has this crazy, crazy eye. Butanyways, first thing we started with was superkeys. So a super key was just any number ofcolumns that make ensure uniqueness, but thena table. So we have super key. Which I stilldon't know why that's only one word, whichjust confuses the heck out of me. So someoneplease explain that, then we had a candidatekey. Now the difference between a super keyand a candidate key key is that a candidatekey is the least number of columns used toenforce uniqueness. Now you cannot you canhave multiple candidate keys if you like acertain group. So if that's the case, youhave to pick one as your primary key. Theprimary key is the one you select as the themain key for your table, all the other onesyou can assign as alternate keys. Which, ifthat's the case, they still can be unique,you can still enforce uniqueness upon those,but they're not used as the main key withinyour table. What else? Alright, well, thenthere's, that's pretty much the all the ideaswith finding a primary key. And then whenyou reference that in another table, you havewhat's known as a foreign key. foreign keysreference, a primary key is a connection.That's how you make connections between tables.If you have a user user table, and then youhave a is a comments table. Well, the commentis posted by a user, so you can referencethat user in that comments table. And we talkeda lot about that. So I'm pretty sure you guysgot thatpretty well.Now, this is pretty much all you got to worryabout four primary keys and foreign keys,you just got to worry mainly about these two.These ones are less important about findingand figuring out. These two are essential.I'm not saying these aren't important, they'reimportant. But really defining a super keyis kind of pointless when you know there'sthe candidate key you don't have to say, Okay,well all of these define uniqueness. That'sone. That's one super key. All of these defineuniqueness. That's another super key. Oh,finally, we're down to the correct size fora candidate key. That's our candidate key.And then I'm check marketing By the way, justso you know what that is. And then oh, okay,we got a total of 10 candidate keys, fivesuper keys. Now let's find a good primarykey. Oh, this one looks nice. We'll use thatone we'll set the rest is alternate keys.That's just complexity that is unnecessarywhen it's easier just to say, oh, there'sa candidate key. It's already the smallestthat we need. We'll use that as our primarykey. And now let's see if we have any otheralternate keys. We don't have any good onesaren't too bad. It's fine. who really cares?Oh, we do have some good ones. Oh, yeah. Let'sset those an index and make sure they're unique.And we'll use this for some searching lateron. That's an example of how you would finda primary key. You don't have to make thismanual labor for like 20 hours to figure outwhich primary key to use. Alright? Then youreference that primary key in other tablesforeign key. So that's why these two are themost important foreign and primary, thoseare the two you should focus on the most andmemorize, okay, the alternate candidate superkey, and they're kind of important, but notas important. Alright, so let's take a lookat primary for. let's just, let's clear thisout for a second. Alright,Rob Merritt.Now, there's some categories, dude, I dustednow it's like I can breathe. There's somecategories of keys that we can talk about.So the first one was surrogate and natural.So surrogate natural keys. a surrogate keyis just a random number that has no real worldvalue, or no real world meaning, just likean ID, user ID, student ID, caller IDclassroom ID,store ID, sale ID, record ID, song ID, picture,ID, whatever you want it to be, Idon't really care.So long as it's an ID, and it's a surrogatekey, then,yeah, yeah.I don't know what I just did there. But anyways,natural something that's already naturallyin the database. And you just define it asyour key. Now, typically, when you want touse keys in your database, which you will,because it's your relational database, youwill pick either surrogate or natural keys,and you will use them for throughout the restof the database, you're not going to switchfrom surrogate and natural, you're not goingto be like, Oh, this one has a natural key,we'll use natural, but this one doesn't, andwe'll make it surrogate. That's a bad design,you should be able to enforce uniqueness bythe columns that are already there. And thenyou can add a surrogate key if that's whatyou want to do. And if by any chance, youcannot possibly define uniqueness withouta surrogate key, you're pretty much requiredto use the surrogate key. But that situationshould try to be avoided. But there's timeswhen you literally cannot find a natural keythat will be sufficient. So you guys kindof follow it. I mean, surrogate keys, randomnumbers, natural keys already in the table.Cool. Simple. Speaking of simple, we'll talkabout the simple key. Alright, so we got simple.We got a compositecompound.Okay, so simple is basically a one columnkey, composite and compound, our multiplecolumn keys. We talked about those in theprevious video, I believe. Yeah, so that'spretty much all the categories of keys. Thewhen you define your primary or foreign keys,you're not gonna have to specify if it's asurrogate natural, simple composite or compound.This is for our sake, to design the best way,because it's, for example, do you want touse composite keys? Or do you want to usesimple keys? That's a good question. If youdecide you want to use simple keys, whichby the way, surrogate naturals kind of thingyou do throughout your entire database, youcan kind of switch back and forth from simpleand composite. For example, you could havea user's table, and we could have a look,I sorry, students table, a class table, andthen we're gonna have a, an enrollment a withthe students taking certain classes, we coulduse a week, I guess we can use a compoundfor this table. And then we could use simplefor these tables. That's something you coulddo. But you should still use either surrogateor natural, just pick one and use it throughoutyour database. Hopefully, I'm not talkingtoo fast, because I'm going pretty quickly.If I am just let me know as I see a columnA couple inches, come down a couple decibels,because kind of hyper, okay. Now, the otherthing we talked about was foreign keys andforeign key constraints. Well, the main kindof foreign keys, the main kind of foreignkey 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 statementor create or whatever your relational databasemanagement system does to create tables, thechances are, you're going to say thatyou have a foreign key. I'm justgonna put fk for short, and then you havea constraint and you can usually name thatconstraint. So you can be like blah, blah,blah, and name it whatever. And then we couldsay on update,or on delete, and the options were the onesI thought you were do nothing or basicallythrown air or we could update the childrenor we could basically get rid of the valuefor the children. So that would be restrictcascade and set. No. But your relational databasemanagement system might use something a littlebit different, which is fine. Yeah, that's,that's about it. Yeah. Yeah. That's prettymuch it.Cool. Cool.So yeah, that's a summary of keys. So yeah,well, that was aton of stuff.So there's a couple different names for this.So basically, it's just a standard. It's astandard for drawing databases. And the coupleterms you might hear, or acronyms I guess,e AR, model, E, AR, D, orE AR model,or just database design, whichever one's easiest.But basically, these are some terms used torefer to a method used to draw out your entiredatabase structure. These videos have beendrawn tables and lines and drama, drawn tablesand lines and stuff, and all kinds of coolthings like that, but actually haven't officiallydesigned any database. So what we're goingto do now that we've learned most of the concepts,the only thing we haven't really covered isnormal forms, which we'll be getting intothose. But anyways, and I'll make them easy,don't worry. So what we're going to do iswe're going to learn the standard for drawingthese databases, we're going to practice withthat. And then we're going to learn the normalforms. And then we're going to design databasesfor a lot of stuff oriented. So we're goingto be getting into the standards of drawingdatabases. And obviously, the standards kindof rough. I mean, there's, there's not a specificway, you have to do everything, because that'sannoying and dumb. So what we're going todo is we're going to be drawing pretty simplethings. And I'll just be kind of illustratingkind of how that works in this video. So whatthis means is enhanced Entity Relationshipmodel, enhance relationship diagram, enhancedrelationshipmodel.Yeah, I think that's what they stand for.I don't think it stands for relation. I thinkit stands for relationship, which there'sa difference, as you should probably know,by now. A relation is a table, a relationshipis a connection between two tables. Yeah,so anyways, yeah, so we're gonna be talkingabout these design techniques are probablythis call and like, er, D er, er, er? Or,who knows? But yeah, the way you do it isyou define your database structure. So anythingthat is DDL pretty much data Definition Languagepart of SQL, we talked about that a long timeago. We're not going to define the actualvalues. For example, if we have a table forusers, we're not going to put in a row. Oh,Caleb curry with the password of sweet pieis delicious. 24. And the email of Yeah, yeah,yeah, yeah. yeah.com and the telephone numberof 100 you wish or anything like that. We'renot going to put anything like that in there.We're just going to put the columns username,password, email, telephone, three d that isso cool. Okay, anyways,let's get started.So to draw a table, database table, you justdraw square, it's like super easy, okay. Well,how he would evolve and so easy, right. Andobviously, doing this on a chalkboard is notvery convenient, because for one, you're limitedin space. And for two, most of you guys don'thave chalkboards. And for three, I don't knowa third reason. But most of this is actuallydone on a computer. And we will be doing thathopefully soon. So stick with these conceptswe'll be getting into on the computer designfor whatever reasons. And obviously, as Imake new videos in the future, that's whatI'll be doing. So for now, we'll learn theconcepts. And you can download Entity Relationshipdesign programs, such as MySQL Workbench,I made other ones like verta bello or something,I don't remember what it's called. And a coupleothers, which you can design for any relationaldatabase management system on that one. Sogo check out my other videos, or my playlist,I think it's under reviews or something. ButI don't know, look up database design on mychannel, see what I got. You can also justlook up er, er, er D relationship, or design.And you can get all kinds of apps and programsto help you design your databases. And thensome of them have what's known as engineering.So you could forward engineer your databasedesign, and it will basically just programyour database for you. And it's super easy.Yeah, but I'm getting way off topic. So let'sget back on topic. Okay. So here's how wedraw a table. What we do is we give it a tablename. So you put the table name either righthere in the in a complete column like that.or miss right above it. So let's put it upabove it right now. So this is a user table.And then what we can do within here is weput the columns this way, vertically, whenwe write them horizontally, but we list themthis way, we're not putting them across here.Because if we put them across here, we willassume that we're going to have row value,like Caleb, password, and his awesome emailis legit, or cool, and then the password alreadyset password. But we're not doing that, we'rejust going to put the columns. So the firstcolumn we can have is user ID, because we'rejust going to use a surrogate primary keyin this example. So we're gonna have userID. Now, when you get into it, you can alsoput other things like data types in here,and any other restrictions you have, suchas 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 goingto be writing the same thing over and overagain. And you kind of only do that when you'refinalizing your database. So you can kindof worry about the data types later on. Butfor now, we're just going to get the columnsfigure that out. Good.We'll go from there.So user ID, username,the password,and so forth. Hopefully, you guys can readthat. Probably not. And then when we do ifwe want to create another table, guess whatwe do? We just draw another box, right? Andwe connect these with lines. And we can, thereare certain ways to draw lines that representdifferent linear relationships. Like that'sone 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'spretty much all the basics of entity relationshipdiagram drawing, just remember, we put thesein here, the columns. And what we can alsodo if we wanted, we could put indexes downhere, we could put like index types. And thenwe could also draw our relationship typeswith different lines. And we can draw ourforeign keys by either putting them down hereor doing some other kind of thing. Now, there'salso other conventions you might have heardof, or have used, like UML, and other designtechniques. And I'm not saying those are bad,I'm just saying this one super easy for beginnersand 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 drawingout databases. cardinality is basically therelationship type between a row of one tableand rows or a row of another table. So I thoughtof the the best example I have for this isa credit card database. So like, basically,a credit card companyhasa table for credit cards, and then a tablefor card holders or the people who order thecredit cards and use them to buy things andbasically their cut the credit card companiescustomers, I guess. So I will draw those tablesout, just so you can see this, I'm not goingto draw them or vague or anything. But justso you see how the connection works. So wehave a card holder. And then over here wehave the card. And there's some kind of relationshipbetween here between these two tables. Now,the only two possibilities for cardinalityare one, or many. Obviously, for both sides,you can have one too many, one, too many,or many, too many, those are the only threepossibilities. Now, another thing to thinkabout when it comes to many to many, thatmight even you might even consider that asnot a possibility because you'd have to havean intermediary table, which we talked aboutthat when we talked about designing many tomany relationships, when when we're talkingabout many to many relationships here. It'slogical, 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 ina database, we would have to have a cardholdercard holding table and then the cards tablethat would be how to draw a many to many relationship.So hopefully that kind of makes sense forthat. So in reality, when you're actuallydrawing out finalized products of databases,the only possibilities are one to many, orone to many, because even if you have an intermediarytable this is good. Going to be a one to manyrelationship 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 rightnow. So I wonder many, it's pretty simple.The one side, all you have is a straight upand down line like this, the many side youhave this, this thing that we're doing rightnow is known as crows foot notation. Becauseit looks like crows foot, I think that's whythey call it that I don't really know. Butthis is a one, this isn't many. So when we'redrawing it, one, many, we could also haveone to one, of course. So that's another possibility.Iforgot to mention one to one.Now, before we started drawing on both sides,I think it's easy just to think about oneside, like think of a cardholder relationshipto a card, we would have onecard holder.Now that we have that side figured out wegot, we just think about the card holder,then we can add in the card, and we have manycards, so one card holder can have many cards.And that's how you would design the line betweenthe tables. Like that. Now there's anotherthing 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 itsimple, just drawing these things here. Butyou might see also like circles, you mightsee a circle here, for example. We'll talkabout that in the next video. So now you knowhow to draw a one to many relationship. Sowhat does this exactly mean? That means onerow can be connected with many rows and thistable. It's not necessarily the table butthe rows within the table. So we can thinkof a specific card holder, we can have a cardholder with the name jack, Id of two. Andwe can have many rows over here cards, wecan add a card with the ID ofID six,Id eight,Id 12. And you can see this guy has a lotof credit cards. So all of these rows canbe connected to an individual row within thecardholder table. Now let's look at it theother way. We could have a many to one relationship,or basically a one to many going the otherdirection. So we could add this. So now onecard can be connected to multiple people.But multiple people can be connected to onecard, I'm sorry, one card can be connectedto multiple people. But one card holder canonly be connected to one card. So that meansone 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 havea card with the ID for the ID of six doesn'treally matter what the numbers are. And thenwe have a card holder, we just got got a guynamed jack, which ID is three. And then wegot billID two,well, we have we can have one card attachedto many people. So this card can be attachedto many people, which means these people coown a car, they're both the owner of one card.But now you have to realize an individualcardholder can't be connected to multiplecards. So jack cannot own another car, that'snot going to work out. If you wanted thatto work out, then you'd have to have an intermediarytable, which we talked about and designingmany to many relationships. And this videois less to be a review of relationships, butmore of how to draw them. So you have to realizethe difference between all of these possibilitieson one, one to many, or one to many, it justdepends on the actual application modality,it's basically whether or not the child isa required child the relationship is required.We talked about that when we talked aboutthe column characteristic.Not No.So, think of it like this, we have a creditcard 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 thecard from the credit card company, they'regoing to have an ID, and that's going to bea not null column, meaning it has to havea value. That is obvious because it's goingto be the primary key surrogate primary key,and it has to have a value. So we have likethis guy, and he has an ID of seven, we haveanother guy with the ID of 12, and then 368,and so forth. Those are ID cccard,that is going to have a column, which is aforeign key, referencing the cardholder becausethe card is the child in this relationship.So the cardholder has the primary key, yousat down there, if you can, card is goingto 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 havea card, a card with the ID of 48, I'm justmaking up numbers are here, in a card withthe idea of 98, we have a card of the ID of112. Well, they each have an owner, you seethis 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 thatperson. Seven, this card is going to be ownedby that person. So all of these three cardsare owned by that person 368, that's goingto be owned by somebody else. Now, if we gavethis column right here, the foreign key thebasically the cardholder column, because it'sa child, if we gave it the column characteristic,not know, it would be required that everysingle row has an owner. That means we can'thave a card in here that is either not activatedin doesn't have an owner, or we can't havea card in there that is not being currentlypossessed by a cardholder. Because you thinkif you have a credit card, somebody owns thatcredit card. But when you first make a creditcard or something or if you like, if if youget rid of the credit card or something, itmight be a credit card that currently doesnot have an owner. Does that make sense? Sobasically just a credit card that's not beingactively used, or is disabled or somethinglike that, and does not have a cardholder?Well, if we wanted us to if we wanted thisdatabase to be able to store cards that don'thave owners, we would not want the nominalcharacteristic, because that's going to preventit. So that's kind of how that works. So ifwe take that off, then we could add a carddown here with the ID of 101.That doesn't have a card. Well, I know wealready explained this all but now let's talkabout designing. Let's just make this simple.Pay attention to the Say, say one car, well,this obviously is going we're going to talkabout a a one to many relationship, meaningone card holder owns many cards. And we'realso going to be talking about a one to onerelationship, meaning one card owner ownsone card for one too many too many. We justneed an intermediary table, the same conceptkind of applies. But we don't necessarilyhave to go through that just break it intoa one to many, two to one to many relationships.Alright, so let's try this. So we have overhere we have the table. That's going to bethe card owner. Over here, we have the tablefor the cards. And there's a relationshipbetween these two as a really big gap butwhatever. So let's start over here. We cansay one card holder because that's what we'rejust going to say one card holder for eachtime. Let me bring this in so they can actuallysee.One card holdercan have one card. So what we just did iscardinality. We talked about this in the lastvideo. Now let's do another example was thesame 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. Sowe have the basic relationships. Now we canadd one more thing to say if it's nullableor not nullable, basically, does the child.This is the child. Which is the card. We'reasking, does it does the foreign key columnhave the nominal characteristic, that's sowe have the possible relationships, one toone, one to many. Now, what we're going todo is we're able to add the modality in andthe way we do that is we add either a littlecircle, or a little bash, or you can thinkof it as a zero or a one, which actually willmake sense. And we'll see why in a second,zero,or one.So, over here, we have the cardholder, onecardholder for all these examples. And thenover here, we have the card. zero means thecolumn does not have the not null characteristic,meaning it accepts no value. So this is sayingit can accept no value. So we have a one toone relationship, that's the maximum, thecard holder can have up to one card, but it'snot required. So zero or one cards, one orone, cards, zero, at least zero cards, becauseyou 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 generallyfrom table to table. So we would have cardholder, and then card. And why do I have tosay that, basically, because if we have, ifwe have zero here, that means there's notnecessarily going to be relationship betweena certain row. So when we're talking aboutrows, we might not actually draw a relationshipbetween certain rows. When we draw relationships,we talk from table to table, and those relationships,explain what rows can have relationships withdifferent rows. So in this example, let'sjust focus on the top one, just to make iteasier,we would draw this within our database application,we would say we have the table cardholder.And then that has some kind of relationshipwith the table card. Now we could say onecard order can have zero up to one relationshipswith one individual card. So we have a cardholder with the ID of seven. And then we havean ID card with the ID of 68 owned by thecard holder seven, so that points back andthat's acceptable. We can also have a cardwe got a card 69. And we could have a personeight, and we could say oh it doesn't havean owner, that's fine, we could have anothercard 70 and it can have the owner eight, whichpoints back to eight. And then we can haveanother person which could be a seven, we'regonna have a card, we're gonna have a card63 which the has the owner 87 and that pointsback to the owner 87. So that's how thoserelationships work. Now, at first, this canbe kind of confusing, so just practice. Makesure you fully understand how each one works.Just review we could have, there's four possibilitieswe 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 cardbecause you can have one the least and thenup to one and then you have one card holderzero or more than one card holder one or more.These two are required, meaning every singlechild has to have an owner. Or every singlecard has to have an owner, every single childhas to have a parent. So hopefully that allmakes good sense. I just want to introducea topic. So in the next three or so videos,when we go over it, it's gonna be like kindof not all new to you, you'll kind of havean idea of what's going on. So we're goingover what's known as normalization. So, normalizationis a process where we go through our, ourdatabase plan that we have, and we start correctingthings that may cause data integrity problems,or just repeating data, etc, etc, whateveryou want to say. The normal forms are basicallylike a checklist that you're going to follow.And when you get to the end, you will havea pretty good design database. There's threemain 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 guessedit. Third,normal frame.I'm kidding, it's form. That'd be weird, though.Alright, so God,first of all, form second normal form. thirdnormal form.These are the three steps of normalizationfor databases. Now, if you really, really,really want to get into database design, youjust like, man, I love this stuff a guy getmore? Well, there's tons of more steps, youcan add and make things all complicated ifyou really want. But for our practical purposes,these are the only three that you're goingto really, really need. And I know there'slike a glare right here, because these lights,and I know, I'm just working on it. So giveme a couple videos, and I'll try to work onmy lighting a bit. But for now, first of allform second Normal Formthird normal form.Why do we have these? Well, it's because wewant a systematic way to follow to producea normalized or a good structure database.Alright,there's a couple things you should know whenit comes to normalization, we still have totalk about the idea of things being atomic,we want everything to be atomic, you alsohave to kind of think about data dependingon other data. So if you think of like, let'ssay a primary key, yes, 608, just a numberthat could refer to anything that could bea person, an order an event, whatever it is,is just the primary key. And that also isgoing to have different columns in the table,which have different attributes about whateverthis thing is. So let's say it's a person.And we have a column for the name. And let'ssay this dude's name is Caleb, what a dumbname, right?This depends upon the primary key. So whatI mean by that is, if we change the primarykey, 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 differentperson, right? Because they're two separateentities,this one's referring to someguy over here. And this one is referring tosome guy over here, two separate entities.You can also think, oh, by the way, that thatprocess or that thing, this is called dependency.So the next three videos will be going overthe first normal form, second normal form.And the third normal form. The other mainthing is that these are kind of comprehensive,I guess, you could say. So what that meansis once you have first normal form, now youcan go on to the second normal form, but youcan't go jump in right here, you have to haveit in first normal form before you can getinto second normal form, same thing for secondnormal form into the third normal form. Sothat means if you have a database that isin third normal form, it is also in secondnormal form, and in first normal form. SoI know I'm like getting all these technicalterms in junk. So if it's freaking me out,don't worry. It's really simple. Guy, getmy hair out of my face. Alright. But basically,it's, it's a list of steps. list of thingsto do. And basically, you can do this steptwo until you do step one. So this year secondnormal form, that's step two. Can't do thisuntil you do that. So you got to go throughthe process and then once you're at thirdnormal form, things are good to go. This videowill be over the first normal form, whichis the first step in database normalizationthis normal form deals with data being atomicor atomicity. Alright, so let's begin. Let'sfirst look at three examples where there areproblems. And then I'm going to explain asolution. If we take a look at this firstexample, we have a user ID, first name, lastname, email, and address, I have some valuesin here. But those aren't really important.In this example, the main thing you need topay attention to is the address, because anaddress if you think about actually consistsof a street address, state, country, and soforth. So this column is not atomic, we'reasking for multiple things within one column.So that is the problem in this example, thesolution for this one would be to break itup into multiple columns, you know, a state,and so forth. That way, we don't have a bunchof things within one column as an individualvalue. This example, you can see, we haveone row with a user ID, first name, last name,and email. But if you look closely, we havetwo emails, these emails were entered togetheras one individual value. So you can thinkof maybe on a website or ask you for youremail, imagine putting one email in and thenputting your second email in the same boxand clicking Submit. Well, it's entered intothe database as a single value, maybe withthe comma or something, but it's still enteredin as the email for this user. So two valueswithin one, which is not atomic, in this example,the actual values given for the email areatomic, because they're only one email, butwe put two entries within our database. Sonow we have this in here twice, this in heretwice, and it's in here twice, and the primarykey should never be in there twice. The onlytime you can see the primary key in theretwice is if it's a reference as a foreignkey, because the foreign key can be put inthere multiple times. But the primary keyof five should only be in there one time.So we have all that duplicate data. Now whatif 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. AndI actually like thatname. But anyways,if he gets a name change, and he updates hisinformation, what if only this one's updated.So now we have conflicting data. And see that'sthe problem with data integrity, we do makesure that doesn't happen. So in this example,two column is the problem. And this example,the values for the row is the problem. Andin this example, the values for the row isthe problem. So these basically have the sameproblem. How do we figure out the solution?Let's take over here, let's take a look overhere. And as you can see, there is no solution.So yeah, guys, thanks for watching. I'm justkidding, let's, let's do this, the best solutionfor this problem is to have a user table.And I'm going to write the columns that arewithin this user table. So we would have auser ID, then we are going to have a firstname. And we're going to have a last name.If you wanted the address in there, you couldhave street address, state country, all thatother good stuff, then we're also going tohave an email table. So the solution is tobreak off the problem column. You can seein this example, the email is the thing causingthe 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 forthat. But since this one, even though thecolumns correct, it's the values within itthat's causing the problem, we would needto create a new table for that. So that'swhy we have this email table. And now withinhere, we're going to have an email id. Andthen we're going to have a email, which isgoing to be the actual email address, whichin this case will be these. And then we'regoing to have a user ID. Now this is goingto be a foreign key. That references thisuser 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 andsee what it might look like. Here's what theemail table might look like.We would have an emailID which is just a randomly generated numberfor each email 600 dates fine. And then theemail that's going to be the actual addressof the email. So we could have this I tryto make it just as bad as handwriting.Perfect, then the user ID of who owns that,which in this case would have been five, becausefive was the ID that was used for this user,this will be your first row, then we're gonnahave another row, and it'll be a new email.So we'd have a new email id, it'd be a differentemail, we would use this one this time. AndI'm going to make that beautiful handwritingyou guys love. There we go. And then the userID for that would also be five. Now this repeatingvalue 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 aseparate email because this is the primarykey of the email table. Now, when we lookback at the user table, we don't have anyrepeating data, because we're only going tohave a user ID such as five first name, Caleb,and the last name, curry. And there's no needto make a repeating row. Because we have allthe information we need without having anyproblems. If we change the name, we will justupdate this keep the same ID change this towhatever we name our name ourselves, or whateverthe person names themselves. So there yougo, everybody, that's first normal format.Now, second, normal form deals, deals withwhat's known as a partial dependency, that'swhen a column only depends on part of theprimary key. So in order for it to dependon only part of it, you have to have a compoundor composite key. So basically, the primarykey has to be multiple columns. Because ifyou just have one column as the primary key,a columncan partially depend on half of it. That kindof makes sense. I don't know, maybe not. Let'ssay we just have a random table for people.So we have you know, the person. So let'sgo with let's go. Let's just use a personID. And we have some attributes about thisperson, we have their name, you know, maybetheir phone, or their pH level, and maybetheir email. Let's first talk about what adependency is, before we go and talk aboutpartial dependencies. These columns dependupon the primary key. So in this case, thisis the primary key. And there's a dependencyhere. Another way you can think about is ifwe have a another table about cars, we canhave a car ID, which is also a surrogate key.Now this person's name, it does not have adependency here, you can see this name isonly dependent on the person ID not upon thecar. So this doesn't make any sense here.Another thing is that the person ID doesn'thave an unrelated entity or an unrelated attributeabout the car ID so such as thecar color.The car color has a dependency on the car,not the person, this isn't going to make anysense. That's what a dependency is. Now, whatis a partial dependency. So let's see whata partial dependency looks like using surrogatekeys, you'll see this when you have a manyto many relationship, broken up with an intermediarytable, which is the correct way to designa many to many relationship. So let's thinkof the example of books and authors becauseif you think about it, one author can writemany books and one book can be written bymany authors. So conceptually, it's a manyto many relationship, we're going to storethat in the database is one to many relationshipon one side, and then one to many relationshipon the other side. So we get this lookat the table,intermediary table, and then a table. So overhere, let's put the author's so this is theauthor. Then over here we have the book. Sothis is the book table. And then in betweenwe have the intermediate intermediary tableof book authors, or book author.So this is the correct way to design it. Nowwhen it comes to attributes, all the attributesabout The author are going to go over here,all the attributes about the book are goingto go over here, and all the attributes thathave to do with both the app, the book, andthe author's connected, those will go in thistable. So first, let's give them some surrogatekeys will give us one author ID,we'll give this one a book ID. And this one'sgoing to have two foreign keys of the authorID, and the book ID. And those foreign keystogether 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 theirlast name, maybe their birth date. Whateveryou want to put about the author, the bookwould have stuff about, you know, the ISDN,which is, the ISDN is the code on the backof a book, you can look up, it tries to uniquelydefine that book. So the exact addition andeverything else, so you can just use thatto define it. You know, we will make maybehave the page numbers or the publisher, thepublisher could be a primary key or a foreignkey to another publisher table, or you couldjust have it in that table. If, if that'show it worked, but likely to go to anothertable. But we're going off topic. Anyways,it looks something like this. So author informationgoes over here, book information goes overhere. This is the correct way to design thistable. Because when it comes to dependency,the first name of the author has only to dowith the author. And it has to doall about the author.Now let's look at things that have to do withboth the book and the author, we could havesomething such as the author position.Now what that is,when when you write books, often there's aperson who has like the first author position,which is what the big name is. And then youhave 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 theauthor. Because if you think about it, ifwe put author position here, that's goingto depend on what book we're talking about,we can't just put author position one, andsay he's first on every single book he everis going to write, that doesn't make sense.We can't just say author position one on thebook, because it doesn't say which authorwe're talking about which authors one, itdoesn't make sense. That's why we have tohave it in this table, because it has to dowith the book and the author. So we couldsay book ID is 17. Author ID is 22. And theauthor position is one. So that would saythe book with the ID of 17. And the authorwith the ID of 22 is in the second or thefirst position on that book. It's a littlecomplicated, but we combine that with joinsto make it make more sense for the actualviewing of the data. But anyways, this relieson the book and the author ID, that's whyit's in this table, this is correct. Now anincorrect thing would be something like theISDN. Because the ISDN has to do with thebook only. So when you look at this, it reliesupon the book ID it has a dependency on thebook ID, but it doesn't have a dependencyon the author ID. This is an example of apartial dependency. Now the correct way tofix this, in this case would just to be takeisbm and put it in the book table, which weobviously already did, because I put thatin there first when we started. But if wedidn't have this table and you're workingwith to say like one table, maybe well, thenthe correct way to do it is to take the bookID the iasb and drag them to another table,and then use a foreign key to connect to thattable,which we alreadyhave it structured correctly because we understoodhow to design that many to many relationship,which is why relationships are useful to understandwhat if you don't have it already set up correctly,you'll have to take the partial dependencyand move it to another table and referenceit with a foreign key for a second normalform, you want to first be in first normalform. And second, remove all partial dependenciesby moving the columns as we did here we tookthat is being put in the correct table. Nowanother thing you can think about is if youhave a table where there's a primary key ofonly one column, you're already in secondnormal form for that, like imagine it forthis. How can birthday be dependent on onlypart of the primary key which is author IDbecause there's only one you can't you can'tdepend on only part of An individual column.Thermal form in order to get there, you haveto first do first normal form, and secondnormal 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 thisone deals with what's known as a transitivedependency, which is when a column dependsupon a column, which depends upon the primarykey. 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 reviewtable. As you can see, I'm kidding, you can'tsee it's just a square. So we could say, review.So like, this could be on a website, it couldbe, you know, like a shopping website whereyou review a product, or it can be a reviewon a video or anything really, it could bereview on a book,most likely, it'll be on a shopping website,where you buy something, whether it be a bookor anything, or cars or whatever, it doesn'tmatter. But in this, you would have somethinglike a review ID, obviously, which will bethe primary key, you would also likely havewhat that review is, like in a text form.So like a comment. So you would have the actualreview. And then you would have maybe a starrating, or multiple star ratings. So you couldhave a star rating, so maybe one through five,one being the worst five being awesome. Andthen for maybe being like really sort of Okay,you know, and then we might have somethingthat has a starstar, meaning.So that would be like a sort of okay, right.And finally, we might have something likeeither a useror the poster, I'll just put the user becauselikely, you will have some kind of user accounton this website where you can post comments.So this will likely be a user ID. And it willbe a foreign key to a user table. But we'renot going to put that in here right now. Becausethat's unrelated to the actual point. So foreignkey primary key. Now, let's look at righthere we have an issue. This is what's knownas a transitive dependency, because the starmeaning depends upon the star. And a way youcan kind of try and figure out if it dependson it is, okay, well, if we change the staris the star meaning going to change? Well,if we take the star, and we change it froma four to a three, and might go for, it mightgo from really super okay to really average.So the star meaning is going to change. Sothat's a dependency. But then the star actuallydepends upon the actual review ID. Becauseif you change the actual review, that staris able to change. So this is known as a transitivedependency, and it is bad. Now you can alsohave a multiple transitive dependency, whichI don't even know what those are called. Butthat would be where I call them depends upona column, which depends upon a column, whichdepends upon a column, which depends uponthe actual primary key, it can go on forever,for as long as you want. But you don't wantit because it's bad, which lever going toremove. And so to remove this, you take thecolumns that are causing a problem, you movethem to a separate table, and then you createa foreign key and replace of them which referencesthe actual other column. So let's do this.So since these columns right here are theones causing the main problem, we take them,we put them in their own table, we have astar table, within the star table, we're goingto have a star I make. And then we're goingto 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 replacethese columns, let me make this a little prettier.And I just made it five times worse. But we'regonna replace that with a star ID, which isgoing to reference this table over here. Sonow we don't have that problem, because theonly column over here is star ID, we don'thave the star meaning, which was the realcolumn that was causing the problem. Now overhere, we could have if we drew out this table,we would have you know, we could have a starID of three. And that star could be a threestar rating. And it could be meaning it couldmean Average. Now, we're likely to only havefive individual rows in this table. So it'skind 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 theID is, because the ID is just a number usedby the database that has no real world value.So we'd have five rows in here, and then wewould reference them in the reviews table.So we could have, okay, this is too ugly,let's let me just explain it, we can haveone review with the star rating ID of five,which could correlate to a certain ratingfrom this ratings table. That's the main ideaof the third normal form, is to take thosetransitive dependencies, move them to a newtable, create a foreign key to reference thattable. So under view, first normal form ismaking everything atomic second normal formis removing any partial dependencies. Andthen third normal form is removing any transitivedependencies.This video, we will be discussing indexes,the best way to think about it is to takea book, look in the back, there is often anindex here, which basically has a huge listof topics. And it says what pages are on.So you'd be like, oh, wow, that looks funqueries, go to page 2078.Wow,it's right there, I found it so fast, I haveto go search through the entire book to findit. That's an example of an index. It's basicallya list of where certain data points are anotherkind of indexes, you can think of a phonebook,you go to a phonebook, and you search throughthe phonebook, you find someone's name, G,we go to Grandma, and it'll have the phonenumber right there. That's another type ofindex, it's where the data is sorted in away that you can easily find it and the datais right there, versus an index in the back,or it's a list of data pointing to where it'sat.The onewe first discussed, is known as a non clusteredindex. What that means is that the data isnot actually where the index is, the indexis a separate thing. And it basically justsorts the data. And it's basically a pointthat tells you how to get to the data. Sothe way it works in a book is you find thetopic, and it'll tell you where to go 365,for example, go to page 365. That's a nonclustered index. A clustered index is likethe phonebook, where it actually reorganizesthe actual data in a way that's easy to usenon clustered, points to the data clusteredorganizes the actual data. So we can havemultiple non clustered indexes. That's becausea non clustered index is basically just alist of references that point into the data.So we could organize it in different ways.But as for the clustered index, we can onlyhave one of those. That's because it actuallyorganizes the data that way. So think of aphone book, it's organized a to z, or however,you go through there and you find it. That'sa clustered index, it organizes the data.Well, what if I wanted to put the data inby the phone number, so the smallest phonenumber like 0000001, which would be an awfulway to organize a phonebook at the beginning,and then 99999989 at the end, that's an awfulway to design a phonebook, like I just said,but that is another example of a clusteredindex. That's because it actually reorganizesthe data. That's why you can only have oneof those because a phonebook can't be listedA to Z, and also, by the size of the phonenumber, see what I mean. It's conflicting,you can have both at the same time, you canonly have, okay, you can only have, okay,this is sorted by name, or Oh, this is sortedby 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 belike this, to have the actual phone numbersin the back, and it would point to where it'slocated. So at the beginning of the index,it would be like, okay, the phone number that's0000000001 would be on page 743. The phonenumber with 0000000002 is on page 242. Yousee that the actual data isn't organized.By the, the phone numbers, but you can usethat non clustered index that sorts it thatway, and has a reference to the actual datawhere it's located. Now, you guys are probablysick and tired of me rambling about this.So let me just explain how this makes sensein databases. Well, when we're working, we'reworking with a database, often, we will usea certain column for certain things. And thatcolumn will be used frequently. And we wantthat to be like super fast. So we want thedatabase to know how to use it the best way.So we create an index, the database understandsthe index so that way, when you tell it todo something, it can do it faster. Ratherthan having to go through all of the data,which is called a table scan, when you'reworking with a database with millions of rowsthat can take forever. If you have an index,it'll use an index seek, which will basicallyknow where to start searching for that data.That's awesome, because it makes our queriestons faster, and saves a lot of resources.There's downsides to it, though, because whenyou create an index, not only does the actualtable have to update whenever you update it,but you also have to update the indexes. Thinkof like a book, when you add something tothis book, or you change something withinthis book, not only does the actual book contentin the beginning of the book change, but alsowhen you flip to the back, the index mustalso be updated. Otherwise, all this indexinformation is going to be out of date anduseless. So that's a downside to indexes iswhen you update your information, it mighttake a little longer in a database query,we might say something like select the firstname, last name, and the phone number andthe email of the user with the user ID of72. For example, well, I kind of said thatin English, but when you're actually workingwith a database, it might be more like selectfirst name, last name, email, phone number,where user ID equals 72. That would be anexample of a database query. Now the whereclause 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, andthen display their first name, last name,phone number, an email. Well, that where isusing a column that has an index, the primarykey is usually the one that's going to havethe clustered index. So that's the way itactually sorts the data. So now, it doesn'thave to go through the entire entire entireentire table to find the person with the IDof 72. Because imagine if, if it was justrandom data thrown in there with no organization,you have 2 billion rows, right? And the databasehas to go through and find the one individualrow with the user ID of 72. Go to the firstone, nope, not 72. Second one, nope, not 72.There, nope, not 72. And keep going on, onnon non to finally found the person with theuser ID of 72. Basically, that will take everwith a clustered index, it knows well, 72is going to be in this part of the database,it comes after 60 and comes for 80. Becausethat's the way it's sorted. So it's gonnabe like, boom, got done. That is how indexeskind of work. So clustered indexes are goingto be faster and better. But you can onlyhave one, that's usually the primary key.But it doesn't always have to be dependingon the database, and what your goal for thedatabases, the non clustered indexes, whichare 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 hasa list of all the John's and where they'relocated on the database are on the table,I guess, that's also a good thing. But youdon't want to have an index on something you'renot going to be using a lot, because you'lljust have to have another thing updated. Anotherthing 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 takeforever. Another reason for indexes is toincrease the speed of joins. So the way thejoin works is it takes data from one tableand another table and combines it togetherby the primary key and foreign key connection.So think of a users, users and comments. Soa user post a comment. So you'd have a usertable. 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 threepeople over here, we have four comments ona website or review, or it doesn't reallymatter. Well, this one comment is posted byone person and one person can posts multiplecomments, it's a one to many relationship.Well, when we want to output this to wherewe can review the data, we want to use a join.So it's not across multiple tables. Becauseif you're thinking like, this comment wasposted by the user with the user ID of 72,you're not going to know who that is rightoff the top of your head, because that's aforeign key connection. So rather, you wantto replace that 72 with the actual name ofthat 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. Andwe want to output with a join a new tablethat looks more prettier and easier to read.When we do this, we're going to do somethingthat says, the user ID of this call, the userID of this table, is the same as the userID in this table. So if the use if a commenthas the user ID of 72, we know that it's doneby the user with the user ID of 72. That'sthe foreign key connection with which we'vetalked about like some tire series, so I'mnot going to explain that anymore. But nowdown here, in this table, it's going to say,Caleb curry, then oh, put the comment, youknow, join that. Well, this is also done witha where clause, in some cases, for differentdatabases of the terms might be a little different.But basically, that column that you're connectingby, which in this case would be the primarykey needs to be indexed to make it faster.So whenever you're joining a certain column,the two tables, the column you're joiningshould be indexed. You can also have somethingthat's known as a composite index, which isan index that is an index on two or more columns.So think of like a first name, and a lastname, or last name, first name. Now, the thingabout these is when you use the indexes, solet's say you select where first name equalsthis and last name equals that you have todo them both, or it's not going to use thatindex. Now, there are exceptions to this,think it for MySQL, actually, if you orderthem in a certain way, like like this, youput an index on the last name, and then alsothe first name, you can, you can do a whereclause for both the last name and first name.So finding everybody with the last name, curryand the first name, Caleb would be reallyfast. But the way this is set up, you canalso do the leftmost one, too. So you cansay, find the person with the last name, Curry,that's going to work. But in this case, youcouldn't do it with the first name, becauseit's not on the left, that's just the wayMySQL is set up. So different database systemsmight have it set up differently, where youcan search with them individually, or youcan. But most likely, when you make a compositeindex, you should expect to always use thatindex by itself, unless you have it set upto where you make a pretty awesome index thatcan be optimized to do multiple differentqueries that you need to do like if I'm goingto make a ton of queries where I put the lastname as the where, and then I'm going to makea ton of queries where I put last name andfirst name for the where this will be a goodway to set that up. Because I'm making oneindex index for last name, first name thatcan 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 tonsof syntax actually making them because like,you should know by now, this is a databasedesign course, we're just trying to learnhow to design our indexes on our tables, sothat our database works good. So that's somethingyou may have to learn more once you actuallyprogram a database, because you can test thespeed of the actual indexes to see this forspeed optimization to make the database asfast as possible. You can't really do that.If you're only designing a database, you canonly get so far when designing indexes withthe database, but you can get pretty far becauseI mean, obviously, you'll likely be usingthe primary key for where clauses and joins.So that's something that's good do avid clusteredindex on. What a data type is, is when youcreate a column, every single value in thatcolumn has to be of a certain data type. Soyou need to tell that column when you createthe database, what data type it is to be.Also, when you're designing, you usually putthe data types there as Well, so that way,you have an idea of how your database is goingto work. So there's three main categoriesof data types. And the naming conventions,or the names of these data types from onedatabase management system to another databasemanagement system might vary a little bit.But in general, they're organized into threemain kinds. That's date, numeric, and string.So basically, we're going to be going overthese in a little bit of depth, we're notgoing to go super in depth, because rightnow you just need to understand what theseare. And just understand that your databaseapplication might be slightly different fromothers. Let's start from the bottom up justto make things fun string, a string is anycharacters or like letters. So basically,you put these within quotes. Sohey,this is an example of a string. Even a numberwithin quotes can be used as a string, inthis case, this will be used as a string,basically, thethe threedoesn't have any mathematical value unlessit's converted to numeric. Within string,you often have some subcategories, you willhave something that's either char or var char,which does this mean character or variablecharacter. What that means is, it's just likea list of characters, the character or chart,usually is stuff that's set in length, solike a phone number, or a zip code, somethingthat doesn't change, size wise. So basically,with a char, you'll have something like, sharpeight, and every single value within thatchar column is going to be stored as a individualcharacters. Now even if you don't put eightcharacters, let's say you put six, then there'sgoing 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 canhave anywhere from zero up to eight. There'salso often the subcategory or sub data typeof text, which is often used for larger thingssuch 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 stringthat has a number in it that's used differentlythan the numeric data type. The numeric datatype doesn't have quotes. It's just the numbersthat you're using. Just like string, there'soften some subcategories of decimal, and thenfloating point or double decimal works inbase 10. Now, I know we haven't really gottenmuch into basis. And I'm just gonna put dateback up here. So we haven't got there yet.But I thinkit's really important to know and in fact,I've made a lot of videos over binary, hexadecimaland stuff, basically different numbering systemsthat use different bases. Well, decimal usesbase 10 is the number system we're used tocounting, you know, 0123456789, and then itgoes back to 10 1112 1314. It's base 10. That'swhat deaths it means in decimal. Well, there'salso binary. So we have decimal and then wehave binary decimals, base 10, binaries, andbase two. So binary, we only have the optionsof zero, and one. Now, you don't really needto understand how to like calculate in binary.But if you want to know that I do have videosfor that, but just know when we're workingwith decimal is working with a base 10 system.When we're working with floating points. We'reworking with binary, which is stored differently.It's stored in binary, and then you can haveit presented as decimal for math or whatever.So what's really the difference? Do you usedecimal or do you use floating points? Itjust kind of really depends. decimal generallyis more accurate on mathematics to a point.There are problems that can come up with decimal,but you'll just have to research those. Butbasically, this one has some errors binaryit has problems with certain mathematicalequations or numbers. For example,when we want to store1/4, it's stored like this, we have 0.01 Theway this kind of works is we have anythingafter the point that divides the number, basically,in half, so we would have half, but we don'thave, so we have half of a half1/4kind of makes sense. So the place values wouldbe, this one right here would be, we'd havehalf, this place value would be 1/4. And thenanything after that would be 1/8. And anythingafter that would be 116. So right now we'rewhere we have it, we would take one half,multiply it by zero, to get zero, becausewe 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 understandhow it works. But when we have a number suchaspoint one,and decimal, that is basically impossibleto store in binary, simply because we're workingin a base two system. And we can't get topoint one very easily. Because 1/8, that doesn'tquite cut it, you have to basically have acombination of a bunch of different fractionsto 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 likethat, you would if it needs to have accuracy,you would have to have it stored as a decimal,which stores it differently. Sorry, if itcompletely overwhelmed you with that numberstuff. And if it did, don't even worry aboutit. That was a little too fast. You need itslower, please check out my introduction tobinary video. It's a super long video whereI basically explain it step by step. So youunderstand. And then I have a couple othervideos adding and subtracting. And also fractions.in binary, you will also have an integer type,which will only store numbers that don't haveany decimals after them. So only whole numberslike 512 28, negative 52, so forth with numbers,we also have to worry about whether they areunsigned or signed. If they are unsigned numbers,that means that they can't be negative. Ifthey're signed, that means it can be positiveor negative. probably wondering why wouldyou ever do that? Well, if you're workingwith 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 bepositive or negative, the range of potentialvalues stays the same. But since you includenegatives, the maximum value is different.It's not as high think about if you have arange from zero to 1000. But then you includenegatives, now you have from negative 500to 500. That is going to be told with thedata type. So you might say something like,double, unsigned, that would work as a number.That's enough for numbers. I don't want totalk about this anymore. Let's talk aboutdates, dates, well, there's a couple differentthings you need to know about dates, there'sdate, then there's time, there's date, time,and then there's timestamp. So let's talkabout date time first, because it makes sensethe best. It basically just stores a datesuch as you know, oh two for February, thenyou might have a colon you might have 22 forthe date and 22nd and then a colon maybe 1998for 1998. And the year, then you have thetime, which could be whatever it could be12 3022, which would be the seconds and youmight even be would have some millisecondsor microseconds. That's how you would storedate time. The way it actually looks mightvary from database management system to databasemanagement system. Some might have colonsbetween the numbers, some some might havehyphens or commas or whatever, doesn't reallymatter. I don't need to think about that.Right now, I just want you to think aboutthe date being a date. And the time beinga time,date, time being the combination of a dateand a time. That's pretty much that for dates.The only other thing is the timestamp. Thetimestamp is an exact moment in time that'srecorded to establish when something was createdor done. It can be in milliseconds or it canbe in the month, the day and then the secondhour and then the seconds and then the milliseconds.Or it can just be a number of seconds, soforth. It varies. But anyways, that's goingto give you a number when something was done.It's usually for something like when an accountwas created. Either when a transaction wasdone or when something happened, when youupdate a column, that timestamp will oftenupdate. And that's going to keep it to likewhen that row was entered into the database.So if you enter a new row, and one of thecolumns is a timestamp, it's going to giveyou a value as soon as you submit that, andit's put into the database, and timestampscreated. And it says, Okay, this row was enteredat 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 thoseclassifications have different data typesfor each one. But in general, as long as youknow, like, the gist of what is what, you'llbe fine, you don't have to worry too much.Because when you get an actually programminga database, you will have more specifics onwhat data types to use. So this video, we'llbe talking about joins. And in fact, the nextwhole group of videos will be about joins.So it's going to be a lot. Now, joins area complex subject there, they can be confusingand tricky, and very syntactical, I don'tknow if that's a word. But basically, thesyntax from relational database managementto another relational database managementsystem can be confusing and complex. But ratherthan diving in and just learning all thatstuff, we're going to be talking about theconcepts of what the types of joins are. AndI know you might not even know what a joinis yet. So you're probably thinking, I don'tknow what you're talking about. Well, that'swhat we're talking about today. So thank youfor joining me, let's begin. So a join iswhen you take something that's structuredin a database in a confusing, non user friendlyway, and you're presenting it in a user friendlyway. Now this is done so that way, we canstore our data in the database in a way that'sthe best structured, to where we protect ourdata integrity, and it's normalized and separatedacross tables with the proper relationshipsand data types and keys and indexes and everything.But when we actually want to present thatdata, 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 justviews those calculations after the databasedoes all the work with the analyzation. Well,that end result is often in a way, it looksin a way that is organized and structured,it makes sense to us. That's the purpose ofa join. So it takes a mess, and puts it outin a way that looks beautiful. And this canbe 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 threedifferent tables, with relationships betweenthe tables, we all get the specifics of whatkind of tables but basically, a join is goingto take all of this.And it's going to present us with a generatedtable, that is more pretty. It's more beautiful,and more structured. And it makes more sense.Now, the way this is done, is using foreignkey primary key connections. So when we havethese relationships between these tables,where I drew these arrows here, that representsa foreign key connection, that referencesa primary key. So one of these tables is theparent and one of them's the child, and wewant the end result and combine them togetherinto one. Now, when you do this, these columnsthat are connected are going to be indexedto make it much faster. Now, when you jointables, often you will replace names of thingswith more user friendly names. So for example,we could have you know, let's say we had acomment on a website. And it says, Wow,thenand then this is posted by Caleb. Well, youcan see that if you have a username here,it might actually be a user ID in a commenttable. So for example, we can say, This appearsthe comment table. This is the user table.And there is a foreign key from the user IDreferencing 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 withthe actual username. So rather than havingyou know we can have it to where it's likeUser 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 theuser table, and put that username insteadof the user ID. That is how a join works conceptually.That's another important thing. All this,think of it conceptually, these videos aregoing to be helping you understand the conceptsof the different kinds of joins. And throughoutit, I'll be giving you some examples of howyou would type that in SQL, maybe, but I'mnot going to be getting into the specifics.That's because joins are done differentlyfrom database management system to databasemanagement system. So you just need to knowis how the joins work, and the expected results.And then when you start working with MySQL,or SQL Server, or Oracle database or whateverelse, then you can just figure out how todo their joins, you will already know whatthe results are going to be because you understandthe joints. And then you just type it out.And boom, you got to join there. Now there'sone other thing to know that's important,is all of this is data manipulation. Mostof what we've been talking about before isdata definition. So that was in basicallyDDL. But now we're going on to DML. So DDLdata Definition Language, and data manipulation,language, these are two parts of SQL, we allwe all talked about this and the older videos,but we're on like, way farther now. So youguys might have forgot this. But this is importantto know. Because now we already have the definition,the structure of the database, the databasemight already be completely designed and structuredwith the columns and the rows. But now, we'remanipulating that data to look a certain way.So by doing joins, you're not actually changingthe structure of the database, you are changingthe presentation of that database, that isextremely important to know. And you may bewondering, 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 structureof the database, and then the database administratoror the software engineer person, or whoevercan go in there and use my database that Idesigned and created. And they can do allthe data manipulation stuff to fit their application,right? Well, maybe, but not likely. That'sbecause the joins are also very importantto understand. Because when you design yourdatabase, you need to think of it in a userfriendly way. And then normalize it, makeit confusing, and then present it in a userfriendly way. It's kind of confusing, becauseyou start with let's say you are really badat database design. And you're structuringa table, and you have it to where it's likecomments comment table. And within this, youhave 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 someIDs in there. Basically, you don't know whatyou're doing. And you put the username, whatis Caleb See?Comment lame.There got another one from Caleb See? Cool.Caleb see off. When you look at this, andyou show it to your grandma, she understandsit. She's like, okay, she might not understandit, but she gets if you explained it, shemight. Caleb cposted the comment lane. Caleb c also postedthe comment cool. Caleb also posted the commentupscales. He also posted the comment, awesome.But this isn't the best way to do this becausewe learned about data integrity, and alsorepeating data because now we have this inhere three times. So instead, we use a userID and get rid of Caleb C. And we get ridof 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 overhere we have a user table with the user IDcolumn, which has all have our user IDs foreach individual person. So we started offwith 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 upand normalize it. So that way, we have multipletables. So we have this table. And then wehave this table over here. But that's notthe only thing we do. The next thing we haveto do is you have to take up those piecesof the puzzle, and put them back togetherin a join to get the final result. So we basicallywant to replicate the original table we hadhere by using a join of our normalized database,and recreating it. So what we're going todo is we're going to take the user ID column,and we're going to take the user ID, you guyssee that all all good, it's everything. ThisThis 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 connectedto Caleb See,using a join,we are going to connect these two tables bybasically using this foreign key which pointsto seven and finding the associated usernamewith that user ID. And our join will looklike this.username,comment. And the name of the table can bewhat mean whatever you want, it can be generatedtable. Or it doesn't even need a name, ifyou're just putting it on a website or something.And then under the username column, it'llbe Caleb See, and then Caleb See,and then Caleb see.And then under the comment, you're going tohave lame and cool and awesome. But this wasn'tjust like this to begin with, we got the usernamefrom this table. And we got the comment fromthis table. So not only do you have to designthe structure of the database, but you haveto structure the joins, to basically put ourdatabase back into simple language or grandmareadable tables. Okay? That that's basicallywhat we want to do. So let's say you get ajob, or you're working for a developer orsomething, and they say, okay, on my website,I want a table on this Users page, where Ican sign in with my username, password, andI can see their username, their password,their email, their date, they signed up andeverything like that. And if I want, I cango in there and edit it. That way. They don'thave to go in and type SQL, you're you'regoing to have that programmed in the in theactual application to send that SQL. But weneed to structure the tables, we need to makea join, that's going to be able to combinethose all to one pretty table, which that'sthe job of the database designer, likely.I mean, if the applications or the databaseis huge, you might have someone who does the,the designing, and then someone who does theimplementation, and then someone who doesthe joins and then someone who does this otherjunk. But I mean, if you're just working onyour own, and you got to do everything, you'realso gonna have to design the joints.And I highly, highly, highlyrecommend you do that before you go in andprogramming your application. And then you'relike, Oh, crap, I don't want to do this, orOh, my database is structured wrong. I wantto do this all beforehand. Don't rush intothings because then you're going to be like,dude, dawg, my application ain't working.Right. And the reason is, because you didn'ttake the time to structure your database theright way, and develop yourjoins.Okay, once you got that, you're good to go.And yeah, so there are a couple differentkinds of joins. There's Inner Joins, outerjoins, cross joins, unions, there's all kindsof different things that we're going to betalking about. Okay, so, when energyne. Theway an inner join works, is it's going totake one table and another table. And thenwherever there is rows from this table andthis table that are connected, it's goingto present them in a new generated table.So let's just break this down and see howit works. Let's go with an example. Becauseyou gotta use examples. We're gonna have acustomer table We're also going to have acard table. So think of like a bank, for example,you're going to have a customer table whereyou're going to put all your customers inthere. And then you have a card table whereyou're going to put every single card that'sbeen given out, or will be given out to peoplewho have credit cards, where you could maketo where you can even include debit cardsin there, but we'll just worry about creditcards for now. So basically, we're going todraw this out.And we're notgoing to have it required that a card hasa customer, a customer has a card. So neitherof these columns are going to be labeled asnot null. So basically, the card does notneed to have a customer. And a customer doesnot need to have a card. So we'd have onetable over here.And another table over here.And generally this will be designed as a oneto many relationship, because one person canhave many cards. And usually one card is ownedby one person, you could design it as a manyto many relationship where you can join acard or multiple people can own a card. Butyeah, 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 havesomething like of course, we have an ID forthe customer. So we're gonna have the customerID, you know, their first name, last name,their address, their date of birth, all theirpersonal information that we need for theirdata for the bank. That's where that's goingto go. Over here, we're going to have a cardID. And then we're also going to have a customerID. And because this is a one to many relationship,we have the child and the parent. If thisis a one to one relationship, we could haveit to where there is also a card ID and thecustomer table if we wanted. But I'm not goingto do that. And we can get into one to onerelationship design best practices in anothervideo. But for now, the customer ID is goingto reference this customer ID. and here wecan also have more information about the car,we could have the the total amount or thelimit max amount, I guess I can say, max amount,you can have monthly bill. And because thisis a really simplified example, we don't reallyhave to worry about other tables such as,you know, bills and invoices, I mean, andall that other junk, we're just keeping itsimple here. And let's say this is how westructured our database. And we want to makeit more user friendly with a join to wherewe join information across the table. Becausewe don't want to have to have the customerID for the card. So we create a join. Andwe make this new generated table. I don'tknow why I made that like circle, but andwithin here, we could say we could say it'sthe card customer. For example, it doesn'treally matter what we give the title of theactual table. What we're going to do in hereis what we decide which columns we want totake. Well, the first thing we we always startwith the ID because that's how things aregoing to be joined. And we don't necessarilyhave to return that Id within our table. Butwe need to think about it because that's howwe connect the tables. So generally the theID is just for the databases sake, it's usuallynot something you use outside of the database,it doesn't have any real world value. So weusually don't need to record it in the joinfor a view the application or whatever we'reusing this for. Now, if you're the databaseadministrator and you want some more presentabletables like this, but you want to keep theIDS in there, that's fine too. But we're notgoing to we're not going to return the customerID. There's no need to it's just a number.But since it's in both tables, if we did joinit down here, it would only be in one column.So we would have you know, cost a customerID. And that's going to take the combinationof these. We're gonna have the customer idlike let's say 62 and then card. I Be likeseven and then customer ID 62 and currentID eight. So that's kind of how that wouldwork. But we're not going to return those.So what we're going to do is we're going toreturn just the first name, last name. Andwe're also going to return the Let's add anothercolumn amount paid. So basically, it's sayinglike how far they paid off their credit cardbill, let's say you borrowed 10,000, and youowe 6000, you've paid back 4000. So far, justto see how far people are getting to payingoff their bills. And like I said, this isvery simplified example, you can make it towhere it gives a percentage. And you couldhave more tables for bills and invoices andall that junk. But just so you guys understandthe joint concept, it works the same for basicallyany columns you use. So within here, we couldhave first name, last name, and the amountpaid. These are our columns within our table.I'm just gonna open this up, so we can writesome. And we could say that we have individualpeople we could say, okay, okay. And thenhis amount paid on one of his cards is 2200.And we also have Caleb curry. And the amountpaid is 720. And, if you wanted, you couldgo in and edit the join adding maximum out.So that's basically, or we could have a columnon here such asamount owed.And we'd have the hat in here too. And thatwould basically say, how much they paid outof how much so look like. Then here, wegot amount of,like 3000, we're just basically saying 2200out of 3000. If you wanted to do that, youcould do that. But for simplicity sake, let'skeep it like this. And then we could haveJimmy john. And the amount paid could be 2800,and so forth. You can make more rows if youwanted. Now you're wondering, okay, what'san inner join, we talked about that earlier?Well, basically, an inner join is going totake only the rows that either a card hasa customer and a customer has a card wherethey intersect. So this new table is joinedtable is going to eliminate any customersthat do not have a card. And it's also goingto eliminate any cardsthat do not have a customer. So I'm goingto erase this, I'm just going to draw a littleillustrationto show you how this works. Now, you can findmore pictures like this online, which I'lllink 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 cardtable. And this part in the middle. This iskind of like the join table or where theyintersect. So here are the customers thathave a card. And here are the cards that havea customer. So within here, this is goingto contain all of the rows that have a customerthat also owns a card or a card and is ownedby a customer. So if you have a card withno customer, it will stay out of the end result.And if you have a customer with no card, it'llstay out of the end result. So this is whatyou can think of it as all of these thingswithin this part of the Venn diagram or whatever,that's going to be returned. Now, there'sdifferent kinds of joins where it'll onlywhere it will only return, for example, thisside or this side. But that's where we'regoing to be getting in, in future videos.So if you want to do a left join, if you wantto a left join and go on here, right joinand go over here. But an inner join is justthe intersection of both of the tables. Ihave this habit of just like wiping it offwith my hands and then my hands are like darkgreen. 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 becauseremember we are in we're using data manipulationlanguage which is done through selects. Soit would be something like this. Now to warnyou, the syntax is going to vary a lot fromrelational database management system fromrelational database management system, butthe concept stays the same. So you just gotto replace a couple things, insert a couplethings, boom, Bada, bang, you got a new join.So we're going to say select. And then whatevercomes after the Select, I'm going to switchover. So everything that comes after the Selectis going to be the columns that we want inthe joined, generated table. So in the lastexample, we had first name, and then we alsohad the last name. And we also had, what wasit amount paid? I don't remember. But somethinglike that. So that's going to be the columnswe 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 multipletables. Sohow do we know which onewe're going to use, or we're going to putthe left one first, which, in our drawing,we had the user or the customer, and thenwe had the card. So we're going to pull itfrom customer. And then you're going to doa join and join the other table to the customer.So we're going to say, enter join. And thenyou're going to say the other table, whichwas card. Now what you're going to do is sayhow are these tables connected? So the waywe have them connected, or the way we wantto join these tables, is by the foreign keyconnecting to the primary key. And the wayyou signify that in syntax would be with thislittle statement here on which is basicallysaying what are we joining it by. And you'regoing to say, customer dot customer ID, andwhat that what that.is is basically sayingthe first part of this is the table. The secondpart is the column. The reason we have todo that is because both tables have a customerID. And then you're just going to connectthat to the other table by saying equals carddot customer.Id.Now that's basically all you have to do forthis, it's going to connect, if you look atthe syntax, it's going to connect and geta different marker here. Colors makes thingsso useful forus.Okay, we've got the customer joined with thecard. Those are the tables. And then we'regoing to say what columns we want to returnand the new join. And that's first name, lastname and amount paid. And then we have sayhow they're related. That's the customer IDof the customer table is equal to the customerIDof the card table. And that's how you getyour join results. So the end result wouldof course look like this. And you're goingto have first name, last name,and then mountpaid.There you go.So that is how an inner join works. The nextthing we're going to be talking about is leftjoins and right joins and all the other coolkinds of joins. Main thing though, don't worrytoo much about the syntax. If it's overwhelming,just worry about the concept. Understand thatif you do an inner join, it's going to excludeall of the rows from either the left or theright table that are in both tables, it hasto be in both tables in order for it to work.That's the most important thing to understandwhen it comes to these joints. Because ifyou understand that, all you got to do literallyis 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 syntaxcode, replace the columns they gave you withthe columns D one and the tables with thetables that you want. It's really simple.So you don't really need to like yeah, obviouslyyou should learn this in the long run. Butif you're new to this, don't worry about divingin studying that for 20 years. Worry aboutthe concepts because once you get the concepts,it's easier to pick up on this junk. So inthis video we'll be discussing Inner Joinscrossed multiple tables. So you're probablywondering, 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 multipletables with more tables? So basically, we'regoing to have a join over three or more tablestrying to find the green marker. There itis. Okay, how does this work? Well, let'sjust draw some boxes to kind of get an ideaof how this might look. Okay, so you mightbe like, okay, cool, we figured out the InnerJoins and how those work. But how do theywork when you have three tables? Let's, let'sfind out. Here we have three tables. And whenwe do an inner join, let's say in the lastexample, we join these two tables, we hadsome kind of join condition, which was somethinglike customer dot, customer ID equals cardcustomer ID, you know, well, it doesn't alwayshave to be that it just depends on what you'redoing with it. But basically, you're goingto have a join condition right here. And you'realso going to have a join condition righthere, ifyou want to join inanother table, these tables don't necessarilyhave to be connected, like a many to manyrelationship, because when we design manyto many relationships, it looked like thiswith like the intermediary table, it's notalways the case, for example, this table couldhave relationship with this table. And thistable could have relationship with this table.But this table doesn't necessarily have tohave a relationship with this table. Thinkof it like this, when you do the join on thesetwo tables, you're going to get a result set.Right, then if you want to know what yournext results are going to bewhen you have this table and think of joiningthese, and you're going to get a final resultset. So if you do an inner join across multipletables,the end result set is going to be smallerthan this, because you think about it. Ifyou join these two tables, you get a resultset. But now, you have to take all of theserows, and you're only going to display theones that meet the requirements of this joincondition, which is going to decrease thesize, 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 beforewith the cards and the customer. But we'regoing to add a new table, right. So we'rejust going to draw a style. But this is thecustomer table. And we had a customer ID,a first name, last name, you can also haveany other junk you want to store about thecustomer in there. Then we have a card tableor the card, card ID and then the customerID and then the like maximum out or whateverelse you want to store about the car, andthe bill or whatever the price or the interestrate. Now, let's throw in another table inthis equation, let's throw another table intothis equation. It's going to be a card typetable. So you know when you get like a creditcard, because all you college students arelike, yeah, I need pizza. Well, there's thecard is a type such as Visa, MasterCard, orwhatever else American Express. Well, that'skind of like the card type. Generally, a bankwould just give out like one card type. Butwe could say that they can, like we couldjust say what kind of card type the card inthis table is. Now when you structure it thatway, how many different card types are there,four or five main ones, Visa, MasterCard,American Express, maybe two other ones thatare 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 ofrepeating data. And then for example, withMasterCard decides to change their name orthey get bought out by somebody else. Andall the cards now are named ultra card, thenthat's going to cause problems because allof the you're gonna have, you're gonna havea MasterCard and then ultra card, it's gonnabe confusing. So what you can do is use alookup table which we discussed in a longvideo ago. And that's just going to be basicallya reference to another table with all thepossible options. And that way if you needto add a new option, you can do that easilyand then just reference that or if you needto update an option, or if you want to putextra information about that option. For example,if MasterCard has a standard fee or somethingyou could put that within the card type table,rather than within the card table. Becauseif you have the card table, like, Look, ifyou have card, ID, and then you have cardtype, and then you have something about thatcard type. So card type interest or something.Well, this is a transitive dependency, whichis a violation of the normal forms, I believe,second normal form. Don't forget, third normalform, I'm sorry. Anyways, that's not good.So we want to take these and put them in anew table. So it lookup table is a defenseagainst problems like that. And it also reducesintegrity problems. And it can also reducerepeating values. So now let's design this,we're going to have a lookup table, becausecard type. And this is going to have a cardtype ID, and then the card type, which isgoing to be like the name of it. And thenyou could have any other rows about the cardtype in there.And then in the card,we're going to reference that with the foreignkey. So it says looking up the values throughthe through the lookup table. So we're goingto have cardtypeID, that is a foreign key. Now let's drawthese connections. Customer ID is a foreignkey to the customer. And the card type IDis a foreign key to the card type. So youcan see we have all this information spreadout over three tables. And we want to addage, we want to join these and create a newgenerated table that contains informationfrom all three, we want it to be an innerjoin in this case, but you can do the samething with outer joins and other types ofjoins. We'll be discussing those soon. Butfor this, we want it to look like this, thisis what we want our generated view or a newtable to look like, we want to select stuffabout the customer, just add some columnsin there just to put them in here, we canhave the first name, last name, email, phone,now paid. And then we could also have thecard type right here, it's more, because we'regonna do some cool while we're completelyoff topic just for a second. I always likedrew comics as a kid and I always like drewa bubble around like either a thought or atalk. And then I tried to fit all the wordsinto that and never worked. And then my masterdrawing friend was like, dude, draw the wordsfirst and then draw a bubble around, it waslike the best idea ever. But I still don'tdo it. I mean, I always have to erase thesize of my database tables, fit more junkin there should always write the words firstand then draw a square around them. But thiswork, these are all from this table. Thisis from this table, the card type is fromthis table. It's a join across three tables.And you could of course add more. Now, thejoin conditions, well, it would be for this,the customer ID is the same as the customerID. And the card type ID is the same as thecard type ID. So this table versus going toexclude here, I'll just write out what it'sgoing to exclude. This isn't part of the table,but I'm just putting it here, it's going toexclude customers with no card, or cards haveno customers, that's going to be the firstjoint condition, because we're going fromhere.And thenthe second join condition, it's going to removecards with no card type. And also card typeswith no cards. And that means basically ifyou have MasterCard as a card type, and youhaven't created any card rows that also havethe foreign key that references MasterCard,or whatever I said, then that's not goingto be included because there would be nothingto put with it. And just say MasterCard, nonormal normal. That's because no cards haveit 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 resultis going to basically get smaller and smallerand smaller and smaller for every single tableyou add. Now, a couple other things to thinkabout here. It has to do with things beingno or not No. Okay, let's talk about the waywe have a set up is the customers. They canhave a card but they don't have have to havea cart. That's because there's no requiredcolumn in here that says you have to havea card ID. So you could have a customer thathas multiple cards, for example. We also havea card table. And well, the way we have setup the card doesn't necessarily have to havea customer. If you want to change that, youcould take this customer ID right here. Andyou could make it not not, that is going tochange the end result. Because now, everysingle card is going to have to have a customer.That means cards with no customers. Although,functionally, it's removing all the cardswith no customers, every single card has acustomer. So the end result doesn't eliminatecards with no customers. Functionally it does,though, because that's what the inner joindoes. But since there's nothing to remove,it doesn't do anything. So you can kind ofthink of it as not doing this, even thoughfunctionally does remember that it's the difference.All right. Now, we also have it where thecard, I would say should have a card ID ora card type ID, I should say, because almostevery card you're going to get ever is goingto have a card type, right? Unless for somereason they wouldn't, you'd likely have thatas not know. So functionally, this is goingto remove cards without card types. But sinceit's labeled as not null, every single cardis going to have a card type. Therefore,in the end result,it doesn't really remove cards with no cardtype. Because every card has a card type.So there's nothing to remove.Now card type,there might be a possibility, you're offeringa new card type such as, let's say, so I can'tthink of any cool names like legit card type12. And like a new company comes out in there,you get a legit card type 12 card and allthrough your new bank account. And that mightyou when you first get that there might notbe a card that has been issued that card typeyet. So in the end result, you're only reallyremoving the customers with no cards. AndI guess, the card types with no cards,I'm going to keep that. So that's going tostay so these two are going to be removed.And this is the end resulthere. We're going to keep card types of nocards and customers with no cards. That wasa big one man with our hearts. We talked abouthim last year, but never actually talked aboutthe join conditions very much. So I'm goingto go over a new example in this video. Let'sdo this. So we're gonna have a user table,not a customer table user table. And thenwe're going to have a comment table and avideo table. So think of like a video sharingwebsite like that one called, starts withthe youknow, but anyways, we havea 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 writethe name of the table. I'm not gonna reallywrite any comments in here, just low on space.So got the user got the comment. And thenwe got the video. So the the only way theuser has any connection with the video isthrough the comment table because a user posta comment on a video. So it makes sense, hopefullydoes. I don't even know ask you still, I'mtrying to stop screaming crazy. What are thejoint conditions for this, if we want to doan inner join, let's discuss the conditionsand also the results we should expect. Okay,so let's just define the keys that are goingto connect these tables. So we have a userID. And then we're gonna have a common ID,and then a user ID, and then a video ID. Andthen this video ID will be referenced in thistable by a video ID. Okay, now, if you thinkof this as a many to many relationship betweenuser and the video, you could potentiallyget rid of the comment ID and just use thecombination of user ID and video ID. But thatis not the best way to do it. One user couldpost multiple comments on one video. So yousee, like, if we had this common table, wecould have user ID and then we could havevideo ID and then we could have a Comment,we have the user ID with seven video ID was106. And the comment was lame, and then theuser ID of seven, video ID of 106 could beJK. So this is a bad idea, you wouldn't wantto use those combined. Unless you are workingon some kind of thing where you can only leavelike one review on a product to say insteadof just use a review product or user review,whatever. In that case, you could use userID combined with the product ID or the videoID to allow that, but I'm going to use thecomment ID in this. And this is going to bea surrogate ID that's going to be used asthe primary key. And these are only goingto be foreign keys, the user ID and the videoID, they're only going to be foreign keys,and they're not going to be part of a compoundsare a compound key in any sort of way, theyare just another column, that's a foreignkey referencing a table to see you guys understandthe structure of how I set this up. Alright,now there's going to be a join condition righthere, which basically a joint condition iswhat columns are in both tables, right? Youdon't always have to say user ID equals userID, or customer ID equals customer ID or commentequals common ID, you can do other stuff withlike greater than less than and all thosefancy things, but we're just going to be usingequals because it's the most common one andthe easiest to understand the one here, it'sgoing to be the user ID. So we're gonna haveuser dot user ID is equal to comment dot userID. This is the first joint condition. Nowfor this, we're gonna have a second join conditionbetween these two. And the only thing we canuse is the video ID because it's the onlything in both tables. So we're basically goingto havecomment,dot video ID equals video, dot video ID. Wegot to say what table there and because they'rein multiple tables, so we have to use thedot syntax, table dot column equal to table.com.I know you guys probably can't read that Iwrote it so small, but it's okay. As longas you understand this is the john, this isthe joint condition for these two, and thisis the joint condition for these two. Whenyou join these, you're going to join themby those columns. And now what about not knowingno columns? What are they going to be returnedas? Well, the way we have a structured usercan comment on as many videos as they want.So they can be in the comment table multipletimes, so got one too many there. Now, a commentcan only be posted by one user, and can onlybe posted on one video, because each individualcomment is going to be a separate row in thecomment table. So for video ID, we're goingto have that set to not know. And also fora user ID, we're going to have that set tonot know that's because there should neverbe a comment that doesn't exist on a video,there should never be a comment on a videothat wasn't posted by a user. Right? Now forvideo. We don't necessarily have to have commentson it. So there's nothing we need to worryabout that. Just like a user, we don't haveto worry about having to have a comment. Sothe only time we need to put not know is onthese two columns. To make sure the commentsexist with everything they need. You see,the user ID would be not normal. That's becauseevery user is a user and every video is avideo. But we don't have to say every userhas to comment that would make it impractical.So that's the setup for the nominal and therelationship types. So then when we do aninner join, in this case, what are we goingto get? Well, I just want to think about thatfor a little so we can kind of understandwhat our end result is going to be. So whatdo we want? Well, we want a table that lookslike this up a little bit. We want a tablethat looks like this. And that's because wewouldn't want it any other shape, right?Um, Steven, but basically,we're going to takethe user with the user ID and likely we'llhave more information about them, such astheir username. So if you wanted, you couldalso include that in the table, which wouldlikely be done from the beginning, becauseyou'll likely do your joins and stuff afterthat. Make sure you put your user name inthere. You don't have to add that in therebecause then half your users won't have ausername and half of them will which isn'tgood. So username from the beginning, likelyinclude your username, and then will likelyinclude the comment. So we will need to includethe actual comment in the comment table. Thereason I didn't put these in there earlierbecause I just wanted to think about the keysfor now. But now we can put those in therethat says we're designing this right. Andthen we also want the video title. Maybe alink. But we'll just stick with the titlefor now. So these are the three columns thatwe want to include. And we could have setit up in a table, and each individual rowis going to be a new comment. Well, let'ssee, let's think about what can be excludedfrom this. So then we can decide which onesactually will be excluded? Well, let's justask questions, can a user be excluded? Well,if we're using an inner join, we're only goingto return users that posted a comment on avideo. So yes, any user that did not posta comment will not be included. Now, whatabout videos? Will videos be excluded? orsensitive INNER JOIN, we're not going to includeanyvideos 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 tosay that it can be excluded, users can beexcluded. Now, can comments be excluded? Well,let's think about each individual key, wehave the user or the foreign keys, user ID,which is labeled as not know. So that meansevery single comment is going to have a userand also, the video ID is not. And also everysingle comment is going to have a video. soin this situation, all of the comments willbe returned. So we can't remove those. Sowithin here, every single comment is goingto be included. So if we had 10 comments,we're going to have them labeled one through10, we're actually going to label them butwe would have all 10 of them there. And thenonly some users would show up here. And alltheir comments, all the comments would showup in this column. So it'd be like, like,it'd be like, like, black and lame. And thenthe video title, only some videos are gonnabe included here.It's ascrap chance. That's how it's gonna work.And let's just see how it goes. So yeah. Let'ssee this in action. Here I have some snapshotsof some tables I created. And I'll just beillustrating this and I'll be drawing on thescreen as needed, and have one of them walkthem tablets. And if I am drawing all overthe place, it's because I haven't installedthe software, not because I can't write betterthan a kindergartener. Alright, so let's begin.Here we have what would be like a user tableor something. We have the user ID email usernameand the password, which is like just encrypted,basically. And this is going to be joinedwith the comment table over here. I mean,sorry, that's the video table here. Me. Thisis the comment table. Alright, so here, wegot each comment, and foreign keys to theuser ID and the video ID as well as the actualcomment. And then we have the video table.Video table has the title of the video, andalso the description, which would go likeunder the video or on the side or appendedto the title, whatever you want to do foryour website. And this is just what the videois about, right. And as you can see, I'm advertisingmy database design course, which is prettyawesome, right? And what we want to do iswe just want to join these. So we have theusername, the title, and the comment, allfrom three different tables. So the usernamecomes from the user table right here. Andthen the title comes from the video titleright here. And then finally, the commentcomes from the common table right here. Solet's go back to the join table. What we wantto do is, is if we're using let's say thecomment table, we want to replace the userID with the username and the video ID withthe title So let's look at the comment table,we want to replace all these numbers in theuser ID column with the username, which meanswe have to reference the user table. And thenthe video ID, we want to replace with thevideo name, which means we'll have to referencethe 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 howthis works. So yeah, this is how it works,the username from the user table, the titlefrom the video table and the comment fromthe comment table. Now let's go through howthe join looks. I have all the tables here.Now, it's kind of small. So sorry, if youcan't see well, but we have the join tableright here. This is the joint table. That'sthe join. Then we have the comment, the user,and we have the video. And we're going tobasically just borrow some columns. So thetitle that's going to comefromthe video, so I'm just going to go over hereto do to do, it's going to come from overhere. And then the username is going to comefrom appear. And then finally we have thecomment. And the comment is going to comefrom right over here. You can even see thatprobably not. Okay. So there's a couple thingsthough, not all of the columns of values areincluded. So not every single row. That'sbecause this is an inner join. Now, if youlook at the username, we've got all guidepizza, let's follow this user. So let's goto the user table. They got all sky pizza,right there. Then we got if we keep goingdown this table, we got ha 12 which of courseis he's my mouse now right there. Then wegot Pablo is all dot dot, which is Paulo wasawesome. Then we got milk and COO, which ismilk and cookies. And then we got yellow swag,which is yellow swag, hashtagyolo swag.And then we got that I don't even know whatthat says cifs which is this person, right?Best. Best speller in the world. Okay, I madethese A while ago. So you can see these twopeople, though they're not included. That'sbecause it's an inner join, and they are notpart of the end result. Now let's look atthe video. So we got the videos, we got databasedesign 1234567. where's where's eight, thoughI don't see an eight on here. Well, you cansee eight. On the video side right here, it'snot included. So eight is not included. Basically,we're taking different columns from differenttables, but not every single row is included.That's because it's they don't meet the qualificationsfor the inner join. So the syntax or somethinglike this would be like this. Said the Selectand then the username, title and the comment.These are all from different three tables.But it might vary on how it looks for yourdatabase management system. But it looks somethinglike this. And then from the user, commentin video table and we just do the inner joininner join the comment by the user ID andthe comment user ID and then the inner joina video by the video video ID being the sameas the comment video ID. So what's the differencebetween an inner join and an outer join, that'swhat we will be discussing in this entirevideo. It's going to be about 20 videos.So hold on a notch. Itwon't take too long. Once you understand whatan inner join is picking up outer joins andthe other junk is like super easy. Okay, kindof how like once you learn a one programminglanguage, the second one is only somewhatless hard. Right? See I have some junk writtenbehind me. Let's look at it. Alright, so wehave what's known as a join condition. Thatis at the end of the joint statement. Do youremember doing this? I can't hear you. I'mjust kidding. Okay, so we got customer customerID and card customer ID. That means there'stwo tables, the customer table and the cardtable. And then both of these have a columncustomer ID to basically just simple dot syntaxThe table dot column. And this is making anassociation. This is saying that we want tojoin two tables. And the rows that need tobe connected, are connected by the columnof the customer IDand customer ID.So the two columns here, this is going tomake a connection. And our join is going tobuild is going to be built upon that so weknow where to connect things. So the way thiswill work with an inner join me to show Yeah,we got, let me just redraw those two tablesreal quick, we have a customer. And then wehave the card. So this is like a bank, andpeople can get credit cards. And a persondoesn't necessarily have to have a creditcard because they can go to that bank withouthaving a credit card. And also, a card doesn'tnecessarily have to have a current owner becauseit could be a disabled card, or it could bea card that hasn't yet been activated, orsomething else could happen to where a persondoesn't own that card. So there's no requiredcolumns for this. Now, when we join these,the join table is going to only include customerswho own a card, that's going to be the endresult. That is with a inner join. So we havethe customer card, join table of card andthe customer that is exclusive, pushing awayallof the customers with no cards, and all ofthe cards without an associated customer whoowns that card. The Outer Join works a littledifferently, in that it's still going to returnall of the rows of from one of these tables,you can decide which one whether or not, theyhave an associated entity on the other side.So that joint statement comes in here, it'simportant. So we had it to where it was thecustomer ID, I'm just going to put C for short.If we have it, where we have it what's knownas 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 fromthe left table. The right one, though, isstill going to be a Bane that join statement.So the joint statement on the right out wasa cause customer. So with this left join,all of the rows are going to ignore this onthe left side. So it's going to return backevery single row. On the right side, though,it's going to only return. The ones that havea customer ID that has a customer ID havethe same value within the customer table.So for example, we could have a card withthe ID, the customer ID ofseven. Sothat's customer ID not card ID. So that'sreferring to the person who actually ownsthe car. All right, we can have that personover here. And since if you basically insertthis into the join condition, we have sevenequals seven, because this card customer IDhas an equal value in this table, which meansthis card is owned by that person. It's kindof how it works. But with an outer join, weare going to ignore that. And basically say,we're going to return all of the rows foreither the left or right side. So there aremade that are and there are three main classificationsof joins, outer joins, that are I'm goingto write them all and then next video is we'regoing to discuss them, there is a left outerjoin, Right Outer Join. And that seems tobe an outer OUTER JOIN. I'm so dumb. I meantto write. Full OUTER JOIN, excuse me. Butbasically, we're going to be discussing thesetwo right off hand and full, that can be alittle morecomplicated for some database management systems.So we're gonna probably discuss that a littlefarther down the road, hopefullyNow the right outer joins, we're basicallythe same way as the left outer joins. Butinstead of the left table, it's going to bethe 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'sgoing to solidify your knowledge. And you'llbe like, ready to tackle this, because I'mnot trying to get you to watch my other videos,I'm trying to teach you something that's arequirement to understand this. And I don'twanna have to reteach it all, for the sakeof one little concept. So let's draw thisout a little bit. We have it to where we havetwo tables, a user, our customer table, anda card table. So this would be like for abank or something. And let's first see howwould a left join would do a left join wouldtake all of these customers. And then it willtake only the cards that have an associatedcustomer and return those. So we returned,we basically connect the dots. And then wewould get a joint view, which is going tolist all the customers, but only the cardsthat have a customer. So this is going tobe like customer listed card they have. Andif they have another card is going to listthat customer again, list the card they havewe discussed that in depth in the last video.Well, for a right join, it's going to workthe same way, except now it's going to doit for the other team. So we're gonna havethe same exact setup, we're gonna have a card,I mean, a customer and card table.Andnow, if we set it up with a Right Outer Join,it's going to take all of the cards, put themin the join table, so every single card, andthen it will list the associated owner ofthat 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 columncharacteristic of not No, that's gonna, thatmight confuse you a little bit because thenevery single card here is going to have anassociated customer, which kind of that'sthe whole point of the Right Outer Join, orthe left outer join for that sake, is to includethe rows that don't have that association.So if it's set to not know, it's going toreturn them all anyways, whether you use aRight Outer Join, and so forth, you guys understand,because basically, if we use the Right OuterJoin for this, it's going to return them all.But it's going to return them all anyways,because every single one has an associatedcard, we'll discuss that more in the future,don't worry about it. Now there's somethingelse to consider. This basically works thesame exact way as the left outer join, exceptis switched around. So when we discussed inthe last video, how you could say which tablewas the left,rightwhere the froms the basically the form belike select, maybe and then you'd have likethe columns first name, last name, and cardamount, or you can have your card amount ofus good. And then you have something thatsays like from Well, the column that you putright here, the table you put right thereis considered left when we're talking aboutthis design stuff. So in theory, you couldswitch it around to where a left join worksthe same way as a right join, you're justswitching the direction of the tables. Soin practice, most people don't even use rightjoins, I'm only teaching you so you understandwhen you see one. Instead of using a rightjoin, they'll often use a left join. And thenthey'll just flip the column the tables I'msorry. So like this, you have your two tables.And I'll give you two possibilities that aregoing to give the same result. You could haveyour customer and then the card. And you canuse a Right Outer Join. Or you could haveit to where you have over here you have thecard and the customer with the same exactcolumns in same exact foreign keys. Nothingchanged except in design, you just switchthe things and when you make the SQL statement,you're going to switch the location of thecall of the tables within that statement.But overall the function works exactly thesame. And now you're going to use aleft outer join.These are going to return the same exact thing.Because if you do this one it's going to returnAll these rows and the associated columnswith those rows, if you do this, it's goingto return all of the rows, and all the associatedcolumns, those rows. So either way, you'regetting all the cards in part of the customersz. So in practice, a lot of people will onlyuse left outer joins for consistency, andclarity. So if someone else sees it, theyalready know it's the left outer join, theydon't have to worry about left or right, andthen draw it out and get all confused. Mostpeople just use the left outer joins, butyou can always use right outer joins, if youreally wish that is up to you. Well, I'vebeen talking a little bit about not no columns,basically columns that aren't allowed to haveno value. And that can cause some confusionwhen it comes to joins, because you mightget results you don't expect. And I'm justgoing to be explaining that just a tiny bitmore, this video will be quick. So if youalready understand it, go ahead and skip itif that's what you wish. Alright, so I'm goingto be going over an example of a user commentsystem. So basically, you're going to havea table with the users, and then you're goingto 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 notknow. And what this means is that you're notallowed to have them to where there's no valuein that row for that column. So for example,if in comment, we had comment ID, and thenwe had user ID. And we gave it a column characteristicof nominal, well, what is that going to do?That's going to basically say that every singlerow within this comment table has to havea user ID associated with that comment. Andthat would be the person who posted that comment.Now, the user, on the other hand, doesn'thave to post a comment. It's a one to manyrelationship, and the user does not have topost a comment. But a comment has to be ownedby a user. That's important to know. So there'sthree real possible options of joins thatwe can do on this table. The first is an innerjoin, I'm gonna put these in caps, becauseit's probably better to do that. Now the innerjoin does not matter which one is left orright, because it's going to take the middleof both of them. So the positioning of yourtables within your query of the select statementdoes not really matter. All it matters isthat you're using the inner join, what isthe inner join going to give you and the innerjoin will give youall of the users who have comments. That'sbecause if a user doesn't have a comment,it's not going to be in his ID won't be inthe comment table. And he'll be left out.There's a comment that doesn'thave a user, well, then it would be left outtoo. But that's not the case. Because it'snot No. All right, the other option is a leftouter join. And I'm going to go into placeplaces in a second left,outerjoin. And then we could put the comment onthe left. So if you need help position, ifyou need help thinking about that, in yourhead, switch these two tables around. ButI'm not going to do that, because that's partof the illustration already. We're good. Right?The other option will actually let's talkabout what that's going to do first, leftouter join is going to give you all of thecomments,and then give you the associated users whohave posted those comments. So every singlecomment will be included. So the returnresult will be all comments, and associatedusers, okay? Now think about this, though,these things are actually going to give youthe same exact thing. That's because if youdo an inner join, it's going to give you allthe users who have comments. Okay? So it'sgoing to take you all of the users who havea comment and put it in there. And then it'sgoing to take all the comments that have auser and put it in there, ensure that allthe comments that have a user, all the commentsthat have a user. So these are going to giveyou the same result. So just understand that.The third option is to flip the left jointhe tables that is so got a left outer joinwith the user table and left. Okay, now whatis this going to do? It's going to give youall of the users and the associated commentsfrom those users. Now, let's talk about sowe're going to have it's either it eitherincludes them all or exclude some of them.So basically, if it includes With all of theusers, I'm going to label it with a star.If it only includes some users, I'm goingto 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 usersare going to post comments. So that's goingto be x on the user end.Butall of the users who have comments, that'sgoing to include all of the comments, becauseall of the comments are labeled as not know.So every user posts a comment has a row withinthe comment table. So it's going to returnthem all. So it's going to turn them all onthe user end.That's a star.Now, left outer join with the comments onleft returns, all comments and associatedusers. All the comments. So all on the commentside, it's going to return them all. But theusers, not all of them just part of them.Now, left outer join is going to return allusers and associated comments. So the firstone is some users all comments. The secondone is some users all comments. The thirdone is all users and all comments, this, that'swhat you kind of need to do when you're tryingto plan out which join you need to use, gothrough all the possibilities with that joinin is right amount. So you see that thesetwo are actually the same, this one is justa tiny bit different. And that includes allof the users. So you may be wondering, well,which of these two should you use? Well, tobe honest, it doesn't really matter. If ifyou wanted to return all of the comments,don't worry about whether it's not normalor not just use a left outer join with thecomments on the left. And then you're guaranteedto have that you don't have to worry aboutwhether it's not normal or not. So that'swhat I would do if you only want the usersand the comments included. And you don't wantto include any comments that don't have auser, which is never going to happen, thenyou do ainner join.So what you do is you just think pretend allthe comments, all the columns are knowable,or they don't have to have a value. And thenyou plan your joints around that. And it canstill work the same way. If that's what you'dlike to do. Otherwise, you just plan out likethis. And realize you can use either one ofthese, and it's going to work out fine forwhat you want to do. This video we will bediscussing using outer joins. When there arethree or more tables, we will be discussingthe example using three tables. But you canbasically apply it to any example as longas you understand the concepts, I really recommendthat you watch the video about using InnerJoins when there are three or more tables,because the concepts are going to be basicallythe same except different results will bereturned. So for this, the best way to thinkabout it is let's say we have three tables,ignore these two right now, we have a usercomment and video table. So basically, it'sa system where you can comment on videos,each video that does get a comment will bein this table. And every user that post acomment will also be in this table connectedwith that video, right? What I'm going todo is I'm going to use a left outer join onthis side and a Right Outer Join on this side.And we will just see what those results arelike. Alright, so first thing is first, let'sjoin the user and comment table. That's thebest way to think about it is to imagine thesetwo tables being joined here to make a newtemporary join table. And then imagine thisnew temporary table being joined with thethird table to give a final result join table.So if we do a left outer join here, everysingle user is going to be brought down tothis table in this situation of a left outerjoin. It will only take the comments thathave users because there has to be an associationof the user ID in both tables for that. Butit's likely that every comment is going tohave an associated user because that thatuser ID column will likely be set to not knowthat way you don't have comments that aren'tposted by anybody because that would be weird.So this will also return all comments. Great.Now that we have that new table. You see webasically just took everything from up here.Let's imagine taking this and joining it withthe videos and we're going to use a RightOuter Join. So this is going to be Right OuterJoin for Outer Join, we're going to take allof the videos and bring them down here. That'sbasically how right outer joins work, theywill automatically take every single row fromthe right table. And then we're going to takeany row in this table that has an associationwith the video table. So that means the commentswill have to have a video, which all of themwill. Alright, so what what will be removedhere? Well, some of the users will be removed.The reason that is is because some of theusers aren't going to be in the comment table.And if they're not in the comment table, theycan't have an association with the video table.So think about the join statements here, we'lllikely have something where it's like, userID over here is equal to the user ID overhere. And then we'll have it to where it'sdone, we'll have it to where it's a videoID over here. And that's equal to a videoID 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 thecomment table. Because it's a left outer join,which automatically takes all of the users,all of the comments will be returned, becauseevery comment will have an associated user.But once we get over here, it gets a littledifferent. Now it's about the video, not theuser. So we're going to take all of the commentsthat have the video ID, and all of the videosthat have a video ID. Because it's a RightOuter Join, we're going to take all the videoswhether or not they have a video ID in thecomment table. So the first thing, take allthe videos, we're also going to take all ofthe comments are only going to take some users.The reason that is is because if a user, let'sgive some example data, let's say we havea user over here with the ID of seven. Andhe he doesn't leave a comment, but becausewe do a left outer join. He comes down hereanyway. So now he's in this new generatedtable. Well, he has a user ID. But does hehave a video ID in the comment table to beassociated with the video ID in the videotable? No, he does not. So he wouldn't makeit on past this stage. And he wouldn't bein this final outer table as joined table.So the final result is going to be all videos,all comments, and some users. That's justan example of using right and left outer joinswhen there's three tables. Or you could dothe same thing with inner join. So if youdid an inner join, it's only going to takethe users you have comments. So I mean, youcan basically apply the same example to alot of different situations to get the resultsyou want. As long as you understand how aleft join works, how a right join works, andhow an inner join works, you can get essentiallyeverything you really need to get. The onlyone we haven't really discussed is the fullOUTER JOIN, which you might need sometimes.And that's a little different. So we'll betalking about that soon. In this video, we'llbe taking a quick break from joins that we'vebeen discussing for the last eight or so videos.And we will be discussing something that'sa little unrelated, but kind of related, ifthat makes sense. But anyways, we'll be discussingaliases. So an alias is when you rename somethingand you give it kind of like a nickname ormore of a user friendly name, or a name thatyou are more used to seeing. And you can dothis when you're writing SELECT statementsand SQL and you can do that to either makeit easier to write or easier to read. So whenyou want to give a table an alias, you willuse the A S or as keyword. And that's goingto say anything that follows as we which iswhat we want to call the table. So let's saywe had that user table. And instead of usinguser you wanted to say a customer, well thenwhen you use the table and the rest of thequery and you use your joins, rather thanusing user, you can use the customer. Andoften you won't take a short name and makeit into something longer. You will do somethinglike replace user with a Uand then instead of you doing user dot userID, you would be you die userID.That's an example of an alias. When you doit on tables. It's often to make the selectstatement easier. But you can also do it forcolumns. Now when you do it on a column, it'sgoing to work a little differently. So whenI selectstatement, you put all your columns at thetop. So here's just an example of what youmight say to a database, you're going to selectthe email, first name, and last name. Whenyou use an alias with columns, not only doyou change what the column is called, butit's going to change what it's presented as.So when you get that new structured view,that generated table, and it looks somethinglike this, rather than saying email, firstname 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 youcan replace it with you know, first spacename.This works similar in the way that the otheralias works for the tables, except you putit right after the column. So you can sayselect 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 whatyou 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 causea problem. Comma, and then underscore lastlastname.Okay, see all that. That's basically how analias works, you can take the alias to makethings easier to write easier to present.And also, it can change the way the view looks.So it looks more pretty, right? In the nextvideo, we're going to be talking about joins,that you reference the same table or a selfjoin. And basically, you're going to takethe table you have and basically tell themlast 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 ifyou give it an alias, so this table will becalled T one and this one V T two, for example.So that's an example of when you would usean alias. Other than renaming your columnsor your tables. The way a self join worksis you take a table, and you are essentiallyjoining it with itself. And that's what kindof makes self joins confusing. And a lot ofpeople 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 arecomplicated. And a lot of people can't findpractical purposes for them. In this video,I'll be giving you at least one good reasonfor them. So hopefully, this video is helpful.So when you have a self join, think of basicallyduplicating the table you have and makingan exact copy. And then joining those twotables together. That's a good way you canthink about it. So for this example, I'm goingto be using a user account system on a website.And you know how some websites have like areferral option. Basically, if you refer somebodyyou get like 20% of their money and or theirpoints or 10% of their money, so forth. That'slike referral marketing. And when you do thatyou are assigned a person who referred you,right, and it can become a chain like thisperson or for this person or for this personor for this person. So within a user table,you might have a user ID. And then on topof that, you're going to have, you know, email,first name, last name, and the person whoreferred you, I'm putting the person who referredyou within a column known as referred by,and this is actually a foreign key to thesame table. So within here, you're going tohave numbers such as 12,which is going to point back to the user IDof 12. So for example, the user ID 11 mightbe a person who was referred by the user withthe user ID of 12. Now, the only way you canmake this really work for a join, is if youuse a self join. Basically, what we want todo is we want to replace referred by withthe ID, we want to use a join to replace thisnumber with that user's email, which I alsoforgot to put in here. So you know, just throwan email in there. So we'll take this, andthen our outputted result will look somethinglike this. And this is the table that we'regoing to have. And then we're going to insertdata into that table. So you might have auser such as Caleb curry, and his emails isgoing to be swag@yolo.com. And then it's referredby that and we're going to replace that numberwith a user's email such asHoward SouthYeah.dot org.So that's just an example of what we can dowith this self join. Now in order for thisto work, we have to tell the database thatwe're going to be using the same table inbasically two different tables. And that cankind of be confusing. And in order for thatto work, you have to use what's known as analias. 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 wecould call each table, we can have the firsttable, which would also be the user table.And then the second table, which would beanother copy of the user table. Now we'renot actually like copying the data. This isjust for illustration point, it's going touse that data that same table to withdrawthe data from, and we could say this one isv one and v two, for example. Those are ouraliases. And in order to define those, weput as after our data after our table andput v1. So we can say, user as v1, and thenjoin user as v2. This is something kind oflike what you would do. So now we have a useras v1. And then we're joining user as v2.And it kind of gets the idea that oh, thisperson wants to use the same table for thissituation. Let's try to figure this out. Let'sbegin, we're going to put this within a selectstatement. And I'm going to give you somespecific syntax just so this can kind of makesense. So we have a select statement.Then right after the selectstatement, we always put the columns we wantto take our data from. Now this can get kindof confusing, though, because we have twobasically two different tables that we'regoing to be taking data from. So we have tothink which table we want to take which columnsfrom the way we had it set up before is wehave this table and this table. This was vone and this was v two. Well, that's kindof in our imaginary brains, which we don'thave, we're gonna say this table is for theuser. And this table is for the referrals.So we're going to take from this table, we'lltake the the user ID, maybe for the firstname, the last name, and the email. And thenfrom this table, we're going to take the emailof the personwho referred you, so we'll take thereferred byperson's email. So the way this will workis we're going to have the user IDs such asseven and Caleb curry, and then their email,and then it'll have an ID of seven or somethingaround eight, for the referred by, and thenthat's going to go to the second table toget that person's email, you got to keep thisin mind. And you have to figure out what you'regoing to name your tables before you startthe Select. Because now that we are goingto say the columns, we have to qualify thosecolumns by putting a dot before them and sayingwhat table they're from. And we can just usethe 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 thenwe can say V one dot last name, commav onedot email. Comma. Finally, we're going totake v two and we're going to take that person'semail or you can do their first name or lastname,whatever you want to put in that referredby thing, if you wanted to say their firstname, you can say v2 dot first name, if youwant to be their last name, it can be v2 dotlast name. Or if you want a combination ofboth, you can use a concatenation functionor something. So we're just going to use theemail. So we're going to take the v two dotemail. Now that we know what columns and whattables that we want to take the data from,we actually have to put the from statementstill and say something like from and thenput the table we're taking this data from.This is where we are going to define our alias.So we could say, from userasv onethat is going to basically tell the databasethat the user table is known as v one. Nowwe can do a join.Join,this is going to default to an inner join.If you just use join like that. We could sayjoin user as the two that is going to basicallytell them that user is also going to be knownas v2 but it's going to be considered a differenttable in this situation. Then you're goingto do the on which is going to say where weare going To join these things. Now this iswhere it can get really confusing, becausethere's so many possibilities that you canput right here. For this situation, we aregoing to take v1, and we're going to takethe referred by column, and that is goingto be the same as V to u user ID column. Soif you can think about this, imagine literallytwo tables with the same exact data. Here'sthe one, here's v2, and we have a user ID.And then within here, we also have a referredto by or referred by guess that's going toreference the user ID of the v2 table. Sothat means we need to basically make thisconnection and say that if there's a six overhere, and they're referred by table, it needsto be the same as the user ID over here thathas six. If you need more practical data underto understand this, imagine, we have thisguy over here. And his name is Samuel. Andwe 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 IDof six, within this user row of the user IDof let's say, 12. So Samuel has the ID of12, when he was referred by the person withthe user ID of six, which is I've heard of,and then this other table, which is also thesame user table, it's going to refer to theperson with the ID of six,which is this guy.Hopefully, that makes sense. But in reality,you're not going to have two exact copiesof the data, it's smart enough to understandthat these are all one table. So it's basicallygoing to take that referred by, and then goback to the user column, and join that intowhat would look like taking two tables andputting them together. That's why I get kindof confusing. But when you take this queryas it is, and you put it in your database,you will get something that looks like a normaljoin 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 goingto have the V one class name and the v1 email,and the v2 email. Then if we were to put exampledata into this, it would look something likeCaleb, Curry, and email and then the v2 emailwould be like another guy's email. So suchas socalm,that's what it would look like. And it wouldjust give you all the rows for that. So yeah,you can mess around, do all kinds of cooljoins and all kinds of stuff. But there'sone more thing that I want to tell you. Andthat has to do with aliases. Well, you know,this might not be too pretty how you haveright now. And it might be confusing havingan email and then right beside it anotheremail, like what is the what are those mean?Well, you might want to say that one columnis the user, and another is the person whoreferred them. So you can use an alias forthat, too. So for that you also use as, butinstead of putting in the from, you're justgoing to put it right after the column thatyou want to change. So you can put here, here,here, and here. So if I wanted the table toemail, or the person who referred, you couldmake it say like, v two dot email, as referredby, and this you're going to want to put inquotes to say you'll want that string as thetitle for that column. And then when you getthat new table, join, instead of saying email,it's going to say referred by, and then alist of data. Yo, what's up my homie homies,this video are the section of the video, Iguess, we will just be discussing this ina little more depth by showing this real examplethat I explained on the board. So you cansee I have a user table. And within my usertable, you know, I got user ID pass emailfor first name, last name, and a referredby this referred by is a foreign key referencingthe user ID. It's kind of interesting howthat works. So you can see here is this guyallama swag at YOLO calm is referred by to,which in this case would be Hi mom at lowlevel calm, which he basically referred everybodythat's why has a zero here. And if you scrolldown, we also have some people who are referredby one. And basically what we want to do iswe want to replace this referred by with theperson's email. And to do that we can usea self join. And who knows there might bea way easier way to do this. So if there isjust be sure to let me know. Alright, so here'sa query that would work to do that. So letme just refresh this. And here's what it'sgoing to do. It's going to select the firstname, last name, email. Mail of the personwho was referred, was also going to take theemail of the referral person, the person whoreferred the other person, the person withinthis preferred by column. Okay, so you cansee we got first name, last name, email. That'sone 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 referredby and put referred by, that's why it showsup right here, which is what we want, that'sgood. Then we say what table we're going totake the user table and call it v1, whichyou can basically name it whatever, as longas you update right here, then it'll stillwork. That's just the name I made up, I couldn'tthink of something cool. But base, basically,a version one, you know, this inner join istaken our other version of the table and justsay, no, this inner is optional. So you cantake that away, and it's gonna work exactlythe same way. Then we're going to say howit's related. So basically, this referredby email, that needs to have an ID in thattable that refers to a person. So the referredby is going to have an ID, and that needsto 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 actualperson, it needs to be actual data withinthat second version of the user table. Andit's going to go to the second version ofthe user table, find that person with theID of two. And it's going to take that emailand display it right here. So hopefully, thatmakes sense, guys, and of course, you canmess this mess up the square, you can justmess around with it and put your own stuffin it to get your own results. And you caneven change this statement right here to geteven more crazy results. So yeah, guys, thanksfor watching peace on earth, and catch youin the next video. And subscribe, as wellas v one dot last name and V Ah, it's a onenight now moron. For the aliases of the table.We're going to tell my SQL ah crap nuggets.It's not my SQL databases.\n"