Page 118 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Unit 7: Relational Database Design

In the second and third tables above, the role of NULL values is confusing. Also the candidate Notes
key in the above relations is (emp name, projects, language) and existential integrity requires
that no NULLs be specified. These problems may be overcome by decomposing a relation as
follows:

emp_name Projects
DEV Proj_A
DEV Proj_B

emp_name languages
DEV C
DEV JAVA
DEV C++

This decomposition is the concept of 4NF. Functional dependency A B relates one value of A
to one value of B while multivalued dependency A B defines a relationship where a set of
values of attribute B are determined by a single value of A. Multivalued dependencies were
developed to provide a basis for decomposition of relations like the one above. Let us define the
multivalued dependency formally.

Task Normalization is a key concept of DBMS. Suggest.
Multivalued dependency: The multivalued dependency X Y is said to hold for a relation
R(X, Y, Z) if, for a given set of value (set of values if X is more than one attribute) for attribute X,
there is a set of (zero or more) associated values for the set of attributes Y and the Y values
depend only on X values and have no dependence on the set of attributes Z.

Notes Whenever X Y holds, so does X Z since the role of the attributes Y and Z
is symmetrical.
In the example given above, if there was some dependence between the attributes projects and
language, for example, the language was related to the projects (perhaps the projects are prepared
in a particular language), then the relation would not have MVD and could not be decomposed
into two relations as above. However, assuming there is no dependence, emp_name
projects and emp_name languages holds.

Trival MVD: A MVC X Y is called trivial MVD if either Y is a subset of X or X and Y together
form the relation R.
The MVD is trivial since it results in no constraints being placed on the relation. If a relation like
emp(eno, edpendent#) has a relationship between eno and edependent# in which eno uniquely
determines the values of edependent#, the dependence of edependent# on eno is called a trivial
MVD since the relation emp cannot be decomposed any further.
Therefore, a relation having non-trivial MVDs must have at least three attributes; two of them
multivalued and not dependent on each other. Non-trivial MVDs result in the relation having
some constraints on it since all possible combinations of the multivalued attributes are then
required to be in the relation.
Let us now define the concept of MVD in a different way. Consider the relation R(X, Y, Z) having
a multi-valued set of attributes Y associated with a value of X. Assume that the attributes Y and

LOVELY PROFESSIONAL UNIVERSITY 111