Wednesday, June 6, 2007

An Entity Relationship Diagram Example

It seems like a dying art, but I still strongly feel that Entity Relationship Diagrams (ERD) should be the starting point of all software development projects. Since they are for me anyway, I wanted a place to refer colleagues to for how to read these diagrams, and an Entity Relationship Diagram Example seemed like a great place to start.

The Example: A Resource Management Application

Consider that we’re writing a resource management application. The first step to creating an ERD is always to identify the nouns (entities). In this case let’s start with:

  • Company
  • Employee
  • Project; and
  • Technology Project (which are a specific type of Project that perhaps require special fields like “number of entities”)

Here’s the Example Entity Relationship Diagram I’ll decipher piece by piece in this article (click to enlarge):

(note that I’m now using singular names since my somewhat controversial decision to switch to naming entities in the singular)

To read the notations of an Entity Relationship Diagram:

An Entity Relationship Diagram conveys a lot of information with a very concise notation. The important part to keep in mind is to limit what you’re reading using the following technique:

  1. Choose two entities (e.g. Company and Employee)
  2. Pick one that you’re interested in (e.g. how a single Company relates to employees)
  3. Read the notation on the second entity (e.g. the crow’s feet with the O above it next to the Employee entity).

The set of symbols consist of Crow’s feet (which Wikipedia describes as looking like the forward digits of a bird’s claw), O, and dash, but they can be combined in four distinct combinations. Here are the four combinations:

  • Zero through Many (crow's feet, O)
  • One through Many (crow's feet, dash)
  • One and Only One (dash, dash)
  • Zero or One (dash, O)

Zero through Many

If, as in the diagram above, the notation closest to the second entity is a crow’s feet with an O next to it, then the first entity can have zero, one, or many of the second entity. Consequently the diagram above would read: “A company can have zero, one, or many employees”.

This is the most common relationship type, and consequently many people ignore the O. While you can consider the O optional, I consider it a best practice to be explicit to differentiate it from the less common one through many relationship.

One through Many

If, as the next diagram shows, the notation closest to the second entity is a crow’s feet with a dash, then the first entity can have one through many of the second entity. More specifically it may not contain zero of the second entity. The example above would thus read (read bottom to top): “A Project can have one through many Employees working on it.”

This is an interesting combination because it can’t (and for various reasons probably shouldn’t if it could) be enforced by a database. Thus, you will only see these in logical, but not a physical, data models. It is still useful to distinguish, but your application will need to enforce the relationship in business rules.

One and Only One (onne)

If the notation closest to the second entity contains two dashes it indicates that the first entity can have one and only one of the second. More specifically it cannot have zero, and it cannot have more than one. The example would thus read: “An Employee can have one and only one Company.”

This combination is the most common after zero through many, and so frequently people consider the second dash optional. In fact, some ignore both dashes, but I would highly recommend at least using one for clarity so as not to confuse the notation with “I’ll fill in the relationship details later”.

Zero or One

A zero or one relationship is indicated by a dash and an O. It indicates that the first entity can have zero or one of the second, but not more than one. The relationship in the example above would thus read: “A Project can have zero or one Technology Project.”

The zero or one relationship is quite common and is frequently abbreviated with just an O (however it is most commonly seen in a many-to-many relationship rather than the one-to-one above, more on this later).

Relationship Types

Having examined the four types of notation, the discussion wouldn’t be complete without a quick overview of the three relationship types. These are:

  • One to Many
  • Many to Many
  • One to One

One-to-Many

A one-to-many (1N) is by far the most common relationship type. It consists of either a one through many or a zero through many notation on one side of a relationship and a one and only one or zero or one notation on the other. The relationship between Company and Employee in the example is a one-to-many relationship.

Many-to-Many

The next most common relationship is a many-to-many (NM). It consists of a zero through many or one through many on both sides of a relationship. This construct only exists in logical data models because databases can’t implement the relationship directly. Physical data models implement a many-to-many relationship by using an associative (or link or resolving) table via two one-to-many relationships.

The relationship between Employee and Project in the example is a many to many relationship. It would exist in logical and physical data models as follows:

One-to-One

Probably the least common and most misunderstood relationship is the one-to-one. It consists of a one and only one notation on one side of a relationship and a zero or one on the other. It warrants a discussion unto itself, but for now the Project to Technology Project relationship in the example is a one to one. Because these relationships are easy to mistake for traditional one-to-many relationships, I have taken to drawing a red dashed line around them. The red dashed line is not standard at all (although a colleague, Steve Dempsey uses a similar notation), but in my experience it can help eliminate confusion.

Conclusion

I hope you’ve found this a useful example for deciphering and verifying entity relationship diagrams. As always please add any comments, disagreements, thoughts or related resources.

---

8/2/2007 Update

BTW, I now reference this article in all of my data models. If you would like to as well here is the tiny url: http://tinyurl.com/yw6f6e

74 comments:

Anonymous said...

Thanks, I think it's a decent refresher course for those who have missed DB normalization class during good old college days :-)

Mike Kow

BigJim61 said...

Or for those of use whose college days were in the 70's and they didn't do ER diagrams on the punched cards we used! :)

Anonymous said...

Nice article. One thing bothers me though....couldn't one do exactly same first stage modelling with UML class diagrams? Or am I missing something?

Lee Richardson said...

"couldn't one do exactly same first stage modelling with UML class diagrams"

Yes, good point, you can accomplish the same thing with UML class diagrams.

I would argue, however, that an ERD is simpler and better designed for a non-technical audience. I always walk my customers through an ERD prior to moving from analysis to design, and they usually learn how to read it and spot mistakes within minutes.

However, I will research this more and will post on it next.

Scotty Z said...

Nice article Lee! I did notice however, that you cast the ER diagram in the light of a class hierarchy, when I find the most suitable use for an ER diagram is when doing database schema design. Also, aren't relationship-entities (such as in a many to many) drawn as a diamond (square turned 45 degrees)?

As pointed out by the above anonymous coward, class hierarchies are usually shown using UML notation, but UML is usually not used for database schemas (unless you begin "overloading" the UML notations to bind their database equivalents (e.g. - and + could denote "table metadata" vs. "real application data", respectively.)

The main difference between the two are that ERs are best for detailing structured data, whereas UML has notations for behaviors (methods) in addition to data, making them more suitable for modeling class hierarchies (who have both state and behavior).

It might also be good to detail the other "edge notations" (such as Chen notation, using 1:N, M:N, etc.) as those are still commonly in use in many places.

Here is the original paper from the ER OG Peter Chen. http://csc.lsu.edu/news/erd.pdf

...and here is Chen's web page: Peter Chen is teh Man (Credit where credit's due *wink*)

Lee Richardson said...

Scotty Z,

Thanks for your post man, I'll have to break my response up.

---

"you cast the ER diagram in the light of a class hierarchy"

I assume you're referring to my use of Project and TechnologyProject to demonstrate a one-to-one relationship. I would argue that this is the correct way to model for a database AND IS THE EXACT SAME THING as an inheritance structure in OO. i.e. 1-1 in your DB = inheritance in your code. This is simply good normalization.

---

"I find the most suitable use for an ER diagram is when doing database schema design"

While I agree that ERD is the starting point for your DB if you have one, I also use it as the starting point for my UML class diagrams, sequence diagrams, etc. And also just to get all stakeholders using the same language. But if you've done your job right, the class diagrams and ERD's should be nearly identical anyway.

---

"Also, aren't relationship-entities drawn as a diamond?"

I don't believe this is true when using the crow's feet notation. Regardless I've never seen anyone use the diamond, but I could see how it would be helpful.

---

"Credit where credit's due"

Thank you. I should have cited Peter Chen to begin with even though he doesn't use the crow's feet notation that I like so much.

EBHD said...
This comment has been removed by the author.
EBHD said...

Like they always say, "A picture is worth a thousands words." That's exactly what you get with ER diagrams and other modeling tools. Thanks for putting this article together. I came across it looking for a refresher on interpreting ER diagrams and this did the trick.

Thanks!!
Eric H
Miami of Ohio '94
Xavier '04

Anonymous said...

I thought one-to-many relationships were enforced through the use of foreign key constraints...

Ex:
Set EmployeeID in the PROJECT table to NOT NULL with a foreign key constraint pointing to the EMPLOYEE table.

Am I missing something?

Lee Richardson said...

"I thought one-to-many relationships were enforced through the use of foreign key constraints"

Sure, physically they are. But that's the beauty of ERD's. They abstract away implementation details and just focus on the important parts: Entities and their relationships. And in the process removes numerous arguments like surrogate vs. natural keys.

Sabih said...

Thanks dude!!

i am just 16 ans willing to make an online network! it will help me a lot

Anonymous said...

Many thanks, needed some info on how many-to-many relationships are actually implemented. Article helped alot.

Anonymous said...

Hi,
I'm accountancy student in Singapore(NTU). I'm preparing for my exam-Business IT. I find the examples very useful and easy to comprehend. Thanks =)

