690 likes | 946 Vues
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?.
E N D
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?
Purpose of ER Diagrams To describe the structure of the database
ER diagram? What is it?
Definition of ER Diagrams ER diagram is a list of entities and their relationship to each other
Definition of Entity Anything about which we want to keep persistent data
Entity Attributes Values Table Fields Data From Logical to Physical
Jargon? Why is it important?
Design of Large Databases ER diagrams make it possible to have large databases
Logically missing data? What do you mean?
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
Duplication wastes effort
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
Multiple Tables Reduce Inefficiencies ER diagram leads to specification of multiple tables
Recognizing Entities Use case and description of database functions
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."
Looking for Entities Examine statements about uses of EMR database
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."
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."
Diagnosis is about Patients? Diagnosis Treatment Patient Other facts
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
Treatment? Is treatment a fact about the patient?
Treatment Entity Charge Risk Treatment Description Code
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.
Key Organizing Concept Primary key is what tables are organized around
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
Not Black & White Address does not belong to the patient either as it changes over time
Design Choices • Number of Tables & Entities • For our example
Art & Science Do’s and Don’ts
Patient Attributes? What fields are needed?
Social Security Number Avoid it to improve privacy
Primary Keys for Patient Entity • Combination of fields • Auto-number
Patient Entity Attributes • Contact information • Demographic data • Address
Atomic Facts not Collections of Facts Address is a collection of facts
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
Provider table? What attributes should be included in the Provider table?
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
Treatment Entity? What attributes should be included?
Cost Warning Typical medication Treatment Description Code Treatment Attributes
Visit Table • Many records • Few fields
Patient ID ID Date Treatment code Visit Diagnosis Provider ID Visit Attributes
Foreign Keys • Primary keys of another table • Included to link to other tables
Components of ER Diagram ER Diagram Shows each entity (their attributes) and the relationship between the entities
Setting Relationships In Access you can do this by creating the tables and then connecting the tables to each other
What If What if information in one table is inadvertently deleted. Then we loose the meaning of information in other tables.
Inferential Integrity Cascaded deletion of all related records
How? How do you set inferential integrity in Access?
Cascaded Updates Updating one table will lead to the change for all other related tables
Types of Joins Matching to missing information