Database Normalization in SQL - 1NF, 2NF, 3NF, 4NF - SQL Training Online

Today we are going to talk about normalizing first, second, third and fourth normal shapes and we are going to transform this table into this, okay, so let's start making our normal shapes. I have an example here with a sporting goods company that sells baseball, basketball and golf equipment this table here is a customer order table and the first thing I want to start with is some small examples where I have broken this table down so that we can understand the different concepts, so there are four normal forms that we are going to use. Review, everyone has their own little definitions, the first normal form has a big definition, but really what you need to pay attention to is that it has to do with atomic values ​​and I'll tell you what that is in just a second second normal form. has to do with compound keys and partial dependency, the third normal form has to do with transitive dependency and the fourth normal form has to do with multiple value facts, so let's start with the first normal form, so these are little pieces B of our big table that explains our Let's show some of the problems we will have with the first normal form, so in example number 1 we are dealing with atomic values.

What that means is that in example number one you have two columns, you have a customer column and you have a The first normal form of the customer address column says that you can't store two different things in the same column, so which in this case we have the customer address and the customer email address all in that same column, so we need to separate them for first normal form, so let me move on to the responses and this first table. What we have done is take the customer address and split it into two columns, so now the customer address and email address are in separate columns in this table. now it satisfies the first normal form, so let's take a look at example number two in this example, instead of having two different things like physical address and email address in the same column, we have a multi-value data there, so these are customer email subscriptions. and Bob Smith is signing up to receive two different email letters, one for baseball and one for basketball, so to separate them, we'll get more rows if you look at Bill Smith.

Bill Smith has two different customer email subscriptions, one for golf. one for basketball, so if you look here to satisfy the first normal form, we separate them into two rows, so now Bob Smith has two rows, one for basketball and one for baseball. Jill Thomas only had one moment to start, so she's still just one. row and Bill Smith now has two rows, so now this table satisfies first normal form, so example number three is what you'll find, sometimes two, it looks good because in a column there are no extra values, just There is a value, but it is something like that. a trap because these three columns actually form a concept, so they take three columns that are actually a group of male catalogs, so in this case a male catalog means that they will receive a physical catalog sent to their physical address. so in this case, Bob Smith will get the basketball catalog, the golf catalog, and the baseball catalog.

Bill Smith will get the golf and baseball catalogs, so to satisfy this we need to combine them into one column and add them as rows, so you can look what we've done here Bob Smith now has three rows, one for each catalog he'll get, and Bill Smith has two, one for each catalog you will get, so the first normal form has to do with atomic values, now we move on to the second. normal way and it has to do with composite keys and partial dependency, so composite keys you first need to understand what that means, i.e. that more than one column is needed to achieve row uniqueness, so in this case we have the order table and I have simplified it quite a bit, the order needs the customer to have the product and the date to make a single row.

In this case, Bob Smith ordered two things, he ordered a basketball and he ordered a basketball and a bat on the same day, but the key to this for each record will be the customer, the product, and the date of the order, so what is called a composite key. Now partial dependency means that the column only depends on a part of the key but not on the entire key, so in this case we need to check the other columns that are not part of the key and determine what dependency they have, so that The first customer address you need the three columns to determine the customer address, no, the only thing you need to know the customer address is to be a customer, so the customer address depends only on the customer, it does not depend on the product and it does not depends on the order date, that's why it's called partial dependency, so this customer address has a partial dependency and it doesn't satisfy the second normal form, so we have to fix that, let's check the other columns and see what else we have , the manufacturer of the product, so the manufacturer of the product, Spalding, in this case it depends on the customer, no, it depends on the product, yes, any product we have has a Correct from the manufacturer, it does not depend on the order date, for so the product manufacturer only depends on the product that is part of the apart key, so it's a partial dependency again, so we need to get rid of that column to satisfy the second normal form and now the final column. quantity, in each of these order lines, in this case Bob Smith bought one, but that quantity actually depends on the entire key, so we need to know the customer, the product and the order date to determine the quantity, so the quantity belongs here, so we don't have to do anything with it to satisfy the second normal form, it's in the right place, so to split this we're going to end up with multiple tables, so what I've done done here is I've taken the customer column and put it here.

I take the product column, put it here. I take the order date so we know that's our primary key, a primary composite key and then we know that the quantity still belongs to our table so it stayed in our main customer orders table but we need something with the address and what we did was put the customers and their addresses in their own tables. Well, in this case I still have some extra things here, so let me remove them as we go and I, Mark Smith, Lindy was there once, this would make a lot more sense, so now we have a customer table whose primary key is customer, the customer's name in this case and his address only appears there once, so it was as before before satisfying second normal form, if Bob Smith's address had changed we would have had to update it several times, we had to update all the places you ordered for every line you ordered for every product you ordered every day you ordered, so that's a lot of updates. for an address, we pull this address when Bob Smith changes his address, it changes in one place and we are done, we have to update several points so that you can get rid of any update anomalies that you may have now, what do we do with the product?

