Napa Valley Care Center Database Damla Bayindir Ivette

Napa Valley Care Center Database Damla Bayindir Ivette Bigit Robert Dooley Michael Ellison Ashwini Purohit Ma Than Thaik Jannat Dena Vaziri

Final Presentation Overview v. Client Description v. Our Goal v. EER v. Relational Schema Screenshot v. Normalization Analysis v. Queries v. Forms v. Reports

Client Description v Napa Valley Care Center is a nursing care center with ~100 employees v Focus on Station 2: rehabilitation and short-term care v 43 beds in this section v All patient data and forms are currently recorded by hand v 1000+ records of weekly summaries v Schedules of employees and shifts are confusing

Our Goal v. Increase the efficiency of the care center v. Save the employees time v. Eliminate errors and inconsistencies in scheduling v. Reduce the number of duplicate charting errors v. Make patient health information more consistent, reliable, and accessible to nurses v. Universalize all record keeping techniques

Visitor (0, 1) Lives in timestamp (0, N) Bed (1, N) Assigned to (1, N) Shift (1, 1) (1, N) has assess (1, 1) (0, M) (1, 1) visits (1, 1) Insurance Plan (1, N) (0, N) from during (0, N) Error works (1, N) (0, N) Due to Insurance Company CNA (1, N) (0, N) Patient performs (1, N) (1, M) (0, N) gives (0, 1) (0, N) Permission Center Treatment has Personal Medication (1, 1) uses Inventory Employee RNA p, d LVN involves (1, N) Center Assessmen t (1, 1) (0, N) RN Food Item (0, N) Trained for (0, N) Is a type of (0, N) Center Prescription has (1, 1) Pre-existing Medical History performs Meal Admin p, d (1, 1) (1, N) Care Center Activity about uses (0, N) p, d (1, N) (0, N) ADL (0, N) Observation History Medication History p, d (0, 1) (0, N) includes Medication (1, N) Medication Admin involves Activity Types p, d (1, N) Trained for (1, N) Treatment History (1, 1) includes Rehab (0, N) Procedures (0, N) Trained for (0, N) Assessment (1, N) Trained for (0, N) t, d (0, N) (1, 1) Provided by (1, N) Treatment Types External has (1, 1) treatment type t, d (0, N) (1, N) Supplier (General) Inventory item IVs Non-IV Trained for (0, N) (1, N) Trained for

Relational Schema 1. Patient(PSSN, Fname, Lname, Phone. Num, Bed. ID 5, Pre. MID 13) 2. Employee(ESSN, Emp. FName, Emp. LName) 2 a. RN(ESSN 2) 2 b. LVN(ESSN 2) 2 c. RNA(ESSN 2) 2 d. CNA(ESSN 2) 3. Visitor(VID, Vfname, Vlname) 4. Permission(PID, Permission. Name, Description) 5. Bed(Bed. ID, Room. Num, XCoord, YCoord) 6. Patient. Insurance (PSSN 1, Plan. ID 36, Member. ID) 7. Shift(Shift. ID, Shift. Name, Start. Time, End. Time) 8. Error( Error. ID, Shift. ID 7, Error. Name, Error. Description) 9. Supplier(Sup. ID, Sup. Name, Contact) 10. Procedures(Proced. ID) 10 a. External. Treatment. Type(Proced. ID 10, External. Name, External. Description)

Relational Schema (cont. ) 10 b. Non-IV(Act. ID 11, Nonl. VName, Description) 10 c. IV(Act. ID 11, IVname, Description) 11. Activity Types(Act. ID, Act. Name, Act. Description) 11 a. ADL(Act. ID 11, ADLName, ADLDescription) 11 b. Assessment(Act. ID, Assess. Name, Assess. Description) 11 c. Treatment Types(Act. ID 11 , Proced. ID 10, Proced. ID, Treatment. Name, Treatment. Description) 11 d. Meal Admin(Admin. Meal. ID, Act. ID 11, Time. Served) 11 e. Rehab(Rehab. ID, Rehab. Name, Rehab. Description) 11 f. Medication Admin(Admin. MID, Act. ID 11, Med. ID 14) 12. Care Center Activity(Act. ID, PSSN 1, ESSN 2, CCDate, CCName, CCDesc, ) 12 a. Center Treatment(Act. ID, PSSN 1, ESSN 2, CCDate, TTID 11 c, Proced. ID, Med. ID 14)

