Hey guys, welcome back. In this video, we will start fleshing out or putting in more tables into our school management database. Now, coming off the last topic of relationships, foreign keys and normalization, people generally say that Oh, they don't know where to start or how to start. They know that stuff needs to be related, but then sometimes they get it in the wrong direction. Now, when I'm designing a database, what I like to do is start with the tables that I know are atomic. When I say atomic, I mean that they have no dependencies, and they can exist entirely on their own.

So we already started that process here with our school management database, where we said courses and students now a student can exist entirely on their own the student is the student right? The student can be there, technically without Of course because if they pay the school fee for the general fees, they're allowed To go on the campus, but they may not be taking a course. So the student can exist with no association to a course, just the same way I can create a school and I am offering courses and I don't have any students just yet. So of course can exist on its own and and that is a that is something that you can actually employ when trying to decide what is atomic or not think about it in a real life scenario. If in real life, this element or this entity can exist without dependency on another one, right.

So that is the first step. So when designing a database, I always start with atomic elements, those that can exist by themselves. So we're actually just going to create about two more tables for this section. To begin this concept, I'm going to go ahead and use the editor and I'm going to create another table and this one will be for teachers. So We all know that we always start off with an ID primary key, you can see ID or just teacher ID, whichever one, it depending on your naming convention. And we'll give it the type of int.

And of course, we go down here are professionals that this by law, so you know that we just go and make it's the identity. And we also flag it as primary key, which we can just right click and say set primary key. So I'm just going to go ahead and fill out the details of this. Give me a few. Alright, so I filled out some columns here, I gave the teachers record a first name, a last name, and the date joined and I use the wrong data type here. So I just say a date time.

And of course, you could just say dates. So he's gonna say date, and then what we would be tempted to do and I could completely understand if you'd be tempted to put a course ID or associate this lecture. feature with a course right here. And of course, we're talking about foreign keys and primary keys. So you're thinking that, okay, so clearly a teacher has to have a course teaching, which is, which is fear. But then I teach, I can also teach many courses.

So that is something you need to take into consideration because then if we put an ID here, for the course ID, then we're, we're only associating this teacher record with one course at a time. And so if this teacher is teaching another course hold, we accommodate that we would have to repeat all of these details and set another course ID, which is not the optimal way to store our data in a relational database setting. All right, so that is one of those caveats that people would run into where they would try to put the foreign key at the wrong place because really, and truly the teacher or like I said in the beginning can exist in my school. Not be teaching because maybe as a free semester, maybe he's a specialist, I don't know. But I can have a teacher, I can hire a teacher. And because I have no students, I'm offering courses but I have no students, then this teacher is not actively teaching a course.

So I'm going to leave the teacher table like this. And save. Okay, so now we have three tables, I can just go and refresh. And I have three tables. I have my courses, I have my students and my teachers. Once again, they're all atomic.

So you probably say, Okay, so how do I relate the teacher to a course? No, let us explore once again in real life. I have a school right now. I have a course when I created this database course. The course was there. Yes, I was a teacher.

And what I had no students, what you did was enroll into this course and then the platform that you're learning This course on then said that me trouble Williams, the teacher, yeah, it will be associated with this course because I have many courses on this platform. And you the students will be associated with this course with this teacher. And I'm sure you're doing more multiple courses on this platform. And so that kind of relational relationship needs to exist between the course the student and the teacher. So setting a foreign key in any one of these tables would not fully accommodate that kind of three part relationship because at some point, something will have to repeat in these three tables with details being spread all over the place. What we want to reduce is a spreading of details in multiple places.

So when we see this situation we're trying to associate two tables with each other will lead to some duplication of details in the long run, what we do is have another table that is going to facilitate these relationships. All right? So what I'm going to do is create a new table and call it enrollment. So I'm just going to say ID, you get the primary key, and we make it identity. Alright, so it's auto incrementing. And then I'm going to have and once again, let me just save this table for now.

So we know we're working with this is enrollments. So an enrollment can only exist if we have a teacher with a course with at least one students. And then we have an enrollment because the student is enrolled in a class being taught by this teacher. Right? So then we will create teacher ID. So this is the person teaching and it's teacher ID.

