Functional dependency and Attribute Closure in DBMS

Finding functional dependencies in a relation (table)

Functional dependencies in a relation are dependent on the domain (set of possible values that a column can accept) in that table.

 Consider the table student

• As we know, ID is capable of defining uniquely each record in the table hence ID-> Name, ID-> Address, ID->Age hold true
• In the same way State ->Country will also be true because there are chances for two rows to have the same state then they will have the same country as well.

Functional dependency set

• Functional dependency set is nothing but the set of all functional dependencies that are existed in a table or relation
• For example, in the table student, we can observe the functional dependencies as
  • {ID ->Name, ID ->State, ID-> Country, ID-> age, State-> Country}

Attribute closure

The set “A*” is said to be the closure set of “A” if the set of attributes are functionally dependent on the attributes of “A”

Finding attribute closure of an attribute set

• First, add elements of the attribute set to the result set
• Add elements to the result set which are functionally determined from the result set recursively

In the relation student an attribute closure is determined as

• (ID)+ = {ID, Name, State,Country,Age}
• (State)+ = {State, Country}

Finding candidate keys and super keys through attribute closure

• If all attributes of the relation are present in attribute closure of an attribute set then such an attribute set will have a super key for that relation
• If all attributes of the relation or determined by none of the attribute set then such a set will be a candidate key as well
  • (ID, Name)+ = {ID, Name, State, Country, Age}
  • (ID)+ = {ID, Name,State,Country, Age}
• (ID, Name) will be super key but not as a candidate key because its subset (ID) + is equal to all attributes present in the relation. Hence, ID will be a candidate key.