Database Normalisation: Second Normal Form

This video is part of a series about database normalisation. It explains how to transform a database, which is already in first normal form, into second normal form by working through an example. It covers the criteria for the second normal form including ensuring that a relation, that is a table, does not contain any partial dependencies on the primary key. The video explains what is meant by a functional dependency and a partial dependency.

[@deathnightpwnage123]

This channel is quite underrated. Thank you for publishing these videos!

[@TheZliKadi]

i've watched several normalisation tutorials, also listened to a college course, and this is the best material i've found! you could say - normalisation for dummies :) TY

[@ComputerScienceLessons]

Very kind of you to say so. :)KD

[@firepower01]

These are hands down the best computer science videos on the internet... Thank you so much for helping me get through my degree over the years!

[@jeffreytello589]

best explanation of normalization on the internet! thank you

[@ComputerScienceLessons]

Thanks - I appreciate the comment. :)KD

[@3NESHKUMAR]

@@ComputerScienceLessons Really, Best Explanation ...Great work

[@Combatechist]

It's good to know there are people who have a clear mental thought process in this industry.

[@ComputerScienceLessons]

TY :)KD

[@jamie-leevellem91]

This is by far the best explanation there is out there!!!!! Thank you

[@ComputerScienceLessons]

You are very welcome, and thank you :)KD

[@user-gs2fw4nu7t]

Thank you so much for this series! You're much better at explaining database normalisation than our university professors.

[@SiddiqNx]

This is the clearest video I have seen on this topic. Your way of teaching is really amazing! Clear, concise, comprehensible and to the point. Keep up the work. Please don't ever stop making such high quality videos. :) Thanks.

[@dhkim5674]

All the lingo about partial dependencies, prime and non prime attributes had me confused but you were able to explain it in such a clear and concise manner! Much appreciated, and definitely helped me understand this part of my DBMS course much better!

[@ComputerScienceLessons]

Delighted to help. I agree, technobabble often makes things seem more complicated than they really are. :)KD

[@mgray999]

You have a very professional voice, and I like how you use examples and problems that would occur in the field. So grateful for this! I was getting horribly flustered and depressed from this week's work because the book and prof aren't doing the best job. Thank you.

[@ComputerScienceLessons]

You are most welcome. Thanks for complement. Hang in there. The best way to get to know databases is to build a few. Nice horse BTW :)KD

[@abu-bakrmohamed1707]

literally the best normalization tutorial i have ever watched !, thank you so much 💖

[@ComputerScienceLessons]

Thank You :)KD

[@iyasuwalalign2860]

ur voice ,logicallity of teaching is just perfect

[@bobbyw1074]

Thank you. You are the only one that can explain it simply.

[@ComputerScienceLessons]

You are very welcome! :)KD

[@James-mv4ge]

This was one of if not the most helpful videos I found on normalisation.

[@ComputerScienceLessons]

Thanks for the comment :) KD

[@fadygamilmahrousmasoud5863]

for sure it's the best explanation ever for normalization on youtube, thanks

[@NixTheG0at]

thanks man, after a 3 , 4 days of researching finally i understand 2nf from this video, you are my database hero bro :X

[@ComputerScienceLessons]

Glad to help :) KD

[@Billbillbillhahagdvdve]

These vids are great. Super clear and easy to understand unlike so many others !!

[@ComputerScienceLessons]

Thank you for the lovely comment :)KD

[@Jobe-bj2pq]

I struggled with 2NF all day in my class but this video had the best explanation yet! Thank you so much!

[@mikekirby5592]

Superior treatment of normalization; great teacher

[@GamerBoyRobby]

I am in my final year of my degree - for 5 years I have struggled with 2nd normal form but your description that the table should only contain that which is unique to the primary key has helped my understanding massively! Thank you!

[@ComputerScienceLessons]

You are most welcome. Sometimes it takes only a tiny idea to switch the lightbulb on. 💡 :)KD

