Entity–relationship model

In software engineering, an entity–relationship model is a conceptual model of data. Very often, the term is shortened to ER model. ER models are used to create relational databases. They show how the different parts of a data model interact with each other.

A sample ER diagram

Peter Chen proposed to use this modelling approach in an article he wrote in 1976. Chen was not the first to have this idea. After the publication of the article, the idea was modified as well.

ER modelling

Two related entities

An entity with an attribute

A relationship with an attribute

Primary key

In an ER model, there are three different types of objects:

• An entity exists in the real world; for example: an employee called Smith, or a project with number 1234.
• A relationship shows how two entities relate to each other
• Both entities and relationships can have attributes. Anything that is useful to know about an entity or a relationship is an attribute.

Since the database must be able to find an entity or a relationship, it must be able to identify it with a set of attributes that is unique. This set of attributes is usually called primary key. After all the relations have been mapped, they are usually also revised to include cardinalities; a cardinality specifies the number of entities related in a relationship. This information is needed when the database is created. Depending on cardinality and the level of database normalisation, it may be necessary to introduce additional entities and relationships.

Creating a database

When everything has been described, it is easy to create a relational database, as follows:

• Every type of entity can directly be mapped to a relation ("database table")
• Every type of attribute can directly be mapped to attributes ("columns" in the database table)
• Attributes can directly be mapped to attributes ("columns" in the database table)

Limitations

An ER model describes the relations between entities. It is adapted to represent relational data. Data that cannot easily be put into this form should not be modelled with an ER model. ER is aimed at designing a database from scratch; using it to describe and change a database that already exists may be more difficult to do.

Even where it is suitable in principle, ER modeling is rarely used as a separate activity. One reason for this is that there are many tools that allow diagramming and that have other design support directly on relational database management systems. These tools can extract database diagrams that are very close to ER diagrams from existing databases, and they provide alternative views on the information contained in such diagrams.

In a survey, Brodie and Liu could not find a single instance of entity–relationship modeling inside a sample of ten Fortune 100 companies. Badia and Lemire blame this lack of use on the lack of guidance but also on the lack of benefits, such as lack of support for data integration.