[0:00]First normal form. Today we are going to discuss the first normal form. So, what is the rule for the first normal form? According to the E.F. Codd, E.F. Codd, guys, father of D.B.M.S. Their rule for first normal form is the table should not contain any multi-valued attribute. It means, a table should not contain any attribute that has more than one value for a single particular person. Like, if I have a student table, and I have Roll no., Name and Course in it. Now, Roll no. 1, Sai, C and C++. What is the meaning of that? Sai has registered in C and C++ both courses. Yes, it is possible that a student is enrolled in more than one course. But my motive here is how we represent it, the representation is wrong. We cannot represent like this. Because here, there are more than one values for a student. Besides course, guys, there can be address. Address can have more than one value for a student, phone number can have more than two values. So, if we look at this table, I can clearly say that this table is not in first normal form. Because here, the problem is with the Course.
[1:26]Because for Sai, there are more than one values, and even for Onkar, there are more than one values. So, if even a single attribute in your table has more than one value, then you can clearly say that the table is not in the first normal form. So, the next question is how to convert this table to first normal form? For that, we have three solutions, one by one we check all the solutions. The first solution is to draw the table, or you can say create the table like this. Roll no., Name, and Course. Attributes, same three attributes here, the same three attributes I have written there. But how we will insert the row? One, Sai, C, and one, Sai, C++. I have converted it into two rows. Two, Harsh, Java. Three, Onkar, C.
[2:39]Three, Onkar, DBMS. So, now, the representation will be like this. So, I have divided the table like that, that a roll number has been written multiple times. So, if someone asks you in this table what will be the primary key in this table, then, guys, you can clearly check that values are repeating in the Roll no., so it cannot be a primary key. Name cannot be a primary key. Course, yes, course can be, but Course alone is not a primary key because C and C values are repeating over there. But if I combine Roll no. and Course, then definitely it will be the primary key. So, if I mention the primary key separately here, then the primary key will be Roll no. Course combined, which we call composite primary key. So, this is the first representation like that. The second representation will be the same table. Same table we are representing like that.
[3:51]Like Roll no., Name of the student, Course. Now, in courses, guys, I know what is the maximum value of the course. It means, a student is enrolled in a maximum of how many courses? So, here, a student is enrolled in a maximum of two courses. So, I have made two separate columns. One is Course 1 and one is Course 2. So, Roll no. 1, Sai, C, and C++. Roll no. 2, Harsh, Java. Now the important part is what will be the Course 2 for Harsh? He is not registered in Course 2. So, that value will be Null. Null means empty. It is not zero actually, Null means not available, the value is not existing over there. So, you have to leave it empty. So, three, Onkar, C, and DBMS. So, this will be the second kind of representation. So, in this, you can be asked what will be the primary key in this table? The same question, what can be the primary key in this table? So, guys, in this, Roll no. can directly be the primary key. Only Roll no. alone can be a primary key. So, we can say it's a simple primary key, that is Roll no. But if you look at this, there is a problem that a student is enrolled in N number of courses, and another student is enrolled in only one course. So, what will happen, guys, you are writing the columns again and again for one. You are writing the N number of columns and there will be some values for that. But there will also be a student who is enrolled in only one course, so what you will write? Null. So, if you represent Null so many times here, then overall there will be more Nulls in the table representation. So, which is not a good representation. So, you can represent in the third format. The third format will be like, uh, this table. We divide this table itself into two tables. One is a base table. In the base table, we can have Roll no. and Name. In the Roll no. like Roll no. 1, Sai, Roll no. 2, Harsh, Roll no. 3, Onkar. And there will be one referencing table or another table that table will contain Roll no. and Course. So, Roll no. 1, course will be C. 1, C++. 2, Java. 3, C, and 3, DBMS. So, here, if this is the base table, why are we calling it a base table? Because primary key will exist in this, that is the Roll no. So, Roll no. will be the primary key here, but there is no foreign key in this because this is the base table. But here, we call this a referencing table because this Roll no. here is working as a foreign key. It will take reference from where? As we already know that a foreign key always takes reference from a primary key. So, our primary key in this table is Roll no., so this will take the reference from this. And here, if you want to see what will be the primary key, then Roll no. and Course combined will be the primary key. So, I can represent here below that the primary key of this table will be Roll no. And there is no foreign key in this because this is the base table. But here, what will be the primary key? Roll no., Course combined.
[7:47]And what will be the foreign key? Foreign key will be Roll no. which will take the reference from the Roll no. of the base table. So, guys, you can represent it like this. In this, if you want to see, if a student is enrolled in N number of courses, then you can easily enter the values over there. But in the base table, there will be only one entry for that student. So, this is a good representation of the first normal form. So, that means if you have any table, you can convert it into first normal form in three ways. So, you can implement these three methods one by one and you can compare them. Thank you.
