Lec 4: Functional dependency in DBMS | What is functional dependency | Database Management System – Transcript

00:00In this lecture we will talk about functional dependency. What is functional dependency? How to find out functional dependency from a given table or relation or you can say whatever is given that is functional dependency or not. To check that is functional dependency or not. See this is very important topic for the point of view of normalization. If you get it what is functional dependency the basics of functional dependency very well then you can

00:26easily get the normalization concept all the normal forms. If you are not good if you don't know what is functional dependency how to check out that this is functional dependency or not or you can say how to write down functional dependency of a table then you will never get the concept of normalization or the normal forms right. So now we will discuss what is functional dependency in this video with a small example and after that I will discuss

00:54with a complete example we will discuss all the functional dependencies from that table. We will draw functional dependencies from that table right with the help of proper example. See here functional dependencies basically you know we represented something like this with the help of an arrow right. If I say see as the name suggests functional dependency means dependency some

01:21something is dependent on another thing or some attributes are dependent on another attributes like this right. Now if I see x determines y let us suppose I am taking a simple example I am taking one relation or you can say one table there I have only two attributes right. These are having some values this is a relation r or a table right. Now if I write the functional

01:50dependency is x to y means x determines y. It means if I tell you value of x then from this table you can search value of y. See here x determines y doesn't mean that I give you the value of x and you will compute the value of y something like this. See suppose y is equal to x square plus 5x. So now y

02:18is dependent on x and if I give you x is equal to 5 then you can tell me what is value of this y that would be 50. Here we are computing the value of y by putting value of x here right functionally dependent this y is functionally dependent on this x but in this case here in the case of dbms I am discussing functional dependency in this case this is not a case here you

02:45just give you I just give you the value of x and you can search value of y from the table. It's not like that you don't have any table and I will tell you value of x and you will tell me value of y by calculating or by computing something no. For this you need a table from the table according to value of x you can tell me value of y right that is you can say a functional dependency it means

03:13y is you can say functionally dependent on x or you can say x determines y fine. So here x is known as determinant and y is known as dependent. Now let us fill this table with some values and then we will see in this table this functional dependency exists or no you need to follow some rule to check that this is a functional dependency or not in this table right I'll tell

03:40you all the rules. Now let us take in this table I have these values x value is 1 2 3 4 y value is this one this this this right now see if I tell you if I tell you that value of x is 2 now according to 2 tell me what is value of y from this table obviously we will search value of y from this table and the searching would be based on some rules see now what are those rules I'm saying value of x

04:08is 2 now can you tell me value of y find out where is x value 2 value of y is 1 yes I can tell you value of y is 1 right. Now suppose here I have inserted one more that row that is this one 2 and 5. Now if I say value of x is 2 now tell me value of y from this table search what is value of y now x is 2 y value is

04:381 but again I have x is 2 and y value is 5 now which value you are going to tell me 1 or 5 I cannot say so this cannot be a case see the same example if y is equal to x square right and x is equal to 5 I am taking right so now for x is equal to

04:595 y is equal to 25 right x is equal to 5 y is equal to 25 if I write x is equal to 5 y is equal to 35 then definitely it would be wrong that is not that cannot be a case right when you functionally compute a value right this is related to this thing here we are not computing but we are searching but the that logic is

05:24same now x 5 x 5 then at the both place y should be 25 it cannot be 35 if x value is same y value should be or you can say must be same I hope you are getting my point right so here x value is 2 and 2 so y value must be either 1 and 1 or 5 or

05:495 right it cannot be a case that here y value is 1 for this 2 and for this 2 y value is 5 so here we cannot say that x determines y because obviously x cannot determine y in this case if I because on the base of x you cannot tell me value of y then how can you say that x is determining y we cannot say so this is

06:14not a functional dependency in this case right if this is the case fine now see here second case may be there if I am taking x is equal to minus 5 then also y is 25 right but vice versa is not true if y is equal to 25 right and in 2

