Understanding Database Normalization and BCNF in Database Systems

Learn about the process of database development, including E-R diagrams, converting to relations, developing database operations, normalization, and BCNF. Explore algorithms for achieving BCNF and example scenarios to understand key concepts in database systems.

• Database Systems
• Normalization
• BCNF
• Database Development
• E-R Diagrams

boes_na Follow

Uploaded on Nov 27, 2024 | 0 Views

Download Presentation

Please find below an Image/Link to download the presentation.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.

Presentation Transcript

1. CSCE-608 Database Systems Spring 2024 Instructor: Jianer Chen Office: PETR 428 Phone: 845-4259 Email: chen@cse.tamu.edu Notes 7: The third Normal Form

2. Process of Database Development Description of the database application High-level representation of the database (E-R diagram) Chapter 4 Converting the E-R diagram into relations (tables) Developing database operations (using DML) Chapter 4 Chapters 6-8 Developing database application user interface Relation normalization Chapter 3 Chapter 9 Defining database schema (using DDL) Testing Chapter 2 2

3. Boyce-Codd Normal Form (BCNF) Definition. A relation R is in Boyce-Codd Normal Form (BCNF) if every nontrivial FD X A (i.e., A X) has its left side X a superkey. 3

4. Boyce-Codd Normal Form (BCNF) Definition. A relation R is in Boyce-Codd Normal Form (BCNF) if every nontrivial FD X A (i.e., A X) has its left side X a superkey. Algorithm RemoveBadFD(R, T; Y) Input: A relation R with FD set T, in which there is a bad FD Y A from Y. 1. Call Decomposition(Y) to decompose relation R into two smaller relations R1 and R2; 2. Call Decomposed-FDs(Rk) for k = 1, 2 to construct the FD sets T1 for R1 and T2 for R2; 3. Output (R1, T1) and (R2, T2). Algorithm Decomposition(X) 1. Compute S1 = X+ using the FDs in R; 2. Let W be the set of attributes that are not in S1; 3. Make a relation R1 of schema S1; 4. Make a relation R2 with schema S2 = X W; 5. Compute the FDs for R1 and R2. Algorithm Decomposed-FDs(R1) 1. T1 = ; \\ T1 is the FDs for R1 2. For each subset Y of S1 Do 2.1 compute Y+ using the FDs in R; 2.2 For each A in S1 (Y+\Y) add Y A to T1; 3. While changes Do 3.1 Drop from T1 those FDs that are derivable from the others; 3.2 For each XB A in T1, if X A is implied by T1, then replace XB A in T1 by X A. Algorithm BCNF(R, T) Input: A relation R and its FDs T Output: A collection C of relations in BCNF 1. C = ; C = {(R, T)}; 2. While C Do 2.1 Pick (R , T ) in C (and remove it from C ); 2.2 If (R , T ) has a BCNF violator X; 2.3 Then Call RemoveBadFD(R , T ; X) to construct the two relations (R1, T1) and (R2, T2); add (R1, T1) and (R2, T2) to C ; 2.4 Else add (R , T ) to C; 4

5. Example R = Drinkers(name, addr, beersLiked, manf, favBeer) T = {name addr, name favBeer, beersLiked manf} The only key is {name, beersLiked}, C = {(R, T)} 5

6. Example R = Drinkers(name, addr, beersLiked, manf, favBeer) T = {name addr, name favBeer, beersLiked manf} The only key is {name, beersLiked}, C = {(R, T)} Pick (R, T) from C , and pick a BCNF violation name addr in R 6

7. Example R = Drinkers(name, addr, beersLiked, manf, favBeer) T = {name addr, name favBeer, beersLiked manf} The only key is {name, beersLiked}, C = {(R, T)} Pick (R, T) from C , and pick a BCNF violation name addr in R Close the left side: {name}+ = {name, addr, favBeer}. 7

8. Example R = Drinkers(name, addr, beersLiked, manf, favBeer) T = {name addr, name favBeer, beersLiked manf} The only key is {name, beersLiked}, C = {(R, T)} Pick (R, T) from C , and pick a BCNF violation name addr in R Close the left side: {name}+ = {name, addr, favBeer}. Decomposed relations: R1 = Drinkers1(name, addr, favBeer) R2 = Drinkers2(name, beersLiked, manf) 8