[@GamerBoyRobby]

@@ComputerScienceLessons I have always been good at starting with 1NF and getting to 3NF. Never understood why 2NF was so hard for me to understand lol. Once again, thank you 😊

[@katzwhacky]

Lifesaver! Database Systems textbook is too dense! Thanks for helping my grade! Subscribed!

[@SinuraVinudhiWahalathanthri]

Thank you so much for this valuable video. I had a big issue about the 2nd normal form i watched about 20 videos but nothing was clear as this was sir. Thank you so much

[@ComputerScienceLessons]

You are most welcome. Glad to help :)KD

[@AayushBartaula]

This type of computerised video are very informative and to the point but i don't know why youtube algorithm don't make it popular

[@ComputerScienceLessons]

Thanks for the complement. I do need to work on my visibility. :)KD

[@JohnDoe-cn9lt]

This is the best explanation of normalization ever!!!

[@ComputerScienceLessons]

Comments like that keep me going. Thanks a million. :) KD

[@St1ckl3r]

Extraordinary teaching skills, well done!

[@ComputerScienceLessons]

Thank you. You're very kind :)KD

[@manilakid3]

Well done. You have command on the subject and know how to explain it in clear simple English.

[@ComputerScienceLessons]

Thank you :)KD

[@derekmiddlemiss6358]

Excellent, you're a great teacher! Thanks

[@ComputerScienceLessons]

Thank you. That's lovely to hear :)KD

[@Mike-vj8do]

best normalisation video on youtube

[@ComputerScienceLessons]

Thank you :)KD

[@wdub1362]

Thank you for being clear and concise. Examples were great

[@ComputerScienceLessons]

You're very welcome. Thanks for commenting :)KD

[@bharathisundarvelu1569]

Fantastic Explanation ... Just fantasticcc...

[@ComputerScienceLessons]

Thank you :)KD

[@selinklnc4903]

thank you it was the best explanation for normalisation

[@ComputerScienceLessons]

Thank you. You're very kind :)KD

[@tormund_gb1908]

Thank you so much for this video. You made a topic that seems so complex very simple to understand.

[@kola3880]

thank you so much for actually giving an understandable explanation thnak you!!!!

[@ComputerScienceLessons]

You are most welcome :)KD

[@ahcmadtaufiqbinchairilanwa3904]

I have watched other video about interpretation Second Normal Form and this video very helpfully thanks

[@dastanpc874]

thanks mate! a life savior you are

[@ComputerScienceLessons]

Really appreciate the comment. :)KD

[@منصةروادالتكنولوجيا]

من اليمن. استمر. From Yemen. Continue

[@ComputerScienceLessons]

I will. Thanks for the comment :)KD

[@youssefnov9037]

ممكن مساعده بخصوص هذا الدرس اكون شاكرتلك ضروري

[@saumyasingh7216]

Thank you for explaining it so nicely!

[@ComputerScienceLessons]

You're most welcome :)KD

[@tombaxter716]

I love you you’ve made my day

[@artistryartistry7239]

6:17 hi, great video, but when it comes to Foreign key names, might be less confusing if you change ID to studentID so there's less confusion about whether the ID column is a foreign or primary key for those not familiar with the concept.

[@ComputerScienceLessons]

Good point. Thanks :)KD

[@artistryartistry7239]

@@ComputerScienceLessons Sure thing mate :) This video is so good I keep coming back to it for a review, even after having previously grasped the concepts.

[@paurushgargtube]

Best Explanation

[@ComputerScienceLessons]

Thank you :)KD

[@kshatryakopf]

This video is a gem, thank you!

[@ComputerScienceLessons]

You're welcome. And thank you :)KD

[@rufina305]

Thank you so much for the clear explanation

[@gudisa-1]

WELL DONE!

[@ComputerScienceLessons]

TY :)KD

[@cloudsandRain01]

