7. ENTITY RELATIONSHIP MODELLING
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 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 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:
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.