Regards

Lee Richardson said...

I'm so glad everyone has found it useful.

Prashanth BR said...

This is wonderful article, wherein the basic notations, and relationship descriptions are made clear. Wonderful work

Daniel said...

Hello,

I was reading this, and I was hoping to get an answer to:

What should you put in the Crow's style ERD when you have many-to-many relationships: the logical diagram or the physical diagram of it?

When you have a many-to-many relationship is good to just leave it specified in order to get the "big picture", but this "big picture" does not allow you to specify attributes on the resulting physical table as you would in the original entities of the diagram. So, if you are to draw a many-to-many relationship in an ERD following the Crow's style, how would you do it?

Thanks.

Francesco Levorato said...

Hi and thanks for the good article, it's hard to find a short text that makes a clear overview of this diagramming technique.
I see that there are no generalizations structure (like a Doctors is a Person), how would you graphically describe one?
Cheers!

Lee Richardson said...

Daniel,

The picture under "Many-to-Many" answers your question of "how would you do it?" As for whether to do logical or physical, if I do a facilitated session with an end user or customer I usually use a white board and draw a logical many to many. But when I implement that in Visio at the end of the day I always use physical since 1. Visio doesn't support logical many to many constructs and 2. Since I use Visio to forward engineer my database (i.e. generate DDL).

