Entity Relationship Diagram (ERD)

The History of Entity Relationship Diagrams

Peter Chen developed ERDs in the 1970s and published his proposal for entity relationship modeling in a 1976 paper titled "The Entity-Relationship Model: Toward a Unified View of Data". Peter Chen was a computer scientist who worked on improving database design. His entity relationship model was a way to visualize a database that unified other existing models into a single understanding that removed ambiguities. Prior to ERDs, there were three data models for databases: the network model, the relational model, and the entity set model. Each had their own strengths and weaknesses, but none provided a complete view of the database. With an ERD, Chen could provide a unified framework for database modeling.

Peter Chen's work was greatly influenced by scientists and engineers who came before him, specifically Charles Bachman, who worked on visualizing databases in the 1960s and his data structure diagrams became known as Bachman diagrams.

Chen's entity relationship model is in many ways the foundation for later practices like Unified Modeling Language or UML in information systems.

In the 1980s, another computer scientist named James Martin, worked to further refine Chen's ER model and introduced what's known today as the IE notation. IE notation uses Crow's foot to express cardinality (one to many relationship) instead of Chen's notation to epxress the same.

IDEF1X Notation ERD - Relational Schema

IDEF1X stands for integrated definition for data modeling. This type of ER diagram will show entities connected to each other without relationship symbols. The attributes for any entity will be listed as part of a table inside each entity shape instead of separate symbols. Some also call this type of ER diagram a Relational Schema diagram.

Styles of Cardinality

Cardinality is the mathematical sense just means the number of values in a set. In relationship to databases and ERD, cardinality specifies how many instances of an entity relate to one instance of another entity. Ordinality is also closely linked to cardinality. While cardinality specifies the occurrences of a relationship, ordinality describes the relationship as either mandatory or optional. In other words, cardinality specifies the maximum number of relationships and ordinality specifies the absolute minimum number of relationships.

In other words, there will be multiple instances of each entity in a database. Cardinality allows you express the number of each entity that can be associated with another entity. For example, in an employee database, a manager will have multiple employee reports (in a one to many relationship), but an employee will only have one ID number (a one to one relationship). There are three main types of relationships in a database expressed using cardinality notation in an ER diagram.

• one-to-one
• one-to-many
• many-to-many

There are many notation styles that express cardinality.
Information Engineering Style, IE Notation or Crow's Foot Notation

Documenting an Existing Database from Data

There are two reasons to create a database diagram. You're either designing a new schema or you need to document your existing structure.

If you have an existing database you need to document, you can create a database diagram using data directly from your database. You can export your database structure as a CSV file (there are some scripts on how to this here), then have a program generate the ERD automatically.

This will be the most accurate portrait of your database and will require no drawing on your part.

Here's an example of a very basic database structure generated from data.

This type of ERD will be considered a physical data model and will contain all the technical details of your database using IDEF1X notation.

If you want to create a new plan, you can edit the generated diagram and collaborate with your team on what changes to make.

Learn more about generating ER diagrams from data automatically.

Entity Relationship Diagram Tutorial

Here are some best practice tips for constructing an ERD:

• Identify the entities. The first step in making an ERD is to identify all the entities you will use. An entity is nothing more than a rectangle with a description of something that your system stores information about. This could be a customer, a manager, an invoice, a schedule, etc. Draw a rectangle for each entity you can think of on your page. Keep them spaced out a bit.
  
• Identify relationships. Look at two entities, are they related? If so draw a solid line connecting the two entities and add a diamond between them with a brief description of how they are related.
• Add attributes. Any key attributes of entities should be added using oval-shaped symbols.
• Complete the diagram. Continue to connect the entities with lines, and adding diamonds to describe each relationship until all relationships have been described. Each of your entities may not have any relationships, some may have multiple relationships. That is okay.