Page 121 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Database Management Systems/Managing Database

Notes Why?

Let us explain this with the help of a definition of join dependency. The decomposition mentioned
above will create tables as given below:
Emp_project

emp_name Projects
DEV Proj_A
RAM Proj_A
DEV Proj_B
RAM Proj_B

Emp_language
Emp_name languages
DEV C
RAM JAVA
RAM C++

On taking join of these relations on emp_name it will produce the following result:
emp_name Projects Languages
DEV Proj_A C
RAM Proj_A JAVA
RAM Proj_A C++
DEV Proj_B C
RAM Proj_B JAVA
RAM Proj_B C++

Since the joined table does not match the actual table, we can say that it is a lossy decomposition.
Thus, the expected join dependency expression:
*((emp_name, project), (emp_name, language)) does not satisfy the conditions of lossless
decomposition. Hence, the decomposed tables are losing some important information.
Can the relation ‘Programmer’ be decomposed in the following three relations?
(emp_name, project),
(emp_name, language) and
(Projects, language)

Please verify whether this decomposition in lossless or not. The join dependency in this case
would be:
*((emp_name, project), (emp_name, language), (project, language))

and it can be shown that this decomposition is lossless.

Project-Join Normal Form

PJNF is defined using the concept of the join dependencies. A relation schema R having a set F of
functional, multivalued, and join dependencies, is in PJNF (5 NF), if for all the join dependencies
in the closure of F (referred to as F+) that are of the form
*(R , R , . . .,R ),
1 2 n

114 LOVELY PROFESSIONAL UNIVERSITY