Hey guys, welcome back. In this video, we will be working with our school database once more. and in this situation we will be modifying or altering the table to support foreign keys in some tables, we will be adding more tables to this database. And we'll just see exactly how we go about using workbench to facilitate all of these modifications that we're about to make. So in our previous video, we saw that when we were using our designer view, and we went ahead and use the relationship tool to create a relationship, even though we had already designed the table with a column that we had earmarked to be the foreign key column, the designer went ahead and created its own. So we could actually just get rid of this column out of the Orders table.

And it wouldn't be detrimental for anything because the designer gave us its own column for those Primary sorry for the forum. Foreign Key. However, when we're using this design view with an already existing database that we want to modify, the onus is on us to actually create these rules, these new columns sorry, and actually establish them as foreign keys. First order of business would be to run the scripts that are included with this video, and go ahead and insert some lectures and some courses out of this real courses quickly. So I took the liberty of entering some courses. You can enter your own if you wish, but I gave you the script so you could expedite that process.

And now we need to take a stock of what we have and what needs to happen. Now we're doing a school database. We already have three very important entities for any school to have. We have students, we have lectures, and we have courses so we have people to be taught People to teach and what to be taught. However, what we don't have is any way of associating these three entities with each other, because a class has all three elements in a class has students a class also has a lecture or at least one lecture and the class is centered around Of course. So that means we need some form of entity that will embody class which is going to have the associations for the students to the lecture to the course being taught.

No, the lack of diagram or the visual representation might be kind of a deterrent or discourage discouraging factor when dealing with us, modifying tables and putting in relationships, but it is something you will have to soldier through and learn to be comfortable with as long as you understand ization or rather how you go about associating an entity with dependency, then you should have no problem. That being said, in this file, I am depicting something that just occurred to me in talking about how we're going to include additional tables to facilitate the relationships. No, I did say a class would be a merger of a ledger student on course. But then it occurred to me that if I have a one table where you have many students, so you're going to have many students IDs, and then you're going to have one course that mean, all of these students are taking on to one lecture, then you're going to have many IDs and repeating courses and lecture IDs for many students.

And so that that while it could work, to me, that's just kind of unnecessary repetition. And I must warn you that this Kind of a diagnosis of what is unnecessary repetition can lead to what we call over normalization, where you try to breed on one table into too many small tables, and then end up with too many touch points and potential points for failure and break it down by data. So there is a middle ground and there's a balance that one must have when breaking down what needs to be related to watch and hold the facilitate these relationships. But in this situation, because I don't want the lecture and course ideas to repeat so much. I'm going to have one entity called class which is just meant to have the lecture and the course or courses so many lecture IDs and mini course IDs. So this is a link or table between the lecture table and the courses table because one lecture can be teaching many courses and this same course can be taught by many lectures, so we can have three lectures teaching into the programming.

And those same three lectures are teaching other courses. Alright, so that's why I'm creating that linker table just for these two. And you could push it a step further if you wish. And you could include a room, which would then because then if a lecturer is teaching a course, then they must be teaching it in a room. So then you would put in a room entity, which would have an ID and maybe a name. Alright, so you have a room, and a room stable, or entity.

And each room gets its own ID and its own name. And so a class would be a lecturer teaching this course in this room. And I mean, the ideas are just coming to me at this time. All right. So that that is essentially what the timetable looks like. All right, a timetable is going to show you who is teaching what, where and at what time All right.

And then an enrollment suggests that a student saw a class that he or she is interested in. So the student which will be referred to by ID and where I have referees that is just a foreign key reference. All right, and that's just my notation. This isn't necessarily standard. I'm just doing this to depict something too. Alright, so a student would see that Mr. Let me look at my lectures, Mr. Harold Benedict, Harold, is our how identity truther is teaching introduction to programming in science room, one at 4:30pm.

