Entity Relationship 3a4p22

ENMA 6010:

Entity Relationship Diagrams

Reference: Wikipedia – Entity Relationship Diagrams © 2010 – Mark Polczynski All rights reserved

ENMA 6010: Entity Relationship Diagrams

1

Goals of System Modeling: 1.

To focus on important system features while downplaying less important features,

2.

To that we understand the ’s environment,

3.

To discuss changes and corrections to the ’s requirements at low cost and with minimal risk,

4.

To that we have documented our understanding in a way that would allow others to construct/maintain the system.


2

Characteristics of Good Models: 1.

Graphical, with for detailed text descriptions: Picture is worth a thousand words  picture links to a thousand words.

2.

Top-down partitionable: Globe  Continents  Countries  etc…

3.

Minimally redundant: Make changes in just one place in the model.

4.

Transparent: Requires no expertise in model building to understand the model.


3

PFD vs. DFD vs. STD vs. ERD

ERD Start

STD turns on DFD, Causes PFD to execute Object A

Activity 1

Object C

Control Flow 1 Decision?

Object A

Function X

Object C

Object B

Object C

Activity 3

Activity 2

Object B

Activity 4

Objects C

ERD End


4

Modeling tools we will be using in this course:

Why do we need all these models?!

•

Context Diagram: Scope and boundaries of model.

•

Data Flow Diagram: Movement of data and materials – What?

•

State Transition Diagrams: Transitions between system wait states - When?

•

Process Flow Diagrams: Actions and decisions – How?

•

Entity Relationship Diagram: Information stores – Which?

•

Causal Loop Diagram: System cause and effect relationships.

•

IBIS Analysis: Description of “fuzzy” decisions.

•

Monte Carlo Simulation: Entity flow and resource utilization.


5

Different models reveal different aspects of the system we are modeling. Some are not as useful as others, but you may not know which is most useful when you start.


6

Where do we start?! An experienced system analyst approaching a new system is confronted with the same dilemma that a new system analyst faces: Where to start the analysis? This course introduces a number modeling and analysis tools and processes suitable for application to a wide range of systems. In of an optimal sequence of application of these approaches, the best answer is, of course: Apply them all at once!


7

Start With Genchi Genbutsu: •

Genchi genbutsu is a principal of lean thinking which roughly translates to: “Go and see for yourself”.

•

A good place to start your system analysis is to go out and talk to current or perspective system s to see “how things get done”.

•

As you interview people, you will be taking notes on what you hear and see.

•

In this course, we capture this info in a basic process flow diagram, to which we add detail as we apply other modeling techniques to the system.

•

The entity relationship diagram is another excellent tool for capturing this information.


8

Where should you start?! •

As you gain experience using different system modeling tools, you will, in fact, apply them all at the same time to capture a description of the system you are analyzing.

•

However, with experience, you may find it beneficial to entity relationship diagrams first.

•

This tool helps to systematically capture the information obtained through interviews in a standardized format.

•

The tool also reveals gaps and inconsistencies in your description of the system that you will want to correct early in your analysis.

•

But the ERD can be difficult to grasp, so we have delayed discussing it until after presenting some more (hopefully!) intuitive tools.


9

Simple Example – Hospital System Model Here is a portion of your notes taken while interviewing staff in a hospital: • Patients are assigned to a ward, and are treated by doctors. • Wards contain beds occupied by patients. • Nurses work in a ward, where they attend to patients. • Patients consume medications dispensed by nurses. This simplified narrative description of a hospital system is derived from http://www.umsl.edu/~sauter/analysis/er/er_intro.html We will use the description to illustrate the primary elements of entity relationship (ER) models. ENMA 6010: Entity Relationship Diagrams

10

Simple Example – Hospital System Model Here is a portion of your notes taken while interviewing staff in a hospital: • Patients are assigned to a ward, and are treated by doctors. • Wards contain beds occupied by patients. • Nurses work in a ward, where they attend to patients. • Patients consume medications dispensed by nurses. When creating an ER model, your primary focus is on on: •