9. Example R = Drinkers(name, addr, beersLiked, manf, favBeer) T = {name addr, name favBeer, beersLiked manf} The only key is {name, beersLiked}, C = {(R, T)} Pick (R, T) from C , and pick a BCNF violation name addr in R Close the left side: {name}+ = {name, addr, favBeer}. Decomposed relations: R1 = Drinkers1(name, addr, favBeer) R2 = Drinkers2(name, beersLiked, manf) Add R1 and R2 to C ; C = {(R1, T1), (R2, T2)} 9

10. Example R = Drinkers(name, addr, beersLiked, manf, favBeer) T = {name addr, name favBeer, beersLiked manf} The only key is {name, beersLiked}, C = {(R, T)} Pick (R, T) from C , and pick a BCNF violation name addr in R Close the left side: {name}+ = {name, addr, favBeer}. Decomposed relations: R1 = Drinkers1(name, addr, favBeer) R2 = Drinkers2(name, beersLiked, manf) Add R1 and R2 to C ; C = {(R1, T1), (R2, T2)} We are not done, yet since C ; 10

11. Example R = Drinkers(name, addr, beersLiked, manf, favBeer) T = {name addr, name favBeer, beersLiked manf} The only key is {name, beersLiked}, C = {(R, T)} Pick (R, T) from C , and pick a BCNF violation name addr in R Close the left side: {name}+ = {name, addr, favBeer}. Decomposed relations: R1 = Drinkers1(name, addr, favBeer) R2 = Drinkers2(name, beersLiked, manf) Add R1 and R2 to C ; C = {(R1, T1), (R2, T2)} We are not done, yet since C ; Pick R1 = Drinkers1(name, addr, favBeer), with the FD s T1= { name addr, name favBeer}. 11

12. Example R = Drinkers(name, addr, beersLiked, manf, favBeer) T = {name addr, name favBeer, beersLiked manf} The only key is {name, beersLiked}, C = {(R, T)} Pick (R, T) from C , and pick a BCNF violation name addr in R Close the left side: {name}+ = {name, addr, favBeer}. Decomposed relations: R1 = Drinkers1(name, addr, favBeer) R2 = Drinkers2(name, beersLiked, manf) Add R1 and R2 to C ; C = {(R1, T1), (R2, T2)} We are not done, yet since C ; Pick R1 = Drinkers1(name, addr, favBeer), with the FD s T1= { name addr, name favBeer}. {name} is the only key so R1 = Drinkers1 is in BCNF. 12

13. Example R = Drinkers(name, addr, beersLiked, manf, favBeer) T = {name addr, name favBeer, beersLiked manf} The only key is {name, beersLiked}, C = {(R, T)} Pick (R, T) from C , and pick a BCNF violation name addr in R Close the left side: {name}+ = {name, addr, favBeer}. Decomposed relations: R1 = Drinkers1(name, addr, favBeer) in BCNF , add to C R2 = Drinkers2(name, beersLiked, manf) Add R1 and R2 to C ; C = {(R1, T1), (R2, T2)} We are not done, yet since C ; Pick R1 = Drinkers1(name, addr, favBeer), with the FD s T1= { name addr, name favBeer}. {name} is the only key so R1 = Drinkers1 is in BCNF. 13

14. Example R = Drinkers(name, addr, beersLiked, manf, favBeer) T = {name addr, name favBeer, beersLiked manf} The only key is {name, beersLiked}, C = {(R, T)} Pick (R, T) from C , and pick a BCNF violation name addr in R Close the left side: {name}+ = {name, addr, favBeer}. Decomposed relations: R1 = Drinkers1(name, addr, favBeer) in BCNF , add to C R2 = Drinkers2(name, beersLiked, manf) C = {(R2, T2)} 14

15. Example R = Drinkers(name, addr, beersLiked, manf, favBeer) T = {name addr, name favBeer, beersLiked manf} The only key is {name, beersLiked}, C = {(R, T)} Pick (R, T) from C , and pick a BCNF violation name addr in R Close the left side: {name}+ = {name, addr, favBeer}. Decomposed relations: R1 = Drinkers1(name, addr, favBeer) in BCNF , add to C R2 = Drinkers2(name, beersLiked, manf) Pick R2= Drinkers2(name, beersLiked, manf) from C with FDs T2= {beersLiked manf}, the only key is {name, beersLiked} 15

