220 likes | 223 Views
This presentation by Dark Horse Consulting introduces the EHTH Medical Corp. database, covering its company introduction, EER diagram, relational schema, normalization analysis, and in-depth query analysis.
E N D
Dark Horse Consulting presents… EHTH Medical Corp. Database Damon Wilson Kevin Yein Andy Chang Sai Rakchart Mike Lee Shehzad Wadalawala Larry Huan Paolo Vincenti
Presentation Overview • Company Introduction • EER Diagram • Relational Schema • Normalization Analysis • In-Depth Query Analysis
EHTH Medical Corporation • Two doctor private practice. • 2000 active patients • Based in San Jose, CA. • General medicine • Specialization in pulmonology and oncology.
Database Goals • Provide EHTH Medical with an efficient means of storing and retrieving information • Evaluate and study related risk factors for particular diseases • Increase the flexibility of EHTH Medical’s information system.
Relational Schema Entities/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 Attributes 21. 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)
Normalization Analysis FD1 7. Claim FD2 Transitive Dependency violates 3NF Normalization Claim1 FD3 Claim2 FD4
Normalization Analysis Normalization 9. Insurance_Plan FD1 FD2 Partial Dependency violates 2NF Normalization Insurance_Plan1 FD3 Insurance_Plan2 FD4
Scheduling Query • English • For a specific day, generate a schedule that lists the patient name, • reason for appointment, and time of appointment, for all the day’s • appointments. • Important aspects • Determine free appointment times • Organize scheduling system • Identify trends
Total Revenue Query • English • List the total revenue earned by the practice for the last month, • grouped by insurance provider. • Important aspects • Determine origin of clientele • Focus marketing tactics • Insurance provider relationships
Patient Frequency Query • English • List patient name and insurance plan for all patients who • have had an appointment in every month of the selected year. • Double Negative Jargon • List all patients for which there does not exist a month in the selected • year for which there was no appointment. • Important Aspects • Identify important / critical patients • Identify hypochondriacs
Forecasting Query • English • Use a moving average to forecast the number of patients that EHTH • Medical will see in the next month. • Interesting aspects • Predict future demand • Helps determine staffing and supply requirements
Hypothesis Query • English • Tests hypothesis that Asian ethnicity is correlated to the onset • of cancer. • Returns 95% Confidence interval for the number of • Asians that should have cancer if the the hypothesis is true. • Also returns actual number of cases of cancer among Asians. • Interesting Aspects • Specialize treatment towards different patient groups • Warn high risk patients • Can vary conditions and ethnicities