7.22 Give a lossless, dependency-preserving decomposition into 3NF of schema R of Exercise 7.1.
Recall that the schema R of Exercise 7.1 is:
R(A,B,C,D,E)
with the functional dependencies:
A → BC
CD → E
E → A
To decompose R into 3NF, we first check if it is already in 3NF. Since there is only one candidate key (A), and all non-prime attributes (B, C, D, E) depend only on A, R is already in 3NF.
Therefore, the lossless, dependency-preserving decomposition into 3NF of R is simply:
R1(A, B, C)
R2(C, D, E)
where R1 includes the first functional dependency and R2 includes the second and third functional dependencies.
To give a lossless, dependency-preserving decomposition of schema R into 3NF, we need to follow the following steps:
Step 1: Identify the functional dependencies in the schema R. In Exercise 7.1, the functional dependencies are given as follows:
AB -> CDE
CD -> A
E -> F
Step 2: Check if the given functional dependencies violate the 3NF conditions. The conditions for 3NF are as follows:
- The relation must be in 2NF.
- All non-prime attributes must be fully functionally dependent on every candidate key.
Step 3: Find the closure of the functional dependencies to determine the candidate keys. The closure of AB -> CDE is AB -> ABCDEF, which means AB is a candidate key. The closure of CD -> A is CD -> ACDEF, which again suggests CD as a candidate key. The closure of E -> F is E -> EF, which indicates that E is a candidate key.
Step 4: Create separate relations for each candidate key. In this case, we will have three relations: AB, CD, and E.
Step 5: Determine the functional dependencies for each relation based on the original functional dependencies.
For AB:
- AB -> CDE (original dependency remains)
- AB -> F (derived from E -> F)
For CD:
- CD -> A (original dependency remains)
- CD -> B (derived from AB -> CDE)
- CD -> E (derived from E -> F)
For E:
- E -> F (original dependency remains)
Step 6: Check for any remaining functional dependencies that violate the 3NF conditions. In this case, there are no more dependencies violating the conditions.
Step 7: Combine the relations to form the final decomposition. As all the relations are in 3NF and there are no remaining functional dependencies violating the conditions, the final decomposition into 3NF of schema R is as follows:
AB (AB, C, D, E, F)
CD (CD, A, B, E)
E (E, F)