“Things” (entities) in the system about which the system needs to store information, and…

•

What information needs to be stored for each entity. ENMA 6010: Entity Relationship Diagrams

11

Common types of entities about which systems need to store information: •

Tangible objects - MRI machine, lab report

•

Roles played - Patient, doctor

•

Organizational units - Ward, issions

•

Sites/locations - Factory, warehouse

•

Events - Purchase, payment

Relationship to DFD: - DFD models flow of data and materials, - ERD models stored information about data and materials.


12

PFD vs. DFD vs. STD vs. ERD

ERD Start

STD turns on DFD, Causes PFD to execute Object A

Activity 1

Object C

Control Flow 1 Decision?

Object A

Function X

Object C

Object B

Object C

Activity 3

Activity 2

Object B

Activity 4

Objects C

ERD End


13

Identifying Entities Generally speaking, entities are nouns, so lets find some nouns in the text description of the hospital… • Patients are assigned to a ward, and are treated by doctors. • Wards contain beds occupied by patients. • Nurses work in a ward, where they attend to patients. • Patients consume medications dispensed by nurses.

That was easy! What next?


14

Attributes: We said that entities are elements of our system for which we need to store information.

An entity’s attributes are the individual items of information that we need to store. For example, a patient in the hospital has: • Name • Birth date • Gender • Height • Weight Note that a patient also has a particular eye color, but this hospital system doesn’t need to store this information, so it is not an attribute of this entity. ENMA 6010: Entity Relationship Diagrams

15

Key Attributes •

Often, different entities will have the same types of attributes.

•

So, both patients and doctors have birth days, And a patient may even have the same birthday as their doctor.

•

But every entity must have one attribute that uniquely identifies the entity.

•

So, both patients and doctors may have social security numbers, But they never have the same social security number.

•

Thus, for this system, social security number is a key attribute. It can’t be that simple! What else do we need? ENMA 6010: Entity Relationship Diagrams

16

Relationships: Relationships describe how entities are related to one another, e.g. a doctor examines a patient, and a patient is examined by a doctor. To find relationships, just look for the verbs in your text description… • Patients are assigned to a ward, and are treated by doctors. • Wards contain beds occupied by patients. • Nurses work in a ward, where they attend to patients. • Patients consume medications dispensed by nurses.


17

Note: Arena simulation software also uses the concepts of entity and attribute. Also, Arena resources are a kind of entity in ER diagrams.

Don’t worry about this stuff for now, let’s just keep going… Entities are the things that flow through the process we are modeling. • They are the things that get worked on by the process. • Different types of entities can be flowing through the process. Attributes are characteristics that are common to all entities of the same type. • Entities may have many different characteristics, but attributes are just the ones that we care about in the model. • For now, our entities have just two attributes: arrival time and service time. Resources are the things that do the work on the entities. • Just as there can be multiple types of entities in a model, there can be multiple types of resources. • More than one resource or one type of resource may be needed to work on an entity. ENMA 6010: Entity Relationship Diagrams

18

So what we know so far is that entity relationship models consist of: •

Entities, which have attributes,

•

Relationships, which describe how entities are related to one another.

All we need to do now is to convert all this into a diagram.

So let’s do it!


19

You may have already seen a simple form of ER diagram…

Structured (Indented) Bill-Of-Materials: Assembly

Sub-Assembly 1

Component A

Sub-Assembly 2

Component B

Component C •

Assembly uses Sub-Assemblies 1, 2….

•

Sub-Assembly 1 uses Components A, B, C…

This diagram simply describes the “uses” relationship between assemblies, sub-assemblies, and components. ENMA 6010: Entity Relationship Diagrams

20

Here’s another simple form of ER diagram…

Org Chart: CEO

Vice President of Marketing

Northeast Regional Sales Manager

Vice President of Engineering

Southeast Regional Sales Manger

