Open In App

Functional Dependency and Attribute Closure

Last Updated : 07 Nov, 2023
Improve
Improve
Like Article
Like
Save
Share
Report

Fundamental ideas in database management and design are functional dependency and attribute closure. They are essential to maintaining data integrity and building effective, organized, and normalized databases.

Functional Dependency

A functional dependency A->B in a relation holds if two tuples having the same value of attribute A also have the same value for attribute B. For Example, in relation to STUDENT shown in Table 1, Functional Dependencies 

STUD_NO->STUD_NAME, STUD_NO->STUD_PHONE hold

but 

STUD_NAME->STUD_STATE do not hold

Student Table

Student Table

Advantages of Functional Dependencies

  • Through the identification and removal of redundant or unneeded data, they aid in the reduction of data redundancy in databases.
  • By guaranteeing that data is correct and consistent throughout the database, they enhance data integrity.
  • They make it simpler to add, edit, and remove data, which helps with database management.

Disadvantages of Functional Dependencies

  • The process of identifying functional dependencies can be time-consuming and complex, especially in large databases with many tables and relationships.
  • Overly restrictive functional dependencies can result in slow query performance or data inconsistencies, as data that should be related may not be properly linked.
  • Functional dependencies do not take into account the semantic meaning of data, and may not always reflect the true relationships between data elements.

How to find Functional Dependencies for a Relation?

Functional Dependencies in a relation are dependent on the domain of the relation. Consider the STUDENT relation given in Table 1. 

  • We know that STUD_NO is unique for each student. So STUD_NO->STUD_NAME, STUD_NO->STUD_PHONE, STUD_NO->STUD_STATE, STUD_NO->STUD_COUNTRY and STUD_NO -> STUD_AGE all will be true.
  • Similarly, STUD_STATE->STUD_COUNTRY will be true as if two records have same STUD_STATE, they will have same STUD_COUNTRY as well.
  • For relation STUDENT_COURSE, COURSE_NO->COURSE_NAME will be true as two records with same COURSE_NO will have same COURSE_NAME.

Functional Dependency Set

Functional Dependency set or FD set of a relation is the set of all FDs present in the relation. For Example, FD set for relation STUDENT shown in table 1 is: 
 

 { STUD_NO->STUD_NAME, STUD_NO->STUD_PHONE, STUD_NO->STUD_STATE, STUD_NO->STUD_COUNTRY, 
STUD_NO -> STUD_AGE, STUD_STATE->STUD_COUNTRY }

Attribute Closure

Attribute closure of an attribute set can be defined as set of attributes which can be functionally determined from it. 

How to find attribute closure of an attribute set?

To find attribute closure of an attribute set: 

  • Add elements of attribute set to the result set.
  • Recursively add elements to the result set which can be functionally determined from the elements of the result set.

Using FD set of table 1, attribute closure can be determined as: 

(STUD_NO)+ = {STUD_NO, STUD_NAME, STUD_PHONE, STUD_STATE, STUD_COUNTRY, STUD_AGE}
(STUD_STATE)+ = {STUD_STATE, STUD_COUNTRY}

Advantages of Attribute Closure

  • Attribute closures help to identify all possible attributes that can be derived from a set of given attributes.
  • They facilitate database design by identifying relationships between attributes and tables, which can help to optimize query performance.
  • They ensure data consistency by identifying all possible combinations of attributes that can exist in the database.

Disadvantages of Attribute Closure

  • The process of calculating attribute closures can be computationally expensive, especially for large datasets.
  • Attribute closures can become too complex to manage, especially as the number of attributes and tables in a database grows.
  • Attribute closures do not take into account the semantic meaning of data, and may not always accurately reflect the relationships between data elements.

How to Find Candidate Keys and Super Keys Using Attribute Closure?

  • If attribute closure of an attribute set contains all attributes of relation, the attribute set will be super key of the relation.
  • If no subset of this attribute set can functionally determine all attributes of the relation, the set will be candidate key as well. For Example, using FD set of table 1,

(STUD_NO, STUD_NAME)+ = {STUD_NO, STUD_NAME, STUD_PHONE, STUD_STATE, STUD_COUNTRY, STUD_AGE} 

(STUD_NO)+ = {STUD_NO, STUD_NAME, STUD_PHONE, STUD_STATE, STUD_COUNTRY, STUD_AGE} 

(STUD_NO, STUD_NAME) will be super key but not candidate key because its subset (STUD_NO)+ is equal to all attributes of the relation. So, STUD_NO will be a candidate key

Prime and Non-Prime Attributes

Attributes which are parts of any candidate key of relation are called as prime attribute, others are non-prime attributes. For Example, STUD_NO in STUDENT relation is prime attribute, others are non-prime attribute. 

Conclusion

Tools like functional dependency and attribute closure are helpful when designing and optimizing databases. They are useful for:

  • Determine the connections between the tables and the attributes.
  • Boost query efficiency
  • Ascertain data coherence.

GATE Questions

