entity relationship diagram farrokh alemi ph.d. francesco loaiza, ph.d. j.d. vikas arya

Entity Relationship DiagramEntity Relationship Diagram

Farrokh Alemi Ph.D.Farrokh Alemi Ph.D.

Francesco Loaiza, Ph.D. J.D.Francesco Loaiza, Ph.D. J.D.

Vikas AryaVikas Arya

22

ObjectiveObjective

How to construct an Entity Relationship How to construct an Entity Relationship (ER) Diagram  (ER) Diagram  – An exampleAn example

A dialogue between a student and the A dialogue between a student and the instructor instructor

  I Don't Understand!I Don't Understand!

Can you give me an example?Can you give me an example?

Purpose of ER DiagramsPurpose of ER Diagrams

To describe the structure of To describe the structure of the databasethe database

ER diagram?ER diagram?

What is it?What is it?

Definition of ER DiagramsDefinition of ER Diagrams

ER diagram is a list of entities ER diagram is a list of entities and their relationship to each and their relationship to each

otherother

Definition of EntityDefinition of Entity

Anything about which we want Anything about which we want to keep persistent datato keep persistent data

88

From Logical to PhysicalFrom Logical to Physical

EntityEntity

AttributesAttributes

ValuesValues

TableTable

FieldsFields

DataData

Jargon?Jargon?

Why is it important?Why is it important?

Design of Large DatabasesDesign of Large Databases

ER diagrams make it possible ER diagrams make it possible to have large databasesto have large databases

Logically missing data?Logically missing data?

What do you mean? What do you mean?

1212

Logically Missing DataLogically Missing Data

When users have to leave a field as blank When users have to leave a field as blank because it is not appropriate for the recordbecause it is not appropriate for the record– For example, if the patient's visit was about For example, if the patient's visit was about

asthma, and we record data on hypertensionasthma, and we record data on hypertension

DuplicationDuplication

wastes effortwastes effort

1414

Redundant DataRedundant Data

In every visit you would need all In every visit you would need all information about the patient (e.g. contact information about the patient (e.g. contact information, insurance information, etc.)  information, insurance information, etc.) 

This leads to redundant patient data on This leads to redundant patient data on each recordeach record

Multiple Tables Reduce Multiple Tables Reduce InefficienciesInefficiencies

ER diagram leads to ER diagram leads to specification of multiple tablesspecification of multiple tables

Recognizing EntitiesRecognizing Entities

Use case and description of Use case and description of database functionsdatabase functions

1717

Electronic Medical RecordElectronic Medical Record

An EMR is used by a provider of care to An EMR is used by a provider of care to record information about the patient's visit record information about the patient's visit so that the treatment can be coordinated so that the treatment can be coordinated over time with other providers.  over time with other providers. 

It is also used to bill the patient about It is also used to bill the patient about treatment they have received." treatment they have received."

Looking for EntitiesLooking for Entities

Examine statements about Examine statements about uses of EMR databaseuses of EMR database

1919

I See Two EntitiesI See Two Entities

An EMR is used by a An EMR is used by a providerprovider of care to of care to record information about the record information about the patient'spatient's visit visit so that the treatment can be coordinated so that the treatment can be coordinated over time with other over time with other providersproviders.  . 

It is also used to bill the It is also used to bill the patientpatient about about treatment they have received."treatment they have received."

2020

More EntitiesMore Entities

An EMR is used by a An EMR is used by a providerprovider of care to of care to record information about the record information about the patient'spatient's

visitvisit so that the so that the treatmenttreatment can be can be coordinated over time with other coordinated over time with other providersproviders.  . 

It is also used to bill the It is also used to bill the patientpatient about about

treatment treatment they have received."they have received."

2121

Diagnosis

PatientTreatment

Diagnosis is about Patients?Diagnosis is about Patients?

Other facts

Depends on More Than PatientsDepends on More Than Patients

No.  Diagnosis is not a stable fact No.  Diagnosis is not a stable fact about the patient. It shows the about the patient. It shows the

condition of the patient at a point condition of the patient at a point in time, during the visitin time, during the visit

Treatment?Treatment?

Is treatment a fact about the Is treatment a fact about the patient?patient?

2424

Treatment EntityTreatment Entity

Code

Charge

Description

Treatment

Risk

2525

Primary KeyPrimary Key

All the facts in the table are about the All the facts in the table are about the primary key & no other fieldprimary key & no other field

All facts in the table should be unique for a All facts in the table should be unique for a given primary key.given primary key.

Key Organizing ConceptKey Organizing Concept

Primary key is what tables are Primary key is what tables are organized aroundorganized around

2727

Facts Belong to Primary KeysFacts Belong to Primary Keys

