 Right. So data modeling is a really important step where, you know, phase and database design. And when we talk about data modeling, we refer to one of these three stages or phases and the development process. Okay, so it's consistent of a conceptual data modeling or conceptual design. And I have a logical design and then we have the actual physical data design. Okay, so I'll just put note in here saying that all relational database systems, okay, this is true for all database systems. Not may not be true for, you know, the no SQL databases, but this is the structure type of database system must have at least the following three things. So the table which in this case is the actual we call the table or a entity should be should be used or should be created in such a way that you will describe one and only one entity. Okay, so an entity will look at a little bit later the meaning of the entity is an actual record. So the table should be very unique, you should describe that particular per object, like a person right it's very unique, the only you only refer to about that person. Anything, any descriptions or attitude we describe of that object that person should be related to that person only you should not include like, you know, my cars, my house or my work place things like that they're not of the same type. The table should be very specific. Okay, so think about an object or rent a program design as well you when you create a class for a pro in a program that class should be very specific to one thing that object that class of a car a class of a tree right very specific. And then the next one is that all the roles must be unique. Okay, meaning that in this case it refers to a key, we call the primary key and I'm sure you already kind of seen that already. When you do your homework assignments and we talked about that briefly in the past that every table has a unique key called the primary key or the pk key you see that in the notation. And then last but not least, the column and the role. The order should not be should not matter. Okay, so the order of the column it doesn't really matter it doesn't have to be in a certain order. I mean it's nice to have it in order so that it's easier for us to understand, but it's not going to cost any problem and the database table. It's usually the order of columns and the roles, you do sorting, you do that after the fact that after you pull the data out, you can rearrange the columns and anyway you want, order the roles and a record anyway you like so. So that is the query part of it, but data table itself. It's not a required study point. Okay, so so it can be in any order doesn't matter. Okay, so down here is look at the conceptual design is a high level design very high level, just concepts, and it's usually it's a map of concepts and relationships. And it also where you will list all your business requirements for your for the for the design, right, what is the need was required. So if you like the same idea as if you were to build a house. Right. So before you even start building a house you have to have this conceptual design of a house like what kind of house do you need to build. So is it a single story or is it a trial level. It doesn't have a basement right how big is it, how many rooms do you want so all these are just the requirements you need to put together, right before you even start the blueprinting design right so the concept design is very high level design so usually this is this is this this step is took place when you create a project or some sort and you have no it's like a brand new project, then you need to gather that that information so it involves in that case. If you were able to give in some data already somebody only has some data has the table created has some raw data already, then you may already kind of skip this part already you're going to go directly to the logical design. Okay. So here's an example of that using some notation here. We have a student has some kind of association or relationship with a course and vice versa right so what, what kind of relationship to they have and you. And based on this design, you can say Oh, a student will can take a class, and then a class can also have a student, or can a class have more than one student. Yeah, can a class has class at zero student. And I don't know depend right so that is a requirement. You have to specify that in your requirements. Okay, so can a student take a class. Typically, yes, can a student take more than one class right you have all those rules defined these are the business requirements right. And can a student be a student if the student doesn't take any class right all these things have to have to be defined before you even start into that process okay so that's a conceptual design the overall map of the program like you get like the roadmap. So I think we're going to look at the second phase which is the logical design. More. Okay, this is where you actually start building for writing for creating the diagrams for the database itself. And you saw this and the database design right you so I show you the table, and you go to the David database design and you show that you can see all these tables look similar to this one here as the lines connected to each other, and some might be independent. You see a bunch of lines and you have some numbers and they go in key and things like that right so that is the ERD, we call the entity relationship diagram. Okay, so it gives you the physical layout of what these tables are like, and how they're related. So this is a blueprint design or template. And it consists of entities of entities here is sometimes is used to refer the actual object of that type, or sometimes it's also referred to the table because again, this table student really is the blueprint to create a student instance by a student record. The actual entity itself is the individual record of that student. So you will refer to that student by name or by the ID, right, it's the actual object. Okay, so that's the entity. And then here is an example of that logical design so notice here the logical design, we have more information than the conceptual it's very broad right just the type of entities. We're going a little bit deeper, we're adding the actual fields into each of these entities or tables. Okay, so each student has an unique ID and their information about them. And then we have a course over here course ID course name and then this is a not the table we will call this as the junction table, and has to do with the type of relationships will talk about that later as well. And you also need something like this to design your physical table, but you see that these two student and course table are joined by another table in between. So again, this is the junction table that connects the two. The reason why that happens is because this is a will look at level later is what's called a many to many relationship. Okay, so this symbol here, the little three, like cross feed here. This is what looks like three lines, just say for many to many. So they have that in that case is a business requirement, we assume that a student can take many classes. Okay, that's why we have a free cross foot here. And people say that a course can be taken or enrolled by many students. Right, so it's a main to many relationship and drawing we do something with something like this to be a little bit more specific and logical design. We add another table in between to make this work. Okay. So we cannot just like do like this and the physical design where you actually write the code, it won't work like this. You have to create additional table to make it work in the physical design we'll see that next week. Okay, so the logical design is the blueprint is a non system specific, meaning that you can create this, you know, work in any database system. It doesn't have to be SQL server, it would, it would work for Oracle, my SQL, my progress SQL SQL light doesn't matter. As long as it's a relational database systems, this will work. So you don't really care about what system you need. We create this design. And then after that we once we have the logical design created it's all good to go. Then we go ahead and start the actual physical design so the physical design here is the actual database and the entities in this case tables creation. This is not very specific to a certain system. Right. So this example here we are creating a table call position using SQL server. Right. So it's a very specific to that system. And because it's very specific, you know, the code here may not work for another system. Right. So, like, it may not work in Oracle. So you have to kind of, you know, make some modifications or changes if you want to port it into another system. Otherwise, you have to write in such a way so that everything can be portable over and then that's fine too. And it will take a little bit more work to do that. But again, that's part of the design. Right. The business requirement. What database system are we going to use. If you're going to use Oracle, then your physical design should be based on that. If you're going to make it so it's kind of cross platform across database servers, then you want to make sure your code is, you know, portable in that way. So, again, that's part of the business requirement early on in this stage. Okay, so we'll look at the example here is I showed you here what's called a all LTP and all LAP so two common types of databases system use in industry. Okay, so LTP is the online transactional processing system. These are the common most common ones used today in e-commerce anywhere you go. This is a live data. It's like you go to Amazon, go to Walmart. You purchase something at the store. These data always changing. So it's constantly changing. So it's live. So more than likely probably 90 or 99% of the database you see online is an all LTP type. And the ones that we're creating is really all LTP type. The other type is all that all up here is online analytical processing database systems and these are used for like, you know, data mining data analytics, right? Because in data analytics, you just basically pull some historical data and, you know, model it and create some kind of, you know, system design and then use that to make predictions, right? So we focus mainly on the all LTP type. Okay, so again here just some definition of what those types are. You can read about them here. We're going to jump down here to the ERD. Okay, the ERD, the entity relationship diagram is a tool. It's a tool. Really, it's a mechanism or a way that helps database administrators create a database. Okay. And there are many different ways how to do that. So I showed you here the three different types of ERD news in the past, and maybe someone still use today, but these are like different ways so you can create databases using just some diagram to show you. Okay, so this is like a car, a car of particular customer and then each of these, each of these car using actual customer, right, or a person itself. Okay, like an object. This is a very, probably, you know, integrated and always, and it's old and we don't use that anymore, I guess. The other one is something like this you might see. Okay, so using a database here has a lot of a table here has a lot of objects inside. So each of these is an individual entity, right? It's another design. And here is another one we showed you earlier, it's just a box. Okay, so this is like the high concept conceptual design approach. So let's go back here. Some add some vocabularies we need to look at. Okay, the entity type, the type here refers to a category or the actual name or type of database tables. It refers to the table you want to create. So, for example, the employee table, the name of this employee type table, it's called employee. So we expect this table to represent employee. So it's an employee type, right? So it is just all it means. It's a technical term or really it's just the actual name of your table and usually you should name your tables in a very meaningful way. So if it's employee, then, you know, they should contain employees. You shouldn't have put like cars in here, right? But it doesn't make sense. Okay, so the type has to be meaningful as well and concise. So we have like employee, student, a class, all these are considered as the entity type. What type? And then we have the attributes. Okay, attributes. Oh, by the way, these terminologies here, the entity, the attributes and entities here, these are generally used when you are doing the logical design. So if you move over to the physical design, then we don't call, I mean, they have a different terms for these, right? And basically we call it tables and columns and rows. Okay, so these are the physical, the names or the vocabulary is used when you do a physical design. You refer to the tables, like in the assignment, you know, join, you know, table user to table stores or something like that. So the actual table names are used or referenced in the physical design. But when you hear the terms entity attributes and things like that, these are usually referred to the logical design. Okay, so the attribute here is just basically the columns. And these are, if you think about an object and a class, you know, a scenario would be the name fields or the data fields of a class. Okay. And so, for example, like this employee ID, the address, the name, these are all like descriptions. So the adjectives that describe this entity type. So the entity type is a noun, right? The noun, the attributes, although they also know, but I usually, you guys, you can refer to them as the adjective that we describe this particular entity or noun. Okay. And then the actual entity itself is the actual values. In this case, the record itself, right? A single record is the actual entity of this type. So John, for example, is the actual object of a person or student type. Okay, so those terms are used. And when you talk about ERD relationships. Okay, so down here. When you design databases, you have two classical approaches. And this is also common and program design as well. You know, maybe not possible in like building design, but usually in programming and database design, you have these two approach. You have the top down and the bottom up approach. So which one do you use? It really depends where you're at and the development. Also depends on whether you have some data already given to you, or whether you have to start from the very beginning where no data are present, you have to collect data. Okay, so those two things will take into account. And if you do have some data already, then you can do a hybrid of those. I do both of them. Some top down, some bottom up. So the design here, if you look at this, you can see we have the conceptual model up here. So if you go into top down, top down is used when you don't have anything given to you. And so you created new project and new game, new app doesn't matter. And you need to come up with some database tables or database system to store data. So how do you start from there? You know, how many tables do you need? What kind of tables do you need? So if you were to design a video game, for example, you might need a table for the player, right? You might have a table for, you know, the world or the map, right? Each world, each level. You have a table for the weapons, you know, one for the enemies. So all those are different classes or different database tables, right? So conceptually, you design that and then from there on, you go down from that, right, really, really broad topic, you go down. Okay, so in the hero class, what do I need, right? So you have, you define, okay, I need a hero class, I need an enemy class, I need a weapon class or a table, right? From there on, okay, so in the enemy or the hero class or table, what attributes do you need? So you're going down to the very, very low level or very finite attributes, right? It has a name for the attribute. You might have a class, but the hero could be a mage or it could be a, I don't know, a wizard or something, right? So you have different classes, the names, you have the HPs and, you know, and so forth, right? And then, same thing for the other one. So as you can see, we're going down from the top to the bottom for something very conceptualized, very broad to something very, very granular, okay? And the bottom approach is just the opposite. You can think of it during the bottom of approach. Or you can say, like, okay, like back to the game, okay, you think directly to the things that a hero or a character has. Oh, a character has a name, you know, put the name down, okay, it has a, it can carry a certain type of weapon. It has, you know, maybe it has certain type of abilities. You put that, you list all those details and it has a class, it has a level of some sort, right? It has skills level and then you put all those together and say, well, this one, you belong to the hero class, I created a hero object or person. And some of this also related to the enemy class, right? So you go up that way and then all these belong to another table and so forth. So you go to the bottom up approach that way, okay? So either way, it's fine. Some people like to do it the top down because it's easier. If you already have an idea of what you want, then you can go from the bottom up. You can list the details and then make it broader, broader as you go forward. If you are still kind of like brainstorming what to do with it, you basically go the top down approach, right? I'm going to list all the objects I need first. And then I'm going to go into each object and list the details. So I would say that, you know, I don't know who would use more or the other, it depends on the project type, okay? So in terms of databases is the same idea, right? So if you were given no data built from scratch, then it's likely that you will stop, you will start from the, you may start from the top down approach. Or maybe even bottom up because you're like brainstorming, sometimes you do both. It's called a hybrid. You start somewhere in the middle and you go both directions. If you're already given a set of data, some really raw data that somebody have, you know, I have an Excel sheet of all the data are collected from an event or some sort and they give it to you and they want you to design the database for that. So in that way, you have some raw data already, and then you would then go in from there, which approach do you take? Okay, so again, it's dependent on the type of data you have. Okay, so just keep that in mind. So if you go into like, you know, SQL Server and you start creating code already, then usually in that way you're actually doing, you might be doing the bottom up approach. Right, so down here, the processes before you start creating the ERD or the logical design is you have to make sure that the following are already kind of defined like the business cases. Again, these are the requirements, and this is for the concept design, right? You have to have that in mind already. And then you would then define the entities, what type of theme, what type of database tables are these for? So if you create an app or a program for an organization, you know, maybe these tables are related to that organization. So you're going to have like employees, departments, the sales and, you know, HR are also related to that theme only. And if you focus more only on the, let's say the sales department or the R&D development team or department, then those have a different theme itself. So the way you name your tables will be more meaningful in that sense. Right, so if you have like a theme, for example, a college, right, your table will be related to the college. You have student classes, you know, books and faculty and things like that, but you're not going to have certainly a table of games, right? It's not a common theme. Unless you have like sports, then that would be a sports theme. Right, and the school general, you have something like that, right, very common. And then you have, you know, the attributes you want to do and then you have to define some primary keys. And so every table should have a primary key. This is a key that is unique across the entire table. There's no duplicates. And it must represent a single instance, a single record of that particular entity. And then, like, there cannot be two Jeffrey, right, or Cam there, you know, maybe in a different world, different dimension. I don't know, like this movie you see, but there's only one Jeffrey, Jeffrey and the entire universe, right? So like that. And then foreign key will look at that later foreign key is another very important key that is used in generally to link two or more tables together. So it's not possible to link to tables without using foreign key if they are somehow if they are related. It doesn't mean that you are not able to query data from two tables that have no relationship. Of course you can. It doesn't matter, you can, but the data may not be meaningful, right? But if they are related, then usually these two keys are used to form a very strong bond or strong connection between the two. And sometimes we create something what's called a dependency or something of kind of a referential integrity between the two tables. So one cannot exist. It's like the foreign table or the child table cannot exist without a parent table. So he has a really strong bond between the two. Okay. So if you think about this like in person has like organs, right? Organs will be another a child table that has all types of organs, but the organs cannot exist without the person. And you can say true the other way too, right? But which one is more, which is stronger. Okay. The entity again is a thing is the now in the real world that we can actually refer to. It can exist independently by itself, or sometimes it needs to be dependent on other things like I mentioned about organ, right, like that. So they're classified under two major types, and there are many more, but these are the two most important or most common types. The tangible and the intangible types, right? And then there will be something you can see in the real world like your personal car or a house, as opposed to a tangible and tangible would be like something conceptual, or something in memory or in a program, like a bank account. You can't really see that in the real world, right? Or the address, right? It just a certain number you can look at. It can be like student grade. I'm a senior, I'm a freshman, right? You don't really, you can't really tell and see that in the real world. It's a some kind of indication only, right? So two types, at least in, but they're all actually nouns. So here next one is an example of an entity diagram. I'm going to show you different properties or components of entity. So when we draw entity diagrams, ERD, we usually refer to a diagram that looks like this. Now this is the most common type nowadays used to design databases. So you will see a diagram looks like this. It looks kind of similar to the UML diagram. If you have used that in software development programming where we have the class name on the top, very identical. Okay, so the name, the box on the top is the actual name of the entity. It's usually named using the camel case, okay? Apple case, the first letter of every word in there. And the same rules for creating classes in computer programs, right? That's why we mentioned that all the time we write programs, follow the convention. And then so, and then we have the next word now is the primary key. So this key here is the unique key that identifies every instance of this entity. The actual object itself, right? It has to be unique. Like I have my own social security number. You have your own. So that is a unique key that only one person, I guess, in America can have. It does not apply to even go overseas, of course, but in America, for example. Okay, so that is listed here. And then again, the convention here follows the camel case, right? Lower case here and then the primary key here. Again, it's just a convention. It doesn't mean that you should, you have to do this one. You don't have to, okay? Sometimes you will see it's underlined here. The underlined, just the indication that this is a primary key. And it's also indicated on the left column here. It has a column on the left. Sometimes you see the line. Sometimes you may not see it. But again, this is a common one. You see it's a line on the left column. And if it's a key primary key, then you see the PK here for primary key. If it's a foreign key, then you see the acronym FK. Okay, sometimes you see the AK for the alternate key, meaning that it's a different key as well, but you see that as well. You might see that later in some locations. The underneath that you have the other attributes, right? The actual column or fields, like the, you know, the first name, last name, you know, age, address and so forth like those. So that is an entity. So all tables will look something like this we designed. Right? So types of entities, there are two major categories. One is called a strong type. Sometimes referred to as the domain. These are independent in a strong entity. That means that they can exist by themselves. Right? So if you think of a student in a car, they can exist. A student is probably debatable, but maybe a person is probably more right. A person can exist by themselves. A student, you can debate saying that, oh, you know, if I'm not enrolled in a class or in a school, I'm not a student. Right? So I can exist. Right? So it is, again, depends on the business logic that you have, the requirements. Then the weak ones are things that would depend on one of the other entities like the table. Like for example, enrollment, right? Enrollment cannot exist without students, right? So there's no enrollment. Therefore, if you don't have any students enroll. So that is a noun, but really it's weak. It can exist without students. So that is a weak type. Okay? So again, a race, right? A race cannot exist without cars or racing, right? Or a person, it doesn't matter what kind of race it is. So something like that is, it's a weak entity. Okay? So there are others as well, but these are the two common ones. And then we'll look at relationships. So tables are created because of the relationships. And the term relations here really refers to the actual tables for the entities. That's why these are called relational database systems. They are related somehow. Okay? And examples I do here again, back to that, just the entities, the table themselves. And then each table has a primary key. You must have a primary key. You don't have one. Usually the database system will have one already created for you. Otherwise, it could be a problem if you move data over to a different system. They might contradict all of that. So the primary key, we'll look at this later when we do the normalization and unit seven. So the key can be what's called a natural key or a surrogate key. Okay. And this is used to define what type of key you're using, what type of column you need. For example, if you think about in a person, like we have our social security number, this is something that we don't just make it up. Of course, somebody has to make it up in the government system, but by assuming that below that, right, it is applied, is given to you when you were born. Okay. So because of that, you always have that same SSN pretty much for the rest of your life, right? So it's a natural key. Same thing for the number of a car, right? When they built the car, the car become existing to existence, they created a new number for the car. It's given to that. It's naturally designed, assigned to the car. You cannot change it, right? Same thing with the ISP and for a book. Okay. So this is called natural keys. The surrogate key is something that you can make up as we go. The actors table. I can make up the actor ID and call what I want. Actor one, two, three, actor, you know, James Bond, zero, five, doesn't matter, right? This idea that you make up or username is also simply unique all the way, right? You create an application and you let user create a unique username. It's something that is not very given to you naturally, okay? So these are common terms. Again, you will hear and database design. And primary keys are always unique. They must be unique and they cannot ever be known, right? You must have some data. Otherwise it's not a primary key, okay? So then databases have relationships, okay? Because, I mean, there are tables, I should say, have relationships. Unless you have only one table, then, you know, that's for itself, but usually an application, you will probably have more than at least two tables or more, okay? And we have those two tables or more, do they have some kind of relationship? And chances are, you know, they will, they do. So we use that. How do you define relationships between tables is by using some kind of special notation, okay? And you will see a little bit later that we use the arrowhead notation to draw the relationship between two tables in the flow of data, kind of like in data program design. And we use something very common. We call it the cross foot notation, okay? And so in database, there are three types of relationships. These are known here. The one-to-one. It's usually notated like using the one colon one here like that. It's one-to-one. This is the left table. This is the right table, okay? It's one-to-one. It has equal mapping. And then we have one-to-many, one-to-many, sometimes one-to-end. They might call, they might use n, or it doesn't really matter. It's just another letter because the m here could be an infinity, right? I don't know how long it can go, but it could be a different number. We don't know what the m is. And then we have the many-to-many. Again, the writing here, if one is on the left, then we're saying that the table on the left refer to the left and then it's on the right. Sometimes you see m calling one, right? Depending on which side of the table you're referring to. But they both still one-to-many or many-to-one, doesn't matter. And then we have the many-to-many. And then there's a reason why you don't use mm here. If you use mm like this, then you don't know which is which. And sometimes if you mm, then you're assuming that both have to be the same, right? Like one-to-one. And because they're not the same, because this table might have three, this might have five, right? For example, so they may not be equal. So that's why they have to use different letters to denote these are two different sizes. But indeed, they're many-to-many. Okay, so these are the only three types of relationship you see in database systems, right? And also in programming too, I'm sure. So here's look at the one-to-one relationship. The one-to-one is, I will put some examples here, right? It's a one-to-one direct mapping. So one table maps exactly one, one record from this table maps exactly one record at the other table. You cannot have two, right? Like a person has exactly one social security number, right? So the person class over here or table has all the people, they might have the same name, of course, but then they're mapping exactly one social security number. Okay, so this is the SSN table. Each number will map exactly to one individual on the other table. There is no duplicates in that. You're assuming a perfect system, right? So same thing with a student, right? You have one exactly one ID for, you've been studying at Gateway and that ID is assigned only to you, right? So these are one-to-one relationship. This notation here is very simple, but it's also referred to as the Buckman notation by a database scientist who actually came up with the design here. It's a simple design. So one-to-one. And then we have the one-to-many or many-to-one. It's represented using the cross-foot here, the three lines here to show the many. The nothing on this side here is shown is the one, okay? So one-to-many, or if you read the other direction, many-to-one. So some examples here again would be like a person can own many cars, right? But a car can be owned or registered but only a single person, although it's not entirely true, but usually like that, right? Okay? So that's the case. And then we have the many-to-many. So again, the cross-foot on both sides, so like a bar can sell many beers, right? So one type of beer can be sold in many bars, right? Okay? So that's what that means. A student can enroll in many classes. A class can have many students, okay? So that is what this means. Now, there's another term called cardinality. Okay, cardinality refers to the degree of relationship. Okay, this is the number of instances, a number of actual objects that one table can exist and another table. Okay, so kind of back to this one here. Many-to-many, okay? So the cardinality looks something like this. So here's another table I pulled from the video. Okay, so again, the notation has to do with the actual number itself. So these symbols here kind of give you the meaning of that here. So we say had exactly one, this diagram doesn't show you the detail, but so we say this book here has exactly one author, right? Although entirely not true, but again based on your business rules, we can rule out and say that requirement is a book can only be written by one author. Right, so it has one author and that is the cardinality. So it would be like M colon one. So the author can write, can have many books, okay? So it has more than one book or it has none also. Okay, another thing that you will run into is you have what's called repeating fields. Like these right here, the subject one, subject two, subject three, subject four, right? You don't want to do this in a database design. For example, like if it's a book, if it's a person, you know, I have like a phone type one. I can have another phone, home phone number, my cell phone, my landline phone, my business phone, my car phone, right? What else? But later on, if I add more phones in there, you have a problem because you have to go back and then modify your table in that way. You might have a problem because your table already has data. If you add, if you make changes to, if you make structural changes to your table, it might cause some problem. So you have to design that early on to make sure everything is correct. So we have a something like this. It's better off to move these out and create another table for that we'll call it phone or telephone. And then in that telephone, we can list the type and then the actual number. Then when you know, link that to a particular author or a person. Okay. So that way it's, it's not problematic. We can do that later when we do a normalization and unit seven. And then again, if you look at the names here, the name convention. So all the foreign keys and primary key, all the key should be all capitalized like this. And then the, the column names. As you can see all using the camel case in this example here. You can use camel case. You can use the underlying. All over case like this fine too. The entities, the tables are usually singular noun only. Okay, although you will see people use like, you know, scores and points and users like the convention is to use only singular because we're referring to a single instance of that table. Okay. The word itself is plural, but it's, you know, it's single, like series, right? It can be used in singular term as well.