**1st, 2nd and 3rd Normal Form (Database Normalisation) — Transcript & Summary | SozAI** Source: https://sozai.app/transcript/1st-2nd-3rd-normal-form-database-normalisation/ A beginner's guide explaining 1st, 2nd, and 3rd normal forms in database normalization with examples and key concepts. ## Key Takeaways - Normalization improves database design by reducing redundancy and dependency. - 1NF focuses on unique rows and atomic cell values. - 2NF removes partial dependencies on composite keys by splitting tables. - 3NF removes transitive dependencies to ensure data integrity. - Proper normalization requires sequential application of rules from 1NF to 3NF. ## What the video covers - Introduction to database normalization focusing on first, second, and third normal forms. - First Normal Form (1NF) requires unique rows and atomic (indivisible) values in each cell. - Examples show issues with duplicate rows and cells containing multiple values. - Adding unique identifiers like order IDs helps achieve 1NF by ensuring row uniqueness. - Second Normal Form (2NF) requires the table to be in 1NF and no partial dependency on a composite key. - Partial dependencies are resolved by separating data into related tables to ensure full dependency on the primary key. - Third Normal Form (3NF) eliminates transitive dependencies, ensuring non-key attributes depend only on the primary key. - Examples include splitting customer names into first and last names and separating course fees from student-course assignments. - The video emphasizes step-by-step normalization starting from 1NF to 3NF with practical table restructuring examples. - Clear explanations of atomicity, uniqueness, partial dependency, and transitive dependency in database tables. ## Chapters 1. 00:00 Introduction and First Normal Form Basics 2. 00:48 Atomic Values and Non-Divisible Data 3. 01:36 Examples of Duplicate Rows and Order IDs 4. 02:26 Handling Multiple Values in Cells 5. 04:00 Separating Data into Atomic Units 6. 04:49 Introduction to Second Normal Form 7. 06:33 Partial Dependency and Composite Keys 8. 07:13 Resolving Partial Dependencies with New Tables 9. 08:43 Third Normal Form and Transitive Dependency Answers ## Questions about this video What is the main requirement of the first normal form? First normal form requires that all rows in a table be unique and that each cell contains atomic, indivisible values. How does second normal form improve upon first normal form? Second normal form eliminates partial dependencies by ensuring that all non-key attributes are fully dependent on the entire primary or composite key. What does third normal form address in database normalization? Third normal form removes transitive dependencies, ensuring that non-key attributes depend only on the primary key and not on other non-key attributes. ## Full Transcript — Download SRT & Markdown 00:00 Speaker A Hello and welcome to this beginner's guide to first, second, and third normal form when looking at database normalization. So let's get started. First normal form: this one is nice and simple. All rows must be unique, and all cells must contain atomic values. 00:18 Speaker A must contain atomic values well what does that mean well it's really quite simple each row in the table must be unique and therefore there cannot be any duplicate rows in the table so you can't have two different rows where all of the 00:34 Speaker A Well, what does that mean? Well, it's really quite simple. Each row in the table must be unique, and therefore there cannot be any duplicate rows in the table. So you can't have two different rows where all of the data contained within those rows is identical. And we'll give you an example in a minute. Each cell must only contain a single value, so you can't have a cell in a table that has a list of values in that cell. That's a no-no. 00:48 Speaker A cell that's a no-no and finally each value should be non divisible which means that if you've got something in a Cell you can't break that down into further sensible parts so let's have a look at an example of each of those so 01:03 Speaker A And finally, each value should be non-divisible, which means that if you've got something in a cell, you can't break that down into further sensible parts. So let's have a look at an example of each of those. So each row must be unique. So let's look here. We've got a table of takeaway orders here, and for each takeaway order, we've got customer name and a customer order. 01:23 Speaker A ordered burger fries and coke Fred ordered nuggets lemonade and fries and then Bob also ordered burger fries and coke now that's fine apart from we don't we can't tell the difference between this row here and this row here 01:36 Speaker A So, for instance, Bob Jones orders here. He ordered burger, fries, and coke. Fred ordered nuggets, lemonade, and fries. And then Bob also ordered burger, fries, and coke. Now, that's fine apart from we can't tell the difference between this row here and this row here. 01:54 Speaker A here order ID number one all that was Bob Jones and he ordered his favorites their order I too was was Fred's and he ordered all those and then the final order was Bob Jones and he ordered his favorite I'd order again there so 02:09 Speaker A So we have no idea, for instance, which order was ordered first. No idea at all. So we need to change that because they need to be uniquely identifiable. And the way we do that, nice and simple, is we just add an order ID column. So here, order ID number one, all that was Bob Jones, and he ordered his favorites. Their order two was Fred's, and he ordered all those. And then the final order was Bob Jones, and he ordered his favorite order again there. 02:26 Speaker A value well yep as you can see here this cell here doesn't contain a single value it contains three items a burger fries and coke if at all of them do so these ones here these and this in this area 02:41 Speaker A So because we've got these order IDs, that row there is now no longer identical to that row there. There we go, good. So that's the first part of first normal form done. Second part, let's have a look. Each cell must only contain a single value. 02:53 Speaker A look a bit like that this table here has an order ID in one column and the item corresponding to that order ID there so order ID one has a burger order ID one also has fries and order ID one also has 03:11 Speaker A Well, yep, as you can see here, this cell here doesn't contain a single value. It contains three items: a burger, fries, and coke. All of them do. So these ones here, these, and this in this area, the actual order, they all do. So that's no good. 03:31 Speaker A well what's the problem well if you ever look over here at customer named Bob Jones you can actually see that Bob Jones isn't atomic it can be divided further into Bob's fur into his first name and last name and that's no good 03:47 Speaker A So what we need to do there is we need to move those into a separate table so that each row only has one item and some alone. So it's going to look a bit like that. This table here has an order ID in one column and the item corresponding to that order ID there. 04:00 Speaker A name and you just put them in separate cells looks like that good so that's how you put a database table into first normal form so now let's move on to second normal form now second normal form not quite as much to it all you 04:18 Speaker A So order ID one has a burger, order ID one also has fries, and order ID one also has a coke in there. And as you can see, each item in this table now is unique, and there's no duplicate and multiple values per cell here. So that's sorted. Now that's the second bit. Good. 04:32 Speaker A form until you've gone through all those rules that we just did from the previous one from first normal form it must must do this first and then we must have all non-prime attribute should be fully dependent on the candidate key and it's 04:49 Speaker A Third thing: all data must be atomic or non-divisible. Well, what's the problem? Well, if you ever look over here at customer named Bob Jones, you can actually see that Bob Jones isn't atomic. It can be divided further into Bob's first name and last name, and that's no good. 05:05 Speaker A much that course costs so here this student they're doing course so student 1 he's doing course ID number 1 and that course costs 500 and student so look so student ID 2 here he's doing course 3 and it's 750 and student 3 is as fish 05:27 Speaker A Okay, because that makes it some kind of composite value. That's no good. It needs to be separated out because they're different things. It looks just like that. So you create a column for their first name, a column for their last name, and you just put them in separate cells. Looks like that. Good. 05:43 Speaker A the course fee is a set fee that only depends on the course ID so this particular course course one costs 500 course two cost of thousands course three cost 750 and as you can see here it doesn't matter whether it's student 06:00 Speaker A So that's how you put a database table into first normal form. So now let's move on to second normal form. Now, second normal form, not quite as much to it. All you need is there are no partial dependencies. 06:16 Speaker A the course fee is not dependent on the student ID and the student ID forms part of this composite key here of student ID and course ID so this is the composite key but this bit is only dependent on 06:33 Speaker A Well, what does that mean? Well, what it means is first of all that the table must 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 that we just did from the previous one, from first normal form. It must do this first. 06:43 Speaker A key so that's where we get the partial part of it so what have you got to do to fix this well nice and simple all we do is remove this information to a separate table just like that so we have a table about 07:00 Speaker A And then we must have all non-prime attributes fully dependent on the candidate key, and it's fully dependent on the whole candidate key. Well, what does that mean? It sounds a bit complicated, but it's not. So let's have a look here. 07:13 Speaker A don't need this extra course fee information we just get rid of that column and we only have the student ID and the corresponding course ID so student 1 is doing course one student 1 is also doing close to student 2 is 07:27 Speaker A We've got a list here of students, so the student ID, the course ID that that student is doing, and how much that course costs. So here this student, they're doing course. So student 1, he's doing course ID number 1, and that course costs 500. 07:46 Speaker A so it's similar to the previous one but we're not talking this time about the primary key or the composite key we're talking about something else so just like when you try to put something into second normal form it had to already be 08:03 Speaker A And student 2, so student ID 2 here, he's doing course 3, and it's 750. And student 3 is also doing course 3, and it's costing 750. Well, what's the problem with this table? And the problem is that the course fee has got nothing to do with the student ID. 08:16 Speaker A normal form it must also be in first normal form because the to go back so anything that's in thirds got to be in second and obviously anything this in seconds already gotta be in first so it needs to tick all those over boxes it 08:30 Speaker A So the course fee is a set fee that only depends on the course ID. So this particular course, course one, costs 500. Course two costs 1,000. Course three costs 750. And as you can see here, it doesn't matter whether it's student ID 2 doing course 3 or student 3 doing course 3, for either of those students it costs 750. 08:47 Speaker A sounds a bit weird but all it means is that all fields must be only determinable by the primary or composite key and you can't determine the any of the fields by other keys in there so what does that mean well let me show 09:01 Speaker A And that's because the course fee has nothing to do with the student ID. Now, it's important here to note that the course fee is not dependent on the student ID, and the student ID forms part of this composite key here of student ID and course ID. So this is the composite key. 09:15 Speaker A tournament and the winners date of birth so this works well you it's it all fits through to first and second normal form but there is a problem with this with this table and the problem is is that you can determine one of these keys by 09:33 Speaker A But this bit is only dependent on that bit, and it's not dependent on that bit. And that's where you get this partial bit because it's not dependent—it is dependent on this part of the composite key, but it's not dependent on this part of the composite key. So that's where we get the partial part of it. 09:47 Speaker A you can go you know that the winners date of birth is this this day of birth because that's his name again he's got that name so although it's not dependent on these bits here you can't tell the winners date of birth from the 10:00 Speaker A So what have you got to do to fix this? Well, nice and simple. All we do is remove this information to a separate table just like that. So we have a table about course fees, and each course ID has a course fee attached to it: course one is 500, course two is 1,000, etc. 10:17 Speaker A could do that by having a separate table of winners here so for instance here we've got the in the tournament name the year and the winners the winner there and we have a separate table that says okay Alfredson his date of birth is this 10:37 Speaker A And the other table, we pretty much keep the same. We just don't need this extra course fee information. We just get rid of that column, and we only have the student ID and the corresponding course ID. So student 1 is doing course one, student 1 is also doing course two, student 2 is doing course one and course three, etc., etc. 10:53 Speaker A just by the name of the tournament itself and there are multiple tournaments in one year which means you can't determine the winner just by looking at the year you have to use both of the name of the tournament and the 11:07 Speaker A So that is no partial dependency. It must be fully dependent on each of these parts of the composite key if you've got one, okay? So that's second normal form. Third normal form: no transitive dependency. 11:23 Speaker A third normal form if you like that so then please subscribe to my channel if you've got any comments or any suggestions for other videos then please just drop me a comment and good luck thank you very much Topics: database normalization first normal form second normal form third normal form atomic values partial dependency transitive dependency composite key database design Learn Scratch Tutorials Study this video - [Flashcards from this video](https://sozai.app/tools/ai-flashcard-generator/?from=transcript&id=79051) - [Quiz on this video](https://sozai.app/tools/quiz-generator/?from=transcript&id=79051) Free, made by AI from this transcript. No signup. --- This is the markdown twin of https://sozai.app/transcript/1st-2nd-3rd-normal-form-database-normalisation/ — the same content, without the markup. Published by SozAI (https://sozai.app). Reuse and quotation are allowed with attribution and a link back. Machine-readable index: https://sozai.app/llms.txt · data API: https://sozai.app/api/