Hey guys, for this video, we will be looking at inserting records into tables that have relationships. So in previous videos, we would have already inserted some records into some of our tables. And I discussed the atomic tables versus the ones that have dependencies, or periods tables versus child tables. That's another expression you hear in database nomenclature or database terminology. So we have courses and we created some of them just right clicking tables, I'm going to edit top 200 because now we're supposed to be comfortable with our editor. And we should be proficient enough in SQL to be doing our SELECT statements if we want to see what's there.

But for expediency for speed, I'm just going to use the editor for most of these operations. So we didn't put in any teacher so I'm just going to go ahead and insert about three teachers. Alright, so I did three teachers and notes because they use the date as opposed to date time we don't see a timestamp on on the day. Joined column. So that's what dates looks like. I think we did.

Date of Birth had the timestamp, right? So I'm just showing you the different data types. Once again, beats may not be available in previous versions of SQL and in your corporation, you may not be using the latest one. So I'm just showing you all of the different scenarios so you can act according to your context. So we have three teachers we have a few students, and we have a few courses know what we don't have our enrollments, we don't have any teacher activity teaching any students through any course. Alright, so that is why we created the enrollments table so we could actually bind them together through relationships.

So I'm just going to edit the top 200 and then you will notice based on our previous design and and based on what's on the screen know, all that is allowed in these columns are integer values. They're just IDs right and agreed. We had an string value a lot for this, but these are only willing to accept integer values. So what this means is when we have a table with relationships, and we set up the relationship to be on the primary key columns, and the IDS have too much, and they're all integers, your primary key foreign key column is always just going to have the referencing ID. So the detailed record, and that was discussed in the first video of this series. So once again, we're not repeating the teacher's details every time there's an enrollment, because if john is teaching multiple courses, then we don't need to see john, john, john, john, john, john, john, every time you see john, of course, we're seeing john throughout the database, right, that would lead to messy data.

And that's a part of normalization where we're cutting out all of that repetition. So if you realize you're in a situation where you The details of something is going to be repeated more than once, then you probably need to look at creating another table to have that detailed information once and then what you do is only repeat that referencing ID to that detail information. So that is that is how normalization works, you will see things about first Normal Form second Normal Form third normal form. To me, those normal forms are important when you're looking at already messy data and trying to fix it. But for me, I skip past the normal forms. And I just say design the database in a normalized form from day one.

If you see repeating details, put them in a table for themselves, so they only appear once and then repeat the ID associated with for whatever it is. So that is what we're doing here. So instead of repeating the teacher's details, because john is currently going to be teaching the top three courses in SQL and PHP, we want to just Repeat his ID. So we'll associate the ID two with the courses. One, two, and three. All right.

And I'm going to give him one students for each. So Jody McIntosh is currently in all three of those courses being taught by john. All right. And the reason I'm doing that is the fact that we have IDs, we can't insert any Oh, I did, I did alone No on my design, so I just pop on over to the design quickly. And I did a little note, which means that we don't have to enter all of the values. The only thing that is really absolutely necessary is the ID.

So we can do that. So as it stands, we have given john with ID to the course the schools one of these the courses, HTML, SQL, and PHP, and let's just see, he has no students at the moment. So we're going to associate teacher ID two is john with course ID one. I can press enter and see it says it's an all students. He's also teaching course ID two. And he's also teaching course ID three.

All right? No, this kind of looks stupid. I can appreciate that because then you're moving away from clearly seeing who teach ID to john. And here no just looking at twos and threes and numbers right, though, from a human perspective, it may look foolish because we prefer to see letters or numbers, but the computers thrive more on numbers. So it's actually more efficient to get this number and find all the details in somewhere else than to have those details repeating every time and that will actually lead to a slow database and a slower application overall because I figure you're building your database here to use in an application. And that's point number one.

Point number two is that because you're using your database in an application, filling out these IDs would be much easier from an application standpoint. If you're not an app developer just yet, that's fine. But I'll just give a high level overview. But do remember that when you're when you would be filling out and creating these associations, you would kind of be dynamically associating the controls in your app with the values from the ID columns. And so you can pass that into your database quite easily through code. And you wouldn't have to necessarily manually come in here and actually be writing twos and threes and ones and twos.