16. Example R = Drinkers(name, addr, beersLiked, manf, favBeer) T = {name addr, name favBeer, beersLiked manf} The only key is {name, beersLiked}, C = {(R, T)} Pick (R, T) from C , and pick a BCNF violation name addr in R Close the left side: {name}+ = {name, addr, favBeer}. Decomposed relations: R1 = Drinkers1(name, addr, favBeer) in BCNF , add to C R2 = Drinkers2(name, beersLiked, manf) Pick R2= Drinkers2(name, beersLiked, manf) from C with FDs T2= {beersLiked manf}, the only key is {name, beersLiked} So beersLiked manf is a BCNF violator. 16

17. Example R = Drinkers(name, addr, beersLiked, manf, favBeer) T = {name addr, name favBeer, beersLiked manf} The only key is {name, beersLiked}, C = {(R, T)} Pick (R, T) from C , and pick a BCNF violation name addr in R Close the left side: {name}+ = {name, addr, favBeer}. Decomposed relations: R1 = Drinkers1(name, addr, favBeer) in BCNF , add to C R2 = Drinkers2(name, beersLiked, manf) Pick R2= Drinkers2(name, beersLiked, manf) from C with FDs T2= {beersLiked manf}, the only key is {name, beersLiked} So beersLiked manf is a BCNF violator. {beersLiked}+ = {beersLiked, manf}. 17

18. Example R = Drinkers(name, addr, beersLiked, manf, favBeer) T = {name addr, name favBeer, beersLiked manf} The only key is {name, beersLiked}, C = {(R, T)} Pick (R, T) from C , and pick a BCNF violation name addr in R Close the left side: {name}+ = {name, addr, favBeer}. Decomposed relations: R1 = Drinkers1(name, addr, favBeer) in BCNF , add to C R2 = Drinkers2(name, beersLiked, manf) Pick R2= Drinkers2(name, beersLiked, manf) from C with FDs T2= {beersLiked manf}, the only key is {name, beersLiked} So beersLiked manf is a BCNF violator. {beersLiked}+ = {beersLiked, manf}. Decompose Drinkers2: R3= Drinkers3(beersLiked, manf), T3 = {beersLiked manf} R4= Drinkers4(name, beersLiked), no nontrivial FD 18

19. Example R = Drinkers(name, addr, beersLiked, manf, favBeer) T = {name addr, name favBeer, beersLiked manf} The only key is {name, beersLiked}, C = {(R, T)} Pick (R, T) from C , and pick a BCNF violation name addr in R Close the left side: {name}+ = {name, addr, favBeer}. Decomposed relations: R1 = Drinkers1(name, addr, favBeer) in BCNF , add to C R2 = Drinkers2(name, beersLiked, manf) Pick R2= Drinkers2(name, beersLiked, manf) from C with FDs T2= {beersLiked manf}, the only key is {name, beersLiked} So beersLiked manf is a BCNF violator. {beersLiked}+ = {beersLiked, manf}. Decompose Drinkers2: R3= Drinkers3(beersLiked, manf), T3 = {beersLiked manf} R4= Drinkers4(name, beersLiked), no nontrivial FD Add R3, R4 to C . They are in BCNF, so will be moved to C. 19

20. Example R = Drinkers(name, addr, beersLiked, manf, favBeer) T = {name addr, name favBeer, beersLiked manf} The only key is {name, beersLiked}, C = {(R, T)} Pick (R, T) from C , and pick a BCNF violation name addr in R Close the left side: {name}+ = {name, addr, favBeer}. Decomposed relations: R1 = Drinkers1(name, addr, favBeer) in BCNF , add to C R2 = Drinkers2(name, beersLiked, manf) The resulting decomposition of Drinkers: R1 = Drinkers1(name, addr, favBeer) R3 = Drinkers3(beersLiked, manf) R4 = Drinkers4(name, beersLiked) 20

21. Example R = Drinkers(name, addr, beersLiked, manf, favBeer) T = {name addr, name favBeer, beersLiked manf} The only key is {name, beersLiked}, C = {(R, T)} Pick (R, T) from C , and pick a BCNF violation name addr in R Close the left side: {name}+ = {name, addr, favBeer}. Decomposed relations: R1 = Drinkers1(name, addr, favBeer) in BCNF , add to C R2 = Drinkers2(name, beersLiked, manf) The resulting decomposition of Drinkers: R1 = Drinkers1(name, addr, favBeer) R3 = Drinkers3(beersLiked, manf) R4 = Drinkers4(name, beersLiked) Notice: Drinkers1 tells about drinkers, Drinkers3 tells about beers, and Drinkers4 tells the relationship between drinkers and the beers they like. 21

