NR 542 W5 Relational Schema SOLUTION

This assignment is intended to facilitate the database project through identification of the relational schema underlying the planned database.

Please Note: The database project will be developed in Microsoft Excel or Access. Your three questions should be included on the diagram.

My database project is CAUTIs (Catheter associated urinary tract infections)
The database questions are
1) What is the unit compliance rate with proper urinary catheter infection?
2) Did 2 nurses verify catheter insertion?
3) Is there an assessment, implementation, and documentation on foley care and the need for insertion/removal to prevent CAUTIs?

Assignment 2: Relational Schema

Healthcare-associated infections (HAIs) are a major source of illness, mortality, and unnecessary spending in all healthcare institutions. According to Pérez et al. (2017), HAIs afflicts one out of every twenty patients in the hospital at any given moment. The Agency for Healthcare Research and Quality (AHRQ) is sponsoring a countrywide effort to promote the adoption of the Comprehensive Unit-based Safety Program (CUSP) to minimize catheter-associated urinary tract infection (CAUTI) in healthcare settings as part of the National Action Plan. This project will create a database focusing on the various features of CAUTIs diagnosis and treatment. The database will aid in establishing whether or not two nurses have confirmed catheter insertion and whether or not there has been an assessment, implementation, and documentation on foley care and the necessity for insertion/removal to prevent CAUTIs. This paper will highlight the concept of relational tables, entity relationship diagrams for the database project, highlight the different table attributes and keys.

Click here to ORDER an A++ paper from our Verified MASTERS and DOCTORATE WRITERS: NR 542 W5 Relational Schema SOLUTION

Concept of Relational Tables

A relational database structures data into tables that can be connected (or related) depending on data that everyone shares. One may create a whole new table from data in one or more existing tables (Johnst

on, 2014). It also enables users and the organization to better understand the relationships between all accessible data and gain fresh insights to make more informed decisions or uncover new opportunities.

This project, for instance, depicts a patient table that contains hospital data on each patient’s details and one or more treatment tables that have data detailing individual treatments. The fields (or columns) for the patient table may be PatientID, HospitalName, HospitalAddress, and so on; the columns for the treatment table could be TreatmentDate, PatientID, Dosage, TreatmentMethod, and so on. The tables can be linked using the common PatientID field. As a result, one can query the table to generate beneficial results, such as a summarized patient treatment report.

Entity Relationship Diagram

Figure 1: ER Diagram

Figure 2: Entity-Relationship diagram

One of the secondary keys in the database is the foreign key. The CAUTI table contains the foreign key. The foreign key constraint is critical to designing relational databases (Johnston & Weis, 2010). It enables us to link data based on the requirements. According to Johnston and Weis (2010), it allows us to decide what to do on DELETE and UPDATE actions made on the records of the primary table since it generates some dependence between the fields of the foreign and primary tables. We secure the integrity, accuracy, and clarity of data by employing the foreign key constraint. The foreign table’s entries must be present in the primary table. We can also put rules on the type of relationship, such as many-to-many, one-to-many, or one-to-one.

Relational Tables

Relational tables were created using Microsoft Access.

Attributes

Figure 3: Attributes for table nurse

Figure 4: Attributes for table patient

Figure 5: Attributes for table CAUTI

Keys

Figure 6: NurseId set as Primary Key for table nurse

Figure 7: PatientID set as Primary Key for table patient

To ensure the correctness of the data in the database, a secondary key (foreign key) was created in the CAUTI table on the PatientID attribute to ensure that when updates are made, they are reflected in the patient’s table. The figure below illustrates how this was achieved.

Figure 8: Setting Foreign Key

Figure 9: Setting Foreign Key

Conclusion

Working on this project has enhanced my skills in database design, the importance of a database, and why it is important to enforce referential integrity on relational tables. Databases, which are utilized in many parts of our daily lives, allow data to be saved quickly and easily. Behind the scenes, we need DBMSs to accomplish all of the aforementioned tasks. Many of the services we use daily are powered by databases. Referential integrity, such as primary and secondary keys, ensures that the relationship between different tables remains in sync when the update and delete actions are executed. It is critical because it prevents users from inserting errors into the database.

References

Johnston, T. (2014). The relational paradigm. In Bitemporal Data (pp. 79–97). Elsevier. https://doi.org/10.1016/B978-0-12-408067-6.00006-1

Johnston, T., & Weis, R. (2010). Temporal transactions on multiple tables. In Managing Time in Relational Databases (pp. 241–260). Elsevier. https://doi.org/10.1016/B978-0-12-375041-9.00011-X

Pérez, E., Uyan, B., Rohde, R. E., Wehbe-Janek, H., Hochhalter, A. K., & Fenton, S. H. (2017). Assessing catheter-associated urinary tract infection prevention interventions in intensive care units: A discrete event simulation study. IISE Transactions on Healthcare Systems Engineering, 7(1), 43–52. https://doi.org/10.1080/19488300.2016.1265616