Page 117 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Database Management Systems/Managing Database

Notes The above relation includes two multivalued attributes of the entity programmer -projects and
languages. There are no functional dependencies.
The attributes projects and languages are assumed to be independent of each other. If we were to
consider projects and languages as separate entities, we would have two relationships (one
between employees and projects and the other between employees and programming languages).
Both the above relationships are many-to-many relation, in the following sense:
1. One programmer could have several projects.
2. May know several programming languages, also

3. One project may be obtained by several programmers, and
4. One programming language may be known to many programmers.
The above relation is in 3NF (even in BCNF) with some disadvantages. Suppose a programmer
has several projects (Proj_A, Proj_B, Proj_C, etc.) and is proficient in several programming
languages, how should this information be represented? There are several possibilities.

emp_name Projects languages
DEV Proj_A C
DEV Proj_A JAVA
DEV Proj_A C++
DEV Proj_B C
DEV Proj_B JAVA
DEV Proj_B C++

emp_name Projects languages
DEV Proj_A NULL
DEV Proj_B NULL
DEV NULL C
DEV NULL JAVA
DEV NULL C++

emp_name Projects languages
DEV Proj_A C
DEV Proj_B JAVA
DEV NULL C++
Other variations are possible. Please note this is so as there is no relationship between the
attributes ‘projects’ and programming ‘languages’. All the said variations have some
disadvantages. If the information is repeated, we face the problems of repeated information and
anomalies as we did when second or third normal form conditions were violated. Without
repetition, difficulties still exist with insertions, deletions and update operations. For example,
in the first table we want to insert a new person RAM who has just joined the organisation and
is proficient in C and JAVA. However, this information cannot be inserted in the first table as
RAM has not been allotted to work on any project. Thus, there is an insertion anomaly in the first
table. Similarly, if both Project A and Project B get completed on which DEV was working (so we
delete all the tuples in the first table) then the information that DEV is proficient in C, JAVA, and
C++ languages will also be lost. This is the deletion anomaly. Finally, please note that the
information that DEV is working on Project A is being repeated at least three times. Also the
information that DEV is proficient in JAVA is repeated. Thus, there is redundancy of information
in the first tables that may lead to inconsistency on updating (update anomaly).

110 LOVELY PROFESSIONAL UNIVERSITY