•

VPs report to CEO

•

Sales Managers report to VP of Marketing

The org chart shows the “report to” relationships among staff positions.


21

Here’s What Goes Into an ER Diagram: Entity (Object)

Relationship

Attribute

Noun, “things” often grouped into sixclasses: Roles, events, locations, physical items, devices, organizational units. May be termed “object”.

Verb (verb phrase) which describes how entities (objects) are related to one another, e.g. a doctor examines a patient, and a patient is examined by a doctor.

Noun, which characterizes an entity, e.g. a doctor has a name, and examines a patient with a name.


22

Here is the General Form of an ER Diagram:

Attribute 1.1

Entity 1

Relationship A

Entity 2

Attribute 1.2

Attribute 2.1

Attribute 2.2

ER Diagrams have Entities, Relationships, and Attributes.

Often times, Entities are termed Objects.


23

Here is an Example: Simple model of a patient-doctor relationship:

Name*

Condition

Name*

Patient

Examined By Examines

Doctor

Specialty

* Indicates a key attribute


24

Relationships are always bi-directional:

Name

Condition

Name

Patient is examined by a Doctor

Patient

Examined By Examines

Doctor

Specialty

Doctor examines a Patient

There is a simpler way to show this… ENMA 6010: Entity Relationship Diagrams

25

Simplified notation: Arrow shows how to read relationship

Name

Condition

Patient

Examined By

Name

Doctor

Specialty

Patient is examined by a Doctor The arrow simply shows how to read the relationship. The arrow has nothing to do with flow, since nothing “flows” in an ER diagram. ENMA 6010: Entity Relationship Diagrams

26

Simplified notation: Arrow shows how to read relationship

Name

Condition

Patient

Examines

Name

Doctor

Specialty

Doctor examines Patient This diagram is exactly equivalent to the diagram on the previous slide.


27

Here is an even more simplified ER notation that we will use:

Entity

Attributes

Doctor - Name* - Specialty

Relationship

Examines

Patient - Name* - Condition

Direction

* Indicates a key attribute


28

In Visio, go here for ER diagrams

It’s convenient to draw simplified ER diagrams in Visio


29

My favorite ER shapes…

Entity 1 Attribute 1 Attribute 2


Entity 2 Relationship

Attribute 1 Attribute 2

30

So now we know what the basic elements of an ER model are, And also the basic components of an ER diagram.

Let’s walk through the construction of a diagram using our hospital example…


31

Identifying Entities Noun, “things” often grouped into six classes: Roles, events, locations, physical items, devices, organizational units. May be termed “object”. • Patients are assigned to a ward, and are treated by doctors. • Wards contain beds occupied by patients. • Nurses work in a ward, where they attend to patients. • Patients consume medications dispensed by nurses.

To help keep track of things, let’s put this into a matrix format…


32

Entity-Entity Matrix – Step 1: Entities Patients

Doctors

Nurses

Wards

Beds

Meds

Patients Doctors Nurses Wards Beds Meds

All we need to do here is go back to the narrative, pull out all the entities, and put them on the axes of the matrix.


33

Identifying Relationships: Relationships describe how entities are related to one another. To find relationships, just look for the verbs in your text description…

• Patients are assigned to a ward, and are treated by doctors. • Wards contain beds occupied by patients. • Nurses work in a ward, where they attend to patients. • Patients consume medications dispensed by nurses.


34

Entity-Entity Matrix – Step 2: Relationships Patients Patients Doctors Nurses Wards Beds Meds

Treated by Attended by Assigned to Occupy Consume

Doctors

Nurses

Wards

Beds

Meds

Work in Contain Dispense

Now, we go back to the narrative, pull out all the verbs, and put them in the appropriate cells in the matrix.

Note: We are just showing the relationships in one direction, so only half the matrix needs to be filled in


35

Here’s our basic ER diagram in matrix form with entities and relationships: Patients Patients Doctors Nurses Wards Beds Meds


