Copyright 2004 Pearson Education Inc Chapter 10 Functional Dependencies and Normalization for Relational Databases Copyright 2004 Pearson Education Inc FIGURE 10 1 A simplified COMPANY relational database schema Elmasri and Navathe Fundamentals of Database Systems Fourth Edition Copyright 2004 Pearson Education Inc Slide 10 3 FIGURE 10 2 Example database state for the relational database schema of Figure 10 1 Elmasri and Navathe Fundamentals of Database Systems Fourth Edition Copyright 2004 Pearson Education Inc Slide 10 4 FIGURE 10 3 Two relation schemas suffering from update anomalies Elmasri and Navathe Fundamentals of Database Systems Fourth Edition Copyright 2004 Pearson Education Inc Slide 10 5 FIGURE 10 4 Example states for EMP DEPT and EMP PROJ resulting from applying NATURAL JOIN to the relations in Figure 10 2 These may be stored a base relations for performance reasons Elmasri and Navathe Fundamentals of Database Systems Fourth Edition Copyright 2004 Pearson Education Inc Slide 10 6 FIGURE 10 4 continued Example states for EMP DEPT and EMP PROJ resulting from applying NATURAL JOIN to the relations in Figure 10 2 These may be stored a base relations for performance reasons Elmasri and Navathe Fundamentals of Database Systems Fourth Edition Copyright 2004 Pearson Education Inc Slide 10 7 FIGURE 10 5 Particularly poor design for the EMP PROJ relation of Figure 10 3b a The two relation schemas EMP LOCS and EMP PROJ1 b The result of projecting the extension of EMP PROJ from Figure 10 4 onto the relations EMP LOCS and EMP PROJ1 Elmasri and Navathe Fundamentals of Database Systems Fourth Edition Copyright 2004 Pearson Education Inc Slide 10 8 FIGURE 10 5 continued Particularly poor design for the EMP PROJ relation of Figure 10 3b a The two relation schemas EMP LOCS and EMP PROJ1 b The result of projecting the extension of EMP PROJ from Figure 10 4 onto the relations EMP LOCS and EMP PROJ1 Elmasri and Navathe Fundamentals of Database Systems Fourth Edition Copyright 2004 Pearson Education Inc Slide 10 9 FIGURE 10 6 Result of applying NATURAL JOIN to the tuples above the dotted lines in EMP PROJ1 and EMP LOCS of Figure 10 5 Generated spurious tuples are marked by asterisks Elmasri and Navathe Fundamentals of Database Systems Fourth Edition Copyright 2004 Pearson Education Inc Slide 10 10 FIGURE 10 7 A relation state of TEACH with a possible functional dependency TEXT COURSE However TEACHER COURSE is ruled out Elmasri and Navathe Fundamentals of Database Systems Fourth Edition Copyright 2004 Pearson Education Inc Slide 10 11 FIGURE 10 8 Normalization into 1NF a A relation schema that is not in 1NF b Example state of relation DEPARTMENT c 1NF version of same relation with redundancy Elmasri and Navathe Fundamentals of Database Systems Fourth Edition Copyright 2004 Pearson Education Inc Slide 10 12 FIGURE 10 9 Normalizing nested relations into 1NF a Schema of the EMP PROJ relation with a nested relation attribute PROJS b Example extension of the EMP PROJ relation showing nested relations within each tuple c Decomposition of EMP PROJ into relations EMP PROJ1 and EMP PROJ2 by propagating the primary key Elmasri and Navathe Fundamentals of Database Systems Fourth Edition Copyright 2004 Pearson Education Inc Slide 10 13 FIGURE 10 10 Normalizing into 2NF and 3NF a Normalizing EMP PROJ into 2NF relations b Normalizing EMP DEPT into 3NF relations Elmasri and Navathe Fundamentals of Database Systems Fourth Edition Copyright 2004 Pearson Education Inc Slide 10 14 FIGURE 10 11 Normalization into 2NF and 3NF a the LOTS relation with its functional dependencies FD1 though FD4 b Decomposing into the 2NF relations LOTS1 and LOTS2 c Decomposing LOTS1 into the 3NF relations LOTS1A and LOTS1B d Summary of the progressive normalization of LOTS Elmasri and Navathe Fundamentals of Database Systems Fourth Edition Copyright 2004 Pearson Education Inc Slide 10 15 FIGURE 10 12 Boyce Codd normal form a BCNF normalization of LOTS1A with the functional dependency FD2 being lost in the decomposition b A schematic relation with FDs it is in 3NF but not in BCNF Elmasri and Navathe Fundamentals of Database Systems Fourth Edition Copyright 2004 Pearson Education Inc Slide 10 16 FIGURE 10 13 A relation TEACH that is in 3NF but not BCNF Elmasri and Navathe Fundamentals of Database Systems Fourth Edition Copyright 2004 Pearson Education Inc Slide 10 17