Managing & Using MySQL, 2nd Edition

Relationships

The identifiers in our entities enable us to model their relationships. A relationship describes a binary association between two entities. A relationship may also exist between an entity and itself. Such a relationship is called a recursive relationship . Each entity within a relationship describes and is described by the other entity. Each side of the relationship has two components: a name and a degree.

Each side of the relationship has a name that describes the relationship. Take two hypothetical entities, an Employee and a Department. One possible relationship between the two is that an Employee is “assigned to” a Department. That Department is “responsible for” an Employee. The Employee side of the relationship is thus named “assigned to” and the Department side “responsible for.”

Degree, also referred to as cardinality, states how many instances of the describing entity must describe one instance of the described entity. Degree is expressed using two different values: “one and only one” (1) and “one or many” (M). An employee is assigned to one department at a time, so Employee has a one-and-only-one relationship with Department. In the other direction, a department is responsible for many employees. We therefore say Department has a “one-or-many” relationship with Employee. As a result, a Department could have exactly one Employee.

It is sometimes helpful to express a relationship verbally. One way of doing this is to plug the various components of the relationship ...