Good manufacturer, we also recalled it and it has the same problem. I need to get rid of the multiple lines here. There should be one line per product in this table. What we have here is something to satisfy the second normal form, so we have a compound. key, the only columns we have there depend on the full key, there is no partial dependency, so the quantity depends on the full quantity. Kia depends on the customer product and the order date and when we had partial dependency we split them into their own tables and now we have a product table and we have a customer table and I should also mention that now we have a primary key with a single column in the product in the customer table and we reference those tables with foreign keys and I'm going to put a different color for the foreign keys, so now every time we want to change the name of a manufacturer or change the address of a client, this is done in one place and you don't have to worry about updates and deletions and those kinds of issues, so it's the second normal. form compound keys and partial dependency, the third normal form is now about transitive dependency so transitive dependency is there and in this case I want to use an example of product table so here we had a product table and I I only had two. columns product and product manufacturer, well now I went ahead and added the product manufacturer address, product details, product cost, so that's all there, so what we want to see now for the third normal form , we have a unique key, so this is a The primary key is the product and we need to see if everything relates directly to it, so for the third normal form the first normal form and the second normal form are satisfied.

The first normal form is satisfied because we have no other non-atomic columns in each column. it only stores one thing, the second normal form is satisfied because we don't even have a composite key, so everything relates to the key we end up with. Composite keys are the only thing that can have a partial dependency, since our primary key is We are done with the product with the second normal form, so now we have to find out if this is the third normal form, so the manufacturer of the product, if we know the product, a basketball, we already know the manufacturer of the product, that can be confusing because you can start thinking well. different companies make basketballs, but in this example just look at these four rows ignoring what you know about business because we would like to change that in the real world, but for this example a basket, if we know basketball, we know the manufacturer Spalding and then, If we know basketball, we also know that the manufacturer's address is a Spalding unit, we know that the product detail is twenty-nine point five inches, and we know that it costs twenty-five dollars.

This is where the transitive dependence on the manufacturing direction actually depends on the right of the manufacturer and then the manufacturer depends on the basketball, it's a two-step process. If we know the manufacturer, we also know the direction, so two steps is the transitive property which is approximately the third normal form and we will see the problem and why the third normal form. If you look at the second and third rows with Rawlings, you can see that we have listed his address twice. Now all that Rawlings manufactures the way we have our model here in the table is that if we had fifty products manufactured by Rawlings, we would have to update their address fifty times if they made a change in the address, those are the types of things that we are trying to eliminate with

normalization

, so in this case what we are going to do is separate the manufacturer from the products table and what I did was take the manufacturer and its address and pasted them here and got rid of the duplicate row, so now Rawlings is in this manufacturers table once and the primary key is the unique key of this manufacturers table. is the manufacturer now in the product table, we no longer have the address of the manufacturer, we have our primary key and then we have the manufacturer of the product and it is related here, so if we want to know the address of Rawlings we would have to join the table of products.

Go to the product manufacturer table and we would have its address at twenty-three Rawlings Corp. The rest of these columns are directly related to the product, so they are simply looking up the table and they have already fulfilled third normal form, so now This table says third normal face. form like this now we are in fourth normal form so it was a transitive dependency so fourth normal form has to deal with multiple value facts now in fourth normal form we have to be in first normal form second normal form, third normal form and then finally the fourth normal form, well in this case we look at the first normal form and this column here shows us that we are not in the first normal form because we have a non-atomic value there, it has two different values in a column.

So what we need to do now is separate that, but before we do that right, let me show you that separated. If we separate that, our primary key changes, before our primary key was just the client, now our primary key, Bob is missing twice because they had basketball and baseball in their email subscriptions, well, that's a problem and that happens with multi value data, so to include it there they will add more rows, now the ultimate solution is to separate the tables. So in this case we take everything except the subscription and paste it here, so these are all the things that are related to the customer, but they only have one value, not multiple values, but just one value, so in this case there is only one client. address, we do not allowmultiple addresses, we don't allow multiple email addresses, so this table, as the customer primary key, only has the attributes to have one thing now, when we move to the new table we have here, which is just like subscriptions of customers email, this table will list the multiple value subscriptions, so Bob Smith is here twice, so our original table that had the customer's primary key is kept and this table here has the multiple values and that is how it is satisfied. fourth normal way now this has a foreign key that returns the primary key, but the question I have is what is the primary key of this table.

