Business Analysis Thought Leadership - Articles



BA ARTICLE ARCHIVE
» July 2014 (5)
» June 2014 (7)
» May 2014 (5)
» April 2014 (5)
» March 2014 (7)
» February 2014 (6)
» January 2014 (6)
» December 2013 (7)
» November 2013 (4)
» October 2013 (5)
» September 2013 (6)
» August 2013 (8)
» July 2013 (8)
» June 2013 (7)
» May 2013 (8)
» April 2013 (8)
» March 2013 (4)
» February 2013 (6)
» January 2013 (6)
» December 2012 (5)
» November 2012 (7)
» October 2012 (6)
» September 2012 (6)
» August 2012 (5)
» July 2012 (9)
» June 2012 (5)
» May 2012 (9)
» April 2012 (7)
» March 2012 (7)
» February 2012 (5)
» January 2012 (7)
» December 2011 (6)
» November 2011 (6)
» October 2011 (8)
» September 2011 (6)
» August 2011 (8)
» July 2011 (7)
» June 2011 (7)
» May 2011 (5)
» April 2011 (8)
» March 2011 (6)
» February 2011 (5)
» January 2011 (6)
» December 2010 (5)
» November 2010 (9)
» October 2010 (5)
» September 2010 (6)
» August 2010 (8)
» July 2010 (6)
» June 2010 (6)
» May 2010 (10)
» April 2010 (5)
» March 2010 (8)
» February 2010 (7)
» January 2010 (7)
» December 2009 (7)
» November 2009 (7)
» October 2009 (6)
» September 2009 (8)
» August 2009 (10)
» July 2009 (9)
» June 2009 (5)
» May 2009 (10)
» April 2009 (5)
» March 2009 (12)
» February 2009 (8)
» January 2009 (6)
» December 2008 (9)
» November 2008 (8)
» October 2008 (9)
» September 2008 (4)
» August 2008 (6)
» July 2008 (8)
» June 2008 (17)
» May 2008 (12)
» April 2008 (7)
» March 2008 (21)
» February 2008 (16)
» January 2008 (13)
» December 2007 (9)
» November 2007 (25)
» October 2007 (2)
» September 2007 (23)
» August 2007 (12)
» July 2007 (11)
» June 2007 (7)
» May 2007 (6)
» April 2007 (9)
» March 2007 (5)
» February 2007 (3)
» January 2007 (2)
Articles and White Papers
Monday, February 25, 2013
6495 Views 1 Comments 19 members voted Article Rating

This article describes the Entity Relationship Diagram that allows you to document the structure of a database in terms of persistent entities and the relationships between them.

Introduction

The Entity-Relationship Diagram (ERD) provides a way of graphically representing the logical relationships between entities in order to create a database schema to persist those entities.

The Entity-Relationship Model was first proposed by Peter Chen of Massachusetts Institute of Technology (MIT) in the 1970s. You will see later how Chen’s own notation for Entity-Relationship Diagrams produces diagrams that are more verbose and cluttered than the diagram presented herein which uses the alternative Crow’s Foot notation taken from SSADM and other design methods.

Diagram Elements

The diagram elements listed below and in the subsequent worked example are based on the Crow’s Foot Notation supported by the Microsoft Visio Database Model Diagram. You will read later how an Entity-Relationship Diagram may be drawn in other modeling tools using the alternative ‘Chen’ notation.

An persistent Entity, which usually maps to a table in the underlying database, is represented as a rectangle containing Attributes that map to table columns or fields. An Entity is something that has an independent existence and which can be uniquely identified.

 

 

 

 An Attribute is a named property of an Entity that stores some value. Attributes may be required (shown bold) and may be designated as primary (PK) or foreign (FK) keys in the underlying database table.

 

 

 

 

 

A Relationship line shows that one Entity is related to another one because the underlying database tables are linked via a primary key / foreign key agreement. Symbols at the ends of the relationship line are used to show the minimum and maximum numbers of each Entity that participate in the relationship, for example -H------O< meaning "one to zero or more". Where two Entities are connected by more than one relationship, a role name (e.g. source_account) can be used to distinguish the relationships.

The cardinality of relationships deserves some further explanation:

-H---- or ----H- (double line) means mandatory one
>I---- or ----I< (line and crow’s foot) means mandatory many.
-IO---- or ----OI- (O and line) means optional one.
>O---- or ----O< (O and crow’s foot) means optional many.

While the table of diagram elements is informative, the only way to truly appreciate the role of the Entity-Relationship Diagram is via a concrete worked example.

Worked Example

