Vorlesen

Theory

The contents of this section follow - with some adaptions - the theory part of an earlier webtrainer designed by Andreas Hohengassner [Hohe99].

General Information

Entity relationship modelling has become a key technique in modelling information systems. It describes the static data structure, i.e. the objects involved and their inter-relationship. The dynamic component (the business process) would be modelled using event-driven process chains. The original method was proposed by P.P. Chen [Chen76]. The model presented here is following the notation of Chen [Chen83] and includes notational elements by A.-W. Scheer [Sche98].

In the following subsections a descriptive method will be explained and used.

Concerning the practical utilization, it should be mentioned that entity relationship models play an important role in modelling concepts for business engineering and customizing of SAP R/3 systems.

Basic ERM

During the construction of an entity relationship model (ERM) it is necessary to identify those objects and their relationships which illustrate reality. Distinguishable objects of the real or conceptional world are called entities in an entity relationship model. An entity is, for example, a certain customer who is defined by his name and address and can furthermore be uniquely identified by his customer number. Note: ER modelling works on a type level, whereas particular instances are stored in the database tables derived from the ERM.

Entities with the same characteristics, designated as attributes (e.g. Name, Address), are combined to entity types (e.g. Customer). Depending on their characteristics the individual entities can have different values of attributes. The set of values an attribute can take is called a domain.

One can distinguish between descriptive and identifying attributes. Descriptive attributes define the relevant data of an entity. Identifying attributes, also known as key attributes, provide unique identification of a certain entity. Several attributes where each of them can serve as identifier are designated as key candidates.

Entities / AttributesCustomer-IDNameCity / SuburbRoadZip Code
Entity 119720224Smith RayMosman12, Vista St.2088
Entity 219730623Peters VeronikaPyrmont123, Harris St.2009
Entity 319731010Wang PeterC.B.D8, Liverpool St.2007
Entity 4...............
Entity n...............

Table No. 1 - ERM: Exemplary Table Representation of Entity Type "Customer"

The distinguishing feature between entity and attribute is that attributes are assigned to entities; attributes, however, cannot possess their own attributes. If an attribute is to be described by other attributes, it becomes an entity type. For example, the attribute Address that is described using further attributes such as City, Road and Zip Code, will become the entity type Address. Relationship types are logic functions between entity types. An individual relationship can therefore only exist if the entities related to each other exist. For instance, the entity types Employee and Department may be linked by the relationship type Belongs to. Furthermore, relationship types may bear attributes. Belongs to, for instance, may have the attribute Date_of_Entry.

Graphically entity types are plotted as rectangles, relationship types as lozenges and attributes as ovals. This representational form follows the notation of Chen, which is the original and still considered to be the most common method used.

Entity Type

rectangle

Symbol "Entity"

Definition: A Set of distinguishable objects of the real or conceptional world.

Characteristics:

  • They can only be expressed by nouns

  • They are described or identified by attributes

  • They are registered at the data view

Relationship Type

lozenge

Symbol "Relationship"

Definition: Logical function between two or more entity types.

Characteristics:

  • They will normally be expressed by verbs

  • They may bear attributes

  • They are registered at the data view

Attribute

oval

Symbol "Attribute"

Definition: Property of an entity (type) or a relationship type.

Characteristics:

  • They may be descriptive or identifying (key attributes)

  • If being described by further attributes, they become entity types

  • They are registered at the data view

Cardinalities

Kein Bild hinterlegt

Relationship types are differentiated according to the number of entities they connect. For instance: can a Customer purchase several or only one Article, can an Article be purchased by several Customers? This is represented by cardinalities. The cardinality of a relationship indicates whether the relationship is 1 : 1, 1 : n, or n : m.

Connecting entity types A and B by a 1 : 1 relationship means that each entity of an entity type A is attributed to at most one entity of entity type B and vice versa. In a 1 : n relationship an entity of type A is assigned to none, one, or several entities of type B. Each entity of type B, however, is assigned to at most one entity type A. In a n : m relationship each entity of an entity type is assigned to zero, one, or many entities of the other entity type in the relationship (Note: It does not matter which entity type is given the n or the m. Sometimes such relationships are also referred to as n : n).

Figure No. 1 - ERM: Cardinalities of Relationships between Entity Types

Figure No. 1 - ERM: Cardinalities of Relationships between Entity Types

Kein Bild hinterlegt

Excurse: In German literature the cardinality of a 1 : n relationship is often described in a reverse way. However, in most English texts, as well as in all examples and exercises of this webtrainer, the 1 : n notation, as described above, will be used.

Cardinalities are entered at the edges of an ERM. In order to be able to clearly identify entities, a 1 : 1 relationship has to exist between an entity type and at least one attribute. The identifying or key attributes are underlined in the diagram (In figure no. 2 cardinalities as well as key attributes have been depicted in red color!).

ERM: Representation of Cardinalities and Key Attributes

Figure No. 2 - ERM: Representation of Cardinalities and Key Attributes

Extended Cardinalities

