Understanding Entity Relationship Diagram (ERD)

There is A LOT of information in a database. Understanding how database elements interact with each other can be challenging to grasp. We need a visual way to understand how all the separate parts are related and work together. They call this visual representation Entity Relationship Diagram (ERD). Sometimes you may hear it referred to as Entity Relationship Model, data models, or even database schema.

You may wonder why I need to know this if I want to be an analyst or data scientist. It’s helpful to understand and read ERD because you can very quickly understand the different elements within the system and how they are related. It’s also handy in communicating with businesspeople.

ERD is easy to understand. No extensive training is needed. What I’ll cover should give you a reasonable understanding of it. Here are the topics we will look at:

• What is ERD
• Use of ERD
• History of ERDs
• ERD Key Components
• Cardinality and Ordinality
• Types of ERD
• Common ERD Symbols
• Styles of Cardinality

What is ERD

ERD is a graphical representation used in database design. It describes how entities relate to each other. Entities are the things we need to store data about, for example, customers, products, orders, departments etc.

Use of ERD

We’ve already covered that ERD is used to show the structure of databases. It helps businesses document existing databases and is a great reference tool for debugging and troubleshooting. Another use of ERD is for designing and modelling new databases. It is also helpful if any re-modelling of a database is required. ERD will aid in identifying any logic or design flaws before implementation.

History of ERDs

Peter Chen, a computer scientist, is credited to have developed ERD in the 1970s, with a focus on entities and relationships. His work was influenced by engineers and scientists who came before him, notably Charles Bachman. Bachman developed one of the early forms of ERDs.
If you want a more detailed ERD history, check out this article.

Key Components of ERD

There are three key components to an ERD:

Entities: these are components within a database that we store data about. It could be real or abstract. Entities are the tables in the relational database, such as a Customer table.

Attributes: these are descriptions of the properties of an entity. Did I hear you ask what that even means? Put simply, it refers to the column (field) names in a table and their characteristics, such as data type.

Relationships: I think this is rather self-explanatory. It denotes how various entities interact with each other- how they share information.

Cardinality and Ordinality

Cardinality

The concept of Cardinality and Ordinality is related to relationship.
Cardinality refers to the relationship type between two tables, specifically the numerical relationship between rows of one table and rows in the other table. Three common types of cardinalities are one-to-one (1:1), one-to-many (1:N), and many-to-many (M:N) relationships.

One-to-one (1:1): one row in a database table relates exactly to one row in a second table.

One-to-many (1:N): one row in a database table relates to many rows in a second table. For example, many students enrolled in a single course.

Many-to-many (M:N): many rows in one table are related to many rows in a second table. For example, a student can sign up for many classes, and a class can have many students signed up.

Ordinality

Ordinality is related to cardinality. It describes the relationship as either optional or mandatory. Another way to explain this is that it refers to the absolute minimum number of relationships.

Types of ERD

If you do a google image search of ERD, you’ll likely notice that there are two types of visual:

The first one looks like a flowchart and the second one has many rectangular boxes.
The first is the traditional ERD, and the latter is IDEF1X Notation ERD. Some people call the IDEF1X Notation a Relational Schema diagram.

Traditional ERD: The traditional Chen ERD illustrates the model like a flowchart using connected symbols for entities, attributes, and relationships.

IDEF1X Notation ERD: IDEF1X stands for an integrated definition for data modelling. This type of diagram shows entities in rectangles connected to entities without using the relationship symbol (diamond) in traditional ERD.

Common ERD Symbols

The type of symbols or illustrations used to display entities, attributes, and relationship differs depending on the kind of ERD. The image below illustrates this.

Styles of Cardinality

There are a few different notational styles to express cardinality. The Information Engineering (IE) style is the most frequently seen, commonly referred to as Crow’s Foot Notation.

That’s ERD in a nutshell. See, I told you ERD is easy to understand. Though I think one thing that might trip people over is the concept of cardinality and ordinality. It certainly did for me initially. If you’re still unsure what it means, refer to this Vertabelo page: What Is Cardinality in Data Modeling? The Theory and Practice of Database Cardinality.