Pages

Functional dependency with examples

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 nota 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–>Cis
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.