Designing Relational Database Systems (Dv-Mps Designing)

The most common type of relationship between entities is one-to-many, wherein a single instance of one entity can be associated with zero, one, or many instances of another entity. The majority of the normalization techniques discussed in Chapter 2 result in relations with one-to-many relationships between them.

One-to-many relationships present few problems once they've been identified. However, it's important to be careful in specifying the optionality on each side of the relationship. It's commonly thought that only the many side of the relationship can be optional, but this isn't the case. Take, for example, the relationship shown in Figure 3-12.

Figure 3-12. This relationship is optional in both directions.

The relationship between Client and CustomerServiceRep is optional in both directions. In English, this would be expressed as a "CustomerServiceRep can have zero or more clients. A client's CustomerServiceRep, if one has been assigned, must be present in the CustomerServiceRep relation." Specifying optionality on the one side of a one-to-many relationship has important implications for both the implementation and usability of the system. We'll discuss these issues in detail in Chapters 4 and 14, but understand here that relational theory does not require that the one side of a one-to-many relationship be mandatory.

Identifying the primary and foreign relations in a one-to-many relationship is easy. The entity on the one side of the relationship is always the primary relation; its candidate key is copied to the relation on the many side, which becomes the foreign relation. The key candidate of the primary relation often forms part of the candidate key for the relation on the many side, but it can never uniquely identify the tuples of foreign relation by itself. It must be combined with one or more other attributes to form a candidate key.