Entity Modelling

www.entitymodelling.org - entity modelling introduced from first principles - relational database design theory and practice - dependent type theory

Uniqueness Assertions/Identifiers

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.

Examples occuring when many-many relationships are eliminated

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.

Figure 28
Students and courses in many-many relationship represented using an intersection type enrollment. Because it represents a many-many realtionship between student and course, for any student and course there is at most one enrollement of that student on that course. Another way of saying this is that an enrollment can be identified uniquely by identifying the course and the student that it is related to. This is the meaning of the bars across the two relationships. Loosely spesaking they are said to be identifying relationships.

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.

Figure 29
Eliminating of a many-many relationship using composition. Now the intersection type is party and, as shown by the bars, it has two identifying relationships one a dependence and one a reference relationship.

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.

Figure 30
The shape after elimination of a many-many recursive relationship.

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: