Okay, welcome back. We're still talking about the textbased relational notation. I switched over to Wordpad so I could actually show you how, you know, you can put things in underline and put things in italics. Okay, I think we got that. There's some examples on page 166 that, you know, show it. They have like department blah blah blah blah blah and employee. And so that they have have to have a way to connect an employee to a department. So, they're using a foreign key over here that points back to this guy. Now, in this particular case, I don't particularly like this, believe it or not, because their department, I would normally have the exact same name. If I called it department here and department name there, I would probably lose my hair. Okay? So, I don't do it that way. I I typically make these things the same thing. And here's one where we have skew data blah blah blah and then they have this u order number and then the skew is both a primary key because it's underlined and italics because it goes back to skew data. Okay, so we got all this. So, one more time, referential integrity means that if I'm entering an employee, I got a brand new employee and um I type in Bob, you know, employee number four, Bob Smith, and department is sales, but I misspel sales sales. the machine's going to come back and say, "I'm sorry, we're not going to be able to insert this record for Bob because there is no matching thing called whatever I type um in the department table." So, that's what referential integrity is all about. It keeps me from making a mistake. When I type in the department name, it has to already exist and it has to be spelled correctly. That's the whole point of referral integrity is to prevent garbage from getting into your database. It does a pretty good job. Okay. So, so h how do you find out if something is sort of like meets the rules of a relation? Okay. If you say it out loud, if I say a student should have one and only one first name. Okay. See how this kind of works when you when you when you say it that way? A student should have one and only one first name, one and only one last name, one and only one major. one and only one. Well, a primary major. Clearly, a student could have more than one major, but let's don't make things more complicated. A student should have more one and only one student type and only one phone. Well, okay, maybe they will have more than one phone, but again, let's don't make this too complicated. So, do students have classes? Well, of course they do. But do they have one and only one class? I hope not. Okay. So, a test for a relation that first rule for a relation is the one and only one. Just say it out loud. Given a stock number, I should be able to tell its description. Yeah. Unit of measure. Yeah. Unit price. Yeah. You know, warehouse. Yeah. Customer. No. Because uh an item in the warehouse may be associated with a customer order. But one and only one order. I certainly hope not. So, just say it out loud. One and only one. That's an excellent way of determining whether something is a relation. Okay. So, one more time. Does a student have one and only one first name? Yes. Does a student have one and only one class? No. Okay. That's how you make the determination of whether something is a relation. Good. All right. On page 167, they talk about normal forms. Okay. I know this is tough. Just bear with me. So, one more time, why do we do normal forums? Well, it's to reduce duplicates, but that's not the real reason. That's what it does. That's not what it what it's for. What it's for is to reduce modification anomalies. I I'll say that again. Modification anomalies. I'll say it a third time. Modification anomalies. Are you paying attention? Okay, good. All right. So the purpose for normal forums is to reduce or eliminate modification anomalies. So if I ask you in a test question, why are we doing this? You you say, oh well, we're doing this so we'll reduce modification anomalies so that when someone changes their name or moves, they only get one magazine and not three. Okay, that's the reason. Okay, good. I'm glad you're there. So, anytime data shows up twice in a table, you might have a problem. So, here's an example. So, here we go. So, here we have a table and it's got an item number. So, and an equipment type and its acquisition cost and a repair and a repair date and a repair cost. So, let's just go through it. Given an item, should an item have one and only one type? Yeah. One and only acquisition cost. Yeah, right. I mean, it cost $3,500. One and only one repair. No, it may be repaired multiple times. See how this works? Let's look at it from another point of view. Given a repair number, should I be able to tell the repair? Should it should a repair have one and only one repair date? Yeah. One and only one repair cost? Yeah, I'll go along with that. So, this table actually needs to be split right down the middle here because it doesn't meet those the rules of the one and only one. Okay. So, what what we'll end up with is a table that only has three column for an equipment only has item number 100, 200, and 300. And no duplicates here because this drill press got repaired multiple times. So, let's talk about a modification anomaly. Let's say you're looking at this thing and you go, "Oh man, we I made a mistake. That drill press is not $3500. It's $35,000. I made a mistake." So, you go into this row and you make that change right here, but it's wrong in three other locations. That is a modification anomaly where you fixed it in one place, but the error exists in another place. Okay, that's an example of a modification anomaly. Okay, so first normal form. The first normal form rule is actually pretty easy. The first normal rule basically says it's a relation. All you have to do to determine if it's a relation is one and only one. So is this in first is this a relation? No, it violates the rules. Okay, cool. So the first normal form is just in the relation and has a primary key. Now I can't see that they have a primary key here. So that's not really the point. So the rule is that everything in the table has to be directly related to the key. Okay? That's that's that's the the textbook way of saying it. That's not the way I say it. I say one and only one. One and only one. One and only one. That makes a little bit more sense to to me. Okay. The problem is that uh first normal form doesn't solve all the problems that that you could be have some additional problems. So we're going to go through a bunch of these types of so this there's a thing called functional dependency and it's going to apply to these things which we haven't gotten to yet. So just bear with me. Basically it talks about u that everything is related to just the primary key. That's the whole point of this a multialue dependency later on. U this is going to be one of those ones where in order to enter one piece of information I have to enter I want to add uh okay the example I always give is I have a table that contains uh what language you speak and what skills you have and so you put down English electrician but then you became a plumber so I have to duplicate that I have to have to insert uh wait Spanish speakaking electrician and an English speaking plumber, but it's the same person. So, we'll go through that. I promise you. And then data constraints. We're not actually going to do that. So, I I break it up into three categories, not the three that they have here. First normal form and second normal form are all about functional dependencies. Third normal form and up one called BCNF. We haven't got there yet, so don't panic. It's called transitive dependencies. And then fourth normal form is the multialue dependencies. So, those are the three that I'm going to be dealing. I am not going to be worrying with five NF or DK NF. Okay. So, so far so good. All right. Here we go. Second normal form. Are you ready? Here it goes. First of all, these things are cumulative. So, if it's second normal form, that means it's already in first normal form. All right. So, these things are cumulative. and says the rule is I'm going to go ahead and read what it says because it it all non-key attributes appear on the entire uh primary key. Let me let me give you an example. What what this means is that um if I have a compound by the way second normal form is always going to have a compound primary key. If you do not have a compound primary key, there is zero chances in hell of having a second normal foreign violation. You can just skip over that one and go to three. Okay, I'm just saying. All right, so here's an item in a warehouse and a quantity on hand. It kind of makes sense because I have, you know, item number 14 appears both in the south warehouse and the main warehouse. Okay? And item 15 appears in both warehouses. So, for example, we don't have any plastic sheeting in the main warehouse, but we got 40 of what in the south. Okay. All right. So, here here's how this thing works. Second normal form says that each one of these columns, the non-key columns, has to relate to both parts of the primary key. So, let's just look at this one. Um, does plastic sheeting Okay. Does is that always going to be item number 14? Yeah, certainly looks like it. Does it matter what warehouse it's in? Is isn't the description only related to part of the primary key? In other words, the description has nothing to do with what warehouse it's in. Okay? Do you see what I'm looking at? So if you have a a compound primary key and you have a column that only talks about just one part that is a second normal forum violation. Okay. Okay. So let's look at some examples here. So here's that student. Here's a student activity. Okay. So given a student, should I be able to tell what activity they signed up for? Yeah, I think so. And uh what the fee is? Yeah. Okay, I guess. Um so what would be my my probably my primary key would be uh the student and the activity together, right? Because otherwise I would have duplicates, right? So let's assume that I've got a primary key built a compound primary key with student and activity. Does the activity have anything to do with the student? The fee? No. The fee is always going to be $65 for golf. Doesn't matter who the student is. So, that would be a violation. Okay. Here's how you would fix it. You would go go in here like this and then separate out the activity. Now, student and golf. Got it. But if you want to know what the golf fee is, it's actually in a second table. Okay. This is how it's done. All right. This will be an example of a second normal foreign violation. Okay, good. All right, I'm going to continue. This is I know this is an awful lot to absorb, so just go through it. You may actually want to watch this video a second time. Third normal form on page 171. So a third normal form, first of all, since it's cumulative, it has to already be in second normal form. But they found out that there's a problem here and that is what happens if something depends on something else. Like for example, this student is assigned to a particular student I mean a building. Okay. And then the building has a fee. So it looks like that student ID would be the primary key. Good. Right. No duplicates. And it and given a student, should I be able to tell what building they're assigned to? Sure. Given a student, I should be able to tell the the building fee. Yeah, I go along with that because a student I say it out loud. Student has one and only one building. Yeah. One and only one building fee. Yeah. Okay. So, I'm not violating the vi the the rule for relation. But isn't it fairly obvious that the building determines the building fee and not the student? So if this was the only primary key we have, no compounds here. The only primary key we have, then this is like a hop. The student determines the building, but the building determines the fee. So whenever you have a little hop in there, that's called a transitive dependency. And transitive dependencies is third normal form violation. Okay? And so the way you say it out loud is everything is related to just the primary key and nothing else. In other words, this appears to be related to that and that's not part of the primary key. Okay, this kind of sort of making sense. Okay, this is a good place to stop. Let your brain cool down a little bit and we'll continue