Data Modeling (Entity Relationship Diagrams)

Welcome to Lecture on Data Modeling of the course HSCI 709 Healthcare Databases.  This lecture continues our exploration of the methodologies and procedures for creating a relational database.  This course has 3 components:

1. Abstract business process into database requirements

2. Model system requirements into a database, and

3. Use Standard Query Language to gain access to the data. 

This lecture belongs to the first component of the course, i.e. abstracting business processes into database requirements.  You should have completed all the exercises suggested in previous lectures.  If you have not, please review the materials in that lecture and work through the exercises, since this lecture builds on the concepts introduced there.  As before, you should ask a question when you review this lecture; we will try to answer it within a short interval. 

Entities

In the last lecture, you learned how to specify system requirements for a database in terms of a list of fields.  To specify a database, we need to decide about the tables within this list.  We can decide about the tables in a database by divide the list of fields into entities.  Then each entity will represent a table and each field in the table is an attribute of the entity.  According to Merriam Webster Dictionary an entity is something that has an independent, separate, or self-contained existence.  It could be just about anything:  a person, a disease, a time frame.   It can have an objective or conceptual reality.  It can be something alive or dead.  It can be a large concept such as organizations or a small concept such as a purchase.    In this lecture, we learn to divide system requirements into an underlying set of entities.   

An entity can be thought of as a class of data.  Each entity has a name, a definition, a type.  In addition, each entity has a set of attributes that describe the various characteristics of the entity.  Each attribute also has a name, a definition, a type and constraints.  The attribute types are text, numeric, binary and date types.  Field and attributes are different name for the same thing.  Entity and table are different name for the same thing.  In the context of relationship diagrams, the words entity and attributes are used.  In the context of physical database work, the words table and field are used.  

A list of fields is divided into entities by examining common features of the fields.  Each field is considered an attribute or characteristic of an entity.  The database designer thinks through a set of attributes that describe a particular entity.  For example, the three fields "Patient's first name," "Patient's last name" and "Patient's birthday" seem to suggest an entity called "Patient."  They are about the patient.  The two fields "Type of diagnosis" and "Name of diagnosis" seem to be about diagnosis and therefore they suggest an entity called "Diagnosis."  Review the entire list of fields and divide it into as many entities that makes sense to you.  Keep in mind that a list of entities should also make sense to others and therefore entities should be named in self apparent ways.  An entity called "Patient" is about patients and an entity called "Diagnosis" is about diagnoses.   The names imply what the entity is about.

Hernandez in his book Database Designers for Mere Mortals (pages 193-198) suggests the following rules for naming an entity:

1. "Create a name that accurately, clearly and unambiguously identifies the subject of a table.

2. Use the minimum number of words necessary to convey the subject of the table.

3. Do not use words that convey physical characteristics of the database.  Avoid using words such as 'File', 'Record,' and 'Table.'

4. Do not use acronyms and abbreviations.

5. Do not use proper names or other words that will unduly restrict the data that can be entered into the table.

6. Do not use a name that implicitly or explicitly identifies more than one concept (e.g. facility or branch entity).

7. Do not use the plural form of the name."

Always include a brief description of the entity.  The description should tell what is the definition of the entity and say why is it important to track this entity.  The definition should be sufficiently clear to help the reader set up expectations about what types of fields might be included under the entity.  For example, the entity "Patient" may be defined as: "The clients in the court diversion program who have mental illness."  The description may continue with stating why it is important to track these information:  "Information about the patients need to be kept in order to track if their court ordered treatment is working and has been followed."

The process of dividing fields into entities is difficult.  In some ways there is no right or wrong set of entities.  Two database designers may arrive at different set of entities for a health care business and they may be both right.  Design, after all, is an art form and not an exact science.  But at the same time there are a number of rules that should be followed that would make the database more useful and more efficient.  Some of these rules are the following:

1. No two entities should be about the same things.  For example, calling one entity "Patient" and another "Client" may create both confusion and inefficiency.  The inefficiency is obvious as same information has to be entered both places.  But this is not the worst of the problems.  Even more important than inefficiency is confusion in where to look for data.  Users of the database will be confused because they would not know if data on patients will be under the table called "Patient" or under the table called "Client."  
    

2. Do not assign the same field in two different entities.  This too will create inefficiency and confusion.  Take for example, the patient's name.  We may correctly decide that this information belongs to the entity "Patient."  We may also erroneously assume that the patient's name should also be kept in the entity "Diagnosis."  After all,  patients have diagnoses.  The question in your mind should always be where would future user's of the database look for the name of the patient, in the table "Patient" or in the table "Diagnosis."  Certainly these entities are related but that is not how we decide where to put various fields.  By putting it in two places, we are just creating confusion of where information is kept.  If not sure where fields belong, always choose the place that is most commonly thought of as the place for the data.  Meet and satisfy the intuitions of future users of the database. 
    

3. Make sure that time based events are separated from time independent fields.  For example, a patient's diagnoses may seem at first glance to belong to the entity "Patients."  But all fields in the "Patients" entity seem to be time invariant:  first name is not likely to change very often.   But a patients' diagnosis changes every visit.  Putting these two fields in the same entity will create a problem later when frequent changes needs to be made to diagnoses but not first name.  Instead create a separate entity, perhaps "Visits."  Visits will be time based and each visit may contain one or more diagnoses.   The field "Patient's diagnosis" needs to reside with an entity that is clearly time dependent, like "Visits."
    

4. If several entities share the same fields, see if you can partition the shared fields into a separate entity.  For example, in the design of a database for a mental health court, the mental health employees, the court staff, the patient, the lawyer and the judge all have a series of fields called "First name," "Last name,"  "Middle initial,"  "Birthday" and "City of Birth".  One way to make the design more efficient is to create a new entity called "Person" and keep these shared fields in it.  Then if an employee falls ill or is seen in the court, their name does not need to be entered several times once as an employee, next as a patient and last as a court client.  The fields are entered into the table "Person" and the role this person is playing may be entered into other tables. 
    

5. Include look-up entities as you proceed.  In a number of situations the possible range of a field is well defined ahead of time.  For example, the field "Observed gender" in the entity "Patient" may have three possibilities: male, female, and unknown.  These fixed responses are entered into a separate entity called "Gender" with two fields:  code and code description.    Then the table "Gender" is considered a look up table for entering the values in the field "Observed Gender" in the table "Patient."  Clearly in any database numerous look up tables may exist in order to simplify entering values for various fields.  It is important to keep track of these look up tables.

Once you have settled on a list of entities, review the fields within these entities for completeness.  Many entities suggest new fields that have not been thought about.  For example, a characteristic of a patient is his age.  This suggests that the field "Birthday" should be added to the entity "Patient," if it is not already there. Once a complete set of fields have been identified review them again to see if they fit the list of entities.  Share the list with organizational members, to see if the naming makes sense to them and if all relevant concepts have been captured.  Get as much input as you go along designing the database.

End the process with clear documentation.  Make sure that each entity has a name, a description, a statement of why it is important to track the entity, and a list of fields that belong to it. 

Entity Relationships

The last step in creating entity relationship diagrams is the specification of the relationships among the entities.   Just as every object in the real world has some kind of relationship to one or more objects so too the entities in a database are related to other entities.  The nature of relationships between entities is usually implied in the very definition of the entity.  Despite the obviousness of these relationships, it is important to review all entities and specify how they relate to each other.  There are at least three types of relationships possible:

1. One to one, where one entity corresponds to exactly another entity.  For example, a table about patient's death has a one to one relationship with the table "Person." 

2. One to many, where one instance of one entity can be repeatedly used by another.  For example the look up table Gender may be repeatedly used in the table "Patient."

3. Many to many where one instance of both entities can be repeatedly used by another.  For example, tables "Patient" and "Clinician" have many to many relationships as a clinician may have many patient and a patient may have many clinicians.

Sometimes, the relationship between two entities is not clear.  The most common cause is that a third entity is missing.  This often occurs when two entity have many to many relationship.  For example, the entity Patient and the entity Clinician have, as mentioned earlier,  many to many relationship.  It is difficult to show these relationships inside a database in a way that can easily be manipulated.  An alternative is to show a new table that links these two tables to each other and has one to many relationship to each of the tables.  For example, we can make a new table called Visit.  Within a visit a patients is diagnosed.  Both the patients and the clinicians identity are kept in the visit table.  The Visit table has one to many relationship with either patient or clinician table.  Sometimes, as we specify the relationships among entities, a new entity must be defined.

Linkages between entities are part of the business rules that databases should capture.  In our example, the business rule for the linkage between a Clinician and a Patient is that a clinician may have zero, one, or, more patients.  The business rule for the linkage between the Patient and the Clinician is that a patient may have one, or, more clinicians.  Note that these are the business rules that someone may have specified.  In a different information system someone could decide that a patient can only have one clinician at a time, or that the number of clinicians dealing with a patient must always be 3, or some other similar rule.  The important point is that entities can be linked to each other, and that the nature of the linkage is part of the business rules of the system.

To make tracking of information simpler, many modeling languages have standardized how entities and relationships are shown.  A common approach is to show entities as boxes with their names as their labels.  Inside the entity box the fields are listed.  In the Figure below two entities are shown: the Patient and the Clinician entities.

The figure also shows that the two entities are related to each other.  The graphical representation of data  linkages depends on the modeling language one uses.  In IDEF1X, a modeling language, one can represent many to many relationships by using a line with a large black dot at both ends.  This icon means that there is a many-to-many linkage between the entities connected.  If there is a one to many relationship the large dot is out in the side of entity with many instances. 

In Access, a database, The line shows the relationship between the two tables and the shared field shows the nature of the relationship. The arrow shows if the relationship is one to many, with the many side shown by the direction of the arrow. 

As with the specification of the entities discussed at the beginning of this lecture, the documentation of the relationships is part of the logical information model.  The format for documenting the linkages among entities includes the name of both entities, the verb phrase that describes the semantics of the linkage and the cardinality of the linkage (i.e. whether one to one, one to many or many to many).  The statement of the cardinality can be made plain English.  All relationships must be documented before proceeding to the physical design of the database.

Summary

In this lecture we have seen how one can take an information flow and conceptualize one or more entities out of it.  Data entities have a name, a type, a definition and attributes.  Any change in the system requirements must be faithfully and completely captured by the corresponding changes in the entity relationship diagram.  This may be either by adding or refining the definition of entities or attributes.  We reviewed the rules for how entities can be found from a list of fields/attributes.

What Do You Know?

Advanced learners like you, often need different ways of understanding a topic. Reading is just one way of understanding. Another way is through writing about what you have read.  The following assessment is designed to get you to think more about the concepts taught in this session.

1. Decide on a subset of fields that you want to include in your design of mental health court database.  Make a comprehensive list of the fields including their names and definitions and data types.  Make sure that at least 50 fields are included, if your use cases have not produced 50 fields, you can use some of the fields in the semester long project.  

2. Provide a comprehensive list of entities that will contain the list of fields you specified.  Document the entities by providing their names, their definitions, the importance of tracking the entity and the fields that are contained in the entity.

3. Provide the relationships among the entities and document the relationships by providing a verb phrase name for the entity, a brief description and the cardinality of the relationship.

If you decided to work on your own project, instead of the mental health court database, do the same for your project. Remeber that you need to have at least 50 fields in your database. Submit your responses as a word document attached to your instructor. Remember to follow rules of creating fields from the previous lecture  Here are examples of work submitted by other students:

Jesleen Papneja, Angela Simmons, Ebenexzer Ogoe and Leah Sigafoes provide an ER Diagrams for the mental health court.

To see what are the most common problems with this assignment, please see presnetation (avi version).

Presentations

To assist you in reviewing the material in this lecture, please see:

• Listen to lecture on Data Modeling .  See slides. See updated slides.

Narrated lectures require use of Flash. 

Frequently Asked Questions

Ask a question and we will answer it within the next 48 hours.  If you have no questions, please review the answer to the questions asked by others: 

Question: dear sir my question is that.draw ER diagram for the entities students,clasess,teachers,coursess  Answer: Dear Hamid, You need to provide additional details about your question so that I can answer it. If your question is how to draw an ER diagram for these 4 entities, the answer depends on what you want to do with it. You could have four tables, where the table class will also contain links to course, students and teacher table  This question was asked on 2/22/2007 3:53:55 AM and answered on 2/23/2007 9:53:29 AM .

Question: I have the entity ROYTE for a courier company.A specific ROUTE may be acting in Monday and in Friday.I must add an entity DAY in my ER diagram?don't want to MULTI-VALUED attributes.  Answer: If you are trying to maintain data on route and day of taking the route, you need two fields. If the route information is only a date, then you can discard it. But I assume that it is a map of sort or name of a specific method of getting to a place. Then you need them both, preferrably in the same table as both are about the same underlying concept  This question was asked on 2/13/2007 12:05:51 PM and answered on 2/23/2007 9:47:26 AM .

Question: Draw an ER Diagram for Library Information System by illustrating the diagram with attributes of the entity and relationship."""" ::Library Information System:: Punjab Public Library is one of the most famous libraries in Pakistan. This library is composed of books, periodic, and CDs located in different sections. Each book is described by a number, has title, authors, version, year, edition, subject area, and location. A book can be classified either as a consultation only item or as a loan item. The books that can be borrowed have an associated maximum period for loan. The loan period is different for each type of user of the library. The library database maintains record of the total number of copies for a certain book and the number of copies that are on loan. Periodic items are for consultation only. Each periodic is classified by a number and is described by title, volume, number, year, location, and related subject area. The CDs are also identified by a number. A CD can be of type music, computer game, or related to a periodic. Music and game CDs can be borrowed and have a title and associated singers/authors. The library database maintains record of the total number of copies for a certain CD. Periodic CDs are only for consultation and have a title. The library users include students, academics, university staff, and people from outside the university (interlibrary loan). Each user is unique identified by an ID code and name. They also have an address and telephone number. For each type of users there is maximum number of items that the user of this type can borrow for a certain period of time. Information about an item that was borrowed need to be known at any instance of time, including the date that the item was borrowed and the date that it is supposed to be returned. A fine of 2 Rs per day is charged for each day that a user delays on returning an item of the library. Information about fines is registered for each related user. The library contains its own staff member (librarian). Each librarian is described by an ID code, name, address, date of birth, sex, salary, function, and related working section. A librarian is allocated to a unique section. Answer: This is an excellent topic and appropriate for an ER project. I will post it on the web in the hope that someone would like to do this, but obviously we cannot do so as we focus on health care databases.  Answer: Again, we are receiving a lot of requests to draw an ER charts. This is really beyond the scope of this service and we cannot help you with these requests  This question was asked on 1/24/2007 12:47:36 AM and answered on 1/28/2007 12:25:29 PM .

Question: i wanted a ER diagram for stores management system.This is for the karnataka power corporation ltd., in India.  Answer: Unfortunately, our focus is on health care issues, but I will post your request to the web -- perhaps someone else can offer a solution  This question was asked on 1/22/2007 1:02:52 PM and answered on 1/28/2007 12:23:47 PM .

Question: How to create a conceptual data model of the data requirements for a company that specializes in IT training. The company has 30 instructors and can handle up to 100 trainees per training session. The company offers five advanced technology courses, each of which is taught by a teaching team of two or more instructors. Each instructor is assigned to a maximum of two teaching teams or may be assigned to do research. Each trainee undertakes one advanced technology course per training session. 1. Identify the main types for the company. 2. Identify the main relationship types and specify the multiplicity for each relationship. State any assumptions you make about the data. 3. Using your answers for (a) and (b), draw a single ER diagram to represent the data requirements for the company. Please help.  Answer: This is a great question. I would imagine that you would organize tables around each of the concepts you are metioning in your notes, e.g. instructor, class, trainee, etc.  This question was asked on 10/6/2006 11:51:53 AM and answered on 10/6/2006 12:18:17 PM .

Question: If we use person as an entity to hold all demographic information about users of the system, such as patients, social workers, etc, inorder not to repeat this information in each entity; do we still need a special ID for each user type, such as the social worker to relate him to other entities? or can we only use the person ID (social security number) and determine if he is a social worker by a role code or so?  Answer: I would think you can design a system where a table describes a person and another table describes the role of the person and you can track the information about who provided service by who played the particular role.  This question was asked on 9/15/2006 4:55:57 PM and answered on 9/15/2006 6:16:39 PM .

Question: would we keep any information about the offender's demographic or screening before being admitted to the program? So will the patient ID represent the patients who are admitted to the program or do we still need another ID, like FAST ID for the patients once ther are admitted to the program?  Answer: These are good questions and the answer depends on the purpose of the system and the design. However, make sure that each table has a primary key, where all the information in the table is about the key.  This question was asked on 9/15/2006 4:42:19 PM and answered on 9/15/2006 6:15:21 PM .

Question: No questions. The lecture was straight forward and succint. The visual diagrams helped me understand the lecture content even more.  Answer: Thanks   This question was asked on 9/11/2006 10:24:51 PM and answered on 9/12/2006 9:59:33 PM .

Question: A typical ER diagram for Patient,Health care Professionals,Hospital staff and pharmacies.  Answer: You can find a typical ER diagram for hospital operations (staff, pharmacy and more) by looking at the VistA ER diagrams. You can find these at ftp://ftp.va.gov/vista/Software/ERdiagrams/   This question was asked on 3/26/2006 4:38:18 PM and answered on 3/29/2006 8:00:02 AM .

Question: The company employee administrative database stores information about the employees,the department and the projects of a company. The following data have been identified in the requirements collection and analysis phase and they are to be represented int he enterprise. The company is organised into departments. Each department has a unique name, a unique number and a particular employee who manages the department. The database keeps track of the startdate when an employee began managing the department. A department controls a number of projects, each project has a unique name, unique number and a single location. The database also stores the number of work hours budgeted for each project. The databases stores each employees name, pin number , address, salary , sex , birthdate, date hired and date he/she terminates employment. An employee is assigned to one department but he/she may work on sveral projects, which are not necessarily controlled by the same department. The database also keeps tracks of the number of hours an employee already worked on a projectt and the direct superviser of each employee if he/she has one. (Note that an employee need not have and assigned supervisor). For insurance purposes, the database keeps track of the dependents of each employee . Each dependents name, sex , birthdate and relationship to the employee is recorded in the database. Expand on the development life cycle that is likely to be used here. Draw entity diagrams and map them to relations.   Answer: This is a good problem for students to try. Thanks for providing it.   This question was asked on 3/13/2006 7:48:36 AM and answered on 3/21/2006 1:37:15 PM .

Suggested Changes

In this section, you can review suggestions made by you and others regarding how to improve this lecture. 

Comment: I liked this lecture and the exercise.  This comment was left on 10/11/2009 4:58:32 PM.

Comment: Lecture and slides much easier to understand than reading.  This comment was left on 9/20/2009 8:11:43 PM.

Comment: The video lecture was much more efficient & understandable than the text. Good lecture.  This comment was left on 9/20/2009 2:36:16 PM.

Comment: For the most part the lecture and notes were clear and this portion of the course made more sense than the scenario/use case module.  This comment was left on 9/26/2008 1:01:18 PM.

Comment: I liked the lecture, it was very informative.  This comment was left on 9/16/2008 4:35:44 PM.

Comment: The lecture was clear.   This comment was left on 9/14/2006 7:38:23 PM.

Comment: Supplementing the lecture reading material with visual diagrams worked well.  This comment was left on 9/11/2006 10:23:36 PM.

Comment: Please give some more Detailed ER diagrams in your examples  This comment was left on 6/19/2006 12:57:40 AM.

Comment: Giving the pictures and exambles made it a lot easier to understand.  This comment was left on 2/18/2005 9:57:32 AM.

Comment: Need an explanation of Logical Data Types: String, Binary, Numeric, and Date/Time and when to use them. Information starting to get confusing to the novice.  This comment was left on 2/13/2005 11:22:59 AM.

More

• An excellent and thorough lecture on UML data classes. 

• UML class diagram guidelines, part 1.

• UML class diagram guidelines, part 2.  

• Arabic translation of the lecture and narrated lecture:
   

This page is part of the course on Healthcare Databases, the lecture on Data Modeling (ER Diagrams).  It was last edited on Tuesday November 27, 2007 by Farrokh Alemi, Ph.D.  © Copyright protected.  Updated by Janusz Wojtusiak, Ph.D.