Now, let's see what a customer is, he can't be a customer because Bob Smith is in two lines, right? email subscriptions, well that can't be that because baseball can belong to two things, so the primary key of this is everything, so it's both columns together, let's go over what we talked about first, no more shapes, values atomic, second normal form, which are the composite keys. and you only have to worry about the second normal form if you have composite keys because then you have to look for the partial dependency and in the third normal form you have to look for the transitive dependency where a column does not directly depend on the primary key and then finally you have the data of multiple values ​​to get to our four normal forms what I want to do is just go through the thought process of how I would normalize this table, so I don't normally think in terms of first normal form, second normal form, third normal form, fourth form normal.

I usually think in terms of dependency. and the gist of what I'm looking at, so let's get started and walk through this example, so the first thing we need to do is identify what this table is, what is the key, the primary key of this table, well, in this case, that It's supposed to be the same as the order table that we did our example on, so we had the customer, we have the product and then we have the order date, so I'm going to highlight those columns now, let's make sure they are primary keys. Okay, so we have a client, we have Bob Smith, we have Jill Thomas and we have Bob Smith again.

Now the first thing that comes up is that Bob Smith in this case may be a different person and I think he is, so what? What we need to do is get some uniqueness for our client, so what we can do is add a new client ID and we'll create a smart key in this case. Okay, so what we want to do is add a client ID because we want uniqueness here. and then we'll just do Smith's, which would be the unique ID of the client here, and then we'll say J Thomas one and then we'll say B Smith.

Okay, now our client has a unique ID most of the time. We're just going to use an identity with a number and an auto increment key, but in this case I want to use something that we can actually read, so it's not my key anymore, so I'm going to delete it, but now this is our key okay, now We have a unique key for the customer, the next question we have is that the product is unique. Well, this is going to get even more confusing here, let's look at all the basketballs, so we have a Spalding basketball. a Titleist basketball which doesn't make sense, but we'll keep it that way, so in this case the basketball itself is not unique, so we need a key for that product, so we're going to make an ID key for the product and Let's say basketball - SP for Spalding and I guess we have to do that for all of them here so now we have a key and this is actually our unique key for the products so let's clear that up okay now we think that I have a uniqueness, so the first thing I'm thinking about here is, here are all my unique keys and I'm thinking more about dependency, so I'm wondering which of these rows only depend on which of these columns only depend on the client or the customer ID, we know well the customer's name, his address, his email subscriptions, his mail catalog subscriptions, all those things only depend on the customer's rights, so we can cancel them immediately, so I'm not thinking in terms of first, second, third, fourth, normal. forms I'm thinking about dependency and I know that my customer ID I don't need to store all the information about them with the order, so the first thing I want to do is take all that stuff and carve it out. in its own table and then I would no longer need then I would no longer need all the customer information there and now I also have a primary key in this table which is the customer ID and now here is a foreign key which will also be part of the primary key composite, but it will be a foreign key, so now I have already broken down a table, so let's think about the product ID, what things depend on the product ID, okay, the product name, the manufacturer, the manufacturer's address, product details, product. costs everything that is directly related to the product ID, so I already have a second table.

I'll take them all out. I'll put them here and get rid of these columns. Now I have my board and this didn't happen. It didn't work very well, the total line amount was a calculation that is now gone because I took the cost of the product, so that's something to think about properly, in this case and this is where the business rules come in where in In this case we have the product. cost and this is, take this first row, the Spalding basketball, the cost of the product is $25. Well, we assume that the cost of the product does not change, so we will not store it in the order line item, but they are probably cost changes. you'll want to store the product cost on the order line; also in this case we will leave it here with the product in the products table so that the total amount of the line we can store here or we cannot store here. and in this case I'm going to go ahead and store the calculations here to see if I can calculate it real quick, so we have $25, we have an L battery which is $35 that we have and this is the problem, so if the If the cost of the product doesn't change , we're actually storing this redundantly, so I'm going to remove it because you can always calculate it with a sequel join by just joining those two tables so that when we get rid of the total line. quantity, that leaves us with the order date and the order total or and the product ID is now a foreign key here, so we'll put it in there so the order date is here twice.

Do we really need the order date with the product ID? What we actually have here are like line items for an order, so there's one more step we can take in this customer order table and we can call this customer line line items, but we're really looking at and on the order lines. we have a quantity of one and we have the product but the order date applies to the entire order not just the order lines so I think we need to split up and have an order table and if we do that the order table customer orders now we can have a customer order ID and we can start grouping them together, so before we do that, I'm going to insert a customer order ID so you can see this and I'm going to say this is order one, order one, those no t dates and then I'm going to call this order - order - and I'm going to call this order three, okay, now we can have customer orders and we're going to have 1 2 3 4 and then the customer order date and we're going to have 8 9, okay now we have an order date so the question is is the order total at the line level or is it at the correct order total level and that is at the order level so now we have the order total and keep it at 70 70 and then the order total was 57 so now that we have a primary key here we can leave the order date here and we can leave the order here okay now this is a foreign key , okay and then just to do this is in the way it should be just to make it look better so even though the foreign keys are in front with the client id, you can have two clients per order, there will still be a client or Durr port, so we don't need the client id to be sent. on the lines, so what we need to do is bring it here, so let me add some space.

