NSG/543 Week 2: Tables, Keys and Relationships, sample paper

Five Tables, Seven Keys and One Row Per Fact: Designing and Normalizing the Relational Schema for a Volunteer Vigil Program

[Student Name]

University of Phoenix

NSG/543: Database Management

Week 2 Assignment

[Instructor Name]

[Date]

The hospital, the program and the design are a composite written for a model paper.

In Week 1, I identified five entities for a database to replace the spreadsheet used by our hospital's No One Dies Alone program: volunteers, training records, patient vigils, shifts and units. This paper designs the tables that will hold them and shows how the design avoids the problems of the spreadsheet.

Design Goals

The design has three goals, each tied to a problem found in Week 1. Store each fact once, so a volunteer's phone number cannot become inconsistent. Record requested shifts as well as filled ones, so uncovered hours can be counted. And identify patients reliably, so two patients with the same initials cannot be confused.

Table 1: Volunteer

Fields: VolunteerID, integer, primary key, assigned automatically; FirstName and LastName, text; Phone, text of 10 digits; Email, text; StartDate, date; Status, text limited to active, inactive or leave; PreferredShifts, text; and Notes, text.

Why: every fact about a volunteer that does not change from vigil to vigil lives here and only here.

Table 2: Training

Fields: TrainingID, integer, primary key; VolunteerID, integer, foreign key to Volunteer; CourseName, text limited to orientation, annual refresher or bereavement module; DateCompleted, date; and ExpirationDate, date, calculated as one year after completion for the refresher.

Why: a volunteer completes many trainings over time, so training cannot be a single column in the volunteer table without either overwriting history or repeating columns.

Table 3: Unit

Fields: UnitID, integer, primary key; UnitName, text; Floor, integer; and NursingContact, text.

Table 4: Vigil

Fields: VigilID, integer, primary key; MRN, text, the patient's medical record number; UnitID, integer, foreign key to Unit; Room, text; RequestDateTime, date-time; RequestingNurse, text; ExpectedDurationHours, integer; EndDateTime, date-time; and EndReason, text limited to patient died, family arrived, transferred or vigil not needed.

Why: the medical record number identifies the patient uniquely, which solves the initials problem; the patient's name is not stored here but displayed from the record when needed, keeping protected health information in the database to a minimum.

Table 5: Shift

Fields: ShiftID, integer, primary key; VigilID, integer, foreign key to Vigil; VolunteerID, integer, foreign key to Volunteer, left empty when the shift is unfilled; ScheduledStart and ScheduledEnd, date-time; ActualStart and ActualEnd, date-time; and FilledStatus, text limited to filled, unfilled or canceled.

Why: when a vigil is requested, the coordinator creates shifts for the expected duration in two-hour blocks. A shift row that exists without a volunteer is the database's way of recording an hour when a dying patient was alone, which the spreadsheet could never show.

Relationships and the Diagram

The entity-relationship diagram, described in words, has Volunteer at the left, Vigil at the right and Shift between them. Volunteer to Shift is one to many, since a volunteer works many shifts; Vigil to Shift is one to many, since a vigil has many shifts. Shift therefore resolves the many-to-many relationship between volunteers and vigils. Volunteer to Training is one to many. Unit to Vigil is one to many. Every foreign key is enforced, so a shift cannot refer to a vigil that does not exist.

Normalization

Codd (1970) introduced normal forms to prevent the anomalies that arise when data are repeated. The spreadsheet illustrates each.

First normal form requires that each field hold a single value and each row be unique. The spreadsheet's notes column sometimes listed two volunteers in one cell when they overlapped; the Shift table gives each volunteer's shift its own row.

Second normal form requires that every non-key field depend on the whole key. In the spreadsheet, a row was effectively identified by date, time and volunteer, yet the volunteer's phone number depended only on the volunteer; moving phone numbers to the Volunteer table fixes this.

Third normal form requires that non-key fields not depend on other non-key fields. In the spreadsheet, the unit's floor depended on the unit name, not on the shift; the Unit table fixes this.

The result is that each fact appears once: a phone number in Volunteer, a floor in Unit and a training date in Training.

Validation Rules

The design includes rules that prevent bad data from entering. ActualEnd must be after ActualStart. A shift cannot be marked filled without a VolunteerID. ExpirationDate is calculated rather than typed. EndReason must come from its list. A volunteer with status inactive cannot be assigned to a new shift.

Testing the Design Against the Program's Questions

The design was checked against the questions the steering committee asks. How many vigil hours were requested and how many were covered last quarter? Answerable from Shift. Which volunteers need refresher training this month? Answerable from Training. Which units request vigils most often? Answerable from Vigil and Unit. How quickly are first shifts filled after a request? Answerable by comparing RequestDateTime with the earliest ActualStart. Each question can be answered without storing any fact twice. McGonigle and Mastrian (2022) describe designing a database from the questions its users need answered, which is how these tests were chosen.

Design Choices Considered and Rejected

Two alternatives were considered. Storing patient names in the Vigil table would make reports easier to read but would duplicate protected health information that already exists in the record; the medical record number is enough to retrieve the name when a coordinator needs it. Combining Vigil and Shift into one table would simplify the design but would repeat the patient, unit and request details on every shift row, reintroducing the anomalies normalization removes. Programs of this kind depend on accurate scheduling across many volunteers (Bradas et al., 2014), and a design that keeps each fact in one place protects that accuracy.

Growth

The design allows growth. If the program adds bedside music or legacy projects, a new table can hold those activities linked to the vigil without changing existing tables.

Security in the Design

Only the Vigil table holds a patient identifier. Access will be controlled by role, with the coordinator and chaplains able to view and edit, and nursing leadership able to see only reports without identifiers.

Documentation

The design is documented in a data dictionary that lists every table and field, its type, its allowed values and its meaning in plain words, so that a future coordinator or analyst can understand the database without the designer. The dictionary also records why each design choice was made, including the choice to leave patient names in the record rather than the database.

Build Plan

The tables will be built in the hospital's approved database platform in a test environment first. Two years of spreadsheet data will be migrated with a script that splits each spreadsheet row into its Volunteer, Vigil, Shift and Unit parts, and the migrated counts will be checked against the spreadsheet before go-live.

Conclusion

Five normalized tables linked by keys now represent the program's work: volunteers and their training, units, vigils and the shifts that connect volunteers to vigils. The design stores each fact once, records unfilled shifts so uncovered hours can be measured, identifies patients reliably and limits protected health information. Week 3 will design the forms through which staff and coordinators enter data.

References

Bradas, C. M., Bowden, V., Moldaver, B., & Mion, L. C. (2014). Implementing the "No One Dies Alone" program: Process and lessons learned. Geriatric Nursing, 35(6), 471-473. https://doi.org/10.1016/j.gerinurse.2014.10.005

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

McGonigle, D., & Mastrian, K. G. (2022). Nursing informatics and the foundation of knowledge (5th ed.). Jones & Bartlett Learning.