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
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