7.4.3: Cardinality

Cardinality is a constraint on a relationship specifying the number of entity instances that a specific entity may be related to via the relationship. Suppose we have the following rules for departments and employees:

• • • A department can have several employees that work in the department
    • An employee is assigned to work in one department.

From these rules we know the cardinalities for the works in relationship and we express them with the cardinality symbols 1 and n below.

The n represents an arbitrary number of instances, and the 1 represents at most one instance. For the above works in relationship we have

• • • a specific employee works in at most only one department, and
    • a specific department may have many (zero or more) employees who work there.

n, m, N, and M are common symbols used in ER diagrams for representing an arbitrary number of occurrences; however, any alphabetic character will suffice.

Based on cardinality there are three types of binary relationships: one-to-one, one-to- many, and many-to-many.

One-to-One

One-to-one relationships have 1 specified for both cardinalities. Suppose we have two entity types Driver and Vehicle. Assume that we are only concerned with the current driver of a vehicle, and that we are only concerned with the current vehicle that a driver is operating. Our two rules associate an instance of one entity type with at most one instance of the other entity type:

• a driver operates at most one vehicle, and
• a vehicle is operated by at most one driver.

And so, the relationship is one-to-one.

One-to-Many

One-to-many relationships are the most common ones in database designs. Suppose we have customer entities and invoice entities and:

• • • an invoice is for exactly one customer, and
    • a customer could have any number (zero or more) of invoices at any point in time.

Because one instance of an Invoice can only be associated with a single instance of Customer, and because one instance of Customer can be associated with any number of Invoice instances, this is a one-to-many relationship:

Many-to-Many

Suppose we are interested in courses and students and the fact that students register for courses. Our two rule statements are:

• any student may enroll in several courses,
• a course may be taken by several students.

This situation is represented as a many-to-many relationship between Course and Student:

As will be discussed again later, a many-to-many relationship is implemented in a relational database in a separate relation. In a relational database for the above, there would be three relations: one for Student, one for Course, and one for the many-to-many. (Sometimes this 3rd relation is called an intersection table, a composite table, a bridge table.)

Partly because of the need for a separate structure when the database is implemented, many modellers will ‘resolve’ a many-to-many relationship into two one-to-many relationships as they are modelling. We can restructure the above many-to-many as two one-to-many relationships where we have ‘invented’ a new entity type called Enrollment:

A student can have many enrollments, and each course may have many enrollments.

An enrollment entity is related to one student entity and to one course entity.