If a fact can belong to the primary key and If a fact can belong to the primary key and nothing else, then it belongs to the entity  nothing else, then it belongs to the entity 

Otherwise it belongs to a different entityOtherwise it belongs to a different entity

Consider address  Consider address 

Consider diagnosis Consider diagnosis 

Not Black & WhiteNot Black & White

Address does not belong to Address does not belong to the patient either as it the patient either as it

changes over time changes over time

2929

Design ChoicesDesign Choices

Number of Tables & EntitiesNumber of Tables & Entities

For our exampleFor our example

Art & ScienceArt & Science

Do’s and Don’tsDo’s and Don’ts

Patient Attributes?Patient Attributes?

What fields are needed?What fields are needed?

Social Security NumberSocial Security Number

Avoid it to improve privacy Avoid it to improve privacy

3333

Primary Keys for Patient EntityPrimary Keys for Patient Entity

Combination of fieldsCombination of fields

Auto-numberAuto-number

3434

Patient Entity AttributesPatient Entity Attributes

Contact informationContact information

Demographic dataDemographic data

AddressAddress

Atomic Facts not Collections Atomic Facts not Collections of Factsof Facts

Address is a collection of factsAddress is a collection of facts

3636

Patient Attributes RevisedPatient Attributes Revised

Street

Date of Birth

First name

Patient

Gender

Last name

Middle initials

RaceInsurance number

Insurance company

ZipStreet number

city

State

Provider table?Provider table?

What attributes should be What attributes should be included in the Provider table?included in the Provider table?

3838

Provider AttributesProvider Attributes

Street

Date of Birth

First name

Providers

Title

Last name

Middle initials

Year of graduation

Board certification

Telephone

ZipStreet number

city

State

Email

Employee ID

Treatment Entity?Treatment Entity?

What attributes should be What attributes should be included?included?

4040

Treatment AttributesTreatment Attributes

Code

Cost

Description

Treatment

Warning

Typical medication

4141

Visit TableVisit Table

Many recordsMany records

Few fieldsFew fields

4242

Visit AttributesVisit Attributes

Provider ID

Patient ID

Diagnosis

Visit

ID

Date Treatment code

4343

Foreign KeysForeign Keys

Primary keys of another tablePrimary keys of another table

Included to link to other tablesIncluded to link to other tables

Components of ER DiagramComponents of ER Diagram

ER Diagram Shows each entity ER Diagram Shows each entity (their attributes) and the (their attributes) and the

relationship between the entitiesrelationship between the entities

Setting RelationshipsSetting Relationships

In Access you can do this by In Access you can do this by creating the tables and then creating the tables and then

connecting the tables to each connecting the tables to each otherother

What IfWhat If

What if information in one table is What if information in one table is inadvertently deleted.  Then we inadvertently deleted.  Then we

loose the meaning of information loose the meaning of information in other tables. in other tables. 

Inferential IntegrityInferential Integrity

Cascaded deletion of all Cascaded deletion of all related recordsrelated records

How? How?

How do you set inferential How do you set inferential integrity in Access?integrity in Access?

Cascaded UpdatesCascaded Updates

Updating one table will lead to Updating one table will lead to the change for all other related the change for all other related

tablestables

Types of JoinsTypes of Joins

Matching to missing Matching to missing informationinformation

Join Examples?Join Examples?

Give me an example of when Give me an example of when you want to have this type of you want to have this type of

joins. joins.

5252

Non-clinical ProvidersNon-clinical Providers

Know if there are providers not taking care Know if there are providers not taking care of any patients.  of any patients. 

If we stay with our match of foreign and If we stay with our match of foreign and primary key all cases with no match will be primary key all cases with no match will be eliminated and we will not be able to see if eliminated and we will not be able to see if there is a provider with no patient.  there is a provider with no patient. 

Relationships in Words?Relationships in Words?

Is there a way of putting words Is there a way of putting words to the relationship between to the relationship between

two tables two tables

5454

Relationships in WordsRelationships in Words

The words of course are implied in the The words of course are implied in the field names.  field names.  – If the foreign key is ID of the son, then it If the foreign key is ID of the son, then it

implies that it links the father to the son.  implies that it links the father to the son. 

Some ER diagrams allow the specification Some ER diagrams allow the specification of the relationships in words. of the relationships in words. 

5555

One to One JoinsOne to One Joins

A one to one relationship requires a record A one to one relationship requires a record for each item in the other table.  for each item in the other table.  – For example, a word and its meaning in a For example, a word and its meaning in a

dictionary have a one to one relationship." dictionary have a one to one relationship."

5656

One to Many JoinsOne to Many Joins

Yes, a one to many relationship allows Yes, a one to many relationship allows one record to have multiple records in one record to have multiple records in another table linked to it.  another table linked to it.  – For example, the patient record may have For example, the patient record may have