Q.1: Consider the relation scheme R = {E, F, G, H, I, J, K, L, M, N} and the set of functional dependencies {{E, F} -> {G}, {F} -> {I, J}, {E, H} -> {K, L}, K -> {M}, L -> {N} on R. What is the key for R? (GATE-CS-2014) 

A. {E, F} 
B. {E, F, H} 
C. {E, F, H, K, L} 
D. {E} 

Solution:

Finding attribute closure of all given options, we get: 
{E,F}+ = {EFGIJ} 
{E,F,H}+ = {EFHGIJKLMN} 
{E,F,H,K,L}+ = {{EFHGIJKLMN} 
{E}+ = {E} 
{EFH}+ and {EFHKL}+ results in set of all attributes, but EFH is minimal. So it will be candidate key. So correct option is (B). 

Q.2: How to check whether an FD can be derived from a given FD set?

Solution:

To check whether an FD A->B can be derived from an FD set F, 
 

  1. Find (A)+ using FD set F.
  2. If B is subset of (A)+, then A->B is true else not true.

Q.3: In a schema with attributes A, B, C, D and E following set of functional dependencies are given 
{A -> B, A -> C, CD -> E, B -> D, E -> A} 
Which of the following functional dependencies is NOT implied by the above set? (GATE IT 2005) 

A. CD -> AC 
B. BD -> CD 
C. BC -> CD 
D. AC -> BC 

Solution:

Using FD set given in question, 
(CD)+ = {CDEAB} which means CD -> AC also holds true. 
(BD)+ = {BD} which means BD -> CD can’t hold true. So this FD is no implied in FD set. So (B) is the required option. 
Others can be checked in the same way. 

Q.4: Consider a relation scheme R = (A, B, C, D, E, H) on which the following functional dependencies hold: {A–>B, BC–> D, E–>C, D–>A}. What are the candidate keys of R? [GATE 2005] 

(a) AE, BE 
(b) AE, BE, DE 
(c) AEH, BEH, BCH 
(d) AEH, BEH, DEH 

Solution:

(AE)+ = {ABECD} which is not set of all attributes. So AE is not a candidate key. Hence option A and B are wrong. 
(AEH)+ = {ABCDEH} 
(BEH)+ = {BEHCDA} 
(BCH)+ = {BCHDA} which is not set of all attributes. So BCH is not a candidate key. Hence option C is wrong. 
So correct answer is D. 



Previous Article
Next Article

Similar Reads

Differentiate between Partial Dependency and Fully Functional Dependency
Fully Functional Dependency :If X and Y are an attribute set of a relation, Y is fully functional dependent on X, if Y is functionally dependent on X but not on any proper subset of X.Example -In the relation ABC->D, attribute D is fully functionally dependent on ABC and not on any proper subset of ABC. That means that subsets of ABC like AB, BC
3 min read
Finding Attribute Closure and Candidate Keys using Functional Dependencies
In this article, we will find the attribute closure and also we will find the candidate keys using the functional dependency. We will look into this topic in detail. But before proceeding to this topic, we will first learn about what is functional dependency. A functional dependency X->Y in a relation holds if two tuples having the same value fo
6 min read
Armstrong's Axioms in Functional Dependency in DBMS
Prerequisite - Functional Dependencies This article contains Armstrong's Axioms and how Armstrong's Axioms are used to decide about the functional dependency on the database. We will be also learning about the Secondary Rules and Armstrong Relations. We will learn each thing in detail. Before moving ahead, you must have a knowledge of Functional De
4 min read
How do you Check Functional Dependency Validity?
Answer: To validate functional dependency ensure a clear relationship, check for redundancy, and analyze data consistency using closure and transitivity.Understanding FD ConceptFunctional Dependency (FD) denotes that the value of one attribute determines another in a database. Ensuring Data Redundancy-FreePrioritize a redundancy-free dataset before
1 min read
Fully Functional Dependency in DBMS
In the case of database management systems (DBMS), knowledge of dependencies is vital for the base built on this and it is a must for the development of the database that is most useful and practical. Special interdependency, which is expressed in the schema of the database, is based on the rule of changing attributes by changing the rest of the sc
4 min read
What is Functional Dependency in DBMS?
Functional dependency in DBMS is an important concept that describes the relationship between attributes (columns) in a table. It shows that the value of one attribute determines the other. In this article, we will learn about functional dependencies and their types. What is Functional Dependency in DBMS?A dependency function (FD) is a database con
6 min read
Attribute Closure Algorithm and its Utilization
Closure of a set F of FDs is the set F+ of all FDs that can be inferred from F. It is also known as complete set of Functional Dependency. It is denoted by F+. Algorithm : Attribute Closure set Algorithm to compute a+, the closure of a under F Result:= a; while (changes to Result) do for each B → Y in F do Begin if B ⊆ Result then Result := Result
4 min read
Easiest way to find the closure set of attribute
Set of all those attributes which can be functionally determined from an attribute set is called closure of the attribute set and the closure of the attribute set {X} is denoted as {X}+. We can only find candidate key and primary keys only with help of closure set of an attribute. So let see the easiest way to calculate the closure set of attribute
2 min read
Lossless Join and Dependency Preserving Decomposition
Decomposition of a relation is done when a relation in a relational model is not in appropriate normal form. Relation R is decomposed into two or more relations if decomposition is lossless join as well as dependency preserving. Lossless Join DecompositionIf we decompose a relation R into relations R1 and R2, Decomposition is lossy if R1 ⋈ R2 ⊃ RDe
4 min read
Multivalued Dependency and Fourth Normal Form
In database management systems, normalization is an essential process to ensure that data is organized efficiently and effectively. Multivalued dependency (MVD) is a concept that helps to identify and eliminate data redundancy and anomalies, and Fourth Normal Form (4NF) is a normalization form that addresses the challenges associated with multivalu
5 min read
Article Tags :