{"id":198937,"date":"2024-02-25T05:43:14","date_gmt":"2024-02-25T05:43:14","guid":{"rendered":"https:\/\/www.henryharvin.com\/blog\/?p=198937"},"modified":"2024-04-02T13:59:09","modified_gmt":"2024-04-02T13:59:09","slug":"functional-dependency-in-dbms-types-and-advantages","status":"publish","type":"post","link":"https:\/\/www.henryharvin.com\/blog\/functional-dependency-in-dbms-types-and-advantages\/","title":{"rendered":"Functional Dependency in DBMS: Types And Advantages"},"content":{"rendered":"\n

Functional dependency in a DBMS is a relationship that exists when an attribute of a table uniquely determines the value of another attribute. In other words, if an attribute(X) acts as a unique identifier for one or more attributes(Y, Z) then we can say that Y and Z are functionally dependent on X. <\/p>\n\n\n\n

This dependency can be represented by an equation where an arrow(->) is used as a mark of dependency, the pointed side of the arrow represents the dependent attribute, whereas the other side of the arrow represents the determinant side.<\/p>\n\n\n\n

If R is a relation (table) with attributes X, Y, and Z; where X acts as a unique identifier for the value of Y and Z, Then Y and Z are functionally dependent on X, however we can also represent this dependency through an equation  X -> Y, Z  where the left side of the arrow (X) is Determinant and the right side of the arrow (Y, Z) is the Dependent.<\/p>\n\n\n\n

For example:<\/p>\n\n\n\n

Employee ID (X)  ->  First Name (Y),   Last Name (Z);<\/p>\n\n\n\n

Where every time the value of Employee ID (X) Changes, First name (Y) and Last name (Z) also change. <\/p>\n\n\n\n

Hence Y and Z are functionally dependent on X or can be represented in the equation as X -> Y, Z.<\/p>\n\n\n\n

6 Types of Functional Dependency in DBMS, <\/h2>\n\n\n\n

To better understand, let\u2019s learn them with suitable examples:<\/p>\n\n\n\n

Trivial Functional Dependency:<\/h3>\n\n\n\n

Where an attribute or set of attributes that are dependent on a determinant are the subset of the attribute that is determinant there will be Trivial Functional dependency.<\/p>\n\n\n\n

For example:<\/p>\n\n\n\n

X = {a,b,c,d,e,f}  and Y = {b,d,f} ;    And X -> Y <\/p>\n\n\n\n

In the above example, attribute Y (dependent) is a subset of attribute X (determinant), We can say that there is a trivial functional dependency between attribute X and Y.<\/p>\n\n\n\n

Another Example:<\/p>\n\n\n\n

{X,Y}  -> Y  <\/p>\n\n\n\n

In the above example, Y is dependent here and Y is a subset of {X, Y} which is determinant here, there will be a trivial functional dependency.<\/p>\n\n\n\n

Non-Trivial Functional Dependency:<\/h3>\n\n\n\n

Where an attribute or set of attributes that are dependent on a determinant is not a sub-set of determinants and their intersection of dependent and determinant is null there will be Non-trivial functional dependency.<\/p>\n\n\n\n

For example:<\/p>\n\n\n\n

X = {a,b,c,d,e,f}  and Y = {p, q, r};    And X -> Y <\/p>\n\n\n\n

In the above example attribute Y is dependent on attribute X (Determinant), but as stated in the definition Y dependent is not a subset of  X determinant; hence there will be Non Trivial Functional Dependency.<\/p>\n\n\n\n

Multivalued Functional Dependency: <\/h3>\n\n\n\n

Where determinant attribute X acts as an identifier for multiple values of dependent attribute Y, there is a multivalued dependency, this type of dependency is represented by two arrows (-> ->), where the pointed side of the arrow represents the dependent side whereas the other side of the arrow represents the determinant side.<\/p>\n\n\n\n

For example:<\/p>\n\n\n\n

Name of student<\/strong><\/td>Courses <\/strong><\/td>Department <\/strong><\/td><\/tr>
X<\/td>P, R<\/td>A<\/td><\/tr>
Y<\/td>R, Q<\/td>B<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

In the above example, the Attribute \u201cName of student\u201d  is Determinant for the attribute \u201cDepartment\u201d, hence we can say that \u201cName of student\u201d  ->  \u201cDepartment\u201d;<\/p>\n\n\n\n

But the determinant attribute \u201cName of student ” identifies multiple values for the dependent attribute \u201cCourse\u201d, hence there will be Multi-valued Functional Dependency and this can be described as \u201cName of student\u201d  -> ->  \u201dCourse\u201d.<\/p>\n\n\n\n

Transitive Function Dependency:<\/h3>\n\n\n\n

where there is an indirect relationship between dependent attribute Y and determinant attribute X, there is said to be Transitive function dependency.<\/p>\n\n\n\n

For example:<\/p>\n\n\n\n

Name of student<\/strong><\/td>Courses <\/strong><\/td>Department <\/strong><\/td>Building<\/strong><\/td><\/tr>
X<\/td>P, R<\/td>A<\/td>1<\/td><\/tr>
Y<\/td>R, Q<\/td>B<\/td>2<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

In the above example, attribute \u201c Name of student\u201d is the determinant and attribute \u201cDepartment\u201d is a dependent; hence \u201cName of the student\u201d  ->  \u201cDepartment\u201d ;<\/p>\n\n\n\n

But the attribute \u201cBuilding\u201d is dependent on the attribute \u201cdepartment\u201d which is also a dependent attribute to the determinant attribute \u201cName of student\u201d;<\/p>\n\n\n\n

hence there is an indirect relationship exists between the determinant attribute \u201cName of student\u201d and the dependent attribute \u201cBuilding\u201d which can also be represented as \u201cName of student\u201d  ->  \u201cBuilding\u201d.<\/p>\n\n\n\n

Fully Functional Dependency:<\/h3>\n\n\n\n

Where any subset of determinant attribute X can not determine the dependent attribute Y, there is a fully functional dependency.<\/p>\n\n\n\n

For example:<\/p>\n\n\n\n

If determinant attribute {A, B,} determined the value of dependent attribute {C} and the value of C can not be determined by {A} or {B}  there is a fully functional dependency.<\/p>\n\n\n\n

Partial Functional Dependency:<\/h3>\n\n\n\n

Where any subset of determinant attribute X can also determine the dependent attribute Y, there is a Partial functional dependency.<\/p>\n\n\n\n

For example: <\/p>\n\n\n\n

If determinant attribute {A, B} determines the value of dependent attribute {C} and the value of C can be determined by {A} or {B}, there is a Partial functional dependency.<\/p>\n\n\n\n

Advantages of DBMS<\/h2>\n\n\n\n

Now we have learned the types of Functional Dependency in DBMS, let\u2019s quickly have a look at the Advantages:<\/p>\n\n\n\n

Data Normalization: <\/h3>\n\n\n\n

with the help of functional dependency, we can identify the Primary keys and the Candidate keys in a table,  which helps to normalize the data, smalled normalized data needs less space to store and requires less effort to manage.<\/p>\n\n\n\n

Query Optimization: <\/h3>\n\n\n\n

Functional dependency helps to establish relationships between various data tables. It identifies important attributes that are necessary to retrieve data from the large data tables.<\/p>\n\n\n\n

Data Integrity: <\/h3>\n\n\n\n

By identifying the important attributes we can easily separate inconsistent and irrelevant data attributes. It prevents unnecessary data from entering our database and makes our data more and more relevant and accurate.<\/p>\n\n\n\n

Next step:<\/h2>\n\n\n\n

In case you are interested in learning more about the topic or want to make a bustling career in the field of Data Analytics. Then I recommend you, please check out the \u201cData Analytics course<\/a>\u201d facilitated by one of the leading e-learning platforms in India “Henry Harvin Education”. <\/p>\n\n\n\n

\"Henry<\/figure><\/div>\n\n\n\n

Henry Harvin Education<\/a> is one of the largest and most reliable Ed-tech companies globally with a vision to provide individuals and organizations with the latest and state-of-the-art technology. The Institute has more than 1200 courses under its portfolio operating across more than 37 categories.<\/p>\n\n\n\n

<\/p>\n\n\n\n

Conclusion:<\/h3>\n\n\n\n

Functional Dependency in DBMS acts as a blueprint for maintaining accurate and efficient organizational data in the database. Every organization should consider it as a key factor in determining the data storage and maintenance constraints in the organization. However, the topic is very vast and hard to learn with just an article. But I hope you may have got an overview of the Functional Dependency in DBMS.<\/p>\n\n\n\n

<\/p>\n\n\n\n

Recommended reads:<\/h2>\n\n\n\n