So it has to be an integer. All right. I'll leave Based on our designs, so far, we have student ID, because the teacher is teaching the students and I'll just keep my casing consistent. And then we have the course that these that this teacher and the students are both going to be involved in. Alright. And that is the beginning.

And I will save this quickly so we can get it updated. That is the beginning of the creation of a foreign key. Now there's another step that needs to occur. So now we need to actually have the relationship. So yes, we created the foreign key properties, but they're not foreign keys yet because we need to actually establish the relationship between this and the teacher table. And then that is what will give us what we call database integrity when you know that the relationship and the constants are there.

Once a constraint is there, then teacher ID can never ever have a value that is not present in the teacher table. If I have five teachers, and they are numbered IDs, one through five, then teacher ID can never ever get a value of 10. So as it stuns that can happen because there are no constraints, there is no actual relationship. It's just a record saying, Oh, this is the teacher ID, teacher, Id should go here. But once I create this relationship, we set up the constraint how we discussed constraints in our design, that that's a limitation to what can actually occur in this column. And what we will do know is actually just right click on the first one, which is teacher ID, and then you'll see that they have an option relationships.

That brings up a dialog box, which allows us to add, all right, and then that creates some generic thing I use the enrollments enrollments, that's not really what we want. So the name of the foreign key here usually indicates the tables that are related to each other. So we want his enrollments to be related to teacher. So we'll click on this one. And then we go over to this side and drop down the tables and phone specifications. And we can just click the ellipsis button here.

And then that brings up another dialog box, which is reiterating our name. And the one is here for me, because I had started it before. So this is what yours would look like. And that's fine. And then what we want to say is, what is the primary key table, no door call, or primary key for teacher ID is stored in our teacher table. So our primary key is coming from our teacher table.

And the column that has that primary key is our ID column. See that so when I switched it to teacher, it gave me the drop down, and I select ID and I want the ID From teacher from the teacher table to be directly associated with which column in the enrollments table. So it's already it's agreeing, oh, it's the foreign key tip, because it knows it's a foreign key table. I'm initiating this relationship creation from the foreign key table. So it's going to meet that assumption, but it doesn't know what columns. So I'm going to go ahead, and I have the responsibility of naming my columns such that I can know exactly what the relationship is.

All right. So there's no direct stipulation that teacher ID has to be named that way. I could have said Puppy's ID, but that would be very bad practice on my part, because later down the line, if somebody inherits this database, they would know why puppies is related to teachers, right? So I kept call the teacher ID so that if I did this work, fell asleep and looked at it the next day, I could remember that teacher ID was earmarked to be the foreign key column for my teacher relationship. So teacher ID Here in our enrollments table will be directly related to the ID column from our teacher table. And then I click OK. And then you probably just want to update the name here just to see enrollments, teachers, just so that if you look at it tomorrow, you'll know that this foreign key is between the enrollments and the teachers.

And then you can click Close. All right, and then you see it made a change, and it's asking us to see. And then vice when we are when it asks us to see, it's telling us that we're about to make some changes to the teacher and enrollment. So that means it's making some structural change in the backend to associate the teacher and the enrollments. So I can just click Yes. And that will happen that that will be accepted.

So I just go ahead and do that same activity for students ID and I'll do them slowly. So we have The first one, and then we can just go ahead and add. And now we're going to do the same steps where we just click on our ellipsis button in the tables and column specifications space. And then this time we're doing students ID. So I say I want the primary key table to be students. And then when I drop this down, you'll see all of the student columns, but I know I'm only interested in the ID.

And on the foreign key side, I'm only interested in students ID. And so I click OK. And then this one was automatically named, because I didn't edit it. So that is what should happen. That is what probably happened for you and teachers. And that is what really should happen. So once you set up that relationship, we will actually rename it accordingly.

