1st, 2nd and 3rd Normal Form (Database Normalisation)

Hello and welcome to this beginner's guide on first, second and third

normal

form

when we discuss

database

normal

ization, so let's start with first normal

form

. This one is nice and simple. All rows must be unique and all cells must contain atomic values. What does that mean? It's actually quite simple: each row in the table must be unique and therefore there can be no duplicate rows in the table, so you cannot have two different rows where all the data contained in those rows is identical and we will search you will find an example in a minute and each cell should only contain a single value so you cannot have a cell in a table that has a list of values ​​in that cell which is a no-no and finally each value should not be divisible, which means that if you have something in a cell you can't divide it into more sensible parts, so let's take a look at an example of each of them, so each row must be like this, each room has been unique, so let's take a look here.

We have a takeaway order table here and for each takeaway order we have the customer name and a customer order, so for example, Bob Jones orders here, he ordered burger fries and coke, Fred ordered nuggets , lemonade and fries, and then Bob also ordered fried hamburgers and coke, okay, other than we don't know the difference between this line here and this line here, so we have no idea, for example, which order it was requested first, I have no idea at all, so we have to change. This is because they need to be uniquely identifiable and the way we do this nice and easy is to simply add an order ID column, so here order ID number one was Bob Jones and he ordered the favorites of the.

His me order was also Fred's and him. I ordered all of those and then the final order was Bob Jones and he ordered his favorite. I would sort it again there, so since we got these order IDs, that row there that it's now in is no longer identical to that row, let's go okay, so that's the first one. part of first normal form made second part let's take a look each cell should only contain a single value well yes as you can see here this cell here does not contain a single value it contains three items a hamburger fries and coke , in any case. they do these here these and this in this area the actual order that I do they all do so that's not good so what we have to do there is move them to a separate table so that each row only has one element and some alone, so it's going to look a little bit like this table here has an order ID in one column and the item corresponding to that order ID in there, so order ID one has a burger order ID, one it also has fries and an order id it also has a coke in there and as you can tell each element in this table is now unique and there is no doop-doop and multiple values ​​per cell here so that's sorted now that's the second bit Goods, third element, all data must be atomic or non-divisible, well, what's the problem, well, if you ever look here at the client named Bob Jones, you can see that Bob Jones is not atomic, you can further split into Bob's fur into his first and last name, and that's not right, because this turns it into some kind of compound value which is not good, it needs to be separated because there are different things, it looks like this, so you create a column for their first name, a column for that last name and you just put them in separate cells, it looks that good.

This is how to place a

database

table in first normal form. Now let's move on to the second normal form. Now the second normal form is not so much. All you need is no partial dependencies. Well, what does that mean? Well, what does it mean? First of all, the table has to be in first normal form, so you can't have a table that's in second normal form until you've gone through all those rules we just did in the previous one from first normal form. Do this first and then we should have all the non-primary attributes that should be completely dependent on the candidate key and completely dependent on the entire candidate key.

Well, what does that mean? It sounds a bit complicated, but it's not, let's take a look here. I have a list here of students, so the student ID, the ID of the course that student is doing and how much that course costs, so here this student is doing the course, then student 1 is doing course ID number 1 and that course costs 500 and student so look, student ID 2 here is doing course 3 and it costs 750 and student 3 is like fish guy 2 who is doing course 3 and here student 3 is also doing course three and it costs 750 well what is the problem with your table and the problem is that the free course has nothing to do with the student id so the course fee is a flat rate that only depends on the student id course, so this particular course, course one costs 500, course two costs thousands, course three costs 750 and As you can see here, it doesn't matter if it's student ID, student 2 is doing course 3 or Student 3 is doing the course for free for any of those students, it costs 750 for each one and that is because the course fee has nothing to do with the student.

ID now it's important to note that the course fee is not dependent on the student id and the student id is part of this composite key here of the student id and the course id so this is the composite key , but this bit only depends on that bit. and it doesn't depend on that beer and that's where you get this partial bit from because it doesn't depend on this part of the composite key, but it doesn't depend on this part of the composite key, so that's where we get the partial part from . So what do you have to do to fix this?

Well, nice and simple, all we do is remove this information into a separate table like this, so we have a table about course fees and each course ID has a course that you can tap on, of course. one is 500, of course, 2 is a thousand, etc. and in the other table we pretty much keep the same, but we don't need this additional information about the course fee, we just get rid of that column and just have the student ID and the corresponding one. Course ID, so student 1 is doing course one, student 1 is also doing close to student 2, is doing course 1 and course 3, etc., etc., so there is no partial dependency, it must depend completely on each of these parts of the compound key if We have one right, then that is the second normal form, the third normal form knows the transitive dependency, so it is similar to the previous one, but this time we're not talking about the primary key or the compound key, we're talking about something else, just like when you try to put something in second normal form, it already had to be in first normal form, well third normal form is the same idea , what you need to do is you need to be in second normal form, but of course that also means that, therefore, if you need to be in second normal form you also need to be in first normal form because to come back, anything Anything that's in thirds has to be in second, and obviously anything that's in seconds has to already be in first, so check all the top boxes. it can't be cheap curd, it can't be divisible, there can be multiple values, you can't have a partial dependency either, so all of those and then you also have to do this third, this other element here that is not a transitive dependency, again it sounds a bit strange, but all it means is that all fields should only be determinable by the primary or composite key and none of the fields can be determined by other keys there, so what does that mean?

Well, let me show you a nice simple example, here we go, so here we have a list of tournaments that I called from Wikipedia and here is ER, there is the broken name and that is the year the tournament was held and then we got the winner of the tournament and the date of birth of the winners, so this works fine, everything fits into the first and second normal form, but there is a problem with this table and the problem is that you can determine one of these keys by looking at another one of those. keys and you see which one is good, nice and simple, it is here, you can know who is the winner, what is the date of birth of the winner, by going to the name of the winner, so here you can go, you know that the date of birth of the winner It is this, this day. of birth because that's his name again he has that name so although it doesn't depend on these bits here you can't know the winners date of birth from the tournament name or the year you can know the date of birth of the winners based on the name of the winner. so that is not good, so this information as it is cannot stay inside the table, so what are we going to do?

We have to fix that and you could do that by having a separate table of winners here, so for example here We have in the tournament name the year and the winners, the winner there and we have a separate table that says Ok, Alfredson, your date of birth is this and we divide the two and this works because here the winner is It depends on the tournament, the name of the tournament and the year, because that tournament happens every year, so you can't determine who the winner is just by the name of the tournament itself and there are several tournaments in a year, which means you can't determine it. the winner just looking at the year you have to use both the name of the tournament and the year it was in order to know who the winner is so that fits perfectly and then here again the date of birth corresponds to that particular winner, there you have it , okay, so that's the first, second and third normal way, if you like it, please subscribe to my channel if you have any comments or suggestions for other videos, then send me a comment and well. good luck thank you very much