Jump to content

Entity–relationship model

From Wikipedia, the free encyclopedia
This is an old revision of this page, as edited by Bsodmike (talk | contribs) at 04:03, 11 July 2011 (→Diagramming conventions: updated caption text for image.). The present address (URL) is a permanent link to this revision, which may differ significantly from the current revision.
A sample Entity-relationship diagram using Chen's notation

In software engineering, an entity-relationship model (ERM) is an abstract and conceptual representation of data. Entity-relationship modeling is a database modeling method, used to produce a type of conceptual schema or semantic data model of a system, often a relational database, and its requirements in a top-down fashion. Diagrams created by this process are called entity-relationship diagrams, ER diagrams, or ERDs.

This article refers to the techniques proposed in Peter Chen's 1976 paper.[1] However, variants of the idea existed previously,[2] and have been devised subsequently.

Overview

The first stage of information system design uses these models during the requirements analysis to describe information needs or the type of information that is to be stored in a database. The data modeling technique can be used to describe any ontology (i.e. an overview and classifications of used terms and their relationships) for a certain area of interest. In the case of the design of an information system that is based on a database, the conceptual data model is, at a later stage (usually called logical design), mapped to a logical data model, such as the relational model; this in turn is mapped to a physical model during physical design. Note that sometimes, both of these phases are referred to as "physical design".

The building blocks: entities, relationships, and attributes

Two related entities
An entity with an attribute
A relationship with an attribute
Primary key

An entity may be defined as a thing which is recognized as being capable of an independent existence and which can be uniquely identified. An entity is an abstraction from the complexities of some domain. When we speak of an entity we normally speak of some aspect of the real world which can be distinguished from other aspects of the real world.[3]

An entity may be a physical object such as a house or a car, an event such as a house sale or a car service, or a concept such as a customer transaction or order. Although the term entity is the one most commonly used, following Chen we should really distinguish between an entity and an entity-type. An entity-type is a category. An entity, strictly speaking, is an instance of a given entity-type. There are usually many instances of an entity-type. Because the term entity-type is somewhat cumbersome, most people tend to use the term entity as a synonym for this term.

Entities can be thought of as nouns. Examples: a computer, an employee, a song, a mathematical theorem.

A relationship captures how two or more entities are related to one another. Relationships can be thought of as verbs, linking two or more nouns. Examples: an owns relationship between a company and a computer, a supervises relationship between an employee and a department, a performs relationship between an artist and a song, a proved relationship between a mathematician and a theorem.

The model's linguistic aspect described above is utilized in the declarative database query language ERROL, which mimics natural language constructs. ERROL's semantics and implementation are based on Reshaped relational algebra (RRA), a relational algebra which is adapted to the ERM and captures its linguistic aspect.

Entities and relationships can both have attributes. Examples: an employee entity might have a Social Security Number (SSN) attribute; the proved relationship may have a date attribute.

Every entity (unless it is a weak entity) must have a minimal set of uniquely identifying attributes, which is called the entity's primary key.

Entity-relationship diagrams don't show single entities or single instances of relations. Rather, they show entity sets and relationship sets. Example: a particular song is an entity. The collection of all songs in a database is an entity set. The eaten relationship between a child and her lunch is a single relationship. The set of all such child-lunch relationships in a database is a relationship set. In other words, a relationship set corresponds to a relation in mathematics, while a relationship corresponds to a member of the relation.

Certain cardinality constraints on relationship sets may be indicated as well.

Diagramming conventions

Entity sets are drawn as rectangles, relationship sets as diamonds. If an entity set participates in a relationship set, they are connected with a line.

Attributes are drawn as ovals and are connected with a line to exactly one entity or relationship set.

Cardinality constraints are expressed as follows:

  • a double line indicates a participation constraint, totality or surjectivity: all entities in the entity set must participate in at least one relationship in the relationship set;
  • an arrow from entity set to relationship set indicates a key constraint, i.e. injectivity: each entity of the entity set can participate in at most one relationship in the relationship set;
  • a thick line indicates both, i.e. bijectivity: each entity in the entity set is involved in exactly one relationship.
  • an underlined name of an attribute indicates that it is a key: two different entities or relationships with this attribute always have different values for this attribute.