Now we're going to have the customer id and we're going to have B Smith, so now this is a foreign key and now on the lines, we don't need it for the orders to connect to the lines, we can actually get rid of this column , so now we're getting to the right point of it all and that's what we're trying to figure out: what level are we at? You need to record everything in the customer order, so what you end up having are the customer orders and then in the customer orders, you actually have the line items for the products they ordered and how many they ordered.

Okay, but we don't actually store the product names here we store the product ID in the product ID we can get the rest of the information about the cost, okay, so there's really nowhere else to go in this case. I mean, that comes down to the bare minimum. Customer orders are all on the customer. order with the same grain as the customer's order id, so the order has its own date and its own order total and has the customer attached to it, so there's nowhere else to go either, so we have to go down to our other tables down here, so we have our customer order lines, we have customer orders now, what about our product table and our customer table?

Well, the product table, let me expand this a little bit. I'm going to take them individually and I'm going to move them around so we can see each one individually, some will be reduced to empty space, let's see the products by themselves, so we already learned from our previous third normal form

normalization

. The clothing of the manufacturer of this product refers to the manufacturer of the product, which then refers to the product, so there is a transitive dependency, so we need to take the address of the product out to its own table so that we no longer need this address, so let me get rid of that, so now the address is here and we have our own. primary key now before we do that, I have to spread this out a little bit more so we can see what's going on, we have multiple here, so this primary key should be the product manufacturer, but we have this and this and this and delete them, okay, now we have our product manufacturers table just like we did in our other exercise, if you remember that was our third normal form when we said to the product manufacturer, okay, now the project manufacturers, is there anything else in the product? we have a product ID and now instead of the product name relating to the product ID, the manufacturer actually relates to the product ID because in this case Spalding and Titleist have correct basketballs, so we have You have to get down to the product ID to make sense, so the ID related to the product, the product manufacturer related ID, the details were related to the ID and then the costs were related to the ID.

Okay, so I think we've broken down the product to the extent that we can break it down, so now we've normalized it and let me do this for a second. We'll make this the maker which is probably the primary key and now this is a foreign key relationship, so finally let me grab the tape from our client so the client knows that we want this. will be our primary key, but we have so many rows right now, but we don't need them anymore, not all of them, let's see what repeats here, so these rows are actually just repeating, so let's delete those rows because all we need. it's the 1b Smith for customer ID so we're in one row, Jill Thomas, that's two exact rows, so let's delete them and then BS Smith to two exact rows, okay, so we've actually had three clients. two bob smith, but now they are two different people with two different IDs, but now we have to deal with our first problems as normal, when I see two things stored in a column, it is automatically a problem for me, so we can easily insert . and we move the cells to the right and add our client's email and now we need to get rid of the extra information that's in there, okay, now we've arranged for the client to store two different things in one column.

I also want to fix the formatting here. and now we have these customer email subscriptions that have two things and then we have these two categories here, so we know from our previous exercise in fourth normal form that we need to separate this into two tables with the primary key and then a foreign key . key in email subscriptions, so in this case we need to do it twice. We know that we are going to have this part of the table alone.This is our customer table. I'll leave it here, but now we're going to have our customer email subscriptions as its own table, so I need this one.

I'll leave it here. I need this one and we need to split this row in two, so now we have B Smith and then we have basketball and then we have baseball, okay? Now that we've gotten rid of that non-atomic field, we need to do the same thing with these two different columns, so let's do it. I'm going to grab them and then I'm going to get this back and we need a new row here so B Smith also has a golf catalog and then B Smith 2 also has a baseball catalog and now we just have one catalog and now we have everything normalized so let me put together the whole picture, well, that's our model. we have customer order lines, we have customer orders, we have products and customer tables, we have the email subscriptions and mail catalog split and then we also have the product manufacturer separated from the product table, so we go through the normal first, second, third and fourth forms.

In fact, we take a fairly complex table and split it into several tables. We go from this table here until we get to these tables divided into normal shapes, as always. If you liked this video, please subscribe, like the video below and connect. If you have any questions on LinkedIn or Facebook page, or you can connect with me at Sequel Training Online com. I'll talk to you next time