ehth medical corp. database damon wilsonkevin yein andy changsai rakchart mike leeshehzad wadalawala...
Post on 21-Dec-2015
214 views
TRANSCRIPT
![Page 1: EHTH Medical Corp. Database Damon WilsonKevin Yein Andy ChangSai Rakchart Mike LeeShehzad Wadalawala Larry HuanPaolo Vincenti Dark Horse Consulting presents…](https://reader030.vdocuments.us/reader030/viewer/2022032704/56649d595503460f94a39b60/html5/thumbnails/1.jpg)
EHTH Medical Corp. DatabaseEHTH Medical Corp. Database
Damon WilsonDamon Wilson Kevin YeinKevin Yein
Andy ChangAndy Chang Sai RakchartSai Rakchart
Mike LeeMike Lee Shehzad WadalawalaShehzad Wadalawala
Larry HuanLarry Huan Paolo VincentiPaolo Vincenti
Dark Horse Dark Horse ConsultingConsultingpresents…presents…
![Page 2: EHTH Medical Corp. Database Damon WilsonKevin Yein Andy ChangSai Rakchart Mike LeeShehzad Wadalawala Larry HuanPaolo Vincenti Dark Horse Consulting presents…](https://reader030.vdocuments.us/reader030/viewer/2022032704/56649d595503460f94a39b60/html5/thumbnails/2.jpg)
Presentation OverviewPresentation Overview
Company Introduction Company Introduction EER DiagramEER DiagramRelational SchemaRelational SchemaNormalization AnalysisNormalization AnalysisIn-Depth Query AnalysisIn-Depth Query Analysis
![Page 3: EHTH Medical Corp. Database Damon WilsonKevin Yein Andy ChangSai Rakchart Mike LeeShehzad Wadalawala Larry HuanPaolo Vincenti Dark Horse Consulting presents…](https://reader030.vdocuments.us/reader030/viewer/2022032704/56649d595503460f94a39b60/html5/thumbnails/3.jpg)
EHTH Medical CorporationEHTH Medical Corporation
– Two doctor private practice.Two doctor private practice.
– 2000 active patients2000 active patients
– Based in San Jose, CA.Based in San Jose, CA.
– General medicineGeneral medicine
– Specialization in Specialization in pulmonologypulmonology and and oncologyoncology..
![Page 4: EHTH Medical Corp. Database Damon WilsonKevin Yein Andy ChangSai Rakchart Mike LeeShehzad Wadalawala Larry HuanPaolo Vincenti Dark Horse Consulting presents…](https://reader030.vdocuments.us/reader030/viewer/2022032704/56649d595503460f94a39b60/html5/thumbnails/4.jpg)
Database GoalsDatabase Goals
• Provide EHTH Medical with an efficient means of Provide EHTH Medical with an efficient means of storing and retrieving informationstoring and retrieving information
• Evaluate and study related risk factors for particular Evaluate and study related risk factors for particular diseasesdiseases
• Increase the flexibility of EHTH Medical’s information Increase the flexibility of EHTH Medical’s information system.system.
![Page 5: EHTH Medical Corp. Database Damon WilsonKevin Yein Andy ChangSai Rakchart Mike LeeShehzad Wadalawala Larry HuanPaolo Vincenti Dark Horse Consulting presents…](https://reader030.vdocuments.us/reader030/viewer/2022032704/56649d595503460f94a39b60/html5/thumbnails/5.jpg)
EER DiagramEER Diagram
![Page 6: EHTH Medical Corp. Database Damon WilsonKevin Yein Andy ChangSai Rakchart Mike LeeShehzad Wadalawala Larry HuanPaolo Vincenti Dark Horse Consulting presents…](https://reader030.vdocuments.us/reader030/viewer/2022032704/56649d595503460f94a39b60/html5/thumbnails/6.jpg)
EER DiagramEER Diagram
![Page 7: EHTH Medical Corp. Database Damon WilsonKevin Yein Andy ChangSai Rakchart Mike LeeShehzad Wadalawala Larry HuanPaolo Vincenti Dark Horse Consulting presents…](https://reader030.vdocuments.us/reader030/viewer/2022032704/56649d595503460f94a39b60/html5/thumbnails/7.jpg)
Relational SchemaRelational SchemaEntities/1:N Relationships
1. Employee (SSN, LName, FName, MName, Home_Phone)1a. Secretary (SSN1)1b. Physician (SSN1, pager_number) 1c. MedStaff (SSN1, Work_Days)
2. Patient(SSN, LName, FName, MName, PlanID, Name_of_Provider, Home_Address, Home_City, Home_State, Home_Zip Home_Phone, Work_Phone, Birth_Month, Birth_Day, Birth_Year, Sex, Marital_Status, Employer, Ethnicity, etc.
2a. General_Patient (SSN2) 2b. Cancer_Patient (SSN2, Area, Stage, Treatment)2c. Pulmonological_Patient (SSN2)
3. Condition (CID, CName, Description)4. Drug (DID, DName, Category, Active_Ingredient, Dosage)5. Family (FID, FName)6. Appointment (AID, Patient_SSN2, Time, Month, Day, Year, Secretary_SSN1a, Doctor_SSN1b, Claim_ID7, Cancel, Description)7. Claim (CID, Phy_SSN1b, InsProvID8, AID6, Fname_phys, Lname-phys)8. Insurance_Provider (InsProvID, Name, Address, City, State, Zip, Phone, Type)9. Insurance_Plan (PlanID, Ins_Prov_ID8, Copayment, Deductable, Name_of_provider) 10. Outside_Doctor(DocID, LName, FName, MName, Address, City, etc.
N:M Relationships 11. Treats (PhysSSN1b, PatientSSN2) 12. ParticipatesIn (StaffSSN1c, AID6, Month, Day, Year, Time)13. ReferredTo (OutPhysID10, PatientSSN2, RefferingPhySSN1b, Month, etc. 14. RelatedTo (SSN2, FID5)
15. Has (PatientSSN2, CondID3, Onset_Month, Onset_Year)16. Taking (SSN2, DrugID4, Start_Month, Start_Day, Start_Year, End_Month, End_Day, End_Year)17. AllergicTo (PatientSSN2, DrugID4)18. For (DrugID4, CondID3)19. NoToBeTakenWith (DrugID14, DrugID24) 20. Had(PatientSSN2, CondID3, End_Month, End_Year)
Multi Attributes21. Family_Siblings(FID5, FName, LName, MName, Status, Cause_of_Death)22. Family_Parents(FID5, FName, LName, MName, Status, Cause_of_Death)
23. Family_Dependents(FID5, FName, LName, MName, Status, Cause_of_Death)
24. Family_Notes(FID5, Info)25.
Patient_Emergency_Contact(PatientSSN2, FName, LName, MName, Phone, Relationship)26. Family_Maternal_Relatives(FID5, FName, LName, MName, Status, Cause_of_Death)27. Family_Paternal_Relatives(FID5, FName, LName, MName, Status, Cause_of_Death)
28. Patient_Hospital_Visit(PatientSSN2, Month, Day, Year, Reason, Treatment)29. Claim_Charges(ClaimID7, ProcedureID, Amount, Month, Day, Year)
![Page 8: EHTH Medical Corp. Database Damon WilsonKevin Yein Andy ChangSai Rakchart Mike LeeShehzad Wadalawala Larry HuanPaolo Vincenti Dark Horse Consulting presents…](https://reader030.vdocuments.us/reader030/viewer/2022032704/56649d595503460f94a39b60/html5/thumbnails/8.jpg)
Normalization AnalysisNormalization Analysis7. Claim7. Claim
CID Phy_SSN1b InsProvID8 AID6 Fname_phys Lname-phys
FD1
FD2
Transitive Dependency violates 3NF
NormalizationClaim1
CID Phy_SSN1b InsProvID8 AID6
Phy_SSN1b Fname_phys Lname-phys
Claim2
FD3
FD4
![Page 9: EHTH Medical Corp. Database Damon WilsonKevin Yein Andy ChangSai Rakchart Mike LeeShehzad Wadalawala Larry HuanPaolo Vincenti Dark Horse Consulting presents…](https://reader030.vdocuments.us/reader030/viewer/2022032704/56649d595503460f94a39b60/html5/thumbnails/9.jpg)
Normalization AnalysisNormalization Analysis9. Insurance_Plan9. Insurance_Plan
PlanID Ins_Prov_ID8 Copayment Deductable Name_of_provider
FD1
FD2
Partial Dependency violates 2NF
Normalization
Insurance_Plan1
Insurance_Plan2
FD3
FD4
PlanID Ins_Prov_ID8 Copayment Deductable
Ins_Prov_ID8 Name_of_provider
![Page 10: EHTH Medical Corp. Database Damon WilsonKevin Yein Andy ChangSai Rakchart Mike LeeShehzad Wadalawala Larry HuanPaolo Vincenti Dark Horse Consulting presents…](https://reader030.vdocuments.us/reader030/viewer/2022032704/56649d595503460f94a39b60/html5/thumbnails/10.jpg)
Scheduling QueryScheduling Query
EnglishEnglishFor a specific day, generate a schedule that lists the patient name, For a specific day, generate a schedule that lists the patient name, reason for appointment, and time of appointment, for all the day’s reason for appointment, and time of appointment, for all the day’s appointments.appointments.
Important aspectsImportant aspects• Determine free appointment timesDetermine free appointment times• Organize scheduling systemOrganize scheduling system• Identify trendsIdentify trends
![Page 11: EHTH Medical Corp. Database Damon WilsonKevin Yein Andy ChangSai Rakchart Mike LeeShehzad Wadalawala Larry HuanPaolo Vincenti Dark Horse Consulting presents…](https://reader030.vdocuments.us/reader030/viewer/2022032704/56649d595503460f94a39b60/html5/thumbnails/11.jpg)
Scheduling Query ScreenshotScheduling Query Screenshot
![Page 12: EHTH Medical Corp. Database Damon WilsonKevin Yein Andy ChangSai Rakchart Mike LeeShehzad Wadalawala Larry HuanPaolo Vincenti Dark Horse Consulting presents…](https://reader030.vdocuments.us/reader030/viewer/2022032704/56649d595503460f94a39b60/html5/thumbnails/12.jpg)
Total Revenue QueryTotal Revenue Query
EnglishEnglishList the total revenue earned by the practice for the last month, List the total revenue earned by the practice for the last month, grouped by insurance provider.grouped by insurance provider.
Important aspectsImportant aspects• Determine origin of clienteleDetermine origin of clientele• Focus marketing tacticsFocus marketing tactics• Insurance provider relationshipsInsurance provider relationships
![Page 13: EHTH Medical Corp. Database Damon WilsonKevin Yein Andy ChangSai Rakchart Mike LeeShehzad Wadalawala Larry HuanPaolo Vincenti Dark Horse Consulting presents…](https://reader030.vdocuments.us/reader030/viewer/2022032704/56649d595503460f94a39b60/html5/thumbnails/13.jpg)
Total Revenue Query ScreenshotTotal Revenue Query Screenshot
![Page 14: EHTH Medical Corp. Database Damon WilsonKevin Yein Andy ChangSai Rakchart Mike LeeShehzad Wadalawala Larry HuanPaolo Vincenti Dark Horse Consulting presents…](https://reader030.vdocuments.us/reader030/viewer/2022032704/56649d595503460f94a39b60/html5/thumbnails/14.jpg)
Patient Frequency QueryPatient Frequency Query
EnglishEnglishList patient name and insurance plan for all patients whoList patient name and insurance plan for all patients whohave had an appointment in every month of the selected year. have had an appointment in every month of the selected year.
Double Negative JargonDouble Negative JargonList all patients for which there does not exist a month in the selectedList all patients for which there does not exist a month in the selectedyear for which there was no appointment.year for which there was no appointment.
Important AspectsImportant Aspects• Identify important / critical patientsIdentify important / critical patients• Identify hypochondriacsIdentify hypochondriacs
![Page 15: EHTH Medical Corp. Database Damon WilsonKevin Yein Andy ChangSai Rakchart Mike LeeShehzad Wadalawala Larry HuanPaolo Vincenti Dark Horse Consulting presents…](https://reader030.vdocuments.us/reader030/viewer/2022032704/56649d595503460f94a39b60/html5/thumbnails/15.jpg)
Patient Frequency ScreenshotPatient Frequency Screenshot
![Page 16: EHTH Medical Corp. Database Damon WilsonKevin Yein Andy ChangSai Rakchart Mike LeeShehzad Wadalawala Larry HuanPaolo Vincenti Dark Horse Consulting presents…](https://reader030.vdocuments.us/reader030/viewer/2022032704/56649d595503460f94a39b60/html5/thumbnails/16.jpg)
Forecasting QueryForecasting Query
EnglishEnglishUse a moving average to forecast the number of patients that EHTH Use a moving average to forecast the number of patients that EHTH Medical will see in the next month.Medical will see in the next month.
Interesting aspectsInteresting aspects• Predict future demandPredict future demand• Helps determine staffing and supply requirementsHelps determine staffing and supply requirements
![Page 17: EHTH Medical Corp. Database Damon WilsonKevin Yein Andy ChangSai Rakchart Mike LeeShehzad Wadalawala Larry HuanPaolo Vincenti Dark Horse Consulting presents…](https://reader030.vdocuments.us/reader030/viewer/2022032704/56649d595503460f94a39b60/html5/thumbnails/17.jpg)
Forecasting Query ScreenshotForecasting Query Screenshot
![Page 18: EHTH Medical Corp. Database Damon WilsonKevin Yein Andy ChangSai Rakchart Mike LeeShehzad Wadalawala Larry HuanPaolo Vincenti Dark Horse Consulting presents…](https://reader030.vdocuments.us/reader030/viewer/2022032704/56649d595503460f94a39b60/html5/thumbnails/18.jpg)
Hypothesis QueryHypothesis Query
EnglishEnglishTests hypothesis that Asian ethnicity is correlated to the onsetTests hypothesis that Asian ethnicity is correlated to the onsetof cancer. of cancer.
Returns 95% Confidence interval for the number ofReturns 95% Confidence interval for the number ofAsians that should have cancer if the the hypothesis is true.Asians that should have cancer if the the hypothesis is true.Also returns actual number of cases of cancer among Asians.Also returns actual number of cases of cancer among Asians.
Interesting AspectsInteresting Aspects• Specialize treatment towards different patient groupsSpecialize treatment towards different patient groups• Warn high risk patientsWarn high risk patients• Can vary conditions and ethnicitiesCan vary conditions and ethnicities
![Page 19: EHTH Medical Corp. Database Damon WilsonKevin Yein Andy ChangSai Rakchart Mike LeeShehzad Wadalawala Larry HuanPaolo Vincenti Dark Horse Consulting presents…](https://reader030.vdocuments.us/reader030/viewer/2022032704/56649d595503460f94a39b60/html5/thumbnails/19.jpg)
Hypothesis Query ScreenshotHypothesis Query Screenshot
![Page 20: EHTH Medical Corp. Database Damon WilsonKevin Yein Andy ChangSai Rakchart Mike LeeShehzad Wadalawala Larry HuanPaolo Vincenti Dark Horse Consulting presents…](https://reader030.vdocuments.us/reader030/viewer/2022032704/56649d595503460f94a39b60/html5/thumbnails/20.jpg)
Form ScreenshotsForm Screenshots
![Page 21: EHTH Medical Corp. Database Damon WilsonKevin Yein Andy ChangSai Rakchart Mike LeeShehzad Wadalawala Larry HuanPaolo Vincenti Dark Horse Consulting presents…](https://reader030.vdocuments.us/reader030/viewer/2022032704/56649d595503460f94a39b60/html5/thumbnails/21.jpg)
![Page 22: EHTH Medical Corp. Database Damon WilsonKevin Yein Andy ChangSai Rakchart Mike LeeShehzad Wadalawala Larry HuanPaolo Vincenti Dark Horse Consulting presents…](https://reader030.vdocuments.us/reader030/viewer/2022032704/56649d595503460f94a39b60/html5/thumbnails/22.jpg)
Questions…Questions…