Second Normal Form (2NF) | Database Normalization | DBMS

hey everyone welcome back to study tonight's YouTube channel I would like to start with an apology for the delay in this video we received a lot of comments and requests over email for making video for the second and third normal forms and we want to thank you all for that it feels good to know that you guys are equally excited and loving the content so the doubt any further delay let's start in this video we will cover the second normal form of normalization we will discuss about the criterias required for a table to be in second normal form and why second normal form is so important for a table to be in second normal form it must satisfy two conditions first one the table should be in the first normal form and the second one there should be no partial dependency in the table we have already covered the first normal form in a previous video so if you missed that video we suggest you to go check out that video first now the main question is what is partial dependency to understand partial dependency you first need to understand what dependency in a table is so let's take an example of a student table in which we save student's name students registration number students branch and students address etc in this table student ID is the primary key and is unique for every row hence we can use the student ID to fetch any row from this table even for a case where student names are same if we know the student ID we can easily fetch the correct row or column of data primarykey is nothing but a column or at times a group of columns which can uniquely identify each row in the table or in simple words all you need is the primary key to fetch any data from the table for example I can say give me the branch name of student with ID 10 and I can get it similarly if I ask for name of student ID then I will get it so all I need is student ID in every other column depends on it this is dependency in table now that we know what dependency is we are in a better state to understand what partial dependency is so for a simple table like student a single column which is student ID can uniquely identify the complete row or all the other columns in a row but this is not true all the time now let's extend our example to see if more than one column together can act as a primary key or not let's create another table for subject in this table we have subject ID which is the primary key for this table and the subject name again subject ID can be used to get all the information stored in all the other columns unfortunately there is only one other column in this case now we have a student table with student information and another table for subjects during exams another table score is created to store the marks obtained by every student in the respective subjects also name of the teacher who teaches the subject is saved along with the marks so in her school table we will have columns score ID student ID subject ID marks and teacher name let's add some data to this table as you can see in the school table we are saving student ID to know which students marks are these subject ID to know for which subject in the marks are and the marks and the teacher name in this table we can say that the primary key is score ID but student ID and subject ID together forms a more meaningful primary key for this table and we can fetch all the information using it you would ask how see if I asked you to get me marks of student with student ID 10 can you get it from this table no because you don't know for which subject and if I give you a subject ID you don't know for which student hence we need student ID and subject ID together to uniquely identify any row of data because we have a many-to-many relationship here when I say that we have a menu to menu relationship here I mean a student can appear for any number of subjects test also a subject can be opted by any number of students now if you look at the school table we have a column named teacher which is only dependent on the subject for Java it is Java teacher for C++ it is C+ this teacher and so on now as we just discussed our primary key here is a composition of two column which is student ID and subject ID but the teacher's name only depends on subject it has nothing to do with student ID this is partial dependency and for a table to be in second normal form this should not exist now the question arises how to remove this there can be many different solutions for this but our objective is to remove teacher's name from school table we can do this in many different possible ways one of the ways is to move teacher name to the subject table where it fits appropriately along with the subject names we can also add the respective teacher's name or we can also create another table for teacher and use the teacher's ID wherever we want so this is the second normal form in this video we have tried to explain the second normal form using a very simple example we advise you to practice because we think that as you will practice more you will learn more out.how second normal form is actually very useful in practical applications also we advise you to start using Auto increment primary keys because that will make your life a lot simpler hey everyone hope you enjoyed this video and we promise we're coming up with a third normal form very soon apart from that we're also working on an entrepreneurship tutorial or course in Hindi especially for the Indian students that we have and apart from that we're also working on a very unique programming tutorial which will include a seven day challenge to learn something new like Java or maybe Python so we will start with unity engine to develop games in 2d and 3d so stay tuned if you haven't subscribed the straight right channel it's time you do

Channel: Studytonight with Abhishek

Views: 1,008,587

Keywords: database normalisation, second normal form, 2NF normal form, 2nd normal form, normalisation for beginners, 2nf for beginners, dbms normalisation 2NF, 2nd Normal Form DBMS, what is 2NF, table in second normal form, convert table to second normal form, convert table to 2NF, partial dependency, partial dependency 2nf, 2NF DBMS example, 2NF with example, database normalization

Id: R7UblSu4744

Length: 7min 12sec (432 seconds)

Published: Sun Feb 04 2018