Discover

(Redirected from Entity relationship diagrams)

In data modeling, an 'entity-relationship model' (ERM) is a representation of structured data; 'entity-relationship modeling' is the process of generating these models. The end-product of the modeling process is an 'entity-relationship diagram' (ERD), a type of conceptual data model or semantic data model.
The first stage of information system design uses these models to describe information needs or the type of information that is to be stored in a database during the requirements analysis. 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 universe of discourse (i.e. 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".
There are a number of conventions for entity-relationship diagrams (ERDs). The classical notation is described in the remainder of this article, and mainly relates to conceptual modelling. There are a range of notations more typically employed in logical and physical database design, including ''information engineering'', ''IDEF1x'' (ICAM DEFinition Language) and ''dimensional modelling''.

Contents

Connection

Less common symbols

Alternative diagramming conventions

Crow's Feet

See also

Proprietary ER diagramming tools

References

External links

Connection

An entity represents a discrete object. Entities can be thought of as nouns. Examples: a computer, an employee, a song, a mathematical theorem. Entities are represented as rectangles.
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. Relationships are represented as diamonds, connected by lines to each of the entities in the relationship.
The model's linguistic aspect described above is utilized in the database query language ERROL.
Entities and relationships can both have attributes. Examples: an employee entity might have a social security number attribute (in the US); the ''proved'' relationship may have a ''date'' attribute. Attributes are represented as ellipses connected to their owning entity sets by a line.
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.
Lines are drawn between entity sets and the relationship sets they are involved in. If all entities in an entity set must participate in the relationship set, a thick or double line is drawn. This is called a participation constraint. If each entity of the entity set can participate in at most one relationship in the relationship set, an arrow is drawn from the entity set to the relationship set. This is called a key constraint. To indicate that each entity in the entity set is involved in exactly one relationship, a thick arrow is drawn.
Associative entity is used to solve the problem of two entities with a many-to-many relationship [1].
Unary Relationships - a unary relationship is a relationship between the rows of a single table.

Less common symbols

Some notions in ER modeling are used less often; many of them are avoided by modelers as a matter of principle.
Entities may be either ''strong'' or ''weak''.

★ A normal entity as described above is alternatively called a ''strong entity'', in that it can be uniquely defined by its attributes alone.

★ A ''weak entity'' is an entity that isn't uniquely identified by its own attributes, and therefore includes one or more of its relationships into its primary key. It is indicated by drawing the entity's rectangle, the diamonds of the identifying relationships, and the connections to them, in bold or with double lines. For example, in a work tracking database, if a task is co-identified by the person to whom the task is assigned, and ''person'' is an entity, then ''task'' is a weak entity.
Attributes in an ER model may be further described as multi-valued, composite, or derived.

★ A ''multi-valued attribute'', illustrated with a double-line ellipse, may have more than one value for at least one instance of its entity. For example, a piece of software (where the entity is ''application'') may have the multivalued attribute "platform" because instances of that application may run on more than one platform.

★ A ''composite attribute'' may itself contain two or more attributes and is indicated as having contributing attributes of its own. For example, addresses can be composite attributes, composed of attributes such as street address, city, and so forth.

★ A ''derived attribute'' is one whose value is entirely determined by other information in the database; it is indicated by a dashed ellipse. For example, if we have an employee database with an employee entity along with an age attribute, the age attribute would be derived from a birth date attribute.
Sometimes two entities are more specific subtypes of a more general type of entity. For example, programmers and marketers might both be types of employees at a software company. To indicate this, a triangle with "ISA" on the inside is drawn. The superclass is connected to the point on top and the two (or more) subclasses are connected to the base.
A relation and all its participating entity sets can be treated as a single entity set for the purpose of taking part in another relation through aggregation, indicated by drawing a dotted rectangle around all aggregated entities and relationships.

Alternative diagramming conventions

Crow's Feet

The "Crow's Feet" notation (named for the resemblance of one of its symbols to the forward digits of a bird's claw) represents relationships with connecting lines between entities, and symbols at the ends of those lines to represent the cardinality of the relationship.
Three symbols are used to represent cardinality:

★ the ''ring'' represents "zero"

★ the ''dash'' represents "one"

★ the ''crow's foot'' represents "more" or "many"
These symbols are used in combination to represent the four types of cardinality that an entity may have in a relationship.

★ ''ring'' and ''dash'' → 'zero or one'

★ ''dash'' and ''dash'' → 'exactly one'

★ ''ring'' and ''crow's foot'' → 'zero or more'

★ ''dash'' and ''crow's foot'' → 'one or more'
You can see an example of the crow's feet notation in the diagram to the right.
In the diagram, the following facts are detailed:

★ An Artist can perform 'zero or more' Songs

★ A Song is performed by 'exactly one' Artist
This notation is gaining acceptance through common usage in Oracle texts, and in tools such as Visio and PowerDesigner, with the following benefits:

★ Clarity in identifying the many, or child, side of the relationship, using the crow's foot.

★ Concise notation for identifying mandatory relationship, using a perpendicular bar, or an optional relation, using an open circle.

See also

★ Data model

★ Data structure diagram

★ Object Role Modeling (''ORM'')

★ Unified Modeling Language (''UML'')

★ Value range structure diagrams

★ Natural language Information Analysis Method (''NIAM'')

★ Business rules (''BR'')

Proprietary ER diagramming tools

★ CA ERwin Data Modeler: ERD tool, ability to generate HTML reports.

★ ConceptDraw: cross platform software for creating ER Diagrams

★ DB Visual ARCHITECT: Support UML Class Diagram and ERD

★ ER/Studio: robust, easy-to use ER modeling tool from Embarcadero.

★ Microsoft Visio: The Enterprise Architect version supports generating and reverse engineering databases

★ ModelRight: innovative and complete physical modeling tool.

★ OmniGraffle: diagramming software for Mac OS

★ Oracle Designer: Oracle's CASE tool for designing an information system and generating it

★ PowerDesigner: modeling suite from Sybase which includes Data Architect for constructing or reverse engineering conceptual, logical and physical models with many of the leading RDBMS brands.

★ Rational Rose: Old software designed for producing UML diagrams. Notably easy to use which has aided its longevity

★ SILVERRUN ModelSphere: supporting conceptual, logical and physical data modeling including interfaces for multiple target systems.

★ SmartDraw: point and click drawing method combined with many templates creates professional diagrams.
== Free software ER diagramming tools ==

★ DBDesigner: designer for MySQL databases that uses ERMs.

★ DBDesigner-Fork: a fork of DBDesigner to make it work with other databases such as PostgreSQL.

★ Dia: program to draw many kinds of diagram, including ERDs.

★ Ferret (software): ERM tool distributed with Debian and Ubuntu.

★ Kivio: flowcharting program that supports ER Diagrams.

★ MySQL Workbench: tool for graphically creating schemas or reverse engineering schemas [Alpha Software], works with many database engines.

★ Open System Architect: ER Diagram modeller, apparently very good, but the last version is as old as 2005.

★ POWER
★ ARCHITECT : ER Diagram modeller in Java, Modelling and forward engineering for several databases, both free software and proprietary.

References

★ The Entity-Relationship Model - Toward a Unified View of Data, , Peter P., Chen, ACM Transactions on Database Systems, 1976
This paper is one of the most cited papers in the computer field. It was selected as one of the most influential papers in computer science in a recent survey of over 1,000 computer science professors. The citation is listed, for example, in DBLP: http://dblp.uni-trier.de/ [2]

External links

★ An Entity Relationship Diagram Example Demonstrates the crow's feet notation by way of an example.

★ Peter Chen home page at Louisiana State University http://bit.csc.lsu.edu/~chen/chen.html

★
★ Origins of ER model pioneering

★
★ English, Chinese and ER diagrams (more deepened analysis of Chinese language)

★
★ The Entity-Relationship Model--Toward a Unified View of Data

★ Case study: E-R diagram for Acme Fashion Supplies by Mark H. Ridley

★ IDEF1X

★ Notes: Logical Data Structures (LDSs) - Getting started - by - Tony Drewry

★ Introduction to Data Modeling