Lee Richardson said...

Francesco Levorato,

I would consider the "Project" to "Technology Project" to be what you describe as a "generalizations structure." In any event it is an "is a" type of relationship. Basically I tend to implement class inheritence with one-to-one relationships. I hope that answers your question.

Francesco Levorato said...

It does. Very grateful for the fast and to-the-point response. Keep up the good work! Cheers!

Anonymous said...

Good example. Easy to understand. Thanks a lot.

- A L Narayana

Daniel said...

Thanks for your answer!

What about representing hierarchies? Also, it seems that Visio does not support this feature. How can you workaround this?

Daniel

Lee Richardson said...

Hey Daniel,

I assume you mean self-referential relationships? Like an employee-manager relationship? Yea, I guess you're right the article didn't cover it, but it's pretty straight forward. In Visio just add a link from a table to itself. Usually Visio doesn't know what foreign key to use, so you have to add a new column like ManagerId and then click on the relationship and manually associate it with EmployeeId. Should be pretty straight forward.

Anonymous said...

Thank's dude.
Very clearly and simple.

mas selamat said...

good work dude.

republic polytechnic sucks!

Singanan said...

You have made the job very simple..

Thanks a lot

Anonymous said...

Thanks!! It is very clear.

Anonymous said...

Thanks!! It is very clear.
Phani Kumar Palika

Anonymous said...

very simple and easy to understand.. :)

Anonymous said...

hello richardson,
I need to make a job search application can you guide me for the databse design.

Leonard said...

Did I miss something? When you converted your many-to-many relationship from logical to physical, did not the the one-to-many constraint get converted to a zero-to-many constraint?

shiouming said...

This article seems been posted on beginning of 2007, but constantly receiving comments and feedbacks even until Sep 2008. That shows how useful is it to many people :).

BTW, I have the same doubt as leonard, does logical-physical conversion match each other?

Lee Richardson said...

Leonard & shiouming,

You're absolutely right that the many-to-many resolution lost information in it's implementation, namely that a project must have one employee. That is actually a point I mentioned earlier in the section "One Through Many". Specifically:

"This is an interesting combination because it can’t ... be enforced by a database."

So essentially this is a business rule that will need to be enforced by your application tier / UI.

Shahbaz said...

its really very helpful for me..

thankx

Shabaz_ch@hotmail.com

