HI520 Unit 2 Entity Relationship Diagram Example

Eight Entities, Nine Relationships, a Minimum and Maximum at Both Ends: A Unit 2 Entity Relationship Diagram for a Four-Site Pediatric Asthma Program

[Student Name]

Purdue University Global

HI520: Database Design and SQL

Unit 2 Entity Relationship Diagram

[Instructor Name]

[Date]

Calder Bay Pediatrics and its scenario are composite; the modeling sources cited are real.

Notation

The diagram uses Information Engineering notation, the crow's foot style, where the two symbols drawn at every line end give the fewest and the most instances allowed: zero or one for the first, one or many for the second. Entities are written in capitals with their attributes beneath. PK marks a primary key and FK a foreign key borrowed from another entity. Entity relationship modeling was proposed to describe real-world things and their associations independently of how they will be stored (Chen, 1976), and the diagram below follows that intent: it describes Calder Bay's asthma program, not a particular database product. Converting it into tables comes later, under the relational model, which stores each entity once and connects tables through shared key values (Codd, 1970).

Entities and Attributes

1. PATIENT: patient_id (PK), medical record number, first name, last name, birth date, sex, primary language, guardian name, guardian phone.

2. SITE: site_id (PK), site name, street address, city, phone.

3. PROVIDER: provider_id (PK), national provider identifier, name, credential, home site_id (FK).

4. ENCOUNTER: encounter_id (PK), patient_id (FK), site_id (FK), provider_id (FK), start time, end time, visit type, asthma control score where one was recorded.

5. DIAGNOSIS CODE: code (PK), description, code system.

6. ENCOUNTER DIAGNOSIS: encounter_id (PK, FK), code (PK, FK), sequence number.

7. TEST ORDER: order_id (PK), encounter_id (FK), ordering provider_id (FK), test type, order time, status.

8. TEST RESULT: result_id (PK), order_id (FK), performing site_id (FK), result time, result value, units, interpretation.

ENCOUNTER DIAGNOSIS exists to resolve the many-to-many link between encounters and codes. It carries a sequence number so the first-listed diagnosis can be told from the others, which matters for reporting asthma visits.

Relationships, Read Both Ways

1. PATIENT to ENCOUNTER. A patient has zero or many encounters; each encounter belongs to exactly one patient. Zero is allowed because a child can be registered before the first visit.

2. SITE to ENCOUNTER. A site hosts zero or many encounters; each encounter takes place at exactly one site. A new site may have no encounters on its first day.

3. PROVIDER to ENCOUNTER. A provider conducts zero or many encounters; each encounter has exactly one rendering provider.

4. ENCOUNTER to ENCOUNTER DIAGNOSIS. An encounter records one or many diagnoses; each encounter diagnosis belongs to exactly one encounter. The minimum of one reflects the practice's rule that every visit is coded, including the school form visit.

5. DIAGNOSIS CODE to ENCOUNTER DIAGNOSIS. A code appears on zero or many encounter diagnoses; each encounter diagnosis uses exactly one code.

6. ENCOUNTER to TEST ORDER. An encounter produces zero or many orders; each order comes from exactly one encounter.

7. PROVIDER to TEST ORDER. A provider places zero or many orders; each order has exactly one ordering provider, who may differ from the encounter's rendering provider when a physician co-signs a nurse practitioner's plan.

8. TEST ORDER to TEST RESULT. An order produces zero or many results; each result answers exactly one order. Zero covers orders not yet performed; many covers a spirometry test reported before and after a bronchodilator.

9. SITE to TEST RESULT. A site performs zero or many tests; each result was performed at zero or one site, zero because some results come from outside laboratories that are not Calder Bay sites.

The result points to its order, not to an encounter or a date, which is the single design choice that solves the problem of results arriving days after a visit.

Assumptions the Scenario Left Unsettled

1. A patient has one medical record number across all four sites. If the practice later merged with another group carrying its own numbers, PATIENT would need a separate identifier table.

2. Each encounter has one rendering provider. Team visits with two providers would require an encounter provider table.

3. Asthma control is recorded as a single score on the encounter. If the practice used two instruments by age, a separate assessment entity would be cleaner.

4. Guardian details are attributes of PATIENT. A child with two guardians at different addresses would push guardians into their own entity, which the practice may need for custody situations.

5. Outside results enter through the order that requested them. Results that arrive with no Calder Bay order, such as a hospital's discharge spirometry, are out of scope for this version.

How the Diagram Answers the Unit 1 Problem

In the flat spreadsheet, a date stood in for an event. Here, Mia's two Tuesday visits are two ENCOUNTER rows at different sites with different providers, each with its own diagnoses. Her spirometry is a TEST ORDER from the first encounter, and Friday's result is a TEST RESULT pointing to that order. Nothing is pasted into notes, and a report counting asthma encounters by site or listing results by ordering provider can be answered from the structure itself. Data quality frameworks describe this kind of conformance, data stored where its definition says it belongs, as a precondition for any later reuse (Weiskopf & Weng, 2013).

References

Chen, P. P.-S. (1976). The entity-relationship model: Toward a unified view of data. ACM Transactions on Database Systems, 1(1), 9-36. https://doi.org/10.1145/320434.320440

Codd, E. F. (1970). A relational model of data for large shared data banks. Communications of the ACM, 13(6), 377-387. https://doi.org/10.1145/362384.362685

Weiskopf, N. G., & Weng, C. (2013). Methods and dimensions of electronic health record data quality assessment: Enabling reuse for clinical research. Journal of the American Medical Informatics Association, 20(1), 144-151. https://doi.org/10.1136/amiajnl-2011-000681