VERY helpful, thank you loads !!

[@ComputerScienceLessons]

You're welcome :)KD

[@NilakshMalpotra]

amazing as always :) Thank you so much

[@ComputerScienceLessons]

Thank you. You are very welcome :)KD

[@miguelamezcua4629]

Every well made! Thank you

[@ComputerScienceLessons]

You are welcome. :)KD

[@liquidation22]

thanks interwebs! good class...well explained

[@youssefnov9037]

Can you help me?? 😭😭

[@liquidation22]

@@youssefnov9037 what do you need help with?

[@youssefnov9037]

@@liquidation22 I have a question of this type, but I am not sure of the answer. Do you have an Insta account or a number or an email to communicate because I need to make sure

[@liquidation22]

[e-mail]

[@shivangraina9698]

Wow superb explanation

[@ComputerScienceLessons]

Thank you :)KD

[@ahmedziyad2731]

Thank you so much 🙏❤

[@shreenthasleem2820]

It took 5 years till I see ur video to understand

[@ComputerScienceLessons]

Glad to help :)KD

[@mikemihay]

Thank you! Keep uploading, You got a new subscriber!

[@parthshisode6125]

fantastic tutorial :D

[@ComputerScienceLessons]

Thanks for the lovely comment :)KD

[@JonathanHobbe]

Thanks so much!!!

[@ComputerScienceLessons]

You're welcome :)KD

[@abdiwahabadan1817]

thank you so much brother

[@ComputerScienceLessons]

You are most welcome. :)KD

[@ridajaafar863]

Thank you❤❤

[@ComputerScienceLessons]

Most welcome :)KD ❤

[@meritonqafleshi7416]

Thenk you bro

[@ComputerScienceLessons]

You're welcome :)KD

[@fluorescent_axolotl]

thank you!

[@ComputerScienceLessons]

You're welcome :)KD

[@icis27]

Just clarifying my understanding, around the 3:30 mark when we're talking about dependencies on the PK, would marital status actually be dependant? As you say, if the ID changes, first name and last name WILL change, but potentially maritial status wouldn't? (i haven't finished the video yet btw)

[@revjohnlee]

I would love for you to make some further videos in this series about the higher order normalizations. You've explained so well that I would enjoy the explanations even though I doubt I would ever be called upon to render 5th or 6th normal forms.

[@ComputerScienceLessons]

I'll do something about 4NF (and mention the others), but as you said, there isn't much call for higher order normalisation these days, especially as storage becomes ever cheaper. The NoSQL is on the rise. :)KD

[@hassan7522]

3:32 but when you change the id from 1 to 2 then the maritial status doesnt change. So wouldnt that mean that maritial status is not dependent of id because it doesnt change when id changes?

[@Ben_True]

wouldnt it be better to create a table for each separate subject ? for eg maths(student Id, grade). wont the table get messy when adding more subjects and especially students?

[@ComputerScienceLessons]

You could take that approach but if a school wanted to introduce a new subject they would have to get the database developer back in to upgrade the system for them.

[@Iamnabink]

Just awesome.Loved your Tutorial videos. Keep uploading. :)

[@ComputerScienceLessons]

Thanks a million. :)

[@noahrubin375]

I now hit like on these videos before they even start

[@naderkilani1421]

Usually a course will have a COURSE ID, why can't find one here?

[@ComputerScienceLessons]

A COURSE ID is not necessary if the course title is always unique. The course title can act as a primary key (in this scenario). Having said that, I've worked in organisations that encouraged database designers to include a surrogate key on every table. :)KD

[@naderkilani1421]

@@ComputerScienceLessons Oh, I see. Thanks for the clarification ,well explained tutorial.

[@didintlem_]

What app do you use to create the tables?

[@ComputerScienceLessons]

Microsoft Excel :)KD

[@durpomak]

