In this section we explore the various types of relationships that can occur and discuss the constraints that are used to specify such relationships. In Chapter 2, we briefly explored three different relationship types: 1:N (one-to-many), M:N (many-to-many), and 1:1 (one-to-one). The connotations of these three relationships are what we refer to as cardinality. The cardinality constraint of a relationship relates to the specification of how many occurrences of an entity can participate in the given relationship with any one occurrence of the other entity in the relationship.
In Figure 3.12, part (a), we demonstrate the specification of cardinality constraints for the one-to-many relationship Works. Note that the specification is done by placing the “1” above the left line of the relationship, specifying one employee performs an employee work day; and the “N” above the right line, specifying that many client work days may be performed by an employee. To determine the cardinality of a relationship, you have to ask yourself the question, “How many items (records) in this entity could be related to any one item (record) in the other entity—one or multiple?” The answer determines that half of the cardinality ratio, and then the same question is asked in the reverse direction of the relationship in order to determine the other half of the cardinality ratio. In our example, we take the relationship in Figure 3.12, part (a) and ask the question, “How many work days can an employee have on a client service engagement?” The answer is many (based on the attributes specified for WORK_COMPLETED in Figure 3.12, part (a), which indicates that a given occurrence in the WORK_COMPLETED entity relates to one employee’s time spent on a given client in a single day—based on time being captured by the Date attribute). The question is then reversed and we ask, “How many employees can provide a specific employee work day?” The maximum number will be one. Hence, the cardinality of the relationship is specified as one-to-many and notated on the diagram with the “1” and “N.” For the database to maintain this relationship, a constraint must be enforced to ensure that data are never entered indicating that more than one employee is responsible for a given client work day.
Cardinality is the most common constraint specified in E-R diagrams. The other meaningful constraint that may be specified is participation. The participation constraint is used to specify both the minimum and maximum participation of one entity in the relationship with the other entity. In Figure 3.12, part (b), the participation constraints are reflected in the partial E-R diagram. In our Works relationship we just discussed, not every employee will have worked on specific client service projects, but rather may have non-client service responsibilities, such as training, that he or she spends time on. The “many” in the cardinality ratio only specifies the maximum participation in the relationship, not the minimum. In specifying the participation in the relationship, the maximum is still many, but the minimum may be zero. The line on the right reflects the range of zero to many occurrences of work being completed on client projects with the notation (0,N), where the numbers reflect (minimum, maximum). On the other hand, for any given occurrence of a client workday, the maximum of one employee providing the specific service still holds. At the same time, the minimum is also one as there must be an employee who performs a particular occurrence of the completed work. Note the required participation of one, and only one, employee is shown on the left line of the relationship as (1,1).
While the participation constraint may provide more information, it is still used much less frequently than the cardinality constraint. As such, we will tend to present the diagrams in this text using cardinality constraints. It is important, however, that you are familiar with both types of constraints and the notation applied, since as a member of a team developing or using an E-R diagram, you need to be able to communicate using the methods selected by a given organization.
What is different about the information provided through cardinality constraints versus participation constraints?