New Year Province EHR System A Customized Solution to Your EHR Needs

1. New Year Province EHR SystemA Customized Solution to Your EHR Needs Janessa Griffith Victor Guo Marianne Leenaerts Will Yang Ryan Zhang

2. New Year Province EHR SystemA Customized Solution to Your EHR Needs Background and introduction Project conduct Demonstration Issues in conducting the project Learning outcomes Conclusion

3. Background and Introduction Goal: To develop an EHR for all hospitals in a province Purpose: To increase Patient safety Wait-time monitoring Data integration, sharing and analytics Communication Collaboration Efficiency Cost-savings

4. Background and Introduction (Cont’d) System features Create Update Search Delete Alert Provide feedback

5. Background and Introduction (Cont’d) Project scope Work breakdown structure Entity relationship diagram as blueprint SQL statements to produce database Java to interface the system Organization and project planning

6. Work Breakdown Structure Background and Introduction (Cont’d)

7. Work Breakdown Structure Background and Introduction (Cont’d) H

8. Project Conduct Entity Relationship Diagram 13 Entities Primary, Foreign, and Composite key Cardinalities

10. Project Conduct(Cont’d) Create Tables /* Create Patient table */ create table patient ( pt_id number(10) , pt_lst_nm varchar2(20) not null , pt_frst_nm varchar2(20) not null , pt_gender varchar2(1) , pt_dob date , pt_adrs varchar2(30) , pt_cty varchar2(20) , pt_prvnc varchar2(20) , pt_pstl_cd varchar2(10) , pt_phone number(15) , pt_phn number(10) , pt_emrgncy_cntct varchar2(50) , pt_emrgncy_phone number(15) , constraint patient_PK primary key (pt_id) , constraint patient_CK1 check (pt_id between 1000000000 and 1999999999) , constraint patient_CK2 check (pt_phn between 9000000000 and 9999999999) ) ;

11. Project Conduct(Cont’d) Patient Table

12. Project Conduct(Cont’d) Create Sequences /* Create Physician_ID sequence */ create sequence phy_seq start with 30001 increment by 1 maxvalue 39999; /* Insert records into the Physician table*/ insert into physician values ( (phy_seq.nextval) , 'Iwata' , 'Hitomi' , '2893 Dallas Rd.' , 'Langford' , 'NY' , 'V1G 2M8' , 2504745555 , 'Dermatology' );

13. Project Conduct(Cont’d) More Sequences /* Create Medication_ID sequence */ create sequence mdctn_seq start with 40000001 increment by 1 maxvalue 49999999; /* Create Prescription_ID sequence */ create sequence px_seq start with 70000001 increment by 1 maxvalue 79999999; /* Create Diagnosis_ID sequence */ create sequence dgn_seq start with 800000001 increment by 1 maxvalue 899999999;

14. Project Conduct(Cont’d) Primary & Foreign Keys /* Create Visit table */ create table visit ( vst_id number(9) , vst_dx varchar2(500) not null , vst_dt date not null , vst_tp varchar2(20) , pt_idnumber(10) , hsptl_id number(3) , constraint visit_PKprimary key (vst_id) , constraint visit_FK1 foreign key (pt_id) references patient (pt_id) on delete set null , constraint visit_FK2 foreign key (hsptl_id) references hospital (hsptl_id) on delete set null , constraint visit_CKcheck (vst_id between 200000000 and 299999999) );

15. Project Conduct(Cont’d) Composite Key /* Create Patient_Allergy table */ create table patient_allergy ( allrgy_id varchar2(8) not null , pt_id number(10) not null , constraint patient_allergy_PK primary key (allrgy_id,pt_id) , constraint patient_allergy_CK1 check (allrgy_id >= 100000 and allrgy_id <= 199999) , constraint patient_allergy_CK2 check (pt_id between 1000000000 and 1999999999) );

16. Project Conduct(Cont’d) Create Triggers • PL/SQL is used to create triggers • Patient_Allergy • If a patient has no allergy, an error message will display when an allergy is created for this patient in the Patient Allergy table • Imaging • If a CT scan is entered, the message “Warning, radiation exposure” will display

17. Demonstration New Year Province-Electronic Health Record

18. Issues encountered Technical issues SQL sequences SQL insertion complication Triggers ERD relationships

19. Issues encountered (Cont’d) Non-technical issues Group schema Timing

20. Issues encountered (Cont’d) Estimate Hjhj Actual

21. Learning outcomes Project planning Producing and accepting feedback Building ERDs with MS Visio Using SQL in Oracle 10g Express Java interfacing Communicating across disciplines

22. Learning outcomes (Cont’d) Communication • Met with group every week (5 hours) • Asked professor for help, as necessary • Prepared for meetings with agendas • Discussed: • Achievements to date • Next steps • Outstanding issues • Emailed and teleconferenced between meetings • Summarized meetings with minutes

24. Conclusion “The question in health care is not whether the information exists, but rather providing access to the right information at the right time.” -- Glen Tullman

25. Questions ?

26. Try yourself User name: User Password: User http://154.5.121.25:9000/NYP-EHR-stage/

27. Trigger

28. Trigger(Cont’d)