Applied Database 1 COP 4708 Project Assignment # 1 Introduction: A physician office (private practice) approached yo i an effort to convert their paperwork process into a health information database. The physician provided a copy of the paperwork used in the office to collect patient information. The physician also explained the need to secure patient information according to HIPPA rules and regulations. Additional information will be provided upon request The physician would like to add to patient records results from abs and imaging. In addition, the physician would like to link with a list of pharmacies in the area and send patient prescription electronically. As a database expert you will be developing patient database and all required tables using relational database approach Requirement: 1. Analyze the provided forms below identifying the entities that should be tracked in the 2. Identify the fields (attributes) associated with each entity, listing possible candidate keys 3. Based on your analysis and the given forms identify all possible functional dependencies system (determinants) (remember that those functional dependencies will not only assist in creating the proper tables but also will assist in understanding relationships between tables) 4. Create a separate entity to secure database (just write the name of the entity and its attributes) Forms and Documentation Provided: The following are copies of the paper forms used in the office. Use these form to complete the assignment requirements above PATIENT INFORMATION Thank you for choosing our office! In order to serve you properly, we need the following Please print. All information will be confidential Date SSN Patient Name Patient f Home phone State Female Birthdate City Check appropriate box: Minor Single Married Divorced widowed Patient's or parent's employer Business address Spouse or parent's name If patient is a student, name of school college Whom may we thank for referring you Person to contact in case of emergency In case of a medical emergency, if the patient is of school age 15+,it is all right to treat in my absence. Separated Work phone City Work phon City State Parent or guardian signature Responsible Party Name of person responsible for this account Relationship to patient Home phone Financial institution Work phone Driver's license s this person currenthy a patient at our office Insurance Information Name of insured Yes Relationship to patient Date employed Social Security Number Work phone Name of employer Addeess of employer Insurance company Ins. Co, address How much is your deductible? State Union or local # Zip Group # City Zip How much have you used Max. annual benefit Do you have any additional insurance? Yes No If the Name of insured Relationship to patient Date employed Social Security Number Name of employer Address of employer Insurance company Ins. Co. address How much is your deductible Work phone City Group # City State Union or local # State How much have you used Max, annual benefit? I authorize release of any information concerning my for my child's) heath care,advice and treatment provided for the purpose of evaluating and administering claims for insurance benefits. I also hereby authorize payment of insurance benefits otherwise payable to me directly to the doctor Signature of patient or parent if minor NEW PATIENT HEALTH HISTORY FORM Patient Name: Birth date:Date Referring Physician: Address Pharmacy Name: Phone Number: Reason for today's visit: Please describe this problem PRIOR SURGERIES CURRENT/ PRIOR ILLNESSES/ INJURIES Please list ALL medications (prescription and non-prescription) that you take. (Include herbal remedies, vitamins, over- the-counter, street drugs, prescriptions etc.) MEDICATION DOSAGE MEDICATION DOSAGE Do you take any blood thinning products such as Vitamin E, Plavix, Coumadin, or Aspirin? O NO YES Do you have an , environmental, or drug alier s? NO YES ALLERGY TYPE REACTION Do you smoke? n NO and Never have YES (Please explain below) TYPE OF SMOKING HOW MUCH HOW LONG Do you drink alcohol NO and Never have Socially OnlyDailyBeer/ WineHard Liquor Hand Dominance: RIGHT LEFT Please describe any family health issue below: FAMILY HISTORY GOOD/ NONE ! UNKNOWN ILLNE REASON FOR DEATH MOTHER FATHER SIBLING(S OTHER HEREDITARY ILLNESS Patient Signature: Date: Physician Signature: Date Reviewed: J HEALTH HISTORY FORM 2 Do you have or have you ever had any of the following Symptoms/Illness NO YES, Explain Symptoms/ Illness NO YES, Explain Fever or Chills Breast Abnormalities Nipple Discharge Last Mammogram Changes in Moles Weight Loss Date: HIV/Other Blood Diseases Bleeding Disorders Endocrine Thyroid Problems History of Keloids Neurological Problems Headaches GENITOURINARY Genital or Oral Herpes S.T.D.'s Blood in Urine Urinary Tract Infection Problems Urinating Prostate Problems Kidney Problems Eyes Vision Problems Arthritis Mobility/Joint Problems GASTROINTESTINAL Constipation Blood in Stool Liver Problems CARDIOVASCULAR Heart Problems Deep Vein ThrombosDVT Blood Clots in Lungs/Legs High Blood Pressure RESPIRATORY Hearing Problems Sinus Problems PSYCHLATRIC Mood Swings Anxiety/Depression Sleep Apnea Please list any other conditions/illnesses not indicated above: best of my know6ecte, this form ation as complete and correct. I understand that my responsbdy to irform my doctor there aro amy danes to my he Patient Signature: Date: Physician Signature: Date Reviewed: . Patient Name Outpatient Lab Order Form MI Mercy Lab Fax Number 641-428-7886 i1 ex Colection DateTim When Tests are Complete: Send Patient Home Send Patient to Office Page Provider on Page Call Results to Fax Results to 3. o Courtesy Copy to Medicare will pay only for tests that meet the Medicare coverage criteria and are reasonable and necessary to treat or diagnose an individual patient. Medicare does not pay for tests for which documentation, inclucing the patient record, does not support that the lests were reasonable and necessary. Medicare generally does not cover routine screening tests even if the physician or other authorized test orderer considers the tests appropriate for the paient Tests with an "behind them means that reflex testing may be perfarmed- Please refer to the Mercy Lab Test Index Antibody Screen ALTISGPT ASTISGOT T3 Free T4 Free Testosterone, Total and Free Triglyceride AST Hepais 8 Surface Antigen* HIV Medicare Screen Iron and Iron Binding Latex RA CRP Sensitive (Cardiac) CA125 CBC with No Diff CBC with Manual Dt CEA Cell Morphology LH LH Urinalysis Routine" itamin B12/Folate Vitamin B12 Vit D 25-Hydroxy CEA Microalbumin Urine PTHINTParathyroid Hormone Intact VD25H Chemistry Panels HDL HDL Cholesterol Prenatal Profile WHIM Prenatal Profile no HIV Basic Metabolic Panel LDL Direct Cholestera ATPN Dermatologv Panel CK CREAT VCCL CRT Creatinine .stain74vneur 2ett E' URawn Urne Total volume Drug Screen Random Urine PSA B PSA, Medicare Screen Lipid Panel Comprehensive Metabolic Panel Protein 24 hr Urine Total Valume. Renal Panel General Health Panel (CBCAD, TSH, CMPL) Glucose 1 hr Gestational Glycohemoglobin (A1C) HCG Quant Serum llt; lli kudleFaibi @3a Sedimentation Rate Drug Levels HGBXHemoglobin Last Dose Date and Time 2. Mayo# Sianature of Orderina Provider Physi ician Order Form -Ima ging Services PATIENT INFORMATION Date of Birth: Please call Patient Patient will call to schedule Authorization # REQUESTING PHYSICIAN INFORMATION Phone Referring Physician Signature Results (check all that apply) D E-mail report (e-mail) O Fax report: (fax #) O CD with Images Special Request Phone Report: (phone #) EXAM FOCUS Brain MRI Brain MRA Neck MRI Neck MRA MRI Cervical Spine Thoracic Lumbar wol contrast who contrast O Other (specify) | O Vagal Nerve Stimulator. Program both generator output current and magnet output current to OMA to the MRI Cervical Spine Diagnostic Brain O Sinus ChestAbdomen Pelvis Thoracic Lumbar wol contrast w/wo contrast Other O Extremity (specify) Screening O Others (specify): Mammogram Abdomen O Pelvis OB/GYN Ultrasound Other Bone Liver PETICT Other Barium Enema (please select): O With air contrast O Without air contrast Upper G.I. (please select: O With small bowel series O Without small bowel series O X-ray (specfy): General Radiology Volding Cystourethrogram Fluoro Other (specify): Vascular Lab Chronic Venous Exam OPPG'S ABrs with waveform Dialysis Graft Eval. Peripheral Arial Exam Upper ExtremityTranscranial Doppler Carotid O Graft Flow O Temporal Artery Lower Extremity | O Nielsen Cold Challenge O Finger OToe(s) O R Arterial Duplex OAbdomen (please select) O Renal O Mesenteric O Portal Hepatic OAAA ORenal Transplant Other (specify): Left Other Specify Rev 0UDB Scan to PO-7070 Applied Database 1 COP 4708 Project Assignment # 1 Introduction: A physician office (private practice) approached yo i an effort to convert their paperwork process into a health information database. The physician provided a copy of the paperwork used in the office to collect patient information. The physician also explained the need to secure patient information according to HIPPA rules and regulations. Additional information will be provided upon request The physician would like to add to patient records results from abs and imaging. In addition, the physician would like to link with a list of pharmacies in the area and send patient prescription electronically. As a database expert you will be developing patient database and all required tables using relational database approach Requirement: 1. Analyze the provided forms below identifying the entities that should be tracked in the 2. Identify the fields (attributes) associated with each entity, listing possible candidate keys 3. Based on your analysis and the given forms identify all possible functional dependencies system (determinants) (remember that those functional dependencies will not only assist in creating the proper tables but also will assist in understanding relationships between tables) 4. Create a separate entity to secure database (just write the name of the entity and its attributes) Forms and Documentation Provided: The following are copies of the paper forms used in the office. Use these form to complete the assignment requirements above PATIENT INFORMATION Thank you for choosing our office! In order to serve you properly, we need the following Please print. All information will be confidential Date SSN Patient Name Patient f Home phone State Female Birthdate City Check appropriate box: Minor Single Married Divorced widowed Patient's or parent's employer Business address Spouse or parent's name If patient is a student, name of school college Whom may we thank for referring you Person to contact in case of emergency In case of a medical emergency, if the patient is of school age 15+,it is all right to treat in my absence. Separated Work phone City Work phon City State Parent or guardian signature Responsible Party Name of person responsible for this account Relationship to patient Home phone Financial institution Work phone Driver's license s this person currenthy a patient at our office Insurance Information Name of insured Yes Relationship to patient Date employed Social Security Number Work phone Name of employer Addeess of employer Insurance company Ins. Co, address How much is your deductible? State Union or local # Zip Group # City Zip How much have you used Max. annual benefit Do you have any additional insurance? Yes No If the Name of insured Relationship to patient Date employed Social Security Number Name of employer Address of employer Insurance company Ins. Co. address How much is your deductible Work phone City Group # City State Union or local # State How much have you used Max, annual benefit? I authorize release of any information concerning my for my child's) heath care,advice and treatment provided for the purpose of evaluating and administering claims for insurance benefits. I also hereby authorize payment of insurance benefits otherwise payable to me directly to the doctor Signature of patient or parent if minor NEW PATIENT HEALTH HISTORY FORM Patient Name: Birth date:Date Referring Physician: Address Pharmacy Name: Phone Number: Reason for today's visit: Please describe this problem PRIOR SURGERIES CURRENT/ PRIOR ILLNESSES/ INJURIES Please list ALL medications (prescription and non-prescription) that you take. (Include herbal remedies, vitamins, over- the-counter, street drugs, prescriptions etc.) MEDICATION DOSAGE MEDICATION DOSAGE Do you take any blood thinning products such as Vitamin E, Plavix, Coumadin, or Aspirin? O NO YES Do you have an , environmental, or drug alier s? NO YES ALLERGY TYPE REACTION Do you smoke? n NO and Never have YES (Please explain below) TYPE OF SMOKING HOW MUCH HOW LONG Do you drink alcohol NO and Never have Socially OnlyDailyBeer/ WineHard Liquor Hand Dominance: RIGHT LEFT Please describe any family health issue below: FAMILY HISTORY GOOD/ NONE ! UNKNOWN ILLNE REASON FOR DEATH MOTHER FATHER SIBLING(S OTHER HEREDITARY ILLNESS Patient Signature: Date: Physician Signature: Date Reviewed: J HEALTH HISTORY FORM 2 Do you have or have you ever had any of the following Symptoms/Illness NO YES, Explain Symptoms/ Illness NO YES, Explain Fever or Chills Breast Abnormalities Nipple Discharge Last Mammogram Changes in Moles Weight Loss Date: HIV/Other Blood Diseases Bleeding Disorders Endocrine Thyroid Problems History of Keloids Neurological Problems Headaches GENITOURINARY Genital or Oral Herpes S.T.D.'s Blood in Urine Urinary Tract Infection Problems Urinating Prostate Problems Kidney Problems Eyes Vision Problems Arthritis Mobility/Joint Problems GASTROINTESTINAL Constipation Blood in Stool Liver Problems CARDIOVASCULAR Heart Problems Deep Vein ThrombosDVT Blood Clots in Lungs/Legs High Blood Pressure RESPIRATORY Hearing Problems Sinus Problems PSYCHLATRIC Mood Swings Anxiety/Depression Sleep Apnea Please list any other conditions/illnesses not indicated above: best of my know6ecte, this form ation as complete and correct. I understand that my responsbdy to irform my doctor there aro amy danes to my he Patient Signature: Date: Physician Signature: Date Reviewed: . Patient Name Outpatient Lab Order Form MI Mercy Lab Fax Number 641-428-7886 i1 ex Colection DateTim When Tests are Complete: Send Patient Home Send Patient to Office Page Provider on Page Call Results to Fax Results to 3. o Courtesy Copy to Medicare will pay only for tests that meet the Medicare coverage criteria and are reasonable and necessary to treat or diagnose an individual patient. Medicare does not pay for tests for which documentation, inclucing the patient record, does not support that the lests were reasonable and necessary. Medicare generally does not cover routine screening tests even if the physician or other authorized test orderer considers the tests appropriate for the paient Tests with an "behind them means that reflex testing may be perfarmed- Please refer to the Mercy Lab Test Index Antibody Screen ALTISGPT ASTISGOT T3 Free T4 Free Testosterone, Total and Free Triglyceride AST Hepais 8 Surface Antigen* HIV Medicare Screen Iron and Iron Binding Latex RA CRP Sensitive (Cardiac) CA125 CBC with No Diff CBC with Manual Dt CEA Cell Morphology LH LH Urinalysis Routine" itamin B12/Folate Vitamin B12 Vit D 25-Hydroxy CEA Microalbumin Urine PTHINTParathyroid Hormone Intact VD25H Chemistry Panels HDL HDL Cholesterol Prenatal Profile WHIM Prenatal Profile no HIV Basic Metabolic Panel LDL Direct Cholestera ATPN Dermatologv Panel CK CREAT VCCL CRT Creatinine .stain74vneur 2ett E' URawn Urne Total volume Drug Screen Random Urine PSA B PSA, Medicare Screen Lipid Panel Comprehensive Metabolic Panel Protein 24 hr Urine Total Valume. Renal Panel General Health Panel (CBCAD, TSH, CMPL) Glucose 1 hr Gestational Glycohemoglobin (A1C) HCG Quant Serum llt; lli kudleFaibi @3a Sedimentation Rate Drug Levels HGBXHemoglobin Last Dose Date and Time 2. Mayo# Sianature of Orderina Provider Physi ician Order Form -Ima ging Services PATIENT INFORMATION Date of Birth: Please call Patient Patient will call to schedule Authorization # REQUESTING PHYSICIAN INFORMATION Phone Referring Physician Signature Results (check all that apply) D E-mail report (e-mail) O Fax report: (fax #) O CD with Images Special Request Phone Report: (phone #) EXAM FOCUS Brain MRI Brain MRA Neck MRI Neck MRA MRI Cervical Spine Thoracic Lumbar wol contrast who contrast O Other (specify) | O Vagal Nerve Stimulator. Program both generator output current and magnet output current to OMA to the MRI Cervical Spine Diagnostic Brain O Sinus ChestAbdomen Pelvis Thoracic Lumbar wol contrast w/wo contrast Other O Extremity (specify) Screening O Others (specify): Mammogram Abdomen O Pelvis OB/GYN Ultrasound Other Bone Liver PETICT Other Barium Enema (please select): O With air contrast O Without air contrast Upper G.I. (please select: O With small bowel series O Without small bowel series O X-ray (specfy): General Radiology Volding Cystourethrogram Fluoro Other (specify): Vascular Lab Chronic Venous Exam OPPG'S ABrs with waveform Dialysis Graft Eval. Peripheral Arial Exam Upper ExtremityTranscranial Doppler Carotid O Graft Flow O Temporal Artery Lower Extremity | O Nielsen Cold Challenge O Finger OToe(s) O R Arterial Duplex OAbdomen (please select) O Renal O Mesenteric O Portal Hepatic OAAA ORenal Transplant Other (specify): Left Other Specify Rev 0UDB Scan to PO-7070