www.entitymodelling.org - entity modelling introduced from first principles - relational database design theory and practice - dependent type theory
Often the instances of a particular entity type have a number of relationships with other entity types which taken in combination have a uniqueness condition that holds true of them to the effect that that entities of the type are uniquely determined by their combined relationships with entitites of the other related types. Each combination of relationships for which such a uniquness condition holds is a candidate identification systems for entities of that type and this in turn is significant for data modelling as discussed in the earlier perpective in section. foundations of data. We use a bar notation across the contributing relationships as shown in the examples that follow. If we wish to document multiple independent uniqueness constraints then the bars can be subscripted.
A many-many relationship is one depicted with a crows foot at each end whereas a many-one relationship is one with a crows foot at one end but not the other. An example many-many would be:
An alternative to using a many-many relationship is to use two many-one relationships and an entity type. The result is shown in figure 28. Any entity type introduced to eliminate a many-many relationship in this way is commonly called an intersection entity; unavoidably an entity of this type, and, by the way, we are better calling it the intersection type, is unique with respect to the entities it relates. In the example of figure 28 this is to say that for any student and any course there is at most one enrollment that relates to that student and that course. This is documented by placing a bar across each of the two relationships of the enrollment entity type.
The avoidance or elimination of many-many-relationships from models is important to database designers who wish to represent or communicate the facts of a situation in tabular form. After elimination of many-many relationships the tables that are required are just one table per entity type in the model. This topic is covered in chapter 5.
Instead of using a pair of reference relationships in the elimination of a many-many, one composition and one reference can be used instead as illustrated in figure 29.
Elimination by party within agreement— this is the model that feels most appropriate conceptually in that ‘parties’ are parts of agreements rather than parts of individuals.
When a many-many recursive relationship is eliminated using a pair of reference relationships then the resulting model has the shape shown in figure 30.
In regard to any model shaped as in figure 30, we can think of entities of the left hand type as being points and those of the right hand type as connections between these points; in this way we can imagine the instances of a many-many relationship as forming a network. For example because social interactions between people are many to many we can, and do, speak of social networks and in like manner we speak of business networks, supply networks, computer networks and so on; these are all usages based on many-many relationships between various types of entities as for instance this one: