Simple modeling exercise

The whole purpose of ER modeling is to create an accurate reflection of the real world in a database. The ER model doesn't actually give us a database description. It gives us an intermediate step from which it is easy to define a database. Let's look at an example. (You will see much more detail on these concepts in the rest of this chapter. For now just try to understand the overall process.)

Suppose you are presented with the following situation and are told to create a database for it:

Every department within our company is in only one division. Each division has more than one department in it. We don't have an upper limit on the number of departments that a division can have. For example, the New Business Development — the one managed by Mackenzie — and Higher Education departments are both in the Marketing division.

This is a fairly clear description of a situation. Many things are left unsaid that we understand about the situation. For example: each division has a name, and that name is unique within the company. For now, though, let's focus on the description as it is given.

The first step is to figure out the items of interest in this situation. (In this document you will come across Problems. You should attempt to perform these before continuing the reading. Simply reading the problem and then reading the answer is not sufficient — you should attempt the problem yourself before you continue reading. Understanding these problems are integral to understanding the text. The answer to the problem appears in the text immediately after the problem.)

Problem 1.1

What are the items of interest here?

Answer to Problem 1.1

Here's a formal, if somewhat ambiguous, definition.

An entity type is a collection of entities that share a common definition. An entity is a person, place, concept, or thing about which the business needs data.

So, Department is the name of one entity type. One instance of this entity type is the New Business Development department. The Marketing division is an instance of the Division entity type. Mackenzie is one instance of the Employee entity type. Instances of entity types are referred to as entities. Put more simply: You can touch an entity but an entity type is simply an idea. Person is an idea (entity type) while Scott, Nancy, Lindsey, and Mackenzie are touchable (entities). Entity types provide us with a means for making generalizations about entities. For example, instead of saying “Every department within our company is in only one division,” we could have gone down the list of all departments (that is, all entities with entity type Department) and asserted that each one is, indeed, in one division: > “The New Business Development department is in one division. The Higher Education department is in one division. …” And so on until we've noted that each is on only one division.

But we know more than the facts about each individual department being in one division. We know that all new departments will also be in just one division. And if there is a new division, it, too, will have departments that are unique to the division. So, instead of providing information in the form of statements about specific entities, we use a more powerful and concise format and provide information in the form of statements about relationships among entity types.

Thus, in ER modeling we look for relationships among entity types because it is easier and more concise to speak of relationships among general entity types rather than the touchable entities themselves.

Problem 1.2

Each of the following is either an entity type and/or an entity. If it's an entity type, then provide examples of entities; if it's an entity, define a possible entity type.

  1. A municipal bond from Detroit
  2. Ford
  3. Clothes
  4. Employee

Answer to Problem 1.2

Back to our example: we have identified three entity types and four entities. From the description we can assume that there are more entities for each entity type. Go back and read the situation description if you do not think this is immediately obvious.

From the description there is some sort of relationship between Department and Division and another sort of relationship between Department and Employee. The first relationship is one of containment: each division has one or more departments, but any one department can only be in one division.

Think about an NCAA conference such as the Big 10 (the bucket) having many teams (a bunch of balls). On the other hand, each team (one ball) can only be in one bucket (a conference). In this instance the bucket is the division and the balls are the departments.

The second relationship tells us that an employee has a certain relationship relative to a certain Department, namely, that the employee manages the department, and the department is managed by the employee. Determining the relationships among entity types is another important step in the process of ER modeling.

A relationship is an association between entity types.

Problem 1.3

What would you name these two relationships?

Answer to Problem 1.3

The defining characteristic of a relationship is that several entity types are involved. So something like a name or birth date would not be a relationship since only one entity is involved.

Now we have identified three entity types (Employee, Department, Division) and two relationships among these entity types (manages, contains). Now we can begin to represent the problem in the language of ER modeling.

ER models are usually represented graphically. The language we are going to use represents entity types as rectangles and relationships as lines between rectangles. Below is the representation of the situation we are working with.


Notice that the contains/is within relationship is drawn between the two entities that it is associated with. Similarly for the other relationship. This (simplified) ER model tells us that:

  • Division is related to department through a relationship, and this relationship tells us that a division contains a department and a department is within a division.
  • Departments are related to employees through a relationship, and this relationship tells us that a department is managed by an employee, and an employee is manager of a department.
  • Employees are not directly related to divisions.

Certainly we know more about the problem than this. Consider the relationship between divisions and departments. We know that divisions have multiple departments and departments can only be contained within one division. Or, for every one division there can be many departments. In the language of ER modeling this is called a 1:M (read: “one to many”) relationship.

Problem 1.4

What is the relationship between departments and managers? Fill in the blanks with either a one or a many: </P>

  1. For each department there can be, at most, _ managing employee(s).
  2. For each managing employee there can be, at most, _ department(s).

Answer to Problem 1.4

This information can also be represented in the ER diagram:


As you might have determined, the M part of a relationship is represented by putting an “crow's foot” next to the appropriate entity type in the relationship while the 1 part is represented by putting a short dash right next to the rectangle across the connecting relationship line. The ER diagram now represents much more information than it did above:

  • Any one division can contain many departments. Any one department can be contained in, at most, one division.
  • Any department can have, at most, one managing employee (or manager). Any manager can manage, at most, one department.

If you are a bit confused about all this 1:M and 1:1 stuff, never fear. You'll see a lot more clarifying detail later.

Several other questions remain about this situation that are not addressed in the description:

  • What is the minimum number of departments in a division?
  • Does a department have to be associated with a division?
  • Does a department have to have a manager?

These questions would have to be answered before we complete the ER model. And we will answer these questions later. For now we are going to stop this part of the analysis since the purpose of this example is to demonstrate what ER modeling is all about.

The ER modeling process is not something for which a set of steps can be given and then performed. The process contains almost as much art as science. Some steps are performed many times and many decisions are re-visited and revised. Given these conditions, a broad outline can be given:

  1. Determine what entity types are involved.
  2. Determine which entity types are related.
  3. Refine the definition of the relationships.

Understand now that there are several methods for representing ER models graphically. Some use diamonds for the relationship instead of putting words on the line. It's not really important how the entity types and relationships are represented; it's just important that they are represented.

Notice what has happened with this situation. Initially we had a text description of the problem. After analyzing it and making some necessary assumptions, we created an ER diagram that reflects the situation accurately and makes explicit the relationship among the entity types. This is why we perform ER modeling. We don't know any more than we used to about the problem — we just have made explicit what we do know. It is quite a straight-forward step to go from this ER model to an implemented database. Remember why we are doing all this: We are finding out all we need to know to create a database that will hold our data. And a well-defined database can be a very useful tool for solving business problems — and it is also in high demand by recruiters. You will learn how to perform the steps necessary to create such a database in later

Continue with more details on the ER modeling process.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-NonCommercial-ShareAlike 3.0 License