Entity-Relationship Diagram

Use ER diagrams to illustrate relationships between database entities

Entity-Relationship Diagram

 L. McAlpine

An entity-relationship diagram is a specialized graphic that illustrates the relationships between entities in a database. ER diagrams often use symbols to represent three types of information: entities (or concepts), relationships, and attributes. In industry standard ER diagrams, rectangles or squares are used to represent entities, which are tables that hold specific information in a database. Diamonds are used to represent relationships, which are the interactions between the entities, and ovals are used to represent attributes, which is data that describes an entity. 

Although to the untrained eye, entity-relationship diagrams can look complicated, they help knowledgeable users understand database structures at a high level without accompanying details.

Database designers use ER diagrams for modeling 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 on the residents of a city. The ER diagram shown in the image accompanying this article 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.

In general, nouns are used to describe entities and attributes, while verbs are used to describe relationships.

Entities Are Objects That Are Tracked

Each item that you track 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 you have a database containing 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, and it consists 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. You are 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, you can track information about the city where each person lives. You could also track information about the city itself in a City entity with a relationship that ties together People and City information.

There are three types of relationships between entities: One-to-One, One-to-Many, and Many-to-Many.

  • One to One: Sometimes a single entity is associated with a single other entity. For example, each employee in a database would have only one Social Security number, and the number would be unique.
  • One to Many: A single entity may also be related with 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, your company may manufacture three products, and you have sales staff that work to sell those products. Some of the sales staff may split their time between among the products.

How to Create an ER Diagram

Before you design a relational database, it makes sense to create an ER diagram. Your software may be equipped to handle this process. If not, you can do it the old-fashioned way — by putting pen (or pencil) to paper — or by using a software program that can handle the charting requirements. 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.