SyaFia said...

thanks for good explenation

Manny Bonet said...

ER Diagrams are a great first step; however, they do not allow the semantic richness needed to fully convey a conceptual schema (concepts, associations and constraints, both base and derived for structural and behavioral context).

I am happy to see that you place a high value on the conceptual schema since as you've pointed out it is the glue which can drive your persistence, object oriented framework and business rules (inference) layers.

For a full exposition see Conceptual Modeling of Information Systems, Antoni Olive or Information Modeling and Relational Databases, Terry Halpin. Two excellent texts which address these issues.

Admin said...

can we get a graph from er diagram using any tool or software....plx help me for the project that it requires

Anonymous said...

Thanks,
it is very usefull for beginers.

Saya said...

Thank you very much!

Sadly, I couldn:t find your writing while I was in my database class...

ColinA said...

A nice, simple reference - very helpful for those of us on crappy courses that try to explain things in the most complex and convoluted way possible!

Anonymous said...

Nicely done, thanks.

Anonymous said...

I was very confused with crow bar notation and search many articles, but this explanation is really very help full

Anonymous said...

I agree with all past postings that praise this article.

It seems to cover much of the more formal information processing lacking in the details of database writing.

As you say; a diagram covers a thousand words, but I was wondering how yourself might start to draw a relationship seeing as you've mentioned a whiteboard.

Would you start with a line and then draw crows feet on one side of an entity to signify a many to one relationship, say?

After this, adding mandatory conditions by empty or solid circles or by ticks?

On paper, how would one clearly cross out a subtle tick or circle if the customer disagrees with the interpretation for their buisness.


Sincerly Yours,

Eric H. said...

I'm currently working with a file based ERP but am looking forward to getting back to relational. This was a great refresher on "decoding" the links between entities. Good Job!

Anurag said...

I think this notation is utterly confusing. It puts too much intellectual burden on the reader.
A more better one would the notation devised by Michael Senko.

Marianne said...

Your post is a great help to me. I have assignment on my databse subject related to your post. thanks a bunch

Mynt said...

Clear and very nicely done,
Thanks

Anonymous said...

Thanks very much!
A clear explanation that covers the essence of ER modelling (other authors take 30+ pages to tell the same).

Gopi Nathan said...

Your article is good. Thanks. As an oldtimer who has used ER diagrams and Bachman notations, I feel The Crow's Foot notation itself is not good! It's too cryptic. ER diagram is an abstartion of the data relationships. The reference to Crow's foot is an added abstration on top of an abstration. The idea of diagrams is to convey the basic idea, not to convey the whole idea. The Crow's foot tried to put too much stuff into foot, O and dashes. That defeats the purpose. Every time one looks at such a diagram, one has to go back and refresh the notation itself.

I may come back to this later.

best regards
GN

easyb said...

Hi Lee,
I am a newbie into the world of databases. Could you help me given the scenario described below:

Consider the following set of requirements for a university database that is used to keep track of students' transcripts.

a. The university keeps track of each student's name, student number, social security number, current address and phone, permanent address and phone, birthdates, sex, class (freshman, sophomore, ... , graduate), major department, minor department (if any), and degree program (B.A., B.S., ... , Ph.D.). Some user applications need to refer to the city, state, and zip code of the student's permanent address and to the student's last name. Both social security number and student number have unique values for each student.

b. Each department is described by a name, department code, office number, office phone, and college. Both name and code have unique values for each department.

c. Each course has a course name, description, course number, number of semester hours, level, and offering department. The value of the course number is unique for each course.

d. Each section has an instructor, semester, year, course, and section number. The section number distinguishes sections of the same course that are taught during the same semester/year; its values are 1,2,3, ... , up to the number of sections
taught during each semester.

e. A grade report has a student, section, letter grade, and numeric grade (0, 1, 2, 3, or 4).

Question:
Design an ER schema for this application, and draw an ER diagram for that schema. Specify key attributes of each entity type, and structural constraints on each relationship type.

Note any unspecified requirements, and make appropriate assumptions to make the specification complete.

Anonymous said...

I have a question on the phsical diagram.why u put minimum value of 0 in the project-projectEmployee relationship? it is not clear for me.

Anonymous said...

Thanks brother, a very nice and simple example to understand.

