- E-R diagrams capture meaning in your data.
- This high-level visual tool identifies core components of an enterprise
- Simple steps to follow to create the diagram.
Entity-Relationship (E-R) Modeling is one approach to visualize what story your data is trying to tell. This goal of this predecessor to object modeling (e.g. UML or CRC cards) is to give you a high-level, graphical view of the core components of an enterprisethe E-R diagram. An E-R diagram (sometimes called a Chen diagram, after its creator, Peter Chen) is a conceptual graph that captures meaning rather than implementation [1]. Once you have the diagram, you can convert it to a set of tables.
Entity Relationship Diagrams can quickly become very complex and can seem overwhelming to look at for the first time. However, the diagram is built block by block, based on elements which you define. If youre familiar with your data, and you know some basic E-R diagram symbols (more on that below), you can build an E-R diagram following a few simple rules.
The starting point for making an E-R diagram is to identify a few key items from users. Some key questions to ask [2]:
- What data needs to be kept?
- What queries do we need to ask?
- What business rules should we build in? For example, if the BANK ACCOUNT table has one column for owner, then you have committed to having one owner per bank account.
Next, you want to identify entities. An entity is something about which data is collected, stored or maintained [3]. Look for concrete objects in the problem domain that you can describe with a noun. For example, your core components for a telecommunications company might be WORKERS, CUSTOMERS, CELL PHONES and SERVICE PLANS. Youre looking to describe objects that are clearly distinguishable from other objects, like people, places, and things. Things can also be an abstraction like project names or departments.
List the attributes of each entity. An attribute is a characteristic of an entityusually a noun. Attributes describe parts of an entity and should be indivisible [ideally] single-valued. For example, TEACHER might have the attributes name, title and specialty. These tend to (more or less) become the fields. Although these should ideally be single (like social security number), they can be layered/composite (like city/state/zip). The domain tells us what values are permissible for an attribute. For example, (John, 123 Front Street, 9999).
Putting the above two components together, each ENTITY is described by a set of attributes. For example:
TEACHER (entity): Id, Name, Address, Parking Decal Number (attributes).
Build Relationships: A relationship is an association between two or more entities. What relationships are present in your data? Employee-supervisor? Teacher-student? Product-Consumer? Sometimes you may have to make a choice if entities and relationships arent clear cut. For example, customer orders could be an entity, or it could be a relationship.
From this basic information, you can create a simple E-R diagram. First, you need to be familiar with the meaning of a few basic shapes. Like flow charts, each shape in the E-R diagram has a specific meaning:
- Rectangle: Entity sets.
- Double rectangle: weak entity: one that exists only as the result of another entity. Examples dependents, invoices, transactions.
- Diamond: Relationship.
- Oval: Attribute.
- Double Oval: attributes that can have multiple values.
- Dashed oval: An attribute that can be derived from others. In other words, it can be calculated from other stored attributes in the database. For example, age can be derived from date of birth and the current date.
The above diagram shows the basic shapes. Start with a few core entities, and build out. As you should follow a few placement rules, like keeping attributes above entities, you’ll want to use software (even something simple like PowerPoint will work) to create one. A few tips for making the E-R diagram:
- Common practice is for entities to be described by a single noun.
- Attributes should be kept above the entity if possible.
- Connect relationship diamonds from the left and right points.
- Use straight connecting lines: do not use arrows.
- Underline key attributes–unique identifiers like social security number or driver license number.
My next posts cover:
References
Image of Basic E-R diagram: By Author
[1] Database Design http://jcsites.juniata.edu/faculty/rhodes/dbms/ermodel.htm
[2] Entity-Relationship modeling http://pld.cs.luc.edu/database/ER.html
[3] Drawing the Entity-Relationship Diagram https://www.csee.umbc.edu/portal/help/oracle8/java.815/a64686/05_de…
