Functional dependency with examples

FUNCTIONAL DEPENDENCY

 

Functional dependency:

A Functional dependency is a relationship between attributes. Suppose that given the value of one attribute, we can obtain the value of another attribute.

For example,

If we know the value of customer account number, we can obtain customer address, balance etc. By this, we say that customer address and balance is functionally dependent on customer account number.

Types of Functional Dependency:

 

·         Single Valued Functional Dependency

·         Fully Functional Dependency

·         Partial Functional Dependency

·         Transitive Functional Dependency

·         Trivial Functional Dependency

 

Single Valued Functional Dependency –

 

A simple example of single value functional dependency is when A is the primary key of an entity (eg. SID) and B is some single valued attribute of the entity (eg. Sname). Then, A → B must always hold.

CID

SID

Sname

C1

S1

A

C1

S2

A

C2

S1

A

C3

S1

A

SID àSname

 

Fully Functional Dependency –

 

A functional dependency P → Q is full functional dependency if removal of any attribute A from P means that the dependency does not hold any more.

In a relation R, an attribute Q is said to be fully functional dependent on attribute P, if it is functionally dependent on P and not functionally dependent on any proper subset of P. The dependency P → Q is left reduced, their being no extraneous attributes in the left hand side of the dependency.

 

If AD → C, is fully functional dependency, then we cannot remove A or D. i.e. C is fully functional dependent on AD. If we are able to remove A or D, then it is not full functional dependency.

Another Example, Consider the following Company Relational Schema,

 

{SSN, PNUMBER} → HOURS is a full FD since neither SSN → HOURS

Nor PNUMBER → HOURS hold

 

{SSN, PNUMBER} → ENAME is not  a full FD (it is called a partial dependency )

since SSN → ENAME also holds.

 

 

Partial Functional Dependency –

 

A Functional Dependency in which one or more non key attributes are functionally depending on a part of the primary key is called partial functional dependency. or

where the determinant consists of key attributes, but not the entire primary key, and the determined consist of non-key attributes.

 

For example, Consider a Relation R(A,B,C,D,E) having

FD : AB → CDE where PK is AB.

 

Then, { A → C; A → D; A → E; B → C; B → D; B → E }

        all are Partial Dependencies.

 

 

Transitive Dependency –

 

Given a relation R(A,B,C)  then dependency like A–>B, B–>C   is a transitive dependency, since   A–>C is implied .

 

In the above Fig ,

SSN --> DMGRSSN is a transitive FD

                {since SSN --> DNUMBER and DNUMBER --> DMGRSSN hold}

 

SSN --> ENAME is non-transitive FD since there is no set of attributes X

                where SSN --> X and X --> ENAME.

 

Trivial Functional Dependency –

 

Some functional dependencies are said to be trivial because they are satisfied by all relations. Functional dependency of the form A–>B is trivial if B subset= A . or

A trivial Functional Dependency is the one where RHS is a subset of LHS.

 

Example, A-->A is satisfied  by all relations involving attribute A.

SSN-->SSN

PNUMBER-->PNUMBER

SSN PNUMBER -->PNUMBER

SSN PNUMBER --> SSN PNUMBER

Related topics

Professor Jayesh video tutorial

Please use contact page in this website if you find anything incorrect or you want to share more information about the topic discussed above.