Question

Consider the relation R(A, B, C, D, E), and the decomposition of R into R1(ABC) and R2(ADE).

(a) Give a set of functional dependencies (FDs) such that the decomposition into R1 and R2 is lossless join and dependency preserving. Justify why your FDs satisfy the criteria.
(b) Give a set of functional dependencies such that the decomposition into R1 and R2 is lossless join, but not dependency preserving. Justify why your FDs satisfy the criteria.
(c) Give a set of functional dependencies such that the decomposition into R1 and R2 is not lossless join, but dependency preserving. Justify why your FDs satisfy the criteria.

Answer 1

Functional dependencies can be created to achieve different types of decomposition properties for a database relation. A lossless join and dependency preserving decomposition requires a common key across sub-relations, while a lossless join without dependency preservation can omit certain dependencies. Non-lossless join decompositions don't share keys between sub-relations but can preserve dependencies if split carefully.

Step-by-step explanation:

The decomposition of a relation R into R1 and R2 being lossless join and dependency preserving is a pivotal concept in database normalization. To create examples for functional dependencies (FDs) that satisfy each condition, we can proceed as follows:

• a) FDs like A → BC and A → DE ensure a lossless join and dependency preserving decomposition. Here, attribute A is a common attribute in both R1(ABC) and R2(ADE) and acts as a key for both, ensuring that joining R1 and R2 on A would result in the original relation R without losing information. Since all dependencies are represented in R1 and R2, it is dependency preserving.
• b) FDs such as A → BCD and A → E would provide a lossless join since A is still the key in both R1 and R2. However, because the dependency BCD is not fully represented in R1 or R2, this decomposition is not dependency preserving.
• c) FDs like AB → C and DE → A demonstrate a case where the decomposition is not a lossless join because there is no common attribute acting as a key across both R1 and R2. However, if split correctly, they can still preserve all the dependencies, making it a dependency preserving decomposition.