Entity-Relationship Diagram

Use ER diagrams to illustrate relationships between database entities

Entity-Relationship Diagram

An entity-relationship diagram is a specialized graphic that illustrates the relationships between entities in a database. ER diagrams use symbols to represent three types of information: entities (or concepts), relationships, and attributes.

About Entity-Relationship Diagrams

In industry standard ER diagrams, rectangles or squares represent entities, which are tables that hold specific information in a database. Diamonds represent relationships, which are the interactions between the entities. Ovals represent attributes, which is data that describes an entity. 

Although entity-relationship diagrams may look complicated, these diagrams help knowledgeable users understand database structures at a high level without accompanying details.

Database designers use ER diagrams to model the relationships between database entities in a clear format. Many software packages have automated methods to generate ER diagrams from existing databases.

Entity-Relationship Diagram Example

Consider the example of a database that contains information about the residents of a city. The ER diagram shown in the image above contains two entities in rectangles: Person and City. A single "Lives In" relationship in a diamond ties the two together. Each person lives in only one city, but each city can house many people. In the example diagram, attributes shown in ovals are the person's name and the city's population.

Nouns are used to describe entities and attributes; verbs are used to describe relationships.

Entities Are Objects That Are Tracked

Each item that is tracked in a database is an entity, and each entity is a table in a relational database. Usually, each entity in a database corresponds to a row.

If a database contains the names of people, its entity might be called Person. A table with the same name would exist in the database, and every person would be assigned to a row in the Person table.

Attributes Describe Entities

Databases contain information about each entity. This information is referred to as attributes. Attributes consist of information unique for each entity listed. In the Person example, attributes could include first name, last name, birthdate, and an identifying number.

Attributes provide detailed information about an entity. In a relational database, attributes are held in the fields where the information inside a record is held. A database is not limited to a specific number of attributes.

Relationships Hold the Data Together

The value of an entity-relationship diagram lies in its ability to display information about the relationships between entities. In the example, information about the city where each person lives can be tracked. Information about the city itself in a City entity with a relationship that ties together People and City information can also be tracked.

There are three types of relationships between entities:

  • One-to-One: Sometimes a single entity is associated with a single other entity. For example, each employee in a database has only one Social Security number, and the number is unique.
  • One-to-Many: A single entity may also be related to several other entities. For example, a company branch office and all the employees who work at that branch have a one-to-many relationship.
  • Many-to-Many: Multiple entities may be related to multiple other entities. For example, a company may manufacture three products, and has a sales staff that work to sell those products. Some of the sales staff may split their time between the products.

How to Create an ER Diagram

Before you design a relational database, it makes sense to create an ER diagram. You may have software that is equipped to handle this process. If not, do it the old-fashioned way — by putting pen (or pencil) to paper — or find a software program that can handle the charting requirements.

To create an ER diagram by hand:

  1. Create a rectangular box for each entity or concept relevant to your model.
  2. Draw lines to connect related entities to model the relationships. Label the relationships using verbs inside diamond shapes. 
  3. Identify the relevant attributes for each entity, beginning with the most important attributes and enter them in ovals in the diagram. Later, you can make the attribute lists more detailed.

When you finish, you have illustrated how different concepts relate to one another, and you have a conceptual foundation for the design of a relational database.