Diagram

Wk 3 tables (Create into diagrams)

1.
Patient Information Table:

· Patient ID (MRN)

· First Name

· Last Name

· Date of Birth

· Gender

· Contact Information

2.
Medications Table:

· Medication #

· Medication Name

· Dosage

· Route of Administration

· Prescribing Physician

· Pharmacy Information

3.
Medication Administration Errors Table:

· Error #

· Patient ID

· Medication #

· Date and Time of Error

· Description of Error

· Severity of Error

· Staff Member Involved

· Location (Inpatient, Outpatient, Home Health)

4.
Adverse Events Table:

· Event #

· Patient ID

· Medication #

· Date and Time of Event

· Type of Adverse Event (e.g., Allergic Reaction, Overdose)

· Description of Event

· Severity of Event

· Location

5.
Inpatient Information Table:

· Admission #

· Patient ID

· Admission Date

· Discharge Date

· Unit

· Attending Physician

6.
Outpatient Visits Table:

· Visit ID

· Patient ID

· Visit Date

· Department

· Treating Physician

7.
Home Health Visits Table:

· Visit #

· Patient ID

· Visit Date

· Home Health Agency

· Visiting Nurse

8.
Staff Information Table:

· Staff ID

· First Name

· Last Name

· Position

· Contact Information

WK 4 proposed changes

To address specific design issues such as one-to-many relationships and data types in the given database design, you can make the following adjustments:

1. Patient Information Table:

· Patient ID (MRN) should be the primary key.

· Contact Information should be split into separate fields such as Address, Phone Number, and Email to maintain data integrity and improve query performance.

2. Medications Table:

· Medication # should be the primary key.

· Consider adding a Medication Type field to differentiate between different types of medications (e.g., Prescription, Over-the-counter).

· Dosage should specify the unit of measurement (e.g., mg, mL).

· Route of Administration could be a separate table with a foreign key reference to ensure consistency in route options.

3. Medication Administration Errors Table:

· Error # should be the primary key.

· Patient ID and Medication # should be foreign keys referencing the Patient Information Table and Medications Table, respectively.

· Severity of Error should be defined as a discrete set of values (e.g., Low, Medium, High) rather than free text.

· Location should reference a separate Location Table to ensure consistent entries.

4. Adverse Events Table:

· Event # should be the primary key.

· Patient ID and Medication # should be foreign keys referencing the Patient Information Table and Medications Table, respectively.

· Type of Adverse Event should reference a separate Event Type Table to maintain consistency.

· Severity of Event can be categorized into predefined levels (e.g., Mild, Moderate, Severe).

5. Inpatient Information Table:

· Admission # should be the primary key.

· Patient ID should be a foreign key referencing the Patient Information Table.

· Unit should reference a separate Unit Table to maintain consistency.

6. Outpatient Visits Table:

· Visit ID should be the primary key.

· Patient ID should be a foreign key referencing the Patient Information Table.

· Department should reference a separate Department Table to ensure consistent department names.

7. Home Health Visits Table:

· Visit # should be the primary key.

· Patient ID should be a foreign key referencing the Patient Information Table.

· Home Health Agency and Visiting Nurse should reference separate tables to maintain consistency.

8. Staff Information Table:

· Staff ID should be the primary key.

· Consider adding a Staff Role table with a foreign key reference to specify the position/role of the staff member.

· Contact Information can be split into separate fields like Address, Phone Number, and Email for better data organization.

By making these adjustments, you ensure that data is organized efficiently and that one-to-many relationships are properly established, improving the overall integrity and usability of the database.