Essay NR542-11252 Week 5 Course Project Relational Schema LT

Essay NR542-11252 Week 5 Course Project Relational Schema LT

Essay NR542-11252 Week 5 Course Project Relational Schema LT

NR542-11252 Week 5 Course Project Relational Schema LT

Introduction

This project relates to creating and explaining a database through the identification of the relational database system (RDMS) using a relational schema. The project helps to understand the concept and idea of creating relational tables for the database. The database was created using particular entities with attributes. The purpose of initiating a database project for Vila Health Hospital in the United States observed several ventilator associated pneumonia (VAP) patients on the stage of hospitalization. The healthcare professionals and nurses observed that after one to five days of admission in the hospital, the patients suffered from VAP issues. Since VAP has several health implications and risk factors such as  defences, contaminated equipment, invasive monitoring devices, and aspiration of GI contents, during COVID-19 (Boltey Yakusheva, & Costa, 2017).  A conceptual database model was created by the experts that focused on surveillance of VAP and contained an algorithm that allows electronic monitoring of VAP based on radiology reports, laboratory values, and trend data. 

Concept of Relational Tables

The three major questions asked in this project were: Was a ventilator associated pneumonia bundle be used in the hospital? How many ventilators days did the patient have? And what are the complications faced by patients due to the VAP? Since RDMBS designed was used to plan a database, a relational design consists of data stored and accessed through tables. This means that a relational database is a set of tables that help to save or store interrelated data. For example, the fields held in a table are called rows and columns. Each table can contain more than one row or column associated with a specific data type (Lubis, 2020). Therefore, in RDMBS, each table stores information about a specific thing described below in the entity-relationship diagram (ER).

Essay NR542-11252 Week 3 Course Project—Database Plan LT 

NR542-11252 Week 5 Course Project Relational Schema LT

The following tables can be created in the database according to the ER diagram in the previous weeks ‘work. The database name created in SQL server is proposed as “Ventilator Associated Pneumonia Prevention”. According to the number of entities in the ER diagram, there will be at least four tables required to be created in the database with their associated attributes. For example, the detailed tables their rows and columns are given below:

Tables and Relations  

Table 1: Patient 

VAP Event ID Number	Patient’s Prognosis	Respiratory Status	Amount of Secretions	Admitting Diagnosis
12300	60 years	normal	20+ per day	Severe pneumonia

Table 2: Respiratory Therapist

Therapist ID number or License	Training Period/experience	Readiness for Extubating	VAP Event ID Number	Daily Spontaneous Breathing Trials
00121	9 years	Good	12300	3

Table 3: Nurse 

Nurse ID (MRN)	Staffing Ratio	VAP Event ID Number	Ward ID	Credentials
00012	1:3	12300	12300	DNP-prepared

Table 4: VAP Prevention Bundle 

VAP Event ID Number	Daily Oral Care with Chlorhexid	Prophylaxis of venous thromoboembolism and peptic ulcer	Sedation holidays	Elevation of head of the bed
12300	3 times mouthwash	1 doze	2	30 degrees

 Table 5: Environment

Ward ID	ICU Setting	Hand Hygiene	Use of personal protective equipment	Daily Oral Care with chlorhexidi
12300	Ventilated intensive care	Wash 8-10 times a day	Continuously	3 times a day

NR542-11252 Week 5 Course Project Relational Schema LT

Relational Tables

Attributes and Primary Keys

As seen in the tables, the five entities have separate tables such as Nurse, Respiratory Therapist, VAP Prevention Bundle, and the Environment. These entities are chosen because all of them play a crucial role in the prevention of VAP-related issues faced by patients. Each table is related to each other due to specific attributes (Jans, 2017). The benefit of a relational table is that information is only entered once, however, different departments can access it simultaneously. 

For example, the tables show how the VAP bundle is used at the hospital facility using a Patient Table that provides details about each VAP event, patient’s prognosis and respiratory status, and also the amount of secretions. The Respiratory Therapist Table shows the number of training days and his readiness for the job. The Nurse table shows each nurse’s ID or license number along with credentials and staffing ratio. The VAP Prevention Bundle provides information about daily oral care with medications and how many days a ventilator bed’s head is elevated.

NR542-11252 Week 5 Course Project Relational Schema LT

The Primary and Foreign keys are vital in all tables because they help to describe the relationships between information in all these tables (Lubis, 2020). These keys help to define the structure of a database table. This means through primary key, users can identify a table’ rows and also define its relationship with other tables. For example, for the Patient table, the primary key is the VAP Event or ID Number that uniquely identifies each patient. For Respiratory Trainer, the training period is the most essential characteristic to identify the effectiveness of a therapist. For VAP Prevention Bundle, again patient ID should be the extra attribute that can be the primary key. For Nurse Table, Nurse ID or License Number is the primary key. For environment entity, VAP Event ID is the primary key. The relationships represent the common attributes found in different tables. Similarly, for the Environment table, VAP Event ID is the primary key that uniquely identifies that table. The overall environment also consists of ICU settings, hand hygiene, use of personal protective equipment, and daily oral care with chlorhexidi. 

Entity-Relationship Diagram (ER) and Relations 

The following diagram shows the entity-relationship diagram (ER) that describes the type of entities used in the database and their associated attributes. The ER diagram helped to determine the relationship between nursing staff, prognosis, and workload etc. The Respiratory Therapist attributes show that the patients are receiving daily sedation vacations and spontaneous breathing trials scheduled for each patient. The VAP Prevention bundle when applied to the patients show positive outcomes that included oral care with chlorhexidine and sedative interruption and assessment of readiness to extubate to the VAP bundle. 

NR542-11252 Week 5 Course Project Relational Schema LT

The additional entities are added in the last week’s diagram is the Environment that shows environmental concerns such as the presence of an Endotracheal tube (EET) that helps to move pathogens towards distal airways. This means that a strict infection control measures and a clean environment is essential for preventing VAP.  The cardinalities show that patient’s hygiene is related to their environment and VAP prevention is possible through VAP Prevention Bundle. The Respiratory Therapist is managing the VAP while a nurse is assigned to each separate patient. 

Conclusion 

Ventilator-associated pneumonia (VAP) and is a vital case of pneumonia in the hospital setting that required human intervention to create a relational database model for its prevention. This project shows the schema and organization of database tables and the rationale for using each entity and its attributes. Through this database, trends in VAP incidences will be monitored easily to reduce the risk factors of VAP. 

ER Diagram 

Cardinalities

References

Boltey, E., Yakusheva, O., & Costa, D. K. (2017). 5 Nursing strategies to prevent ventilator-associated pneumonia. American nurse today, 12(6), 42–43.

Jans, M., & Soffer, P. (2017, September). From relational database to event log: decisions with quality impact. In International Conference on Business Process Management (pp. 588-599). Springer, Cham.

Lubis, J. H., & Zamzami, E. M. (2020, June). Relational database reconstruction from SQL to Entity Relational Diagrams. In Journal of Physics: Conference Series (Vol. 1566, No. 1, p. 012072). IOP Publishing.

Essay NR542-11252 Week 5 Course Project Relational Schema LT