馃攳
Lec-23: Functional Dependency & its properties in DBMS in HINDI - YouTube
Channel: Gate Smashers
[0]
Functional dependency
[1]
Functional Dependency is method which
[3]
describes the relationship
between the attributes.
[6]
That means if I have a lot of attributes
[8]
in my table.
[9]
What is the dependency between them,
[11]
what are the relationship between them that
[13]
We represent through functional dependency.
[16]
Okay. How? Let's see, like
[20]
The general format is like X goes to Y
[24]
Means, what is the meaning of X goes to Y?
[26]
It means X determimes Y
[30]
X determines Y.
[35]
or you can say
[37]
Y is
[39]
determined
[42]
by X.
[46]
Y is determined by X.
[48]
Here
[49]
X is a
[52]
determinant.
[56]
Means an attribute X that is determining
[60]
This is the main attribute.
[61]
And Y is a dependant attribute.
[68]
Y is a dependent attribute.
[71]
And X is a Determinant Attribute.
[73]
who is determining Y.
[75]
Let's see with the simple example.
[77]
Like If I have one
[79]
dependency given,
[81]
like SID goes to S.name
[84]
Means, I wrote SID instead of X.
[86]
S.name instead of Y.
[88]
Now, What is this funda of functional dependency?
[91]
Like if I have some value in S.name
[94]
Like if I'm putting two values here.
[98]
Ranjit
[100]
Ranjit
[102]
Means
[103]
I have put some values in S.name.
[105]
Ranjit & Ranjit.
[107]
What was this meaning of this?
[109]
Now I have a confusion here.
[111]
Confusion of this what?
[113]
Are these two different ranjit?
[115]
Or It is the same Ranjit
[117]
whose value is written twice.
[119]
means, suppose in my class, here I am
[122]
writing the student name in my class.
[124]
Ranjit Singh & Ranjit, Ranjit & Ranjit
[127]
So there were two ranjit here.
[129]
So here, I may have a confusion
[131]
whether there is only one Ranjit
[132]
whose data is coming twice
[134]
Or is it two different ranjit?
[135]
So how do we remove this thing?
[138]
I will go to the SID.
[140]
SID is a kind of roll number.
[142]
in the roll number, i saw
here it was written one.
[146]
Here it was written two.
[148]
Means
[149]
This is ranjit whose roll number is one
[152]
& this is ranjit whose roll number is two.
[155]
So here my confusion is cleared that
[158]
these are two different students.
[160]
But there name are same.
[162]
Similarly likes I take another set, ta
[164]
ke another combination
[168]
that if
[174]
Again, I'm here, Ranjit
[176]
writing two names, Ranjit & Ranjit
[181]
Again same confusion
[183]
Is this a student
[184]
whose data has been written twice?
[186]
Or these are two different students
[188]
guys, when we are in class,
[190]
we can easily tell by standing two guys
[192]
in class and can easily examine,
[195]
whether they are two different students
[197]
or they are same student.
[198]
But when we use the database online
[201]
means the server is at somewhere else location
[203]
And User is using the interfaces
[205]
from the different location.
[206]
So there, when calling the database online,
[209]
it's very difficult to determind.
[211]
Whether these are two different students
[213]
Or the same students.
[214]
So I went to the SID again
[217]
and its written in SID one
[220]
& one
[221]
Means, again my confusion cleared here.
[224]
This is only one student ranjit
[226]
but accidentally his data,
[228]
will be written twice.
[229]
So that means my confusion here cleared.
[231]
This is the same student, Ranjit.
[233]
That means whenever you have problem
[235]
in dependent variable,
[238]
You go to determinant variables,
[241]
attribute,
[242]
& it will clear your confusion
[246]
Is it Clear? then let's see
[248]
What are the different
[251]
Combinations can be formed in it.
[253]
Like if take the value of X and Y
[256]
X and Y
[257]
So in the value of X and Y, like X goes to Y
[260]
So, if SID goes to S.name
[265]
So four combinations, like
[266]
if I'm writing first Ranjit
[269]
Ranjit.
[272]
Here SID one
[274]
SID one
[277]
This is the case one.
[279]
Case two is
[290]
Ranjit, Ranjit.
[293]
One
[296]
Case three is
[307]
Like, Ranjit.
[310]
Varun, One, two
[331]
These four cases have been made
[332]
What i did that i kept both the values same,
[334]
here also same,
[335]
Both the values same here. Here are different
[337]
Both the values are different,
[338]
here are also different.
[339]
Both the values are different,
here are same
[341]
So these four cases are possible in it.
[344]
So see the case number one.
[345]
Ranjit, Ranjit.
[346]
Now theres is a confusion,
[348]
is there are same student or two different?
[350]
I went to the SID
[352]
It said both are same student.
[353]
His data may have been
written twice by mistake.
[356]
So that means my confusion has cleared
[358]
So, This case is true
[359]
Means this case is valid
[361]
We can say, this is a valid case,
[366]
This is you have to check,
[368]
weather it is a valid and invalid.
[369]
Yes, it is a valid
[371]
Now check for this
[373]
Ranjit, Ranjit.
[375]
Is there one student or two students?
[377]
So I went to the SID.
[379]
It said roll number one and two.
[382]
Ranjit, Ranjit means there are two students
[385]
& Their names are by chance same.
[387]
So, this case is also valid.
[389]
Means, I'm getting the answer here too.
[391]
Come to the third case, Ranjit , Varun
[394]
Means, two students are different.
[396]
As the name suggests, Ranjeet and Varun
[398]
are two different students.
[399]
Still, I check from the SID
[401]
SID is also saying that roll number one
[403]
is of ranjit & roll number two is of varun.
[405]
Yes, this Case is Valid.
[407]
It is also giving me the correct output
[409]
Let's check for the fourth
[411]
S.name,, Student name is ranjit and varun
[413]
Means, there are two students here,
[415]
Ranjit and varum
[416]
Once again, we go to the SID
[418]
But see guys,
[420]
What's SID saying here?
[422]
One, one
[424]
Means there is only one SID
[426]
SID value is one.
[428]
But how the S.name can be two?
[432]
When determined attribute
[434]
which is determinant,
[435]
who is the main variable, main attribute
[438]
It is saying there's only one student here
[440]
But the dependent is saying that
[442]
there are two students.
[443]
This is invalid case.
[447]
Invalid
[449]
Because what is the student name saying?
[451]
There are two students.
[452]
One is Ranjit, other name is varun
[454]
But what is SID is saying?
[456]
SID is saying that there are only one student.
[459]
So, the one who is determining
[462]
If he says that there is only one student
[465]
Then how the student name
[466]
can say that there are
two different students here?
[469]
So this case is invalid.
[471]
Many times we get questions in the GATE
[473]
or in normal exams also.
[475]
Where we have to check the case that
[477]
Which are such cases here?
[479]
So if I am saying that
[480]
the SID is determining the S.name
[482]
Then, this is only the invalid case.
[484]
Rest of all three are Valid cases.
[487]
In this, I'm assuming that
[489]
SID Iis determining the student name.
[492]
Is it clear guys?
[493]
So this is the important part over there.
[495]
Now, the Functional Dependencies are of
[498]
Two Types. One is trivial
[500]
And non trivial.
[501]
That also you check it once.
[503]
What is trivial
[505]
and non trivial.
[508]
Trivial function dependency.
[509]
What is trivial functional dependency.
[517]
Trivial FD is
[519]
if there is
[521]
any dependency is X goes to Y
[523]
So Y
[525]
is a subset of X.
[527]
That is called a trivial.
[528]
You write it down.
[529]
If X goes to Y,
[531]
is any of the functional dependemcy
[532]
Then Y is
[535]
subset of X
[537]
This is a general format, guys.
[539]
You have any student ID, student name,
[541]
student's father name, phone number,
[543]
roll number.
[544]
You can fill the value of any attribute in it.
[546]
If I'm saying here.
[549]
Y will be the subset of X.
[551]
So what is the meaning of that?
[552]
if I'm saying that SID
[554]
goes to S I D.
[557]
Mean this is X,
[558]
This is Y.
[560]
So Y is the subset of X
[562]
Yes, SID is the subset of SID
[566]
So this trivial functional
dependency are always valid.
[570]
They are always true.
[572]
Means You don't have
to make these four cases.
[575]
Because it's always valid,
[580]
It is always true.
[581]
Reason being is
[584]
The thing we are determining,
[585]
the attribute we are determining,
[587]
is a subset of the left hand side.
[591]
So means, in a way, there is a reflexive property
[593]
that I am determining myself.
[595]
When an Attribute is Determining itself,
[598]
So in that, there is no need to check the values
[600]
Because it will always be Valid.
[602]
There's another way to check it.
[605]
If X goes to Y
[608]
So if you take intersection of
[610]
the left-hand side and
[613]
the right hand side.
[614]
If you take the intersection of
left hand side & right hand side.
[617]
That will
[619]
never come empty.
[621]
In case of trivial functional dependency,
[624]
if you take the intersection of the left hand side
[627]
& right hand side, that will never come phi.
[630]
Means, I'll give another example here like
[632]
SID
[633]
S.name
[635]
goes to SID.
[637]
Means created a functional dependency,
[639]
SID, S.name combinedly
[641]
determininh the SID,
[643]
so if take the intersection of
left hand side and right hand side.
[645]
Then what will come? SID
[648]
So yes, it is a Trivial.
[650]
It is always Valid.
[653]
Now.
[654]
Check for the.
[657]
Non trivial
[661]
Chexk for the non trivial
[670]
Non trivial FD means
[672]
If I'm writing X goes to Y
[675]
X goes to Y
[676]
Then X and Y
[678]
Means Y is not a subset of X.
[681]
Mean if these two
[683]
If i take X Intersection y
[686]
Then what will come, phi will always come.
[689]
Mean, SID goes to S.name
[693]
Student ID goes to Semester
[697]
Student ID
[698]
goes to.
[699]
Phone number.
[701]
Employee ID.
[703]
goes to Employee Name
[705]
Employee ID goes to location
[708]
These are all, these are all
[711]
Non trivial Functional dependency.
[713]
In this if you take the intersection of
[715]
left hand and right hand side.
[717]
Then it will always come phi
[719]
In this case, you have to check the cases.
[723]
In it, you have to check the cases.
[724]
& among these you
have find out which is valid or
[727]
Which is invalid?
[729]
Okay guys.
[731]
These are some properties,
[732]
please note it down..
[737]
properties of Functional dependency.
[740]
Note that properties of
functional dependency.
[748]
First property is
[750]
Reflexivity.
[752]
Reflexive, as i dicussed in the trivial
[754]
What it says, If Y is a subset of X,
[757]
then X goes to Y.
[758]
This is a trivial.
[760]
means reflexive.
[761]
An attribute.
[763]
determining itself.
[766]
That is called Reflexive.
[767]
And guys, you don't need to check,
[769]
it is valid or invalid, because it is always valid.
[773]
Augmentation
[775]
Augmentation means, if X goes to Y
[777]
Then XZ goes to YZ, means
[780]
If SID
[782]
determining Student name
[785]
ID of the student is
determining name of the student.
[788]
So you are augment any attributes,
[790]
putting extra in it.
[792]
Like, Phone number.
[794]
So the student's name
[796]
phone number, this is also valid.
[800]
Means this is same,
[801]
if SID is determining S.name
[803]
Then, SID Phone number will determine
[805]
S.name phone number as well.
[806]
Transitive
[807]
Transitive property is again
very important property.
[812]
Because this is going to be used
[814]
a lot in the future.
[815]
What is the transitive property?
[817]
If X goes to Y and Y goes to Z
[820]
Then X goes to Z
[822]
Means if the student's ID
[826]
is determining student's name.
[828]
And
[832]
The student's name is determining, like City.
[835]
Then
[837]
student's ID
[838]
will determine the city.
[842]
It's been too much used in future
[844]
Transitive property.
[845]
Union, if X goes to Y and X goes to Z,
[848]
then X goes to YZ
[850]
Means we just simply union Y & Z
[855]
Decomposition, if X goes to YZ,
[858]
then X goes to Y
[859]
and X goes to Z
[861]
But here guys, very carefully you have to check.
[864]
If here I say, XY goes to Z
[867]
Can we write X goes to Z.
[870]
Y goes to Z
[873]
No.
[876]
XY goes to Z, X goes to Z,
[878]
Y goes to Z is invalid
[880]
because
[881]
What is the meaning of this?
[883]
XY together
[885]
determining some Z.
[888]
And you broke them,
[889]
means separated the X, and Y
[892]
And then you're saying
[893]
that they determining Z. No
[895]
This is wrong.
[896]
this means that XY together determining some Z.
[900]
& you've written that X alone determining
[903]
Y alone determining, this is invalid.
[906]
You cannot decompose
[907]
the left hand side.
[909]
On the left hand side you can never
[911]
put a decomposition.
[912]
In the right hand side,
[913]
you can easily write them separately
[915]
Because it means X is determining YZ.
[918]
What was this meaning of this?
[920]
How can you say it? X is determining YZ.
[923]
So I wrote like this X is determining Y,
[925]
X is determining Z
[927]
This is valid.
[928]
No problem
[929]
but you can never decompose
[932]
the left hand side.
[934]
Then Pseudo transitivity,
[935]
pseudo transitivity means if X goes to Y
[938]
and WY goes to Z
[940]
Means here
[941]
X goes to Y and Y goes to Z is not there,
[943]
here W extra added here.
[944]
Then what they are saying,
[946]
WX goes to Z.
[948]
what is common on the left hand side,
[950]
that will defintely come on the left side
[952]
and here on the left side.
[953]
X goes to Y
[955]
WY goes to Z. So Means, WX goes to Z
[960]
Then composition, if X goes to Y.
[963]
and Z goes to W
[964]
Then XZ goes to
[967]
XZ goes to YW means the left hand side
[971]
Gathered here.
[974]
And right hand side, gatheredhere.
[979]
So many times, this properties
will comes in the exam.
[981]
Like.
[983]
in 5 marks or 10 marks question.
[985]
But if out of these properties,
[987]
you are going to use, the
mostly use properties are
[990]
Transitive and Reflexive. Remember it.
[993]
It is very important
[994]
And also remember in decomposition
[996]
never break the left hand side.
[1000]
& whenever we will check the validity invalidity.
[1003]
So always check in non trivial
[1005]
functional dependency
[1006]
Because
[1007]
trivial is a kind of reflexive
[1009]
Which is always valid. Fine.
[1015]
So guys, If you liked this video, please like it.
[1018]
And make a comment on that.
[1020]
Because this will give me motivation
[1022]
and definitely I will bring more good videos
[1024]
and please subscribe to my channel.
[1027]
Thank you.
Most Recent Videos:
You can go back to the homepage right here: Homepage