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 / Attributes	Customer-ID	Name	City / Suburb	Road	Zip Code
Entities / AttributesEntity 1	Customer-ID19720224	NameSmith Ray	City / SuburbMosman	Road12, Vista St.	Zip Code2088
Entities / AttributesEntity 2	Customer-ID19730623	NamePeters Veronika	City / SuburbPyrmont	Road123, Harris St.	Zip Code2009
Entities / AttributesEntity 3	Customer-ID19731010	NameWang Peter	City / SuburbC.B.D	Road8, Liverpool St.	Zip Code2007
Entities / AttributesEntity 4	Customer-ID...	Name...	City / Suburb...	Road...	Zip Code...
Entities / AttributesEntity n	Customer-ID...	Name...	City / Suburb...	Road...	Zip Code...

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.

ERM Example No. 1 - "Basic ERM"

ERM Example No. 2 - "Cardinalities / 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 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.

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:

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-#.

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 Modelling	Relational Model	Table (physical) view
Entity Relationship ModellingEntity / Relationship Type	Relational ModelRelation	Table (physical) viewTable
Entity Relationship ModellingEntity / Relationship	Relational ModelTuple	Table (physical) viewLine or (Data) Record
Entity Relationship ModellingAttribute	Relational ModelAttribute	Table (physical) viewRow

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:

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):

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:

ERM Example No. 7 - "Recursive n : m Relationship"!