Zubair Mahsher said...

Hey guys,

First of all I would like to thank Lee. He has pointed a very useful and important topic about ER diagram, I found what exactly I was looking for and searching. I am doing my bachelor in CS and I think I will need more help .

Anonymous said...

How should I interpret the relation between project and TechnologyProject? I mean, in daily practice, on what field they should be related to eachother? A column ProjectType in the entiry Project?

KokaLa said...

This really helped me. I'm a beginner or you can say 'just learned' about DBMS theory. Thanks for this example Lee.

Anonymous said...

Thanks a lot...
the clearer the better...
keep up!...

Anonymous said...

Thanks alot! Your explanation was clearer than my textbook! Keep it up! :)

Jeremy May said...

As one with 25 years + system analytical and data modelling experience (both business and public) I would emphasize the following regarding Mr Reston’s piece:
1) his usage of ‘can’ to mean either ‘may’ (see example 1 ‘zero thru many’ and example 4 ‘zero or one’) or ‘must’ (see example 2 ‘one thru many’ and example 3 ‘one only’) is misleading and bewildering, especially for those relatively new to data modelling
2) his usage of the following symbols is both redundant and contrary to accepted data modelling practice:
- the double dash to denote the mandatory existence of one entity occurrence involved in a relationship with one or more other entity occurrences; instead, the use of a single dash without the crows feet symbol (used to indicate multiple occurrences) next to it is the norm
- the use of a single dash to denote a single entity occurrence whether optional or mandatory; data modellers typically just leave out the crows feet symbol to do this and only use (see comment above) the single dash to indicate that the occurrence is mandatory, not if it’s optional
- the use of ‘O’ to denote only a zero entity occurrence; the normal practice is to use ‘O’ only to indicate whether the occurrence or occurrences of that entity are optionally (i.e. zero, one or more occurrences) related to another entity
3) his failure to mention that the presence of an associative or link table in many-to-many type relationships in a physical model is also common practice in the design of a logical model (where tables are called entities and where such entities may contain unique attributes)
4) his use of a broken rather than an unbroken line linking related entities in the examples could do with explanation – why?

Anonymous said...

Smashing! Damn! Your good! I wish that our professors are as good as you! Thanks a lot on this ERD!

-an intuitive student from Philippines

Scott Levy said...

Great post. Here’s a tutorial that shows how you can easily build an online database-driven web application with a parent-child table relationship, without codinghttp://blog.caspio.com/web-database/creating-one-to-many-relational-datapages/

Anonymous said...

Can any one please tell me how to construct database tables in RAD? Is is possible in the first place? Please let me know.

Abhishek N.

Anonymous said...

THANKS.NICE ARTICLE.

Shabab Haider Siddique said...

Nice post brother, can you please send me the reference on what base you written this? I am preparing a paper and I need to know the root. Please give me a reference.

Omar Tariq Jutt said...

Awesome! I wish you were my Software Engineering teacher :D

Anonymous said...

Ui would like to ask what do you call the word inside the ENTITY and RELATIONSHIP? thanks..:)

maestro said...

Nice article. A good refresher!

maestro said...

Thanks for the refresher!

Anonymous said...

That's a good refresher... it reminded me a lot.
thanks for that.

Karren Barlow said...

Great article! I have also found ER Diagram Symbols and Meaning (Entity Relationship Diagram) through lucidchart and found it to be very informative and helpful!

Anonymous said...

It should be noted that the physical implementation is wrong. The relationship from Project to ProjectEmployee should be 1:1,M not 1:0,M.

Anonymous said...

Thanks this info was really helpful! I used a website called Lucidchart to create my own er diagram and it was really easy to understand. If you use diagrams often you should check it out!

Macy Dalby said...

Thanks this info was really helpful! I used a website called Lucidchart to create my own erd diagram and it was really easy to understand. If you use diagrams often you should check it out!

Karren Barlow said...

Great article! I have also found a great website that teaches How to Draw ERD (Entity Relationship Diagram) and it is very helpful and easy to use! Check out Lucidchart!

Karren Barlow said...

304Very informative! I have also learned ER Diagram Symbols and Meaning (Entity Relationship Diagram) through Lucidchart and their site is very helpful and easy to use! Give it a try!