Entity-Relationship Diagram

Use ER diagrams to illustrate relationships between database entities

E-R Diagram
E-R Diagram.

An entity-relationship diagram is a specialized graphic form 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, boxes are used to represent entities. Diamonds are used to represent relationships, and ovals are used to represent attributes.

 

Although to the untrained eye, entity-relationship diagrams can look incredibly complicated, to knowledgeable viewers, they help business 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.

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: Person and City. A single "Lives In" relationship ties the two together. Each person lives in only one city, but each city can house many people. In the example diagram, attributes 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

Each item that you track in a database is an entity, and each entity is a table in the 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

Databases contain information about each entity. This information is termed "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

The value of an entity-relationship diagram lies in its ability to display information about the relationships between entities. In our 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.

How to Create an ER Diagram

  1. Create a box for each entity or concept relevant in 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 your attribute lists more detailed.

    When you are finished, you will have clearly illustrated how different business concepts relate to each other, and you will have a conceptual foundation for the design of a relational database to support your business.