Designing Relational Database Systems (Dv-Mps Designing)

Some basic terminology applies to relationships. The entities that are related are called participants, and the number of participants in a relationship is its degree. The vast majority of relationships are binary, having two participants, but unary relationships (a relation that is related to itself) are also common, while ternary relationships (with three participants) are not unknown. The examples throughout most of this chapter are binary. We'll examine unary and ternary relationships as special cases later in the chapter.

The participation of an entity in a relationship can be classified as total participation or partial participation, depending on whether the entity can exist without participating in the relationship. For example, given the two entities Customer and Order, the participation of Customer in the relationship is partial since Customer details might be entered before the customer has placed any orders. Order, on the other hand, has total participation, since an order can't be placed except by a Customer.

The same principle is sometimes used to classify the entities themselves as either weak (having total participation) or regular (having partial participation). Weak entities can exist only in relationships with other entities, while regular entities can exist in isolation. This classification is part of the Entity Relationship (E/R) diagramming method as originally described by Chen.

Of the three ways to classify a relationship—total or partial, optional or mandatory, and in terms of weak or regular entities—I have always found optionality to be the most useful. However, explicitly expressing the effect of the relationship on the entity can be helpful in large or complex systems. You can use the notation shown in Figure 3-1 in E/R diagrams to show whether an entity is weak or regular.

Figure 3-1. This notation can be used to distinguish weak and regular entities.

It's occasionally useful to divide relationships into "IsA" and "HasA" types. The concept is straightforward: entity A either IsA B or HasA B. For example, an Employee IsA SoftballTeam member; the same Employee HasA(n) Address. Of course, "is" and "has" are not always very good English terms to describe a relationship. An Employee doesn't "have" a SalesOrder, he "creates" one; but as it's clearly not the case that the Employee is a SalesOrder, the intellectual stretch isn't too great.

The classification of participation in a relationship is also an indication of the optionality of the relationship: whether or not an entity is required to participate in a given relationship. This is a rather tricky area because implementation by the database engines doesn't match the problem domain, as we'll see when we discuss the implementation of data integrity in Chapter 4.

The maximum number of instances of one entity that can be associated with an instance of another entity is what I refer to as the cardinality of a relationship. (Note that both degree and cardinality have slightly different meanings when applied to relationships than when applied to relations.) There are three generic flavors of cardinality: one-to-one, one-to-many, and many-to-many.

I use the notation shown in Figure 3-2 to indicate the cardinality and optionality of relationships. I find the crow's foot notation (introduced in Chapter 1) the most expressive and simplest to explain to clients. Obviously, alternative techniques are available, and you must use the one that works best for you.

Figure 3-2. This notation will be used to indicate optionality and cardinality.