Entity-relationship models

EmployeeSupervisionmanagesworks forDepartmentNameLocationsIDcontrolsProjectworks onHoursIDLocationNameNumber ofemployeesStartdateSsnBirth dateNameFirst nameLast nameSexSalaryAddressMiddle nameN1N1111NMN
Example: Simple data model with employees, departments, and projects

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.

Contents

  1. Terms
  2. Basic elements
    1. Entity types
    2. Relationship types
    3. Attributes
  3. Cardinality
  4. Total participation
  5. Weak entities and identifying relationships
  6. Enhanced entity-relationship model
    1. Regular inheritance
    2. Disjoint subclasses
    3. Overlapping subclasses
    4. Categories
  7. Literature

Terms

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:

Basic elements

Entity types

Entity type
An entity type is a classification of similar entities, e.g. employees, projects, and departments. Entity types are divided into strong entity types (as shown on topthe left) and weak entity types (see below) — depending on whether there are one or more key attributes of the same entity type (strong) or related entities are required for identification (weak).
Weak entity type
For the identification of weak entities, it is necessary to use an attribute value of another strong entity related to this entity, e.g. room numbers could only become identifiable with the corresponding building. Weak entities and identifying relations are explained in a separate section.

Relationship types

Relation- ship
Relationship types are classifications of similar relationships / links, e.g. Employee manages Project, Employee is employee of Department. Relationship types are binary or n-ary, while the same entity type can appear in one or more roles. Roles in entities are represented with a solid line between entity type and relationship type.
Ident. Relation- ship
Identifying relationship types allow, in accordance with their naming, the identification of weak entities. For example, the weak entity type rooms may be related to the strong entity type buildings via the identifying relationship type in — and the identification of a single entity happens via the partial key room number as well as via the foreign key building number. Weak entities and identifying relations are explained in a separate section.

Attributes

Attribute
Attributes are typifications of characteristics of objects of an entity type — and represent e.g. first names, last names, dates of birth. There are different types of attributes, which are represented with different borders (multi-valued, derived) or understrokes (key attributes, partial keys).
Key attribute
Key attributes are attributes or attribute combinations that uniquely identify an entity. If a key attribute is an attribute combination, it is modeled as a composite attribute, with the root node being a key attribute. Thus, several different attribute combinations or individual identifying attributes can be clearly distinguished from each other and represented as key attributes.
Multivalued attribute
Attributes can consist of multiple values, e.g. for multiple addresses for a single person or multiple keywords for a document. These multi-valued attributes are represented by an ellipse with a double line.
Derived attribute
Some information about an entity is already known from other attributes or relationships, e.g. the age from the current date and birthday of a person or the number of orders from the set of stored relationships between person and orders. Derived attributes represent that such information about entities is known but does not need to be stored (again).
Partial key
Identification of weak entities usually requires both partial key attributes and identifying relations. Attributes of a weak entity that can partially identify this entity are underlined in dashed lines.
CompositeattributeBAC

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.

Cardinality

1:1

PersonDriver'slicensehas11
Example: 1:1 relationship

1:1

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.

1:n

EmployeeDepartmentworks inN1
Example: 1:n relationship

1:n

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.

n:m

CustomerProductbuysNM
Example: n:m relationship

n:m

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 in n-ary relationships

Cardinalities with multiple entity types involved are covered in pertinent lectures and in textbooks.

Total participation

PersonDriver'slicensehas11
Example: Total participation - no driver's license without an owner

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.

Weak entities and identifying relationships

RoomBuildingbelongs toRoomNo.Building No.FloorAddressN1
Example: Rooms of buildings as weak entity type

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.

Enhanced entity-relationship model

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.

Regular inheritance

VehicleCar

Regular inheritance

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.

Disjoint subclasses

VehicledCarTruckCargoholdMax.occupantsVehicleIDLicense plate
Example: Disjoint subclasses

Disjoint subclasses

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.

Overlapping subclasses

oAlumnusStudentEmployeePersonNameMajor subjectSalaryDegreesSSN
Example: Overlapping subclasses

Overlapping subclasses

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

CompanyBankPersonuOwner
Example: Category

Categories

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.

Literature

Elmasri, R., & Navathe, S. B. (2017). Fundamentals of Database Systems 7th Edition.

Dr. Johannes Tenschert

CEO, Process Science

+49 89 21540190
johannes.tenschert@pertuniti.de

Wir nutzen Cookies und Google Analytics um diese Webseite für Sie zu optimieren. Sind Sie damit einverstanden? (Opt-In)

(Sie können diese Entscheidung jederzeit widerrufen - mehr Informationen)