In fact surrogate keys mean nothing to users so they

This preview shows page 15 - 17 out of 22 pages.

captured in the database. In fact, surrogate keys mean nothing to users, so they are usu-ally never shown to the user. Instead, the natural keys are used as identifiers in searches.Step 3: Map Binary RelationshipsThe procedure for representing relationships depends on both the degree of the relation-ships (unary, binary, or ternary) and the cardinalities of the relationships. We describe and illustrate the important cases in the following discussion.MAP BINARY ONE-TO-MANY RELATIONSHIPSFor each binary 1:Mrelationship, first cre-ate a relation for each of the two entity types participating in the relationship, using the procedure described in Step 1. Next, include the primary key attribute (or attributes) of the entity on the one-side of the relationship as a foreign key in the relation that is on the many-side of the relationship. (A mnemonic you can use to remember this rule is this: The primary key migrates to the many side.) To illustrate this simple process, we use the Submits relationship between cus-tomers and orders for Pine Valley Furniture Company (see Figure 2-22). This 1:Mrelationship is illustrated in Figure 4-12a. (Again, we show only a few attributes for simplicity.) Figure 4-12b shows the result of applying this rule to map the entity types with the 1:Mrelationship. The primary key CustomerID of CUSTOMER (the one side) is included as a foreign key in ORDER (the many side). The foreign key relationship SubmitsCUSTOMERCustomer IDCustomer NameCustomer AddressCustomer Postal CodeORDERORDER IDOrder DateFIGURE 4-12Example of mapping a 1:MrelationshipOrderDateOrderIDORDERCustomerIDCustomerIDCUSTOMERCustomerAddressCustomerPostalCodeCustomerName(a) Relationship between CUSTOMER and ORDER entities(b) CUSTOMER and ORDER relations with a foreign key in ORDERWOW! eBook
206Part III • Database Designis indicated with an arrow. Please note that it is not necessary to name the foreign key attribute CustomerID. It is, however, essential that it has the same domain as the primary key it references.MAP BINARY MANY-TO-MANY RELATIONSHIPSSuppose that there is a binary many-to-many (M:N) relationship between two entity types, A and B. For such a relationship, create a new relation, C. Include as foreign key attributes in C the primary key for each of the two participating entity types. These attributes together become the primary key of C. Any nonkey attributes that are associated with the M:Nrelationship are included with the relation C.Figure 4-13 shows an example of applying this rule. Figure 4-13a shows the Completes relationship between the entity types EMPLOYEE and COURSE from Figure  2-11a. Figure 4-13b shows the three relations (EMPLOYEE, COURSE, and CERTIFICATE) that are formed from the entity types and the Completes relationship. If Completes had been represented as an associative entity, as is done in Figure 2-11b, a similar result would occur, but we will deal with associative entities in a subsequent section. In the case of an M:Nrelationship, a relation is first created for each of the

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture