BYU-Idaho Online Learning
Video Transcript
[Single Speaker]
[Screen shows Caleb standing in front of a blank chalkboard speaking to the camera.]
Caleb Curry: Yo! What's up, nerds? In this video we're going to be discussing the second normal form. So, in order to put things in second normal form, they must already be in first normal form. So, check out the video I made right before this one before you come and watch this one.
[Screen shows Caleb speaking to the camera and make gestures with hands to nothing in particular.]
Now, second normal form deals with what's known as a partial dependency. That's when a column only depends on part of the primary key. So, in order for it to depend on only part of it, you have to have a compound or composite key. So, basically the primary key has to be multiple columns because think, if you just have one column as the primary key the column can't partially depend on half of it. Does that kind of make sense? I don't know, maybe not.
Let's say we just have a random table for people. So, we have, you know, the person's—let's just use a person ID and we have some attributes about this person. We have their name, you know, maybe their phone, or their pH level haha, and maybe their email.
[Screen shows Caleb writing on the middle of the chalkboard “Person_id”. He then draws a line left of the “Person_id” and writes “name” at the end of the line, connecting the two words. He draws another line to the right of “Person_id” and writes “ph” at the end of the line connecting the two words. Finally, he draws a vertical line down from “Person_id” and writes “email” at the end of the line.]
Let's first talk about what a dependency is before we go and talk about partial dependencies. These columns depend upon the primary key, so, in this case, this is the primary key and there's a dependency here. Another way you can think about it is if we have another table about cars, we can have a car ID, which is also a surrogate key.
[Screen shows Caleb underline the three words surrounding “Person_id”. He then points to and draws a circle around the “Person_id”, indicating that it is the primary key on which the other three items are dependent on. He then writes “car_id” to the right of the “Person_id” bubble map.]
Now, this person's name, it does not have a dependency here, you can see this name is only dependent on the person ID, not upon the car. So, this doesn't make any sense here.
[Screen shows Caleb draw a curved line from “Person_id” to “car_id” before erasing that connection.]
Another thing is that the person ID doesn't have an unrelated attribute about the car ID, such as the car color. The car color has a dependency on the car, not the person. This isn't going to make any sense.
[Screen shows Caleb write “car_color” above “car_id” and draw a line to connect the two words. He then draws a curvy line from “car_color” to “Person_id” before erasing that connection.]
That's what a dependency is. Now, what is a partial dependency?
[Screen shows Caleb speaking and standing in front of a now blank chalkboard.]
So, let's see what a partial dependency looks like using surrogate keys. You'll see this when you have a many-to-many relationship broken up with an intermediary table, which is the correct way to design a many-to-many relationship.
So, let's think of the example of books and authors because, if you think about it, one author can write many books and one book can be written by many authors. So, conceptually, it's a many-to-many relationship, but we're going to store that in the database as one-to-many relationship on one side and then one-to-many relationship on the other side.
[Screen shows Caleb continue to speak to and make hand gestures to the camera.]
So, we get this look at the table intermediary table and then a table. So, over here, let's put the authors. So, this is the author table. Then over here we have the book. This is the book table and then in between we have the intermediary table of book authors or book author. So, this is the correct way to design it.
[Screen shows Caleb draw three rectangles on the chalkboard to represent tables. He then labels the first “author”, the second “book_author”, and the third “book”.]
Now, when it comes to attributes, all the attributes about the author are going to go over here, all the attributes about the book are going to go over here, and all the attributes that have to do with both the book and the authors connected, those will go in this table.
[Screen shows Caleb first point to the “author” table, then the “book” table, and then the “book_author” table.]
So, first let's give them some surrogate keys. We'll give this one an author ID. We’ll give this one a book ID and this one's going to have two foreign keys of the author ID and the book ID. Those foreign keys together will be the key for this table.
[Screen shows Caleb write “author_id” in the author table, “book_id” in the book table, and then “book_id” and “author_id” in the book_author table. He then draws a line connecting the “book_id” in the book_author table to the “book_id” in the book table and then another line connecting the “author_id” in the book_author table to the “author_id in the author table.]
So, that's kind of how you would set this up. Now, things about the author go over here, so, you know, their first name, maybe their last name, maybe their birthdate. Whatever you want to put about the author.
[Screen shows Caleb write “author_id”, “fn”, “ln”, and “birthdate” in the author table.]
The book would have stuff about, you know, the ISBN, which is—the ISBN is the code on the back of the book you can look up. It tries to uniquely define that book, so the exact edition and everything else. So, you can just use that to define it. You know, we would maybe have the page numbers or the publisher. The publisher could be a primary key or a foreign key to another publisher table, or you could just have it in that table if that's how it worked, but likely to go to another table. But we're getting off topic.
[Screen shows Caleb write “isbn” in the book table and then continue to speak and make hand gestures to the camera.]
Anyways, it would look something like this. So, author information goes over here; book information goes over here. This is the correct way to design this table because when it comes to dependency the first name of the author has only to do with the author and it has to do all about the author.
[Screen shows Caleb point to the author table, then the book table. He then points and gestures to the author table as he speaks about it.]
Now, let's look at things that have to do with both the book and the author. We could have something such as the author position. Now, what that is—when you write books, often there's a person who has like the first author position, which is what the big name is and then you have the second and the third and the fourth. People often compete to try to get first author. This has to do with both the book and the author because, if you think about it, if we put author position here, that's going to depend on what book we're talking about. We can't just put author position one and say he's first on every single book he ever is going to write. That doesn't make sense.
[Screen shows Caleb point to the book_author table. He then writes “author_position” in the book_author table. He then points to the author table.]
We can’t just say author position one on the book because it doesn't say which author we’re talking about. Which author is one? It doesn't make sense. That's why we have to have it in this table because it has to do with the book and the author.
[Screen shows Caleb point to the book table. He then points to the book_author table.]
So, we could say book ID is 17, author ID is 22, and the author position is 1. So, that would say the book with the ID of 17 and the author with the ID of 22 is in the second or the first position on that book. It's a little complicated, but we combine that with Joins to make it make more sense for the actual viewing of the data.
[Screen shows Caleb point to the book table, then the author table, then the book_author table. He then continues to speak and gesture to the camera.]
But, anyways, this relies on the book and the author ID. That's why it's in this table. This is correct. Now, an incorrect thing would be something like the ISBN because the ISBN has to do with the book only. So, when you look at this, it relies upon the book ID; it has a dependency on the book ID, but it doesn't have a dependency on the author ID. This is an example of a partial dependency.
[Screen shows Caleb point to the book_author table. He then writes “isbn” in the book_author table then point to the book table. He then draws an arrow from the “isbn” in the book_author table to the “book_id” in the book_author table.]
Now, the correct way to fix this, in this case, which would just be to take the ISBN and put it in the book table, which we obviously already did because I put that in there first when we started. But if you didn't have this table when you're working with, just say like one table maybe, well, then the correct way to do it is to take the book ID, the ISBN, drag them to another table, and then use a foreign key to connect to that table, which we already have it structured correctly because we understood how to design that many-to-many relationship, which is why relationships are useful to understand.
[Screen shows Caleb point to the “isbn” in the book_author table and the point to the book table. He then points to the book_author table and make hand gestures as he speaks to the camera.]
But if you don't have it already set up correctly? You'll have to take the partial dependency and move it to another table and reference it with a foreign key. For second normal form, you want to first: be in first normal form, and second: remove all partial dependencies by moving the columns as we did here. We took that ISBN put in the correct table.
[Screen shows Caleb standing in front of the chalkboard, making hand gestures and speaking to the camera.]
Now, another thing you can think about is if you have a table where there's a primary key of only one column, you're already in second normal form for that. Like, imagine it for this: how can birthday be dependent on only part of the primary key, which is author ID? Because there's only one. You can't depend on only part of an individual column.
[Screen shows Caleb point to the different items in the author column and speak to the camera.
Does that make sense? Hopefully, it does. I don't know why I even ask you guys that. Like, “Does that make sense?” It's like you're watching like a little kids show. “Yeah!” So, yeah. Peace out, guys! Thank you for watching. I'll see you in the third normal form video.
[Screen shows Caleb continue to speak to the camera and then make fun of his previous words with a funny voice. He then shows a peace sign with his fingers and the video fades out.]
[End of Video.]