| Course | C792 Data Modeling and Database Management Systems |
|---|---|
| Task | Task 2 |
| Paper type | Entity relationship model |
| Length | About 1,100 words, 4 pages |
| Format | APA 7 |
| School | Western Governors University (WGU) |
| Program | MSN Nursing Informatics |
| Updated | September 2026 |
Free sample paper for C792 Task 2
Entity Relationship Model for Structured Suicide Risk Screening: Seven Entities, Their Keys and Relationships, Normalization and Three Queries the Model Must Answer
Student Name
Leavitt School of Health, Western Governors University
C792: Data Modeling and Database Management Systems, Task 2
Course Instructor
Month Day, Year
Entity Relationship Model for Structured Suicide Risk Screening: Seven Entities, Their Keys and Relationships, Normalization and Three Queries the Model Must Answer
From Workflow to Data Model
The workflow analysis for this project, the move from a paper suicide risk screener to a structured screener in the electronic health record (EHR) of a composite 110-bed rural hospital, identified the data the future process must capture for each Columbia Suicide Severity Rating Scale screener (Posner et al., 2011): patients, encounters, screening events and their answers, calculated risk levels, the interventions a high risk result triggers, the staff who act and the notifications sent. This paper turns that list into a logical data model using the entity relationship approach, which represents the real world as entities, the attributes that describe them and the relationships between them (Chen, 1976). The model is logical rather than physical: it defines what must be stored and how the pieces relate, not how a particular EHR vendor implements tables.
The model has to answer three questions the hospital cares about. What proportion of eligible ED patients were screened last month? For patients who screened high risk, how long did it take to start one-to-one observation? And when an inpatient is admitted, what is the most recent screening result and who recorded it?
Entities and Attributes
Each entity below has a primary key (PK) that identifies one instance uniquely, and foreign keys (FK) that link it to other entities. Data types are shown in general terms.
| Entity | Primary key | Key attributes | Foreign keys |
|---|---|---|---|
| PATIENT | patient_id | medical_record_number, date_of_birth, preferred_language | none |
| ENCOUNTER | encounter_id | encounter_type (ED, inpatient), arrival_datetime, discharge_datetime, disposition | patient_id |
| STAFF | staff_id | name, role (RN, provider, liaison, observer), department | none |
| SCREENING | screening_id | screening_datetime, setting (triage, admission, reassessment), interpreter_used (yes/no), deferral_reason, risk_level, scoring_version | encounter_id, staff_id (screener) |
| SCREENING_ANSWER | screening_id + question_code | answer_value (yes, no, unable to answer) | screening_id, question_code |
| QUESTION | question_code | question_text, sequence, standard_code (LOINC), active_from, active_to | none |
| INTERVENTION | intervention_id | intervention_type (observation, room safety check, liaison evaluation), ordered_datetime, started_datetime, ended_datetime | screening_id, ordered_by (staff_id), performed_by (staff_id) |
Relationships and Cardinality
PATIENT to ENCOUNTER is one to many: a patient can have many visits, but each encounter belongs to one patient. ENCOUNTER to SCREENING is one to many, and optional on the screening side: an encounter may have no screening (a patient under 12, or one who left before triage) or several (a triage screen and an admission rescreen). SCREENING to SCREENING_ANSWER is one to many and mandatory: a completed screening has at least one answer. QUESTION to SCREENING_ANSWER is also one to many; the combination of screening and question forms the answer's primary key, which resolves the many-to-many relationship between screenings and questions. SCREENING to INTERVENTION is one to many and optional, since most screenings are low risk and generate no intervention. STAFF relates to SCREENING and INTERVENTION through separate foreign keys for the person who screened, ordered and performed, so one staff member can appear in many roles across many records.
The QUESTION entity carries active dates. If the screener wording changes, a new question row is added and the old one is retired rather than edited, so historical answers still point to the text the patient was actually asked. The SCREENING entity records the scoring version for the same reason.
The Diagram in Text Form
PATIENT (1) to ENCOUNTER (0..N); ENCOUNTER (1) to SCREENING (0..N); SCREENING (1) to SCREENING_ANSWER (1..N); QUESTION (1) to SCREENING_ANSWER (0..N).
SCREENING (1) to INTERVENTION (0..N).
STAFF (1) to SCREENING (0..N) as screener; STAFF (1) to INTERVENTION (0..N) as orderer and as performer.
Normalization
Normalization removes redundancy so that each fact is stored once and updates cannot create contradictions, an approach grounded in the relational model of data (Codd, 1970). The model is in third normal form. It is in first normal form because every attribute holds one value: answers are stored one per row in SCREENING_ANSWER rather than as six columns or a comma-separated list, which also lets the screener grow or shrink without changing the table. It is in second normal form because the only composite key, screening plus question, has no attribute that depends on just one part of it: question text lives in QUESTION, not in the answer table. It is in third normal form because no non-key attribute depends on another non-key attribute: the patient's date of birth is stored in PATIENT, not repeated in each ENCOUNTER, and a staff member's role is stored once in STAFF.
One deliberate exception is risk_level in SCREENING. It could be derived from the answers each time, but storing it with the scoring version preserves what the nurse saw at the moment of screening, which matters clinically and legally if the scoring logic changes later.
Queries the Model Must Answer
The three questions from the introduction can be answered with standard SQL against this model. The first counts screened patients against eligible ED encounters: SELECT COUNT(DISTINCT s.encounter_id) * 1.0 / COUNT(DISTINCT e.encounter_id) FROM ENCOUNTER e LEFT JOIN SCREENING s ON s.encounter_id = e.encounter_id WHERE e.encounter_type = 'ED' AND the patient was 12 or older at arrival AND the arrival date falls in the month.
The second measures time to observation for high risk screens: SELECT s.screening_id, i.started_datetime minus s.screening_datetime AS minutes_to_observation FROM SCREENING s JOIN INTERVENTION i ON i.screening_id = s.screening_id WHERE s.risk_level = 'high' AND i.intervention_type = 'observation'.
The third returns the most recent screen at admission: SELECT the screening with the latest screening_datetime for the patient across encounters in the last 72 hours, joined to STAFF for the screener's name and role. Each query uses only keys and attributes defined above, which confirms that the model supports the workflow's reporting needs.
Data Integrity and Security
Referential integrity rules prevent orphan records: an answer cannot exist without its screening, and a screening cannot exist without its encounter. Required fields enforce the workflow's rule that a screening cannot be closed with a blank answer unless a deferral reason is recorded. Because suicide screening data are sensitive, access is limited by role, every read and change is logged, and reporting extracts use de-identified patient keys rather than names or record numbers.
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
Posner, K., Brown, G. K., Stanley, B., Brent, D. A., Yershova, K. V., Oquendo, M. A., Currier, G. W., Melvin, G. A., Greenhill, L., Shen, S., & Mann, J. J. (2011). The Columbia-Suicide Severity Rating Scale: Initial validity and internal consistency findings from three multisite studies with adolescents and adults. American Journal of Psychiatry, 168(12), 1266-1277. https://doi.org/10.1176/appi.ajp.2011.10111704
What the C792 Task 2 instructions ask
The second C792 task asks you to design the data model behind a clinical process. Most versions ask for entities and attributes, primary and foreign keys, relationships with cardinality, a diagram, an explanation of normalization and a discussion of how the model supports the organization's questions. Some versions ask for sample queries or for integrity and security considerations. The model should come from your workflow analysis, so each entity reflects something the process creates or uses. The evaluator reads for a model that is correct as a database design and useful for the clinical questions it is meant to answer.
How this C792 Task 2 example is built
The paper begins by connecting the model to the workflow analysis and stating the three questions it must answer. Each entity is listed with its keys and attributes and a short justification. Relationships are described in words and then in a compact text diagram with cardinality, so the reader can check them both ways. The normalization section explains how splitting answers from screenings prevents repeated data and update errors. The query section writes SQL for each question and explains what it returns. Integrity and security rules close the paper, covering required fields, orphan records and role-based access to sensitive answers.
Where the C792 Task 2 rubric puts the marks
The C792 Task 2 rubric marks each aspect competent, approaching competence or not evident. Entity and attribute aspects check that the model includes the needed entities with appropriate attributes and data types. A keys aspect looks for correct primary and foreign keys. A relationship aspect asks for accurate cardinality. A diagram aspect expects a clear visual or textual representation. A normalization aspect wants an explanation, not just a claim that the model is normalized. Where included, query and integrity aspects check that the model answers real questions and protects data. APA citations for modeling concepts and professional writing are also scored.
C792 Task 2 help: what sends it back
The most common data model error is storing repeating answers inside one table, such as question one, question two and so on as columns. Put each answer in its own row linked to its screening. Second, cardinality is often wrong or missing; ask how many of each entity can relate to one of the other, in both directions. Third, primary keys sometimes use values that can change, such as names. Use stable identifiers. Fourth, normalization is claimed without explanation. Show what redundancy your design removes. Finally, test the model against the questions it must answer. If a query cannot be written, an entity or relationship is probably missing. A quick way to check normalization is to imagine changing one fact, such as a question's wording, and asking how many rows would have to change; the answer should be one.
Get a C792 Task 2 example written to your instructions
Send the Task 2 instructions and rubric aspects from your C792 course of study, with your workflow. We write a custom data model to those exact aspects, returned in 24-48h. The first custom sample is free.
More C792 papers
Other Nursing (MSN) sample papers
- D158 Task 2 Implementation Plan
- D159 Task 1 Evaluation Measure Set
- D030 Task 1 Nursing Service Line Proposal and SWOT Analysis
- C790 Task 1 Informatics E-portfolio
C792 Task 2 questions, answered
Does C792 require an actual diagram image?
Most versions expect a diagram. This sample describes the relationships in text; your submission should include the drawn diagram in the notation your instructions name.
How many entities should a C792 model have?
Enough to represent every distinct thing the workflow records without repeating data. The sample uses seven, from patient and encounter to screening, question and screening answer.
What is the most common C792 data model error?
Storing repeating items as columns, such as one column per screening question. Each answer belongs in its own row linked to its screening, which keeps the model normalized.
Does C792 Task 2 need SQL queries?
Check your instructions. Writing a few queries, as the sample does for screening rate and time to observation, is a strong way to show the model answers the organization's questions.
Where can I find a free C792 Task 2 sample paper?
Every entity, key, relationship and query is reproduced above with commentary. For a model based on your own workflow, share the C792 task and the first custom model is prepared free.