You are here

Entity Relationship (E-R) Diagrams

19 January, 2016 - 12:35

We have now worked our way through all the pieces necessary to develop effective E-R diagrams. If you have successfully gotten a handle on each of the concepts explored so far in this chapter, you should be ready to start developing an integrated database model. Each of the data model segments that has been displayed in Figure 3.8 through 3.12 represents part of the evolution toward our diagram.

Review Question

What is the importance of an E-R diagram in facilitating event-driven systems and the integration of data between business processes?

 

At this point, it may be worth recalling our discussion earlier in this chapter on event-driven systems. One of the fundamental requirements for moving toward an event-driven model was the complete integration of data related to an organization’s various business events. We will use our data modeling techniques to demonstrate the integration of just two business processes: client billing and human resources.

The objective in the development of an E-R diagram is to integrate the data in a manner that allows business processes access to the data necessary for effective performance. Figure 3.13 presents the integrated data model for two business processes (i.e., the billing and human resources functions).

In a service organization such as a consultancy firm, billing of clients is heavily dependent on tracking the actual person-hours put into providing service to a client. To effectively execute the client billing process, our database needs to capture data related to all employees who contributed time to client service and, at the same time, be able to tie these employees’ efforts to a specific client. To further complicate matters, each employee may have a different billing rate for his or her time. To meet the needs of the billing process, we must be able to aggregate specific employees’ time put into providing service to a client, each employee’s billing rate, and sufficient information about the client to deliver the billing statement. Three entities are involved in the billing process: (1) EMPLOYEE, (2) CLIENT, and (3) WORK_COMPLETED. Note in Figure 3.13 that the three entities for the billing process are linked together on the right half of the diagram. The linkages allow us to pull together information related to the employees’ hours worked on a specific client, their billing rates, and the contact address for sending the billing statement.

media/image48.JPG
Figure 3.13 An Integrated Model for the Billing and Human Resources Processes 
 

Service organizations are also interested in tracking employee work activities through the human resources process. The human resources process includes (among other activities) both payroll activities and employee education and development. To complete the payroll process, information is needed regarding work hours completed, pay rate, vacation time, sick days, and training time. Payroll activity information can be drawn from four entities (i.e., RELEASE_TIME, TRAINING_COMPLETED, EMPLOYEE, and WORK_COMPLETED) in order to aggregate the information necessary to determine the employee’s pay rate, hours worked, hours in training, and hours used of allocated sick and vacation time.

Regarding employee education and development, the human resources department monitors training activities to assure the employee is receiving enough continuing education. At the same time, human resources also monitors the percentage of billable hours the employee has accumulated as a measure of job performance. To handle all of these activities, human resources needs to be able to link data related to completed work activities and training programs. This information can be drawn from three entities (i.e., EMPLOYEE, TRAINING_COMPLETED, and WORK_COMPLETED) to determine a given employee’s training coverage and percentage of billable hours.

Again, it is important to recognize that Figure 3.13 demonstrates only a small part of the overall enterprise model that would be required to integrate all information across an organization. The E-R diagram does effectively integrate the data required for the prescribed business processes. As other business processes are selected and integrated, the model will continue to expand through an explosion of entities and relationships.