22. Boyce-Codd Normal Form (BCNF) Definition. A relation R is in Boyce-Codd Normal Form (BCNF) if every nontrivial FD X A (i.e., A X) has its left side X a superkey. Algorithm BCNF(R, T) Input: A relation R and its FDs T Output: A collection C of relations in BCNF 1. C = ; C = {(R, T)}; 2. While C Do 2.1 Pick (R , T ) in C (and remove it from C ); 2.2 If (R , T ) has a BCNF violator X; 2.3 Then Call RemoveBadFD(R , T ; X) to construct the two relations (R1, T1) and (R2, T2); add (R1, T1) and (R2, T2) to C ; 2.4 Else add (R , T ) to C; Remarks. Algorithm constructs relations in BCNF. 22

23. Boyce-Codd Normal Form (BCNF) Definition. A relation R is in Boyce-Codd Normal Form (BCNF) if every nontrivial FD X A (i.e., A X) has its left side X a superkey. Algorithm BCNF(R, T) Input: A relation R and its FDs T Output: A collection C of relations in BCNF 1. C = ; C = {(R, T)}; 2. While C Do 2.1 Pick (R , T ) in C (and remove it from C ); 2.2 If (R , T ) has a BCNF violator X; 2.3 Then Call RemoveBadFD(R , T ; X) to construct the two relations (R1, T1) and (R2, T2); add (R1, T1) and (R2, T2) to C ; 2.4 Else add (R , T ) to C; Remarks. Algorithm constructs relations in BCNF. No information in the relation R is changed by the algorithm. This is because the algorithm Decomposition does not change information 23

24. Eliminating bad FD X A (for all A) Remarks. Algorithm Decomposition(X) eliminates the bad FD X A: * X A is still an FD in R1=(X+) but now X is a superkey for R1; * X A is not FD in R2=(X W) because A is not in X W. Does Decomposition(X) change (i.e., lose or add extra) information for R: No. Can R1 and R2 still have bad FDs: Yes. Algorithm Decomposition(X) 1. Compute S1 = X+ using the FDs in R; 2. Let W be the set of attributes that are not in S1; 3. Make a relation R1 of schema S1; 4. Make a relation R2 with schema S2 = X W; 5. Compute the FDs for R1 and R2. Algorithm Decomposed-FDs(R1) 1. T1 = ; \\ T1 is the FDs for R1 2. For each subset Y of S1 Do 2.1 compute Y+ using the FDs in R; 2.2 For each A in S1 (Y+\Y) add Y A to T1; 3. While changes Do 3.1 Drop from T1 those FDs that are derivable from the others; 3.2 For each XB A in T1, if X A is implied by T1, then replace XB A in T1 by X A. 24

25. Eliminating bad FD X A (for all A) Remarks. Algorithm Decomposition(X) eliminates the bad FD X A: * X A is still an FD in R1=(X+) but now X is a superkey for R1; * X A is not FD in R2=(X W) because A is not in X W. Does Decomposition(X) change (i.e., lose or add extra) information for R: No. Can R1 and R2 still have bad FDs: Yes. However, Decomposition(X) may not preserve FDs in R !! Algorithm Decomposition(X) 1. Compute S1 = X+ using the FDs in R; 2. Let W be the set of attributes that are not in S1; 3. Make a relation R1 of schema S1; 4. Make a relation R2 with schema S2 = X W; 5. Compute the FDs for R1 and R2. Algorithm Decomposed-FDs(R1) 1. T1 = ; \\ T1 is the FDs for R1 2. For each subset Y of S1 Do 2.1 compute Y+ using the FDs in R; 2.2 For each A in S1 (Y+\Y) add Y A to T1; 3. While changes Do 3.1 Drop from T1 those FDs that are derivable from the others; 3.2 For each XB A in T1, if X A is implied by T1, then replace XB A in T1 by X A. 25

26. Decomposition Does not Preserve FDs Example. Relation R(A,B,C) with FDs = {AB C, C B} e.g., A = street address, B = city, C = zip code. Street Addr City Zip Code 1 University Dr. Orange, CA 92866 1 University Dr. Pembroke, NC 28372 26