Doctors

Nurses

Wards

Beds

Meds


• Patients are assigned to a ward, and are treated by doctors. • Wards contain beds occupied by patients. • Nurses work in a ward, where they attend to patients.

• Patients consume medications dispensed by nurses.


36

Here’s our basic ER diagram in matrix form with entities and relationships: Patients Patients Doctors Nurses Wards Beds Meds


Doctors

Nurses

Wards

Beds

Meds


Hey! What’s with these blank cells?! Are Doctors related to Meds? Are Wards related to Meds? What about all the other blank cells?


37

Entity-Entity Matrix – Step 3: Validation Patients Patients Doctors Nurses Wards Beds Meds


Doctors

Nurses

Wards

Beds

Meds

Work in Perscribe

Dispense

Contain Stock

Missed Missed in in original original verbal description! description! Here are two relationships missed in the initial system description. An advantage of the matrix approach is that it allows thorough examination of all potential relationships. Now let’s build the diagram! ENMA 6010: Entity Relationship Diagrams

38

Building the Diagram: Just for fun, let’s pretend the doctor-patient relationship is the most important part of our system, so let’s start there…

Patient

Patients Patients Doctors Nurses Wards Beds Meds


Treated by

Doctors

Nurses

Doctor

Wards

Beds

Meds

Work in Perscribe

Dispense

Contain Stock


39

Building the Diagram: Nurses are pretty important, too, so let’s add them next…

Patient

Treated by

Doctor

Attended by Nurse Patients Patients Doctors Nurses Wards Beds Meds


Doctors

Nurses

Wards

Beds

Meds

Work in Perscribe

Dispense

Contain Stock


40

Now let’s throw in Wards… Assigned to

Patient Ward

Doctor

Attended by Works in


Treated by


Doctors

Nurse

Nurses

Wards

Beds

Meds

Work in Perscribe

Dispense

Contain Stock


41

Now add Beds…

Occupies

Bed

Contain Assigned to Patient Ward

Doctor



Treated by


Doctors

Nurse

Nurses

Wards

Beds

Meds

Work in Perscribe

Dispense

Contain Stock


42

Now add Medications… Bed

Occupies

Contains Assigned to Patient Ward


Stocks

Treated by

Doctor

Consumes

Nurse

Prescribes

Dispenses Medication

So, that completes our basic ER diagram for our hospital system analysis. ENMA 6010: Entity Relationship Diagrams

43

This is the basic idea behind ER diagrams, now let’s add some detail: •

Entity dictionary,

•

Cardinality,

•

Logical model vs. physical model,

•

ER diagram rules,

•

Many-to-many relationships.


44

Entity Dictionary: Earlier, we introduced a matrix notation to keep track of entities and relationships. You can see that showing attributes on an ER diagram can get quite cumbersome, so you may choose to show an entities attributes in a separate entity dictionary:

Doctor - S.S. No* - Name - Specialty

Hospital System Entity Dictionary Doctor S.S.No.* Name Specialty Patient S.S.No.* Name Condition ENMA 6010: Entity Relationship Diagrams

45

Cardinality

We need to show cardinality of relationships

Bed is occupied by only 1 patient at a time.

Bed

Patient occupies only 1 bed at a time.

Occupies

Contains Assigned to Patient Ward


Stocks

Treated by

Doctor

Consumes

Nurse

Prescribes

Dispenses Medication


46

Cardinality: In order to keep things clear and well-defined, a detailed ER diagram will include “cardinality” notation –

Entity

Exactly 1 (mandatory)

Entity

One or more (mandatory)

Entity

Zero or more (optional)

Entity

Zero or one (optional)

Here’s an example…


47

Example of Cardinality:

Department

Runs

Supervisor

• Supervisors run departments.

Staffed by

• Departments are staffed by employees. • Employees work on projects.

Employee

Works on

Project


Now lets add cardinality… 48

Here is an Example:

Department

