On This Page
We've been using tables with columns and rows to represent our databases. That's fine when we
only have three dogs. But when a database has thousands or millions of records, we need a
better (smaller) way to show the relationships between the entities in each table. Such a tool
exists, and it's called an ERD (Entity Relationship Diagram). Below is an ERD of our database as
it is structured so far (1NF):
This ERD shows two tables: DOG and SKILL. Tables should be named in the singular (e.g.,
not "DOGS" and not "SKILLS," but your employer may have a different standard). The top box
is used to show the name of each table, and then the fields in the table are listed below
that. You will also notice a line drawn between the two tables, which shows a relationship.
On the left side of this line, there are two vertical slashes which means "must exist."
On the right side of this line is a circle and a three-pronged triton, which means "zero
or more." When you put it all together, it reads "One dog can have zero or more skills"
with the understanding that the dog must exist before it can have any skills. This
type of set up is known as a "one-to-many relationship."
One way to view an ERD is to see the entities as the "nouns" of your data:
person, places, and things. Attributes (columns or fields) are the "adjectives"
for those nouns, because they describe (a person's name, a person's age,
a person's address, etc.). The relationships between entities are the "verbs."
Top of page
Entity relationship diagrams come in various flavors, and both vendors and scholars
(Examples include Chen, Bachman, Martin, IE, Merise, Shlaer/Mellor, IDEF1X, and proprietary
CASE notations. PeopleSoft, for example uses EasyCASE to produce some of their ERD's.)
have invented their own notation systems. The notation used in the ERD above is a hybrid
cross between two very common notational systems (one for the logical, and one for the
physical). A logical ERD is a high level view that focuses on just the table relationships,
and doesn't include details about fields. On the other hand, the physical ERD includes
information on the fields in a table, and shows how they work as keys to connect one
table to the next. Here is how our example database would look in each diagram type:
| LOGICAL ERD | | PHYSICAL ERD |
|---|
 | |
 |
Top of page
When interpreting a logical ERD, a double vertical bars on the connecting line mean
that entity "must exist" before the relationship can be established. In all the examples
below, a dog must exist before it can have a skill, owner, funeral, etc. The symbols on
the end of the lines are generally a combination of one or two symbols. The first symbol
represents the minimum, and the second (or only symbol in some cases) represents the
maximum. The symbols are a circle (means "zero"), single vertical slash (means "one"),
and a three-pronged triton (means "many"). Here are some examples and how they are read:
 |
One DOG can have ZERO or MANY SKILLs |
 |
One DOG can have ONE or MANY OWNERs |
 |
One DOG can have TWO or MANY ANCESTORs
(because we didn't specify a minimum of
1 or 0 the minimum is an implied 2) |
 |
One DOG can have either ZERO or ONE FUNERAL
|
Try your hand at it now, with these examples:
- An employee can be single, but if married they can have only one spouse.
- An employee works in at least one cost center, but they could work in several.
- Our network connects many different computers together. Obviously, you need at
least two computers or it wouldn't be a network, would it?
- An employee may not be eligible to attend a service awards banquet, but if
they've been here long enough, they could have possibly attended several.
These statements could easily have come from a design session, where a database
designer interviewed a user. By asking specific questions he can identify the entities
to be tracked, and how those entities relate to each other. Those complex relationships
can be visualized in an ERD produced from such an interview.
Top of page
Logical ERD's are great for overviews and planning, but eventually you'll have to
deal with the details. The physical ERD gives that kind of detail. Specifically,
it shows the keys (index keys) that are used to link two tables in a relationship.
In a physical ERD, the shape of a box is significant. Boxes with square corners
are considered independent entities. Boxes with curved corners are dependent. You
will also note that a box is divided into two sections. Fields listed above the
horizontal divider are primary keys. Fields below the line are secondary keys. In a
dependent entity, you'll also see the suffix <FK> after a field name. This stands
for "Foreign Key." A foreign key is simply a link to a Primary Key in another table
(i.e., the link from a child record to a parent record). The primary key in a table
must be unique, but that key may be duplicated many times in the dependent (child)
table as a foreign key. For example, Nip is dog number 12. There will be
only one dog #12 in the DOG table, but there can be many records in the SKILL
table with dog #12 entries. That's because a single dog can have many skills.
Here are the meanings for the different connecting lines in a physical ERD:
 |
One to Zero or More |
 |
One to One or More |
 |
One to Exactly n (The letter "N" would be replaced with the appropriate digit.
For example, there are exactly three members in a triad.) | |
 |
One to Zero or One |
 |
Zero or One to One or More. The diamond represents the limit on the
origination side of the relationship. Also note the dashed line,
which specifically means that a relationship is non-identifying. This
would be the case of a lookup or validation table.
|
Top of page
This is the notation we'll use for this tutorial. It borrows the best from several
different notation systems, including the physical and logical notations already shown,
so that we can more accurately diagram a wider selection of relationships. In the section
"An Example ERD" (at the top of this page), I show the hybrid notation
in use: The boldly outlined box at the top of each column contains the table name
in all capital letters. The fields in each table are listed below the table name.
Relationship lines are drawn between the linking fields, and not just between
table names. Therefore, the ERD at the top of the page shows that DOG.DOG
must pre-exist, and may have zero or more occurrences in SKILL.DOG.
Read the lines below from left to right. The different symbols are: vertical slash
(one), circle (zero), three-pronged triton (many), double-vertical slash (must
pre-exist), and solid circles (mutually exclusive). The dashed lines have a
house-shaped arrow head (initialized from, or copied from) while the dotted
lines have a triangular arrow head (conditional). We use two different arrow head
shapes just to make it easier to distinguish dotted and dashed lines.
 |
One to One |
 |
One to One or More |
 |
One to Zero or One |
 |
One to Zero or More |
 |
One to Many (2 minimum, because neither 1 or 0 is "many") |
 |
One (must pre-exist) to Zero or More |
 |
source is copied to destination (non-identifying relationship) |
 |
Conditional relationship exists. E.g., an INVENTORY table has a COMPANY
field which is conditionally interpreted by COMPANY.TYPE (where
type might be "Distributor" or "Manufacturer"). We would draw a
relationship from COMPANY.COMPANY to INVENTORY.COMPANY, and a
conditional line from COMPANY.TYPE to INVENTORY.COMPANY. |
 |
One (must pre-exist) to Zero or More. However the vertical slash before
the line divides means you must choose one path: there is a "fork in the
road." The solid circles after the fork mean "mutually exclusive."
That is, take the top fork or take the bottom fork, but it can't be both
simultaneously. |
Top of page
Copyright © 1995 Craig Edward Given