All right, and then we noticed that it's the same step, regardless of which one we click so even if we close this and then do it on the other one, we're still going to get this dialog box. So what I'm going to do is stay here and just go ahead and add another because I know I have a third one to ask. So I just click Add, and that gives me a third foreign key relationship. And then I'll just click that. And then this time we're dealing with course, so courses and then drop a donor we see course ID. So you see that sometimes I have ID sometimes Of course ID.

So in the courses table, I could have said Id, but I think I explained earlier that you know, it's can be used interchangeably. It's up to you and your pattern and your standards. And so courses course ID, and in the enrollments table, the foreign key column is course ID. All right, so we see a direct match there. So sometimes I don't like doing this because having course ID in the home table and and in the foreign key table can lead to confusion at times. But so I usually just use ID as the ID column.

So we can just go ahead and continue. Click OK. And so we have our three foreign keys and The Romans the courses, enrollments, students and enrollments, the teachers. So that's saying that the enrollments table is now directly related to courses, students and teachers. So once again, the whole purpose of adding those relationships is to set the constraints between what value is allowed here, relative to what value is available here, if the value is not available here, meaning there is no course with ID 10, then the enrollments deal with cannot have 10 in this column, because this column is directly bound to the ID column in our in our periods table. Alright, so just go ahead and save this and you'll see that it is trying to make changes to all the tables that were involved in that relationship creation process, we click Yes. And voila.

Now we can refresh the tables and see our newly created table there. And if I drill down then and click on the keys, then you see that this enrollments table actually has four keys, all right, but then if you take keen pay keen attention to the direction of this key, you'll see that this one is pointed to the left, and that's the pk. So that's showing that's primary key. And then all the others are pointing to the right to show you a foreign key in SQL, express Destro, they do not have that capability to show you the database diagram, you may have to download that separately or use developer to get that functionality. But at the end of the day, you can always just ensure that those keys are there, and you know that those are created properly. So I'm going to add one more column to this table, and I'm going to call the grid.

And the reasoning behind this is when we have a student doing a course he needs a grid, right? Well, then they're all atomic, so I can put greed in the course because there are many students during the course. To that, that wouldn't make sense terms of travel agreed in the courses table. And the student is doing many courses. So he's been taught many grades. So it doesn't make sense me trying to put the grid on the students record.

So what I will do is just put it in the enrollments section where I know that the teacher who is teaching the students through this course is going to give them a grade for this course. Alright, so that is that is the kind of thinking you have to employ when designing a database. So once again, this enrollments table is going to have the teacher who is teaching the students and many teachers can be teaching many students at anytime, and they can be teaching them multiple courses, right. And so for each instance, where you have this specific teacher teaching this specific students in this specific course we give them this specific breed saving if they repeats, we know that each record is going to have a unique greed tool. These three values. So I can just give that in char, I'll just leave that in char and the grid would be a little grid.

So I'll just see one, let me see two, because it could be a minus someone to give them two characters to put in that grid. And I'll save changes to that table. And so we have that relationship table, this table is a bridge to allow these three tables to be related to each other. All right. And there are situations where you have, you know, you have a table and you're going to put in a foreign key in that table. So this was not one of those scenarios.

So it's not a one size fits all, you don't just look at one relational database and then say, Oh, I'm going to use those exact techniques to build my own database in the school management system. The different rules apply to the previous system, the previous database that are shown in the last video where I was doing predictions For worth of matches, alright, so you just have to understand the core concepts and apply them as you go along. So once again, is this entity going to be atomic? Or is it going to have dependencies in this situation, of course doesn't have a dependency student doesn't have a dependency. And a teacher doesn't have a dependency or can exist by themselves. However, for an enrollment or occur, I can only have a record of a teacher teaching my students and this course through an enrollment and so my enrollments table has the dependencies on the teacher table, the student table and the course table.

And while it has its own columns afterwards, so you could always flesh this out an add on date of enrollment or date of start date and an end date. So this would actually creates an instance of when somebody being a student was taking a Class being taught by this teacher, the start of the semester, the end of the semester, those kind of things you can always put into this, but it does have three dependencies on three different tables. Alright, thanks for watching. Do remember to leave any feedback or ask any questions if you are still unclear as to what was going on here. Be quick to reach out