multiple records in the visit table."  multiple records in the visit table." 

One Patient Many VisitsOne Patient Many Visits

Yes that makes sense Yes that makes sense

5858

Many to Many JoinsMany to Many Joins

Suppose we want to allow a patient to live Suppose we want to allow a patient to live at two different addresses and two at two different addresses and two different patients (mother and a child) to different patients (mother and a child) to live at same address.  live at same address. 

Problems: Primary keys need to be Problems: Primary keys need to be unique.  unique. 

One way to solve this problem is to One way to solve this problem is to introduce a junction table  introduce a junction table 

Example of Junctions?Example of Junctions?

Could you layout the example Could you layout the example in more detail? in more detail?

6060

Patient & Address Junction TablePatient & Address Junction Table

Lives at42003

Tenant52132

Landlord52341

RelationshipAddressIDPatientIDID

Lives at42003

Tenant52132

Landlord52341

RelationshipAddressIDPatientIDID

Advantage of JunctionsAdvantage of Junctions

We can have primary keys in We can have primary keys in patient and address table that are patient and address table that are

unique but have them listed in unique but have them listed in multiple ways in the junction table multiple ways in the junction table 

6262

Patient & Patient JunctionPatient & Patient Junction

ID Parent Child Relationship

1 234 213 Mother to

2 213 215 Mother to

3 200 215 Father of

Allows Self RelationshipsAllows Self Relationships

This is a clever way of This is a clever way of keeping information about keeping information about

primary keys in same table primary keys in same table

6464

Take Home LessonsTake Home Lessons

Definitions, including terms such as entity, Definitions, including terms such as entity, attribute and values.  attribute and values.  How entities can be identified.  How entities can be identified.  Specification of attributesSpecification of attributesRelationships among tables using foreign and Relationships among tables using foreign and primary keys.    primary keys.    Types of relationships and inferential integrity.  Types of relationships and inferential integrity.  Junction table can be used to represent many to Junction table can be used to represent many to many relations and relationships between the many relations and relationships between the table and itself table and itself

6565

Take Home LessonsTake Home Lessons

Definitions, including terms such as entity, Definitions, including terms such as entity, attribute and values. attribute and values.  How entities can be identified.  How entities can be identified.  Specification of attributesSpecification of attributesRelationships among tables using foreign and Relationships among tables using foreign and primary keys.    primary keys.    Types of relationships and inferential integrity.  Types of relationships and inferential integrity.  Junction table can be used to represent many to Junction table can be used to represent many to many relations and relationships between the many relations and relationships between the table and itself table and itself

6666

Take Home LessonsTake Home Lessons

Definitions, including terms such as entity, Definitions, including terms such as entity, attribute and values.  attribute and values.  How entities can be identified.How entities can be identified.    Specification of attributesSpecification of attributesRelationships among tables using foreign and Relationships among tables using foreign and primary keys.    primary keys.    Types of relationships and inferential integrity.  Types of relationships and inferential integrity.  Junction table can be used to represent many to Junction table can be used to represent many to many relations and relationships between the many relations and relationships between the table and itself table and itself

6767

Take Home LessonsTake Home Lessons

Definitions, including terms such as entity, Definitions, including terms such as entity, attribute and values.  attribute and values.  How entities can be identified.  How entities can be identified.  Specification of attributesSpecification of attributesRelationships among tables using foreign and Relationships among tables using foreign and primary keys.    primary keys.    Types of relationships and inferential integrity.  Types of relationships and inferential integrity.  Junction table can be used to represent many to Junction table can be used to represent many to many relations and relationships between the many relations and relationships between the table and itself table and itself

6868

Take Home LessonsTake Home Lessons

Definitions, including terms such as entity, Definitions, including terms such as entity, attribute and values.  attribute and values.  How entities can be identified.  How entities can be identified.  Specification of attributesSpecification of attributesRelationships among tables using foreign and Relationships among tables using foreign and primary keys.    primary keys.    Types of relationships and inferential integrity.  Types of relationships and inferential integrity.  Junction table can be used to represent many to Junction table can be used to represent many to many relations and relationships between the many relations and relationships between the table and itself table and itself

6969

Take Home LessonsTake Home Lessons

Definitions, including terms such as entity, Definitions, including terms such as entity, attribute and values.  attribute and values.  How entities can be identified.  How entities can be identified.  Specification of attributesSpecification of attributesRelationships among tables using foreign and Relationships among tables using foreign and primary keys.    primary keys.    Types of relationships and inferential integrity. Types of relationships and inferential integrity.  Junction table can be used to represent many to Junction table can be used to represent many to many relations and relationships between the many relations and relationships between the table and itself table and itself