Relational Schema (cont. ) 12 b. Center Prescription (Act. ID, PSSN 1, ESSN 2, CCDate , Med. ID 14, TTID 11 c) 12 c. Center Assessment(Act. ID, PSSN 1, ESSN 2, CCDate, Scale (1 -10)) 13. Pre-existing Medical History (PSSN 1) 13 a. Observation History(PSSN 1, Obs. Date, Obs. Description) 13 b. Treatment History(PSSN 1, Proced. ID 10, Treatment. Name, Treatment. Description, TTID 11 c, AID ) 13 c. Medication History(PSSN 1, Med. ID 13 Med. Date, Medication) 14. Medication(Med. ID, Med. Name, Dosage, Frequency, Sup. ID 9, Lead. Time, Interest. Rate, Unit. Cost. Setup. Cost, Demand) 15. General Inventory Item(Med. ID 14, Inv. Name, Inv. Type, Current. Inventory) 16. Food Item(Food. ID, Food. Name, Food. Calories) 17. Person. Medication. Inventory(Med. ID 14, PSSN 1, Quantity) 18. Employee. Junction. Shift(Shift. ID 7, ESSN 2) 19. Bedjuction. Shift(Bed. ID 5, Shift. ID 7) 20. Bed. Assess. Shift(Bed. ID 5, Shift. ID 7, Assessment )

Relational Schema (cont. ) 21. Gives. Permission(PID 4, PSSN 1, Per. Date, Acc/Rej) 23. Error. Junction. Emp(Error. ID 8, ESSN 2) 24. Pat. Vis. Junc(VID 3, PSSN 1, VMonth, VDay, Vyear) 25. Adminmeal. Junction. Food(Admin. Meal. ID 11 d, Food. ID 16) 26. Admin. MIDjunction. MED(Med. ID 14, Act. ID 11) 27. Prescrıbed. Medication(PSSN, Admin. MID 11 d, Med. ID 14 , Start. Date, End. Date) 28. Employee. Junction. ADL(Act. ID 11, ESSN 2) 29. Employee. Junction. CC(ESSN 2, Act. ID 12) 30. LVNjunction. ADMIN(ESSN 2, Act. ID 11, Admin. MID 11 d, Med. ID 14) 32. Patient. Junction. Carecenter Act. ID 12, PSSN 1) 32. LVNjunctin. NONIV(ESSN 2, Proced. ID 10) 33. RNAjunction. ASSESS(ESSN 2, Act. ID 12 c) 34. RNAjunctin. REHAB(rehab. ID 11 e, ESSN 2) 35. RNjunction. ACTTYPE(Act. ID 11, ESSN 2) 36. Insurance. Plan(Plan. ID, Insur. Company)

Screenshot

Normalization 1 Analysis Visiting(VID, PSSN, Vmonth, Vday, Vyear, Vfname, Vlname) Not in 2 NF because partial dependencies exist Functional dependencies: {VID} ⇒ {Vfname, Vlname} The relationship can be normalized to 2 NF by removing the partial dependencies: Visitor(VID, Vfname, Vlname) Visit(VID, PSSN, Vmonth, Vday, Vyear) The relation is also in 3 NF because no non-prime attributes determine another non-prime attribute.

Normalization 2 Analysis Insurance(PSSN, Insur. Company, Plan. ID, Member. ID) The following functional dependencies hold: {PSSN, Insur. Company} ⇒ {Plan. ID, Member. ID} {Member. ID, Insur. Company} ⇒ {PSSN, Plan. ID} {Plan. ID} ⇒ {Insur. Company} • Still in 3 NF because the Insur. Company attribute is a prime attribute • Not in BCNF because Plan. ID is not a primary key. Normalizing to BCNF: Patient. Insurance(PSSN, Plan. ID, Member. ID) Insurance. Plans(Plan. ID, Insur. Company)

Error Tracking Query Objective: • Tracks number of errors committed by Number of Employees per Shift, Employee, Shift, and Number beds full per shift, and calculates Chi Square correlation Application: • Allows the client to pinpoint the problem areas at the facility in order to minimize errors and improve the quality of life for patients. [Query name: Errors by Employee] SELECT Error. Junction. Emp. ESSN, Count(*) AS Number_of_Errors FROM Error. Junction. Emp GROUP BY Error. Junction. Emp. ESSN UNION SELECT Employee. ESSN, 0 FROM Employee WHERE Employee. ESSN not IN (SELECT Error. Junction. Emp. ESSN FROM Error. Junction. Emp); [Query name: Avg. Err. Emp] SELECT Avg([Errors by Employee]. Number_of_Errors) AS Avg. Err FROM [Errors by Employee]; [Query name: Error-Employee Correlation] SELECT Sum(([Errors by Employee]. Number_of_Errors - Avg. Err. Emp. Avg. Err)^2) / Sum(Avg. Err. Emp. Avg. Err) AS Chi. Square, IIf(Sum(([Errors by Employee]. Number_of_Errors - Avg. Err. Emp. Avg. Err)^2) / Sum(Avg. Err. Emp. Avg. Err) > Sum(Max. Chi. Square), "95% chance that a correlation exists: see 'Errors by Employee' for more details", "No correlation exists") AS Correlation FROM [Errors by Employee], Avg. Err. Emp, Max. Chi. Square;