27. Decomposition Does not Preserve FDs Example. Relation R(A,B,C) with FDs = {AB C, C B} e.g., A = street address, B = city, C = zip code. Street Addr City Zip Code 1 University Dr. Orange, CA 92866 1 University Dr. Pembroke, NC 28372 There are two keys, {A,B} and {A,C} (C B is a BCNF violation)

28. Decomposition Does not Preserve FDs Example. Relation R(A,B,C) with FDs = {AB C, C B} e.g., A = street address, B = city, C = zip code. Street Addr City Zip Code 1 University Dr. Orange, CA 92866 1 University Dr. Pembroke, NC 28372 There are two keys, {A,B} and {A,C} (C B is a BCNF violation) The algorithm Decomposition(C) breaks R(A,B,C) into Valid R1(A,C) Valid R2(B,C) Street Addr Zip Code City Zip Code 1 University Dr. 1 University Dr. with FDs T1 = {} 92866 28372 Orange, CA 92866 Pembroke, NC with FDs T2 = {C B} 28372 28

29. Decomposition Does not Preserve FDs Example. Relation R(A,B,C) with FDs = {AB C, C B} e.g., A = street address, B = city, C = zip code. Street Addr City Zip Code 1 University Dr. Orange, CA 92866 1 University Dr. Pembroke, NC 28372 There are two keys, {A,B} and {A,C} (C B is a BCNF violation) The algorithm Decomposition(C) breaks R(A,B,C) into Valid R1(A,C) Valid R2(B,C) Street Addr Zip Code City Zip Code 1 University Dr. 1 University Dr. with FDs T1 = {} However, taking the natural join R1(A,C) R2(B,C) = R(A,B,C), we cannot derive {Street Addr, City} Zip (i.e., AB C) from the FDs T1 and T2 92866 28372 Orange, CA 92866 Pembroke, NC with FDs T2 = {C B} 28372 29

30. Eliminating bad FD X A (for all A) Remarks. Algorithm Decomposition(X) eliminates the bad FD X A: * X A is still an FD in R1=(X+) but now X is a superkey for R1; * X A is not FD in R2=(X W) because A is not in X W. Does Decomposition(X) change (i.e., lose or add extra) information for R: No. Can R1 and R2 still have bad FDs: Yes. However, Decomposition(X) may not preserve FDs in R !! Algorithm Decomposition(X) 1. Compute S1 = X+ using the FDs in R; 2. Let W be the set of attributes that are not in S1; 3. Make a relation R1 of schema S1; 4. Make a relation R2 with schema S2 = X W; 5. Compute the FDs for R1 and R2. Algorithm Decomposed-FDs(R1) 1. T1 = ; \\ T1 is the FDs for R1 2. For each subset Y of S1 Do 2.1 compute Y+ using the FDs in R; 2.2 For each A in S1 (Y+\Y) add Y A to T1; 3. While changes Do 3.1 Drop from T1 those FDs that are derivable from the others; 3.2 For each XB A in T1, if X A is implied by T1, then replace XB A in T1 by X A. 30

31. Eliminating bad FD X A (for all A) Remarks. Algorithm Decomposition(X) eliminates the bad FD X A: * X A is still an FD in R1=(X+) but now X is a superkey for R1; * X A is not FD in R2=(X W) because A is not in X W. Does Decomposition(X) change (i.e., lose or add extra) information for R: No. Can R1 and R2 still have bad FDs: Yes. However, Decomposition(X) may not preserve FDs in R !! Algorithm Decomposition(X) 1. Compute S1 = X+ using the FDs in R; 2. Let W be the set of attributes that are not in S1; 3. Make a relation R1 of schema S1; 4. Make a relation R2 with schema S2 = X W; 5. Compute the FDs for R1 and R2. Algorithm Decomposed-FDs(R1) 1. T1 = ; \\ T1 is the FDs for R1 2. For each subset Y of S1 Do 2.1 compute Y+ using the FDs in R; 2.2 For each A in S1 (Y+\Y) add Y A to T1; 3. While changes Do 3.1 Drop from T1 those FDs that are derivable from the others; 3.2 For each XB A in T1, if X A is implied by T1, then replace XB A in T1 by X A. This leads to 31