And so students one is interested in this class. So we just reference the class by ID and associated with that student. And so instead of having two columns repeating, which would have been lecture on course, as many students as subscribed to the same class, you have many students at IDs referencing one class ID. And then we can always look at the details of the class by going back to our class table and looking for that ID. And then we can further see the details of every other referenced entity by going to the original table and the candidate ID. And as we move along, we will be looking at how we go about extracting details of related data.

All right, so for now, we just want to set up this kind of relational system in our already existing database. So the first table I'm going to create is a class table and I'm just going to use the editor to do this, I won't create the script file, so I challenge you to either follow along or try to do it on your own. And I'm calling this one class or classes because I like giving my tables the plural name. Alright, and the first column is an ID column which is primary key or for implementing, and nodes that you don't always need a primary key auto incrementing when dealing with a link or table, so this is a link or table, he really doesn't need its own ID. But I'm going to practice what I preach. And I'm going to put an ID, and then we have a lecture ID.

So, lecture on a square ID and this is a naming convention because we need to be able to just look at the column header and know Oh, that's what it is. So by just seeing lecture on the square ID, I know exactly that if I see any value here, it is referencing some lecture in the lectures table. Also, the data type must match. So because our primary keys are all integers, in at least in this situation, because people do use things that are not integers for primary keys, but in this situation, whatever or ever situation, whatever the origin of the the type of the primary key is The foreign key must be the same. so in this situation we have int. Alright, and then we repeat that beat, I'm also going to set this to not know.

Well, I'm actually in the new does not as as I'm going to make it nullable. Alright, so that means we can look at the timetable from time to time and see that a class course is scheduled to be taught in a certain room at a certain time, but there's nobody assigned to it just yet. So that's another design consideration when to make something low level. and in this situation lecture ID is nullable. So courses on the score ID and once again, this must match the data set. And I'm going to start with those two I did include room and time but then that would require me to create the room table and and then we can put in the time, just the same.

Alright, so I put in time and I made it datatype time and remember if you select it from the drop down or move the parentheses, and then I ended up with a random column that I'm just going to right click, and delete. All right. So that is essentially it. And I'm going to meet these two not lovable. So our class can be created without at least the course and at least the time it's to be taught. But we can be still searching for the lecture even while we have scheduled look class.

Now in order to associate these two columns with their respective primary key tables, I'm going to go down to the bottom parts, the bottom set of tabs in this editor view, and I see here that I have columns, indexes and foreign keys. So this brings me to a new screen where I am asked for a foreign key name, and then it talks about a reference table. Alright, so the foreign key name for me, I usually give it a name that helps me to know which two tables are involved in this relationship. So this will be classes on the score lecturer, there is no real, there's no real stipulation as to what you must name it. This is just a practice that I use once again so that if I look at it tomorrow, I can exactly know what this foreign key meant and what this this relationship is for.

So after putting in the foreign key name, I No need to say which table is being referenced. And there are a friend's table in this situation because I named it classes underscore lecture, we're already modifying the classes table. So that means the reference table must be the lecturers table. Alright, and then over to the right hand side, while having this this foreign key entry still selected, we'll see the list of columns available to us because of what we're creating. In our classes table, know the column to be to be the reference point from our classes table for the classes to lecture relationship would be my lecture ID. So I take that, and then I go ahead and let them know that a referenced column meaning which column in the lectures table should this reference.

And that's easy, because it's the ID. So the lecturers that the ID sorry, from the lectures table is supposed to reference the lecture underscore ID, from our classes table, and that foreign key, that whole relationship will be called classes underscore the lecture. All right. And then to the right, we see that we have foreign key options aware on OBS or undelete. It says no action really great. No, but then you can change it to cascade or strict or set no meaning that if so, thing on either side of the relationship goes missing.

Or rather if the source the if it gets augmented, meaning if the source data gets deleted, you delete the lecture, then do you want us to delete all of the associations with this lecture? So everybody who has some relationship with a lecture table, those records will also be deleted? Or do you want me to just set that value too low? Or do you want me to restrict what you can do? Alright, so no action means that if if anything happens, just leave the data as it is. Now we'll repeat that feat with the next column which is courses.