06:39tuples y is 25 25 it doesn't mean that x should be same that x should be 5 here for x minus 5 also I can get y 25 this case also we will discuss with proper example right now for now just keep in mind this thing if x value is same in 2 rows or in 2 tuples then y value should be same y value must be

07:06same now see this table is there I have removed that row now if I say x value is 4 tell me value of y can you tell me yes x value 4 no repetition of 4 is there in the sex so I can tell y value is 3 right same example like in my class I have 60 students and obviously name or sorry the roll number of every student is different right for the unique identification roll number is there now

07:34if I say roll number 15 tell me your name then obviously only one student is having roll number 15 right means with the help of roll number I can determine each student of my class but now suppose in a seminar hall I'm taking a class and there I have student of CS as well as EC department right now there if I say roll number 15 tell me your name then I cannot determine with the

08:03help of roll number that the name of the roll number 15 is what because in CS also there would be roll number 15 in EC also there would be roll number 15 yes suppose in CS I have 60 student there also I have 60 students right so here I cannot determine with the help of roll number so here roll number determines name is not a functional dependency right but in previous case it was functional dependency so it depends based on the relation whatever relation

08:30you have based on that relation you need to find out functional dependency right now formally how to write down the definition of this functional dependency see functional dependency x 2 y means x determines y this is true on a relation if see I'm taking the case here I have 2 and here I have 5 now see here x

08:57determines why that is not true because x value is 2 but y value is here 1 here 5 if here I can write 1 then this would be a functional dependency right so now if in two tuples like t1 dot x equal to t2 dot x fine here t1 and t2 means any two tuples and x means x values like suppose I'm taking this tuple and this

09:23t1 dot x t1 dot x is 2 t2 dot x t2 dot x is also 2 right if this is a situation see this is not assignment operator this is I'm comparing the values right then y value must be same right same case here yx square x is 5 then y is 25 again I'm writing if x is equal to 5 then y must be 25 right that is true but here if I

09:52write 35 then that is wrong that is not a functional dependency now right so now then t1 dot y equal to t2 dot y this should be a case then only you can say that x this is a functional dependency x 2 y this is functional dependency on this relation right now here check if this is the table then I'm taking any two tuples

10:19suppose I'm taking this tuple first of all t1 and t2 right now check if t1 dot x t1 dot x is 1 t2 dot x t2 dot x is 2 is this true no in that case if this is not the k this is not true on the two for the two tuples you are checking then no need to check for y value y value may

10:46or may not be same right see here you can say y value is same but it doesn't matter because if this is the if this is true then only we will check this condition then only this must be true but it's not like that if this is false then it must be false or if this is false then it it may be true it may be false we does not check this condition if this condition is false

11:14right so here we will not check y values right now suppose I'm taking these two tuples two and three now value is two in this tuple value is three is the same no we will not check y value now check for this tuple this one and this one this is suppose t1 this is two t2 t1 dot x value is two t2 dot x value is two now this condition is true because both are two now you must check this

11:42condition now check t1 dot y t1 is this t1 dot y is one is equal to t2 dot y t2 is this root t2 dot y is five but this condition is not true in this case right so this is not a functional dependency here I hope you got my point suppose I'm writing this one now can I say

12:05this functional dependency exist in this relation yes because see x determines y means check out for any two tuples any two tuples check out value of x for this and this value of x is not same so we we are not bothering about value of y will not check for this and this values are not same for this and this values are not same for this and this values are not are not same right means

12:30no value of x is repeating repeating in this case right if the value of x is repeating then only we care about the value of y but here every value of x is unique so this would definitely be a functional dependency if every value of x is unique then definitely we can determine value of y from x the same case if in my class roll number are different so every student is

12:59having different roll number so definitely I can identify the name of student from the help of roll number name may be same or not see roll number is 14 roll number is 15 right suppose both are having name inertia but I don't care about this because I have what roll number 14 and 15 to identify if I say 14 then she'll say yes ma'am if I say 15 then she'll say yes ma'am right it means