What is functional dependency in DBMS?

Functional dependencies are the constraints that are derived from the meaning and interrelationship of the data.

Definition − Let R is a relation schema with n attributes A1, A2,…..An. A functional dependency, denoted by X->Y, between two sets of attributes X and Y that are subsets of R species a constraint on the possible tuples that can form a relation state r of R.

The constraint is that, for any two tuples t1 and t2 in r that have t1[X]=t2[X], they must have t1[Y]=t2[Y].

  • Y depends on X or Y is determined by X or Y is functional dependent on X. Similarly X determines Y.

  • If X is the candidate key of R then X->Y for any subset of attributes Y of R.

  • If X->Y in R, it does not mean whether or not Y->X in R.

  • Relation extension r(R) that satisfy the FD constraints are called legal relation states (or legal extensions) of R.

Example 1

Given below is an example of functional dependency in database management system (DBMS) −

SSN->ENAME read as SSN functionally dependent on ENAME or SSN
determines ENAME
PNUMBER->{PNAME,PLOCATION} (PNUMBER determines PNAME and PLOCATION)
{SSN,PNUMBER}->HOURS (SSN and PNUMBER combinedly determines HOURS)

Output

You will get the following result −

Example 2

Consider another example: BOOK table.

BookidBnameAuthorPrice
B1DatabaseKorth400
B2DatabaseNavathe300
B3DatastructuresPatel200
B4OopsPatel200

Here,

  • Bookid->Bname {bookid determines Bname}

  • Bname ->author {bookname does not determine author, because same bname has different authors}

  • Author->price {Author determines price}

Note − There is no algorithm to identify functional dependency. We have to use our commonsense and judgment to identify functional dependency.

Type of dependencies

The different types of dependencies on the tables are as follows −

Multivalued Attributes

It is also called as repeating groups. A non-key attribute or groups of non-key attributes the values of which are not uniquely identified by the value of primary key or its part.

Partial dependency

An attribute that is partial dependency when an non-key attribute is determined by a part, but not on its whole of a composite primary key.

Transitive dependency

Transitive dependency means when a non-key attribute determines another non-key attribute.

raja
Bhanu Priya
Published on 03-Jul-2021 09:06:49
Previous Page Print Page
Next Page  
Advertisements