Runs

Supervisor

• One and only one supervisor runs one and only one department. Staffed by

• Can’t have more than one supervisor running a department. • Supervisor can’t run more than one department.

Employee

Works on

Project


49

Here is an Example:

Department

Runs

Supervisor

• One or more departments are staffed by one or more employees. Staffed by Works in

Employee

• There is at least one person in each department.

• A person can work in more than one department, but must be assigned to at least 1 department.

Works on

Project


50

Here is an Example:

Department

Runs

Supervisor

• One or more employees work on zero or more projects. • Every project has at least one employee. Staffed by Works in

Employee

• Some employees don’t work on any projects.

Works on

Project


51

Logical Vs. Physical Models Here is a good time to distinguish between logical and physical models. •

The logical model shows what could be,

•

The physical model shows what actually is for a particular instance.

For ER diagrams, cardinality converts logical models into physical models.

What does that mean?!


52

Cardinality is Implementation Specific:

Department

Runs

Supervisor

• One and only one supervisor runs one and only one department. • Can’t have more than one supervisor running a department. • Supervisor can’t run more than one department. Here, the business rules of a particular organization dictate that a supervisor can not run more than one department, so the physical model must reflect this cardinality. But in a logical model, the logical relationship between supervisors and departments doesn’t specify how many supervisors run how many departments., so cardinality is not included. ENMA 6010: Entity Relationship Diagrams

53

Adding Cardinality to Hospital Model… Bed

Occupies

Treated by Patient

Contains

Doctor

Consumes

Assigned to

Prescribes

Attended by Ward

Works in

Nurse

Dispenses

Stocks


Medication

54

Cardinality Matrix: Showing cardinality on an ER diagram can get confusing, but, again, a matrix representation can help. Patients Patients Doctors Nurses Wards Beds Meds

1 or more 1 or more only 1 only 1 0 or more

Doctors Nurses 0 or more 0 or more

Wards Beds 0 or more 0 or 1 1 or more

only 1 0 or more 0 or more

only 1

Meds 0 or more 0 or more 0 or more 0 or more

1 or more 0 or more

Note that we must show both “ends” of the relationship in this matrix: • A bed can be occupied by 0 or 1 patients. • A patient occupies only 1 bed at a time.


55

Some Rules for ER Diagrams: • The entities in an ER diagram are things that you must store information on in order for the system to work. If you don’t need the entity to make the system work, get rid of it. Do you really need to include information about janitorial services in your hospital ER? • Entities can have many attributes, but you really only need to include the essential attributes relevant to entity-entity relationships. A patient’s driver’s license number is an attribute that probably doesn’t effect the doctor-examines-patient relationship. • Every entity must have a unique key attribute, that allows individual cases (instances) of the entity to be identified. If you can’t tell individual cases apart, then whatever it is that you have identified is not an entity.


56

Some Rules for ER Diagrams: • If you only have one specific case (instance) of an entity, you don’t need to include it in the ER model. For example, if the hospital has only one doctor, you do not need to include Doctor in the ER model. • If an entity has just one attribute, it may be that the entity can be included as an attribute of a different entity. Check all single-attribute entities for this possibility.


57

Some Rules for ER Diagrams: • When identifying entities, eliminate: • Synonyms (multiple names for the same entity) • Homonyms (same name used for different entities).

Synonym: Service Department calls them customers, and Help Desk calls them s, but they are the same people.

Homonym: “Market” can be a geographical area to Sales Department and a demographic designation to Marketing Department.


58

Some Rules for ER Diagrams: Attributes and Relationships A natural way to decompose ER diagrams is to look for groups of entities which are not related to each other…

Entity A

Entity B

Entity X

Entity C

Entity Y


Entity Z

59

Some Rules for ER Diagrams: Self-Referencing Relationships OK, well, what if a doctor examines themself? Wouldn’t the doctor and patient have the same key attribute? Here’s how to show self-referencing relationships:

Doctor - S.S. No.* - Name - Specialty

Examines


Patient - S.S. No.* - Name - Condition

60

Some Rules for ER Diagrams: Multiple Relationships Entities may have multiple relationships. Here is an example: Examines

Doctor

Patient Operates on

Many other rules for building good ER models exist. These can be found in the references and supplemental reading for this lecture. We will discuss one additional complication of ER diagrams…


61

Multiple Relationships Between Multiple Entities Buyer

Seller Negotiates price

Real Estate Agent negotiates price with Buyer and Seller

Real Estate Agent

Buyer’s Attorney

Negotiates

Seller’s Attorney

Real Estate Agent negotiates with Buyer’s and Seller’s Attorneys

Many other rules for building good ER models exist.

We will discuss one additional complication of ER diagrams… ENMA 6010: Entity Relationship Diagrams

62

Critical Aspect of ER Diagrams: Many-to-Many Relationships It is necessary to eliminate many-to-many relationships in the model. A prime example is seen in our patient/doctor relationship in our hospital ER model…

Doctor - Name - Specialty

Examines

Patient - Name - Condition

One or more doctors examine one or more patients. So, who examines who?


63

Resolving Many-To-Many Relationships •

The literature shows several ways to model many-to-many relationships, none of which is particularly elegant.

•

But let’s just reason this out how we could resolve this.

•

The problem is that if you just say many doctors examine many patients…

•

…you don’t know which doctor examines which patient.

•

One result of this is that the doctors don’t know which patient to invoice! (Or just invoice them all and let the insurance companies sort things out, since they’ll just deny coverage anyway). So, what must we add to the model to determine which doctor is examining which patient. ENMA 6010: Entity Relationship Diagrams

64

Resolving Many-To-Many Relationships: Old: Many doctors examine many patients Examine



Add an Examination entity

New: One or more doctors perform one and only one examination on a particular date and time. One and only one patient receives one and only one examination on the date/time. Doctor - Name* - Specialty

Perform

Examination - Date/Time* - Room

Receives


Patient - Name* - Condition 65

Resolving Many-To-Many Relationships: Examine



In essence, we turned a relationship into an entity by giving it attributes. By adding the key attribute of Date/Time to the relationship Examine, we now know exactly which doctors examine which patients.


Perform


Receives


Patient - Name* - Condition 66

Resolving Many-To-Many Relationships: Doctor - Name* - Specialty

Perform


Receivies


Note that doctors in the hospital probably do not randomly walk around the hospital examining any patient they run across (how could they bill for that?!). Rather, they do probably examine patients at some specific time, but this was not shown in our original ER diagram. The point is that the identification of a many-to-one relationship in our model alerted us to a shortcoming to the model, which we subsequently corrected.


67

So why all the fuss over resolving many-to-many relationships?

Well, if you have them in your model, this means one of two things: 1.

Your model isn’t complete and accurate, so you need to fix it.

2.

The system you are modeling isn’t well-designed.

Consider the following…


68

Many-To-Many Relationships Can Be Dangerous! Bed

Occupies

Treated by Patient

Contains

Doctor

Consumes

Assigned to

Prescribes

Attended by Ward

Works in

Nurse

Dispenses

What patients are consuming what medications?! Stocks


Medication

69

So What?! •

Your initial introduction to the system that you are modeling will probably consist of interviews with system s.

•

You need a way of systematically documenting this information and checking it for accuracy.

•

ER diagrams provide one good way to accomplish these objectives, so constitute a tool that can assist in your early modeling efforts.

•

That’s why you may choose to apply this tool first.


70

Entity Relationship 3a4p22

Overview 26281t

More details 6y5l6z

Related Documents 3h463d

Entity Relationship 3a4p22

Entity Relationship Diagram.pptx 626v4t

Entity Relationship Diagram 3v426c

Entity Relationship Diagram Instructions - En 24515z

Example Entity Relationship Diagram Pdf 4n4j4