So I need a lot of foreign key name or relationship a lot. This is classes on the score courses. And this time or referenced table would be school underscore DB dot courses, and then our column that should reference that is horses. Id and their friends column will be course ID. So you see that the names don't really matter much is just a matter of you keeping up with whatever you're doing and whatever the design is. And if you have a standard stick to the standard, alright, so I used just simply ID for the literacy that I use course ID for the courses table, but either way, the name is irrelevant.

The fact is, either one is acting as a primary key in its respective table. And I am making sure that I'm referencing them with the foreign keys in my resulting dependent table. All right, so when I'm satisfied with my design, I just set up the foreign keys, I have all my columns and if I go back over, you see, they have that red prison to the side showing that okay, there's a relationship here, one is filled where one is blank and the blank one is because I'm allowing it to be loadable whereas this one must exist for recording But once I'm satisfied with everything that I've done, I can just click Apply. And then that will generate this block of code, which actually recreates the table so to speak. Now we're seeing that we have a bit more code here, because yes, we should be familiar with all of this.

But then we're adding what we call an index, which is a high speed lookup point on a table. So a primary key is always an index. And by default, any foreign key will also become an index. All right, and then we're adding the constraints. And there is a name that we said classes underscore lecture. So the foreign key will be referencing the ID column in the lecture stable, and it just fills out all of that so you can see this and NP and copy and paste it in another editor and just assess the code that's been generated.

But ultimately, we can just click Apply, and then it will go ahead and execute that. script and alter our tape will create our table with those and it will essentially establish that referential integrity that is implied by our foreign keys. Now the next table that we have on our reader is enrollment and enrollment has a dependency on students and class. So I'm going to go ahead and do the same thing, just go to tables, create table. Now went ahead and filled out the table name and the column names and the data types and then making everything not null or not nullable because it doesn't make sense to have an enrollment without a class being referenced and the students being referenced and that's putting error there. Alright, and then we set up our foreign keys just the same way.

All right, so using the same naming convention, I associate the home table With the primary key table or the foreign key table, the primary key table, and I reference the table accordingly and then the columns and associated columns. Alright. And then once I'm satisfied with all of that, I just click Apply, I can review the code, but I just click Apply once more and finished. And then what happened just know was that there was an all creation of our enrollment stable, and it just appeared over here. Now, the next thing that if you want a visual representation, you can always go to database and you can reverse engineer or school DB database into the ER diagram so you can see all of the lines and the actual relationships that we just created. And in our next video, we will look at how we go about inserting data because if you were paying enough attention to what we were doing all we have at this point in classes, and enroll lense is a bunch of columns that are just supposed to be taking numbers.

So let's look at enrollments, enrollments have student ID, which is an integer class ID, which is an integer and an auto incrementing integer. So we moved away, or we are slowly moving away from having these kinds of, you know, data rich and clear tables where we have lastname firstname gets over two tables where we're just looking at a bunch of integers. All right. And so if I try to type something here, that's going to give an error if I try to apply because clearly that data type does not much what is expected because there are integers, all right. And just the same way that I cannot enter a class or student ID that doesn't exist. Because I have classes that are bigger than my classes stable.

I have no classes actually. So If I tried to enter a class ID here, and I know I have a student, I have students with ID number one, so I can just put in student one. If I apply this, I will get an error, because it's going to tell you that it cannot add. Well, it seemed that the constraint field because the class ID column value must match some value in the classes primary key, the primary key of the classes table. So as a result of the fact that we don't have any classes right now we have an empty table for classes. There is nothing for surface and bring up classes quickly.

And that's empty. So there are no classes to reference. So there is no way I can enter an enrollment for class number one when there is no class number one, and that is what a foreign key and relationship brings to the table and that is what we mean by referential you Integrity where data cannot exist or data can't exist in the pending table without it existing in the parent table. All right, so our next lecture, we're going to look at how we go about inserting records in tables that are, you know, have a dependency on another table.