OnLine
Applied Information Studies

published 20 Oct 1995 : 01 Jan 1996 updated

PowerDesigner Cardinal in Entity Relationships

In E-R diagrams it is customary to specify the "cardinal" or "connectivity" of each end of a relationship; i.e., the minimum and maximum number of instances related. However this is most frequently expressed in more general terms as:

• Minimum for each termination of zero or one
• Maximum for each termination of one or many

In the diagram below, an order is related to a minimum of one customer and a maximum of one customer while a customer relates to a minimum of zero orders and a maximum of any number or orders.

We will see that this representation of cardinal is flawed in both theory and application. Consider the relationship below (simplified) between commercial airline flights and pilots. In most conventional E-R models it might be diagrammed something like this:

indicating that every pilot may fly zero to many flights and each flight must have at least one pilot, possibly more. In fact we know that most (for this example, all) commercial flights require at least two pilots. This could be represented in some E-R methods with the addition or substitution of quantified cardinal labels:

But now it is evident that the symbol on the left representing "a minimum of one pilot per flight" is not correct - the true minimum is two pilots per flight. Why then is the symbolic vocabulary restricted to showing a minimum of one? I suggest that this symbolic format (in many different guises) has developed from a simple Boolean view of the issues:

which is implemented, for example, in PowerDesigner as:

Certainly this Boolean approach is much more compact and understandable than the complete list of min-max combinations used in some CASE tools (e.g., Popkin's System Architect). Regardless if how presented, it also has two unfortunate side effects:

• The symbols represent derived information rather than root data; e.g., if we know that the minimum is 2 then we can derive that the minimum is also > 0.
• In this derivation, information is lost; i.e., the exact minimum, if > 0, and the exact maximum, if it is more specific than "many".

I agree that there are meaningful and important distinctions based on the minimum and maximum instances in a relationship. A termination with any minimum greater than zero is mandatory and thus requires the enforcement of its existence. Some tools (InfoModeler, ER-Win, Silverrun) allow specification of the allowable range of values but still treat the "mandatory" property separately.

Likewise a maximum of one implies very different structural and procedural treatment than a maximum of more than one. The common symbol sets can model these properties for algorithmic translation into coded instructions (DDL and DML). But if we should need to capture more specific limits (e.g., a minimum of two pilots) then these symbols fail and we must resort to unmodeled methods and manual transformation into implementation structures.

Thus, in focusing on those special properties to the exclusion of their more general context, we have discarded a valuable component of E-R modeling and its automatic transformation. Some tools (InfoModeler, ER-Win, Silverrun) allow specification of the allowable range of values but still treat the "mandatory" property separately.