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. Entity Attributes Values Table Fields Data From Logical to Physical

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 patientabout treatment they have received."

20. More Entities • An EMR is used by a provider of care to record information about the patient'svisit so that the treatment can be coordinated over time with other providers.  • It is also used to bill the patientabout 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 Do’s and Don’ts

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. Date of Birth Race Insurance number Gender Insurance company State Patient Middle initials city Last name Street number Zip Street First name Patient Attributes Revised

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

38. Date of Birth Year of graduation Board certification Employee ID Telephone Title State Providers Middle initials city Last name Street number Zip Street First name Email Provider Attributes

39. Treatment Entity? What attributes should be included?

40. Cost Warning Typical medication Treatment Description Code Treatment Attributes

41. Visit Table • Many records • Few fields

42. Patient ID ID Date Treatment code Visit Diagnosis Provider ID 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