Functional Dependency in Database Management System / Role of Functional Dependency in Database design / Armstrong's Axioms / Functional Dependency Examples
Functional Dependency in Database Management System
Introduction
To
proceed further with 2NF, 3NF and so on, it is essential to know about
constraints, especially keys for a relation (table). Functional Dependency is
the one which ensures the strong relationship between the attributes of a
table. It is written as follows;
X → Y
It can
be read as, X uniquely determines Y. In other words; Y is functionally depend
on X. In the above expression,
I.
X is a set of one or more attributes and Y is
another set of one of more attributes.
II. For
one X value, there is only one possible Y value (Uniqueness).
III.
It ensures, set of attributes are depend on
other set of attributes.
Consider
the table 1 given below;
RegNo
|
SName
|
Gen
|
PR
|
Phone
|
Courses
|
R1
|
Sundar
|
M
|
BTech
|
9898786756
|
Database
|
R2
|
Ram
|
M
|
MS
|
9897786776
|
Database
|
R3
|
Karthik
|
M
|
MCA
|
8798987867
|
Data Structures
|
R4
|
John
|
M
|
BSc
|
7898886756
|
Multimedia
|
R1
|
Sundar
|
M
|
BTech
|
9898786756
|
Data Structures
|
R3
|
Karthik
|
M
|
MCA
|
8798987867
|
Multimedia
|
Table 1 - STUDENT
For
this table, we can write the Functional Dependencies as follows;
RegNo →
SName
RegNo →
Gen
RegNo →
PR
RegNo →
Phone
Or,
it can also be written as,
RegNo →
SName Gen PR Phone (1)
According
to I above, in equation 1, (RegNo) is X and (SName Gen PR Phone) is Y.
According
to II, if we consider the FD RegNo →
SName, it means, for any Register Number, there is only one Student Name. That
is, in table 1, for register number ‘R1’, the student name is ‘Sundar’. See
that the register number ‘R1’ comes at two places. In both place, ‘Sundar’ is
the name. It is applicable for all the RegNo values. That is, irrespective of
the number of occurrences of one register number, we have exactly one name
during all the occurrences.
According
to III, it is very clear that the set of attributes SName, Gen, PR, and Phone
are dependent on the RegNo attribute.
In
the process of Normalization, except 1NF, all the other normal forms should
satisfy the previous normal form properties as the first qualifier. That is, if
one would like to check a given table is in 2NF or not, first he has to check
whether it satisfies the condition of 1NF.
For
a table, it is good practice to create Primary Key. This key can be identified
using the set of functional dependencies we found holding on the given table. For
table 1, RegNo can identify all the other attributes uniquely except Courses. That
is, we cannot say RegNo → Courses. The reason is, for some
register numbers, we will get more than one value pointed. Hence, composite
primary key is the solution. That is, try to have more than one attribute in
the left hand side of the functional dependency to check the possibility of
identifying records uniquely. For table1, the following is the possible key.
RegNo Courses → SName Gen PR Phone
And,
this is correct. Means, the combination of attributes RegNo and Courses together
can identify all the other information uniquely. As the result, the combination
(RegNo, Courses) is the Primary key for this relation.
Armstrong’s Axioms
It is
not enough to consider the identified FDs for our further analysis for a key. It
is always required to identify the other set of hidden function dependencies. This
can be done using Armstrong’s axioms (inference rules).
Rule
1: Reflexivity rule – If X is a set of attributes, and Y is subset of X, then
we would say, X → Y.
Rule
2: Augmentation rule – If X →
Y, and Z is another set of attributes, then we write XZ → XY.
Rule
3: Transitivity rule – If X →
Y, and Y → Z,
then X → Z
is true.
From
these rules, we can infer the following additional set of rules;
Rule
4: Union rule – If X →
Y and X → Z,
then X → YZ
is true.
Rule
5: Decomposition rule – Its reverse of Rule 4. If X → YZ, then X → Y, and X → Z are true.
Rule
6: Pseudotransitivity rule – If X →
Y and ZY → A
are true, then XZ →
A is also true.