Page 115 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Database Management Systems/Managing Database

Notes Thus, FD X Y means that the values of the Y component of a tuple in “A” depend on or is
determined by the values of X component. In other words, the value of Y component is uniquely
determined by the value of X component. This is functional dependency from X to Y (but not Y
to X) that is, Y is functionally dependent on X.
The relation schema “A” determines the function dependency of Y on X (X Y) when and only
when:
1. If two tuples in “A”, agree on their X value then
2. They must agree on their Y value.

Please note that if X Y in “A”, does not mean Y X in “A”.
This semantic property of functional dependency explains how the attributes in “A” are related
to one another. A FD in “A” must be used to specify constraints on its attributes that must hold
at all times.

Example: A FD state, city, place pin-code should hold for any address in India. It is
also possible that certain functional dependencies may cease to exist in the real world if the
relationship changes, for example, the FD pin-code area-code used to exist as a relationship
between postal codes and telephone number codes in India, with the proliferation of mobile
telephone, the FD is no longer true.
Consider a relation
STUDENT-COURSE (enrolno, sname, cname, classlocation, hours)
We know that the following functional dependencies (we identify these primarily from
constraints, there is no thumb rule to do so otherwise) should hold:
1. enrolno sname (the enrolment number of a student uniquely determines the student
names alternatively, we can say that sname is functionally determined/dependent on
enrolment number).
2. classcode cname, classlocation (the value of a class code uniquely determines the class
name and class location.
3. enrolno, classcode Hours (a combination of enrolment number and class code values
uniquely determines the number of hours and students study in the class per week (Hours).
These FDs can be optimised to obtain a minimal set of FDs called the canonical cover. However,
these topics are beyond the scope of this course and can be studied by consulting further reading
list.

Normalisation

The first concept of normalisation was proposed by Mr. Codd in 1972. Initially, he alone proposed
three normal forms named first, second and third normal form. Later on, with the joint efforts of
Boyce and Codd, a stronger definition of 3NF called Boyce-Codd Normal Form (BCNF) was
proposed. All the said normal forms are based on the functional dependencies among the
attributes of a relation. The normalisation process depends on the assumptions that:
1. A set of functional dependencies is given for each relation, and
2. Each relation has a designated primary key.

The normalisation process is based on the two assumptions /information above. Codd takes a
relation schema through a series of tests to ascertain whether it satisfies a certain normal form.

108 LOVELY PROFESSIONAL UNIVERSITY