Entity Relationship Diagrams
Entity Relationship diagrams (also known as E-R or ER diagrams) provide database designers with a valuable tool for modeling the relationships between database entities in a clear, precise format. This industry standard approach uses a series of block shapes and lines to describe the structure of a database in a manner understandable to all database professionals. Many database software packages, including Microsoft Access, SQL Server, and Oracle, provide automated methods to quickly create E-R diagrams from existing databases.
In this article, we provide an overview of E-R diagramming techniques to help you read, modify or create your own data models.
EntitiesIn a database model, each object that you wish to track in the database is known as an entity. Normally, each entity is stored in a database table and every instance of an entity corresponds to a row in that table. In an ER diagram, each entity is depicted as a rectangular box with the name of the entity contained within it.
For example, a database containing information about individual people would likely have an entity called Person. This would correspond to a table with the same name in the database and every person tracked in the database would be an instance of that Person entity and have a corresponding row in the Person table. Database designers creating an E-R diagram would draw the Person entity using a shape similar to this:
They would then repeat the process to create a rectangular box for each entity in the data model.
AttributesOf course, tracking entities alone is not sufficient to develop a data model. Databases contain information about each entity. This information is tracked in individual fields known as attributes, which normally correspond to the columns of a database table.
For example, the Person entity might have attributes corresponding to the person's first and last name, date of birth, and a unique person identifier. Each of these attributes is depicted in an E-R diagram as an oval, as shown in the figure below:
Notice that the text in the attribute ovals is formatted slightly differently. There are two text formatting features used to convey additional information about an entity's attributes. First, some fields are displayed in a boldface font. These are required fields, similar to the NOT NULL database constraint. Each instance of an entity must contain information in the FirstName, LastName and PersonID attributes. Also, one attribute is underlined, indicating that it serves as the database's primary key. In this example, PersonID serves as the primary key.
Using this format can be somewhat cumbersome in a diagram containing information about entities with many attributes. Therefore, many database designers prefer to use an alternate format that lists an entity's attributes in tabular form under the name of the entity. Here's a depiction of the Person entity in that format:
That covers the "Entity" part of Entity-Relationship diagrams. Now let's take a look at displaying data relationships.
Relationships and CardinalityThe power of the E-R diagram lies in its ability to accurately display information about the relationships between entities. For example, we might track information in our database about the city where each person lives. Information about the city itself is tracked within a City entity and a relationship is used to tie together Person and City instances.
Relationships are normally given names that are verbs, while attributes and entities are named after nouns. This convention makes it easy to express relationships. For example, if we name our Person/City relationship "Lives In", we can string them together to say "A person lives in a city." We express relationships in E-R diagrams by drawing a line between the related entities and placing a diamond shape that contains the relationship name in the middle of the line. Here's how our Person/City relationship would look: Notice that there are some additional shapes on the line. The double hashed line appearing just to the left of the City entity indicates that this part of the relationship has a cardinality of 1. On the other hand, the crow's foot symbol to the right of the Person entity indicates that this part of the relationship has a cardinality of "many". Stated more plainly, each person may live in only one city, while a city may contain many people.
Those are the basics of Entity-Relationship diagrams. You should now have the information you need to create basic diagrams for your databases.