The E-R model cannot express relationships among relationships.
When would we need such a thing?
Consider a DB with information about employees who work on a particular project and use a number of machines doing that work. We get the E-R diagram shown in Figure 2.20.
Relationship sets work and uses could be combined into a single set. However, they shouldn’t be, as this would obscure the logical structure of this scheme.
The solution is to use aggregation.
- An abstraction through which relationships are treated as higher-level entities.
- For our example, we treat the relationship set work and the entity sets employee and project as a higher-level entity set called work.
- Figure shows the E-R diagram with aggregation.
Figure: E-R diagram with aggregation
Transforming an E-R diagram with aggregation into tabular form is easy. We create a table for each entity and relationship set as before.
The table for relationship set uses contains a column for each attribute in the primary key of machinery and work.