A more detailed representation of cardinalities involves specifying upper and lower limits for the number of permissible relationship instances a relationship may have [Sche98].

Every relationship will be expressed by two (min, max) degrees of complexity. The range of possible values is defined by 0 ≤ min ≤ 1 and 1 ≤ max ≤ *, whereby usually * is used instead of n.

ERM Example No. 3 - "Extended Cardinalities"

Extended ERM (eERM)

Note: Only those extensions to the ERM relevant to solve the exercises of this webtrainer have been included in the theory section. For further reading on this topic please refer to the literature section!

 

Generalization / Specialization

In generalization, similar entity types are grouped together to one supertype. Attributes which are common to the individual entity types (subtypes) are thereby transferred to the generalized entity type. Only the deviating attributes have to be stored with the individual entity type. Such a relationship is plotted using a triangle with the wording "Is a".

The design process may either start with the supertype, e.g. a cost centre, which is then broken down into subtypes - which is called specialization (e.g. performance-dependent and performance-independent cost centres), or it may start with subtypes which are then generalized to a supertype (generalization).

Specialization, must be:

     

  • Disjoint

  •  

  • Complete

  •  

Definition: Let M be the set of instances of the supertype and S1 and S2 the subtypes:

Disjoint: S1 ∩ S2 = { }, Complete: S1 ∪ S2 = M

ERM: Generalization / Specialization

Figure No. 3 - ERM: Generalization / Specialization

ERM Example No.4 - "Generalization / Specialization"


Generally, generalization / specialization is not an end in itself. The criterion is whether it contributes to the understanding of the information system and whether it bears additional information for the system developer. Whenever there is an entity type where some methods are applicable only to a certain group of entities it is a good idea to distinguish subtypes. On the other hand, whenever there are seemingly separate entity types which share some methods applied to them, you may want to combine them to a common supertype.

Redefinition of a Relationship Type

The redefinition of a relationship type as an entity type is graphically represented by a rectangle of an entity type bordering the lozenge of a relationship type.

Figure No. 4 - ERM: Reinterpretation of a Relationship Type

Figure No. 4 - ERM: Reinterpretation of a Relationship Type

The easiest way to understand the reinterpretation of a relationship type is to remember that entity types can only be expressed as nouns and relationship types should be expressed as verbs. In the reinterpretation process the verb will be transferred into a noun (e.g. is shipped - relationship type transferred into the shipping - entity type).

ERM Example No. 5 - "Reinterpretation of a Relationship Type"

 

Recursive Relationships

With recursive relationships several parallel edges between an entity type and a relationship type exist. That means that several entities of the same type can take part in a relationship.

An example of such recursive relationships is a bill of materials. A gozinto graph serves as a starting point for the data model:

Figure No. 5 - ERM: Example of a Gozinto Graph

Figure No. 5 - ERM: Example of a Gozinto Graph

From Fig. 5, entity type Part and relationship type Structure can be taken directly. Each part of the gozinto graph is an item of entity type Part and each arrow an item of relationship type Structure. Each part is identified by a part number Part-#, and each relationship by specification of the higher-level and the lower-level part in the structure. To enhance clarity, part numbers are shown with the additions H (for higher-level) Hpart-#, and L (for lower level) Lpart-#.

Figure No. 6 - ERM: Example of a Bill of Materials

Figure No. 6 - ERM: Example of a Bill of Materials

Relational Data Model

The design of the logical data structure by means of an ERM is the first step in the design of a database. The following paragraphs and figures show how the transfer of the data structure into a relational table definition may be achieved:

Original entity types (and in some cases relationship types) are viewed as relations. Thereby a relation corresponds to an entity type and is determined by a name and a set of attributes (at least one has to be a key attribute). Physically a relation is represented as a table. The individual entities are called tuples in the relational data model, which correspond conceptionally to data records. The following table shows how different views of the same thing lead to different designations:

 

Entity Relationship ModellingRelational ModelTable (physical) view
Entity / Relationship TypeRelationTable
Entity / RelationshipTupleLine or (Data) Record
AttributeAttributeRow

Table No. 3 - ERM: Comparison of Notions

With 1 : 1 relationship types one of the two relations contains the primary key attribute of the referred entity type as a foreign key:

Figure No. 7 - ERM: Table Definition of a 1 : 1 Relationship

Figure No. 7 - ERM: Table Definition of a 1 : 1 Relationship

A 1 : n relationship type can be viewed as a parent - child relationship. It is represented by adding the primary key attribute of the "1-side" (parent) as a foreign key to the entity type on the "n-side" (child):

Figure No. 8 - ERM: Table Definition of a 1 : n Relationship

Figure No. 8 - ERM: Table Definition of a 1 : n Relationship

ERM Example No. 6 "Recursive 1 : n Relationship"


A n : m relationship type requires its own table. This table receives the primary key attributes of both entity types as a combined primary key:

Figure No. 9 - ERM: Table Definition of a n : m Relationship

Figure No. 9 - ERM: Table Definition of a n : m Relationship