The company may assign any given truck to any given

This preview shows page 21 - 23 out of 36 pages.

company keeps a log of its trucking operations to keep track of its driver/truck assignments. The company may assign any given truck to any given driver many times and, as time passes, each driver may be assigned to drive many of the company's trucks. Since this M:N relationship should not be implemented, we create the composite entity named LOG whose attributes are defined by the end-user information requirements. In this case, it may be useful to include LOG_DATE, TRUCK_NUM, DRIVER_NUM, LOG_TIME_OUT, and LOG_TIME_IN.Note that the LOG's TRUCK_NUM and DRIVER_NUM attributes are the driver LOG's foreign keys. The TRUCK_NUM and DRIVER_NUM attribute values provide the bridge between the TRUCK and DRIVER, respectively. In other words, to form a proper bridge between TRUCK and DRIVER, the composite LOG entity must contain at least the primary keys of the entities connected by it.You might think that the combination of the composite entity's foreign keys may be designated to be the composite entity's primary key. However, this combination will not produce unique values over time. For example, the same driver may drive a given truck on different dates. Adding the date to the PK attributes will solve that problem. But we still have a non-unique outcome when the same driver drives a given truck twice on the same date. Adding a time
attribute will finally create a unique set of PK attribute values - but the PK is now composed of four attributes: TRUCK_NUM, DRIVER_NUM, LOG_DATE, and LOG_TIME_OUT. (The combination of these attributes yields a unique outcome, because the same driver cannot check out two trucks at the same time on a given date.)Because multi-attribute PKs may be difficult to manage, it is often advisable to create an"artificial" single-attribute PK, such as LOG_NUM, to uniquely identify each record in the LOG table.(Access users can define such an attribute to be an "autonumber" to ensure that the system will generate unique LOG_NUM values for each record.) Note that this solution produces a LOG table that contains two candidate keys: the designated primary key and the combination of foreign keys that couldhave served as the primary key.While the preceding solution simplifies the PK definition, it does not prevent the creation of duplicate records that merely have a different LOG_NUM value. Note, for example, the first tworecords in the following table:To avoid such duplicate records, you can create a unique indexon TRUCK_NUM + DRIVER_NUM + LOG_DATE + LOG_TIME_OUT.Composite entities may be named to reflect their component entities. For example, an employee may have several insurance policies (life, dental, accident, health, etc.) and each insurance policy may be held by many employees. This M:N relationship is converted to a set of two 1:M relationships, by creating a composite entity named EMP_INS. The EMP_INS entitymust contain at leastthe primary key components of each of the two entities connected by it. How many additional attributes are kept in the composite entity depends on the end-user information requirements.

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture