Wednesday, 5 February 2014

Functional Dependency in Database

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 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 table1, 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.
 

SQL exercises for beginners one

SQL Exercises for Beginners / Simple SQL Exercises with Answers / Solved SQL exercises / SQL solved exercises with simple conditions / So...