 So let's go ahead and go to Lucidchart. And I'm going to, I want to sign in. So you sign up, you can basically use my Gmail account. You can link directly to your Gmail account and just verify and you go in there, okay? So once in here, I'm going to create a new here, a new chart. So new Lucidchart, you can start with the blank one. Blank document. Just say, yeah, just create a document in a way. If you ask me that, we just want a blank one. Okay, so what are we going to do? Let's say that we're going to design a, you know, I'll put it here. So we'll design a logical design for a person owner who buys cars from any auto dealership. Okay, so this one here, what are we looking for? Okay, so this is the logical design we'll get. So we have to kind of like look at that and say, okay, what kind of tables do we need in this scenario? All right, so if you think in the details, we're looking at a person or you can call this the owner. And we'll look at cars, okay, information about cars and the info about a dealership, right? So I can say that a person can buy cars, maybe you can have many cars, okay? From any auto dealership, you don't have to buy only from a single dealership. You can buy from different auto dealership. And the cars, a car, if you think about a car, doesn't matter what type it is, if it's a vehicle, can be stored or owned by the dealership for sale, okay? And then the car has information about the car, like the number and so forth. So we do all those stuff. So that means that a car can be bought from a dealership and the dealership can have a single car, which is not ideal or has many cars, right? So you have some kind of relationship between the two, the three things here, right? One car can only be at one dealership at any time. The unique car, if you think about the actual car itself. And then when the car is sold to the owner, right? This car can only be owned by a one owner. We'll make some requirements that way. But the owner can buy many cars from any dealership, okay? So look at that sentence or that story. These are the conceptual design you have to come in mind, right? Put on the table, talk it out, talk with your team or if you're a client and asking about it, asking lots of questions, what do they need, what are the requirements before you do the design? Okay, so I'm gonna move back over here as we have room. And then we're gonna go into this blank here. So when you do this, I'm not going to use this standard shape. So over here we're not use flow truck. So again, flow truck for the previous, any assembly you wanna use, I'm gonna go and locate, you can turn all these off actually, we don't need these standard shapes here. You wanna go and search in the library here, search for the one that says ERV or entity relationship. Okay, so this is the only one. Check that box and just go ahead and click selected shapes. Okay, the entity relationship, that's what you want. Okay, and now you see here. So very simple, just four of them. If you hover over this, this is not, we want something that looks like this. The second one is the one you want. So it is basically, I think drag it over here. Okay, drag it over here. And now you can, I'll make it a little, I'll make it a little bigger to the seat, but let's see. Okay. So what do we need to put here? So the first table I wanna look at is the, maybe the owner table, right? So for now, we're not gonna do any relationship yet, just put the information about those people or entity. So this one here is gonna be the owner, so I can call it the owner, the name of that table or entity. Okay. And then the key fields, and what do we want to put in this corner? So you're building everything pretty much from top down, because we have to go and identify what information that we need to describe this owner. So every person, and every table must have a unique key. So over here, you can put here PK, a primary key, and just have it over and type, we'll create one, what do I call it? Oh, we call it SSN, doesn't matter. I'll just make one up. Just call it owner underscore ID, okay? So this will be the primary key for that particular owner. And then the rest could be just blank, we'll leave that out, that out, okay? So the next field will be things like, you know, first name, right? You need to know the first name, and then just tap, tap, and then last name, okay? And so forth. You can add more addresses and things like that too. So if you want to add another row before Apple, you can select one of these rows and do a right click on it, and it says, insert below above. So for example, if I do below, then here, I just leave that blank, have it over, and we call this like something like that, okay? So this identifies this owner. I mean, we're just very simple. Look at that, we're smaller, oops. Okay, so we had the owner table, and you can drag it over here again, or you just select this table and press the control D, and it will duplicate that table for you, okay? Like that. So the next one could be the dealership. So let's put here the dealership or, yeah. And each dealership has its own ID. So again, I used the same convention, dealer ID. Okay, that is the primary key that identifies this dealership because the one that can have like 200 dealerships in the same city. So which one is it? Oh, so you see that they have their own ID, if you go to Walmart, each store has their own store ID. That's how you identify those places. So again, the field would be like the name of the dealership and maybe the location, you can be more specific, like the city and things like that, the address. So for now, we're just gonna put this very basic information about that, okay? So that's two or three IDs. We could add more as we go because again, during the building phase, right? If you have more information, we could add in two until we find it. So we need another table for the car, right? So we're gonna go back here and duplicate this table and we just put it somewhere here, doesn't matter. We're right here. And so we'll call this, we'll get the car. So again, notice I used singular noun for these tables, even though you have owners and dealerships and cars. Okay, so just fall back to singular noun and think about each table represents one object. So if it's a single car, a single dealership, single owner, right? Rule. And then the car, I could call it car ID because again, cars are very common. So you can use the VIN number, okay? VIN is usually capitalized because it's very common for cars. And then the information about the car you can have like the make, model, and then we have the year of the car was built and so forth. You have color of the engine and, you know, engine size and whatnot. D-O type and things like that. Wheel, right? So again, very basic about the information. Okay, so now that is pretty much all we need for those three things if you want to build this relationship. So we identify the entities, we identify the unique ID that will be used to identify each individual object for instance of that type, right? So, you know, Shaka could be in here, he owns a car from Honda, first in Honda, right? He bought another car from Kenosha for something, right? So it's all the same pattern. So now let's look at relationships. Okay, so you draw relationships by, if you click on it and hover it right here the dots you can draw a line across. It doesn't have to be, that's mapped directly to the column. Sometimes it's easier to see that way but it doesn't have to be that way. So I could just say this owner, I could draw a line has some kind of relationship. Well, you know, I don't really have, can I do this? It doesn't make sense, right? Can a dealership has the owner? No, you don't, right? Dealerships don't own owners. Owners don't have dealerships, not in a business sense. In a business sense, yeah, you can see, but an owner as a person. So in this case, there's no direct relationship between the two, okay? So it would not make sense to have it this way, right? But so we're gonna say, okay, that's not possible. It doesn't work. Ken only have a car. Yeah, they do, they do. So let's put down here. So we have, it's just nicer to put it this way. So we say, okay, owner can have a car. So I can map that together. If you click the dot and connect 80 dots in the right side, that's matter. And this is the default on line here. Okay, the line here is connector, connecting two together. And the default here is says one, the line here means one. It grows foot on this side and many. So if you read again from the owner's side to the car, I would say owner can have or can own. So those words you can use to add in here. One or many cars, you can also own zero cars. Is it possible? Is it possible that I may not own any car? Yeah, in real life, right? I can live without a car. And so to be more exact, you can click on this line and then on the end of the side over here, if you look on the top diagram, this one here shows you the end point. On this side, this is on the left side of this table here, okay? So based on the line you select, it will give you that option. So I'm gonna say on the right side, the owner can have zero is optional or many cars. So you can drop that, click that drop down and you select the one that has a circle in front of it. The circle with a three line, three cross four. So now you see that it has a circle. I mean, it's optional or I can have many cars, okay? So if I choose the other one, like this one here, optional one, for example, this one here, if you do that, that means I can have zero car or at most I can only one car. I cannot own them more than two cars, one to one car. That is the restriction. So again, based on your business logic, can you allow an owner to own two cars, right? So if that is true, then you change it, okay? So that's how we use this to design this book. So we're gonna have zero or more car. So from the car side, can a car be owned by one person, right? Can a car be owned by nobody? And is that possible, right? In the real world, maybe, if a car is like, you trash or something, then the car is still there. There's no ownership and things like that. Well, assume that a car is like drivable, okay? So in this case, a car must be owned by somebody, right? Either at the owner here or owned by the dealership. So a car can only be owned by, will make a rule that must be owned by exactly one. One person, one owner. So this one here is like one owner and it must, there's no circle. So it's not optional, okay? So they're related somehow. And then right in the line here, you have to go and, if you click on this or that, if you double click on it, I think it lets you to put some text in here. So you write some text. So from the owner side, except owner has or owns, doesn't matter, has a car, okay? So you can read it, owner has a car. But if you read the other side, say car has a owner. That's true too, right? In this case, it works both ways. If it doesn't make sense, then you change the wording to say, and so it'll work in both ways. So like a car like owned by, and then you put it on the side. So sometimes you see that, you see something like this as well. Like, oops, has a, and then you put like a slash and then owned by, like that. You can do that. So it's understood. You can drag it, put aside a little bit above it and make some lines here. And when you read it, it's understood. Like an owner is owned by a car. Of course it doesn't make sense. That way you can say the car is owned by one owner. A owner has a car or has made a car, yes. So you identify that relationship there. Okay, so now we look at the other relationship. So we know that a dealership has no direct relationship with the owner, but dealership can also have cars, right? Okay, so a dealership has to have a car. Before we go to the further though. So when you have this kind of relationship here, we have to specify this table or this table, a what's called a foreign key. Okay, so now they're connected together. Now, these two table here, which table should be a what's called a child table or a parent table. So if this car, okay, if this car is owned by an owner, how do we know that this car belongs to like Jeffrey or belongs to me or belongs to Jacob? So how do we know from this information here? So when you join two tables in here, you must have a foreign key in here. So we can say that this car is owned by an owner. So therefore in this table, you have to have a field. So I'm gonna add another field here. You're right above this make column row. Right click on that and gonna add above it. Now I put here a foreign key. Okay, so the FK and it's gonna map to the owner ID. Okay, so that's how they're related. So the car has a number is owned by the owner based on this owner ID. So the FK is a primary key that is pulled or linked to the primary key of the other table. It's a foreign key. So now I can identify the two. How do I know which car belongs to this person? You check, you say, select da-da-da-da from owner join, right? Join the car on the owner ID, right? So that's how they match. If they match ID ID, then oh yeah, okay, I can pull all the cars from this table that belongs to Jeff or belongs to Jeff or belong to Christian, right? That's how you work. And you see that, you did that in the exercise already. I usually map to, you map to a foreign key in the primary key of the two tables. It would not make sense if I put own ID match to the event number, okay? You're not gonna find that. It's probably even turned to zero. So we got that out of the way. So the next thing is now we think about the car and the dealership. Now don't focus on the owner now. We don't care about the owner. We just focus on the car and the dealership. So who owns who, right? So a car for sure doesn't own a dealership. So it would be kind of similar to the owner, okay? So a dealership also put here, a dry line again, owns a car. So now we go in here and can ideally have zero cars? It's possible, right? No cars, can have more than one car, yeah, right? So you can modify this. And say, okay, well, the dealer can have zero or many cars. But again, a car can either own by a owner or own by a dealer, right? So look at that just one to one. I mean, the table to table. Don't worry about the other tables. Because once you show the car, then it owns the other way. So by the same token, how do I know that this car belongs to the Racine Honda? So the car in this table, likewise, must have another foreign key that links to the dealership table, okay? So over here then I'm gonna add another field right below this F key here. Gonna add another one below it. And we're gonna put here another F key for the dealer ID. Okay, so based on this information, I can find out, okay, this car was owned by the dealership. But when it sold the car, it belongs to the owner now. So this owner bought this car because I mapped the ID. Where is this car bought from? From this dealership here, okay? So you can see that relationship. If this owner sold the car to another owner, right? The owner would change to a different owner, but the original car was actually bought from this dealership. So you can trace back to where it was originally bought or purchased from. Okay, so we got that taken care of. So now we look at this side of the table again. So if you think about a car, okay, so a dealership can have one, zero or many cars of different types. Like I have a Honda, a Tesla or Ford, right? Now, if you think about a car or cars in general, so the way I have here is that I'm saying that all the cars can only be owned by one dealership. Is it true, right? So in this case, it's not true because cars can be anywhere, can be in many dealerships, okay? So in this case, it's not correct because this is very strict. It just means that all the cars can only be sold at this single dealership and that is not true. The one, it means single. You cannot have more than one dealerships. So that's not true. But yeah, all the cars can only be owned by a single owner. That's true. The car, I guess. So we want to also change this one line, saying that the car on the left side can be sold at many dealers, right? Okay, so this one here, I'm going to go in here, add another notation here. Just so we can see right here, you can add another note here right below. Let me see. So we know, yeah, I'll just put it here. By next to the bind, I put here, is a one, two, many, okay? Because many is on the other side, right? The cross foot on the other side. So a person, one owner can have many cars. Then a car can only own one owner. So one calling them, meaning one, two, many. So one, two, many relationship. And then this one here, going to put another message saying dealership can, I guess, same thing has, or put here also, same thing when we get owned by. But then we have here, many, two, many. I mean, not owned by, but I guess, I don't know how you would phrase this. It's not really owned, which is sold at or something. But this was called a many, two, many relationship, okay? So dealership, we have many types of cars. A car can be sold in many dealerships, okay? In our restrict to only one dealership. That's what this means. Or maybe not owned by, I can say, sold at, makes more sense. A car can be sold at a dealership, many dealership. So this and many, many. In this example, it will work fine, but when you write it into a code, it will not make sense. Okay, so when you create a table, you're not going to create exactly like this and you create a many, two, many relationship. You will work in the code. So every time when you have, for any time we have a many, two, many relationship, you always have to have a junction table between the two. Why? Because something we don't put here is, for example, if I go down here, yeah, this won't change, right? It make model year won't change. But what if I put here, for example, let's add another will be lower, we call it, we'll keep that there. And we'll call it the price of the car, right? If I price this car, and let's say this for a month thing at 50K at Racine dealership, at 50K. And if I have the same car, the same year make a model, sold at Kenosha dealership, do they sell the car at the same price at 50K? Or do they sell more or less? Okay, so if you don't have a junction table, then junction table, it's not possible to have a variation in prices. So you lock in the price, then you cannot change. And that is not true for a car, right? So a car could be priced differently at different dealership because of the many, many relationship. Similarly, if you work for a company, like, you know, you work for a company and or you contract out, you are a developer and you do the same task, right? You write program to company A, you write another program becoming the same application, do you charge the same price or do you charge differently? Right? So the price changes because based on the company or that negotiation, right? So your pay rate changes based on the project, even though your role is really a developer, okay? So that means that, oh, your price is not going to be fixed across the board. So that's what this means. So in this case, we're going to create a junction table between the two, okay? So, and the junction table will look something like this. Let me, a little bit, so I'm going to trade another one, I'm just going to duplicate. Yeah, I'll duplicate this is fine, duplicate that, put it over here and I'll make this little bit further up, this side to that spaces, okay? So this table here, so the junction table is used to connect two tables that have a many to many relationship. And when you do that, okay, so this actually changes. So instead of going to directly from the dealership to the car, you would connect that to the dealership here. You would call this different here. You call the dealership, I'll call it dealer, maybe underscore car, okay? It's commonly named that way to indicate that this junction table is a connector or junction table connecting the dealership and the car tables. They put the name dealer underscore car. And then the IDs, the PKs, in this case would be both of the tables PKs. So in here, you can create here, put up with the dealer ID and also the PK for the VIN number. Okay, so you can see I have two PKs. And those are uniquely identified, the dealership and the VIN number. So they join together. So in here, instead of price over here, the difference is just the price. So in this case, the price will not be listed over here, okay? So we delete the price and we leave that out. So the price of the car is dependent on the dealership. Oops, so I delete this row because, and then now the lines will be drawn. I'm gonna move this line. I'll do this line first and connect that to, I'm gonna drag this out and connect it to the dealer over here. So we can see, okay? We'll leave it further over here. So the number here will be different. We have a many to many relationship. It's usually like you would do like this, okay? So the left side of the dealership has a one and then on this side, many, right? Zero or many. And then from this side, you do kind of exactly the same thing. So I draw another line here now from this table to this table up here. So the line will be from this table, this side is one, two, I mean, okay? So you see that it's one on the tables side, one on this side now and many, and many goes to the junction table. So notation will be the same up here. I didn't put it, but you can put the same. Okay, so I could do this, has many cars in the middle here. So that could be something like that. So if I do this way, and we can align probably easier to see. So there is my table you can design that you use. Okay, so this table here uses four two of these keys. It's also very common because this is also the pk, but the dealership is also, the d-line d is actually the dealership foreign key. So here you would write it like this, also fk. Okay, and I'll make this a little bit bigger. So we have more space. I'm doing this way like that, stretch a little bit farther out. There's some room in here. I'm gonna stretch that a little bit like this. All right, so it's also the foreign key as well, but it's also part of the primary key. So put it here like that. Both of them joined together to form a primary key for this table. And they're also the foreign key to the dealership, the foreign key to the car. This is one method, okay? You can do that. Another approach I will put over here, just so you can see it is by using like this. So instead of saying, you know, this will be like the foreign key and I can go back to the original size like that and add another row above it and this will be the pk. And this will be like dealer or ID. You can do that too, if you want. So this is another option, how to create a junction table. I put two here as you can see, but this is actually the preferred approach. And the reason why is because if primary keys every table must have a pk, one or more pks, okay? So here I'm using this pk, this pk, so it's basically joining both of them together to form a primary key. And they're also the foreign keys to the two foreign tables because they will always be unique if you combine them together, right? So a dealership cannot have the same car twice, okay? So this is the preferred approach and database design, but you can certainly create your own additional primary key like this if you don't want to use them together. The way you do here is you add another ID, so follow the rule. Every table has a unique ID, so I just create one. And by doing this way, you actually, it's not efficient because you are creating a unique ID for this table where you can actually already obtain a unique ID to combine the two. So you are creating extra data, which is not useful. So you're just wasting space here, okay? For your optional, what are you not recommended, okay? This is, I mean, it's okay, but this is not, I should say not efficient, probably better. This is not an efficient approach. This is the better approach, okay? So that is our design. So everyone has their number and you will see that sometimes you can break this down further but we'll do this in, you know, when you do normalization, you can break this number down and you can actually break it even further so that, you know, the actual event number can be on its own table. And then I just do one more way quick and I'll just stop. So for example, what do the owner and the dealership have in common? I call it location here, but maybe we'll change this to address, right? Okay, so usually the address would be kind of the same, like in, you know, city, state, right? The address, city, state and zip code, right? So if I were to list that here, it's redundant. I could do like city, state, zip code and so forth, and then city, state, zip code and so forth, right? So what you can do is that you can add another table over here and we'll create one over here on the left side. That can be shared by both owner and the dealership. So I will create over here an address table. Okay, and you can call it like the address ID. And then here you'll put the address address one, address two, and they have the city, right? And let me add another row below city, state and zip. So I think we have here blank here, state and then zip. That's a very common address that can be connected to the owner as well as the dealership. So instead of having this way, I was in here. So the address here then will have, how do you know this address belongs to the owner and this belongs to the dealership? Okay, so you add in here, I will go here and add two keys of one above, one below here, add another one below here. So this would be the FK for the owner ID. This address FK for the dealer ID, okay? So kind of like the car here, car, you know, this car, this address, this car belongs to the owner, this address belongs to the dealer. So same thing, this address here belongs to the owner and also belongs to the dealer. So they share the same table. So you don't have to have like duplicates data inside the owner table and the dealer table, right? You are creating a more efficient data. So here then this table cannot be joined by the owner table over here and then it can also be joined by the dealer table down here. And it's a one to one, right? A dealership had exactly one address. So the notation for both of those will be exactly one to one and then something here one to one, okay? So one address can only be assigned to one particular owner, but you know, in the real world, it's possible that the owner can live in many places, right? So if you look at the individual instance of that, then you have just a one to one. So you can put here again, lives at whatever it is, the same thing located at and then something like that. So one to one, it will be a one to one, I think. No, it may not be right. It's a, no, it's a many to one, sorry. Because if you think about a dealership can have different addresses, so that's not right. If it's one, it's locked in, right? It is not possible, so this is wrong. So on the left side, the address should be many, okay? So the address is assigned to a dealership, but a dealer can have different addresses, right? They don't belong to the same address, so same thing on this side, you know? Should be, I think it should be, I mean, they can have different address. So they're locked in. So like you, like Jeffrey and Jacob and me, if you don't live in the same address, okay? So here would be one, two, so we may need to one. So something like this, I think, and as you think through some more and add more data to it later, you'll see that, oh, it doesn't make sense if you're gonna keep modifying early on, okay? When you add some data, some simple test data, just to make sure it makes sense before you go ahead and roll out and then go live or something. So not all tables are related directly, but I can access this information based on their identity and based on the IDs, right? So I can actually get, like, find it, tell me all the people who bought all the any car from the dealer, from this dealer. I don't have a direct connection, but I can go this way and then I can connect that dealership, right? See how they're related because of these lines here. And sometimes you will see that there is no relationship between the two. Like, for example, you might have a floating, a floating table out here like this. Maybe this is just some kind of a, I don't know, a market. Has no relation to the name and then the type of market you have. Like this table here has nothing to do with any of these cars or dealers, right? How's the market doing, you know, whatever it is. You have the information here for whatever other reasons, like for decision making or something like that. So in this case, it's floating around. So you have like, if you look at this in a big one, see what we call sometimes virtue islands. You have like one big island here and then we have one small island over here. You might have a couple islands that are only connected by two tables or more. And if you look at the amateur works again, if you see that picture, if you expand that adventure works table, you will see a bunch of islands. A group of related, we call it themes, right? We talked about themes before.