Error Tracking Query, cont. Errors by Number of Employees per Shift Errors by Employee

Loneliness Query Objective: • To mitigate loneliness among patients by forecasting the number of expected visits in the next month using a weighted moving average. Application: • Client can then schedule volunteers accordingly, focusing on those patients with the fewest expected visitors. SQL: SELECT DISTINCT pvj. PSSN, p. Fname, p. Lname, ((0. 5*Count 1. count 1)+(0. 3*Count 2. count 2)+(0. 2*Count 3. count 3)) AS Next_Month_Forecast FROM patient AS p, Count 1 INNER JOIN ((Count 3 INNER JOIN Pat. Vis. Junction AS pvj ON Count 3. PSSN=pvj. PSSN) INNER JOIN Count 2 ON pvj. PSSN=Count 2. PSSN) ON Count 1. PSSN=pvj. PSSN WHERE (((pvj. PSSN)=Count 1. PSSN And (pvj. PSSN)=Count 2. PSSN And (pvj. PSSN)=Count 3. PSSN) And ((p. PSSN)=pvj. PSSN));

Sample Report of Loneliness Query

Location Tracking Query Objective: Query will output a floor plan of the facility, providing a mapping of: 1) Patients with specified illness, and average distance between afflicted patients, OR 2) Occupied beds Application: • Allow the client to track and analyze various types of information in a visual manner. SQL: Select b. xcoordinate, b. ycoordinate From bed b patient p pre-existingmedicalhistory pmh Where bed. Bed. ID=p. Bed. ID and pmh. flu=’yes’; Matlab: A=[x y]; dist=[0]; for i=1: length(x)-1 for j=1+i: length(x) dist(end+1)=((A(i, 2)-A(j, 2))^2+(A(i, 1)-A(j, 1))^2)^. 5; end dist(dist==0)=[]; z=factorial(length(x))/(factorial(2) *factorial(length(x)-2)); average=sum(dist)/z Average=292. 1 Returns Average distance between infected beds

Location Tracking Query

Schedule Optimization Query Objective: • Query outputs Pearson Correlation Coefficient, which can be used in a linear program to determine the ideal number of employees to minimize the number of mistakes. SQL: Returns correlation coefficient (Errors vs. # Employees) SELECT SUM((Number_of_Employees - Avg. x_avg)*(Number_of_Errors- Avg. y_avg)) / SQR(SUM((Number_of_Employees - Avg. x_avg)^2) * SUM((Number_of_Errors - Avg. y_avg)^2)) AS pearson_r FROM Errorsvs. Emp, [Avg]; Correlation coefficient is inputted into AMPL Application: • Client can use this query to determine how many employees to place on each shift, to reduce the overall errors.

EOQ Query Objective: • It calculates the quantity to order, the reorder point, and the remaining days of supply. It also calculates the average number of days in which the supplier has been delayed Application • Indicates low levels of inventory, and whether the medicine should be ordered now or later, taking into account the average delay of the supplier. Q Reorder point if LD=0 R Reorder point if LD>0 t

EOQ Query SELECT b. Med. ID AS Med. ID, b. Quantity_to_Order AS Quantity_to_Order, Round((m. Lead. Time + b. Avg. No. Days. Delayed) * m. Demand, 2) AS Reorder_Point, Round((g. Current. Inventory - (((m. Lead. Time + b. Avg. No. Days. Delayed) * m. Demand))) / m. Demand) AS Remaining_Days, IIf(g. Current. Inventory <= (Round((m. Lead. Time + b. Avg. No. Days. Delayed) * m. Demand)), "Order Now", "Wait") AS Indication, Round(Sum(Date. Diff("d", o. Promised. Date, o. Date. Received)) / Count(o. Med. ID), 2) AS New. Avg. No. Days. Delayed FROM General. Inventory AS g INNER JOIN (([EOQ Theoretical] AS b INNER JOIN Medication AS m ON b. Med. ID = m. Med. ID) INNER JOIN Med. Orders. Query AS o ON b. Med. ID = o. Med. ID) ON g. Med. ID = m. Med. ID WHERE (((b. Med. ID) = [m]. [Med. ID] And (b. Med. ID) = [o]. [Med. ID])) GROUP BY b. Med. ID, b. Quantity_to_Order, Round((m. Lead. Time + b. Avg. No. Days. Delayed) * m. Demand, 2), Round((g. Current. Inventory - (((m. Lead. Time + b. Avg. No. Days. Delayed) * m. Demand))) / m. Demand), IIf(g. Current. Inventory <= (Round((m. Lead. Time + b. Avg. No. Days. Delayed) * m. Demand)), "Order Now", "Wait");

Report 1: Inventory

Report 2: Errors by Employee

Form 1: Initial Assessment

Form 2: New Patient Record

Thank you, and a special thank you to Jaspreet Singh Buttar, R. N.