1 / 69

Entity Relationship Diagram

Entity Relationship Diagram. Farrokh Alemi Ph.D. Francesco Loaiza, Ph.D. J.D. Vikas Arya. Objective. How to construct an Entity Relationship (ER) Diagram  An example A dialogue between a student and the instructor .  I Don't Understand!. Can you give me an example?.

nydia
Télécharger la présentation

Entity Relationship Diagram

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related