Database Design 37 - 2NF (Second Normal Form of Database Normalization) 

Some lecturers seem to love over-complicating the shit out of things that are actually relatively trivial.

Then there are profs who read off their slide in a monotone voice lol

@orbitaldildo there is actual theory that a comp sci student is expected to know. while these videos are great for basic understanding, it nowhere near touches actual relational db design theory and solving normalization of data. For example, he lightly touched on data dependency in the video yet understanding that relationship is vital to understanding how data relates, how natural joining data is the reversibility operation for normalization. Or that the operation he was doing by creating a new table, and a bridge table was performing non-loss decomposition to two smaller degree relations via relational algebra projection. It may all sound like wordy bs but knowing terminology and the reasons behind it means you actually know the theory and can put it into practice. What he has done here is also muddy the water a bit because 2NF doesn't strictly require a bridge table(Associative entity), just the situation he created - and he did not clarify that.

You seems like you know shit or 2 about database, why are you here then?@@perpetuaL524

duuuuuuuuuuuuuude i was mad depressed until i heard that joke

The definition of a basic bitch: a Bitch who's pH is greater than 7.

It must be in 2NF, great question 🙂

Thanks bro! You really helped grasp a missing piece that my professor couldn't tell me!

It is a composite key. It's different than a foreign key. By definition: A composite key is a combination of two or more columns in a table that can be used to uniquely identify each row in the table when the columns are combined uniqueness is guaranteed, but when it is taken individually it does not guarantee uniqueness.

Let him in, real quick...it's raining outside.

This makes more sense. Else you could just make a surrogate key for the author_book table and leave the ISBN there, but that wouldn't be 2NF.

+John Caitlin In many cases it's helpful to use your own primary key. That's especially true if your database will last a long time. If, for example, they changed the ISBN system tomorrow (I don't know why, but theoretically they could), you'd be in trouble. For instance, you expected all ISBNs to be just numbers. If they changed that and said ISBNs can now have letters, you'd have to change your database.

Because the values are in two columns. We are not storing a an author id and a book id together in a string column.

A book can have many authors, so it would be many to many. :)

@@codebreakthrough ok so you mean a single book being written by co authors then. But generally books usually have one author.

Exactly what I mean yes. Generally it is one but not always

Why not and what is your solution?

I've just never personally seen any site that needed to order data of that nature, so to me the example you're using is just impractical. But the concept makes total sense and I agree with what you're saying otherwise. :) I don't have much of a solution other than to suggest a better example. Perhaps the example would have made more sense if the intermediary had been a library database that connected the two. So library_books and the columns would be book_id, author_id, library_id (which would have a relationship with a table for library information) Hope that helps. :)

Hey Caleb, your example is excellent and a correct design for a bridge table. Perhaps showing a short list of example rows for each of the tables would help show how and why the first, second, third author are being listed as you say. BTW, I would have put the Book table on the left and author on the right since the bridge table is book_author ;)

Kinda agree, would be better for the viewer for you not to have to explain the nature of the data. Simple examples would be best IMO. Something everyone might understand.

For illustration purposes it should be authors_books because of the way I set up the tables. but I would have preferred if I flipped the tables around and made it book_authors. The way I illustrated the columns may confuse a beginner so I apologize for not explaining in the video. That format is common of ERD. Thanks for the recommended fixes. I'll keep them in mind for future videos.