hi everyone welcome back to my YouTube channel I am RTI and I'm back with another lecture in the tbms series so in the last couple of lectures we learn more about normalization and denormalization in this particular video we would be learning about functional dependency which would be really helpful in understanding all the normal forms such as first 2 third and bcnf so without any further Ado let's get started now as the term suggest functional dependency so whenever there is a kind of dependency which is present in form of any function we can call it as functional dependency you might have heard about the terms such as equations or functions in maths now if there is a function given y = to FX that is y is a function of X where FX is = x² + 2x + 1 so if I know the value of x I can determine the value of y so what I'll do I'll just put the value of x here so that would be 1 + 2 + 1 that is 4 so here if I know the value of x I can determine the value of y so Y is a function of X so we can say that Y is functionally dependent on X because whenever we know the value of x we can find the value of y now in functional dependency it describes the relationship between the attributes in a relation so attributes you can say as columns and relations you can say as table so whenever there is a table consider that this is the table the table name is r or we can say it is a relation where we have two two attributes X and Y we can say columns so if Y is functionally dependent on X it means if I know the value of x I can determine or find the value of y so a functional dependency is a constraint between two set of attributes in a relation from a database so it's not important that it will always happen between single single attributes there could be a set of attributes which could be functionally dependent on some set of attributes so consider that in a table there is a prim AR key now primary key can either be a single attribute or can be a set of attribute so if R number is something which is being repeated for each or every person in a combined Branch like CSC EC so there could be a possibility that in CSC also a person is role number one in EC Branch also there is a person with role number one so what we will do is we will take a composite attribute we will combine two attributes to make it as a primary key or a unique identification key using which we can find the details of that particular person or that particular student in a college now for a relation table R if there are two attributes X and Y then we can say the functional dependency is X determines y or we can say Y is functionally dependent on X where Y is called as dependent and X is called as determinant so X is called as determinant because if we know the value of x we can determine the value of Y and Y is somehow dependent on on the value of x so if I know the value of x I can find the value of y consider in a normal table that if there is a raw number and there is a name so if I know the r number of any person I can tell the name of that particular person so roll number is kind of a determinant and name is kind of a dependent and name is functionally dependent on RW number because if I know the value of R number I can determine the value of name so attribute y is functionally dependent on attribute X so Y is functionally dependent on attribute X or we can say x determine y now if x is equal to 1 we can find the value of y the same example which I show here that if we know the value of x we can find the value of y here as well now functional dependency X determines Y X Y is a subset of the relation so if there is a table or there is a relation then X and Y is always a subset of that particular relation here I'm talking about subset but what subset exactly is so let there be two sets A and B now a is having value 1 2 and 3 and b is having value 1 2 3 4 and 5 now a is a subset of B because every element of a is also an element of B so we can say that a is a subset of B because every element of a one one is also present here two two is also present here three three is also present here so a is a subset of B Because all the elements which is present in a is also present in B but VI Versa is not true because four and five is not present in a so we can say that a is a subset of B because every element of a is an element in B now here there is a table employee ID employee first name and employee last name so here there are some of the functional dependency which is provided so first is that employee ID will always determine employee first first name so let's see if employee ID can determine employee first name so if I know the employee ID as one is there any employee ID with one existing in this particular relation no now uh in correspondence to one what is the employee first name that is REI so if I know the employee ID I can know the employee first name so we can say that employee first name is functionally dependent on employee ID or if I know the employee ID I can find the employee first name so you can also say that in functional dependency X is kind of a unique identification key unique identification key so basically this is a unique identification key which help us to identify or determine or find the value of y when we know the value of x so for this employee ID also there is no two which is present in this particular column so the value would be Rahul for three there is no three which is present in this employee ID column so we can say the value would be surj so we can say that employee ID is determining employee first name or employee first name is functionally dependent on employee ID so employee first name is functionally dependent on employee ID in the same way employee last name is also functionally dependent on employee ID now here you will see that you have written the functional dependency but how will we identify that if there is a table or there is a relation what are the functional dependency which are existing so the first way to determine that is that if there is a X which is determining y x can always be a set of attributes and Y can also be a set of attributes so we can also say that employee ID and employee first name both are determining employee last name so X would be this set of attribute which is employee ID and employee first name in the same way we can also say that employ employ ID is determining employee first name and employee last name so here Y is also a set of attribute so this thing first we need to understand that X determining y x can always be a set of attribute and Y also can always be a set of attribute now how we will know the functional dependency in a given table now according to functional dependency definition if there is a relation r x determines Y where X and Y are the attributes of the relation and X can be a attribute or a set of attribute y can also be an attribute or a set of attribute and Y is functionally dependent on X now here can you say that X or the one which is a determinant is always a primary key so can we say that X is a primary key the answer is no it's not always true that X will always be a primary key this is a unique identification key but it's not a primary key it can be a primary key but it's not important for X to be a primary key it should always be a unique identification key now how do we find that if there is a x which is present or if there is a attribute which is present is acting as a determinant or not so as I told that it is not always true that it will always be a primary key so there could be a possibility that there is some duplicacy in this employee ID column so again the employee ID is one the name is considered Ria and the last name is sing so here you can see now employee ID is having two ones which is present in the table so it is not unique so primary key violation happens because this particular employee ID or this particular attribute doesn't have unique values now if we want to now see that if this employee ID determines employee first name is true or not for this particular table what we need to do is if there are two tles or you can say two records and the value of x is similar for both of the records then the value of y should always be similar so that these are functionally dependent so if there are two tles T1 and T2 and if T1 dox is equal to T2 dox that is this is equal to this then T1 doy should always be equal to T2 doy for the functional dependency to exist so let's see is T1 doxal to T2 dox 1 is equal to 1 so this is true so let's see if t1. Y is equal to T2 doy or not so T1 doy is reti and t2. Y is Ria so is t1. y equal to t2. Y no so now we can't say that employee ID determines employee first name because consider that if I provide the employee ID one what would be the value of why it will return because right now one is having the value as reti and one is also having the value as Ria so now employee ID is not determining employee first name but what if here the value is written as re so now is the employee ID determining the employee first name yes because one is always corresponding to only one value one is not corresponding to two values one is always corresponding to only one value so this is the criteria which we use to follow to find the functional dependencies which are present in our relation so if there are two tles who are having the same value such as like the same record is present in that particular particular two tles then we should always check the value which is being determined or which is functionally dependent on that value so if there is employee ID which is having values such as one and one then we need to check that what is the corresponding value uh to this one because there could be a possibility that this one is giving me reti and this one is giving me Ria so one is having multiple values one should always hold single value because this is a unique identification if I say that one is also giving me r one is also giving me Ria so the unique identification does not exist X shouldn't be a primary key it can or it cannot be but X should always be a unique identification so this is called as functional dependency so whenever we encounter that if there is a employee ID column which is having unique values or where there is a primary key we don't need to check for all those things because if it's a primary key it will always determine some unique values only but if there are some duplicacy which is present in this employee ID then we need to check this condition that for given two tles if the value of x is similar the value of y should always be similar if it's not similar then it's not functionally dependent or the functional dependency does not exist I know this can be a bit confusing but when we will learn about normal forms that would be clear more and more for now you can just understand that in functional dependency there is a relation and then there are some attributes one attribute is functionally dependent on another attribute attribute it basically means that if I know the value of x I can determine the value of y x and y can be a set of attributes X is not always needed to be a primary key it can or it won't be but X should always be a unique identification key that is for one value of x there should always be one value of y there shouldn't be a multiple values of Y now there are some properties of functional dependency or we can also call it as inference rules now what are those properties so the very first property is reflexivity now what is this reflexivity so if there are two attributes X and Y where Y is functionally dependent on X and X determines y we can see that if Y is a subset of X then X determines y or X determines X now how this comes X determines X so here I told that Y is a subset of X so Y is always having some values which is present in X so y will always have the value X so we can say that X Det determines X so a particular attribute can always determine itself and it can determine y if Y is a subset of X so X determines X always holds true in terms of the reflexivity property because Y is a subset of X so y would always be having values which are having X so X determines X always holds true now the second property is augmentation what does this means that if x determines Y and if we are augmenting Zed here then X x z should always determine y z for any Z so consider that there is a employee ID which determines employee name now if I augment or add something so if employee ID I add a employee phone number so employee ID employee phone number both together can determine employee name and employee phone number so I can see that if something is added at LHS and the same thing is added at rhs then there would be a functional dependency which will exist between this combination now the third one is transitivity so what this transitivity says us if x determines y so if x is a attribute which determines Y and then Y is a attribute which determines Z then X is a attribute which will always determine Zed so if x determines Y and and Y determines Z then X will always determine Z we don't need to check for this dependency if this exist or not if x is a attribute which determines Y and Y is an attribute which determines Z then we can say that X will determine Zed as Y is common between them so this is called as transitivity now the fourth one is union now what union says is if x determines Y and X determines it since LHS is common in both the cas cases we can see that X will determine y z so since X determines y x also determines Z so since employee ID determines the employee first name and employee ID determines the employee last name then we can say that employee ID can determine employee first name as well as employee last name so there is a union which happens whenever the LHS is equal but the vice versa is not true if I say y z determines X I can't say that y will determine X and Zed will determine X this is wrong because again YZ is something which is a combination which is helping me for The Unique identification or this is a unique identifier or a key so if the both of these key are helping me to determine one attribute I can't say that a single attribute can determine this particular attribute because the composite version of this is helping me to determine the attribute so if x determines Y and X X determines Z we can say that X determines y z but we can't say that if y z determines X Y will determine X and Zed will also determine X that is wrong and that is not true now the fifth rule is Rule of decomposition so if x determines y z we can say that X determine Y and X determine Zed which is the same which we learned in Union so it's just the reverse of Union so if x determines y z we can say x determine Y and X determine Zed so the determinant can never be broken determinant can never be broken if it's a set of attributes it can't be broke into single attributes and determine something because the set of attribute is helping in the unique identification not the single single attributes right so this can't be broken the rhs can be broken the dependent can be broken but the determinant can't be broken now what are the types of functional dependency so there are two types of functional dependency one is Trivial and the second one is non-trivial there are more but basically now we need to know about these two only trivial and non-trivial so let's see what is Trivial so functional dependency X determines Y is Trivial if Y is a subset of X so it says that if Y is a subset of x x can determine Y and X can also determine itself that is X so this particular dependency is always valid we don't need to go ahead and check in our table because X can always determine X employee ID can always determine employee ID if I say that employee ID is one find the employee ID in the table I can always find the employee ID because I know the employee ID is one here so X will always determine X this is a valid dependency this doesn't needs to check that if there are some employee IDs which are present if again there are some employee IDs which are present because we are seeing in the same column or you can say in the same attribute itself also since Y is a subset of x y will be having values or the elements which is present in X so we can say x will determine X so we can also say it as X determines X now consider that here there is a dependency given where employee ID and employee first name is determining employee ID now this is y and this is X now in y we have employee ID and in X also we have employee ID so we can say that Y is a subset of X because the element which is present in y is also present in X so we can also say that employee ID determines employee ID and employee ID employee first name determines employee ID now this is Trivial because employee ID is a subset of employee ID and employee first name so we can say that trivial dependency exist whenever X intersection Y is always y so whenever we are doing an intersection between them so we are doing an intersection between employee ID and employee ID an employee first name so in intersection we used to provide the common which is present between them so in employee ID employee ID and employee first name employee ID is the common one so we give the value as employee ID for a result so X intersection y always gives us y y is employee ID so here we get the employee ID so this is the trivial dependency now what is non-trivial dependency a functional dependency X determine Y is non-trivial if Y is not a subset of X so Y is not a subset of X basically which means that if there are some uh values which is present in y or if there are some attributes which is present in y it's not necessary for that attributes to be present in X so here if you can see X is having employee name and Y is having employee ID so employee ID is not present in employ name so Y is not a subset of X so these dependency needs to be proved because so right now I am saying saying that employee ID is getting determined by employee name or employee ID is dependent on employee name but is this true like we have to check that if this is valid or not in treil what used to happen is X determines Y and Y is always a subset of X so y always contains some element which is there in X or Y is always having some attributes which is in X so this is always true but when X determines Y and Y is not a subset of X then we need to prove that point okay if employee name is determining employee ID I can check from my table if this functional dependency is present or not present so a functional dependency X determines Y is not trivial when the Y is not a subset of X so if there is X as employee ID and Y as employee first name we can say it is non non trieval because employee first name is not a subset of employee ID so employee first name does not have any element which is there in employee ID so here if we do X intersection y we get five because X intersection y does not gives us anything because there is nothing common between employee first name and employee ID so it will give me f or it will give me empty this was all about this video I hope you like this video so if you like this video please hit the like button if you're someone who is new to my channel can go ahead and watch out the tech content first and if you find it useful can go ahead and subscribe also if you have not followed me on my social media handles you can go ahead and follow the links are in the description till then take care keep learning keep growing keep smiling bye all