1 / 22

EHTH Medical Corp. Database Presentation - Company Introduction, EER Diagram, Relational Schema, Normalization Analysis,

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.

miland
Download Presentation

EHTH Medical Corp. Database Presentation - Company Introduction, EER Diagram, Relational Schema, Normalization Analysis,

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. Dark Horse Consulting presents… EHTH Medical Corp. Database Damon Wilson Kevin Yein Andy Chang Sai Rakchart Mike Lee Shehzad Wadalawala Larry Huan Paolo Vincenti

  2. Presentation Overview • Company Introduction • EER Diagram • Relational Schema • Normalization Analysis • In-Depth Query Analysis

  3. EHTH Medical Corporation • Two doctor private practice. • 2000 active patients • Based in San Jose, CA. • General medicine • Specialization in pulmonology and oncology.

  4. 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.

  5. EER Diagram

  6. EER Diagram

  7. 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)

  8. Normalization Analysis FD1 7. Claim FD2 Transitive Dependency violates 3NF Normalization Claim1 FD3 Claim2 FD4

  9. Normalization Analysis Normalization 9. Insurance_Plan FD1 FD2 Partial Dependency violates 2NF Normalization Insurance_Plan1 FD3 Insurance_Plan2 FD4

  10. 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

  11. Scheduling Query Screenshot

  12. 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

  13. Total Revenue Query Screenshot

  14. 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

  15. Patient Frequency Screenshot

  16. 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

  17. Forecasting Query Screenshot

  18. 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

  19. Hypothesis Query Screenshot

  20. Form Screenshots

  21. Questions…

More Related