Entity Relationship Diagram

1
Entity Relationship Diagram

• Farrokh Alemi Ph.D.
• Francesco Loaiza, Ph.D. J.D.
• Vikas Arya

2
Objective

• How to construct an Entity Relationship (ER)
  Diagram 
• An example
• A dialogue between a student and the instructor

3
 I Don't Understand!

• Can you give me an example?

4
Purpose of ER Diagrams

• To describe the structure of the database

5
ER diagram?

• What is it?

6
Definition of ER Diagrams

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

7
Definition of Entity

• Anything about which we want to keep persistent
  data

8
From Logical to Physical

• Entity
• Attributes
• Values

• Table
• Fields
• Data

9
Jargon?

• Why is it important?

10
Design of Large Databases

• ER diagrams make it possible to have large
  databases

11
Logically missing data?

• What do you mean?

12
Logically Missing Data

• When users have to leave a field as blank because
  it is not appropriate for the record
• For example, if the patient's visit was about
  asthma, and we record data on hypertension

13
Duplication

• wastes effort

14
Redundant Data

• In every visit you would need all information
  about the patient (e.g. contact information,
  insurance information, etc.) 
• This leads to redundant patient data on each
  record

15
Multiple Tables Reduce Inefficiencies

• ER diagram leads to specification of multiple
  tables

16
Recognizing Entities

• Use case and description of database functions

17
Electronic Medical Record

• An EMR is used by a provider of care to record
  information about the patient's visit so that the
  treatment can be coordinated over time with other
  providers. 
• It is also used to bill the patient about
  treatment they have received."

18
Looking for Entities

• Examine statements about uses of EMR database

19
I See Two Entities

• An EMR is used by a provider of care to record
  information about the patient's visit so that the
  treatment can be coordinated over time with other
  providers. 
• It is also used to bill the patient about
  treatment they have received."

20
More Entities

• An EMR is used by a provider of care to record
  information about the patient's visit so that the
  treatment can be coordinated over time with other
  providers. 
• It is also used to bill the patient about
  treatment they have received."

21
Diagnosis is about Patients?
Diagnosis
Treatment
Patient
Other facts
22
Depends on More Than Patients

• No.  Diagnosis is not a stable fact about the
  patient. It shows the condition of the patient
  at a point in time, during the visit

23
Treatment?

• Is treatment a fact about the patient?

24
Treatment Entity
Charge
Risk
Treatment
Description
Code
25
Primary Key

• All the facts in the table are about the primary
  key no other field
• All facts in the table should be unique for a
  given primary key.

26
Key Organizing Concept

• Primary key is what tables are organized around

27
Facts Belong to Primary Keys

• If a fact can belong to the primary key and
  nothing else, then it belongs to the entity 
• Otherwise it belongs to a different entity
• Consider address 
• Consider diagnosis 

28
Not Black White

• Address does not belong to the patient either as
  it changes over time

29
Design Choices

• Number of Tables Entities
• For our example

30
Art Science

• Dos and Donts

31
Patient Attributes?

• What fields are needed?

32
Social Security Number

• Avoid it to improve privacy

33
Primary Keys for Patient Entity

• Combination of fields
• Auto-number

34
Patient Entity Attributes

• Contact information
• Demographic data
• Address

35
Atomic Facts not Collections of Facts

• Address is a collection of facts

36
Patient Attributes Revised
37
Provider table?

• What attributes should be included in the
  Provider table?

38
Provider Attributes
39
Treatment Entity?

• What attributes should be included?

40
Treatment Attributes
41
Visit Table

• Many records
• Few fields

42
Visit Attributes
43
Foreign Keys

• Primary keys of another table
• Included to link to other tables

44
Components of ER Diagram

• ER Diagram Shows each entity (their attributes)
  and the relationship between the entities

45
Setting Relationships

• In Access you can do this by creating the tables
  and then connecting the tables to each other

46
What If

• What if information in one table is inadvertently
  deleted.  Then we loose the meaning of
  information in other tables. 

47
Inferential Integrity

• Cascaded deletion of all related records

48
How?

• How do you set inferential integrity in Access?

49
Cascaded Updates

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

50
Types of Joins

• Matching to missing information

51
Join Examples?

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

52
Non-clinical Providers

• Know if there are providers not taking care of
  any patients. 
• If we stay with our match of foreign and primary
  key all cases with no match will be eliminated
  and we will not be able to see if there is a
  provider with no patient. 

53
Relationships in Words?

• Is there a way of putting words to the
  relationship between two tables

54
Relationships in Words

• The words of course are implied in the field
  names. 
• If the foreign key is ID of the son, then it
  implies that it links the father to the son. 
• Some ER diagrams allow the specification of the
  relationships in words. 

55
One to One Joins

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

56
One to Many Joins

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

57
One Patient Many Visits

• Yes that makes sense

58
Many to Many Joins

• Suppose we want to allow a patient to live at two
  different addresses and two different patients
  (mother and a child) to live at same address. 
• Problems Primary keys need to be unique. 
• One way to solve this problem is to introduce a
  junction table 

59
Example of Junctions?

• Could you layout the example in more detail?

60
Patient Address Junction Table
61
Advantage of Junctions

• We can have primary keys in patient and address
  table that are unique but have them listed in
  multiple ways in the junction table 

62
Patient Patient Junction
63
Allows Self Relationships

• This is a clever way of keeping information about
  primary keys in same table

64
Take Home Lessons

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

65
Take Home Lessons

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

66
Take Home Lessons

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

67
Take Home Lessons

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

68
Take Home Lessons

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

69
Take Home Lessons

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