The figure below shows an Entity-Relationship Diagram that was drawn in Microsoft Visio as a Database Model Diagram using the Crow’s Foot Notation. Try to interpret the diagram yourself and then read the description that follows, keeping in mind that this diagram is not meant to be an exhaustive representation of the problem domain being modeled.

A BankAccount entity is uniquely identified by the account_number primary key attribute. It has a required balance attribute (because an account must have a balance) and an optional overdraft_limit (because not all accounts have an overdraft facility). The BankAccount has a customer_id foreign key attribute that identifies the account customer via the relationship to the Customer entity.

Two BankAccount entities can participate in a funds transfer represented by the FundsTransfer entity. A FundsTransfer must link to two accounts which take the roles of the source_account and destination_account, but any given account can participate in any number of funds transfers.

A FundsTranfer is uniquely identified by the transaction_id primary key attribute. It has a required amount attribute (because every funds transfer must be of an amount) and a required datetime attribute (because each transfer must be timestamped). The source_account_number and destination_account_number foreign key attributes identify the two BankAccounts of the two relationships.

A Customer is uniquely identified by the customer_id primary key attribute, and each customer has a required name. A customer has two pairs of attributes labeled as FK1 (foreign key 1) and FK2 (foreign key 2) that link to to possible Addresses for the customer as indicated by the relationships:

The home_address relationship which is identified by the FK1 pairing of home_house_number and home_postal_code is mandatory since every customer must have a home address on file.

The statement_address relationship which is identified by the FK2 pairing of statement_house_number and statement_postal_code is an optional additional address to which the account statements may be sent.

There is no unique identifier for an Address entity, but the combination of house_number and ,postal_code attributes uniquely identifies any address -- thus forming a composite key. This composite key PK1 is the counterpart of the foreign keys FK1 and FK2 in the Customer entity. Note that this use of composite keys has been contrived for illustration only and should not be treated as an example of good database design.

Tips and Tricks

Note that while the E-R diagram looks superficially similar to a UML Class Diagram, there are some important differences. For example: whereas a UML class representation contains no explicit linking attributes, the E-R identifies keys that link together instances of the various entity types. Furthermore, the E-R linkage is in what might be considered to be the opposite sense. Where a Customer object (in UML parlance) would hold an array of references to the customer’s BankAccounts, in an E-R diagram the same one-to-many linkage is effected by many BankAccount entity instances holding foreign keys to their Customers. Further discussion of this translation of concepts known as object relational mapping is beyond the scope of this article.

An Entity-Relationship diagram represents the logical (but not necessarily physical) structure of a relational database. Although not mandatory, it is usually a good idea to ensure that the logical structure is fully normalised so that redundant data is reduced or eliminated. Our worked example is only partially normalised. For example: the Address entity has been factored out as separate from the Customer entity (which is good) but the Customer entity includes a name attribute which should be separated out as (at least) two attributes for the first_name and last_name so as to be properly normalised.

The worked example in this article was devised using the Crow’s Foot Notation that is used in SSADM and some other design methods. As hinted throughout, the main alternative notation is the one that was devised by Peter Chen and which is illustrated (partially) below. While the Chen notation benefits from showing relationship roles and entity attributes separately and explicitly, the non-concise nature of the notation tends to produce cluttered diagrams.

One advantage that Chen’s notation has over the Crow’s Foot notation is the ability to adorn relationships (FundingSource and TransferDestination in the diagram above) with their own attributes, rather like association classes on a UML Class Diagram. When not using the Chen notation it is possible -- indeed necessary -- to devise additional entities to encapsulate the collection of attributes required on each relationship.

Summary

The Entity-Relationship diagram provides an industry-recognised way to model the logical structure of a relational database.

Related Data Modeling articles:

Putting Systems Analysis “Into Context” using the Context Diagram

Data Flow Diagram with Examples & Tips

Author : Tony Loton - Author & Self-Publisher

As a former IT consultant and consultancy practice manager, Tony has published many IT feature articles and books including the most recent "UML Software Design with Visual Studio 2010"

Rate this:

COMMENTS

Posted on Tuesday, July 16, 2013 8:41 AM by
soaringAlone
Hi,

Can you please explain the cardinal relationship between 'Customer' and 'Address'. I am finding it difficult to understand how

1. A particular home address can have zero to many Customers
2. A particular statement address can have zero to many Customers

Thanks much.
soaringAlone
Only registered users may post comments.

Do you twitter?: If you want short updates on what's going on in the BA world and at ModernAnalyst.com, simply follow us on Twitter: http://twitter.com/ModernAnalyst



Featured Digital Library Resources 

Copyright 2006-2014 by Modern Analyst Media LLC