Attributes are often omitted as they can clutter up a diagram; other diagram techniques often list entity attributes within the rectangles drawn for entity sets.

Two related entities shown using Crow's Foot notation. In this example, an optional relationship is shown between Artist and Song; the symbols closest to the song entity represents "zero, one, or many", whereas a song has "one and only one" Artist. The former is therefore read as, an Artist (can) perform(s) "zero, one, or many" song(s).

Chen's notation for entity-relationship modeling uses rectangles to represent entities, and diamonds to represent relationships appropriate for first-class objects: they can have attributes and relationships of their own.

Related diagramming convention techniques:

Crow's Foot Notation

Crow's Foot notation is used in Barker's Notation, SSADM and Information Engineering. Crow's Foot diagrams represent entities as boxes, and relationships as lines between the boxes. The ends of these lines are shaped to represent the cardinality of the relationship.

Usage of Chen notation is more prevalent in the United States, while Crow's Foot notation is used primarily in the UK. Crow's Foot notation was used in the 1980s by the consultancy practice CACI. Many of the consultants at CACI (including Barker) subsequently moved to Oracle UK, where they developed the early versions of Oracle's CASE tools, introducing the notation to a wider audience. Crow's Foot notation is used by these tools: ARIS, System Architect, Visio, PowerDesigner, Toad Data Modeler, DeZign for Databases, Devgems Data Modeler, OmniGraffle, MySQL Workbench and SQL Developer Data Modeler. CA's ICASE tool, CA Gen aka Information Engineering Facility also uses this notation.

ER diagramming tools

There are many ER diagramming tools. Some free software ER diagramming tools that can interpret and generate ER models, SQL and do database analysis are MySQL Workbench (formerly DBDesigner), and Open ModelSphere (open-source). A freeware ER tool that can generate database and application layer code (webservices) is the RISE Editor. The Open Source Erviz takes a simple textual description of the ERD and then uses Graphviz to automatically produce a layout. The web application TinyModeler allows you to make ER diagrams in a web browser.

Some of the proprietary ER diagramming tools are ARIS, Avolution, Aqua Data Studio, dbForge Studio for MySQL, DeZign for Databases, ER/Studio, Devgems Data Modeler, ERwin, MEGA International, ModelRight, OmniGraffle, Oracle Designer, Oracle Data Modeler, PowerDesigner, Rational Rose, Sparx Enterprise Architect, SQLyog, System Architect, Toad Data Modeler, SQL Maestro, Microsoft Visio, Visible Analyst, and Visual Paradigm.

Some free software diagram tools just draw the shapes without having any knowledge of what they mean, nor do they generate SQL. These include yEd, LucidChart, Gliffy,[5] Kivio, and Dia. DIA diagrams, however, can be translated with tedia2sql.

See also

References

  1. ^ "The Entity Relationship Model: Toward a Unified View of Data" for entity-relationship modeling.
  2. ^ 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.
  3. ^ Paul Beynon-Davies (2004). Database Systems. Houndmills, Basingstoke, UK: Palgrave
  4. ^ IDEF1X[dead link]
  5. ^ "Gliffy March 2007 NewsLetter", March 1, 2007, accessed January 13, 2011

Further reading

  • Richard Barker (1990). CASE Method: Tasks and Deliverables. Wokingham, England: Addison-Wesley.
  • Paul Beynon-Davies (2004). Database Systems. Houndmills, Basingstoke, UK: Palgrave
  • Peter Chen, Peter Pin-Shan (March 1976). "The Entity-Relationship Model - Toward a Unified View of Data". ACM Transactions on Database Systems. 1 (1): 9–36. doi:10.1145/320434.320440.
  • 1976. "The Entity-Relationship Model--Toward a Unified View of Data". In: ACM Transactions on Database Systems 1/1/1976 ACM-Press ISSN 0362-5915, S. 9–36
Wikimedia Commons has media related to Entity-relationship models.
Retrieved from "https://en.wikipedia.org/w/index.php?title=Entity–relationship_model&oldid=438852903"