1 / 27

Napa Valley Care Center Database

Napa Valley Care Center Database. Damla Bayindir Ivette Bigit Robert Dooley Michael Ellison Ashwini Purohit Ma Than Than Thaik Jannat Dena Vaziri. Final Presentation Overview. Client Description Our Goal EER Relational Schema Relational Schema Screenshot Normalization Analysis

karim
Télécharger la présentation

Napa Valley Care Center Database

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Napa Valley Care Center Database DamlaBayindir IvetteBigit Robert Dooley Michael Ellison AshwiniPurohit Ma Than ThanThaik Jannat Dena Vaziri

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

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

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

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

  6. Relational Schema 1.Patient(PSSN, Fname, Lname, PhoneNum, BedID5, PreMID13) 2.Employee(ESSN, EmpFName,EmpLName)2a. RN(ESSN2)2b. LVN(ESSN2) 2c. RNA(ESSN2) 2d. CNA(ESSN2) 3. Visitor(VID, Vfname, Vlname) 4.Permission(PID,PermissionName, Description) 5.Bed(BedID, RoomNum, XCoord, YCoord) 6.PatientInsurance (PSSN1, PlanID36, MemberID) 7.Shift(ShiftID, ShiftName, StartTime, EndTime) 8.Error( ErrorID,ShiftID7,ErrorName,ErrorDescription) 9.Supplier(SupID,SupName,Contact) 10. Procedures(ProcedID) 10a. ExternalTreatmentType(ProcedID10, ExternalName,ExternalDescription)

  7. Relational Schema (cont.) 10b. Non-IV(ActID11,NonlVName, Description)      10c. IV(ActID11,IVname,Description) 11.Activity Types(ActID,ActName,ActDescription)     11a. ADL(ActID11,ADLName,ADLDescription) 11b. Assessment(ActID,AssessName,AssessDescription) 11c. Treatment Types(ActID11 ,ProcedID10, ProcedID, TreatmentName, TreatmentDescription) 11d.Meal Admin(AdminMealID,ActID11,TimeServed) 11e. Rehab(RehabID,RehabName,RehabDescription) 11f. Medication Admin(AdminMID,ActID11,MedID14) 12. Care Center Activity(ActID,PSSN1,ESSN2,CCDate, CCName,CCDesc,) 12a. Center Treatment(ActID,PSSN1,ESSN2,CCDate,TTID11c, ProcedID,MedID14)

  8. Relational Schema (cont.) 12b. Center Prescription (ActID,PSSN1,ESSN2,CCDate ,MedID14,TTID11c) 12c. Center Assessment(ActID,PSSN1,ESSN2,CCDate,Scale (1-10)) 13. Pre-existing Medical History (PSSN1)13a. Observation History(PSSN1,ObsDate,ObsDescription)13b. Treatment History(PSSN1,ProcedID10,TreatmentName,         TreatmentDescription,TTID11c,AID ) 13c. Medication History(PSSN1,MedID13 MedDate,Medication) 14. Medication(MedID,MedName,Dosage, Frequency,SupID9, LeadTime,InterestRate,UnitCostSetupCost,Demand) 15. General Inventory Item(MedID14, InvName,InvType,CurrentInventory) 16. Food Item(FoodID,FoodName,FoodCalories) 17. PersonMedicationInventory(MedID14,PSSN1,Quantity) 18.EmployeeJunctionShift(ShiftID7,ESSN2) 19.BedjuctionShift(BedID5,ShiftID7) 20.BedAssessShift(BedID5,ShiftID7,Assessment)

  9. Relational Schema (cont.) 21.GivesPermission(PID4,PSSN1, PerDate, Acc/Rej) 23.ErrorJunctionEmp(ErrorID8,ESSN2) 24.PatVisJunc(VID3,PSSN1,VMonth, VDay,Vyear) 25. AdminmealJunctionFood(AdminMealID11d,FoodID16) 26. AdminMIDjunctionMED(MedID14,ActID11) 27. PrescrıbedMedication(PSSN,AdminMID11d,MedID14 ,StartDate, EndDate) 28. EmployeeJunctionADL(ActID11,ESSN2) 29. EmployeeJunctionCC(ESSN2,ActID12) 30. LVNjunctionADMIN(ESSN2,ActID11,AdminMID11d,MedID14) 32. PatientJunctionCarecenterActID12,PSSN1) 32. LVNjunctinNONIV(ESSN2,ProcedID10) 33. RNAjunctionASSESS(ESSN2,ActID12c) 34. RNAjunctinREHAB(rehabID11e,ESSN2) 35. RNjunctionACTTYPE(ActID11,ESSN2) 36. InsurancePlan(PlanID,InsurCompany)

  10. Screenshot

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

  12. Normalization 2 Analysis Insurance(PSSN, InsurCompany, PlanID, MemberID) The following functional dependencies hold: {PSSN, InsurCompany} ⇒ {PlanID, MemberID} {MemberID, InsurCompany} ⇒ {PSSN, PlanID} {PlanID} ⇒ {InsurCompany} • Still in 3NF because the InsurCompany attribute is a prime attribute • Not in BCNF because PlanID is not a primary key.   Normalizing to BCNF:PatientInsurance(PSSN, PlanID, MemberID)InsurancePlans(PlanID,InsurCompany)

  13. Error Tracking Query [Query name: Errors by Employee]SELECT ErrorJunctionEmp.ESSN, Count(*) AS Number_of_ErrorsFROM ErrorJunctionEmpGROUP BY ErrorJunctionEmp.ESSNUNIONSELECT Employee.ESSN, 0FROM EmployeeWHERE Employee.ESSN not IN (SELECT ErrorJunctionEmp.ESSNFROM ErrorJunctionEmp);[Query name: AvgErrEmp]SELECT Avg([Errors by Employee].Number_of_Errors) AS AvgErrFROM [Errors by Employee];[Query name: Error-Employee Correlation]SELECT Sum(([Errors by Employee].Number_of_Errors - AvgErrEmp.AvgErr)^2) / Sum(AvgErrEmp.AvgErr) AS ChiSquare, IIf(Sum(([Errors by Employee].Number_of_Errors - AvgErrEmp.AvgErr)^2) / Sum(AvgErrEmp.AvgErr) > Sum(MaxChiSquare.MaxChiSquare), "95% chance that a correlation exists: see 'Errors by Employee' for more details", "No correlation exists") AS CorrelationFROM [Errors by Employee], AvgErrEmp, MaxChiSquare; 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.

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

  15. 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*Count1.count1)+(0.3*Count2.count2)+(0.2*Count3.count3)) AS Next_Month_ForecastFROM patient AS p, Count1 INNER JOIN ((Count3 INNER JOIN PatVisJunction AS pvj ON Count3.PSSN=pvj.PSSN) INNER JOIN Count2 ON pvj.PSSN=Count2.PSSN) ON Count1.PSSN=pvj.PSSNWHERE (((pvj.PSSN)=Count1.PSSN And (pvj.PSSN)=Count2.PSSN And (pvj.PSSN)=Count3.PSSN) And ((p.PSSN)=pvj.PSSN));

  16. Sample Report of Loneliness Query

  17. Location Tracking Query Objective: Query will output a floor plan of the facility, providing a mapping of: • Patients with specified illness, and average distance between afflicted patients, OR • 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-existingmedicalhistorypmh Where bed.BedID=p.BedID 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 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

  18. Location Tracking Query

  19. Schedule Optimization Query 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 ErrorsvsEmp, [Avg]; Correlation coefficient is inputted into AMPL 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. Application: • Client can use this query to determine how many employees to place on each shift, to reduce the overall errors.

  20. 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. R t Q Reorder point if LD=0 Reorder point if LD>0

  21. EOQ Query SELECT b.MedID AS MedID, b.Quantity_to_Order AS Quantity_to_Order, Round((m.LeadTime + b.AvgNoDaysDelayed) * m.Demand, 2) AS Reorder_Point, Round((g.CurrentInventory - (((m.LeadTime + b.AvgNoDaysDelayed) * m.Demand))) / m.Demand) AS Remaining_Days, IIf(g.CurrentInventory <= (Round((m.LeadTime + b.AvgNoDaysDelayed) * m.Demand)), "Order Now", "Wait") AS Indication, Round(Sum(DateDiff("d", o.PromisedDate, o.DateReceived)) / Count(o.MedID), 2) AS NewAvgNoDaysDelayedFROM GeneralInventory AS g INNER JOIN (([EOQ Theoretical] AS b INNER JOIN Medication AS m ON b.MedID = m.MedID) INNER JOIN MedOrdersQuery AS o ON b.MedID = o.MedID) ON g.MedID = m.MedIDWHERE (((b.MedID) = [m].[MedID] And (b.MedID) = [o].[MedID]))GROUP BY b.MedID, b.Quantity_to_Order, Round((m.LeadTime + b.AvgNoDaysDelayed) * m.Demand, 2), Round((g.CurrentInventory - (((m.LeadTime + b.AvgNoDaysDelayed) * m.Demand))) / m.Demand), IIf(g.CurrentInventory <= (Round((m.LeadTime + b.AvgNoDaysDelayed) * m.Demand)), "Order Now", "Wait");

  22. Report 1: Inventory

  23. Report 2: Errors by Employee

  24. Form 1: Initial Assessment

  25. Form 2: New Patient Record

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

More Related