Okay, welcome back. We're talking about the fourth normal forum violation. So, this one is a bit odd. What it basically is all about is I can't have a scenario where I have one row of information that really points to multiple rows of information. I know it sounds a little weird. Anyway, this is called a multialue dependency. It's not the same as the multicolumn problem. And so an example is a a an employee table that has skills and languages. Okay? Employee could have more than one skill and more than one language, but there's no implied relationship between the two. Let me explain what this means. Okay? So here we go. Have an employee ID, a skill, and a language. So employee number 101 speaks English and he's a plumber. He also speaks Spanish and he's an electrical electrician. So do you see a problem here? What if I was to write a query that said, "I need a Spanish-speaking plumber." Would I find a Spanish-sp speakaking plumber? I would not because I asked for where skill is equal to plumbing and language is equal to Spanish. See how that query works on a single line and my data is spread out over multiple lines. So, I cannot find the information I need. This is bad. Okay. Now, you might look at this and go, "Wait a minute. you told me the one and only one rule like an employee ID given an employee ID I should have one and only one skill and you go no so it's obviously a a normal foreign violation number one no the first normal foreign violation requires all parts of the primary key to be used as the identifier okay well I don't even have anything in here that's not a key so the first novel form rule of kind of sort of looks like it does not apply here because I don't have any other columns any other columns that are not part of the key. All right, let's look at another example. Um, and the the way you solve this is if I'm going to if I uh had another skill. So, employee1 is now I don't know a carpenter. How many rows of information would I have to add to this table to make that work? I would have to have carpenter English and carpenter Spanish in order to make this thing work. So, inserting one row of one piece of information requires me to insert two rows of data. Okay, that's a fourth normal form problem. Fourth normal form is all about I add one piece of information, but I can't do it. I have to add two rows. Okay, so here's another example. Here's Al's Pizza and Pizza Variety and delivery area. So, so far so good, right? A restaurant determined multiple varieties. A single restaurant could have multiple varieties and multiple delivery areas, right? Okay. So far so good. This is not a first normal foreign violation because everything is a primary key. But the problem is they're not related. So if I was to add a new delivery area to Al's pizza, I want to go to some other Cinderville, I would have to add a centerville for thick crust and a center for stuffed crust. So adding one piece of information requires me to add two rows of data. That is a problem. Okay. Okay. So that is that part in a nutshell. Now I've got one more thing to show you and then we'll move on. Okay. So here we go. Here's just a review of the things one more time. All right. So first normal form says everything must be related to the key. That's first normal form. And it's functional dependency that does that. And how do you fix the problem? Although right now we're not talking about fixing it, but I would break a table up into two pieces. So here's an example. I have an employee and I have an employee ID and a first name and a last name and a pay rate and quantity on hand. Like what? Employees don't have quantities on hand. So clearly that's the quantity on hand is not related to the employee ID. Cool. Let's go to second normal form. Second normal form always has a compound primary key. And in fact, if you don't have a a compound key, you can just skip this step. Move on. So primary key. So here we go. A part number, a warehouse, a quantity, and the unit price. Now, those are all legit, but the unit price doesn't change on the warehouse. The unit price is only related to the part number, not the whole key. So, the whole key is part number and warehouse. However, parts of it don't belong. So, the unit price isn't related to all parts of the key. All right. Third normal form. Everything should be related to just the key. No, no, no. No uh no jumping around. Okay. So, in this particular case, I have an employee and a department and a department supervisor. Right off the bat, there's nothing wrong with that. Given an employee ID, I should be able to tell the department and the department supervisor. However, isn't it fairly obvious that it would be better for the department to determine the department supervisor? So there's a little a leaping going on here, a little daisy chaining where this thing determines that thing, but that thing determines something else. And so that's a third normal form. This one, of course, is the one that's complicated. It's basically the same rule. It's just it took out a loophole. So if I have student and and major and then it's also possible that the advisor and the major could determine the student I have this over overlappinging candidate key scenario and so there's a problem. Fourth normal form is okay I've got an employee ID a skill and a language. So I put down plumbing English electrician Spanish. Does that mean he can only do Spanish electrician? No. There's no relation. There's no inferred relationship between the skills and the languages. Therefore, it has to come out. Okay, that is it in a nutshell. I hope this was somehow helpful and I'll see you guys in some future video perhaps.