The entity-relationship model (ERM) is used in particular in the modeling of database structures, e.g. for the description of the information and relationships of information systems. The entity-relationship model is the most well-known notation in semantic data model — and is used to describe things / (physical) objects (entities) and their relationships.
The enhanced entity-relationship model (EERM) includes all elements of ER models and extends the notation with regard to relationships of entity types: inheritance, specialization / generalization, categories. With these new relationships between entity types, it is easier to model relationships at different granularities and to represent common attributes of complex data structures.
Pertuniti implements the Chen notation for ER diagrams. Cardinalities are stored and represented as text, which also allows modeling in the (Min,Max) notation. ER diagrams in Pertuniti are stored in an XML schema that represents the semantics in an easily machine-readable way, allowing further automation (e.g. generation of tables and constraints). Semantics and representation are organized separately in this schema.
Since ER models describe data structures abstractly, some terms are often confused or oversimplified. For example, when modelers use the term “entity” they usually mean the “entity type”. The following list is therefore intended to clarify frequently used terms:
Composite attributes represent more complex structures that refer to one entity type. Composite attributes can be combined with the other types of attributes, e.g. as a multi-valued attribute for different addresses each with street, house number, postal code, city, country.
In a 1:1 relationship, each entity is assigned to one or to no other entity. For example, a person can have a driver's license, but does not have to. A driver's license can also be related to only one person.
In ER models, 1:1 relationships are rare and may be an indicator that entity types should be represented as class hierarchies instead, see enhanced entity-relationship model.
In a 1:n relationship, each entity of an entity type is assigned to no entity, one entity, or multiple entities of another entity type. For example, an employee may work in a department (1 in the example) and a department may have multiple employees (N in the example).
1:n relationships are usually mapped using a foreign key in the database table that references one (or none: NULL) other entity.
In n:m relationships, entities on both sides can be related to each other any number of times, e.g., a customer can buy no product, one product, or multiple products, and a product can be bought not at all, once, or more often.
Often, n:m relationships could be better modeled as a separate entity type. For example, the above example makes a lot of sense if customers are to be suggested similar products based on their previous purchases — and the relationship is analyzed for this purpose. However, the buys relationship is not about orders: The same customer could buy the same product more than once, but the specific relationship between that customer and product exists only once.
For mapping in database structures, n:m relationships are usually split into two 1:n relationships. The resulting additional database table consists of foreign keys of the involved entity types and attributes of the original relationship type.
Cardinalities with multiple entity types involved are covered in pertinent lectures and in textbooks.
Each entity of an entity type whose role in a relationship type is marked with a double line as total participation in the relationship type must participate in at least one concrete relationship of that relationship type.
The example on the 1:1 relationship becomes more plausible with total participation: A driver's license is always assigned to exactly one person. Not every person has to have a driver's license. Total participations are also used in the enhanced entity-relationship model to concretize disjoint and overlapping subclasses as well as categories.
Using the example with buildings and rooms introduced above, weak entities and identifying relations are now illustrated.
For a multinational company, buildings and rooms are modeled. Buildings can share an address on a larger campus, i.e. per building, the complete address does not have to be unique. Instead, each building is assigned a unique building number throughout the company, which is signposted on an individual campus and uniquely identifies the building in the database and to employees.
A building can contain many rooms, but a room can be located exclusively in exactly one building. Each room is assigned a room number that contains both the floor and any sequence number that is unique for the building and floor, e.g. “3-12” (floor 3, room 12). The same room number may be reassigned for some buildings, but is unique within that building. The floor can be easily read or derived from the room number.
The room number is thus a partial key attribute that becomes unique only together with the associated building. The entity type room is a weak entity type because the key attribute building number belonging to the entity type building is required to identify a concrete entity. The relationship belongs to makes this circumstance an identifying relationship type.
Frequently, types of real world objects to be modeled are very similar, e.g. employees and customers as contacts both have a name and an address. Therefore, it makes sense to create “class hierarchies” or bottom-up categories, which can be used to abstract similar attributes and relationship types and make them clearer.
Modeling hierarchies of different entity types would be an obvious choice in order to be able to use all known elements of ER diagrams. However, the connection within hierarchies is not possible with a relationship type, as the entities of the involved entity types are the same real world object. In this section, simple inheritance, disjoint and overlapping subclasses, and categories are exemplarily introduced. All elements of the enhanced entity-relationship model can also be modeled in Pertuniti.
Inheritance in enhanced entity-relationship models is implemented in Pertuniti using the notation of Elmasri & Navathe (2017) — both for simple inheritance as shown in this example, and for subclasses and categories.
Simple inheritance is an IS-A relationship, i.e. every entity from the subclass (here car) is always also a member of the superclass (here vehicle). Total participations do not have to be annotated in this representation, since the subclass always participates totally — and if this also applies to the superclass, one can omit the division into two classes.
In inheritance, all attributes and relationships of the superclass are “inherited” by the subclass, i.e. inheritance can be used to generalize and specialize entities.
A class hierarchy with disjoint subclasses is indicated by a circle with a “d” (disjoint). The superclass is represented with a solid line — or a double line for total superclass participation. Subclass connections are marked with a “U” on the line. Subclasses always participate totally in the relationship, so total participation is not additionally represented with a double line.
For disjoint subclasses in this example, each vehicle entity (total participation in the relationship) is also either of the entity type car or of the entity type truck, at least in the modeled mini-world. Each car and truck has a vehicle ID and a license plate number (both of which can be used as key attributes), but only cars have information about the maximum number of passengers, and only trucks have precise information about the cargo hold. Relationships to vehicles, e.g. for rentals, deliveries or sales, also apply to cars and trucks.
Apart from an “o” (overlapping) marking, overlapping subclasses are modeled like disjoint subclasses. Again, the participation of the super class can be total.
For overlapping superclasses in the example, each entity of the type person (total participation in the relationship) is also an entity of at least one of the subclasses employee, alumnus or student, i.e. master data on abstract persons are organized in the entity type person, while properties specific to the respective subclass are modeled there.
Overlapping subclasses do not mean that concrete instances must also always overlap, but that instances that are part of multiple subclasses are possible in the data model.
Categories are subsets of the union of multiple entity sets. In the example, each person, company and bank can be in principle of the entity type owner, but does not have to be. Each owner is a person, a company, or a bank, i.e. like a subclass, the category “owner” totally participates in this relationship between entity types.
If a category is marked with a double line, all entity types that are united to the category participate totally. This situation can also be represented with disjoint subclasses.
Elmasri, R., & Navathe, S. B. (2017). Fundamentals of Database Systems 7th Edition.