PowerPoint PresentationChapter 10FIGURE 10.1 A simplified COMPANY relational database schema.FIGURE 10.2 Example database state for the relational database schema of Figure 10.1.FIGURE 10.3 Two relation schemas suffering from update anomalies.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.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.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.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.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.FIGURE 10.7 A relation state of TEACH with a possible functional dependency TEXT  COURSE. However, TEACHER  COURSE is ruled out.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.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.FIGURE 10.10 Normalizing into 2NF and 3NF. (a) Normalizing EMP_PROJ into 2NF relations (b) Normalizing EMP_DEPT into 3NF relations.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.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.FIGURE 10.13 A relation TEACH that is in 3NF but not BCNF.Copyright © 2004 Pearson Education, Inc.Copyright © 2004 Pearson Education, Inc.Chapter 10Functional Dependencies and Normalization for Relational DatabasesSlide 10-3Elmasri and Navathe, Fundamentals of Database Systems, Fourth EditionCopyright © 2004 Pearson Education, Inc. FIGURE 10.1A simplified COMPANY relational database schema.Slide 10-4Elmasri and Navathe, Fundamentals of Database Systems, Fourth EditionCopyright © 2004 Pearson Education, Inc. FIGURE 10.2Example database state for the relational database schema of Figure 10.1.Slide 10-5Elmasri and Navathe, Fundamentals of Database Systems, Fourth EditionCopyright © 2004 Pearson Education, Inc. FIGURE 10.3Two relation schemas suffering from update anomalies.Slide 10-6Elmasri and Navathe, Fundamentals of Database Systems, Fourth EditionCopyright © 2004 Pearson Education, Inc. FIGURE 10.4Example 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.Slide 10-7Elmasri and Navathe, Fundamentals of Database Systems, Fourth EditionCopyright © 2004 Pearson Education, Inc. 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.Slide 10-8Elmasri and Navathe, Fundamentals of Database Systems, Fourth EditionCopyright © 2004 Pearson Education, Inc. FIGURE 10.5Particularly 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.Slide 10-9Elmasri and Navathe, Fundamentals of Database Systems, Fourth EditionCopyright © 2004 Pearson Education, Inc. 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.Slide 10-10Elmasri and Navathe, Fundamentals of Database Systems, Fourth EditionCopyright © 2004 Pearson Education, Inc. FIGURE 10.6Result 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.Slide 10-11Elmasri and Navathe, Fundamentals of Database Systems, Fourth EditionCopyright © 2004 Pearson Education, Inc. FIGURE 10.7A relation state of TEACH with a possible functional dependency TEXT  COURSE. However, TEACHER  COURSE is ruled out.Slide 10-12Elmasri and Navathe, Fundamentals of Database Systems, Fourth EditionCopyright © 2004 Pearson Education, Inc. FIGURE 10.8Normalization 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.Slide 10-13Elmasri and Navathe, Fundamentals of Database Systems, Fourth EditionCopyright © 2004 Pearson Education, Inc. FIGURE 10.9Normalizing 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.Slide 10-14Elmasri and Navathe, Fundamentals of Database Systems, Fourth EditionCopyright © 2004 Pearson Education, Inc. FIGURE 10.10Normalizing into 2NF and 3NF. (a) Normalizing EMP_PROJ into 2NF relations (b) Normalizing EMP_DEPT into 3NF relations.Slide 10-15Elmasri and Navathe, Fundamentals of Database Systems, Fourth EditionCopyright © 2004 Pearson Education, Inc. FIGURE 10.11Normalization into 2NF and 3NF. (a) the LOTS relation with its functional dependencies FD1 though FD4. (b) Decomposing into