All right, but because we're learning database in the context of database design, I'm just showing you exactly how this data goes into the database. So back to what we're doing. We actually have associated john with courses One, two, and three. So if I look at this enrollment record, I see john teaching course one, which is HTML. See, john also teaching course two, which is SQL. And he's also teaching course three, which is PHP.

And she has given all grades because well, quite frankly, he has no students. So I did say that Jodie is actually taking all three of those courses. So now that we know Jody has enrolled because she looked on the timetable and saw that john is teaching these are these are the three courses being offered by john. And once again, you could have put on two semester details and whatever. Whenever I leave the campus, it's your database, you can do the design, though Jody is interested and Jody is going to click enroll so she goes on to the up the school management up school management system, and she enrolls in these three courses are being taught by john So know her ID, which is three gets associated with john, and each course. So this is what I was saying about repetition.

Imagine if we had john, john, john, Jodi, Jodi, Jodi and HTML, PHP, and and SQL. And it would be very tedious when balloons into more than one and then you have to track all of the John's and all of the journeys. We can just say, which student is doing this with john, we just look for our students ID three, and we see everything about God in one place. All right, so it could get messed up if it's in more multiple places. So we try to keep that repeating data. One place, all right.

No, here's another scenario by Troy has decided to also do SQL and PHP. He's not doing HTML, he's only doing SQL and PHP. Now here's the beauty of using our relationships. We can actually repeat this so we know john is teaching that course. So we can just put in john twice. And I'm just doing this.

So we know Johnny's teaching that and we did say the students in question would be Patra his idea six, so we just put in six and six. I'm sorry something's going on here. Let me do that over. All right, so we're associating petroi with john once more so that out, and he's doing courses one and three only. Alright, so here we see teacher ID two. So we see john is teaching all these courses and he has two students so far in these courses.

So now we can track directly if we were to pull all of the HTML students that john is teaching, we could just look at all of the course ID one and teacher ID two and then we will see all of the student IDs coming back in that query. Alright, and we'll get into that. Eventually, we're just looking at inserting and we will look Add how we go about pulling reports because I mean, obviously, we're only using numbers. So it will take a bit more cursing of the data to actually accomplish viewing certain things. So I'm just going to use this time and I'm just going to fill out some more data. And I'm going to put in about 20 records of enrollments and we'll reconvene.

Alright, so I said, so internality 11, I'm sorry, it's kind of tedious and annoying, and I'm sure you're, you're experiencing the same thing. But I don't know if you've done this as yet, but I'm just going to display something to you. So do remember that by setting up the relationship, we would have enforced certain constraints. The we shouldn't be able to put in a teacher ID that does not exist in our teacher table. So if in enrollments I tried to put in the value 10 and then I just fill out the rest and then move on to the next line. Then it will tell me nothing happened.

It was not clear. into it. And the error message is that it conflicted with the foreign key constraints, the conflicts that so it's not really telling you Oh, there's no value 10 in the parent table, but that is essentially what it is what is happening in the constraint is that no teacher ID value can exist in our relationship in our foreign key column that does not exist in the primary key column of the primary key table. Alright, so that is what that that brings up both. So if we were repeating details and seeing john all the time, then somebody could come in and say, oh, spell his name Quan, because they're trying to be cute or funny or something. And then that would lead to some data integrity, because then we will be wondering who is this one what Where did one come from, and then we really wouldn't know who this teacher was.

So by using foreign keys in a relationship where enforcing certain data integrity across the board to ensure that no value goes in without It being vetted by a second source, so to speak. So I can assume this little one and press Enter. And when I'm pressing Enter, it's telling me another arrow. But then this time it's with the students because I don't have a student with ID two. All right, deleted that one. So I'm just going to change that ID to 12.

And voila, so I have a rich mix of students and teachers, and it's all looks confusing by this point. If I went all the way up, it would have been even more confusing, and rightfully so. So, I mean, inserting isn't the cutest process and procedure and even looking at it, it's not very intuitive, as at least it's not as intuitive as looking at the teacher and the students and the courses. So what we'll do next is look at how we can go about pulling meaningful data from this table when it looks like this.