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.
ER modelling[change | change source]
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[change | change source]
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[change | change source]
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.
References[change | change source]
- Peter Chen. "The Entity Relationship Model: Toward a Unified View of Data" (PDF).
- A.P.G. Brown, "Modelling a Real-World System and Designing a Schema to Represent It", in Douque and Nijssen (eds.), Data Base Description, North-Holland, 1975, ISBN 0-7204-2833-5.
- M. L. Brodie and J. T. Liu. The power and limits of relational technology in the age of information ecosystems Archived 2013-05-10 at the Wayback Machine. On The Move Federated Conferences, 2010.
- A. Badia and D. Lemire. A call to arms: revisiting database design. SIGMOD Record 40, 3 (November 2011), 61-69.