32. Boyce-Codd Normal Form (BCNF) Definition. A relation R is in Boyce-Codd Normal Form (BCNF) if every nontrivial FD X A (i.e., A X) has its left side X a superkey. Algorithm BCNF(R, T) Input: A relation R and its FDs T Output: A collection C of relations in BCNF 1. C = ; C = {(R, T)}; 2. While C Do 2.1 Pick (R , T ) in C (and remove it from C ); 2.2 If (R , T ) has a BCNF violator X; 2.3 Then Call RemoveBadFD(R , T ; X) to construct the two relations (R1, T1) and (R2, T2); add (R1, T1) and (R2, T2) to C ; 2.4 Else add (R , T ) to C; Remarks. Algorithm constructs relations in BCNF. No information in the relation R is changed by the algorithm. The algorithm BCNF may not preserve FDs !! 32

33. The 3rd Normal Form (3NF) Thus, BCNF may be too restricted 33

34. The 3rd Normal Form (3NF) Thus, BCNF may be too restricted We are looking for a constraint that may be less restricted but preserves FDs. 34

35. The 3rd Normal Form (3NF) Thus, BCNF may be too restricted We are looking for a constraint that may be less restricted but preserves FDs. An attribute A is a prime if A is in some key. 35

36. The 3rd Normal Form (3NF) Thus, BCNF may be too restricted We are looking for a constraint that may be less restricted but preserves FDs. An attribute A is a prime if A is in some key. The 3rd Normal Form (3NF) A relation R is in 3NF if for every nontrivial FD X A of R, either X is a superkey or A is a prime. 36

37. The 3rd Normal Form (3NF) Thus, BCNF may be too restricted We are looking for a constraint that may be less restricted but preserves FDs. An attribute A is a prime if A is in some key. The 3rd Normal Form (3NF) A relation R is in 3NF if for every nontrivial FD X A of R, either X is a superkey or A is a prime. * R is in BCNF R is in 3NF 37

38. The 3rd Normal Form (3NF) Thus, BCNF may be too restricted We are looking for a constraint that may be less restricted but preserves FDs. An attribute A is a prime if A is in some key. The 3rd Normal Form (3NF) A relation R is in 3NF if for every nontrivial FD X A of R, either X is a superkey or A is a prime. * R is in BCNF R is in 3NF * X A violates 3NF X is not a superkey & A is not a prime. 38

39. The 3rd Normal Form (3NF) Thus, BCNF may be too restricted We are looking for a constraint that may be less restricted but preserves FDs. An attribute A is a prime if A is in some key. The 3rd Normal Form (3NF) A relation R is in 3NF if for every nontrivial FD X A of R, either X is a superkey or A is a prime. Re-look at the example: R(A,B,C) with FDs {AB C, C B}. 39

40. The 3rd Normal Form (3NF) Thus, BCNF may be too restricted We are looking for a constraint that may be less restricted but preserves FDs. An attribute A is a prime if A is in some key. The 3rd Normal Form (3NF) A relation R is in 3NF if for every nontrivial FD X A of R, either X is a superkey or A is a prime. Re-look at the example: R(A,B,C) with FDs {AB C, C B}. R has keys AB and AC: 40

41. The 3rd Normal Form (3NF) Thus, BCNF may be too restricted We are looking for a constraint that may be less restricted but preserves FDs. An attribute A is a prime if A is in some key. The 3rd Normal Form (3NF) A relation R is in 3NF if for every nontrivial FD X A of R, either X is a superkey or A is a prime. Re-look at the example: R(A,B,C) with FDs {AB C, C B}. R has keys AB and AC: R is not in BCNF because of C B. 41

42. The 3rd Normal Form (3NF) Thus, BCNF may be too restricted We are looking for a constraint that may be less restricted but preserves FDs. An attribute A is a prime if A is in some key. The 3rd Normal Form (3NF) A relation R is in 3NF if for every nontrivial FD X A of R, either X is a superkey or A is a prime. Re-look at the example: R(A,B,C) with FDs {AB C, C B}. R has keys AB and AC: R is not in BCNF because of C B. However, R is in 3NF because in C B, B is a prime 42

43. Recoverability and Dependency Preserving Two important properties of a decomposition: Recoverability: The original relations can be reconstructed from the decomposed relations. Dependency Preservation: The FDs in the original relation can be derived based on the FDs in the decomposed relations. 43