How to Read an Entity Relationship Diagram

In order to write effective joins, you need to be familiar with your data. “That’s great,” I can hear you saying, “but how do I become familiar with it?” Fortunately, entity relationship diagrams graphically represent the relationships between your tables, and they go a long way toward helping you understand common keys.

Understanding ER diagrams is a lot easier than their pedantic-sounding name indicates. An entity is a table. A relationship defines how common keys are related, and may be one of three types:

•  A one-to-one relationship: One publisher (publishers) has one logo (pub_info).

•  A one-to-many relationship: One publisher (publishers) has many employees (employee).

•  A many-to-many relationship: In titleauthor, each title can have several rows if written by many authors. In sales, each title can have several rows if sold to different stores or on different dates. These tables share a many-to-many relationship.

These relationships may also be referred to as the cardinality of the data.

Cardinality expresses the type of relationship that exists between two tables.

This is the definition of cardinality used in this book, but cardinality may also refer to the uniqueness of data in a table. High cardinality means there are many unique values, and low cardinality means there are few unique values. Don’t be confused by these two somewhat different definitions.

Figure 7.4 shows the full entity relationship diagram for the pubs database.

Figure 7.4.  The full ER diagram for the pubs database, used in the examples.

Here’s an analysis of the entity relationship diagram in Figure 7.4. Each of the boxes is an entity, or table. Within the boxes, the primary key for a table is represented in two ways: by the little key symbol after the column name, and by the column’s separation from the rest of the columns by a horizontal line. Some ER diagrams use a key, some use the horizontal line or different shading, and a few use both. The authors table has a primary key on au_id. Some tables do not have primary keys, such as the roysched table.

When a table has foreign keys, this fact is indicated by the (FK) symbol after the column name. The titleauthor table has two foreign keys: au_id references authors, and title_id references titles. Some tables may contain columns that are foreign keys, but which also participate in defining the primary key for that table, such as the sales table. The primary key in sales is the combination of stor_id (a foreign key to stores), ord_num, and title_id (a foreign key to titles).

The heart and soul of the entity relationship diagram is in the graphical representation of relationships. These relationships are indicated by the lines drawn between the tables. The diagram here begins lines at the columns that define the relationship, so the authors and titleauthor tables have a line between the au_id column in each table. Remember, these lines between tables represent ideas, not a physical construct stored on disk. You will take these ideas and then represent the table relationships to the server using join syntax.

The type of relationship is defined by the symbols next to the lines. The authors to titleauthor relationship is a one-to-many relationship: one author writes many books, so the au_id column may appear many times in titleauthor. The author table has a 1 next to its end of the line, and titleauthor has an N next to its end of the line.

Some ER diagrams use this method for defining relationships. Others use the symbolic approach. Table 7.2 describes the types of relationships and the ways these relationships are indicated.

This ER diagram draws its lines between columns involved in the relationships, but many diagrams will simply draw the lines from anywhere on the entity box. The participating columns are then listed next to the lines. I’ve never been a big fan of this approach because, with many tables, this makes the diagram a bit crowded.

You might notice that not all of the relationships between tables are represented here. Titleauthor could join to sales on title_id, but that many-to-many doesn’t appear in the diagram. The reason for this is, as Zen as it sounds, the “directness” of the relationship.

The whole point of an ER diagram is to indicate the number and types of relationships between tables so that meaningful joins can be formulated. Although joins from titleauthor to sales are possible (and, in fact, I’ll show an example of that in a moment), they’re so rare and unusual that the diagram doesn’t indicate the relationship. Their relationship is one more of coincidence than design.