PRO_INFO suppler_name, product_name, Evans 60099 UP-_ LOC ( suppller_name, location Marti Evans FIGURE 94.4 Decomposition of PRODUCT into PRO_INFO and SUP_LOC. SUPPLIES(clientname, supplier_name, location Krad Martin hengru Greene Evans FIGURE 94.5 Instance of suPPlIeS UP-_CLI( client_name, supplier_name SUP- LOC (supplier_name, location Shangyu Martin Metairie Rudd FIGURE 94.6 mposition of SUPPLIES into SUP_CLI and SUP_LOC. The relation of Fig. 94.5, which is in 2NF, highlights update anomalies and data redundancy due to the transitive dependency of a nonprime attribute on a key. The relation gives the name of a client(client_name) the corresponding supplier(supplier_name), and the supplier's location. Each client is assumed to have one supplier. The relations key is client_name, and each supplier has only one location. A supplier and his location cannot be inserted in SUPPLIES unless the supplier has at least one client. In addition, the relation ha deletion anomaly since if Tillis is no longer a client of Rudd, the information about Rudd as a supplier and his location is lost. A change to a supplier's location may updating the location attribute name of several tuples in the relation. Also, although each supplier has only one location, such a location is sometimes repeated several time unnecessarily, leading to data redundancy. The relation exhibits the following transitive dependency: client_name-supplier_name, supplier_name >location(but not the inverse). The relation CLIENT is clearly in 2NF, but because of the transitive dependency of the nonprime attribute location on the key, it is not in 3NE. This is the cause of the anomalies mentioned above. Eliminating this transitive dependency by splitting the schema into two components will remove these anomalies. Clearly, the resulting two relations SUP_CLI and SUP_LOC are in 3NF(see Fig 94.6 Each partial dependency of a nonprime attribute on a key can be expressed as a transitive dependency of a onprime attribute on a key. Therefore, a scheme in NF is also in 2NF BCNF is a stricter form of 3Nf where a relation r on a schema r is in bcnf if whenever a functional dependency X-Yexists in r(R), then X is a superkey of R. The condition of 3NE, which allows Y to be prime if X is not a superkey, does not exist in BCNE. Thus, every scheme in BCNF is also in 3NF, but the opposite is not always true c2000 by CRC Press LLC© 2000 by CRC Press LLC The relation of Fig. 94.5, which is in 2NF, highlights update anomalies and data redundancy due to the transitive dependency of a nonprime attribute on a key. The relation gives the name of a client (client_name), the corresponding supplier (supplier_name), and the supplier’s location. Each client is assumed to have one supplier. The relation’s key is client_name, and each supplier has only one location. A supplier and his location cannot be inserted in SUPPLIES unless the supplier has at least one client. In addition, the relation has a deletion anomaly since if Tillis is no longer a client of Rudd, the information about Rudd as a supplier and his location is lost. A change to a supplier’s location may require updating the location attribute name of several tuples in the relation. Also, although each supplier has only one location, such a location is sometimes repeated several time unnecessarily, leading to data redundancy. The relation exhibits the following transitive dependency: client_name Æ supplier_name, supplier_name Æ location (but not the inverse). The relation CLIENT is clearly in 2NF, but because of the transitive dependency of the nonprime attribute location on the key, it is not in 3NF. This is the cause of the anomalies mentioned above. Eliminating this transitive dependency by splitting the schema into two components will remove these anomalies. Clearly, the resulting two relations SUP_CLI and SUP_LOC are in 3NF (see Fig. 94.6). Each partial dependency of a nonprime attribute on a key can be expressed as a transitive dependency of a nonprime attribute on a key. Therefore, a scheme in 3NF is also in 2NF. BCNF is a stricter form of 3NF, where a relation r on a schema R is in BCNF if whenever a functional dependency X Æ Y exists in r(R), then X is a superkey of R. The condition of 3NF, which allows Y to be prime if X is not a superkey, does not exist in BCNF. Thus, every scheme in BCNF is also in 3NF, but the opposite is not always true. FIGURE 94.4 Decomposition of PRODUCT into PRO_INFO and SUP_LOC. FIGURE 94.5 Instance of SUPPLIES. FIGURE 94.6 Decomposition of SUPPLIES into SUP_CLI and SUP_LOC