At 6:20 in the video, what happens if the student fails the course and retakes it and fails again? This gives two identical entries in table StudentCourse.

[@nyctinasty4991]

im sure in a realistic scenario, there would be an extra attribute including the semester the course was taken.

[@Reigh992]

I don't understand functional dependency?

[@Scott-YouTubeAddict]

Good video. Would be better if it visually highlighted the parts while you were explaining them.

[@nickt423]

@3:41, what exactly do you mean by ID is the ONLY thing that FirstName, LastName and MaritalStatus are dependent on? For instance, why wouldn't FirstName be dependent on LastName?

[@willlamers6929]

In 2NF the Primary Key needs to be unique to that entry; it should identify that entry and only that entry, and the values in that entry would be dependent on that Primary key. In addition, if there were any changes to the values in that entry the Primary key will still point to the same entry. If FirstName was dependent on LastName then you could only have one entry with that last name. For example, if there was an entry for John Smith it would be the only entry for a student named Smith. John would be dependent on the name Smith. If you entered Jane Smith then you are no longer in 2NF, as there would be two entries dependent on Smith. In your scenario 'John' could be the only valid value for the primary key 'Smith'. If you made LastName and FirstName the composite key then you could enter both John Smith and Jane Smith as the combination of the names would be unique, but you could only have one entry for each of those names. As soon as you added another John or Bob Smith you would no longer be in 2NF. Finally, lets say Jane gets married and the last name changes to Jones, but there is already an entry for Jane Jones. The Primary Key JaneJones would no longer be unique and you would not be able to enter her as JaneJones, you would have to leave her as Jane Smith, as all the other tables are relying on the value 'JaneJones' to be unique and unchanging. Hoep this helps...

[@michk5149]

I like his voice for some reason

[@ComputerScienceLessons]

Tnx :) KD

[@artistryartistry7239]

Course Title and ID wouldn't be good, because students do sometimes have to take the same classes, if they fail, or want or a higher grade. Adding the date or year and semester they took the class seems like a good option though.

[@ComputerScienceLessons]

Agreed. Exactly how you implement this depends on the training organisation. Of course, this scenario is fictitious. :)D

[@artistryartistry7239]

@@ComputerScienceLessons Yes, and there was no date or semester field to choose from. :) I should've rephrased my comment differently. Based on the constraints of the example, what you demonstrated makes perfect sense. Thanks, this was really useful,

[@10010011001100110101]

Bit confused with the studentcourse table... Theres still a partial dependency (grade relies on course title and ID)

[@ComputerScienceLessons]

When we say something 'depends on' something else, we really mean that it is an attribute of the same thing. For example, the cost of attending the course and the maximum number of students that can attend are aspects of a particular course - so we say they 'depend on' it. The grade on the other hand depends on both the course and the student (Kevin got a B for Physics, but a D for Basket Weaving, Will got an A for computer science and an A for Quantum Physics).

[@LoopieDoo]

@@ComputerScienceLessons By that logic, on the Students table the last name depends on the first name because if we change the last name we must change the first name too and both first name and last name refer to student.

[@subhanahmed8864]

There is a functional dependency in StudentCourse table? If anyone can explain why and how not please do.

[@youssefhisham6527]

thanks dood

[@bobray342]

David Smith might want to consider the trades...

[@ComputerScienceLessons]

Who is David Smith? :)KD

[@Nabil_ali-mom]

Yes this need it

[@ComputerScienceLessons]

I hope this helps. :)

[@prakashraj4519]

5th normal form please

[@mikah4051]

Good job but my brain is sore from thinking so much.

[@ComputerScienceLessons]

Take it slowly - one step at a time. :)KD

[@mikemihay]

What do you mean when you say : "Every non-key attribute is functional dependent on the primary key." So if we have a non-key depending on a composition key, this will satisfy 2nd Form ? versus having the same non-key depending just on one key of the composition key => thus this will break 2ND Form. Is this correct ?